Automate Tasks using Java Groovy Scripts

DbSchema can automate different processes using Java Groovy scripts with direct access to the DbSchema API.

DbSchema API

Groovy is 100% compatible with Java. In DbSchema, Groovy is used to execute automation scripts and also for the SQL Statements, as Groovy templates. The automation scripts ca be accessed from Tools / Automation Scripts. The SQL statements can be configured from Model / Settings / Database Specific / SQL.

Groovy Scripts

Sample 
// Copy code in DbSchema SQL Editor and choose Groovy
sql.eachRow("select * from address") { r ->
    println "Gromit likes ${r.address_id}"
}
String multiline = """I am a multiline
text"""

Groovy Scripts can be executed from the SQL Editor by switching from SQL to Groovy.

Automate Database Processes

The Groovy scripts are receiving this variables :

sql The physical connection to the database.
project The DbSchema project, with schemes, tables, columns, etc..
out The Result Pane print stream.

Samples Scripts

In the Tools / Automation Scripts you can find different code samples.

Code Samples Dialog

If you wish, our team can help you to write your own script. Please write us using Help / Contact Support.

Groovy Templates

Sample: 
ALTER TABLE ${table} DROP COLUMN ${column}

Groovy Templates are used to configure the SQL Statements in Model / Settings / Database Specific / SQL

The Groovy templates can be any text, where the ${...} expressions will be evaluated with the given variables. The expressions can contain also some logic, like ${ name =='test' ? 'AAA' : 'BBB'}. To use code in templates, add <% ... %> expressions, for example <% println 'Test' %>

Run Automation Scripts in Headless Mode

DbSchema can start in headless mode (no interface) and execute Groovy scripts using: Dbschema.exe -x <path-to-script>.

To start DbSchema in headless mode use java -cp "lib/*" com.wisecoders.dbs.DbSchema -x path_to_sample_script.

Adding further parameters to the command will pass this variables as an array under the variable parameters.