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 3.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 07.03:35
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -47,7 +47,7 @@
47 47  <bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
48 48  <bp:property name="baseVersion" value="0.1.00" />
49 49  <bp:property name="baseTables">
50 -<!-- Add List of TableDefinitions here -->
50 +<!-- Add List of TableDefinitions here (see below) -->
51 51  </bp:property>
52 52  <bp:property name="upateSnippets">
53 53  <!-- Add Update-Snippets here -->
... ... @@ -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 */
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
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 - /* ... */
85 + // ...
87 87   
88 88   private List<TableInfo> setup;
89 89  
90 90   public TableDefinitions() {
91 -/* Create a table */
90 +// 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 */
100 +// 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 */
105 +// Example for creating a primary key
107 107  exampleTable.setPrimaryKey(
108 108  new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
109 109  );
110 110  
111 -/* ... */
110 +// ...
112 112   
113 113  this.setup = Arrays.asList(
114 114  exampleTable,
115 -/* ... */
114 +// ...
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:
128 +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 - 
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 -->
133 133  {{/code}}
134 134  
135 -= {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithSchemaUpdateSnippets"/}}Updating a database schema with SchemaUpdateSnippets =
138 += {{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 -
142 +An example for an implementation of a schema update snippet could look like this:
140 140  
141 -
144 +{{code language="java"}}
145 +package org.clazzes.example.jdbc2xml.updates;
142 142  
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 +
143 143  
144 144  
145 145  
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656809
1 +656821
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/656821/How To create and update Databases using SchemaManager and SchemaUpdateSnippets