Skip to Content
Version 4.1 by christoph_lechleitner@iteg_at on 2013-01-31 04.25:51
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 <bp:property name="upateSnippets">
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> -->
66 </bp:property>
67 </bp:bean>
68 {{/code}}
69
70 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
71
72 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
73
74 To create an initial database schema, SchemaManager needs a list of TableInfo objects.
75
76 The recommended strategy is to implement a table definition class providing this list through a getter.
77
78 This is an example:
79
80 {{code language="java"}}
81 package org.clazzes.example.jdbc2xml;
82
83 import java.sql.Types;
84 import java.util.Arrays;
85 import java.util.List;
86
87 import org.clazzes.jdbc2xml.schema.ColumnInfo;
88 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
89 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
90 import org.clazzes.jdbc2xml.schema.TableInfo;
91  
92 public class TableDefinitions {
93
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  
103 private List<TableInfo> setup;
104
105 public TableDefinitions() {
106 // Create a table
107 TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
108 exampleTable.setColumns(
109 Arrays.asList(new ColumnInfo[] {
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)
114 }));
115  
116 // Example for creating a foreign key reference
117 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
118 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
119 }));
120  
121 // Example for creating a primary key
122 exampleTable.setPrimaryKey(
123 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
124 );
125
126 // ...
127  
128 this.setup = Arrays.asList(
129 exampleTable,
130 // ...
131 );
132
133 }
134  
135 public List<TableInfo> getSetup() {
136 return this.setup;
137 }
138
139 }
140 {{/code}}
141
142 You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
143
144 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
145
146 {{code language="html/xml"}}
147 <!-- SchemaManager bean definition starts here ... -->
148 <bp:property name="baseTables">
149 <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
150 </bp:property>
151 <!-- ... and continues here -->
152 {{/code}}
153
154 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
155
156 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.
157
158 An example for an implementation of a schema update snippet could look like this:
159
160 {{code language="java"}}
161 package org.clazzes.example.jdbc2xml.updates;
162
163 import java.sql.SQLException;
164 import java.sql.Types;
165 import java.util.Arrays;
166 import org.clazzes.jdbc2xml.schema.ColumnInfo;
167 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
168 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
169 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
170 import org.clazzes.jdbc2xml.schema.TableInfo;
171
172 public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
173  
174 // This is only accessed through the getter
175 private static final String TARGET_VERSION = "0.1.01";
176  
177 // Here it is also adviseable to define constants for reuse in statements.
178 public static final String COL_EXAMPLE_GENDER = "GENDER";
179
180
181 @Override
182 public String getTargetVersion() {
183 return TARGET_VERSION;
184 }
185
186 @Override
187 public String getUpdateComment() {
188 return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
189 }
190
191 @Override
192 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
193 TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
194 schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
195 }
196 }
197
198
199 {{/code}}
200
201 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.
202
203 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
204
205 {{code language="java"}}
206 @Override
207 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
208 TableInfo tiGroup = new TableInfo(TB_GROUP);
209 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
210 new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
211 new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
212 new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
213 }));
214 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
215 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
216
217 schemaEngine.createTable(tiGroup, true);
218 }
219 {{/code}}
220
221 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
222
223 {{code language="java"}}
224 @Override
225 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
226 String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
227
228 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
229
230 ps.setNull(1, Types.VARCHAR);
231
232 ps.execute();
233 }
234 {{/code}}
235
236 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
237
238 {{code language="html/xml"}}
239 <!-- SchemaManager bean definition starts here ... -->
240 <bp:property name="upateSnippets">
241 <bp:map>
242 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
243 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
244 </bp:map>
245 </bp:property>
246 <!-- ... and continues here -->
247 {{/code}}
248
249
250
251
252
253
254
255
256
257
258
259