Most users will only access the schema tool indirectly, through the interfaces provided by other tools. You may find, however, that the schema tool is a powerful utility in its own right. The schema tool has two functions:
To reflect on the current database schema, optionally translating it to an XML representation for further manipulation.
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 useful as a general way to manipulate schemas.
You can invoke the schema tool through the
schematool
shell/bat script included in the
Kodo 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
or reflect
actions, 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
.
-kodoTables/-kt <true/t | false/f>
:
When reflecting on the schema, whether to reflect on tables
and sequences whose names start with KODO_
.
Certain Kodo components may use such tables - for example,
the table
schema factory option covered
in Section 4.13.2, “Schema Factory”.
When using other actions, kodoTables
controls whether these tables can be dropped. Defaults to
false
.
-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
.
-dropSequences/-dsq <true/t | false/f>
:
Set this option to true
to drop sequences
that appear to be unused during retain
and refresh
actions. Defaults to
true
.
-sequences/-sq <true/t | false/f>
:
Whether to manipulate sequences.
Defaults to true
.
-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 schema tool. This is equivalent to setting
the kodo.jdbc.Schemas
property for a single run.
The schema tool also accepts an -action
or
-a
flag. The available actions are:
add
: This is the default action if you do
not specify one. It brings 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
.
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.
reflect
: Generate an XML representation
of the current database schema.
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 recreate the current
schema 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 manipulates tables, columns, indexes, constraints, and sequences. 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 4.17. Schema Creation
Add the necessary schema components to the database to match the given XML document, but don't drop any data:
schematool targetSchema.xml
Example 4.18. 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 -f script.sql targetSchema.xml
Write a SQL script that will re-create the current database:
schematool -a createDB -f script.sql