SQL Editor

One of the DbSchema embedded tools is the SQL Editor. The editor is auto complete enabled. Just type CTRL + SPACE to get the complete pop-up.

The execute single query will execute the current selected text or the phrase ending with an ';'. The execute script will execute the complete SQL Editor text. By default the editor does not commit the database changes. You have to press the commit or rollback buttons in menu for this. For execute script option, there are two options:

Executing one single query will paginate the result. This because of memory limits. Get the complete result using 'save' from the result pane

Press one of the column in the result to order. The order will work for the displayed rows, not for the complete query. If the number of the records exceeds the result pane capacity, you have to use ORDER BY in the executed statement.

For scripts, the execution works than like this:

  • First is checked if the current text is a PLSQL block. A PLSQL block should be ended with an / . Before and after its content should have at least one empty line.
  • If is no PLSQL, DbSchema considers each sentance ended with ; and executes it.

Java Script Editor

The SQL editor can execute JavaScript code. In the editor menu is a combo where you can switch to JavaScript.

The variables below can be accessed directly by 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.

Here a simple groovy script listing two columns from a table 'customer':

var stmt = sql.prepareStatement( "SELECT first_name, last_name FROM customer");
var rs = stmt.executeQuery();

while (rs.next()) {
    out.print(rs.getString("FIRST_NAME") + " " + rs.getString("LAST_NAME") + "\n")
}
out.print ('Done');
About Groovy language you will find plenty of documentation on web.

Groovy Editor

The SQL editor can be used for running Groovy scripts as well. In the editor menu is a combo where you can switch to Groovy. Here a simple groovy script listing a column from the table address:

sql.eachRow("select * from address") { r ->
    println "Gromit likes ${r.address_id}"
}
About Groovy language you will find plenty of documentation on web.

From Groovy you may access many of the DbSchema classes and methods. Check the DbSchema JavaDoc.

The variables below can be accessed directly by 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.

Sample scripts

Reverse Engineer Project From Database and Generate HTML5 Documentation using Groovy

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);

Open DbSchema Project From File From Groovy

import com.wisecoders.dbs.schema.*;
import com.wisecoders.dbs.project.store.*;

ProjectLoader projectLoader = new ProjectLoader();
projectLoader.parse( new FileInputStream( new File ("C:\\Temp\\sakila2.dbs") ));
Script to list objects in 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)}, "
 }
}

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