#15803: Add support for stored procedures in Amazon Redshift

alonsomendez reported 2020-07-10T18:37:15Z · last modified 2021-04-21T17:17:57Z

Add support for stored procedures in Amazon Redshift

Dev	vaibhavjain
QA	AmarAccolite

Priority	Major
Complexity	Unknown

Component	DB - RedShift
Version	22.0

Stored Procedures were recently added to the Amazon Redshift platforms. Aqua Data Studio needs to support stored procedure objects.

ADS areas that need to change:

Schema Tree - object nodes, model nodes

Build object extraction queries

Layout - object nodes, model nodes - DB2ProceduresNode as an example

Database

Schema

Procedures

Dependencies

Parameters

Detail Window - HiveColumnsModel as an example.

Scripting - Add Procedures

Define script syntax, system catalog dependencies and keywords

Schema browser "Script object to" Layout - object nodes->popup - DB2TablesNode.constructPopupMenu as an example. Define the functions in the popup.

CREATE

DROP

EXECUTE

GRANT

REVOKE

Functional areas are Schema script generator, Script object to Query Analyzer Schema Compare, Visual editing and preview

Build keyword extraction queries

Create extracts for scripts

Build script syntax for each object - ScriptTable, ScriptView as examples

Schema script generator - See ScriptGeneratorDialog, ScriptGeneratorWorker

Visual Editing

Create, Alter, Drop and Object Properties - See TableDialog

Define the object parameters

Build keyword extraction queries

Create menus for object parameters

Define and build preview scripting - See scripting above.

Query Analyzer Window

Needs to be able to execute the Stored Procedure.

Results Window

Specialized query result sets - See PlatResultsFormatPanel, QueryThread, AFScriptContext and ShellScriptContext

Schema Compare

Add new schema objects to the selection - See SchemaObject

As listed in scripting section, will need to have all listed objects ddl scripted.

Object Search

Build search extracts for all procedure objects. See FindFrame and ObjectSearchctx

AquaScript

Support for new procedures for Schema Compare and Database Schema and Data Exporter.- CoreMetadata.java, ExportDatabaseToCSVTemplate.vm , SchemaCompareTemplate.vm

Test projects in studio

4 attachments

Redshift0.png

2021-03-30T14:45:15Z

112 KB
Redshift1.png

2021-03-30T14:45:28Z

123 KB
Preview Tab Script Generation

2021-04-02T19:58:01Z

135 KB
_aquadatastudio_15803__Add_support_for_stored_procedures_in_Amazon_Redshift.patch

2021-04-06T16:41:47Z

920 B

All Comments (22) Change History

tomconrad 2021-03-09T16:18:22Z

Hi Vaibhav,

Can we do a null check in ExtractProcedure?

while (rs.next()) {

String language = rs.getString(1);

String text = rs.getString(2);

theProcedure = new AFProcedure(database, schema, name, params, language, "", text, "");

}

Thanks,
Tom

vaibhavjain 2021-03-10T13:54:51Z

[Jahid] thanks for the feedback @tom, I have added the null checks.

vaibhavjain 2021-03-10T17:48:22Z · (edited)

[Jahid]

------------------------------------------

1. Procedure Dependencies - tables and referenced procedures

1. Proper Dependencies information is not available in pg_depend table https://www.postgresql.org/docs/7.4/catalog-pg-depend.html

2. The Redshift documentation (documentation) page number 615- Nested Functions says - "Amazon Redshift doesn't track dependencies for UDFs" so most probably the information is not available for procedures also.

3. Dependencies are not supported in PostgreSQL also

4. So need some guidance if anyone has understanding how to fetch dependencies in Redshift DB?

2. Permissions

1. Is the permission tab applicable for Redshift db Alter Procedure UI?? ( Please note permissions tab is not supported in postgresql- Alter Procedure UI)

3. Rename - alter procedure

1. This is not supported currently, please let me know if this is applicable for the Redshift procedure and then i'll see how we implement it?

Please provide your feedback,

Thanks,

Jahid Ali

tomconrad 2021-03-10T17:54:26Z

Hi Vaibhav, Jahid,

Why did we add this in ProcedureEditor? It does not support Redshift?

COptionPane.showMessageDialog

(

fr,

TXT.get("ProcedureEditor.msgDBSupport", "Procedure Editor only supports Oracle, DB2 LUW, SQL Server, Sybase ASE 16.x +, Sybase Anywhere, Sybase IQ, PostgreSQL, Informix and Redshift."),

TXT.get("ProcedureEditor.titleDBSupport", "DB Support"),

JOptionPane.WARNING_MESSAGE

);

Thanks,

Tom

tomconrad 2021-03-10T18:46:29Z

Hi Jahid,

We can skip the dependencies since it doesn't seem to support it. Permissions doesn't have to be added to be consistent with PostgreSQL. I believe permission is just a user being granted privileges to the sp.

We should implement the rename. In the alter gui, allow the user to change the name and under the covers execute:

ALTER PROCEDURE sp_name [ ( [ [ argname ] [ argmode ] argtype [, ...] ] ) ]

RENAME TO new_name

You might need to do a create/replace of the procedure. See how PostgreSQL has it.

Hope that helps,

Tom

vaibhavjain 2021-03-11T13:07:53Z

Hi Tom,

Thank a lot for your inputs.

1. Regarding showMessageDialog msg - please note this message mentions the dbs where Create Procedure in editor functionality is supported.

2. Thanks, I have implemented the Rename functionality in Alter procedure for redshift now

vaibhavjain 2021-03-12T07:16:55Z

[Jahid]

Code checked in under following revisions

58538

