3 * Copyright (C) 2009 EDIT
4 * European Distributed Institute of Taxonomy
5 * http://www.e-taxonomy.eu
7 * The contents of this file are subject to the Mozilla Public License Version 1.1
8 * See LICENSE.TXT at the top of this package for the full license terms.
10 package eu
.etaxonomy
.cdm
.database
.update
;
12 import java
.sql
.SQLException
;
13 import java
.util
.ArrayList
;
14 import java
.util
.Arrays
;
15 import java
.util
.List
;
17 import org
.apache
.commons
.lang
.StringUtils
;
18 import org
.apache
.log4j
.Logger
;
20 import eu
.etaxonomy
.cdm
.common
.monitor
.IProgressMonitor
;
21 import eu
.etaxonomy
.cdm
.database
.DatabaseTypeEnum
;
22 import eu
.etaxonomy
.cdm
.database
.ICdmDataSource
;
29 public class TableCreator
extends SchemaUpdaterStepBase
<TableCreator
> implements ISchemaUpdaterStep
{
30 private static final Logger logger
= Logger
.getLogger(TableCreator
.class);
32 private static final boolean SORT_INDEX
= true;
34 protected String tableName
;
35 private List
<String
> columnNames
;
36 private List
<String
> columnTypes
;
37 private List
<Object
> defaultValues
;
38 private List
<Boolean
> isNotNull
;
39 private List
<String
> referencedTables
;
40 private boolean includeAudTable
;
41 private boolean includeCdmBaseAttributes
;
42 private boolean includeIdentifiableEntity
;
43 protected List
<ColumnAdder
> columnAdders
= new ArrayList
<ColumnAdder
>();
44 protected List
<ISchemaUpdaterStep
> mnTablesStepList
= new ArrayList
<ISchemaUpdaterStep
>();
45 private String primaryKeyParams
;
46 private String primaryKeyParams_AUD
;
47 private String uniqueParams
;
48 private String uniqueParams_AUD
;
51 // public static final TableCreator NewInstance(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, List<Object> defaultValues, List<Boolean> isNull, boolean includeAudTable){
52 public static final TableCreator
NewInstance(String stepName
, String tableName
, List
<String
> columnNames
, List
<String
> columnTypes
, boolean includeAudTable
, boolean includeCdmBaseAttributes
){
53 return new TableCreator(stepName
, tableName
, columnNames
, columnTypes
, null, null, null, includeAudTable
, includeCdmBaseAttributes
, false);
56 public static final TableCreator
NewInstance(String stepName
, String tableName
, String
[] columnNames
, String
[] columnTypes
, String
[] referencedTables
, boolean includeAudTable
, boolean includeCdmBaseAttributes
){
57 return new TableCreator(stepName
, tableName
, Arrays
.asList(columnNames
), Arrays
.asList(columnTypes
), null, null, Arrays
.asList(referencedTables
), includeAudTable
, includeCdmBaseAttributes
, false);
60 public static final TableCreator
NewIdentifiableInstance(String stepName
, String tableName
, String
[] columnNames
, String
[] columnTypes
, String
[] referencedTables
, boolean includeAudTable
){
61 return new TableCreator(stepName
, tableName
, Arrays
.asList(columnNames
), Arrays
.asList(columnTypes
), null, null, Arrays
.asList(referencedTables
), includeAudTable
, true, true);
64 protected TableCreator(String stepName
, String tableName
, List
<String
> columnNames
, List
<String
> columnTypes
, List
<Object
> defaultValues
, List
<Boolean
> isNotNull
, List
<String
> referencedTables
, boolean includeAudTable
, boolean includeCdmBaseAttributes
, boolean includeIdentifiableEntity
) {
66 this.tableName
= tableName
;
67 this.columnNames
= columnNames
;
68 this.columnTypes
= columnTypes
;
69 this.defaultValues
= defaultValues
;
70 this.isNotNull
= isNotNull
;
71 this.referencedTables
= referencedTables
;
72 this.includeAudTable
= includeAudTable
;
73 this.includeCdmBaseAttributes
= includeCdmBaseAttributes
;
74 this.includeIdentifiableEntity
= includeIdentifiableEntity
;
80 private void makeColumnAdders() {
81 if (columnNames
.size() != columnTypes
.size()){
82 throw new RuntimeException ("ColumnNames and columnTypes must be of same size. Step: " + getStepName());
85 for (int i
= 0; i
< columnNames
.size(); i
++){
86 boolean isNotNull
= this.isNotNull
== null ?
false : this.isNotNull
.get(i
);
87 if ("integer".equals(columnTypes
.get(i
)) || "int".equals(columnTypes
.get(i
))){
88 String referencedTable
= (this.referencedTables
== null) ?
null : this.referencedTables
.get(i
);
89 ColumnAdder adder
= ColumnAdder
.NewIntegerInstance(this.getStepName(), this.tableName
, this.columnNames
.get(i
), includeAudTable
, isNotNull
, referencedTable
);
90 this.columnAdders
.add(adder
);
91 }else if ("boolean".equals(columnTypes
.get(i
)) || "bit".equals(columnTypes
.get(i
))){
92 ColumnAdder adder
= ColumnAdder
.NewBooleanInstance(getStepName(), this.tableName
, this.columnNames
.get(i
), includeAudTable
, Boolean
.valueOf(this.defaultValues
.get(i
).toString()));
93 this.columnAdders
.add(adder
);
94 }else if (columnTypes
.get(i
).startsWith("string")){
95 Integer length
= Integer
.valueOf(columnTypes
.get(i
).substring("string_".length()));
96 ColumnAdder adder
= ColumnAdder
.NewStringInstance(this.getStepName(), this.tableName
, this.columnNames
.get(i
), length
, includeAudTable
);
97 this.columnAdders
.add(adder
);
98 }else if ("tinyint".equals(columnTypes
.get(i
)) ){
99 ColumnAdder adder
= ColumnAdder
.NewTinyIntegerInstance(this.getStepName(), this.tableName
, this.columnNames
.get(i
), includeAudTable
, isNotNull
);
100 this.columnAdders
.add(adder
);
107 * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase#invoke(eu.etaxonomy.cdm.database.ICdmDataSource, eu.etaxonomy.cdm.common.IProgressMonitor)
110 public Integer
invoke(ICdmDataSource datasource
, IProgressMonitor monitor
) throws SQLException
{
112 boolean result
= true;
113 result
&= createTable(tableName
, false, datasource
, monitor
);
114 if (includeAudTable
){
116 result
&= createTable(tableName
+ aud
, true, datasource
, monitor
);
118 // result &= invokeMns();
119 return (result
== true )?
0 : null;
120 } catch (DatabaseTypeNotSupportedException e
) {
121 throw new SQLException(e
);
128 public List
<ISchemaUpdaterStep
> getInnerSteps() {
129 return mnTablesStepList
;
132 private String
getColumnsSql(String tableName
, ICdmDataSource datasource
, IProgressMonitor monitor
) throws DatabaseTypeNotSupportedException
{
134 for (ColumnAdder adder
: this.columnAdders
){
135 String singleAdderSQL
= adder
.getUpdateQueryString(tableName
, datasource
, monitor
) + ", ";
137 String
[] split
= singleAdderSQL
.split(ColumnAdder
.getAddColumnSeperator(datasource
));
143 private boolean createTable(String tableName
, boolean isAudit
, ICdmDataSource datasource
, IProgressMonitor monitor
) throws DatabaseTypeNotSupportedException
{
144 boolean result
= true;
145 String updateQuery
= "CREATE TABLE @tableName (";
147 updateQuery
+= " REV integer not null, revtype tinyint, ";
149 if (includeCdmBaseAttributes
){
150 updateQuery
+= " id integer not null,"
151 + " created datetime, "
152 + " uuid varchar(36),"
153 + " updated datetime, "
154 + " createdby_id integer,"
155 + " updatedby_id integer, ";
158 if (this.includeIdentifiableEntity
){
159 updateQuery
+= "lsid_authority varchar(255), lsid_lsid varchar(255), lsid_namespace varchar(255), lsid_object varchar(255), lsid_revision varchar(255), protectedtitlecache bit not null, titleCache varchar(255),";
162 updateQuery
+= getColumnsSql(tableName
, datasource
, monitor
);
164 String primaryKeySql
= primaryKey(isAudit
)==null ?
"" : "primary key (" + primaryKey(isAudit
) + "),";
165 String uniqueSql
= unique(isAudit
)== null ?
"" : "unique(" + unique(isAudit
) + "),";
166 updateQuery
+= primaryKeySql
+ uniqueSql
;
168 updateQuery
= StringUtils
.chomp(updateQuery
.trim(), ",");
171 updateQuery
= updateQuery
.replace("@tableName", tableName
);
172 if (datasource
.getDatabaseType().equals(DatabaseTypeEnum
.MySQL
)){
173 updateQuery
+= " ENGINE=MYISAM DEFAULT CHARSET=utf8 ";
175 logger
.debug(updateQuery
);
177 datasource
.executeUpdate(updateQuery
);
178 } catch (SQLException e
) {
182 result
&= createForeignKeys(tableName
, isAudit
, datasource
, monitor
);
187 private void makeMnTables() {
188 if (this.includeIdentifiableEntity
){
189 TableCreator tableCreator
;
191 stepName
= "Add @tableName annotations";
192 stepName
= stepName
.replace("@tableName", this.tableName
);
193 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, "Annotation", SchemaUpdaterBase
.INCLUDE_AUDIT
);
194 mnTablesStepList
.add(tableCreator
);
197 stepName
= "Add @tableName credits";
198 stepName
= stepName
.replace("@tableName", this.tableName
);
199 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, null, "Credit", null, SchemaUpdaterBase
.INCLUDE_AUDIT
, SORT_INDEX
, false);
200 mnTablesStepList
.add(tableCreator
);
203 stepName
= "Add @tableName extensions";
204 stepName
= stepName
.replace("@tableName", this.tableName
);
205 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, "Extension", SchemaUpdaterBase
.INCLUDE_AUDIT
);
206 mnTablesStepList
.add(tableCreator
);
209 stepName
= "Add @tableName marker";
210 stepName
= stepName
.replace("@tableName", this.tableName
);
211 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, "Marker", SchemaUpdaterBase
.INCLUDE_AUDIT
);
212 mnTablesStepList
.add(tableCreator
);
215 stepName
= "Add @tableName sources";
216 stepName
= stepName
.replace("@tableName", this.tableName
);
217 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, null, "OriginalSourceBase", "sources", SchemaUpdaterBase
.INCLUDE_AUDIT
, false, true);
218 mnTablesStepList
.add(tableCreator
);
221 stepName
= "Add @tableName rights";
222 stepName
= stepName
.replace("@tableName", this.tableName
);
223 tableCreator
= MnTableCreator
.NewMnInstance(stepName
, this.tableName
, "Rights", SchemaUpdaterBase
.INCLUDE_AUDIT
);
224 mnTablesStepList
.add(tableCreator
);
230 private boolean createForeignKeys(String tableName
, boolean isAudit
, ICdmDataSource datasource
, IProgressMonitor monitor
) {
231 boolean result
= true;
232 if (includeCdmBaseAttributes
){
233 String attribute
= "updatedby";
234 String referencedTable
= "UserAccount";
235 result
&= makeForeignKey(tableName
, datasource
, attribute
, referencedTable
);
237 attribute
= "createdby";
238 referencedTable
= "UserAccount";
239 result
&= makeForeignKey(tableName
, datasource
, attribute
, referencedTable
);
243 String attribute
= "REV";
244 String referencedTable
= "AuditEvent";
245 result
&= makeForeignKey(tableName
, datasource
, attribute
, referencedTable
);
247 for (ColumnAdder adder
: this.columnAdders
){
248 if (adder
.getReferencedTable() != null){
249 result
&= makeForeignKey(tableName
, datasource
, adder
.getNewColumnName(), adder
.getReferencedTable());
255 public static boolean makeForeignKey(String tableName
, ICdmDataSource datasource
, String attribute
, String referencedTable
) {
256 boolean result
= true;
257 String index
= "FK@tableName_@attribute";
258 index
= index
.replace("@tableName", tableName
);
259 index
= index
.replace("@attribute", attribute
);
261 String idSuffix
= "_id";
262 if ("REV".equalsIgnoreCase(attribute
) || attribute
.endsWith(idSuffix
)){
265 String updateQuery
= "ALTER TABLE @tableName ADD INDEX @index (@attribute), ADD CONSTRAINT @index FOREIGN KEY (@attribute) REFERENCES @referencedTable (id)";
266 updateQuery
= updateQuery
.replace("@tableName", tableName
);
267 updateQuery
= updateQuery
.replace("@index", index
);
268 updateQuery
= updateQuery
.replace("@attribute", attribute
+ idSuffix
);
269 updateQuery
= updateQuery
.replace("@referencedTable", referencedTable
);
271 logger
.debug(updateQuery
);
273 datasource
.executeUpdate(updateQuery
);
274 } catch (SQLException e
) {
282 protected String
primaryKey(boolean isAudit
){
283 String result
= null;
284 if (! isAudit
&& this.primaryKeyParams
!= null){
285 return this.primaryKeyParams
;
286 }else if (isAudit
&& this.primaryKeyParams_AUD
!= null){
287 return this.primaryKeyParams_AUD
;
290 if (includeCdmBaseAttributes
|| ! includeCdmBaseAttributes
){ //TODO how to handle not CDMBase includes
300 protected String
unique(boolean isAudit
){
302 if (this.uniqueParams
!= null){
303 return this.uniqueParams
;
305 if (includeCdmBaseAttributes
){
306 return "uuid"; //TODO how to handle not CDMBase includes
310 if (this.uniqueParams_AUD
!= null){
311 return this.uniqueParams_AUD
;
317 public void setPrimaryKeyParams(String primaryKeyParams
, String primaryKeyParams_AUD
) {
318 this.primaryKeyParams
= primaryKeyParams
;
319 this.primaryKeyParams_AUD
= primaryKeyParams_AUD
;
322 public void setUniqueParams(String uniqueParams
, String uniqueParams_AUD
) {
323 this.uniqueParams
= uniqueParams
;
324 this.uniqueParams_AUD
= uniqueParams_AUD
;