Automation using Scripts

DbSchema you can automate different processes using JScript or Groovy scripts and direct access to DbSchema API.

Groovy is a pure Java scripting language, with support for closures and lambda as below. Notice the variables in ${...} are replaced in GStrings. Groovy is known for being used in Gradle, is simple and fully Java compatible. The scripts can be edited and tested in the SQL Editor.

// 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"""

From Groovy you can access most of the DbSchema classes and methods. Details in DbSchema API.

The following variables are exposed directly to the Groovy script engine :

sql The physical connection to the database.
project The DbSchema project, with schemes, tables, columns, etc..
out The PrintStream out console output stream, visible in the Script Result Pane.

DbSchema can start in console mode and execute a given Java Groovy script using: Dbschema.exe -x <path-to-script>.

DbSchema can be stated in headless mode ( no splash screen ) using java -cp "lib/*" com.wisecoders.dbs.DbSchema -x path_to_sample_script When adding further parameters to the command, the parameters will be visible in the script using the variable parameters as array.

Sample Groovy Scripts

The script is connecting to a local MySql database, reverse engineer the schema and generate HTML documentation.

import com.wisecoders.dbs.schema.*;

Project proj = new Project("sample", "MySql");
println proj.getName()
// CONNECT TO A LOCAL INSTALLED MYSQL, DATABASE 'sakila' AS USER ROOT
Connector con = proj.createConnector("my_alias","localhost",3306,"com.mysql.jdbc.Driver","sakila","root")
// HERE SPECIFY THE CONNECTION PASSWORD
con.setPassword("abc")
Schema sch = con.importSchema("sakila")
for ( Table table : sch.tables ){
  out.println( table.getName() );
}
// GENERATE HTML DOCUMENTATION TO FILE
Layout layout = proj.createLayoutIncludeAllTables( "myLayout", true );
layout.generateHtmlDocumentation(new File("C:/Temp/sample.html"), true, true);
// SIMILAR GENERATE PDF DOCUMENTATION USING
layout.generatePdfDocumentation(new File("C:/Temp/sample.html"), true, true);

//////////////////////////////////////////////////////////////////////////////
// Mac Usage: /Applications/DbSchema/DbSchema.app/Contents/MacOS/JavaApplicationStub -x dbschema_export.groovy
// Windows Usage: DbSchema.exe  -x dbschema_export.groovy
//////////////////////////////////////////////////////////////////////////////
import com.wisecoders.dbs.schema.*;
import com.wisecoders.dbs.project.store.*;
import groovy.io.FileType;

dir_name = 'C:\\Temp\\'

def list = []
def dir = new File(dir_name)
def pattern = ~/.*\.dbs/
dir.eachFileMatch (pattern) { file ->
    list << file
}

for (file in list) {
    exportLayoutsToHtml(file, dir)
}

def exportLayoutsToHtml(file, dir) {
    println file

    ProjectLoader projectLoader = new ProjectLoader()
    projectLoader.parse( new FileInputStream( file ))
    Project proj = projectLoader.getProject()
    println proj.getName()
    for (layout in proj.layouts) {
        println layout.getName()
        def export_name = proj.getName()
        if (layout.getName() != "Default Layout" && layout.getName() != export_name && layout.getName() != export_name + " " + "Default Layout") {
            export_name = export_name + " - " + layout.getName()
        }
        export_path = dir.toString() + "/" + export_name + ".html"
        println export_path
        htmlFile = new File(export_path)
        layout.generateHtmlDocumentation(htmlFile, true, true)

        frontMatter = "---\n"
        frontMatter += "title: " + export_name + "\n"
        frontMatter += "ordinal: " + export_name.toLowerCase() + "\n"
        frontMatter += "---\n"
        def text = frontMatter + htmlFile.text
        htmlFile.write(text)
    }
    println "------------"
                }

import com.wisecoders.dbs.schema.*;
import com.wisecoders.dbs.rdbms.sync.engine.nodes.*

// LOAD LOCAL PROJECT FROM FILE
ProjectLoader projectLoader = new ProjectLoader()
projectLoader.parse( new FileInputStream( new File("C:/Temp/sakila.dbs" )))
Project localProject = projectLoader.getProject()


// LOAD dbProject FROM THE MYSQL DATABASE, DATABASE 'sakila' AS USER ROOT
Project dbProject = new Project("sample", "MySql");
// CONNECT TO A LOCAL INSTALLED MYSQL, DATABASE 'sakila' AS USER ROOT
Connector con = dbProject.createConnector("my_alias","localhost",3306,"com.mysql.jdbc.Driver","sakila","root")
con.setPassword("abc")
con.importSchema("sakila")

final SyncPair syncProject = new SyncPair( localProject, new SchemaMapping( localProject ), dbProject )
final StringWriter sw = new StringWriter();
final PrintWriter writer = new PrintWriter(sw);
syncProject.setAction( SyncAction.toRight )
syncProject.toSql( localProject.getRdbms(), SyncAction.toRight, SyncSide.left, writer )
out.println("==== Diff Script ====")
out.println( sw )

