8.2. Schema Tool
Prev | Chapter 8. Schema Information | Next

## 8.2. Schema Tool

Most users will only use the schema tool indirectly, through the interface provided by the mapping tool. You may find, however, that the schema tool is a powerful utility in its own right.

The schema tool's function is to take in an XML schema definition, calculate the differences between the XML and the existing database schema, and apply the necessary changes to make the database match the XML. The XML format used by the schema tool abstracts away the differences between SQL dialects used by different database vendors. The tool also automatically adapts its SQL to meet foreign key dependencies. Thus the schema tool is very useful as a general way to manipulate schemas.

You can invoke the schema tool through the `schematool` shell/bat script included in the Kodo JDO distribution, or through its Java class, `kodo.jdbc.schema.SchemaTool`. In addition to the universal flags of the configuration framework, the schema tool accepts the following command line arguments:

- `-ignoreErrors/-i <true/t | false/f>` : If `false`, an exception will be thrown if the tool encounters any database errors. Defaults to `false`.

- `-file/-f <stdout | output file>`: Use this option to write a SQL script for the planned schema modifications, rather them committing them to the database. When used in conjunction with the `export` action, the named file will be used to write the exported schema XML. If the file names a resource in the `CLASSPATH`, data will be written to that resource. Use `stdout` to write to standard output. Defaults to `stdout`.

- `-dropTables/-dt <true/t | false/f>`: Set this option to `true` to drop tables that appear to be unused during `retain` and `refresh` actions. Defaults to `true`.

- `-kodoTables/-kt <true/t | false/f>`: Whether to generate information about special Kodo-generated tables such as sequence tables as part of the database schema. Defaults to `false`.

- `-indexes/-ix <true/t | false/f>`: Whether to manipulate indexes on existing tables. Defaults to `true`.

- `-primaryKeys/-pk <true/t | false/f>` : Whether to manipulate primary keys on existing tables. Defaults to `true`.

- `-foreignKeys/-fk <true/t | false/f>` : Whether to manipulate foreign keys on existing tables. Defaults to `true`.

- `-record/-r <true/t | false/f>`: Use `false` to prevent writing the schema changes made by the tool to the current `schema factory`. Defaults to `true`.

- `-schemas/-s <schema list>`: A list of schema and table names that Kodo should access during this run of the schematool. This is equivalent to setting the kodo.jdbc.Schemas property for a single run.

The schema tool also requires an `-action` flag. The available actions are:

- `add`: Bring the schema up-to-date with the given XML document by adding tables, columns, indexes, etc. This action never drops any schema components.

- `retain`: Keep all schema components in the given XML definition, but drop the rest from the database. This action never adds any schema components.

- `drop`: Drop all schema components in the schema XML. Tables will only be dropped if they would have 0 columns after dropping all columns listed in the XML.

- `refresh`: Equivalent to `retain`, then `add`.

- `createDB`: Generate SQL to re-create the current database. This action is typically used in conjunction with the `-file` flag to write a SQL script that can be used to create a fresh schema for a new database.

- `build`: Generate SQL to build a schema matching the one in the given XML file. Unlike `add`, this option does not take into account the fact that part of the schema defined in the XML file might already exist in the database. Therefore, this action is typically used in conjunction with the `-file` flag to write a SQL script. This script can later be used to recreate the schema in the XML on a fresh database.

- `dropDB`: Generate SQL to drop the current database. Like `createDB`, this action can be used with the `-file` flag to script a database drop rather than perform it.

- `import`: Import the given XML schema definition into the current schema factory. Does nothing if the factory does not store a record of the schema.

- `export`: Export the current schema factory's stored schema definition to XML. May produce an empty file if the factory does not store a record of the schema.

| Note
The schema tool can manipulate tables, columns, indexes, primary keys, and foreign keys. It cannot create or drop the database schema objects in which the tables reside, however. If your XML documents refer to named database schemas, those schemas must exist.

We present some examples of schema tool usage below.

Example 8.2. Schema Creation

Add the necessary schema components to the database to match the given XML document, but don't drop any data:

    schematool -a add targetSchema.xml

Example 8.3. SQL Scripting

Repeat the same action as the first example, but this time don't change the database. Instead, write any planned changes to a SQL script:

    schematool -a add -f script.txt targetSchema.xml

Write a SQL script that will re-create the current database:

    schematool -a createDB -f script.txt

Example 8.4. Schema Drop

Drop the current database:

    schematool -a dropDB

Prev | Up | Next
Chapter 8. Schema Information | Home | 8.3. XML Schema Format