Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 2.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 06.29:38
Change comment: There is no comment for this version
To version 4.1
edited by christoph_lechleitner@iteg_at
on 2013-01-31 04.25:51
Change comment: Name of schema history table configurable now, and text refactoring

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.5fbc055b7cc103006957e1ae
1 +XWiki.christoph_lechleitner@iteg_at
Content
... ... @@ -1,9 +1,11 @@
1 1  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Preliminary"/}}Preliminary =
2 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.
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 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:
5 +==== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-SchemaHistoryTable"/}}Schema History Table ====
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:
8 +
7 7  {{code language="none"}}
8 8  VERSION:varchar(10), not null, primary key
9 9  DESCRIPTION:varchar(512), nullable
... ... @@ -11,17 +11,21 @@
11 11  SERIALNR:integer(5), not null
12 12  {{/code}}
13 13  
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 +
14 14  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ProjectConfiguration"/}}Project Configuration =
15 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}}).
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.
17 17  
18 -Optionally, you may set the base version (default value 0.1.00) and base description String (default "initial database schema").
24 +Optionally, a base version (default value 0.1.00) and a base description String (default "initial database schema") may be specified.
19 19  
20 20  Database updates are passed as a Map<String, ISchemaUpdateSnippet> (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) - details see below.
21 21  
22 22  To perform the operations, call {{code language="none"}}SchemaManager.start(){{/code}}.
23 23  
24 -=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UsingSpringorOSGi/Blueprint"/}}Using Spring or OSGi/Blueprint ===
30 +=== {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-ConfigurationusingSpringorOSGi/Blueprint"/}}Configuration using Spring or OSGi/Blueprint ===
25 25  
26 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 27  
... ... @@ -33,7 +33,7 @@
33 33  </bp:reference>
34 34   
35 35  <bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
36 -<bp:argument> <!-- Pass JDBC URL as an argument -->
42 +<bp:argument ref="jdbcUrl"><!-- bean jdbcUrl specified above -->
37 37  </bp:argument>
38 38  </bp:bean>
39 39   
... ... @@ -45,22 +45,32 @@
45 45  <bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
46 46  <bp:property name="dataSource" ref="dataSource"></bp:property>
47 47  <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 -->
48 48  <bp:property name="baseVersion" value="0.1.00" />
49 49  <bp:property name="baseTables">
50 -<!-- Add List of TableDefinitions here -->
59 +<!-- List of TableDefinitions here (see below), typical: -->
60 + <!-- <bp:bean factory-ref="tableDefinitions" factory-method="getSetup" /> -->
51 51  </bp:property>
52 52  <bp:property name="upateSnippets">
53 53  <!-- 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> -->
54 54  </bp:property>
55 55  </bp:bean>
56 56  {{/code}}
57 57  
58 -By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry lookup for Spring.
70 +By default, JDBC2XML provides an implementation of IDialectFactory and ISchemaEngineFactory as an OSGi service or via ServiceRegistry for Spring.
59 59  
60 60  = {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-Settingupaninitialdatabaseschema"/}}Setting up an initial database schema =
61 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:
74 +To create an initial database schema, SchemaManager needs a list of TableInfo objects.
63 63  
76 +The recommended strategy is to implement a table definition class providing this list through a getter.
77 +
78 +This is an example:
79 +
64 64  {{code language="java"}}
65 65  package org.clazzes.example.jdbc2xml;
66 66  
... ... @@ -75,8 +75,7 @@
75 75   
76 76  public class TableDefinitions {
77 77  
78 - /* It is adviseable to provide the Strings used as names for tables and columns as
79 - constants, so they can be reused to build sql-statements */
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
80 80   public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
81 81   public static final String COL_EXAMPLE_ID = "ID";
82 82   public static final String COL_EXAMPLE_NAME = "NAME";
... ... @@ -83,12 +83,12 @@
83 83   public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
84 84   public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
85 85   
86 - /* ... */
101 + // ...
87 87   
88 88   private List<TableInfo> setup;
89 89  
90 90   public TableDefinitions() {
91 -/* Create a table */
106 +// Create a table
92 92  TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
93 93  exampleTable.setColumns(
94 94   Arrays.asList(new ColumnInfo[] {
... ... @@ -98,21 +98,21 @@
98 98  new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
99 99  }));
100 100   
101 -/* Example for creating a foreign key reference */
116 +// Example for creating a foreign key reference
102 102  exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
103 103  new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
104 104  }));
105 105   
106 -/* Example for creating a primary key */
121 +// Example for creating a primary key
107 107  exampleTable.setPrimaryKey(
108 108  new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
109 109  );
110 110  
111 -/* ... */
126 +// ...
112 112   
113 113  this.setup = Arrays.asList(
114 114  exampleTable,
115 -/* ... */
130 +// ...
116 116  );
117 117  
118 118   }
... ... @@ -126,20 +126,111 @@
126 126  
127 127  You must inject {{code language="none"}}TableDefinitions.getSetup(){{/code}} into {{code language="none"}}SchemaManager.setBaseTables(){{/code}} before calling {{code language="none"}}SchemaManager.start(){{/code}}.
128 128  
129 -Using Blueprint/Spring, you can do this with the following snippet:
144 +Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
130 130  
131 -{{code language="none"}}
132 - 
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 -->
133 133  {{/code}}
134 134  
135 -= {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithSchemaUpdateSnippets"/}}Updating a database schema with SchemaUpdateSnippets =
154 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
136 136  
137 137  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.
138 138  
139 -
158 +An example for an implementation of a schema update snippet could look like this:
140 140  
141 -
160 +{{code language="java"}}
161 +package org.clazzes.example.jdbc2xml.updates;
142 142  
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 +
143 143  
144 144  
145 145  
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656809
1 +656825
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656809/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656825/How To create and update Databases using SchemaManager and SchemaUpdateSnippets