6 Using the Schema Manager

The SchemaManager and its related classes provide API that you can use from a Java application to specify database tables in a generic format, and then create and modify them in a specific relational database. This decouples your TopLink project from a particular database schema while giving you a programmatic means of creating a database schema based on your TopLink project. For example, you can use the schema manager to recreate a production database in a nonproduction environment. This lets you build models of your existing databases, and modify and test them during development.

Note:

You can also create database tables manually during development using Oracle JDeveloper TopLink editor (see Chapter 4, "Using Oracle JDeveloper TopLink Editor") or TopLink Workbench (see Section 5.5.1.2, "Creating New Tables" and Section 5.5.3.4, "Generating Tables on the Database").

This chapter includes the following sections:

Introduction to the Schema Manager
Creating a Table Creator
Creating Tables with a Table Creator
Creating Database Tables Automatically

6.1 Introduction to the Schema Manager

Figure 6-1 summarizes the important SchemaManager classes and the primary means of using them.

Figure 6-1 SchemaManager Usage

Description of "Figure 6-1 SchemaManager Usage"

Although you can use the SchemaManager API directly, Oracle recommends that you create a TableCreator class and use its API (which, in turn, uses the SchemaManager).

You can automatically generate a TableCreator using the following:

TopLink Workbench during development (see Section 6.2.1, "How to Use TopLink Workbench During Development")
DefaultTableGenerator at run time (see Section 6.2.2, "How to Use the Default Table Generator at Run Time")

The TableCreator class owns one or more TableDefinition classes (one for each database table) and the TableDefinition class owns one or more FieldDefinition classes (one for each field).

The TableDefinition class lets you specify a database table schema in a generic format. At run time, TopLink uses the session associated with your TopLink project to determine the specific database type, and uses the generic schema to create the appropriate tables and fields for that database.

After creating a TableCreator class, you can use its API to create and drop tables (see Section 6.3, "Creating Tables with a Table Creator"). You can also configure TopLink to do this automatically (see Section 6.4, "Creating Database Tables Automatically").

Because the schema manager uses Java types rather than database types, it is database-independent. However, because it does not account for database-specific optimizations, it is best-suited for development purposes rather than production. For more information on how the schema manager maps Java types to database types, see Section 6.1.1, "How to Use Schema Manager Java and Database Type Conversion".

Although the schema manager can handle the sequencing configuration that you specify in your TopLink project, if you are using sequencing with non-Oracle databases, there are some sequencing restrictions you should be aware of (see Section 6.1.2, "How to Use Sequencing").

6.1.1 How to Use Schema Manager Java and Database Type Conversion

Table 6-1 lists the Java type to database type conversions that the schema manager supports depending on the database platform your TopLink project uses. This list is specific to the schema manager and does not apply to mappings. TopLink automatically performs conversions between any database types within mappings.

Table 6-1 Java and Database Field Type Conversion

Java Type	Oracle	DB2	Sybase	MySQL	MS Access
`java.lang.Boolean`	NUMBER	SMALLINT	BIT default 0	TINYINT(1)	SHORT
`java.lang.Byte`	NUMBER	SMALLINT	SMALLINT	TINYINT	SHORT
`java.lang.Byte[]`	LONG RAW	BLOB	IMAGE	BLOB	LONGBINARY
`java.lang.Character`	CHAR	CHAR	CHAR	CHAR	TEXT
`java.lang.Character[]`	LONG	CLOB	TEXT	TEXT	LONGTEXT
`java.lang.Double`	NUMBER	FLOAT	FLOAT(32)	DOUBLE	DOUBLE
`java.lang.Float`	NUMBER	FLOAT	FLOAT(16)	FLOAT	DOUBLE
`java.lang.Integer`	NUMBER	INTEGER	INTEGER	INTEGER	LONG
`java.lang.Long`	NUMBER	INTEGER	NUMERIC	BIGINT	DOUBLE
`java.lang.Short`	NUMBER	SMALLINT	SMALLINT	SMALLINT	SHORT
`java.lang.String`	VARCHAR2	VARCHAR	VARCHAR	VARCHAR	TEXT
`java.math.BigDecimal`	NUMBER	DECIMAL	NUMERIC	DECIMAL	DOUBLE
`java.math.BigInteger`	NUMBER	DECIMAL	NUMERIC	BIGINT	DOUBLE
`java.sql.Date`	DATE	DATE	DATETIME	DATE	DATETIME
`java.sql.Time`	DATE	TIME	DATETIME	TIME	DATETIME
`java.sql.Timestamp`	DATE	TIMESTAMP	DATETIME	DATETIME	DATETIME

