Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 1.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 12.59:26
Change comment: There is no comment for this version
To version 8.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 07.32:56
Change comment: Migrated to Confluence 5.3

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.5fbc055b7cc103006957e1ae
1 +XWiki.christoph_lechleitner@iteg_at
Content
... ... @@ -1,25 +1,82 @@
1 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary ===
1 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
2 2  
3 -The SchemaManager and SchemaEngine give you an abstracted access to the database schema of your application, allowing you to add and delete tables, fields and relations with 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.
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 4  
5 -SchemaManager ({{code language="none"}}org.clazzes.jdbc2xml.schema.SchemaManager{{/code}}) 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:
5 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ===
6 6  
7 -{{code language="none"}}
8 -VERSION:varchar(10), not null, primary key
9 -DESCRIPTION:varchar(512), nullable
10 -CREATION_DATE:date, nullable
11 -SERIALNR:integer(5), not null
12 -{{/code}}
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:
13 13  
14 -It will also take a list of TableInfo ({{code language="none"}}org.clazzes.jdbc2xml.schema.TableInfo{{/code}}) Objects and create a database from them, if it finds an empty database when it starts.
15 -
9 +|=(((
16 16  
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 +)))
17 17  
18 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UsingOSGiandBlueprint"/}}Using OSGi and Blueprint ===
59 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
19 19  
20 -After adding a dependency to the {{code language="none"}}org.clazzes.jdbc2xml{{/code}} library with your favourite package management system, add the following to your blueprint {{code language="none"}}services.xml{{/code}} configuration:
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.
21 21  
22 -{{code language="none"}}
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"}}
23 23  <bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
24 24  </bp:reference>
25 25   
... ... @@ -27,7 +27,7 @@
27 27  </bp:reference>
28 28   
29 29  <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
30 -<bp:argument ref="jdbcUrl"> <!-- JDBC URL -->
87 +<bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
31 31  </bp:argument>
32 32  </bp:bean>
33 33   
... ... @@ -35,19 +35,275 @@
35 35  <bp:property name="dialect" ref="sqlDialect">
36 36  </bp:property>
37 37  </bp:bean>
95 +
38 38   
39 -<bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager"
40 -init-method="start">
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">
41 41  <bp:property name="dataSource" ref="dataSource"></bp:property>
42 42  <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 -->
43 43  <bp:property name="baseVersion" value="0.1.00" />
44 44  <bp:property name="baseTables">
45 -<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
107 +<!-- List of TableDefinitions here (see below), typical: -->
108 + <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
46 46  </bp:property>
110 + <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
47 47  <bp:property name="upateSnippets">
48 -<!-- Add Update-Snippets here -->
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>
49 49  </bp:property>
117 + -->
50 50  </bp:bean>
51 51  {{/code}}
52 52  
53 -The SchemaManager takes a map of
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;" %)\\
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656804
1 +656790
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656804/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656790/How To create and update Databases using SchemaManager and SchemaUpdateSnippets