Command Line Interface (CLI) for database access

Command Line Interface (CLI) to execute statements against a configured databases.

Usage:: zero runsql [dbKey] file
Examples:: zero runsql myDB sql/create.sql; zero runsql sql/derby/drop.sql

The runsql task executes an SQL file under one connection's transaction. This means that if a statement fails, the changes made up to that point are not committed.

If the optional dbKey is not provided, the runsql task will look to see if there is only one database configured and use that configuration. If more than one is configured, the runsql task will look in the configuration defaults. For instance, the following zero.config declaration sets a default dbKey:

/config/defaults/dbKey = "mydb"

Statements can be any valid statements against the configured database vendor and version. For instance, statements can be domain definition language (i.e. CREATE and DROP) or domain manipulation language (i.e. INSERT, UPDATE, and DELETE) statements. Query statements can be made, although they will not affect the outcome of the execution.

Frequently, a module provides statements for several vendors. There is no set convention provided, but the recommendation is to separate vendors in a simple folder hierarchy as shown in the following example:

myapp
 - app
 - config
    - sql
       - db2
          - myapp_create.sql
          - myapp_drop.sql
          - myapp_insert.sql
       - mysql
          - myapp_create.sql
          - myapp_drop.sql
          - myapp_insert.sql
 - java
 - public

Clearly more or fewer vendors can be provided. By following this simple recommendation (besides documentation) you can quickly communicate what vendors are supported and where supported statements are located.

The virtual directory feature in WebSphere sMash's application model makes it a best practice to scope your files with a unique prefix. The previous example uses the application name "myapp" to prefix the file names. Doing this prevents the less obvious case in which a dependency inadvertently overrides a file lower in the dependency chain.