5 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 TopLink Workbench (see "Creating New Tables" and "Generating Tables on the Database").

This chapter includes information on the following topics:

• Understanding the Schema Manager

• Creating a Table Creator

• Creating Tables With a Table Creator

• Automatic Database Table Creation

Understanding the Schema Manager

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

Figure 5-1 SchemaManager Usage

Description of "Figure 5-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:

• TopLink Workbench during development (see "Using TopLink Workbench During Development")

• DefaultTableGenerator at run time (see "Using 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 "Creating Tables With a Table Creator"). You can also configure TopLink to do this automatically (see "Automatic Database Table Creation").

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 "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 "Sequencing").

Schema Manager Java and Database Type Conversion

Table 5-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 5-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 "Database Platforms".

Sequencing

If you generate a TableCreator class using TopLink Workbench (see "Using TopLink Workbench During Development") or DefaultTableGenerator (see "Using 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 "Using Java").

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

Creating a Table Creator

You can automatically generate a TableCreator using:

• TopLink Workbench during development (see "Using TopLink Workbench During Development")

• DefaultTableGenerator at run time (see "Using the Default Table Generator at Run Time")

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

Using 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:

1. 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.

2. Enter a name for the table creator class and click OK. The Save As dialog box appears.

3. Choose a location for your table creator class and click OK. TopLink Workbench exports the table creator Java class to the location you specify.

Using the Default Table Generator at Run Time

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

1. Create an instance of DefaultTableGenerator, passing in an instance of your TopLink project:

   DefaultTableGenerator myDefTblGen = new DefaultTableGenerator(toplinkProject);
   
   

2. 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 "Automatic Database Table Creation").

Using Java

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

Creating a TableCreator Class

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

Example 5-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;
    }
...
}

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.

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 "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.

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 "Native Sequencing With a Non-Oracle Database Platform" for detailed information on using sequencing.

Creating Tables With a Table Creator

After creating a TableCreator class (see "Creating a Table Creator"), you can use its API to create and drop tables. The important TableCreator methods are as follows (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.

Automatic Database Table Creation

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 5-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 5-2 Configuring Automatic Table Generation

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

^Footnote 1 Default.

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

^Footnote 3 See Table 5-3.

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 5-3 shows.

Table 5-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 "Default Mapping in CMP Projects Using OC4J at Run Time").