58551

58567

58561

58563

shubham.tripathi 2021-03-16T08:38:02Z · (edited)

Hi Tom,

Grant and Revoke option is not generating any query in the query analyzer for stored procedure.

Is it an expected behaviour or not.

Thanks,

Shubham Tripathi

tomconrad 2021-03-16T16:00:31Z

Hi Shubham,

Although this is possible to implement, it seems that grant and revoke do not work for stored procedures across most of the databases in ADS. So I guess it is expected behavior. Let's not worry about it right now.

Thanks for pointing that out,

Tom

Some samples of grant and revoke...

GRANT EXECUTE on PROCEDURE "tom1"."public"."update_comp3" (IN p_name varchar, OUT p_out int4) to "tom"

REVOKE EXECUTE on PROCEDURE "tom1"."public"."update_comp3" (IN p_name varchar, OUT p_out int4) from "tom"

shubham.tripathi 2021-03-17T04:59:52Z

Hi Tom,

Thank a lot for your feedback..

shubham.tripathi 2021-03-22T07:25:05Z · (edited)

QA verified: Add support for stored procedures in Amazon Redshift

Tested End to end scenarios for stored procedure in Amazon Redshift and its working as expected.

Build tested: ADS v 21.0.0-dev-22

Platform tested : Windows-10

VM - 10.220.201.70

Test rail link : ADS 22.0 test suite_Windows_Driver update issues - TestRail

Thanks

Shubham Tripathi

Laveena_Ambala 2021-03-22T07:27:40Z

QA verified ,It is working as expected.

Tested end to end scenarios for stored procedures in amazon redshift.

Build tested: Aqua Data Studio 22.0.0-dev-22-no-ofsc

Platform tested : Mac Bigsur

Vm ip : 10.20.20.33

DB tested :Amazon Redshift

Test rail link: https://idera.testrail.net/index.php?/runs/view/4168&group_by=cases:section_id&group_order=asc&group_id=28027

rajkiran.reddy 2021-03-24T05:15:58Z

QA verified ,It is working as expected.

Tested end to end scenarios for stored procedures in amazon redshift.

Build tested: Aqua Data Studio 22.0.0-dev-22-no-ofsc

Platform tested : Ubuntu

Vm ip : 10.220.200.61:5901

DB tested :Amazon Redshift

Test rail link: https://idera.testrail.net/index.php?/runs/view/4169&group_by=cases:section_id&group_id=28027

KarunaAccolite 2021-03-26T13:26:58Z

assigning to automation for further action on this

tomconrad 2021-03-30T20:26:01Z · (edited)

Hi Team,

When I operate the schema generator for all table, view and procedure objects, I only see one procedure in the preview window? Please see email that I sent to Jahid.

Thanks,

Tom

vaibhavjain 2021-04-02T19:57:24Z

Hi Tom,

Can you please let me know if there is any other options/setting you had chosen other than using double quote and preview tab for generating the scripts?

I just an a preview on your db (tom1) from my local build and i see all procedures appearing in preview tab.

Please check once more!

vaibhavjain 2021-04-06T11:38:11Z · (edited)

Hi Tom,

I tried to debug this issue of preview tab for procedures on this 10.220.202.3 machine.

One strange behavior i see that the $$ sign is somehow replaced with 'true' on this machine whether generating the script from schema generator or via scripting (script object as-Procedure)

-------------------------------------following script is from 10.220.202.3 -------------------------

CREATE PROCEDURE public.update_comp_out_and_inout (OUT p_out int4, INOUT p_inout int4)

AS trueBEGIN

UPDATE emp SET TOT_COMP= emp.SALARY * emp.COMM

WHERE NAME = 'Curly';

END;true

LANGUAGE 'plpgsql'

-------------------------------------following script is from local build on same tom1 db-------------------------

CREATE PROCEDURE "public"."update_comp_out_and_inout" (OUT p_out int4, INOUT p_inout int4)

AS $$BEGIN

UPDATE emp SET TOT_COMP= emp.SALARY * emp.COMM

WHERE NAME = 'Curly';

END;$$

LANGUAGE 'plpgsql'

Not sure what could i conclude on this environment difference on this machine and on my pc?

-Jahid

tomconrad 2021-04-06T16:46:04Z

Hi Jahid,

Not sure what you are asking above...

Since I guess I wasn't clear enough in my note on how to fix this, I coded up a patch which is attached to fix this issue for Redshift. You now have to apply the same logic to Snowflake and populate the _specificName variable in ExtractProcedure.

Hope that helps,

Tom

vaibhavjain 2021-04-19T05:58:21Z · (edited)

As per tom's comment the code changes for redshift and snowflakes are committed to Revision No #58781

shubham.tripathi 2021-04-19T10:39:12Z

QA verified the issue has been resolved and its working fine now.

Build: Build-\\172.20.32.12\Divisions\Divisions\Aquafold\datastudio\v22.0.0-dev-49-no-ofsc

VM - 10.220.202.3

Os Windows 8.1

Thanks,

Shubham

KarunaAccolite 2021-04-21T07:18:56Z

moving to Amar for further action on this

AmarAccolite 2021-04-21T17:17:57Z

Automation verified. Please find the test run below.

https://idera.testrail.net/index.php?/runs/view/4381

Search Tips

Aqua Data Studio / nhilam

Add support for stored procedures in Amazon Redshift

4 attachments

Issue #15803

Completion

1 issue link

Issue #15845

Search Tips

Aqua Data Studio / nhilam

Title

Add support for stored procedures in Amazon Redshift

4 attachments

Issue #15803

Completion

1 issue link

Issue #15845