Skip to Content
Version 6.1 by christoph_lechleitner@iteg_at on 2013-01-31 07.04:57
Show last authors
1 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
2
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.
4
5 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ===
6
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
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 )))
58
59 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
60
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
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
65 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
66
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.
68
69 Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
70
71 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
72
73 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
74
75 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
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"}}
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">
87 <bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
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  
96 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
97 <bp:property name="dataSource" ref="dataSource"></bp:property>
98 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
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 -->
102 <bp:property name="baseVersion" value="0.1.00" />
103 <bp:property name="baseTables">
104 <!-- List of TableDefinitions here (see below), typical: -->
105 <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
106 </bp:property>
107 <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
108 <bp:property name="upateSnippets">
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>
113 </bp:property>
114 -->
115 </bp:bean>
116 {{/code}}
117
118 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
119
120 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
121
122 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
123
124 The recommended strategy is to create an InitialSchema class providing this list through a getter.
125
126 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
127
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  
140 public class InitialSchema {
141
142 private List<TableInfo> setup;
143
144 public TableDefinitions() {
145 // Create a table
146 TableInfo exampleTable = new TableInfo(TableDefs.TB_EXAMPLE_TABLE_NAME);
147 exampleTable.setColumns(
148 Arrays.asList(new ColumnInfo[] {
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)
153 }));
154  
155 // Example for creating a foreign key reference
156 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
157 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
158 }));
159  
160 // Example for creating a primary key
161 exampleTable.setPrimaryKey(
162 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
163 );
164
165 // ...
166  
167 this.setup = Arrays.asList(
168 exampleTable,
169 // ...
170 );
171
172 }
173  
174 public List<TableInfo> getSchema() {
175 return this.schema;
176 }
177
178 }
179 {{/code}}
180
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
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
212 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
213
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 -->
220 {{/code}}
221
222 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
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
226 An example for an implementation of a schema update snippet could look like this:
227
228 {{code language="java"}}
229 package org.clazzes.example.jdbc2xml.updates;
230
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
317 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
318
319 The JDBC2XML Schema management tools allow for 2 different strategies:
320
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
361
362
363
364
365
366
367