Skip to Content
Version 5.1 by christoph_lechleitner@iteg_at on 2013-01-31 06.18:32
Hide last authors
5fbc055b7cc103006957e1ae 2.1 1 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
5fbc055b7cc103006957e1ae 1.1 2
christoph_lechleitner@iteg_at 4.1 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.
5fbc055b7cc103006957e1ae 1.1 4
christoph_lechleitner@iteg_at 4.1 5 ==== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ====
5fbc055b7cc103006957e1ae 1.1 6
christoph_lechleitner@iteg_at 4.1 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
5fbc055b7cc103006957e1ae 1.1 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}}
15
christoph_lechleitner@iteg_at 4.1 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.
17
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
5fbc055b7cc103006957e1ae 2.1 20 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
5fbc055b7cc103006957e1ae 1.1 21
christoph_lechleitner@iteg_at 4.1 22 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.
5fbc055b7cc103006957e1ae 1.1 23
christoph_lechleitner@iteg_at 4.1 24 Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
5fbc055b7cc103006957e1ae 1.1 25
5fbc055b7cc103006957e1ae 2.1 26 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
5fbc055b7cc103006957e1ae 1.1 27
5fbc055b7cc103006957e1ae 2.1 28 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
29
christoph_lechleitner@iteg_at 4.1 30 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
5fbc055b7cc103006957e1ae 2.1 31
32 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):
33
34 {{code language="html/xml"}}
5fbc055b7cc103006957e1ae 1.1 35 <bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
36 </bp:reference>
37  
38 <bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
39 </bp:reference>
40  
41 <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
christoph_lechleitner@iteg_at 4.1 42 <bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
5fbc055b7cc103006957e1ae 1.1 43 </bp:argument>
44 </bp:bean>
45  
46 <bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
47 <bp:property name="dialect" ref="sqlDialect">
48 </bp:property>
49 </bp:bean>
50  
5fbc055b7cc103006957e1ae 2.1 51 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
5fbc055b7cc103006957e1ae 1.1 52 <bp:property name="dataSource" ref="dataSource"></bp:property>
53 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
christoph_lechleitner@iteg_at 4.1 54 <!-- optional but recommended: special name for schema history table: -->
55 <bp:property name="versionHistoryTable" value="MYLIB_SCHEMA_HISTORY"/>
56 <!-- optional but recommended: explicit first version -->
5fbc055b7cc103006957e1ae 1.1 57 <bp:property name="baseVersion" value="0.1.00" />
58 <bp:property name="baseTables">
christoph_lechleitner@iteg_at 4.1 59 <!-- List of TableDefinitions here (see below), typical: -->
60 <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
5fbc055b7cc103006957e1ae 1.1 61 </bp:property>
christoph_lechleitner@iteg_at 5.1 62 <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
5fbc055b7cc103006957e1ae 1.1 63 <bp:property name="upateSnippets">
christoph_lechleitner@iteg_at 5.1 64 <bp:map>
65 <bp:entry key="0.1.00" value="foo.schema.SchemaUpdate_0_1_01"></bp:entry>
66 <bp:entry key="0.1.01" value="foo.schema.SchemaUpdate_0_2_00"></bp:entry>
67 </bp:map>
5fbc055b7cc103006957e1ae 1.1 68 </bp:property>
christoph_lechleitner@iteg_at 5.1 69 -->
5fbc055b7cc103006957e1ae 1.1 70 </bp:bean>
71 {{/code}}
72
christoph_lechleitner@iteg_at 4.1 73 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
5fbc055b7cc103006957e1ae 2.1 74
75 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
76
christoph_lechleitner@iteg_at 4.1 77 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
5fbc055b7cc103006957e1ae 2.1 78
christoph_lechleitner@iteg_at 4.1 79 The recommended strategy is to implement a table definition class providing this list through a getter.
80
81 This is an example:
82
5fbc055b7cc103006957e1ae 2.1 83 {{code language="java"}}
84 package org.clazzes.example.jdbc2xml;
85
86 import java.sql.Types;
87 import java.util.Arrays;
88 import java.util.List;
89
90 import org.clazzes.jdbc2xml.schema.ColumnInfo;
91 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
92 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
93 import org.clazzes.jdbc2xml.schema.TableInfo;
94  
95 public class TableDefinitions {
96
5fbc055b7cc103006957e1ae 3.1 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
5fbc055b7cc103006957e1ae 2.1 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  
5fbc055b7cc103006957e1ae 3.1 104 // ...
5fbc055b7cc103006957e1ae 2.1 105  
106 private List<TableInfo> setup;
107
108 public TableDefinitions() {
5fbc055b7cc103006957e1ae 3.1 109 // Create a table
5fbc055b7cc103006957e1ae 2.1 110 TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
111 exampleTable.setColumns(
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)
117 }));
118  
5fbc055b7cc103006957e1ae 3.1 119 // Example for creating a foreign key reference
5fbc055b7cc103006957e1ae 2.1 120 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
121 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
122 }));
123  
5fbc055b7cc103006957e1ae 3.1 124 // Example for creating a primary key
5fbc055b7cc103006957e1ae 2.1 125 exampleTable.setPrimaryKey(
126 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
127 );
128
5fbc055b7cc103006957e1ae 3.1 129 // ...
5fbc055b7cc103006957e1ae 2.1 130  
131 this.setup = Arrays.asList(
132 exampleTable,
5fbc055b7cc103006957e1ae 3.1 133 // ...
5fbc055b7cc103006957e1ae 2.1 134 );
135
136 }
137  
138 public List<TableInfo> getSetup() {
139 return this.setup;
140 }
141
142 }
143 {{/code}}
144
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
5fbc055b7cc103006957e1ae 3.1 147 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
5fbc055b7cc103006957e1ae 2.1 148
5fbc055b7cc103006957e1ae 3.1 149 {{code language="html/xml"}}
150 <!-- SchemaManager bean definition starts here ... -->
151 <bp:property name="baseTables">
152 <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
153 </bp:property>
154 <!-- ... and continues here -->
5fbc055b7cc103006957e1ae 2.1 155 {{/code}}
156
5fbc055b7cc103006957e1ae 3.1 157 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
5fbc055b7cc103006957e1ae 2.1 158
159 To update the database or it's content with schema updates, you must create a new implementation of ISchemaUpdateSnippet ({{code language="none"}}org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet{{/code}}) for each consecutive update. SchemaManager takes a {{code language="none"}}Map<String, Class<? extends ISchemaUpdateSnippet>>{{/code}} which contains the update classes keyed by the originating (e.g. previous) version.
160
5fbc055b7cc103006957e1ae 3.1 161 An example for an implementation of a schema update snippet could look like this:
5fbc055b7cc103006957e1ae 2.1 162
5fbc055b7cc103006957e1ae 3.1 163 {{code language="java"}}
164 package org.clazzes.example.jdbc2xml.updates;
5fbc055b7cc103006957e1ae 2.1 165
5fbc055b7cc103006957e1ae 3.1 166 import java.sql.SQLException;
167 import java.sql.Types;
168 import java.util.Arrays;
169 import org.clazzes.jdbc2xml.schema.ColumnInfo;
170 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
171 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
172 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
173 import org.clazzes.jdbc2xml.schema.TableInfo;
174
175 public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
176  
177 // This is only accessed through the getter
178 private static final String TARGET_VERSION = "0.1.01";
179  
180 // Here it is also adviseable to define constants for reuse in statements.
181 public static final String COL_EXAMPLE_GENDER = "GENDER";
182
183
184 @Override
185 public String getTargetVersion() {
186 return TARGET_VERSION;
187 }
188
189 @Override
190 public String getUpdateComment() {
191 return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
192 }
193
194 @Override
195 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
196 TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
197 schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
198 }
199 }
200
201
202 {{/code}}
203
204 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.
205
206 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
207
208 {{code language="java"}}
209 @Override
210 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
211 TableInfo tiGroup = new TableInfo(TB_GROUP);
212 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
213 new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
214 new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
215 new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
216 }));
217 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
218 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
219
220 schemaEngine.createTable(tiGroup, true);
221 }
222 {{/code}}
223
224 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
225
226 {{code language="java"}}
227 @Override
228 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
229 String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
230
231 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
232
233 ps.setNull(1, Types.VARCHAR);
234
235 ps.execute();
236 }
237 {{/code}}
238
239 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
240
241 {{code language="html/xml"}}
242 <!-- SchemaManager bean definition starts here ... -->
243 <bp:property name="upateSnippets">
244 <bp:map>
245 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
246 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
247 </bp:map>
248 </bp:property>
249 <!-- ... and continues here -->
250 {{/code}}
251
5fbc055b7cc103006957e1ae 2.1 252
253
254
255
256
257
258
259
260
261
262