Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Reverse Engineer from Exasol

dprutean
Padawan

I am working for a DbSchema, a database designer used by Exasol clients.

1. We are using a few queries to reverse engineer the procedures, functions, and view definition. There are separate queries to list the function name and source code. As there are differences between different versions of Exasol, and these queries could be not optimal, I am asking for a review (if we do it correctly or not). Below are the current queries.

2. I have also a feature request: in the CREATE TABLE to add brackets for the distribution key and partition key columns.

For example: CREATE TABLE t5 (a DOUBLE, b DOUBLE, DISTRIBUTE BY a,b);

to migrate to CREATE TABLE t5 (a DOUBLE, b DOUBLE, DISTRIBUTE BY (a,b));

We have a DDL parser that will understand easily the statement with brackets.

 

 

list.function=select function_name from exa_all_functions where function_schema = '${schema}'
load.function=select concat( 'CREATE FUNCTION ' , function_schema, '.', function_name, ' ', substr(function_text,instr(function_text,'('))) as forras from exa_all_functions where function_schema = '${schema}' and function_name = '${name}'
list.procedure=select script_name from sys.exa_all_scripts where script_schema = '${schema}'
load.procedure=select script_text as forras from sys.exa_all_scripts where script_schema \= '${schema}' and script_name\='${name}'
load.view=select view_name, view_text from sys.exa_all_views where view_schema\='${schema}'

1 REPLY 1

exa-Nico
Community Manager
Community Manager

Hi @dprutean 

Here are my thoughts on some of them:

list.function=select function_name from exa_all_functions where function_schema = '${schema}':

  • Maybe it's also worthwhile to show some other attributes of the function, ie object id, comment, etc. Otherwise looks good. 

load.function=select concat( 'CREATE FUNCTION ' , function_schema, '.', function_name, ' ', substr(function_text,instr(function_text,'('))) as forras from exa_all_functions where function_schema = '${schema}' and function_name = '${name}'

  • Just a suggestion, but maybe CREATE OR REPLACE is a good alternative depending on the exact use case. How about encapsulating the function schema and name in quotes to remove any problem with identifiers?


list.procedure=select script_name from sys.exa_all_scripts where script_schema = '${schema}'

  • Seems fine, but same feedback as with functions if you want to include additional information. 

load.procedure=select script_text as forras from sys.exa_all_scripts where script_schema \= '${schema}' and script_name\='${name}'

  • I'm not sure of the purpose of the '\=' but with a general '=' it works for me to return the script text. 

load.view=select view_name, view_text from sys.exa_all_views where view_schema\='${schema}'

  • I'm not sure of the purpose of the '\=' but with a general '=' it works for me to return the view text. 

Just as a general note, if you need assistance with this integration, maybe you can reach out to @exa-ThomasB.

For the feature request, I suggest to create an idea here: https://community.exasol.com/t5/ideas/idb-p/Idea

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution