Latest revision as of 16:25, 31 January 2012

DDL Extend Table Support

Enhancement request: bug 368365

Summary

This feature is to allow creating database tables and modify any that already exist so they match the object model. First phase will only allow adding fields to the table, keeping field name changes or removing fields for a later feature.

Why use this feature?

1. Testing while developing using existing test data. This feature avoids the need to use drop and create when the schema changes when new fields are added to the model, reducing the need to repopulate test data.

1. This feature could also be used with Extensibility to add columns, for instance, within a PAAS Manager

How to use this feature

Adding persistence properties

            <property name="eclipselink.ddl-generation" value="create-or-extend-tables" />
            <property name="eclipselink.ddl-generation.output-mode" value="database" />

to the persistence.xml. When the context is loaded, EclipseLink will query the database for each table required in the persistence unit and use the results to determine if the table needs to be created or extended.

Requirements

• Must search for each table individually, and create it or add new fields as neccessary
• Must follow the default behavior of your specific database and JDBC driver combination if issues are encountered, then continue with the next statement
• Must allow configuration through JPA DDL generation properties
• Must allow access to native api to allow adding fields to tables manually

Design

Specifying eclipselink.ddl-generation value of PersistenceUnitProperties.CREATE_OR_EXTEND will cause EclipseLink's DDL generation to use this feature. EntityManagerSetupImpl will process this type and call the appropriate SchemaManager api as it does now for values of DROP_AND_CREATE, CREATE_ONLY and NONE

org.eclipse.persistence.tools.schemaframework.SchemaManager will have

 public void extendDefaultTables(boolean generateFKConstraints) throws EclipseLinkException

that will cycle through all tables in the project and query the database for their column information. If column data doesn't exist, it will create the table. Otherwise it will look within the returned data for the fields needed, and issue alter table statements for each one that is missing using org.eclipse.persistence.tools.schemaframework.TableDefinition's

 public void addFieldOnDatabase(final AbstractSession session, FieldDefinition field)

to generate each field on a table.

SQL generated for each field will be of the form

 "ALTER TABLE <qualifier>.<TableName> ADD (<FIELD_NAME> <TYPE>)"
 

The field name and type will be writen out the same as they would be when the table is created. For example, a simple String on Oracle:

 ALTER TABLE MYENTITY ADD (FIELD_NAME VARCHAR2(255) NULL )

Limitations

• Must have a connection to the database
• Requires ddl-generation.output-mode of "database". It will not write out to a file.
• Can only be used outside of a JTA transaction.
• All required Foreign key constraints are added afterward table processing regardless of some pre-existing
• Indexes and primary keys are only added on initial table creation (see discussions). They are not added to or changed on existing tables.

Testing

Testing needs to run on all databases. A new persistence unit will be need to be added within the DDL generation test suite, and rely on extensibility to ensure that alter table statements are used when refreshing the context with a new mapping.

Open/Future items

• Catalogue and schema are stored in a single string internally, and so cannot be reliably used when querying for the table. This means table names must be unique on the database until this issue is resolved through bug 367887

• Bug 368394 - Allow configuring DDL generation to use a seperate datasource or the non-JTA datasource

• Rename columns
• Remove columns
• Change the type of existing columns
• Drop tables
• Adding new indexes, removing removed indexes