Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 5.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 06.18:32
Change comment: There is no comment for this version
To 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

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 =
... ... @@ -76,9 +76,9 @@
76 76  
77 77  To create an initial database schema, SchemaManager needs a list of TableInfo objects.
78 78  
79 -The recommended strategy is to implement a table definition class providing this list through a getter.
124 +The recommended strategy is to create an InitialSchema class providing this list through a getter.
80 80  
81 -This is an example:
126 +(% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
82 82  
83 83  {{code language="java"}}
84 84  package org.clazzes.example.jdbc2xml;
... ... @@ -92,28 +92,19 @@
92 92  import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
93 93  import org.clazzes.jdbc2xml.schema.TableInfo;
94 94   
95 -public class TableDefinitions {
140 +public class InitialSchema {
96 96  
97 - // 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
98 - public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
99 - public static final String COL_EXAMPLE_ID = "ID";
100 - public static final String COL_EXAMPLE_NAME = "NAME";
101 - public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
102 - public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
103 - 
104 - // ...
105 - 
106 106   private List<TableInfo> setup;
107 107  
108 108   public TableDefinitions() {
109 109  // Create a table
110 -TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
146 +TableInfo exampleTable = new TableInfo(TableDefs.TB_EXAMPLE_TABLE_NAME);
111 111  exampleTable.setColumns(
112 112   Arrays.asList(new ColumnInfo[] {
113 -new ColumnInfo(COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
114 -new ColumnInfo(COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
115 -new ColumnInfo(COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
116 -new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
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)
117 117  }));
118 118   
119 119  // Example for creating a foreign key reference
... ... @@ -135,13 +135,42 @@
135 135  
136 136   }
137 137    
138 - public List<TableInfo> getSetup() {
139 -return this.setup;
174 + public List<TableInfo> getSchema() {
175 +return this.schema;
140 140   }
141 141  
142 142  }
143 143  {{/code}}
144 144  
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 +
145 145  You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
146 146  
147 147  Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
... ... @@ -249,10 +249,50 @@
249 249  <!-- ... and continues here -->
250 250  {{/code}}
251 251  
252 -
317 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaMaintainanceStrategies"/}}Schema Maintainance Strategies =
253 253  
254 -
319 +The JDBC2XML Schema management tools allow for 2 different strategies:
255 255  
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 +
256 256  
257 257  
258 258  
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656824
1 +656817
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656824/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656817/How To create and update Databases using SchemaManager and SchemaUpdateSnippets