How To create and update Databases using SchemaManager and SchemaUpdateSnippets
Preliminary
The SchemaManager and SchemaEngine give you an abstracted access to the database schema of your application, allowing you to add and delete tables, columns, relations and data in the database on application updates. It uses a designated table to keep track of the current schema status, which it will check whenever it is run, and update the database if it is not at the correct version.
SchemaManager (org.clazzes.jdbc2xml.schema.SchemaManager) will create a table called SCHEMA_HISTORY if it can not find it the first time it is run. This table contains the following columns:
DESCRIPTION:varchar(512), nullable
CREATION_DATE:date, nullable
SERIALNR:integer(5), not null
Project Configuration
To function correctly, SchemaManager needs a DataSource (javax.sql.DataSource) and a list of TableInfo (org.clazzes.jdbc2xml.schema.TableInfo) Objects, from which a database will be created if it finds an empty database. To function properly, it also needs an implementation of ISchemaEngine (org.clazzes.jdbc2xml.schema.ISchemaEngine).
Optionally, you may set the base version (default value 0.1.00) and base description String (default "initial database schema").
Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
To perform the operations, call SchemaManager.start().
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):
</bp:reference>
<bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
</bp:reference>
<bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
<bp:argument> <!-- Pass JDBC URL as an argument -->
</bp:argument>
</bp:bean>
<bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
<bp:property name="dialect" ref="sqlDialect">
</bp:property>
</bp:bean>
<bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
<bp:property name="dataSource" ref="dataSource"></bp:property>
<bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
<bp:property name="baseVersion" value="0.1.00" />
<bp:property name="baseTables">
<!-- Add List of TableDefinitions here (see below) -->
</bp:property>
<bp:property name="upateSnippets">
<!-- Add Update-Snippets here -->
</bp:property>
</bp:bean>
By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry lookup for Spring.
Setting up an initial database schema
To create an initial database schema, you will need to provide SchemaManager with a list of TableInfo objects. The recommended way to do this is to provide a class in your project which creates this list in it's constructor and provides it through a getter. You can instantiate this class in your Spring/Blueprint config as a singleton, and feed the provided List to SchemaManager. An example of this class could look like this:
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
import org.clazzes.jdbc2xml.schema.ColumnInfo;
import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
import org.clazzes.jdbc2xml.schema.TableInfo;
public class TableDefinitions {
// It is adviseable to provide the Strings used as names for tables and columns as constants, so they can be reused outside this object to build sql-statements
public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
public static final String COL_EXAMPLE_ID = "ID";
public static final String COL_EXAMPLE_NAME = "NAME";
public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
// ...
private List<TableInfo> setup;
public TableDefinitions() {
// Create a table
TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
exampleTable.setColumns(
Arrays.asList(new ColumnInfo[] {
new ColumnInfo(COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
new ColumnInfo(COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
new ColumnInfo(COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
}));
// Example for creating a foreign key reference
exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
}));
// Example for creating a primary key
exampleTable.setPrimaryKey(
new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
);
// ...
this.setup = Arrays.asList(
exampleTable,
// ...
);
}
public List<TableInfo> getSetup() {
return this.setup;
}
}
You must inject TableDefinitions.getSetup() 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:
<bp:property name="baseTables">
<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
</bp:property>
<!-- ... and continues here -->
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:
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import org.clazzes.jdbc2xml.schema.ColumnInfo;
import org.clazzes.jdbc2xml.schema.ISchemaEngine;
import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
import org.clazzes.jdbc2xml.schema.TableInfo;
public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
// This is only accessed through the getter
private static final String TARGET_VERSION = "0.1.01";
// Here it is also adviseable to define constants for reuse in statements.
public static final String COL_EXAMPLE_GENDER = "GENDER";
@Override
public String getTargetVersion() {
return TARGET_VERSION;
}
@Override
public String getUpdateComment() {
return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
}
@Override
public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
}
}
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 example table created by the TableDefinitions class above.
To add an entire table you would use the ISchemaEngine.createTable() method, like this:
public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
TableInfo tiGroup = new TableInfo(TB_GROUP);
tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
}));
tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
schemaEngine.createTable(tiGroup, true);
}
Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
ps.setNull(1, Types.VARCHAR);
ps.execute();
}
To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
<bp:property name="upateSnippets">
<bp:map>
<bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
<!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
</bp:map>
</bp:property>
<!-- ... and continues here -->