DbSchema model can be imported using Groovy scripts. Here the CSV file:

1,sample,persons,id,varchar,12
2,sample,persons,firstname,varchar,20
3,sample,persons,lastname,varchar,20

And the script. After executing the script save the project file and restart DbSchema.

import java.io.*
import org.apache.commons.csv.*
import com.wisecoders.dbs.schema.*

Reader reader = new FileReader("C:\\Temp\\test.csv");
Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(reader);
for (CSVRecord record : records) {
    String schemaName = record.get(1)
    String tableName = record.get(2)
    String columnName = record.get(3)
    String dataType = record.get(4)
    int length = Integer.parseInt(record.get(4))
    println "schema=${schemaName} table=${tableName} column=${columnName} dataType=${dataType} length=${length}"

    Schema schema = project.getSchema("", schemaName)
    if ( schema == null ) schema = project.createSchema(schemaName,"")
    Table table = schema.getTable(tableName)
    if ( table == null ) table = schema.createTable(tableName)
    Column column = table.createColumn(columnName,dataType)
    column.setLength(length)
}

DbSchema model can be imported using Groovy scripts. Use this or similar script for converting models from other tools ( PowerDesigner, etc. ) to DbSchema. For the current sample script the schema was previous reverse engineer from the database. The script below loads the comments from the csv file as column comments. The delimiter used in this file is '|'. Here is the csv file, with the structure: Schema|Table|Column|Comment

SAKILA|customer|customer_id|The customer unique identifier
SAKILA|customer|store_id|The id of the store where the purchase was done

And the script:

import com.wisecoders.dbs.schema.*

new File('/tmp/schemaComments.csv').eachLine { line ->
 String[] data = line.split('\\|')
 println "Split is: ${data}"
 if ( data.size() == 4 ){
   Schema sch = project.getSchema( null, data[0] )
   if ( sch == null ){
     println "Could not find Schema ${sch} "
   } else {
     Table table = sch.tables.getByName( data[1] )
     if ( table == null ){
       println "Could not find Table ${table} "
     } else {
       Column col = table.columns.getByName( data[2] )
       if ( col == null ){
         println "Could not find Column ${col} "
       } else {
         println "Update ${sch}.${table}${col} comment to ${data[3]}"
         col.setComment( data[3] )
       }
      }
    }
  }
}

This script lists the database tables, columns, etc., by calling methods from the JDBC driver. This script can be used to check if the driver is compatible with the database.

println "--------- List Schemes ---------"
def rsSchema = sql.connection.metaData.getSchemas()
while( rsSchema.next() ){
  println "Schema: ${rsSchema.getString(1)}"
}

println "--------- List Catalogs ---------"
def rsCatalog = sql.connection.metaData.getCatalogs()
while( rsCatalog.next() ){
  println "Catalog: ${rsCatalog.getString(1)}"
}

println "---------List All Tables ---------"
String[] typesAll = ['TABLE', 'VIEW','SYSTEM_TABLE']
// Uses parameters: catalog, schema, table_filter, tableTypes. Change first 2 parameters with values from your schema.
def rsTablesAll = sql.connection.metaData.getTables( '', '', '',typesAll )
while( rsTablesAll.next() ){
  println "Table All: ${rsTablesAll.getString(1)} ${rsTablesAll.getString(2)} ${rsTablesAll.getString(3)}"
}

println "---------List Specific Schema / Catalog Tables ---------"
String[] types = ['TABLE', 'VIEW','SYSTEM_TABLE']
// Uses parameters: catalog, schema, table_filter, tableTypes. Change first 2 parameters with values from your schema.
def rsTables = sql.connection.metaData.getTables( 'mySchema', '', '',types )
while( rsTables.next() ){
  println "Table: ${rsTables.getString(1)} ${rsTables.getString(2)} ${rsTables.getString(3)}"
}

println "---------PrimaryKeys ---------"
// Use parameters: catalog, schema, table_filter. Change first 3 parameters with values from your schema.
def rsPks = sql.connection.metaData.getPrimaryKeys( 'mySchema', '', 'myTable' )
while( rsPks.next() ){
   println "\nPrimary Key (catalog, schema, table_name, column_name, column_position, pk_name)"
 for ( int i = 0; i < rsPks.metaData.getColumnCount(); i++ ){
    print " ${rsPks.getString(i+1)}"
 }
}

println "---------List Indexes ---------"
// requires pareter: catalog, schema, table, unique, approximate. Change first 3 parameters with values from your schema.
def rsIdx = sql.connection.metaData.getIndexInfo( 'mySchema', '', 'myTable', true, true )
while( rsIdx.next() ){
  println "\nIndex Info (catalog, scheam, table_name, non_unique, index_catalog, name, type, column_position, column_name, ordering, cardinality, pages, filter_condition)"
  for ( int i = 0; i < rsIdx.metaData.getColumnCount(); i++ ){
     print " ${rsIdx.getString(i+1)}"
  }
}