For more information about database platforms that TopLink supports, see Section 96.1.3.1, "Database Platforms".

6.1.2 How to Use Sequencing

If you generate a TableCreator class using TopLink Workbench (see Section 6.2.1, "How to Use TopLink Workbench During Development"), or DefaultTableGenerator (see Section 6.2.2, "How to Use the Default Table Generator at Run Time"), then sequencing configuration is included in your TableCreator according to your TopLink project configuration. In this case, when you use TableCreator method createTables, it does the following:

Creates the sequence table as defined in the session DatabaseLogin.
Creates or inserts sequences for each sequence name for all registered descriptors in the session.
Creates the Oracle sequence object if you use Oracle native sequencing.

You can use advanced API to handle special cases like Sybase or Microsoft SQL Server native sequencing (see Section 6.2.3, "How to Use Java to Create a Table Creator").

For more information about sequencing, see Section 18.2, "Sequencing in Relational Projects".

6.2 Creating a Table Creator

You can automatically generate a TableCreator using the following:

Oracle JDeveloper (during development).
TopLink Workbench (during development) (see Section 6.2.1, "How to Use TopLink Workbench During Development").
DefaultTableGenerator (at run time) (see Section 6.2.2, "How to Use the Default Table Generator at Run Time").

After creating a TableCreator class, you can use its API to create and drop tables (see Section 6.3, "Creating Tables with a Table Creator").

6.2.1 How to Use TopLink Workbench During Development

To create a TableCreator class that you can use in a Java application to recreate a database schema using the SchemaManager, use this procedure:

Right-click the project in the Navigator and choose Export > Table Creator Java Source from the context menu. The Table Creator dialog box appears.

You can also select the table and choose Selected > Export > Table Creator Java Source from the menu.
Enter a name for the table creator class and click OK. The Save As dialog box appears.
Choose a location for your table creator class and click OK. TopLink Workbench exports the table creator Java class to the location you specify.

6.2.2 How to Use the Default Table Generator at Run Time

To create a TableCreator class in Java using the DefaultTableGenerator, use this procedure:

Create an instance of DefaultTableGenerator, passing in an instance of your TopLink project:
```
DefaultTableGenerator myDefTblGen = new DefaultTableGenerator(toplinkProject);
```
Create a TableCreator instance:
- If you want a TableCreator that can support any session, use:
```
TableCreator myTblCre = myDefTblGen.generateDefaultTableCreator();
```
- If you want a TableCreator customized for a specific TopLink session, use:
```
TableCreator myTblCre = myDefTblGen.generateFilteredDefaultTableCreator(toplinkSession);
```

You can also configure TopLink to use the DefaultTableGenerator to automatically generate and execute a TableCreator at run time (see Section 6.4, "Creating Database Tables Automatically").

6.2.3 How to Use Java to Create a Table Creator

This section describes how to create a TableCreator class in Java, including the following:

Creating a TableCreator Class
Creating a TableDefinition Class
Adding Fields to a TableDefinition
Defining Sybase and Microsoft SQL Server Native Sequencing

6.2.3.1 Creating a TableCreator Class

To create your own TableCreator instance, you should extend TableCreator, as Example 6-1 shows:

Example 6-1 Creating a TableCreator Class

public class MyTableCreator extends oracle.toplink.tools.schemaframework.TableCreator {

    public M7TableCreator() {
        setName("MyTableCreator");
        addTableDefinition(buildADDRESSTable());
    ...
    }

    public TableDefinition buildADDRESSTable() {
        TableDefinition table = new TableDefinition();
       ...
       return table;
    }
...
}

6.2.3.2 Creating a TableDefinition Class

The TableDefinition class includes all the information required to create a new table, including the names and properties of a table and all its fields.

The TableDefinition class has the following methods:

setName
addField
addPrimaryKeyField
addIdentityField
addForeignKeyConstraint

All table definitions must call the setName method to set the name of the table that is described by the TableDefinition.

