Skip to Content
Version 6.1 by christoph_lechleitner@iteg_at on 2013-01-31 07.04:57
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 6.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
christoph_lechleitner@iteg_at 6.1 9 |=(((
10
11 )))|=(((
12
13 )))
14 |(((
15 (% class="code" %)
16 (((
17 VERSION
18 )))
19 )))|(((
20 (% class="code" %)
21 (((
22 varchar(10), not null, primary key
23 )))
24 )))
25 |(((
26 (% class="code" %)
27 (((
28 DESCRIPTION
29 )))
30 )))|(((
31 (% class="code" %)
32 (((
33 varchar(512), nullable
34 )))
35 )))
36 |(((
37 (% class="code" %)
38 (((
39 CREATION_DATE
40 )))
41 )))|(((
42 (% class="code" %)
43 (((
44 date, nullable
45 )))
46 )))
47 |(((
48 (% class="code" %)
49 (((
50 SERIALNR
51 )))
52 )))|(((
53 (% class="code" %)
54 (((
55 integer(5), not null
56 )))
57 )))
5fbc055b7cc103006957e1ae 1.1 58
christoph_lechleitner@iteg_at 6.1 59 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
christoph_lechleitner@iteg_at 4.1 60
christoph_lechleitner@iteg_at 6.1 61 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)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.
62
christoph_lechleitner@iteg_at 4.1 63 (% 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;" %).
64
5fbc055b7cc103006957e1ae 2.1 65 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
5fbc055b7cc103006957e1ae 1.1 66
christoph_lechleitner@iteg_at 4.1 67 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 68
christoph_lechleitner@iteg_at 4.1 69 Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
5fbc055b7cc103006957e1ae 1.1 70
5fbc055b7cc103006957e1ae 2.1 71 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
5fbc055b7cc103006957e1ae 1.1 72
5fbc055b7cc103006957e1ae 2.1 73 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
74
christoph_lechleitner@iteg_at 4.1 75 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
5fbc055b7cc103006957e1ae 2.1 76
77 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):
78
79 {{code language="html/xml"}}
5fbc055b7cc103006957e1ae 1.1 80 <bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
81 </bp:reference>
82  
83 <bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
84 </bp:reference>
85  
86 <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
christoph_lechleitner@iteg_at 4.1 87 <bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
5fbc055b7cc103006957e1ae 1.1 88 </bp:argument>
89 </bp:bean>
90  
91 <bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
92 <bp:property name="dialect" ref="sqlDialect">
93 </bp:property>
94 </bp:bean>
95  
5fbc055b7cc103006957e1ae 2.1 96 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
5fbc055b7cc103006957e1ae 1.1 97 <bp:property name="dataSource" ref="dataSource"></bp:property>
98 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
christoph_lechleitner@iteg_at 4.1 99 <!-- optional but recommended: special name for schema history table: -->
100 <bp:property name="versionHistoryTable" value="MYLIB_SCHEMA_HISTORY"/>
101 <!-- optional but recommended: explicit first version -->
5fbc055b7cc103006957e1ae 1.1 102 <bp:property name="baseVersion" value="0.1.00" />
103 <bp:property name="baseTables">
christoph_lechleitner@iteg_at 4.1 104 <!-- List of TableDefinitions here (see below), typical: -->
105 <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
5fbc055b7cc103006957e1ae 1.1 106 </bp:property>
christoph_lechleitner@iteg_at 5.1 107 <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
5fbc055b7cc103006957e1ae 1.1 108 <bp:property name="upateSnippets">
christoph_lechleitner@iteg_at 5.1 109 <bp:map>
110 <bp:entry key="0.1.00" value="foo.schema.SchemaUpdate_0_1_01"></bp:entry>
111 <bp:entry key="0.1.01" value="foo.schema.SchemaUpdate_0_2_00"></bp:entry>
112 </bp:map>
5fbc055b7cc103006957e1ae 1.1 113 </bp:property>
christoph_lechleitner@iteg_at 5.1 114 -->
5fbc055b7cc103006957e1ae 1.1 115 </bp:bean>
116 {{/code}}
117
christoph_lechleitner@iteg_at 4.1 118 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
5fbc055b7cc103006957e1ae 2.1 119
120 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
121
christoph_lechleitner@iteg_at 4.1 122 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
5fbc055b7cc103006957e1ae 2.1 123
christoph_lechleitner@iteg_at 6.1 124 The recommended strategy is to create an InitialSchema class providing this list through a getter.
christoph_lechleitner@iteg_at 4.1 125
christoph_lechleitner@iteg_at 6.1 126 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
christoph_lechleitner@iteg_at 4.1 127
5fbc055b7cc103006957e1ae 2.1 128 {{code language="java"}}
129 package org.clazzes.example.jdbc2xml;
130
131 import java.sql.Types;
132 import java.util.Arrays;
133 import java.util.List;
134
135 import org.clazzes.jdbc2xml.schema.ColumnInfo;
136 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
137 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
138 import org.clazzes.jdbc2xml.schema.TableInfo;
139  
christoph_lechleitner@iteg_at 6.1 140 public class InitialSchema {
5fbc055b7cc103006957e1ae 2.1 141
142 private List<TableInfo> setup;
143
144 public TableDefinitions() {
5fbc055b7cc103006957e1ae 3.1 145 // Create a table
christoph_lechleitner@iteg_at 6.1 146 TableInfo exampleTable = new TableInfo(TableDefs.TB_EXAMPLE_TABLE_NAME);
5fbc055b7cc103006957e1ae 2.1 147 exampleTable.setColumns(
148 Arrays.asList(new ColumnInfo[] {
christoph_lechleitner@iteg_at 6.1 149 new ColumnInfo(TableDefs.COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
150 new ColumnInfo(TableDefs.COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
151 new ColumnInfo(TableDefs.COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
152 new ColumnInfo(TableDefs.COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
5fbc055b7cc103006957e1ae 2.1 153 }));
154  
5fbc055b7cc103006957e1ae 3.1 155 // Example for creating a foreign key reference
5fbc055b7cc103006957e1ae 2.1 156 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
157 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
158 }));
159  
5fbc055b7cc103006957e1ae 3.1 160 // Example for creating a primary key
5fbc055b7cc103006957e1ae 2.1 161 exampleTable.setPrimaryKey(
162 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
163 );
164
5fbc055b7cc103006957e1ae 3.1 165 // ...
5fbc055b7cc103006957e1ae 2.1 166  
167 this.setup = Arrays.asList(
168 exampleTable,
5fbc055b7cc103006957e1ae 3.1 169 // ...
5fbc055b7cc103006957e1ae 2.1 170 );
171
172 }
173  
christoph_lechleitner@iteg_at 6.1 174 public List<TableInfo> getSchema() {
175 return this.schema;
5fbc055b7cc103006957e1ae 2.1 176 }
177
178 }
179 {{/code}}
180
christoph_lechleitner@iteg_at 6.1 181 {{code language="java"}}
182 package org.clazzes.example.jdbc2xml;
183
184 import java.sql.Types;
185 import java.util.Arrays;
186 import java.util.List;
187
188 import org.clazzes.jdbc2xml.schema.ColumnInfo;
189 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
190 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
191 import org.clazzes.jdbc2xml.schema.TableInfo;
192
193 public class TableDefs {
194
195 // It is adviseable to provide the Strings used as names for tables and columns as constants,
196 // so they can be reused savely to construct SQL statements
197 public static final String TABLENAME_ADDRESSBOOK = "ADDRESSBOOK";
198 public static final String COL_ADDRESSBOOK_ID = "ID";
199 public static final String COL_ADDRESSBOOK_NAME = "NAME";
200 public static final String COL_ADDRESSBOOK_ADDRESS_REF = "ADDRESS";
201 public static final String COL_ADDRESSBOOK_BIRTHDAY = "BIRTHDAY";
202
203 }
204 {{/code}}
205
206
207
208
209
5fbc055b7cc103006957e1ae 2.1 210 You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
211
5fbc055b7cc103006957e1ae 3.1 212 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
5fbc055b7cc103006957e1ae 2.1 213
5fbc055b7cc103006957e1ae 3.1 214 {{code language="html/xml"}}
215 <!-- SchemaManager bean definition starts here ... -->
216 <bp:property name="baseTables">
217 <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
218 </bp:property>
219 <!-- ... and continues here -->
5fbc055b7cc103006957e1ae 2.1 220 {{/code}}
221
5fbc055b7cc103006957e1ae 3.1 222 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
5fbc055b7cc103006957e1ae 2.1 223
224 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.
225
5fbc055b7cc103006957e1ae 3.1 226 An example for an implementation of a schema update snippet could look like this:
5fbc055b7cc103006957e1ae 2.1 227
5fbc055b7cc103006957e1ae 3.1 228 {{code language="java"}}
229 package org.clazzes.example.jdbc2xml.updates;
5fbc055b7cc103006957e1ae 2.1 230
5fbc055b7cc103006957e1ae 3.1 231 import java.sql.SQLException;
232 import java.sql.Types;
233 import java.util.Arrays;
234 import org.clazzes.jdbc2xml.schema.ColumnInfo;
235 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
236 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
237 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
238 import org.clazzes.jdbc2xml.schema.TableInfo;
239
240 public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
241  
242 // This is only accessed through the getter
243 private static final String TARGET_VERSION = "0.1.01";
244  
245 // Here it is also adviseable to define constants for reuse in statements.
246 public static final String COL_EXAMPLE_GENDER = "GENDER";
247
248
249 @Override
250 public String getTargetVersion() {
251 return TARGET_VERSION;
252 }
253
254 @Override
255 public String getUpdateComment() {
256 return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
257 }
258
259 @Override
260 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
261 TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
262 schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
263 }
264 }
265
266
267 {{/code}}
268
269 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.
270
271 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
272
273 {{code language="java"}}
274 @Override
275 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
276 TableInfo tiGroup = new TableInfo(TB_GROUP);
277 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
278 new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
279 new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
280 new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
281 }));
282 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
283 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
284
285 schemaEngine.createTable(tiGroup, true);
286 }
287 {{/code}}
288
289 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
290
291 {{code language="java"}}
292 @Override
293 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
294 String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
295
296 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
297
298 ps.setNull(1, Types.VARCHAR);
299
300 ps.execute();
301 }
302 {{/code}}
303
304 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
305
306 {{code language="html/xml"}}
307 <!-- SchemaManager bean definition starts here ... -->
308 <bp:property name="upateSnippets">
309 <bp:map>
310 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
311 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
312 </bp:map>
313 </bp:property>
314 <!-- ... and continues here -->
315 {{/code}}
316
christoph_lechleitner@iteg_at 6.1 317 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
5fbc055b7cc103006957e1ae 2.1 318
christoph_lechleitner@iteg_at 6.1 319 The JDBC2XML Schema management tools allow for 2 different strategies:
5fbc055b7cc103006957e1ae 2.1 320
christoph_lechleitner@iteg_at 6.1 321 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Frozen(Initial)TableList"/}}Frozen (% style="font-size: 14.0pt;" %)(Initial) Table List(%%) ===
322
323 The legacy strategy is:
324
325 (% style="list-style-type: square;" %)
326 * At the start of a project, create and use TableDefinitions.java
327 * After the first commit, TableDefinitions are considered frozen, all changes go into SchemaUpdates, up to one update per source code commit
328
329 Advantage: Rock solid.
330
331 Disadvantage: No place to look for the complete current scheme, except actual databases. See //Recommendation 1// below for a workaround.
332
333 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Possiblebutdangerous:Evolving(Initial)TableList"/}}Possible but dangerous: Evolving (Initial) Table List ===
334
335 To keep the TableDefinitions up do date, one might use this strategy:
336
337 (% style="list-style-type: square;" %)
338 * keep the TableDefinitions up to date, so an empty database always gets the current scheme in one shot
339 * SchemaUpdates are only applied to existing databases
340
341 Advantage: Immediate overview over current scheme.
342
343 Disadvantage: Very real danger of messing something up, because
344
345 (% style="list-style-type: square;" %)
346 * schema updates have to be coded in 2 different places in 2 different ways
347 * the bean definition has to be maintained in 2 places but just 1
348
349 Conclusion: DO NOT DO THIS. This strategy may be ok in very early stages, but at some point it has to be
350
351 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Recommendation1:SplitTableDefinitionsfrominitialTableList"/}}Recommendation 1: Split TableDefinitions from initial Table List ===
352
353 If TableDefinitions (pseudo constants for table and column names) go in a dedicated pseudo class it can serve as good overview over the database structure.
354
355 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Recommendation2:FreezeTableDefinition,notafterRC/1.0/GA"/}}Recommendation 2: (% style="font-size: 14.0pt;" %)Freeze Table Definition, not after RC/1.0/GA(%%) ===
356
357 It may be ok to start a new project using a fast changing (% style="font-size: 10.0pt;line-height: 13.0pt;" %)(Initial) Table List.
358
359 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)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!
360
5fbc055b7cc103006957e1ae 2.1 361
362
363
364
365
366
367