Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 7.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 07.32:55
Change comment: There is no comment for this version
To version 4.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 04.25:51
Change comment: Name of schema history table configurable now, and text refactoring

Summary

Details

Page properties
Content
... ... @@ -2,64 +2,19 @@
2 2  
3 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 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ===
5 +==== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ====
6 6  
7 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 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 -)))
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}}
58 58  
59 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
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.
60 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 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 64  
65 65  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
... ... @@ -92,10 +92,7 @@
92 92  <bp:property name="dialect" ref="sqlDialect">
93 93  </bp:property>
94 94  </bp:bean>
95 -
96 96   
97 -<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
98 - 
99 99  <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
100 100  <bp:property name="dataSource" ref="dataSource"></bp:property>
101 101  <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
... ... @@ -105,16 +105,13 @@
105 105  <bp:property name="baseVersion" value="0.1.00" />
106 106  <bp:property name="baseTables">
107 107  <!-- List of TableDefinitions here (see below), typical: -->
108 - <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
60 + <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
109 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 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>
63 +<!-- Add Update-Snippets here -->
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> -->
116 116  </bp:property>
117 - -->
118 118  </bp:bean>
119 119  {{/code}}
120 120  
... ... @@ -122,16 +122,14 @@
122 122  
123 123  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
124 124  
125 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-InitalSchema(InitialTableList)"/}}Inital Schema (Initial Table List) ===
126 -
127 127  To create an initial database schema, SchemaManager needs a list of TableInfo objects.
128 128  
129 -The recommended strategy is to create an InitialSchema class providing this list through a getter.
76 +The recommended strategy is to implement a table definition class providing this list through a getter.
130 130  
131 -(% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
78 +This is an example:
132 132  
133 133  {{code language="java"}}
134 -package foo.schema;
81 +package org.clazzes.example.jdbc2xml;
135 135  
136 136  import java.sql.Types;
137 137  import java.util.Arrays;
... ... @@ -142,31 +142,40 @@
142 142  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
143 143  import org.clazzes.jdbc2xml.schema.TableInfo;
144 144   
145 -public class InitialSchema {
92 +public class TableDefinitions {
146 146  
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
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 + 
101 + // ...
102 + 
147 147   private List<TableInfo> setup;
148 148  
149 - public InitialSchema() {
105 + public TableDefinitions() {
150 150  // Create a table
151 -TableInfo exampleTable = new TableInfo(TableDefs.TABLENAME_ADDRESSBOOK);
107 +TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
152 152  exampleTable.setColumns(
153 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)
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)
158 158  }));
159 159   
160 -// Example for creating a primary key
161 -exampleTable.setPrimaryKey(
162 -new PrimaryKeyInfo("PK_EXAMPLE", COL_ADDRESSBOOK_ID)
163 -);
164 -
165 165  // Example for creating a foreign key reference
166 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)
118 +new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
168 168  }));
169 169   
121 +// Example for creating a primary key
122 +exampleTable.setPrimaryKey(
123 +new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
124 +);
125 +
170 170  // ...
171 171   
172 172  this.setup = Arrays.asList(
... ... @@ -176,56 +176,23 @@
176 176  
177 177   }
178 178    
179 - public List<TableInfo> getSchema() {
180 -return this.schema;
135 + public List<TableInfo> getSetup() {
136 +return this.setup;
181 181   }
182 182  
183 183  }
184 184  {{/code}}
185 185  
186 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-TableDefs,acentralplacefortableandcolumnnames"/}}TableDefs, a central place for table and column names ===
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}}.
187 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 220  Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
221 221  
222 222  {{code language="html/xml"}}
223 -<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
224 - 
147 +<!-- SchemaManager bean definition starts here ... -->
225 225  <bp:property name="baseTables">
226 -<bp:bean factory-ref="initialSchema" factory-method="getSchema" />
149 +<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
227 227  </bp:property>
228 -
151 +<!-- ... and continues here -->
229 229  {{/code}}
230 230  
231 231  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
... ... @@ -235,7 +235,7 @@
235 235  An example for an implementation of a schema update snippet could look like this:
236 236  
237 237  {{code language="java"}}
238 -package foo.schema;
161 +package org.clazzes.example.jdbc2xml.updates;
239 239  
240 240  import java.sql.SQLException;
241 241  import java.sql.Types;
... ... @@ -246,11 +246,15 @@
246 246  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
247 247  import org.clazzes.jdbc2xml.schema.TableInfo;
248 248  
249 -public class SchemaUpdate_0_1_01 implements ISchemaUpdateSnippet {
172 +public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
250 250   
251 251  // This is only accessed through the getter
252 252  private static final String TARGET_VERSION = "0.1.01";
253 253   
177 +// Here it is also adviseable to define constants for reuse in statements.
178 +public static final String COL_EXAMPLE_GENDER = "GENDER";
179 +
180 +
254 254  @Override
255 255  public String getTargetVersion() {
256 256  return TARGET_VERSION;
... ... @@ -258,13 +258,13 @@
258 258  
259 259  @Override
260 260  public String getUpdateComment() {
261 -return "Adding column "+TableDefs.COL_ADDRESSBOOK_GENDER+" to table "+TableDefs.TABLENAME_ADDRESSBOOK+".";
188 +return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
262 262  }
263 263  
264 264  @Override
265 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));
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));
268 268  }
269 269  }
270 270  
... ... @@ -271,7 +271,7 @@
271 271  
272 272  {{/code}}
273 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.
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.
275 275  
276 276  To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
277 277  
... ... @@ -280,12 +280,12 @@
280 280  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
281 281   TableInfo tiGroup = new TableInfo(TB_GROUP);
282 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)
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)
286 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)));
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)));
289 289  
290 290  schemaEngine.createTable(tiGroup, true);
291 291  }
... ... @@ -296,7 +296,7 @@
296 296  {{code language="java"}}
297 297  @Override
298 298  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
299 - String sql = "UPDATE "+TableDefs.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefs.COL_EXAMPLE_NAME+"=?";
226 + String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
300 300  
301 301   PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
302 302  
... ... @@ -319,48 +319,14 @@
319 319  <!-- ... and continues here -->
320 320  {{/code}}
321 321  
322 -= {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
249 +
323 323  
324 -The JDBC2XML Schema management tools allow for 2 different strategies:
251 +
325 325  
326 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Frozen(Initial)TableList"/}}Frozen (% style="font-size: 14.0pt;" %)(Initial) Table List(%%) ===
253 +
327 327  
328 -The legacy strategy is:
255 +
329 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
257 +
333 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;" %)\\
259 +
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656798
1 +656825
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656798/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656825/How To create and update Databases using SchemaManager and SchemaUpdateSnippets