Database Setup Tool

The zero.data.setup.webtools package provides a convenient web interface for creating, dropping, and populating the database tables required by an application. By adding this package to your application's list of dependencies (in ivy.xml) and following a few simple conventions when naming your SQL files, you can provide a quick and easy way for other developers to get started with your code.

Adding the database setup tool to your application

To add the database setup tool to your application, just add the following line to your ivy.xml file:

<dependency name="zero.data.setup.webtools" org="zero" rev="1.0+"/>

This will add the tool that processes your SQL files as well as the tool's web interface.

Creating SQL files

The database setup tool uses the following directory and file naming convention so that the tool can find the right SQL for the right task:

/my-zero-app
    ...
    /sql
        /derby
            create.sql
            drop.sql
            sample.sql
        /mysql
            create.sql
            drop.sql
            sample.sql
        ...

As you can see, the application must have a sql directory that holds all of the SQL files. The files are split up by database product, since some SQL statements require vendor-specific syntax; you should have one directory for each database product that you support. The tool currently recognizes derby (for both embedded and network versions) and mysql.

The file names signify the tasks that are possible with the tool - creating tables, dropping tables, and inserting sample data. Each file should have one or more SQL statements. If a SQL file is not present when a task is executed, the tool assumes that the application does not support the task (or the product) and skips it.

Creating the database configuration

When you select the database product you're using and execute the table creation task, the database setup tool generates a configuration file named data.config that has the proper configuration stanza for your product (including the user name and password provided). This file is included in your application's configuration by appending @include data.config to your zero.config file. The net of this is that you do not need to include any database configuration in your application if you are using the database setup tool.

Executing SQL from dependencies

It is often the case that an application is built by combining one or more Zero components via ivy.xml; these components may have create, delete, and sample tasks of their own, represented by the appropriate SQL files. The database setup tool handles this by executing all of the dependencies' files first, followed by the application's files. As an example, if the user was trying to create the application's database tables for a MySQL database and the application had three dependencies (A, B, and C), the tool would execute the following files:

1. {A}/sql/mysql/create.sql
2. {B}/sql/mysql/create.sql
3. {C}/sql/mysql/create.sql
4. sql/mysqyl/create.sql

Running the tool from the command line

Once your SQL files are saved using the proper naming conventions, you can run the tool from the command line like so:

$ zero [task] -Ddatabase=x -Duser=y -Dpassword=z

where task is one of the following:

1. create-derby-embedded
2. drop-derby-embedded
3. sample-derby-embedded

1. create-derby-network
2. drop-derby-network
3. sample-derby-network

1. create-mysql
2. drop-mysql
3. sample-mysql

As an example:

$ zero create-mysql -Ddatabase=EmployeeDB -Duser=root -Dpassword=secret

Running the tool using the web interface

Invoking the tool from the command line is a requirement in certain situations, but often times it's nicer to use the web interface. You can access the tool's web UI by starting your application and going to http://localhost:8080/setup; there you will find a form that allows you to enter your database and user information and see the results in your browser.

Removing the web interface

Obviously, exposing a web interface that allows someone to drop database tables is a big security hole. If you are packaging your application or component for reuse by disparate users, you will probably want to remove the web interface. You can remove the web UI while still allowing for command line interaction by changing your ivy.xml file to refer to zero.data.setup instead of zero.data.setup.webtools. Another option is to require users to secure the /setup URI as part of their application configuration, but this is more error-prone.