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

Background

EXAplus and many other database browsers provide the functionality to create the DDL for the database.
This solution is a Lua script to do the same task and generates the DDL for each database object based on the system tables.

Prerequisites

There are parameters to influence the output style. Documentation on the parameters and some known restrictions can be found at the beginning of the script. The latest release covers all supported database versions. Please see the header of the script for more information on what is supported and the current limitations.

PARAMETERS:
-

Parameter Data Type Meaning
add_user_structure Boolean If true then DDL for adding roles and users is added (at the top, before everything else).
add_rights Boolean If true then DDL for user & role privileges is added (at the bottom, after everything else).
store_in_table Boolean If true, the entire output is stored in the table "DB_HISTORY"."DATABASE_DDL" before the output is displayed.

 

Execution

You can execute the script with the below statement:

-- parameters: add_user_structure, add_rights, store_in_table
execute script create_db_DDL(true, true, false);

 

Additional References

Comments
Joep
Padawan

This version has a bug on line 661 in the function add_script_to_DDL where it tries to retrieve the source of a script

 
function add_script_to_DDL(schema_namescript_name)                --ADD SCRIPT-------------------------------------------------------------------------------------------
    sqlstr_flush()
    as1_success, as1_res = pquery([[SELECT SCRIPT_SCHEMA, SCRIPT_TEXT FROM EXA_DBA_SCRIPTS WHERE SCRIPT_NAME=:s]], {s=script_name})
        if not as1_success then
            error('Error at as1')
        end
    sqlstr_lf()
    sqlstr_add('-- BEGIN OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n')
    sqlstr_commit()
    sqlstr_add('\nOPEN SCHEMA \"'..schema_name..'\";')    --Open schema to create the script
    sqlstr_commit()
    sqlstr_add('\n--/\n'..as1_res[1][2]..'\n\/')
    sqlstr_commit()
    sqlstr_add('\nCLOSE SCHEMA;')
    sqlstr_commit()
    sqlstr_add('\n-- END OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n')
    sqlstr_lf()
    sqlstr_commit()
end
 

The sql query doesn't filter on SCRIPT_SCHEMA. It also ignores the SCRIPT_SCHEMA returned by the sql query. Together this means it can backup the wrong source for scripts in different schemas with the same name.

mwellbro
Xpert

Hi @exa-Nico , the labels do state that this is only applicable to V.6.0 & V.6.1 and yes, when running this on a V.7 I do have to adjust the GRANT PRIORITY GROUP ( now ALTER USER ... SET CONSUMER_GROUP ) and the corresponding fields from exa_dba_users - is there a particular reason why this is not hosted on github so that the community can pull- & merge something like this ? Or is it and I just wasn´t able to look it up ?

Cheers,
Malte

exa-Nico
Community Manager
Community Manager

@mwellbro we are working to move all of the scripts to Github for exactly that reason 😁

 

Charlie
Xpert

any news on moving this to github?

mwellbro
Xpert

alter @exa-Nico set ping_keep_alive=30 days;  -- 😉

exa-Nico
Community Manager
Community Manager

@mwellbro I've nudged some of my colleagues who are working on it 😅

exa-Aleksandr
Team Exasol
Team Exasol

Hi @mwellbro , hi @Charlie , hi @Joep ,

As you might have noticed, that script was updated and moved to github some time ago.

Today we've updated it there to make it work even better with version 7.

In particular, concerns regarding add_script_to_DDL and priority/consumer groups were addressed.

Feel free to test out and let us know if you have further questions!

ecoh_dr
SQL-Fighter

Hello. While executing the provided CREATE OR REPLACE LUA script I get the following error:

 

Errore SQL [42000]: failed to create script: syntax error in line 1137: <eof> expected near 'OR' (Session: 1724268256439566336)

 

 

databaseProductName = EXASolution
databaseProductVersion = 7.1.4