Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 6.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 07.04:57
Change comment: There is no comment for this version
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
... ... @@ -92,7 +92,10 @@
92 92  <bp:property name="dialect" ref="sqlDialect">
93 93  </bp:property>
94 94  </bp:bean>
95 +
95 95   
97 +<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
98 + 
96 96  <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
97 97  <bp:property name="dataSource" ref="dataSource"></bp:property>
98 98  <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
... ... @@ -102,7 +102,7 @@
102 102  <bp:property name="baseVersion" value="0.1.00" />
103 103  <bp:property name="baseTables">
104 104  <!-- List of TableDefinitions here (see below), typical: -->
105 - <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
108 + <bp:bean factory-ref="initialSchema" factory-method="getSchema" />
106 106  </bp:property>
107 107   <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
108 108  <bp:property name="upateSnippets">
... ... @@ -119,6 +119,8 @@
119 119  
120 120  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
121 121  
125 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-InitalSchema(InitialTableList)"/}}Inital Schema (Initial Table List) ===
126 +
122 122  To create an initial database schema, SchemaManager needs a list of TableInfo objects.
123 123  
124 124  The recommended strategy is to create an InitialSchema class providing this list through a getter.
... ... @@ -126,7 +126,7 @@
126 126  (% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
127 127  
128 128  {{code language="java"}}
129 -package org.clazzes.example.jdbc2xml;
134 +package foo.schema;
130 130  
131 131  import java.sql.Types;
132 132  import java.util.Arrays;
... ... @@ -141,27 +141,27 @@
141 141  
142 142   private List<TableInfo> setup;
143 143  
144 - public TableDefinitions() {
149 + public InitialSchema() {
145 145  // Create a table
146 -TableInfo exampleTable = new TableInfo(TableDefs.TB_EXAMPLE_TABLE_NAME);
151 +TableInfo exampleTable = new TableInfo(TableDefs.TABLENAME_ADDRESSBOOK);
147 147  exampleTable.setColumns(
148 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)
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)
153 153  }));
154 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 160  // Example for creating a primary key
161 161  exampleTable.setPrimaryKey(
162 -new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
162 +new PrimaryKeyInfo("PK_EXAMPLE", COL_ADDRESSBOOK_ID)
163 163  );
164 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 + 
165 165  // ...
166 166   
167 167  this.setup = Arrays.asList(
... ... @@ -178,45 +178,49 @@
178 178  }
179 179  {{/code}}
180 180  
181 -{{code language="java"}}
182 -package org.clazzes.example.jdbc2xml;
186 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-TableDefs,acentralplacefortableandcolumnnames"/}}TableDefs, a central place for table and column names ===
183 183  
184 -import java.sql.Types;
185 -import java.util.Arrays;
186 -import java.util.List;
188 +You may have notice the usage of {{code language="none"}}TableDefs.*{{/code}} members.
187 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;
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;
192 192  
193 193  public class TableDefs {
194 194  
195 195   // It is adviseable to provide the Strings used as names for tables and columns as constants,
196 196   // so they can be reused savely to construct SQL statements
203 + 
204 + // 0.1.00
197 197   public static final String TABLENAME_ADDRESSBOOK = "ADDRESSBOOK";
198 198   public static final String COL_ADDRESSBOOK_ID = "ID";
199 199   public static final String COL_ADDRESSBOOK_NAME = "NAME";
200 200   public static final String COL_ADDRESSBOOK_ADDRESS_REF = "ADDRESS";
201 201   public static final String COL_ADDRESSBOOK_BIRTHDAY = "BIRTHDAY";
202 -
210 + // 0.1.01
211 +public static final String COL_ADDRESSBOOK_GENDER = "GENDER";
212 + 
203 203  }
204 204  {{/code}}
205 205  
206 -
216 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Triggeringthecreationoftheinitialschema"/}}Triggering the creation of the initial schema ===
207 207  
208 -
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}}.
209 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 212  Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
213 213  
214 214  {{code language="html/xml"}}
215 -<!-- SchemaManager bean definition starts here ... -->
223 +<bp:bean id="initialSchema" class="foo.schema.InitialSchema"></bp:bean>
224 + 
216 216  <bp:property name="baseTables">
217 -<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
226 +<bp:bean factory-ref="initialSchema" factory-method="getSchema" />
218 218  </bp:property>
219 -<!-- ... and continues here -->
228 +
220 220  {{/code}}
221 221  
222 222  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
... ... @@ -226,7 +226,7 @@
226 226  An example for an implementation of a schema update snippet could look like this:
227 227  
228 228  {{code language="java"}}
229 -package org.clazzes.example.jdbc2xml.updates;
238 +package foo.schema;
230 230  
231 231  import java.sql.SQLException;
232 232  import java.sql.Types;
... ... @@ -237,15 +237,11 @@
237 237  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
238 238  import org.clazzes.jdbc2xml.schema.TableInfo;
239 239  
240 -public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
249 +public class SchemaUpdate_0_1_01 implements ISchemaUpdateSnippet {
241 241   
242 242  // This is only accessed through the getter
243 243  private static final String TARGET_VERSION = "0.1.01";
244 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 249  @Override
250 250  public String getTargetVersion() {
251 251  return TARGET_VERSION;
... ... @@ -253,13 +253,13 @@
253 253  
254 254  @Override
255 255  public String getUpdateComment() {
256 -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+".";
257 257  }
258 258  
259 259  @Override
260 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));
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));
263 263  }
264 264  }
265 265  
... ... @@ -266,7 +266,7 @@
266 266  
267 267  {{/code}}
268 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.
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.
270 270  
271 271  To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
272 272  
... ... @@ -275,12 +275,12 @@
275 275  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
276 276   TableInfo tiGroup = new TableInfo(TB_GROUP);
277 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)
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)
281 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)));
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)));
284 284  
285 285  schemaEngine.createTable(tiGroup, true);
286 286  }
... ... @@ -291,7 +291,7 @@
291 291  {{code language="java"}}
292 292  @Override
293 293  public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
294 - 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+"=?";
295 295  
296 296   PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
297 297  
... ... @@ -323,22 +323,22 @@
323 323  The legacy strategy is:
324 324  
325 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
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
328 328  
329 329  Advantage: Rock solid.
330 330  
331 -Disadvantage: No place to look for the complete current scheme, except actual databases. See //Recommendation 1// below for a workaround.
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.
332 332  
333 333  === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Possiblebutdangerous:Evolving(Initial)TableList"/}}Possible but dangerous: Evolving (Initial) Table List ===
334 334  
335 -To keep the TableDefinitions up do date, one might use this strategy:
340 +To keep the (Initial) Schema up do date, one might use this strategy:
336 336  
337 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
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
340 340  
341 -Advantage: Immediate overview over current scheme.
346 +Advantage: Immediate overview over exact current scheme.
342 342  
343 343  Disadvantage: Very real danger of messing something up, because
344 344  
... ... @@ -346,22 +346,16 @@
346 346  * schema updates have to be coded in 2 different places in 2 different ways
347 347  * the bean definition has to be maintained in 2 places but just 1
348 348  
349 -Conclusion: DO NOT DO THIS. This strategy may be ok in very early stages, but at some point it has to be
354 +Conclusion: **DO NOT DO THIS**. This strategy may be ok in very early stages, but at some point it has to be
350 350  
351 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Recommendation1:SplitTableDefinitionsfrominitialTableList"/}}Recommendation 1: Split TableDefinitions from initial Table List ===
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)(%%) ===
352 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 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 358  
359 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 360  
361 -
362 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-RealworldExample"/}}Real world Example =
362 362  
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**!
364 364  
365 -
366 -
367 -
366 +(% style="font-size: 10.0pt;line-height: 13.0pt;" %)\\
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656817
1 +656798
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656817/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