Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
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  
97 <bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
98  
99 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
100 <bp:property name="dataSource" ref="dataSource"></bp:property>
101 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
102 <!-- optional but recommended: special name for schema history table: -->
103 <bp:property name="versionHistoryTable" value="MYLIB_SCHEMA_HISTORY"/>
104 <!-- optional but recommended: explicit first version -->
105 <bp:property name="baseVersion" value="0.1.00" />
106 <bp:property name="baseTables">
107 <!-- List of TableDefinitions here (see below), typical: -->
108 <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
109 </bp:property>
110 <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
111 <bp:property name="upateSnippets">
112 <bp:map>
113 <bp:entry key="0.1.00" value="foo.schema.SchemaUpdate_0_1_01"></bp:entry>
114 <bp:entry key="0.1.01" value="foo.schema.SchemaUpdate_0_2_00"></bp:entry>
115 </bp:map>
116 </bp:property>
117 -->
118 </bp:bean>
119 {{/code}}
120
121 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
122
123 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
124
125 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-InitalSchema(InitialTableList)"/}}Inital Schema (Initial Table List) ===
126
127 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
128
129 The recommended strategy is to create an InitialSchema class providing this list through a getter.
130
131 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
132
133 {{code language="java"}}
134 package foo.schema;
135
136 import java.sql.Types;
137 import java.util.Arrays;
138 import java.util.List;
139
140 import org.clazzes.jdbc2xml.schema.ColumnInfo;
141 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
142 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
143 import org.clazzes.jdbc2xml.schema.TableInfo;
144  
145 public class InitialSchema {
146
147 private List<TableInfo> setup;
148
149 public InitialSchema() {
150 // Create a table
151 TableInfo exampleTable = new TableInfo(TableDefs.TABLENAME_ADDRESSBOOK);
152 exampleTable.setColumns(
153 Arrays.asList(new ColumnInfo[] {
154 new ColumnInfo(TableDefs.COL_ADDRESSBOOK_ID, Types.BIGINT, 20, null, false, null,true),
155 new ColumnInfo(TableDefs.COL_ADDRESSBOOK_NAME, Types.VARCHAR, 256, null, false, null),
156 new ColumnInfo(TableDefs.COL_ADDRESSBOOK_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
157 new ColumnInfo(TableDefs.COL_ADDRESSBOOK_BIRTHDAY, Types.DATE, 12, null, false, null)
158 }));
159  
160 // Example for creating a primary key
161 exampleTable.setPrimaryKey(
162 new PrimaryKeyInfo("PK_EXAMPLE", COL_ADDRESSBOOK_ID)
163 );
164
165 // Example for creating a foreign key reference
166 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
167 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", TableDefs.COL_ADDRESSBOOK_ADDRESS_REF, TableDefs.TABLENAME_ADDRESSES, TableDefs.COL_ADDRESS_ID)
168 }));
169  
170 // ...
171  
172 this.setup = Arrays.asList(
173 exampleTable,
174 // ...
175 );
176
177 }
178  
179 public List<TableInfo> getSchema() {
180 return this.schema;
181 }
182
183 }
184 {{/code}}
185
186 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-TableDefs,acentralplacefortableandcolumnnames"/}}TableDefs, a central place for table and column names ===
187
188 You may have notice the usage of {{code language="none"}}TableDefs.*{{/code}} members.
189
190 Table and column names should never be re-typed everywhere as literals, it is highly recommended to use constants.
191
192 Putting these constants in a dedicated class, say {{code language="none"}}TableDef{{/code}}, allows to use this as an easily accessible list of all tables and columns in the database.
193
194 This is an example:
195
196 {{code language="java"}}
197 package foo.schema;
198
199 public class TableDefs {
200
201 // It is adviseable to provide the Strings used as names for tables and columns as constants,
202 // so they can be reused savely to construct SQL statements
203  
204 // 0.1.00
205 public static final String TABLENAME_ADDRESSBOOK = "ADDRESSBOOK";
206 public static final String COL_ADDRESSBOOK_ID = "ID";
207 public static final String COL_ADDRESSBOOK_NAME = "NAME";
208 public static final String COL_ADDRESSBOOK_ADDRESS_REF = "ADDRESS";
209 public static final String COL_ADDRESSBOOK_BIRTHDAY = "BIRTHDAY";
210 // 0.1.01
211 public static final String COL_ADDRESSBOOK_GENDER = "GENDER";
212  
213 }
214 {{/code}}
215
216 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Triggeringthecreationoftheinitialschema"/}}Triggering the creation of the initial schema ===
217
218 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)To trigger the creation of the initial schema when coming across an empty database, (% style="font-size: 10.0pt;line-height: 13.0pt;" %){{code language="none"}}InitialSchema.getSchema(){{/code}} has to be injected into (% style="font-size: 10.0pt;line-height: 13.0pt;" %){{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling (% style="font-size: 10.0pt;line-height: 13.0pt;" %){{code language="none"}}SchemaManager.start(){{/code}}.
219
220 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
221
222 {{code language="html/xml"}}
223 <bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
224  
225 <bp:property name="baseTables">
226 <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
227 </bp:property>
228
229 {{/code}}
230
231 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
232
233 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.
234
235 An example for an implementation of a schema update snippet could look like this:
236
237 {{code language="java"}}
238 package foo.schema;
239
240 import java.sql.SQLException;
241 import java.sql.Types;
242 import java.util.Arrays;
243 import org.clazzes.jdbc2xml.schema.ColumnInfo;
244 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
245 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
246 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
247 import org.clazzes.jdbc2xml.schema.TableInfo;
248
249 public class SchemaUpdate_0_1_01 implements ISchemaUpdateSnippet {
250  
251 // This is only accessed through the getter
252 private static final String TARGET_VERSION = "0.1.01";
253  
254 @Override
255 public String getTargetVersion() {
256 return TARGET_VERSION;
257 }
258
259 @Override
260 public String getUpdateComment() {
261 return "Adding column "+TableDefs.COL_ADDRESSBOOK_GENDER+" to table "+TableDefs.TABLENAME_ADDRESSBOOK+".";
262 }
263
264 @Override
265 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
266 TableInfo ti = schemaEngine.fetchTableInfo(TableDefs.TABLENAME_ADDRESSBOOK, null);
267 schemaEngine.addColumn(ti, new ColumnInfo(TableDefs.COL_ADDRESSBOOK_GENDER, Types.VARCHAR, 1, null, true, null));
268 }
269 }
270
271
272 {{/code}}
273
274 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 ADDRESSBOOK table created via the InitialSchema class above.
275
276 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
277
278 {{code language="java"}}
279 @Override
280 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
281 TableInfo tiGroup = new TableInfo(TB_GROUP);
282 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
283 new ColumnInfo(TableDefs.COL_ID, Types.VARCHAR, 36, null, false, null),
284 new ColumnInfo(TableDefs.COL_NAME, Types.VARCHAR, 100, null, false, null),
285 new ColumnInfo(TableDefs.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
286 }));
287 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefs.COL_ID));
288 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefs.COL_NAME, true, null)));
289
290 schemaEngine.createTable(tiGroup, true);
291 }
292 {{/code}}
293
294 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
295
296 {{code language="java"}}
297 @Override
298 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
299 String sql = "UPDATE "+TableDefs.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefs.COL_EXAMPLE_NAME+"=?";
300
301 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
302
303 ps.setNull(1, Types.VARCHAR);
304
305 ps.execute();
306 }
307 {{/code}}
308
309 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
310
311 {{code language="html/xml"}}
312 <!-- SchemaManager bean definition starts here ... -->
313 <bp:property name="upateSnippets">
314 <bp:map>
315 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
316 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
317 </bp:map>
318 </bp:property>
319 <!-- ... and continues here -->
320 {{/code}}
321
322 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
323
324 The JDBC2XML Schema management tools allow for 2 different strategies:
325
326 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Frozen(Initial)TableList"/}}Frozen (% style="font-size: 14.0pt;" %)(Initial) Table List(%%) ===
327
328 The legacy strategy is:
329
330 (% style="list-style-type: square;" %)
331 * At the start of a project, create and use {{code language="none"}}InitalSchema.java{{/code}}
332 * After the first commit, {{code language="none"}}InitalSchema{{/code}} are considered frozen, all changes go into {{code language="none"}}SchemaUpdates{{/code}}, up to one update per source code commit
333
334 Advantage: Rock solid.
335
336 Disadvantage: No place to look for the complete and exact current scheme, except actual databases. {{code language="none"}}TableDefs.java{{/code}} provide some information, but may become confusing in the long term.
337
338 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Possiblebutdangerous:Evolving(Initial)TableList"/}}Possible but dangerous: Evolving (Initial) Table List ===
339
340 To keep the (Initial) Schema up do date, one might use this strategy:
341
342 (% style="list-style-type: square;" %)
343 * keep the {{code language="none"}}InitalSchema{{/code}} up to date, so an empty database always gets the current scheme in one shot
344 * {{code language="none"}}SchemaUpdates{{/code}} are only applied to existing databases
345
346 Advantage: Immediate overview over exact current scheme.
347
348 Disadvantage: Very real danger of messing something up, because
349
350 (% style="list-style-type: square;" %)
351 * schema updates have to be coded in 2 different places in 2 different ways
352 * the bean definition has to be maintained in 2 places but just 1
353
354 Conclusion: **DO NOT DO THIS**. This strategy may be ok in very early stages, but at some point it has to be
355
356 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Recommendation:FreezeInitialTableDefinitionnotlaterthanthefirstReleaseCandidate(RC)"/}}(% style="font-size: 14.0pt;" %)Recommendation: Freeze Initial Table Definition not later than the first Release Candidate (RC)(%%) ===
357
358 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.
359
360 (% 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!
361
362 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-RealworldExample"/}}Real world Example =
363
364 This HowTo is currently evolving while an additional developer gets acostumed to the SchemaEngine, for developing [[SDS' org.clazzes.sds.impl.schema package>>url:http://svn.clazzes.org/svn/sds/trunk/sds.impl/src/main/java/org/clazzes/sds/impl/schema||style="font-size: 10.0pt;line-height: 13.0pt;" shape="rect"]](% style="font-size: 10.0pt;line-height: 13.0pt;" %) which is **work in progress**!
365
366 (% style="font-size: 10.0pt;line-height: 13.0pt;" %)\\