wsschema command

The schema tool can be used to view the database schema in XML form or match an XML schema to an existing database.

The wsschema tool can reflect on the current database schema, optionally translating it into an XML representation for further manipulation. The schema tool can take an XML schema definition, calculate the differences between the XML and the existing database schema, and apply the necessary changes to make the databases correspond to the XML schema. The XML format used by the schema tool is abstract from the differences in SQL dialects used by different vendors. The tool also automatically adapts its SQL to meet foreign dependencies, thus the schema tool is useful as a general way to manipulate the schemas.

Syntax

The command syntax is as follows:

(UNIX) (ZOS)

wsschema.sh [parameters][arguments]

(iSeries)

wsschema [parameters][arguments]

(Windows)

wsschema.bat [parameters][arguments]

Issue the command from the bin subdirectory of the profile_root directory.

Parameters

The wsschema tool accepts the standard set of command-line arguments defined by the configuration framework along with the following:

• -ignoreErrors/-i <true/t | false/f>: If set to false, an exception occurs if the tool encounters any database errors.

  The default is set to false.

• -file/-f <stdout | output file>: Use this option to write a SQL script for the planned schema modifications, rather than committing them to the database.

  When used with the export or reflect actions, the named file is used to write the exported schema XML. If the file names a resource in the class path, data is written to that resource. Use stdout to write to standard output. The default setting is stdout.

• -openjpatables/-ot <true/t | false/f>: When reflecting the schema, this parameter determines whether to reflect on tables and sequences whose names start with OPENJPA_.

  Certain OpenJPA components can use such tables and sequences, like the table schema factory. When using other actions, openjpaTables controls if these tables can be dropped. The default setting is false.

• -dropTables/-dt <true/t | false/f>: When this option is set to true, schema drops tables that are unused during retain and refresh actions.

  The default is true.

• -dropSequences/-dsq <true/t | false/f>: If this option is set to true, schema drops sequences that are unused during retain and refresh actions.

  The default is true.

• -sequences/-sq <true/t | false/f>: This flag determines if sequences can be manipulated.

  The default is true.

• -indexes/-ix <true/t | false/f>: This flag determines if indexes can be manipulated on existing tables.

  The default is true.

• -primaryKeys/-pk <true/t | false/f>: This flag determines if primary keys can be manipulated on existing tables.

  The default is true.

• -foreignKeys/fk <true/t | false/f>: This flag determines if foreign keys can be manipulated on existing tables.

  The default is true.

• -record/-r <true/t | false/f>: This flag permits or prevents writing schema changes made by the schema tool to the current schema factory.

  Select true to permit writing schema changes or false to prevent writing schema changes. The default is set to true.

• -schemas/-s <schema list>: Denotes a list of schema and table names the OpenJPA should access when running the schema tool.

  This is the equivalent to setting the openjpa.jdbc.Schemas property to run one time.

  Important: The schema tool accepts the -action/-a flag. ultiple actions can be composed in a list, separated by commas. The available actions are:

  • add: This is the default action if no other actions are specified. It updated the schema with the given XML documents by adding tables, columns, indexes, or other components. This action never drops any schema components.
  • retain: This action keeps all schema components in the given XML definition but drops the rest from the database. This action never adds any schema components.
  • drop: Drops all schema components in the schema XML. This action drops tables only if they would have 0 columns after dropping all columns listed in the XML.
  • refresh: This action is the equivalent of the retain and the add functions.
  • build: Generates SQL to build a schema matching the one in the supplied XML file. Unlike theadd action, this option does not take into account the fact that part of the schema defined in the XML file may already exist in the database. This action is typically used with the -file/-f parameter flag to write a SQL script. This script can be used later to recreate the schema in the XML.
  • reflect: Generates an XML representation of the current database schema.
  • createDB: This action generates SQL to recreate the current database. This action is typically used with the -file/-f parameter flag to write a SQL script used to recreate the current schema on a new database.
  • dropDB: Generates SQL to drop the current database. Like the createDB action, this can be used with the -file/-f parameter flag to script a database drop rather than manually perform it.
  • import: Imports the given XML schema definition into the current schema factory.

    This action does nothing if the schema factory does not store a record of the schema.

  • export: Exports the current schema factory stored schema definition to an XML file.

    This can produce an empty file if the schema factory does not store a record of the schema.

  • deleteTableContents: This action implements SQL to delete all rows from all tables that OpenJPA finds.

The wsschema tool is used to obtain an XML file that describes the schema of our database. To generate an XML schema file:

• We must have database tables and the database connection configured.

• Run the wsschema tool from the command line in the $ {profile_root}/bin directory.

• The tool generates an XML file that describes the database schema.

Messages and errors are logged to the administrative console as specified by the configuration.

Examples

Add the necessary schema components to the database to match the given XML document without dropping any data:

(UNIX) (ZOS)

$ wsschema.sh targetSchema.xml

(iSeries)

$ wsschema targetSchema.xml

(Windows)

$ wsschema.bat targetSchema.xml

Repeat the same action as the previous example, this time not changing the database but instead writing any planned changes to a SQL script:

(UNIX) (ZOS)

wsschema.sh -f script.sql targetSchema.xml

(iSeries)

wsschema -f script.sql targetSchema.xml

(Windows)

wsschema.bat -f script.sql targetSchema.xml

Write an SQL script that recreates the current database:

(UNIX) (ZOS)

$ wsschema.sh -a createDB -f script.sql

(iSeries)

$ wsschema -a createDB -f script.sql

(Windows)

$ wsschema.bat -a createDB -f script.sql

Refresh the schema and delete all the contents of all the tables that OpenJPA knows about:

(UNIX) (ZOS)

$ wsschema.bat -a refresh,deleteTableContents

(iSeries)

$ wsschema -a refresh,deleteTableContents

(Windows)

$ wsschema.bat -a refresh,deleteTableContents

Drop the current database:

(UNIX) (ZOS)

$ wsschema.sh -a dropDB

(iSeries)

$ wsschema -a dropDB

(Windows)

$ wsschema.bat -a dropDB

Write an XML representation of the current schema to the file schema.xml:

(UNIX) (ZOS)

$ wsschema.sh -a reflect -f schema.xml

(iSeries)

$ wsschema -a reflect -f schema.xml

(Windows)

$ wsschema.bat -a reflect -f schema.xml

Additional information

For more information read the JDBC information in the Apache OpenJPA documentation.

Apache OpenJPA User's Guide