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.

Developers may find that they need the wsschema tool for its powerful functions. The wsschema tool can reflect on the current database schema, optionally translating it into an XML representation for further manipulation. Also, 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:
[AIX] [HP-UX] [Linux] [Solaris]

wsschema.sh [parameters][arguments]

(Windows)

wsschema.bat [parameters][arguments]

Issue the command from the bin subdirectory of the app_install_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 will be thrown 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 this is 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. 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 may use such tables and sequences, such as the table schema factory. When using other actions, openjpaTables controls whether these tables can be dropped or not. The default setting is false.

• -dropTables/-dt <true/t | false/f>: When this option is set to true, schema drops tables that appear to be 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 appear to be unused during retain and refresh actions. The default is true.

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

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

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

• -foreignKeys/fk <true/t | false/f>: This flag determines if foreign keys may 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 once.

  The schema tool accepts the -action/-a flag. Multiple actions may 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 will drop 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 in conjunction with the -file/-f parameter flag to write a SQL script. This script may be used later to recreate the schema in the XML.

  • reflect: Generates a XML representation of the current database schema.

  • createDB: This action generates SQL to recreate the current database. This action is typically used in conjunction with the -file/-f parameter flag to write a SQL script that can be used to recreate the current schema on a new database.

  • dropDB: Generates SQL to drop the current database. Like the createDB action this may 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 will do nothing if the schema factory does not store a record of the schema.

  • export: Exports the current schema factory's stored schema definition to a XML file.

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

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

 

Usage

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

• we need to have database tables and the database connection configured.

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

• The tool will generate a XML file that describes the database schema.

Messages and errors are logged to the 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: [AIX] [HP-UX] [Linux] [Solaris]

$ wsschema.sh 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: [AIX] [HP-UX] [Linux] [Solaris]

 wsschema.sh -f script.sql targetSchema.xml

(Windows)

 wsschema.bat -f script.sql targetSchema.xml

Write an SQL script that will recreate the current database: [AIX] [HP-UX] [Linux] [Solaris]

$ wsschema.sh -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: [AIX] [HP-UX] [Linux] [Solaris]

$ wsschema.bat -a refresh,deleteTableContents

(Windows)

$ wsschema.bat -a refresh,deleteTableContents

Drop the current database: [AIX] [HP-UX] [Linux] [Solaris]

$ wsschema.sh -a dropDB

(Windows)

$ wsschema.bat -a dropDB

Write a XML representation of the current schema to the file schema.xml: [AIX] [HP-UX] [Linux] [Solaris]

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

(Windows)

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

 

Additional information

See, refer to chapter 4 JDBC, in the OpenJPA reference documentation.

---

 

Related

Troubleshooting Java Persistence API (JPA) applications

 

Related information

Apache OpenJPA User's Guide: schema tool