How To create and update Databases using SchemaManager and SchemaUpdateSnippets
Preliminary
The SchemaManager (org.clazzes.jdbc2xml.schema.SchemaManager
) and SchemaEngine provide means to maintain the database scheme of an application, allowing you to add and delete tables, columns, relations and data in the database along application updates.
Schema History Table
A designated schema table, by default named SCHEMA_HISTORY, is used to keep track of the current scheme. It will be automatically at SchemaManager's first run. SCHEMA_HISTORY contains the following columns:
VERSION | varchar(10), not null, primary key |
DESCRIPTION | varchar(512), nullable |
CREATION_DATE | date, nullable |
SERIALNR | integer(5), not null |
Custom name for Schema History Table
In heterogenous environments as well as in heavily modularized software architectures a single database may be shared by multiple parties each requiring a couple of tables.
To allow multiple modules (applications, libraries, other OSGi bundles) to use JDBC2XML's SchemaManager concurrently within one database, as of JDBC 1.1.1 SchemaManager hold the name of the schema history table in an overwritable property, versionHistoryTable
. See JDBCTOXML-11.
Project Configuration
SchemaManager requires a DataSource (javax.sql.DataSource
) and a list of TableInfo (org.clazzes.jdbc2xml.schema.TableInfo
) Objects, from which database structures will be created if an "empty" database is detected. Furthermore, an implementation of ISchemaEngine (org.clazzes.jdbc2xml.schema.ISchemaEngine
) is required.
Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
To perform the operations, call SchemaManager.start()
.
Configuration using Spring or OSGi/Blueprint
If you are using OSGi with Blueprint or Spring to set up your project, you can configure a SchemaManager instance by adding the following to your blueprint services.xml
(or Spring configuration file):
By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
Setting up an initial database schema
Inital Schema (Initial Table List)
To create an initial database schema, SchemaManager needs a list of TableInfo objects.
The recommended strategy is to create an InitialSchema class providing this list through a getter.
This is an example:
TableDefs, a central place for table and column names
You may have notice the usage of TableDefs.*
members.
Table and column names should never be re-typed everywhere as literals, it is highly recommended to use constants.
Putting these constants in a dedicated class, say TableDef
, allows to use this as an easily accessible list of all tables and columns in the database.
This is an example:
Triggering the creation of the initial schema
To trigger the creation of the initial schema when coming across an empty database, InitialSchema.getSchema()
has to be injected into SchemaManager.setBaseTables()
before calling SchemaManager.start()
.
Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
Updating a database schema with ISchemaUpdateSnippet
To update the database or it's content with schema updates, you must create a new implementation of ISchemaUpdateSnippet (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet
) for each consecutive update. SchemaManager takes a Map<String, Class<? extends ISchemaUpdateSnippet>>
which contains the update classes keyed by the originating (e.g. previous) version.
An example for an implementation of a schema update snippet could look like this:
The return values of ISchemaUpdateSnippet.getTargetVersion()
and ISchemaUpdateSnippet.getUpdateComment()
are written to the SCHEMA_HISTORY
table. The update itself is performed in ISchemaUpdateSnippet.performUpdate()
. In the above example, it adds a column called GENDER
to the ADDRESSBOOK table created via the InitialSchema class above.
To add an entire table you would use the ISchemaEngine.createTable()
method, like this:
Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
Schema Maintainance Strategies
The JDBC2XML Schema management tools allow for 2 different strategies:
Frozen (Initial) Table List
The legacy strategy is:
- At the start of a project, create and use
InitalSchema.java
- After the first commit,
InitalSchema
are considered frozen, all changes go intoSchemaUpdates
, up to one update per source code commit
Advantage: Rock solid.
Disadvantage: No place to look for the complete and exact current scheme, except actual databases. TableDefs.java
provide some information, but may become confusing in the long term.
Possible but dangerous: Evolving (Initial) Table List
To keep the (Initial) Schema up do date, one might use this strategy:
- keep the
InitalSchema
up to date, so an empty database always gets the current scheme in one shot SchemaUpdates
are only applied to existing databases
Advantage: Immediate overview over exact current scheme.
Disadvantage: Very real danger of messing something up, because
- schema updates have to be coded in 2 different places in 2 different ways
- the bean definition has to be maintained in 2 places but just 1
Conclusion: DO NOT DO THIS. This strategy may be ok in very early stages, but at some point it has to be
Recommendation: Freeze Initial Table Definition not later than the first Release Candidate (RC)
It may be ok to start a new project using a fast changing (Initial) Table List.
But, please, freeze it at some point. Once the first test server is setup up, internally or at a friendly customer, the Frozen Initial Table List Strategy is the only valid one!
Real world Example
This HowTo is currently evolving while an additional developer gets acostumed to the SchemaEngine, for developing SDS' org.clazzes.sds.impl.schema package which is work in progress!