6.2.3.3 Adding Fields to a TableDefinition

Use the addField method to add fields to the TableDefinition. To add the primary key field to the table, use the addPrimaryKeyField method rather than the addField method.

To maintain compatibility among different databases, the type parameter requires a Java class rather than a database field type. TopLink translates the Java class to the appropriate database field type at run time. For example, the String class translates to the CHAR type for dBase databases. However, if you are connecting to Sybase, the String class translates to VARCHAR. For more information, see Section 6.1.1, "How to Use Schema Manager Java and Database Type Conversion".

The addField method can also be called with the fieldSize or fieldSubSize parameters for column types that require size and subsize to be specified.

Some databases require a subsize, but others do not. TopLink automatically provides the required information, as necessary.

6.2.3.4 Defining Sybase and Microsoft SQL Server Native Sequencing

Use FieldDefinition method addIdentityField to add fields representing a generated sequence number from Sybase or Microsoft SQL Server native sequencing. See Section 18.2.2.6, "Native Sequencing with a Non-Oracle Database Platform" for detailed information on using sequencing.

6.3 Creating Tables with a Table Creator

After creating a TableCreator class (see Section 6.2, "Creating a Table Creator"), you can use its API to create and drop tables. The important TableCreator methods are the following (each method takes an instance of DatabaseSession):

createTables–this method creates tables, adds constraints, and creates sequence tables and sequences (if sequence tables already exist, this method drops them and recreates them).
dropTables–his method drops all constraints and drops all tables (except sequence tables) that the TableCreator defines.
createConstraints–this method creates constraints on all pre-existing tables that the TableCreator defines.
dropConstraints–this method drops constraints on all pre-existing tables that the TableCreator defines.
replaceTables–this method drops and then creates all tables that the TableCreator defines.

6.4 Creating Database Tables Automatically

You can configure TopLink to create database tables automatically in JPA and EJB CMP projects:

Creating Database Tables Automatically in JPA Projects
Creating Database Tables Automatically in EJB CMP Projects

6.4.1 Creating Database Tables Automatically in JPA Projects

Using EclipseLink JPA persistence unit properties that you can define in a persistence.xml file, you can configure schema generation

For more information, see "Using EclipseLink JPA Extensions for Schema Generation" section of EclipseLink Developer's Guide at http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Extensions_for_Schema_Generation

6.4.2 Creating Database Tables Automatically in EJB CMP Projects

If you deploy a CMP project to OC4J configured to use TopLink as the persistence manager, then you can configure OC4J to automatically create (and, optionally, delete) database tables for your persistent objects.

You can configure automatic database table creation at one of three levels, as Table 6-2 shows. You can override the system level configuration at the application level, and you can override system and application configuration at the EJB module level.

Table 6-2 Configuring Automatic Table Generation

Level	Configuration File	Setting	Values
System (global)	`<OC4J_HOME>/config/application.xml`	`autocreate-tables`	`True` or `False`^Foot 1
		`autodelete-tables`	`True` or `False`^Footref 1
Application (EAR)	`orion-application.xml`	`autocreate-tables`	`True` or `False`^Footref 1
		`autodelete-tables`	`True` or `False`^Footref 1
EJB Module (JAR)	`orion-ejb-jar.xml`	`pm-properties` sub-element `default-mapping` attribute `db-table-gen`^Foot 2	`Create`, `DropAndCreate`, or `UseExisting`^Foot 3

^Footnote 1Default.

^Footnote 2For more information, see Section 9.9.1.3, "Configuring default-mapping Properties".

^Footnote 3See Table 6-3.

Note:

In case of default mapping, the default value for autocreate-tables setting is true.

If you configure automatic table generation at the EJB module level, the value you assign to the db-table-gen attribute corresponds to the autocreate-tables and autodelete-tables settings, as Table 6-3 shows.

Table 6-3 Equivalent Settings for db-table-gen

db-table-gen Setting	autocreate-tables Setting	autodelete-tables Setting
`Create`	`True`	`False`
`DropAndCreate`	`True`	`True`
`UseExisting`	`False`	NA

You can use this feature in conjunction with default mapping (see Section 17.2.3.4, "Default Mapping in EJB 2.n CMP Projects Using OC4J at Run Time").