Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From 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
To 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

Summary

Details

Page properties
Content
... ... @@ -2,19 +2,64 @@
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 -{{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}}
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 +)))
15 15  
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.
59 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
17 17  
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 +
18 18  (% 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;" %).
19 19  
20 20  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
... ... @@ -47,7 +47,10 @@
47 47  <bp:property name="dialect" ref="sqlDialect">
48 48  </bp:property>
49 49  </bp:bean>
95 +
50 50   
97 +<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
98 + 
51 51  <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
52 52  <bp:property name="dataSource" ref="dataSource"></bp:property>
53 53  <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
... ... @@ -57,13 +57,16 @@
57 57  <bp:property name="baseVersion" value="0.1.00" />
58 58  <bp:property name="baseTables">
59 59  <!-- List of TableDefinitions here (see below), typical: -->
60 - <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
108 + <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
61 61  </bp:property>
110 + <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
62 62  <bp:property name="upateSnippets">
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> -->
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>
66 66  </bp:property>
117 + -->
67 67  </bp:bean>
68 68  {{/code}}
69 69  
... ... @@ -71,14 +71,16 @@
71 71  
72 72  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
73 73  
125 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-InitalSchema(InitialTableList)"/}}Inital Schema (Initial Table List) ===
126 +
74 74  To create an initial database schema, SchemaManager needs a list of TableInfo objects.
75 75  
76 -The recommended strategy is to implement a table definition class providing this list through a getter.
129 +The recommended strategy is to create an InitialSchema class providing this list through a getter.
77 77  
78 -This is an example:
131 +(% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
79 79  
80 80  {{code language="java"}}
81 -package org.clazzes.example.jdbc2xml;
134 +package foo.schema;
82 82  
83 83  import java.sql.Types;
84 84  import java.util.Arrays;
... ... @@ -89,40 +89,31 @@
89 89  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
90 90  import org.clazzes.jdbc2xml.schema.TableInfo;
91 91   
92 -public class TableDefinitions {
145 +public class InitialSchema {
93 93  
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 - 
103 103   private List<TableInfo> setup;
104 104  
105 - public TableDefinitions() {
149 + public InitialSchema() {
106 106  // Create a table
107 -TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
151 +TableInfo exampleTable = new TableInfo(TableDefs.TABLENAME_ADDRESSBOOK);
108 108  exampleTable.setColumns(
109 109   Arrays.asList(new ColumnInfo[] {
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)
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)
114 114  }));
115 115   
116 -// Example for creating a foreign key reference
117 -exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
118 -new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
119 -}));
120 - 
121 121  // Example for creating a primary key
122 122  exampleTable.setPrimaryKey(
123 -new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
162 +new PrimaryKeyInfo("PK_EXAMPLE", COL_ADDRESSBOOK_ID)
124 124  );
125 125  
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 + 
126 126  // ...
127 127   
128 128  this.setup = Arrays.asList(
... ... @@ -132,23 +132,56 @@
132 132  
133 133   }
134 134    
135 - public List<TableInfo> getSetup() {
136 -return this.setup;
179 + public List<TableInfo> getSchema() {
180 +return this.schema;
137 137   }
138 138  
139 139  }
140 140  {{/code}}
141 141  
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}}.
186 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-TableDefs,acentralplacefortableandcolumnnames"/}}TableDefs, a central place for table and column names ===
143 143  
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 +
144 144  Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
145 145  
146 146  {{code language="html/xml"}}
147 -<!-- SchemaManager bean definition starts here ... -->
223 +<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
224 + 
148 148  <bp:property name="baseTables">
149 -<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
226 +<bp:bean factory-ref="initialSchema" factory-method="getSchema" />
150 150  </bp:property>
151 -<!-- ... and continues here -->
228 +
152 152  {{/code}}
153 153  
154 154  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
... ... @@ -158,7 +158,7 @@
158 158  An example for an implementation of a schema update snippet could look like this:
159 159  
160 160  {{code language="java"}}
161 -package org.clazzes.example.jdbc2xml.updates;
238 +package foo.schema;
162 162  
163 163  import java.sql.SQLException;
164 164  import java.sql.Types;
... ... @@ -169,15 +169,11 @@
169 169  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
170 170  import org.clazzes.jdbc2xml.schema.TableInfo;
171 171  
172 -public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
249 +public class SchemaUpdate_0_1_01 implements ISchemaUpdateSnippet {
173 173   
174 174  // This is only accessed through the getter
175 175  private static final String TARGET_VERSION = "0.1.01";
176 176   
177 -// Here it is also adviseable to define constants for reuse in statements.
178 -public static final String COL_EXAMPLE_GENDER = "GENDER";
179 -
180 -
181 181  @Override
182 182  public String getTargetVersion() {
183 183  return TARGET_VERSION;
... ... @@ -185,13 +185,13 @@
185 185  
186 186  @Override
187 187  public String getUpdateComment() {
188 -return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
261 +return "Adding column "+TableDefs.COL_ADDRESSBOOK_GENDER+" to table "+TableDefs.TABLENAME_ADDRESSBOOK+".";
189 189  }
190 190  
191 191  @Override
192 192  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
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));
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));
195 195  }
196 196  }
197 197  
... ... @@ -198,7 +198,7 @@
198 198  
199 199  {{/code}}
200 200  
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.
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.
202 202  
203 203  To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
204 204  
... ... @@ -207,12 +207,12 @@
207 207  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
208 208   TableInfo tiGroup = new TableInfo(TB_GROUP);
209 209   tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
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)
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)
213 213   }));
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)));
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)));
216 216  
217 217  schemaEngine.createTable(tiGroup, true);
218 218  }
... ... @@ -223,7 +223,7 @@
223 223  {{code language="java"}}
224 224  @Override
225 225  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
226 - String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
299 + String sql = "UPDATE "+TableDefs.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefs.COL_EXAMPLE_NAME+"=?";
227 227  
228 228   PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
229 229  
... ... @@ -246,14 +246,48 @@
246 246  <!-- ... and continues here -->
247 247  {{/code}}
248 248  
249 -
322 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
250 250  
251 -
324 +The JDBC2XML Schema management tools allow for 2 different strategies:
252 252  
253 -
326 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Frozen(Initial)TableList"/}}Frozen (% style="font-size: 14.0pt;" %)(Initial) Table List(%%) ===
254 254  
255 -
328 +The legacy strategy is:
256 256  
257 -
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
258 258  
259 -
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 -656825
1 +656798
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656825/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656798/How To create and update Databases using SchemaManager and SchemaUpdateSnippets