Skip to Content
Version 5.1 by christoph_lechleitner@iteg_at on 2013-01-31 06.18:32
Show last authors
1 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
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.
4
5 ==== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ====
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:
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}}
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.
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
20 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
21
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.
23
24 Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
25
26 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
27
28 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
29
30 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
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"}}
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">
42 <bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
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  
51 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
52 <bp:property name="dataSource" ref="dataSource"></bp:property>
53 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
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 -->
57 <bp:property name="baseVersion" value="0.1.00" />
58 <bp:property name="baseTables">
59 <!-- List of TableDefinitions here (see below), typical: -->
60 <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
61 </bp:property>
62 <!-- Add Update-Snippets here, example for updates from 0.1.00 to 0.1.01 and on to 0.2.00
63 <bp:property name="upateSnippets">
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>
68 </bp:property>
69 -->
70 </bp:bean>
71 {{/code}}
72
73 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
74
75 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
76
77 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
78
79 The recommended strategy is to implement a table definition class providing this list through a getter.
80
81 This is an example:
82
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
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 private List<TableInfo> setup;
107
108 public TableDefinitions() {
109 // Create a table
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  
119 // Example for creating a foreign key reference
120 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
121 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
122 }));
123  
124 // Example for creating a primary key
125 exampleTable.setPrimaryKey(
126 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
127 );
128
129 // ...
130  
131 this.setup = Arrays.asList(
132 exampleTable,
133 // ...
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
147 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
148
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 -->
155 {{/code}}
156
157 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
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
161 An example for an implementation of a schema update snippet could look like this:
162
163 {{code language="java"}}
164 package org.clazzes.example.jdbc2xml.updates;
165
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
252
253
254
255
256
257
258
259
260
261
262