Skip to Content
Last modified by christoph_lechleitner@iteg_at on 2013-01-31 07.32:56
From version 3.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 07.03:35
Change comment: There is no comment for this version
To version 2.1
edited by 5fbc055b7cc103006957e1ae
on 2012-06-14 06.29:38
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 (see below) -->
50 +<!-- Add List of TableDefinitions here -->
51 51  </bp:property>
52 52  <bp:property name="upateSnippets">
53 53  <!-- Add Update-Snippets here -->
... ... @@ -75,7 +75,8 @@
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 constants, so they can be reused outside this object to build sql-statements
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 */
79 79   public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
80 80   public static final String COL_EXAMPLE_ID = "ID";
81 81   public static final String COL_EXAMPLE_NAME = "NAME";
... ... @@ -82,12 +82,12 @@
82 82   public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
83 83   public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
84 84   
85 - // ...
86 + /* ... */
86 86   
87 87   private List<TableInfo> setup;
88 88  
89 89   public TableDefinitions() {
90 -// Create a table
91 +/* Create a table */
91 91  TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
92 92  exampleTable.setColumns(
93 93   Arrays.asList(new ColumnInfo[] {
... ... @@ -97,21 +97,21 @@
97 97  new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
98 98  }));
99 99   
100 -// Example for creating a foreign key reference
101 +/* Example for creating a foreign key reference */
101 101  exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
102 102  new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
103 103  }));
104 104   
105 -// Example for creating a primary key
106 +/* Example for creating a primary key */
106 106  exampleTable.setPrimaryKey(
107 107  new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
108 108  );
109 109  
110 -// ...
111 +/* ... */
111 111   
112 112  this.setup = Arrays.asList(
113 113  exampleTable,
114 -// ...
115 +/* ... */
115 115  );
116 116  
117 117   }
... ... @@ -125,111 +125,20 @@
125 125  
126 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 127  
128 -Using Blueprint/Spring, you can do this by inserting the following snippet in the bean definition for SchemaManager:
129 +Using Blueprint/Spring, you can do this with the following snippet:
129 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 -->
131 +{{code language="none"}}
132 + 
136 136  {{/code}}
137 137  
138 -= {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithISchemaUpdateSnippet"/}}Updating a database schema with ISchemaUpdateSnippet =
135 += {{id name="HowTocreateandupdateDatabasesusingSchemaManagerandSchemaUpdateSnippets-UpdatingadatabaseschemawithSchemaUpdateSnippets"/}}Updating a database schema with SchemaUpdateSnippets =
139 139  
140 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 141  
142 -An example for an implementation of a schema update snippet could look like this:
139 +
143 143  
144 -{{code language="java"}}
145 -package org.clazzes.example.jdbc2xml.updates;
141 +
146 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 233  
234 234  
235 235  
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -656821
1 +656809
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656821/How To create and update Databases using SchemaManager and SchemaUpdateSnippets
1 +https://clazzes.atlassian.net/wiki/spaces/JDBC2XML/pages/656809/How To create and update Databases using SchemaManager and SchemaUpdateSnippets