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 3.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 07.03:35
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.christoph_lechleitner@iteg_at
1 +XWiki.5fbc055b7cc103006957e1ae
Content
... ... @@ -1,78 +1,27 @@
1 1  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
2 2  
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.
3 +The SchemaManager and SchemaEngine give you an abstracted access to the database schema of your application, allowing you to add and delete tables, columns, relations and data in the database on application updates. It uses a designated table to keep track of the current schema status, which it will check whenever it is run, and update the database if it is not at the correct version.
4 4  
5 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ===
5 +SchemaManager ({{code language="none"}}org.clazzes.jdbc2xml.schema.SchemaManager{{/code}}) will create a table called SCHEMA_HISTORY if it can not find it the first time it is run. This table contains the following columns:
6 6  
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:
7 +{{code language="none"}}
8 +VERSION:varchar(10), not null, primary key
9 +DESCRIPTION:varchar(512), nullable
10 +CREATION_DATE:date, nullable
11 +SERIALNR:integer(5), not null
12 +{{/code}}
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 -)))
58 -
59 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-CustomnameforSchemaHistoryTable"/}}Custom name for Schema History Table ===
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 -(% 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 -
65 65  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
66 66  
67 -SchemaManager requires a DataSource ({{code language="none"}}javax.sql.DataSource{{/code}}) and a list of TableInfo ({{code language="none"}}org.clazzes.jdbc2xml.schema.TableInfo{{/code}}) Objects, from which database structures will be created if an "empty" database is detected. Furthermore, an implementation of ISchemaEngine ({{code language="none"}}org.clazzes.jdbc2xml.schema.ISchemaEngine{{/code}}) is required.
16 +To function correctly, SchemaManager needs a DataSource ({{code language="none"}}javax.sql.DataSource{{/code}}) and a list of TableInfo ({{code language="none"}}org.clazzes.jdbc2xml.schema.TableInfo{{/code}}) Objects, from which a database will be created if it finds an empty database. To function properly, it also needs an implementation of ISchemaEngine ({{code language="none"}}org.clazzes.jdbc2xml.schema.ISchemaEngine{{/code}}).
68 68  
69 -Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
18 +Optionally, you may set the base version (default value 0.1.00) and base description String (default "initial database schema").
70 70  
71 71  Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
72 72  
73 73  To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
74 74  
75 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
24 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UsingSpringorOSGi/Blueprint"/}}Using Spring or OSGi/Blueprint ===
76 76  
77 77  If you are using OSGi with Blueprint or Spring to set up your project, you can configure a SchemaManager instance by adding the following to your blueprint {{code language="none"}}services.xml{{/code}} (or Spring configuration file):
78 78  
... ... @@ -84,7 +84,7 @@
84 84  </bp:reference>
85 85   
86 86  <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
87 -<bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
36 +<bp:argument> <!-- Pass JDBC URL as an argument -->
88 88  </bp:argument>
89 89  </bp:bean>
90 90   
... ... @@ -96,35 +96,22 @@
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>
99 - <!-- optional but recommended: special name for schema history table: -->
100 - <bp:property name="versionHistoryTable" value="MYLIB_SCHEMA_HISTORY"/>
101 - <!-- optional but recommended: explicit first version -->
102 102  <bp:property name="baseVersion" value="0.1.00" />
103 103  <bp:property name="baseTables">
104 -<!-- List of TableDefinitions here (see below), typical: -->
105 - <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
50 +<!-- Add List of TableDefinitions here (see below) -->
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>
53 +<!-- Add Update-Snippets here -->
113 113  </bp:property>
114 - -->
115 115  </bp:bean>
116 116  {{/code}}
117 117  
118 -By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
58 +By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry lookup for Spring.
119 119  
120 120  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
121 121  
122 -To create an initial database schema, SchemaManager needs a list of TableInfo objects.
62 +To create an initial database schema, you will need to provide SchemaManager with a list of TableInfo objects. The recommended way to do this is to provide a class in your project which creates this list in it's constructor and provides it through a getter. You can instantiate this class in your Spring/Blueprint config as a singleton, and feed the provided List to SchemaManager. An example of this class could look like this:
123 123  
124 -The recommended strategy is to create an InitialSchema class providing this list through a getter.
125 -
126 -(% style="font-size: 10.0pt;line-height: 13.0pt;" %)This is an example:
127 -
128 128  {{code language="java"}}
129 129  package org.clazzes.example.jdbc2xml;
130 130  
... ... @@ -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 {
76 +public class TableDefinitions {
141 141  
78 + // 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
79 + public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
80 + public static final String COL_EXAMPLE_ID = "ID";
81 + public static final String COL_EXAMPLE_NAME = "NAME";
82 + public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
83 + public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
84 + 
85 + // ...
86 + 
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);
91 +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)
94 +new ColumnInfo(COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
95 +new ColumnInfo(COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
96 +new ColumnInfo(COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
97 +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;
119 + public List<TableInfo> getSetup() {
120 +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 =
233 +
318 318  
319 -The JDBC2XML Schema management tools allow for 2 different strategies:
235 +
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 +656821
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/656821/How To create and update Databases using SchemaManager and SchemaUpdateSnippets