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 its Java class,
org.apache.openjpa.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.
-openjpaTables/-ot <true/t | false/f>: When reflecting
on the schema, whether to reflect on tables and sequences whose names start with
OPENJPA_. Certain OpenJPA components may use such tables -
for example, the table schema factory option covered in
Section 12.2, “
Schema Factory
”. When using other
actions, openjpaTables controls whether these tables can be
dropped. Defaults to false.
-rollbackBeforeDDL/-rbddl <true/t | false/f>: Set this option to
true to send an initail rollback on the connection before any DDL statement is sent.
-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 OpenJPA should access during this run of the schema tool. This is
equivalent to setting the
openjpa.jdbc.Schemas property for a single run.
The schema tool also accepts an -action or -a
flag. Multiple actions can be composed in a comma-separated list.
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.
deleteTableContents: Execute SQL to delete all rows from
all tables that OpenJPA knows about.
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.18. Schema Creation
Example 4.19. 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:
java org.apache.openjpa.jdbc.schema.SchemaTool -f script.sql targetSchema.xml
Write a SQL script that will re-create the current database:
java org.apache.openjpa.jdbc.schema.SchemaTool -a createDB -f script.sql
Example 4.20. Table Cleanup
Example 4.21. Schema Drop
Drop the current database:
java org.apache.openjpa.jdbc.schema.SchemaTool -a dropDB
Example 4.22. Schema Reflection