Skip to Content
Version 4.1 by christoph_lechleitner@iteg_at on 2013-01-31 04.25:51
Hide last authors
5fbc055b7cc103006957e1ae 2.1 1 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
5fbc055b7cc103006957e1ae 1.1 2
christoph_lechleitner@iteg_at 4.1 3 The SchemaManager ({{code language="none"}}org.clazzes.jdbc2xml.schema.SchemaManager{{/code}}) 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.
5fbc055b7cc103006957e1ae 1.1 4
christoph_lechleitner@iteg_at 4.1 5 ==== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ====
5fbc055b7cc103006957e1ae 1.1 6
christoph_lechleitner@iteg_at 4.1 7 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. (% style="font-size: 10.0pt;line-height: 13.0pt;" %)SCHEMA_HISTORY contains the following columns:
8
5fbc055b7cc103006957e1ae 1.1 9 {{code language="none"}}
10 VERSION:varchar(10), not null, primary key
11 DESCRIPTION:varchar(512), nullable
12 CREATION_DATE:date, nullable
13 SERIALNR:integer(5), not null
14 {{/code}}
15
christoph_lechleitner@iteg_at 4.1 16 In (% style="font-size: 10.0pt;font-weight: normal;line-height: 13.0pt;" %)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.
17
18 (% style="font-size: 10.0pt;font-weight: normal;line-height: 13.0pt;" %)To allow multiple modules (applications, libraries, other OSGi bundles) to use (% style="font-size: 10.0pt;line-height: 13.0pt;" %)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, {{code language="none"}}versionHistoryTable{{/code}}. See (%%)[[JDBCTOXML-11>>url:https://jira.clazzes.org/browse/JDBCTOXML-11||shape="rect"]](% style="font-size: 10.0pt;line-height: 13.0pt;" %).
19
5fbc055b7cc103006957e1ae 2.1 20 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
5fbc055b7cc103006957e1ae 1.1 21
christoph_lechleitner@iteg_at 4.1 22 SchemaManager requires a DataSource ({{code language="none"}}javax.sql.DataSource{{/code}}) and a list of TableInfo ({{code language="none"}}org.clazzes.jdbc2xml.schema.TableInfo{{/code}}) Objects, from which database structures will be created if an "empty" database is detected. Furthermore, an implementation of ISchemaEngine ({{code language="none"}}org.clazzes.jdbc2xml.schema.ISchemaEngine{{/code}}) is required.
5fbc055b7cc103006957e1ae 1.1 23
christoph_lechleitner@iteg_at 4.1 24 Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
5fbc055b7cc103006957e1ae 1.1 25
5fbc055b7cc103006957e1ae 2.1 26 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
5fbc055b7cc103006957e1ae 1.1 27
5fbc055b7cc103006957e1ae 2.1 28 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
29
christoph_lechleitner@iteg_at 4.1 30 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
5fbc055b7cc103006957e1ae 2.1 31
32 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 {{code language="none"}}services.xml{{/code}} (or Spring configuration file):
33
34 {{code language="html/xml"}}
5fbc055b7cc103006957e1ae 1.1 35 <bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
36 </bp:reference>
37  
38 <bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
39 </bp:reference>
40  
41 <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
christoph_lechleitner@iteg_at 4.1 42 <bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
5fbc055b7cc103006957e1ae 1.1 43 </bp:argument>
44 </bp:bean>
45  
46 <bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
47 <bp:property name="dialect" ref="sqlDialect">
48 </bp:property>
49 </bp:bean>
50  
5fbc055b7cc103006957e1ae 2.1 51 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
5fbc055b7cc103006957e1ae 1.1 52 <bp:property name="dataSource" ref="dataSource"></bp:property>
53 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
christoph_lechleitner@iteg_at 4.1 54 <!-- optional but recommended: special name for schema history table: -->
55 <bp:property name="versionHistoryTable" value="MYLIB_SCHEMA_HISTORY"/>
56 <!-- optional but recommended: explicit first version -->
5fbc055b7cc103006957e1ae 1.1 57 <bp:property name="baseVersion" value="0.1.00" />
58 <bp:property name="baseTables">
christoph_lechleitner@iteg_at 4.1 59 <!-- List of TableDefinitions here (see below), typical: -->
60 <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
5fbc055b7cc103006957e1ae 1.1 61 </bp:property>
62 <bp:property name="upateSnippets">
63 <!-- Add Update-Snippets here -->
christoph_lechleitner@iteg_at 4.1 64 <!-- Example for update from 0.1.00 to 0.1.01 -->
65 <!-- <bp:entry key="0.1.00" value="foo.schema.SchemaUpdate_0_1_01"></bp:entry> -->
5fbc055b7cc103006957e1ae 1.1 66 </bp:property>
67 </bp:bean>
68 {{/code}}
69
christoph_lechleitner@iteg_at 4.1 70 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
5fbc055b7cc103006957e1ae 2.1 71
72 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
73
christoph_lechleitner@iteg_at 4.1 74 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
5fbc055b7cc103006957e1ae 2.1 75
christoph_lechleitner@iteg_at 4.1 76 The recommended strategy is to implement a table definition class providing this list through a getter.
77
78 This is an example:
79
5fbc055b7cc103006957e1ae 2.1 80 {{code language="java"}}
81 package org.clazzes.example.jdbc2xml;
82
83 import java.sql.Types;
84 import java.util.Arrays;
85 import java.util.List;
86
87 import org.clazzes.jdbc2xml.schema.ColumnInfo;
88 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
89 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
90 import org.clazzes.jdbc2xml.schema.TableInfo;
91  
92 public class TableDefinitions {
93
5fbc055b7cc103006957e1ae 3.1 94 // 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
5fbc055b7cc103006957e1ae 2.1 95 public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
96 public static final String COL_EXAMPLE_ID = "ID";
97 public static final String COL_EXAMPLE_NAME = "NAME";
98 public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
99 public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
100  
5fbc055b7cc103006957e1ae 3.1 101 // ...
5fbc055b7cc103006957e1ae 2.1 102  
103 private List<TableInfo> setup;
104
105 public TableDefinitions() {
5fbc055b7cc103006957e1ae 3.1 106 // Create a table
5fbc055b7cc103006957e1ae 2.1 107 TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
108 exampleTable.setColumns(
109 Arrays.asList(new ColumnInfo[] {
110 new ColumnInfo(COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
111 new ColumnInfo(COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
112 new ColumnInfo(COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
113 new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
114 }));
115  
5fbc055b7cc103006957e1ae 3.1 116 // Example for creating a foreign key reference
5fbc055b7cc103006957e1ae 2.1 117 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
118 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
119 }));
120  
5fbc055b7cc103006957e1ae 3.1 121 // Example for creating a primary key
5fbc055b7cc103006957e1ae 2.1 122 exampleTable.setPrimaryKey(
123 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
124 );
125
5fbc055b7cc103006957e1ae 3.1 126 // ...
5fbc055b7cc103006957e1ae 2.1 127  
128 this.setup = Arrays.asList(
129 exampleTable,
5fbc055b7cc103006957e1ae 3.1 130 // ...
5fbc055b7cc103006957e1ae 2.1 131 );
132
133 }
134  
135 public List<TableInfo> getSetup() {
136 return this.setup;
137 }
138
139 }
140 {{/code}}
141
142 You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
143
5fbc055b7cc103006957e1ae 3.1 144 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
5fbc055b7cc103006957e1ae 2.1 145
5fbc055b7cc103006957e1ae 3.1 146 {{code language="html/xml"}}
147 <!-- SchemaManager bean definition starts here ... -->
148 <bp:property name="baseTables">
149 <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
150 </bp:property>
151 <!-- ... and continues here -->
5fbc055b7cc103006957e1ae 2.1 152 {{/code}}
153
5fbc055b7cc103006957e1ae 3.1 154 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
5fbc055b7cc103006957e1ae 2.1 155
156 To update the database or it's content with schema updates, you must create a new implementation of ISchemaUpdateSnippet ({{code language="none"}}org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet{{/code}}) for each consecutive update. SchemaManager takes a {{code language="none"}}Map<String, Class<? extends ISchemaUpdateSnippet>>{{/code}} which contains the update classes keyed by the originating (e.g. previous) version.
157
5fbc055b7cc103006957e1ae 3.1 158 An example for an implementation of a schema update snippet could look like this:
5fbc055b7cc103006957e1ae 2.1 159
5fbc055b7cc103006957e1ae 3.1 160 {{code language="java"}}
161 package org.clazzes.example.jdbc2xml.updates;
5fbc055b7cc103006957e1ae 2.1 162
5fbc055b7cc103006957e1ae 3.1 163 import java.sql.SQLException;
164 import java.sql.Types;
165 import java.util.Arrays;
166 import org.clazzes.jdbc2xml.schema.ColumnInfo;
167 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
168 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
169 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
170 import org.clazzes.jdbc2xml.schema.TableInfo;
171
172 public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
173  
174 // This is only accessed through the getter
175 private static final String TARGET_VERSION = "0.1.01";
176  
177 // Here it is also adviseable to define constants for reuse in statements.
178 public static final String COL_EXAMPLE_GENDER = "GENDER";
179
180
181 @Override
182 public String getTargetVersion() {
183 return TARGET_VERSION;
184 }
185
186 @Override
187 public String getUpdateComment() {
188 return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
189 }
190
191 @Override
192 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
193 TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
194 schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
195 }
196 }
197
198
199 {{/code}}
200
201 The return values of {{code language="none"}}ISchemaUpdateSnippet.getTargetVersion(){{/code}} and {{code language="none"}}ISchemaUpdateSnippet.getUpdateComment(){{/code}} are written to the {{code language="none"}}SCHEMA_HISTORY{{/code}} table. The update itself is performed in {{code language="none"}}ISchemaUpdateSnippet.performUpdate(){{/code}}. In the above example, it adds a column called {{code language="none"}}GENDER{{/code}} to the example table created by the TableDefinitions class above.
202
203 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
204
205 {{code language="java"}}
206 @Override
207 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
208 TableInfo tiGroup = new TableInfo(TB_GROUP);
209 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
210 new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
211 new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
212 new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
213 }));
214 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
215 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
216
217 schemaEngine.createTable(tiGroup, true);
218 }
219 {{/code}}
220
221 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
222
223 {{code language="java"}}
224 @Override
225 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
226 String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
227
228 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
229
230 ps.setNull(1, Types.VARCHAR);
231
232 ps.execute();
233 }
234 {{/code}}
235
236 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
237
238 {{code language="html/xml"}}
239 <!-- SchemaManager bean definition starts here ... -->
240 <bp:property name="upateSnippets">
241 <bp:map>
242 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
243 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
244 </bp:map>
245 </bp:property>
246 <!-- ... and continues here -->
247 {{/code}}
248
5fbc055b7cc103006957e1ae 2.1 249
250
251
252
253
254
255
256
257
258
259