println "---------ForeignKeys ---------"
// Use parameters: catalog, schema, table_filter. Change first 3 parameters with values from your schema.
def rsEPks = sql.connection.metaData.getExportedKeys( 'mySchema', 'myCatalog', 'myTable' )
while( rsEPks.next() ){
 println "\nForeign Key as Exported Keys ( from mytable ) ----------"
 for ( int i = 0; i < rsEPks.metaData.getColumnCount(); i++ ){
    print " ${rsEPks.metaData.getColumnName(i+1)}: ${rsEPks.getString(i+1)}, "
 }
}
def rsIPks = sql.connection.metaData.getImportedKeys( 'mySchema', 'myCatalog', 'myTable' )
while( rsIPks.next() ){
 println "\nForeign Key as Imported Keys ( referring myTable )----------"
 for ( int i = 0; i < rsIPks.metaData.getColumnCount(); i++ ){
    print " ${rsIPks.metaData.getColumnName(i+1)}: ${rsIPks.getString(i+1)}, "
 }
} 

Script connects to the database, iterates the tables and checks for row count. Tables with data are included in a new layout.

import com.wisecoders.dbs.schema.*

Layout layout = project.createLayout("Tables with Data")
new java.awt.Point( 0,0)
for ( Schema schema: project.schemas ){
  for ( Table table: schema.tables){
    String query = "SELECT count(*) cnt FROM ${table.getNameWithSchemaName()}"
    sql.eachRow( query ){ r->
      println "Table ${table} count=${r.cnt}"
      if ( r.cnt > 0 ) {
        layout.attach( table )
      }
    }
  }
}
layout.autoArrange()
// THE LAYOUT IS CREATED IN THE LEFT STRUCTURE TREE PANE. TO OPEN IT DOUBLE-CLICK IT THERE.

If you have many scripts to execute, you may store them as Groovy classes outside DbSchema project. This may be also the case when you want to reuse the same scripts in multiple projects. In this example, I save the project file in C:\sample\sakila.dbs. I also create one more script Other.groovy in C:\sample\com\Other.groovy with this text:

package com

import groovy.sql.Sql
import java.sql.SQLException

class Other {
    public Other(){
      println "I am here"
    }

	public int get(){
	   return 100
	}
}
And in DbSchema I create a script with this text:
import com.Other;
int result = new Other().get()
println "I got $result"

No database ensures the columns order, as there is no ALTER TABLE command for this. The table column order can be changed in DbSchema from the Table Dialog, or in diagram by drag & drop of the columns up or down. You can do this also using a Groovy script like below.

import com.wisecoders.dbs.schema.*;
import com.wisecoders.dbs.diagram.model.*;

for (sch in project.schemas) {
  for (tbl in sch.tables) {
    for(col in tbl.columns) {
	  if ( col.getName() == "description" ) {
	    println "Moving up ${tbl.getName()}.${col.getName()} "
	    tbl.columns.moveUp(col);
      }
    }
  }
}
import com.wisecoders.dbs.schema.*;
import com.wisecoders.dbs.project.store.*;

ProjectLoader projectLoader = new ProjectLoader();
projectLoader.parse( new FileInputStream( new File ("C:\\Temp\\sakila.dbs") ));
import com.wisecoders.dbs.schema.*
import com.wisecoders.dbs.diagram.model.*


for ( Layout layout: project.layouts ){
  for ( Depict depict : layout.depicts ){
     println "<rectangle x='${depict.getPosition().getX()}' x='${depict.getPosition().getX()}'>"
     println "<title>${depict.getEntity().getName()}</title>"
     for ( Attribute attr: depict.getEntity().attributes ){
       println "<text>${attr.getName()}</text>"
     }
     println "</rectangle>"
  }
}

This example shows how to create two tables and a virtual foreign key. This code can be changed, so you can create virtual foreign keys by scanning schema tables and matching columns by name or by any other condition. You can also read some information from the database using the sql connection and generate the virtual foreign keys.

import com.wisecoders.dbs.schema.*

Table master = project.schemas.getByName('public').createTable( 'master')
Column master_id = master.createColumn( 'id', 'integer')
master.createColumn( 'firstname', 'varchar').setLength(120)

Table slave = project.schemas.getByName('public').createTable( 'slave')
Column slave_id = slave.createColumn( 'id', 'integer')
slave.createColumn( 'firstname', 'varchar').setLength(120)

ForeignKey fk = slave.createRelation('fk_master_slave')
fk.addColumns( slave_id, master_id )
fk.setVirtual(true)

Please don't hesitate to ask us on Help/Report Bug menu option for more samples or published classes or methods.