Skip to Content
Version 3.1 by 5fbc055b7cc103006957e1ae on 2012-06-14 07.03:35
Show last authors
1 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
2
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
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
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}}
13
14 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
15
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}}).
17
18 Optionally, you may set the base version (default value 0.1.00) and base description String (default "initial database schema").
19
20 Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
21
22 To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
23
24 === {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UsingSpringorOSGi/Blueprint"/}}Using Spring or OSGi/Blueprint ===
25
26 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):
27
28 {{code language="html/xml"}}
29 <bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
30 </bp:reference>
31  
32 <bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
33 </bp:reference>
34  
35 <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
36 <bp:argument> <!-- Pass JDBC URL as an argument -->
37 </bp:argument>
38 </bp:bean>
39  
40 <bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
41 <bp:property name="dialect" ref="sqlDialect">
42 </bp:property>
43 </bp:bean>
44  
45 <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
46 <bp:property name="dataSource" ref="dataSource"></bp:property>
47 <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
48 <bp:property name="baseVersion" value="0.1.00" />
49 <bp:property name="baseTables">
50 <!-- Add List of TableDefinitions here (see below) -->
51 </bp:property>
52 <bp:property name="upateSnippets">
53 <!-- Add Update-Snippets here -->
54 </bp:property>
55 </bp:bean>
56 {{/code}}
57
58 By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry lookup for Spring.
59
60 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
61
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:
63
64 {{code language="java"}}
65 package org.clazzes.example.jdbc2xml;
66
67 import java.sql.Types;
68 import java.util.Arrays;
69 import java.util.List;
70
71 import org.clazzes.jdbc2xml.schema.ColumnInfo;
72 import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
73 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
74 import org.clazzes.jdbc2xml.schema.TableInfo;
75  
76 public class TableDefinitions {
77
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  
87 private List<TableInfo> setup;
88
89 public TableDefinitions() {
90 // Create a table
91 TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
92 exampleTable.setColumns(
93 Arrays.asList(new ColumnInfo[] {
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)
98 }));
99  
100 // Example for creating a foreign key reference
101 exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
102 new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
103 }));
104  
105 // Example for creating a primary key
106 exampleTable.setPrimaryKey(
107 new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
108 );
109
110 // ...
111  
112 this.setup = Arrays.asList(
113 exampleTable,
114 // ...
115 );
116
117 }
118  
119 public List<TableInfo> getSetup() {
120 return this.setup;
121 }
122
123 }
124 {{/code}}
125
126 You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
127
128 Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
129
130 {{code language="html/xml"}}
131 <!-- SchemaManager bean definition starts here ... -->
132 <bp:property name="baseTables">
133 <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
134 </bp:property>
135 <!-- ... and continues here -->
136 {{/code}}
137
138 = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
139
140 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.
141
142 An example for an implementation of a schema update snippet could look like this:
143
144 {{code language="java"}}
145 package org.clazzes.example.jdbc2xml.updates;
146
147 import java.sql.SQLException;
148 import java.sql.Types;
149 import java.util.Arrays;
150 import org.clazzes.jdbc2xml.schema.ColumnInfo;
151 import org.clazzes.jdbc2xml.schema.ISchemaEngine;
152 import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
153 import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
154 import org.clazzes.jdbc2xml.schema.TableInfo;
155
156 public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
157  
158 // This is only accessed through the getter
159 private static final String TARGET_VERSION = "0.1.01";
160  
161 // Here it is also adviseable to define constants for reuse in statements.
162 public static final String COL_EXAMPLE_GENDER = "GENDER";
163
164
165 @Override
166 public String getTargetVersion() {
167 return TARGET_VERSION;
168 }
169
170 @Override
171 public String getUpdateComment() {
172 return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
173 }
174
175 @Override
176 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
177 TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
178 schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
179 }
180 }
181
182
183 {{/code}}
184
185 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.
186
187 To add an entire table you would use the {{code language="none"}}ISchemaEngine.createTable(){{/code}} method, like this:
188
189 {{code language="java"}}
190 @Override
191 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
192 TableInfo tiGroup = new TableInfo(TB_GROUP);
193 tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
194 new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
195 new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
196 new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
197 }));
198 tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
199 tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
200
201 schemaEngine.createTable(tiGroup, true);
202 }
203 {{/code}}
204
205 Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:
206
207 {{code language="java"}}
208 @Override
209 public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
210 String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
211
212 PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
213
214 ps.setNull(1, Types.VARCHAR);
215
216 ps.execute();
217 }
218 {{/code}}
219
220 To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:
221
222 {{code language="html/xml"}}
223 <!-- SchemaManager bean definition starts here ... -->
224 <bp:property name="upateSnippets">
225 <bp:map>
226 <bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
227 <!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
228 </bp:map>
229 </bp:property>
230 <!-- ... and continues here -->
231 {{/code}}
232
233
234
235
236
237
238
239
240
241
242
243