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 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 =
... ... @@ -104,14 +104,11 @@
104 104  <!-- List of TableDefinitions here (see below), typical: -->
105 105   <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
106 106  </bp:property>
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">
109 -<bp:map>
110 - <bp:entry key="0.1.00" value="foo.schema.SchemaUpdate_0_1_01"></bp:entry>
111 - <bp:entry key="0.1.01" value="foo.schema.SchemaUpdate_0_2_00"></bp:entry>
112 -</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> -->
113 113  </bp:property>
114 - -->
115 115  </bp:bean>
116 116  {{/code}}
117 117  
... ... @@ -121,9 +121,9 @@
121 121  
122 122  To create an initial database schema, SchemaManager needs a list of TableInfo objects.
123 123  
124 -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.
125 125  
126 -(% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
78 +This is an example:
127 127  
128 128  {{code language="java"}}
129 129  package org.clazzes.example.jdbc2xml;
... ... @@ -137,19 +137,28 @@
137 137  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
138 138  import org.clazzes.jdbc2xml.schema.TableInfo;
139 139   
140 -public class InitialSchema {
92 +public class TableDefinitions {
141 141  
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 + 
142 142   private List<TableInfo> setup;
143 143  
144 144   public TableDefinitions() {
145 145  // Create a table
146 -TableInfo exampleTable = new TableInfo(TableDefs.TB_EXAMPLE_TABLE_NAME);
107 +TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
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)
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)
153 153  }));
154 154   
155 155  // Example for creating a foreign key reference
... ... @@ -171,42 +171,13 @@
171 171  
172 172   }
173 173    
174 - public List<TableInfo> getSchema() {
175 -return this.schema;
135 + public List<TableInfo> getSetup() {
136 +return this.setup;
176 176   }
177 177  
178 178  }
179 179  {{/code}}
180 180  
181 -{{code language="java"}}
182 -package org.clazzes.example.jdbc2xml;
183 -
184 -import java.sql.Types;
185 -import java.util.Arrays;
186 -import java.util.List;
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;
192 -
193 -public class TableDefs {
194 -
195 - // It is adviseable to provide the Strings used as names for tables and columns as constants,
196 - // so they can be reused savely to construct SQL statements
197 - public static final String TABLENAME_ADDRESSBOOK = "ADDRESSBOOK";
198 - public static final String COL_ADDRESSBOOK_ID = "ID";
199 - public static final String COL_ADDRESSBOOK_NAME = "NAME";
200 - public static final String COL_ADDRESSBOOK_ADDRESS_REF = "ADDRESS";
201 - public static final String COL_ADDRESSBOOK_BIRTHDAY = "BIRTHDAY";
202 -
203 -}
204 -{{/code}}
205 -
206 -
207 -
208 -
209 -
210 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 211  
212 212  Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
... ... @@ -314,50 +314,10 @@
314 314  <!-- ... and continues here -->
315 315  {{/code}}
316 316  
317 -= {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
249 +
318 318  
319 -The JDBC2XML Schema management tools allow for 2 different strategies:
251 +
320 320  
321 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Frozen(Initial)TableList"/}}Frozen (% style="font-size: 14.0pt;" %)(Initial) Table List(%%) ===
322 -
323 -The legacy strategy is:
324 -
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
328 -
329 -Advantage: Rock solid.
330 -
331 -Disadvantage: No place to look for the complete current scheme, except actual databases. See //Recommendation 1// below for a workaround.
332 -
333 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Possiblebutdangerous:Evolving(Initial)TableList"/}}Possible but dangerous: Evolving (Initial) Table List ===
334 -
335 -To keep the TableDefinitions up do date, one might use this strategy:
336 -
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
340 -
341 -Advantage: Immediate overview over current scheme.
342 -
343 -Disadvantage: Very real danger of messing something up, because
344 -
345 -(% style="list-style-type: square;" %)
346 -* schema updates have to be coded in 2 different places in 2 different ways
347 -* the bean definition has to be maintained in 2 places but just 1
348 -
349 -Conclusion: DO NOT DO THIS. This strategy may be ok in very early stages, but at some point it has to be
350 -
351 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Recommendation1:SplitTableDefinitionsfrominitialTableList"/}}Recommendation 1: Split TableDefinitions from initial Table List ===
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 -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 -
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 -
361 361  
362 362  
363 363  
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656817
1 +656825
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/656825/How To create and update Databases using SchemaManager and SchemaUpdateSnippets