BGBM BDI Projects / cdmlib.git / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
include more auditing in schema update and refactor to better allow transaction suppo...
[cdmlib.git] / cdmlib-persistence / src / main / java / eu / etaxonomy / cdm / database / update / v31_33 / SchemaUpdater_31_33.java
1 // $Id$
2 /**
3 * Copyright (C) 2007 EDIT
4 * European Distributed Institute of Taxonomy
5 * http://www.e-taxonomy.eu
6 *
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.
9 */
10
11 package eu.etaxonomy.cdm.database.update.v31_33;
12
13 import java.util.ArrayList;
14 import java.util.List;
15
16 import org.apache.log4j.Logger;
17
18 import com.sun.tools.xjc.reader.gbind.Sequence;
19
20 import eu.etaxonomy.cdm.database.update.ColumnAdder;
21 import eu.etaxonomy.cdm.database.update.ColumnNameChanger;
22 import eu.etaxonomy.cdm.database.update.ColumnRemover;
23 import eu.etaxonomy.cdm.database.update.ColumnTypeChanger;
24 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
25 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
26 import eu.etaxonomy.cdm.database.update.MnTableCreator;
27 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
28 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
29 import eu.etaxonomy.cdm.database.update.SortIndexUpdater;
30 import eu.etaxonomy.cdm.database.update.TableCreator;
31 import eu.etaxonomy.cdm.database.update.TableDroper;
32 import eu.etaxonomy.cdm.database.update.TableNameChanger;
33 import eu.etaxonomy.cdm.database.update.TreeIndexUpdater;
34 import eu.etaxonomy.cdm.database.update.v30_31.SchemaUpdater_30_301;
35 import eu.etaxonomy.cdm.model.common.AnnotationType;
36 import eu.etaxonomy.cdm.model.common.ExtensionType;
37 import eu.etaxonomy.cdm.model.common.Language;
38 import eu.etaxonomy.cdm.model.common.MarkerType;
39 import eu.etaxonomy.cdm.model.common.MaterialAndMethod;
40 import eu.etaxonomy.cdm.model.common.OriginalSourceType;
41 import eu.etaxonomy.cdm.model.common.TermType;
42 import eu.etaxonomy.cdm.model.description.Feature;
43 import eu.etaxonomy.cdm.model.description.MeasurementUnit;
44 import eu.etaxonomy.cdm.model.description.NaturalLanguageTerm;
45 import eu.etaxonomy.cdm.model.description.State;
46 import eu.etaxonomy.cdm.model.description.StatisticalMeasure;
47 import eu.etaxonomy.cdm.model.description.TextFormat;
48 import eu.etaxonomy.cdm.model.location.NamedArea;
49 import eu.etaxonomy.cdm.model.location.NamedAreaLevel;
50 import eu.etaxonomy.cdm.model.location.NamedAreaType;
51 import eu.etaxonomy.cdm.model.location.ReferenceSystem;
52 import eu.etaxonomy.cdm.model.location.WaterbodyOrCountry;
53 import eu.etaxonomy.cdm.model.name.HybridRelationshipType;
54 import eu.etaxonomy.cdm.model.name.NameRelationshipType;
55 import eu.etaxonomy.cdm.model.name.NameTypeDesignationStatus;
56 import eu.etaxonomy.cdm.model.name.NomenclaturalStatusType;
57 import eu.etaxonomy.cdm.model.name.Rank;
58 import eu.etaxonomy.cdm.model.name.SpecimenTypeDesignationStatus;
59 import eu.etaxonomy.cdm.model.occurrence.DerivationEventType;
60 import eu.etaxonomy.cdm.model.occurrence.DerivedUnit;
61 import eu.etaxonomy.cdm.model.occurrence.PreservationMethod;
62 import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationType;
63 import eu.etaxonomy.cdm.model.reference.Reference;
64 import eu.etaxonomy.cdm.model.reference.ReferenceType;
65 import eu.etaxonomy.cdm.model.taxon.SynonymRelationshipType;
66 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
67
68
69 /**
70 * @author a.mueller
71 * @created Jun 06, 2013
72 */
73 public class SchemaUpdater_31_33 extends SchemaUpdaterBase {
74
75 private static final Logger logger = Logger.getLogger(SchemaUpdater_31_33.class);
76 private static final String startSchemaVersion = "3.0.1.0.201104190000";
77 private static final String endSchemaVersion = "3.3.0.0.201308010000";
78
79 // ********************** FACTORY METHOD *******************************************
80
81 public static SchemaUpdater_31_33 NewInstance(){
82 return new SchemaUpdater_31_33();
83 }
84
85 /**
86 * @param startSchemaVersion
87 * @param endSchemaVersion
88 */
89 protected SchemaUpdater_31_33() {
90 super(startSchemaVersion, endSchemaVersion);
91 }
92
93 /* (non-Javadoc)
94 * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getUpdaterList()
95 */
96 @Override
97 protected List<ISchemaUpdaterStep> getUpdaterList() {
98
99 String stepName;
100 String tableName;
101 ISchemaUpdaterStep step;
102 String columnName;
103
104 //CHECKS
105
106 //remove SpecimenOrObservationBase_Media #3597
107 //TODO check if SpecimenOrObservationBase_Media has data => move to first position, don't run update if data exists
108 //TODO check if Description -Specimen Relation has M:M data
109 if (false){
110 throw new RuntimeException("Required check for SpecimenOrObservationBase_Media");
111 }else{
112 logger.warn("CHECKS for inconsistent data not running !!!!");
113 }
114
115
116 List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
117
118 //TODO Does it throw exception if table does not exist?
119 //Was in Schemaupdater_301_31 which was never used and later deleted (r18331).
120 //drop TypeDesignationBase_TaxonNameBase //from schemaUpdater 301_31
121 stepName = "Drop duplicate TypeDesignation-TaxonName table";
122 tableName = "TypeDesignationBase_TaxonNameBase";
123 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
124 stepList.add(step);
125
126 //create original source type column
127 stepName = "Create original source type column";
128 tableName = "OriginalSourceBase";
129 columnName = "sourceType";
130 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT);
131 ((ColumnAdder)step).setNotNull(true);
132 stepList.add(step);
133
134 //update original source type
135 updateOriginalSourceType(stepList);
136
137 //create and update elevenation max, remove error column
138 updateElevationMax(stepList);
139
140 //create TaxonNode tree index
141 stepName = "Create taxon node tree index";
142 tableName = "TaxonNode";
143 columnName = "treeIndex";
144 //TODO NOT NULL unclear //see also columnTypeChanger
145 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
146 stepList.add(step);
147
148 //update treeindex for taxon nodes
149 stepName = "Update TaxonNode treeindex";
150 tableName = "TaxonNode";
151 String treeIdColumnName = "classification_id";
152 step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT);
153 stepList.add(step);
154
155 //create TaxonNode sort index column
156 stepName = "Create taxon node sort index column";
157 tableName = "TaxonNode";
158 columnName = "sortIndex";
159 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
160 stepList.add(step);
161
162 //TODO implement sorted behaviour in model first !!
163 //update sortindex
164 stepName = "Update sort index on TaxonNode children";
165 tableName = "TaxonNode";
166 String parentIdColumn = "parent_id";
167 String sortIndexColumn = "sortIndex";
168 SortIndexUpdater updateSortIndex = SortIndexUpdater.NewInstance(stepName, tableName, parentIdColumn, sortIndexColumn, INCLUDE_AUDIT);
169 stepList.add(updateSortIndex);
170
171
172 //create feature node tree index
173 stepName = "Create feature node tree index";
174 tableName = "FeatureNode";
175 columnName = "treeIndex";
176 //TODO NOT NULL unclear
177 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
178 stepList.add(step);
179
180 //update tree index for feature node
181 stepName = "Update FeatureNode treeindex";
182 tableName = "FeatureNode";
183 treeIdColumnName = "featuretree_id";
184 step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT);
185 stepList.add(step);
186
187 //update introduced: adventitious (casual) label
188 //#3540
189 stepName = "Update introduced: adventitious (casual) label";
190 String query = " UPDATE Representation r " +
191 " SET r.abbreviatedlabel = 'ia' " +
192 " WHERE r.abbreviatedlabel = 'id' AND r.label = 'introduced: adventitious (casual)' ";
193 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation");
194 stepList.add(step);
195
196 //termType for DefinedTerms and TermVocabulary, no type must be null
197 stepName = "Create termType column in DefinedTermBase";
198 tableName = "DefinedTermBase";
199 columnName = "termType";
200 //TODO NOT NULL unclear
201 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
202 stepList.add(step);
203
204 stepName = "Create termType column in TermVocabulary";
205 tableName = "TermVocabulary";
206 columnName = "termType";
207 //TODO NOT NULL unclear
208 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT);
209 stepList.add(step);
210
211
212 //update termType for DefinedTerms, no type must be null
213 updateTermTypesForTerms(stepList);
214
215 //update termType for TermVocabulary, no type must be null
216 updateTermTypesForVocabularies(stepList);
217
218 //update DTYPE of DefinedTerms
219 updateDtypeOfDefinedTerms(stepList);
220
221 //idInVocabulary for DefinedTerms
222 stepName = "Create idInVocabulary column in DefinedTermBase";
223 tableName = "DefinedTermBase";
224 columnName = "idInVocabulary";
225 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
226 stepList.add(step);
227
228 //update idInVocabulary
229 updateIdInVocabulary(stepList);
230
231 //rankClass (#3521)
232 stepName = "Create rankClass column in DefinedTermBase";
233 tableName = "DefinedTermBase";
234 columnName = "rankClass";
235 //TODO NOT NULL unclear
236 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
237 stepList.add(step);
238
239 //update rankClass (#3521)
240 step = RankClassUpdater.NewInstance();
241 stepList.add(step);
242
243 //update datatype->CLOB for URIs. (DefinedTerms, TermVocabulary, Reference, Rights, MediaRepresentationPart )
244 //#3345, TODO adapt type to <65k
245 //TODO sequence.sequence has been changed #3360
246 changeUriType(stepList);
247
248 //Annotation.linkbackUri change name #3374
249 stepName = "Update url to uri (->clob) for Annotation.linkbackUri";
250 columnName = "linkbackUrl";
251 String newColumnName = "linkbackUri";
252 tableName = "Annotation";
253 //TODO check non MySQL and with existing data (probably does not exist)
254 step = ColumnNameChanger.NewClobInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
255 stepList.add(step);
256
257 //update Sicilia -> Sicily
258 //#3540
259 stepName = "Update Sicilia -> Sicily";
260 query = " UPDATE Representation r " +
261 " SET r.label = 'Sicily', r.text = 'Sicily' " +
262 " WHERE (r.abbreviatedlabel = 'SIC-SI' OR r.abbreviatedlabel = 'SIC') AND r.label = 'Sicilia' ";
263 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation");
264 stepList.add(step);
265
266 //remove homotypical group form type designation base
267 stepName = "Remove column homotypical group in type designation base";
268 tableName = "TypeDesignationBase";
269 String oldColumnName = "homotypicalgroup_id";
270 step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT);
271 stepList.add(step);
272
273 //add publish flag to taxon
274 stepName = "Add publish flag column to taxon base";
275 tableName = "TaxonBase";
276 columnName = "publish";
277 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
278 stepList.add(step);
279
280 //add publish flag to specimen
281 stepName = "Add publish flag column to SpecimenOrObservationBase";
282 tableName = "SpecimenOrObservationBase";
283 columnName = "publish";
284 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
285 stepList.add(step);
286
287 //add columns abbrevTitle, abbrevTitleCache and protectedAbbrevTitleCache to Reference
288 stepName = "Add abbrevTitle to Reference";
289 tableName = "Reference";
290 columnName = "abbrevTitle";
291 int length = 255;
292 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
293 stepList.add(step);
294
295 stepName = "Add abbrevTitleCache to Reference";
296 tableName = "Reference";
297 columnName = "abbrevTitleCache";
298 length = 1023;
299 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
300 stepList.add(step);
301
302 stepName = "Add protectedAbbrevTitleCache to Reference";
303 tableName = "Reference";
304 columnName = "protectedAbbrevTitleCache";
305 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
306 stepList.add(step);
307
308 //update abbrevTitle, protectedAbbrevTitle and abbrevTitleCache in Reference
309 updateAbbrevTitle(stepList);
310
311 //add doi to reference
312 stepName = "Add doi to Reference";
313 tableName = "Reference";
314 columnName = "doi";
315 length = 255;
316 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
317 stepList.add(step);
318
319
320 //add start number to PolytomousKey
321 stepName = "Add start number column to PolytomousKey";
322 tableName = "PolytomousKey";
323 columnName = "startNumber";
324 Integer defaultValue = 1;
325 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, true);
326 stepList.add(step);
327
328 //add recordBasis to specimenOrObservationBase
329 stepName = "Add recordBasis to SpecimenOrObservationBase";
330 tableName = "SpecimenOrObservationBase";
331 columnName = "recordBasis";
332 length = 4; //TODO needed?
333 //TODO NOT NULL
334 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
335 stepList.add(step);
336
337 //update recordBasis
338 updateRecordBasis(stepList);
339
340 //update specimenOrObservationBase DTYPE with DerivedUnit where necessary
341 stepName = "Update Specimen -> DerivedUnit";
342 query = " UPDATE SpecimenOrObservationBase sob " +
343 " SET sob.DTYPE = 'DerivedUnit' " +
344 " WHERE sob.DTYPE = 'Specimen' OR sob.DTYPE = 'Fossil' OR sob.DTYPE = 'LivingBeing' OR sob.DTYPE = 'Observation' ";
345 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("SpecimenOrObservationBase");
346 stepList.add(step);
347
348 //update DTYPE FieldObservation -> FieldUnit #3351
349 stepName = "Update FieldObservation -> FieldUnit";
350 query = " UPDATE SpecimenOrObservationBase sob " +
351 " SET sob.DTYPE = 'FieldUnit' " +
352 " WHERE sob.DTYPE = 'FieldObservation' ";
353 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("SpecimenOrObservationBase");
354 stepList.add(step);
355
356 //add kindOfUnit to SpecimenOrObservationBase
357 stepName = "Add kindOfUnit column to SpecimenOrObservationBase";
358 tableName = "SpecimenOrObservationBase";
359 columnName = "kindOfUnit_id";
360 String relatedTable = "DefinedTermBase";
361 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, relatedTable);
362 stepList.add(step);
363
364 //remove citation_id and citation micro-reference columns from Media table #2541
365 //FIXME first check if columns are always empty
366 stepName = "Remove citation column from Media";
367 tableName = "Media";
368 columnName = "citation_id";
369 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
370 stepList.add(step);
371
372 stepName = "Remove citation microreference column from Media";
373 tableName = "Media";
374 columnName = "citationMicroReference";
375 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
376 stepList.add(step);
377
378 //update length of all title caches and full title cache in names #1592
379 updateTitleCacheLength(stepList);
380
381 //rename FK column states_id -> stateData_id in DescriptionElementBase_StateData(+AUD) #2923
382 stepName = "Update states_id to stateData_id in DescriptionElementBase_StateData";
383 tableName = "DescriptionElementBase_StateData";
384 oldColumnName = "states_id";
385 newColumnName = "stateData_id";
386 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
387 stepList.add(step);
388
389 //specimen descriptions #3571
390 //add column DescriptionBase.Specimen_ID #3571
391 stepName = "Add specimen_id column to DescriptionBase";
392 tableName = "DescriptionBase";
393 columnName = "specimen_id";
394 boolean notNull = false;
395 String referencedTable = "SpecimenOrObservationBase";
396 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
397 stepList.add(step);
398
399 //update DescriptionBase.Specimen_ID data #3571
400 updateDescriptionSpecimenRelation(stepList);
401
402 //remove tables DescriptionBase_SpecimenOrObservationBase(_AUD) #3571
403 stepName = "Remove table DescriptionBase_SpecimenOrObservationBase";
404 tableName = "DescriptionBase_SpecimenOrObservationBase";
405 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
406 stepList.add(step);
407
408 //change column type for reference type
409 //TODO test with non-Mysql
410 stepName = "Change column type for Reference.type";
411 tableName = "Reference";
412 columnName = "refType";
413 Integer defaultValueStr = -1;
414 notNull = true;
415 int size = 3;
416 step = ColumnTypeChanger.NewInt2StringInstance(stepName, tableName, columnName, size, true, defaultValueStr, notNull);
417 stepList.add(step);
418
419 //update reference type
420 updateReferenceType(stepList);
421
422 //create table CdmPreferences #3555
423 stepName = "Create table 'CdmPreferences'";
424 tableName = "CdmPreferences";
425 TableCreator stepPref = TableCreator.NewInstance(stepName, tableName,
426 new String[]{"key_subject", "key_predicate","value"}, //colNames
427 new String[]{"string_100", "string_200","string_1023",}, // columnTypes
428 new String[]{null, "DefinedTermBase",null}, //referencedTables
429 ! INCLUDE_AUDIT, false);
430 stepPref.setPrimaryKeyParams("key_subject, key_predicate", null);
431 stepList.add(stepPref);
432 //FIXME length of key >= 1000
433
434 //TODO fill CdmPreferences with default values
435
436 //update RightsTerm to RightsType #1306
437 stepName = "Update RightsTerm -> RightsType";
438 String updateSql = "UPDATE DefinedTermBase SET DTYPE = 'RightsType' WHERE DTYPE = 'RightsTerm'";
439 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, updateSql).setDefaultAuditing("DefinedTermBase");
440 stepList.add(step);
441
442 //update Rights table to RightsInfo
443 updateRights2RightsInfo(stepList);
444
445 //Remove column isDescriptionSeparated from FeatureTree #3678
446 stepName = "Remove column isDescriptionSeparated from FeatureTree";
447 tableName = "FeatureTree";
448 columnName = "descriptionSeparated";
449 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
450 stepList.add(step);
451
452 //remove table Sequence_GenBankAccession #3552
453 stepName = "Remove table Sequence_GenBankAccession";
454 tableName = "Sequence_GenBankAccession";
455 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
456 stepList.add(step);
457
458 //remove table GenBankAccession #3552
459 stepName = "Remove table GenBankAccession";
460 tableName = "GenBankAccession";
461 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
462 stepList.add(step);
463
464 //remove old sequence columns
465 removeOldSequenceColumns(stepList);
466
467 //add MediaSpecimen column #3614
468 stepName = "Add mediaSpecimen column to SpecimenOrObservationBase";
469 tableName = "SpecimenOrObservationBase";
470 columnName = "mediaSpecimen_id";
471 notNull = false;
472 referencedTable = "Media";
473 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
474 stepList.add(step);
475
476 //remove DescriptionBase_Feature #2202
477 stepName = "Remove table DescriptionBase_Feature";
478 tableName = "DescriptionBase_Feature";
479 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
480 stepList.add(step);
481
482 //add timeperiod to columns to description element base #3312
483 addTimeperiodToDescriptionElement(stepList);
484
485
486 //TODO add DnaMarker vocabulary and terms #3591 => TermUpdater
487
488 //SpecimenOrObservationBase_Media #3597
489 stepName = "Remove table SpecimenOrObservationBase_Media";
490 tableName = "SpecimenOrObservationBase_Media";
491 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
492 stepList.add(step);
493
494
495 //Amplification #3360
496 stepName = "Create table 'Primer'";
497 tableName = "Primer";
498 step = TableCreator.NewAnnotatableInstance(stepName, tableName,
499 new String[]{"label","sequence_id","publishedIn_id"}, //colNames
500 new String[]{"string_255","int","int"}, // columnTypes
501 new String[]{null,Sequence.class.getSimpleName(),Reference.class.getSimpleName()}, //referencedTables
502 INCLUDE_AUDIT);
503 stepList.add(step);
504
505 //MaterialAndMethod #3360
506 stepName = "Create table 'MaterialAndMethod'";
507 tableName = MaterialAndMethod.class.getSimpleName();
508 step = TableCreator.NewAnnotatableInstance(stepName, tableName,
509 new String[]{"DTYPE", "materialMethodTerm_id","materialMethodText"}, //colNames
510 new String[]{"string_255", "int","string_1000",}, // columnTypes
511 new String[]{null, "DefinedTermBase",null}, //referencedTables
512 INCLUDE_AUDIT);
513 stepList.add(step);
514
515 //Cloning #3360
516 stepName = "Create table 'Cloning'";
517 tableName = "Cloning";
518 String matMetName = MaterialAndMethod.class.getSimpleName();
519 step = TableCreator.NewEventInstance(stepName, tableName,
520 new String[]{"strain","method_id","forwardPrimer_id","reversePrimer_id"}, //colNames
521 new String[]{"string_255", "int","int","int"}, // columnTypes
522 new String[]{null, matMetName,"Primer","Primer"}, //referencedTables
523 INCLUDE_AUDIT);
524 stepList.add(step);
525
526
527 //Amplification #3360
528 stepName = "Create table 'Amplification'";
529 tableName = "Amplification";
530 step = TableCreator.NewEventInstance(stepName, tableName,
531 new String[]{"dnaSample_id","dnaMarker_id","forwardPrimer_id","reversePrimer_id","purification_id","cloning_id", "gelPhoto_id", "successful","successText","ladderUsed","electrophoresisVoltage","gelRunningTime","gelConcentration"}, //colNames
532 new String[]{"int","int","int","int","int","int","int", "bit","string_255","string_255","double","double","double"}, // columnTypes
533 new String[]{"SpecimenOrObservationBase","DefinedTermBase","Primer","Primer",matMetName, matMetName, "Media", null, null, null, null, null, null}, //referencedTables
534 INCLUDE_AUDIT);
535 stepList.add(step);
536
537 //SingleRead #3360
538 stepName = "Create table 'SingleRead'";
539 tableName = "SingleRead";
540 step = TableCreator.NewEventInstance(stepName, tableName,
541 new String[]{"amplification_id","materialAndMethod_id","primer_id","pherogram_id","direction","sequence_length"}, //colNames
542 new String[]{"int","int","int","int","int","int"}, // columnTypes
543 new String[]{"Amplification",matMetName, "Primer","Media", null, null}, //referencedTables
544 INCLUDE_AUDIT);
545 //TODO length sequence_string
546 stepList.add(step);
547
548 //sequence - consensussequence_string #3360
549 stepName= "Add sequence_string to single read";
550 columnName = "sequence_string";
551 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
552 stepList.add(step);
553
554 //amplification - single reads #3360
555 stepName= "Add single reads to amplification";
556 String firstTable = "Amplification";
557 String secondTable = "SingleRead";
558 step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true);
559 stepList.add(step);
560
561 //sequence - single reads #3360
562 stepName= "Add single reads to sequence";
563 firstTable = "Sequence";
564 secondTable = "SingleRead";
565 step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true);
566 stepList.add(step);
567
568 //sequence - barcode #3360
569 stepName= "Add barcodesequencepart_length to sequence";
570 tableName = "Sequence";
571 columnName = "barcodeSequencePart_length";
572 defaultValue = null;
573 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false);
574 stepList.add(step);
575
576 //sequence - barcode #3360
577 stepName= "Add barcodesequencepart_string to sequence";
578 tableName = "Sequence";
579 columnName = "barcodeSequencePart_string";
580 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
581 stepList.add(step);
582
583 //sequence - consensussequence_length #3360
584 stepName= "Add consensusSequence_length to sequence";
585 tableName = "Sequence";
586 columnName = "consensusSequence_length";
587 defaultValue = null;
588 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false);
589 stepList.add(step);
590
591 //sequence - consensussequence_string #3360
592 stepName= "Add consensusSequence_string to sequence";
593 tableName = "Sequence";
594 columnName = "consensusSequence_string";
595 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
596 stepList.add(step);
597
598 //sequence - contigFile #3360
599 stepName= "Add contigFile to sequence";
600 tableName = "Sequence";
601 columnName = "contigFile_id";
602 referencedTable = "Media";
603 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
604 stepList.add(step);
605
606 //sequence - boldprocessid #3360
607 stepName= "Add boldprocessId to sequence";
608 tableName = "Sequence";
609 columnName = "boldProcessId";
610 length = 20;
611 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
612 stepList.add(step);
613
614 //sequence - boldprocessid #3360
615 stepName= "Add geneticAccessionNumber to sequence";
616 tableName = "Sequence";
617 columnName = "geneticAccessionNumber";
618 length = 20;
619 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
620 stepList.add(step);
621
622 //sequence - haplotype #3360
623 stepName= "Add haplotype to sequence";
624 tableName = "Sequence";
625 columnName = "haplotype";
626 length = 100;
627 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
628 stepList.add(step);
629
630 //sequence - isBarcode #3360
631 stepName= "Add isBarcode to sequence";
632 tableName = "Sequence";
633 columnName = "isBarcode";
634 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
635 stepList.add(step);
636
637 //sequence - dnaMarker #3360
638 stepName= "Add dnaMarker to sequence";
639 tableName = "Sequence";
640 columnName = "dnaMarker_id";
641 referencedTable = "DefinedTermBase";
642 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
643 stepList.add(step);
644
645 //sequence - dnaSample #3360
646 stepName= "Add dnaSample to sequence";
647 tableName = "Sequence";
648 columnName = "dnaSample_id";
649 referencedTable = "SpecimenOrObservationBase";
650 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
651 stepList.add(step);
652
653 return stepList;
654 }
655
656 private void updateRights2RightsInfo(List<ISchemaUpdaterStep> stepList) {
657 //#2945
658 String stepName = "Update Rights to RightsInfo";
659 String tableName = "Rights";
660 String newTableName = "RightsInfo";
661 ISchemaUpdaterStep step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
662 stepList.add(step);
663
664 stepName = "Update AgentBase_Rights to RightsInfo";
665 tableName = "AgentBase_Rights";
666 newTableName = "AgentBase_RightsInfo";
667 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
668 stepList.add(step);
669
670 stepName = "Update Rights_Annotation to RightsInfo";
671 tableName = "Rights_Annotation";
672 newTableName = "RightsInfo_Annotation";
673 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
674 stepList.add(step);
675
676 stepName = "Update Rights_id column in RightsInfo_Annotation";
677 tableName = "RightsInfo_Annotation";
678 String columnName = "Rights_Id";
679 String newColumnName = "RightsInfo_id";
680 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
681 stepList.add(step);
682
683 stepName = "Update Rights_Marker to RightsInfo";
684 tableName = "Rights_Marker";
685 newTableName = "RightsInfo_Marker";
686 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
687 stepList.add(step);
688
689 stepName = "Update Rights_id column in RightsInfo_Marker";
690 tableName = "RightsInfo_Marker";
691 columnName = "Rights_Id";
692 newColumnName = "RightsInfo_id";
693 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
694 stepList.add(step);
695
696 stepName = "Update Classification_Rights to RightsInfo";
697 tableName = "Classification_Rights";
698 newTableName = "Classification_RightsInfo";
699 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
700 stepList.add(step);
701
702 stepName = "Update Collection_Rights to RightsInfo";
703 tableName = "Collection_Rights";
704 newTableName = "Collection_RightsInfo";
705 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
706 stepList.add(step);
707
708 stepName = "Update DefinedTermBase_Rights to RightsInfo";
709 tableName = "DefinedTermBase_Rights";
710 newTableName = "DefinedTermBase_RightsInfo";
711 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
712 stepList.add(step);
713
714 stepName = "Update DescriptionBase_Rights to RightsInfo";
715 tableName = "DescriptionBase_Rights";
716 newTableName = "DescriptionBase_RightsInfo";
717 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
718 stepList.add(step);
719
720 stepName = "Update FeatureTree_Rights to RightsInfo";
721 tableName = "FeatureTree_Rights";
722 newTableName = "FeatureTree_RightsInfo";
723 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
724 stepList.add(step);
725
726 stepName = "Update Media_Rights to RightsInfo";
727 tableName = "Media_Rights";
728 newTableName = "Media_RightsInfo";
729 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
730 stepList.add(step);
731
732 stepName = "Update PolytomousKey_Rights to RightsInfo";
733 tableName = "PolytomousKey_Rights";
734 newTableName = "PolytomousKey_RightsInfo";
735 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
736 stepList.add(step);
737
738 stepName = "Update Reference_Rights to RightsInfo";
739 tableName = "Reference_Rights";
740 newTableName = "Reference_RightsInfo";
741 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
742 stepList.add(step);
743
744 stepName = "Update SpecimenOrObservationBase_Rights to RightsInfo";
745 tableName = "SpecimenOrObservationBase_Rights";
746 newTableName = "SpecimenOrObservationBase_RightsInfo";
747 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
748 stepList.add(step);
749
750 stepName = "Update TaxonBase_Rights to RightsInfo";
751 tableName = "TaxonBase_Rights";
752 newTableName = "TaxonBase_RightsInfo";
753 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
754 stepList.add(step);
755
756 stepName = "Update TaxonNameBase_Rights to RightsInfo";
757 tableName = "TaxonNameBase_Rights";
758 newTableName = "TaxonNameBase_RightsInfo";
759 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
760 stepList.add(step);
761
762 stepName = "Update TermVocabulary_Rights to RightsInfo";
763 tableName = "TermVocabulary_Rights";
764 newTableName = "TermVocabulary_RightsInfo";
765 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
766 stepList.add(step);
767 }
768
769 private void updateReferenceType(List<ISchemaUpdaterStep> stepList) {
770
771 String stepName = "Update reference refType for Reference";
772 String baseQuery = " UPDATE Reference " +
773 " SET refType = '%s' " +
774 " WHERE refType = '%s' ";
775 Integer index = 0;
776 String tableName = "Reference";
777
778 //0-Article
779 String query = String.format(baseQuery, ReferenceType.Article.getKey(), String.valueOf(index++));
780 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
781 stepList.add(step);
782
783 //1-Book
784 query = String.format(baseQuery, ReferenceType.Book.getKey(), String.valueOf(index++));
785 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
786 stepList.add(step);
787
788 //2-Book Section
789 query = String.format(baseQuery, ReferenceType.BookSection.getKey(), String.valueOf(index++));
790 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
791 stepList.add(step);
792
793 //3-CD / DVD
794 query = String.format(baseQuery, ReferenceType.CdDvd.getKey(), String.valueOf(index++));
795 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
796 stepList.add(step);
797
798 //4-Database
799 query = String.format(baseQuery, ReferenceType.Database.getKey(), String.valueOf(index++));
800 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
801 stepList.add(step);
802
803 //5-Generic
804 query = String.format(baseQuery, ReferenceType.Generic.getKey(), String.valueOf(index++));
805 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
806 stepList.add(step);
807
808 //6-InProceedings
809 query = String.format(baseQuery, ReferenceType.InProceedings.getKey(), String.valueOf(index++));
810 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
811 stepList.add(step);
812
813 //7-Journal
814 query = String.format(baseQuery, ReferenceType.Journal.getKey(), String.valueOf(index++));
815 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
816 stepList.add(step);
817
818 //8-Map
819 query = String.format(baseQuery, ReferenceType.Map.getKey(), String.valueOf(index++));
820 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
821 stepList.add(step);
822
823 //9-Patent
824 query = String.format(baseQuery, ReferenceType.Patent.getKey(), String.valueOf(index++));
825 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
826 stepList.add(step);
827
828 //10-Personal Communication
829 query = String.format(baseQuery, ReferenceType.PersonalCommunication.getKey(), String.valueOf(index++));
830 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
831 stepList.add(step);
832
833 //11-PrintSeries
834 query = String.format(baseQuery, ReferenceType.PrintSeries.getKey(), String.valueOf(index++));
835 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
836 stepList.add(step);
837
838 //12-Proceedings
839 query = String.format(baseQuery, ReferenceType.Proceedings.getKey(), String.valueOf(index++));
840 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
841 stepList.add(step);
842
843 //13-Report
844 query = String.format(baseQuery, ReferenceType.Report.getKey(), String.valueOf(index++));
845 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
846 stepList.add(step);
847
848 //14-Thesis
849 query = String.format(baseQuery, ReferenceType.Thesis.getKey(), String.valueOf(index++));
850 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
851 stepList.add(step);
852
853 //15-WebPage
854 query = String.format(baseQuery, ReferenceType.WebPage.getKey(), String.valueOf(index++));
855 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
856 stepList.add(step);
857 }
858
859 private void updateRecordBasis(List<ISchemaUpdaterStep> stepList) {
860 String stepName = "Update recordBasis for SpecimenOrObservationBase";
861 String tableName = "SpecimenOrObservationBase";
862
863 //Field Unit
864 String query = " UPDATE " + tableName +
865 " SET recordBasis = '" + SpecimenOrObservationType.FieldUnit.getKey() + "' " +
866 " WHERE DTYPE = 'FieldUnit' OR DTYPE = 'FieldObservation'";
867 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
868 stepList.add(step);
869
870 //DerivedUnit
871 query = " UPDATE " + tableName +
872 " SET recordBasis = '" + SpecimenOrObservationType.DerivedUnit.getKey() + "' " +
873 " WHERE DTYPE = '" + DerivedUnit.class.getSimpleName() + "'";
874 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
875 stepList.add(step);
876
877 //Living Being
878 query = " UPDATE " + tableName +
879 " SET recordBasis = '" + SpecimenOrObservationType.LivingSpecimen.getKey() + "' " +
880 " WHERE DTYPE = 'LivingBeing'";
881 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
882 stepList.add(step);
883
884 //Observation
885 query = " UPDATE " + tableName +
886 " SET recordBasis = '" + SpecimenOrObservationType.Observation.getKey() + "' " +
887 " WHERE DTYPE = 'Observation'";
888 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
889 stepList.add(step);
890
891 //Preserved Specimen
892 query = " UPDATE " + tableName +
893 " SET recordBasis = '" + SpecimenOrObservationType.PreservedSpecimen.getKey() + "' " +
894 " WHERE DTYPE = 'Specimen'";
895 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
896 stepList.add(step);
897
898 //Fossil
899 query = " UPDATE " + tableName +
900 " SET recordBasis = '" + SpecimenOrObservationType.Fossil.getKey() + "' " +
901 " WHERE DTYPE = 'Fossil'";
902 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
903 stepList.add(step);
904
905 //DnaSample
906 query = " UPDATE " + tableName +
907 " SET recordBasis = '" + SpecimenOrObservationType.DnaSample.getKey() + "' " +
908 " WHERE DTYPE = 'DnaSample'";
909 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
910 stepList.add(step);
911
912 //Unknown as default (if not yet handled before)
913 query = " UPDATE " + tableName +
914 " SET recordBasis = '" + SpecimenOrObservationType.Unknown.getKey() + "' " +
915 " WHERE recordBasis IS NULL ";
916 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
917 stepList.add(step);
918 }
919
920 //update length of all title caches and full title cache in names
921 //TODO test for H2, Postgres, SqlServer
922 //https://dev.e-taxonomy.eu/trac/ticket/1592
923 private void updateTitleCacheLength(List<ISchemaUpdaterStep> stepList) {
924 String stepName;
925 String tableName;
926 ISchemaUpdaterStep step;
927 String columnName;
928 int size = 800;
929
930 stepName = "Change length of TaxonNameBase fullTitleCache";
931 tableName = "TaxonNameBase";
932 columnName = "fullTitleCache";
933 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
934 stepList.add(step);
935
936 stepName = "Change length of TaxonNameBase title cache";
937 tableName = "TaxonNameBase";
938 columnName = "titleCache";
939 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
940 stepList.add(step);
941
942 stepName = "Change length of TaxonBase title cache";
943 tableName = "TaxonNameBase";
944 columnName = "titleCache";
945 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
946 stepList.add(step);
947
948 stepName = "Change length of Classification title cache";
949 tableName = "Classification";
950 columnName = "titleCache";
951 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
952 stepList.add(step);
953
954 stepName = "Change length of DescriptionBase title cache";
955 tableName = "DescriptionBase";
956 columnName = "titleCache";
957 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
958 stepList.add(step);
959
960 stepName = "Change length of FeatureTree title cache";
961 tableName = "FeatureTree";
962 columnName = "titleCache";
963 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
964 stepList.add(step);
965
966 stepName = "Change length of Collection title cache";
967 tableName = "Collection";
968 columnName = "titleCache";
969 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
970 stepList.add(step);
971
972 stepName = "Change length of Reference title cache";
973 tableName = "Reference";
974 columnName = "titleCache";
975 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
976 stepList.add(step);
977
978 stepName = "Change length of Media title cache";
979 tableName = "Media";
980 columnName = "titleCache";
981 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
982 stepList.add(step);
983
984 stepName = "Change length of PolytomousKey title cache";
985 tableName = "PolytomousKey";
986 columnName = "titleCache";
987 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
988 stepList.add(step);
989
990 stepName = "Change length of SpecimenOrObservationBase title cache";
991 tableName = "SpecimenOrObservationBase";
992 columnName = "titleCache";
993 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
994 stepList.add(step);
995
996 stepName = "Change length of DefinedTermBase title cache";
997 tableName = "DefinedTermBase";
998 columnName = "titleCache";
999 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1000 stepList.add(step);
1001
1002 stepName = "Change length of TermVocabulary title cache";
1003 tableName = "TermVocabulary";
1004 columnName = "titleCache";
1005 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1006 stepList.add(step);
1007
1008 }
1009
1010
1011 private void updateDescriptionSpecimenRelation(List<ISchemaUpdaterStep> stepList) {
1012 //TODO warn if multiple entries for 1 description exists
1013 String sqlCount = " SELECT count(*) as n " +
1014 " FROM DescriptionBase_SpecimenOrObservationBase MN " +
1015 " GROUP BY MN.descriptions_id " +
1016 " HAVING count(*) > 1 " +
1017 " ORDER BY MN.descriptions_id, MN.describedspecimenorobservations_id ";
1018
1019 //TODO ... and log the concrete records
1020 // FROM DescriptionBase_SpecimenOrObservationBase ds
1021 // WHERE ds.descriptions_id IN (
1022 // SELECT MN.descriptions_id
1023 // FROM DescriptionBase_SpecimenOrObservationBase MN
1024 // GROUP BY MN.descriptions_id
1025 // HAVING count(*) > 1
1026 // )
1027 // ORDER BY descriptions_id, describedspecimenorobservations_id
1028
1029 //TODO test for H2, Postgresql AND SQLServer (later will need TOP 1)
1030 String stepName = "update Description - Specimen relation data ";
1031 String sql = " UPDATE DescriptionBase db " +
1032 " SET db.specimen_id = " +
1033 " (SELECT MN.describedspecimenorobservations_id " +
1034 " FROM DescriptionBase_SpecimenOrObservationBase MN " +
1035 " WHERE MN.descriptions_id = db.id " +
1036 " LIMIT 1 " +
1037 ")";
1038 //TODO _AUD
1039 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, sql);
1040 stepList.add(step);
1041
1042 }
1043
1044 private void updateAbbrevTitle(List<ISchemaUpdaterStep> stepList) {
1045 String tableName = "Reference";
1046
1047 String stepName = "Update abbrevTitleCache for protected title caches with title";
1048 String query = " UPDATE Reference r " +
1049 " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache";
1050 // + " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 1 ";
1051 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1052 stepList.add(step);
1053
1054 // stepName = "Update abbrevTitleCache for protected title caches with no title";
1055 // query = " UPDATE Reference r " +
1056 // " SET r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" +
1057 // " WHERE r.title IS NULL AND r.protectedTitleCache = 1 ";
1058 // step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setAuditing("Reference");
1059 // stepList.add(step);
1060
1061 // stepName = "Update abbrevTitleCache for protected title caches with title";
1062 // query = " UPDATE Reference r " +
1063 // " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" +
1064 // " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 ";
1065 // step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setAuditing("Reference");
1066 // stepList.add(step);
1067
1068 stepName = "Update reference title, set null where abbrev title very likely";
1069 query = " UPDATE Reference r " +
1070 " SET r.title = NULL " +
1071 " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " +
1072 " ( LENGTH(r.title) <= 15 AND title like '%.%.%' OR LENGTH(r.title) < 30 AND title like '%.%.%.%' OR LENGTH(r.title) < 45 AND title like '%.%.%.%.%' OR LENGTH(r.title) < 60 AND title like '%.%.%.%.%.%' " +
1073 ")" ;
1074 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1075 stepList.add(step);
1076
1077
1078 stepName = "Update reference abbrevTitle, set null where abbrev title very unlikely";
1079 query = " UPDATE Reference r " +
1080 " SET r.abbrevTitle = NULL " +
1081 " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " +
1082 " ( title NOT like '%.%' OR LENGTH(r.title) > 30 AND title NOT like '%.%.%' " +
1083 ")" ;
1084 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1085 stepList.add(step);
1086
1087 }
1088
1089 private void removeOldSequenceColumns(List<ISchemaUpdaterStep> stepList) {
1090 //TODO also remove Identifiable attributes ??
1091
1092 //remove citationmicroreference
1093 String stepName = "Remove citationmicroreference column";
1094 String tableName = "Sequence";
1095 String columnName = "citationMicroReference";
1096 ISchemaUpdaterStep step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1097 stepList.add(step);
1098
1099 //remove datesequenced
1100 stepName = "Remove datesequenced column";
1101 columnName = "datesequenced";
1102 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1103 stepList.add(step);
1104
1105 //remove length
1106 stepName = "Remove length column";
1107 columnName = "length";
1108 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1109 stepList.add(step);
1110
1111 //remove sequence
1112 stepName = "Remove sequence column";
1113 columnName = "sequence";
1114 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1115 stepList.add(step);
1116
1117 //remove locus_id
1118 stepName = "Remove locus_id column";
1119 columnName = "locus_id";
1120 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1121 stepList.add(step);
1122
1123 //remove publishedin_id
1124 stepName = "Remove publishedin_id column";
1125 columnName = "publishedin_id";
1126 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1127 stepList.add(step);
1128
1129 //remove barcode
1130 stepName = "Remove barcode column";
1131 columnName = "barcode";
1132 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1133 stepList.add(step);
1134 }
1135
1136 private void updateIdInVocabulary(List<ISchemaUpdaterStep> stepList) {
1137 String tableName = "DefinedTermBase";
1138
1139 String queryVocUuid = " UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id" +
1140 " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " +
1141 " FROM DefinedTermBase_Representation MN " +
1142 " INNER JOIN Representation r ON r.id = MN.representations_id " +
1143 " WHERE MN.DefinedTermBase_id = dtb.id) " +
1144 " WHERE voc.uuid = '%s'";
1145
1146 //Languages (ISO)
1147 String stepName = "Update idInVocabulary for Languages ";
1148 String query = "UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id " +
1149 " SET dtb.idInVocabulary = dtb.iso639_2 "+
1150 " WHERE voc.uuid = '45ac7043-7f5e-4f37-92f2-3874aaaef2de' ";
1151 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName); //not fully correct as we should join with TermVoc_AUD but good enough for this usecase
1152 stepList.add(step);
1153
1154 //Undefined Languages => all
1155 stepName = "Update idInVocabulary for undefined languages";
1156 String uuid = "7fd1e6d0-2e76-4dfa-bad9-2673dd042c28";
1157 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1158 stepList.add(step);
1159
1160 //Waterbody & Country => all
1161 stepName = "Update idInVocabulary for WaterbodyOrCountries";
1162 uuid = "006b1870-7347-4624-990f-e5ed78484a1a";
1163 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1164 stepList.add(step);
1165
1166 //TdwgAreas => all
1167 stepName = "Update idInVocabulary for TDWG areas";
1168 uuid = NamedArea.uuidTdwgAreaVocabulary.toString();
1169 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1170 stepList.add(step);
1171
1172 //Rank => some
1173 stepName = "Update idInVocabulary for ranks";
1174 uuid = "ef0d1ce1-26e3-4e83-b47b-ca74eed40b1b";
1175 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1176 stepList.add(step);
1177
1178 //SpecimenTypeDesignationStatus => all
1179 stepName = "Update idInVocabulary for SpecimenTypeDesignationStatus";
1180 uuid = "ab177bd7-d3c8-4e58-a388-226fff6ba3c2";
1181 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1182 stepList.add(step);
1183
1184 //NameTypeDesignationStatus => all
1185 stepName = "Update idInVocabulary for NameTypeDesignationStatus";
1186 uuid = "ab60e738-4d09-4c24-a1b3-9466b01f9f55";
1187 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1188 stepList.add(step);
1189
1190 //NomenclaturalStatusType => all, abbrevs.
1191 stepName = "Update idInVocabulary for NomenclaturalStatusType";
1192 uuid = "bb28cdca-2f8a-4f11-9c21-517e9ae87f1f";
1193 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1194 stepList.add(step);
1195
1196 //TaxonRelationshipType, all but 2 (Invalid Designation for, Misapplied Name for)
1197 stepName = "Update idInVocabulary for TaxonRelationshipType";
1198 uuid = "15db0cf7-7afc-4a86-a7d4-221c73b0c9ac";
1199 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1200 stepList.add(step);
1201
1202 //PresenceTerm => all
1203 stepName = "Update idInVocabulary for PresenceTerm";
1204 uuid = "adbbbe15-c4d3-47b7-80a8-c7d104e53a05";
1205 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1206 stepList.add(step);
1207
1208 //AbsenceTerm => all
1209 stepName = "Update idInVocabulary for AbsenceTerm";
1210 uuid = "5cd438c8-a8a1-4958-842e-169e83e2ceee";
1211 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1212 stepList.add(step);
1213
1214 //Sex => all
1215 stepName = "Update idInVocabulary for Sex";
1216 uuid = "9718b7dd-8bc0-4cad-be57-3c54d4d432fe";
1217 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1218 stepList.add(step);
1219
1220 //ExtensionType => all
1221 stepName = "Update idInVocabulary for ExtensionType";
1222 uuid = "117cc307-5bd4-4b10-9b2f-2e14051b3b20";
1223 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1224 stepList.add(step);
1225
1226 //ReferenceSystem => all
1227 stepName = "Update idInVocabulary for ReferenceSystem";
1228 uuid = "ec6376e5-0c9c-4f5c-848b-b288e6c17a86";
1229 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1230 stepList.add(step);
1231
1232 //DeterminationModifier => all
1233 stepName = "Update idInVocabulary for DeterminationModifier";
1234 uuid = "fe87ea8d-6e0a-4e5d-b0da-0ab8ea67ca77";
1235 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1236 stepList.add(step);
1237
1238 //InstitutionType, MeasurementUnit, Scope, Stage, State, TextFormat, Modifier, PreservationMethod => dummies
1239 stepName = "Update idInVocabulary for dummy terms in several vocabularies";
1240 query = " UPDATE DefinedTermBase dtb " +
1241 " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " +
1242 " FROM DefinedTermBase_Representation MN " +
1243 " INNER JOIN Representation r ON r.id = MN.representations_id " +
1244 " WHERE MN.DefinedTermBase_id = dtb.id) " +
1245 " WHERE dtb.termType IN ('%s','%s','%s','%s','%s','%s','%s','%s')";
1246 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(query,
1247 TermType.InstitutionType.getKey(), TermType.MeasurementUnit.getKey(),
1248 TermType.Scope.getKey(), TermType.Stage.getKey(), TermType.State.getKey(),
1249 TermType.TextFormat.getKey(), TermType.Modifier.getKey(), TermType.PreservationMethod.getKey()))
1250 .setDefaultAuditing(tableName);
1251 stepList.add(step);
1252
1253 //NULL for empty strings
1254 stepName = "Update idInVocabulary, replace empty strings by null";
1255 query = "Update DefinedTermBase dtb SET idInVocabulary = NULL WHERE idInVocabulary = ''";
1256 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1257 stepList.add(step);
1258
1259 //MarkerType, AnnotationType, NamedAreaType, NamedAreaLevel, Feature, Continent, DerivationEventType, StatisticalMeasure, RightsType,SynonymRelationshipType & HybridRelationshipType & NameRelationshipType
1260 //=> none
1261
1262 //DnaMarker => yes but no entries
1263
1264 //Clean up empty abbreviated labels in representations
1265 stepName = "Update abbreviated label, replace empty strings by null";
1266 query = "Update Representation r SET r.abbreviatedLabel = NULL WHERE r.abbreviatedLabel = ''";
1267 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation"); //AUD not needed
1268 stepList.add(step);
1269
1270 }
1271
1272 private void updateTermTypesForVocabularies( List<ISchemaUpdaterStep> stepList) {
1273 //vocabularies with terms
1274 for (TermType termType : TermType.values()){
1275 updateTermTypeForVocabularies(stepList, termType);
1276 }
1277
1278 String tableName = "TermVocabulary";
1279 //TODO _AUD
1280 //Natural Language Terms
1281 String stepName = "Updater termType for NaturalLanguageTerms";
1282 String query = "UPDATE TermVocabulary voc " +
1283 " SET voc.termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " +
1284 " WHERE voc.uuid = 'fdaba4b0-5c14-11df-a08a-0800200c9a66'";
1285 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1286 stepList.add(step);
1287
1288 //remaining vocabularies
1289 stepName = "Updater termType for remaining vocabularies";
1290 query = "UPDATE TermVocabulary voc " +
1291 " SET voc.termType = '"+ TermType.Unknown.getKey() +"' " +
1292 " WHERE voc.termType IS NULL";
1293 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1294 stepList.add(step);
1295
1296
1297 }
1298
1299 private void updateTermTypeForVocabularies(List<ISchemaUpdaterStep> stepList, TermType termType) {
1300 String stepName = "Updater vocabulary termType for " + termType.toString();
1301 String query = "UPDATE TermVocabulary voc " +
1302 " SET voc.termType = '" + termType.getKey() + "' " +
1303 " WHERE Exists (SELECT * FROM DefinedTermBase dtb WHERE dtb.termType = '" + termType.getKey() + "' AND dtb.vocabulary_id = voc.id)";
1304 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("TermVocabulary"); //AUD not fully correct as subselect should also work on AUD, good enough for our purposes
1305 stepList.add(step);
1306 }
1307
1308 /**
1309 * @param stepList
1310 * @param stepName
1311 */
1312 private void updateTermTypesForTerms(List<ISchemaUpdaterStep> stepList) {
1313 String stepName = "Update termType for NamedAreas";
1314 String tableName = "DefinedTermBase";
1315
1316 //NamedArea
1317 String query = " UPDATE DefinedTermBase " +
1318 " SET termType = '" + TermType.NamedArea.getKey() + "' " +
1319 " WHERE DTYPE = '" + NamedArea.class.getSimpleName() + "' OR DTYPE = 'TdwgArea' OR DTYPE = '"+ WaterbodyOrCountry.class.getSimpleName() + "' OR DTYPE = 'Continent' ";
1320 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1321 stepList.add(step);
1322
1323 //Lanugage
1324 query = " UPDATE DefinedTermBase " +
1325 " SET termType = '" + TermType.Language.getKey() + "' " +
1326 " WHERE DTYPE = '" + Language.class.getSimpleName() + "' ";
1327 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1328 stepList.add(step);
1329
1330 //RANK
1331 query = " UPDATE DefinedTermBase " +
1332 " SET termType = '" + TermType.Rank.getKey() + "' " +
1333 " WHERE DTYPE = '" + Rank.class.getSimpleName() + "' ";
1334 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1335 stepList.add(step);
1336
1337 //Feature
1338 query = " UPDATE DefinedTermBase " +
1339 " SET termType = '" + TermType.Feature.getKey() + "' " +
1340 " WHERE DTYPE = '" + Feature.class.getSimpleName() + "' ";
1341 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1342 stepList.add(step);
1343
1344 //AnnotationType
1345 query = " UPDATE DefinedTermBase " +
1346 " SET termType = '" + TermType.AnnotationType.getKey() + "' " +
1347 " WHERE DTYPE = '" + AnnotationType.class.getSimpleName() + "' ";
1348 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1349 stepList.add(step);
1350
1351 //MarkerType
1352 query = " UPDATE DefinedTermBase " +
1353 " SET termType = '" + TermType.MarkerType.getKey() + "' " +
1354 " WHERE DTYPE = '" + MarkerType.class.getSimpleName() + "' ";
1355 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1356 stepList.add(step);
1357
1358 //ExtensionType
1359 query = " UPDATE DefinedTermBase " +
1360 " SET termType = '" + TermType.ExtensionType.getKey() + "' " +
1361 " WHERE DTYPE = '" + ExtensionType.class.getSimpleName() + "' ";
1362 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1363 stepList.add(step);
1364
1365 //DerivationEventType
1366 query = " UPDATE DefinedTermBase " +
1367 " SET termType = '" + TermType.DerivationEventType.getKey() + "' " +
1368 " WHERE DTYPE = '" + DerivationEventType.class.getSimpleName() + "' ";
1369 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1370 stepList.add(step);
1371
1372 //PresenceAbsenceTerm
1373 query = " UPDATE DefinedTermBase " +
1374 " SET termType = '" + TermType.PresenceAbsenceTerm.getKey() + "' " +
1375 " WHERE DTYPE = 'PresenceTerm' OR DTYPE = 'AbsenceTerm'";
1376 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1377 stepList.add(step);
1378
1379 //NomenclaturalStatusType
1380 query = " UPDATE DefinedTermBase " +
1381 " SET termType = '" + TermType.NomenclaturalStatusType.getKey() + "' " +
1382 " WHERE DTYPE = '" + NomenclaturalStatusType.class.getSimpleName() + "' ";
1383 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1384 stepList.add(step);
1385
1386 //NameRelationshipType
1387 query = " UPDATE DefinedTermBase " +
1388 " SET termType = '" + TermType.NameRelationshipType.getKey() + "' " +
1389 " WHERE DTYPE = '" + NameRelationshipType.class.getSimpleName() + "' ";
1390 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1391 stepList.add(step);
1392
1393 //HybridRelationshipType
1394 query = " UPDATE DefinedTermBase " +
1395 " SET termType = '" + TermType.HybridRelationshipType.getKey() + "' " +
1396 " WHERE DTYPE = '" + HybridRelationshipType.class.getSimpleName() + "' ";
1397 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1398 stepList.add(step);
1399
1400 //SynonymRelationshipType
1401 query = " UPDATE DefinedTermBase " +
1402 " SET termType = '" + TermType.SynonymRelationshipType.getKey() + "' " +
1403 " WHERE DTYPE = '" + SynonymRelationshipType.class.getSimpleName() + "' ";
1404 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1405 stepList.add(step);
1406
1407 //TaxonRelationshipType
1408 query = " UPDATE DefinedTermBase " +
1409 " SET termType = '" + TermType.TaxonRelationshipType.getKey() + "' " +
1410 " WHERE DTYPE = '" + TaxonRelationshipType.class.getSimpleName() + "' ";
1411 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1412 stepList.add(step);
1413
1414 //NameTypeDesignationStatus
1415 query = " UPDATE DefinedTermBase " +
1416 " SET termType = '" + TermType.NameTypeDesignationStatus.getKey() + "' " +
1417 " WHERE DTYPE = '" + NameTypeDesignationStatus.class.getSimpleName() + "' ";
1418 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1419 stepList.add(step);
1420
1421 //SpecimenTypeDesignationStatus
1422 query = " UPDATE DefinedTermBase " +
1423 " SET termType = '" + TermType.SpecimenTypeDesignationStatus.getKey() + "' " +
1424 " WHERE DTYPE = '" + SpecimenTypeDesignationStatus.class.getSimpleName() + "' ";
1425 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1426 stepList.add(step);
1427
1428 //InstitutionType
1429 query = " UPDATE DefinedTermBase " +
1430 " SET termType = '" + TermType.InstitutionType.getKey() + "' " +
1431 " WHERE DTYPE = 'InstitutionType' ";
1432 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1433 stepList.add(step);
1434
1435 //NamedAreaType
1436 query = " UPDATE DefinedTermBase " +
1437 " SET termType = '" + TermType.NamedAreaType.getKey() + "' " +
1438 " WHERE DTYPE = '" + NamedAreaType.class.getSimpleName() + "' ";
1439 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1440 stepList.add(step);
1441
1442 //NamedAreaLevel
1443 query = " UPDATE DefinedTermBase " +
1444 " SET termType = '" + TermType.NamedAreaLevel.getKey() + "' " +
1445 " WHERE DTYPE = '" + NamedAreaLevel.class.getSimpleName() + "' ";
1446 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1447 stepList.add(step);
1448
1449 //RightsType
1450 query = " UPDATE DefinedTermBase " +
1451 " SET termType = '" + TermType.RightsType.getKey() + "' " +
1452 " WHERE DTYPE = 'RightsType' OR DTYPE = 'RightsTerm' ";
1453 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1454 stepList.add(step);
1455
1456 //MeasurementUnit
1457 query = " UPDATE DefinedTermBase " +
1458 " SET termType = '" + TermType.MeasurementUnit.getKey() + "' " +
1459 " WHERE DTYPE = '" + MeasurementUnit.class.getSimpleName() + "' ";
1460 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1461 stepList.add(step);
1462
1463 //StatisticalMeasure
1464 query = " UPDATE DefinedTermBase " +
1465 " SET termType = '" + TermType.StatisticalMeasure.getKey() + "' " +
1466 " WHERE DTYPE = '" + StatisticalMeasure.class.getSimpleName() + "' ";
1467 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1468 stepList.add(step);
1469
1470 //PreservationMethod
1471 query = " UPDATE DefinedTermBase " +
1472 " SET termType = '" + TermType.PreservationMethod.getKey() + "' " +
1473 " WHERE DTYPE = '" + PreservationMethod.class.getSimpleName() + "' ";
1474 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1475 stepList.add(step);
1476
1477 //Modifier
1478 query = " UPDATE DefinedTermBase " +
1479 " SET termType = '" + TermType.Modifier.getKey() + "' " +
1480 " WHERE DTYPE = 'Modifier' ";
1481 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1482 stepList.add(step);
1483
1484 //Scope
1485 query = " UPDATE DefinedTermBase " +
1486 " SET termType = '" + TermType.Scope.getKey() + "' " +
1487 " WHERE DTYPE = 'Scope' ";
1488 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1489 stepList.add(step);
1490
1491 //Stage
1492 query = " UPDATE DefinedTermBase " +
1493 " SET termType = '" + TermType.Stage.getKey() + "' " +
1494 " WHERE DTYPE = 'Stage' ";
1495 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1496 stepList.add(step);
1497
1498 //Sex
1499 query = " UPDATE DefinedTermBase " +
1500 " SET termType = '" + TermType.Sex.getKey() + "' " +
1501 " WHERE DTYPE = 'Sex' ";
1502 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1503 stepList.add(step);
1504
1505 //ReferenceSystem
1506 query = " UPDATE DefinedTermBase " +
1507 " SET termType = '" + TermType.ReferenceSystem.getKey() + "' " +
1508 " WHERE DTYPE = '" + ReferenceSystem.class.getSimpleName() + "' ";
1509 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1510 stepList.add(step);
1511
1512 //State
1513 query = " UPDATE DefinedTermBase " +
1514 " SET termType = '" + TermType.State.getKey() + "' " +
1515 " WHERE DTYPE = '" + State.class.getSimpleName() + "' ";
1516 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1517 stepList.add(step);
1518
1519 //NaturalLanguageTerm
1520 query = " UPDATE DefinedTermBase " +
1521 " SET termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " +
1522 " WHERE DTYPE = '" + NaturalLanguageTerm.class.getSimpleName() + "' ";
1523 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1524 stepList.add(step);
1525
1526 //TextFormat
1527 query = " UPDATE DefinedTermBase " +
1528 " SET termType = '" + TermType.TextFormat.getKey() + "' " +
1529 " WHERE DTYPE = '" + TextFormat.class.getSimpleName() + "' ";
1530 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1531 stepList.add(step);
1532
1533 //DeterminationModifier
1534 query = " UPDATE DefinedTermBase " +
1535 " SET termType = '" + TermType.DeterminationModifier.getKey() + "' " +
1536 " WHERE DTYPE = 'DeterminationModifier' ";
1537 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1538 stepList.add(step);
1539
1540
1541 }
1542
1543 /**
1544 * @param stepList
1545 */
1546 private void updateDtypeOfDefinedTerms(List<ISchemaUpdaterStep> stepList) {
1547 String tableName = "DefinedTermBase";
1548
1549 //update DTYPE for institution type and modifiers (Stage, Scope, Sex, DeterminationModifier, Modifier) -> DefinedTerm
1550 String stepName = "Update DTYPE for TDWG Areas";
1551 String query = " UPDATE DefinedTermBase " +
1552 " SET DTYPE = 'DefinedTerm' " +
1553 " WHERE DTYPE = 'Stage' OR DTYPE = 'Scope' OR DTYPE = 'Sex' OR DTYPE = 'DeterminationModifier' " +
1554 " OR DTYPE = 'Modifier' OR DTYPE = 'InstitutionType' ";
1555 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1556 stepList.add(step);
1557
1558
1559 //update DTYPE for TDWG Areas and Continents -> NamedArea
1560 stepName = "Update DTYPE for TDWG Areas and Continents";
1561 query = " UPDATE DefinedTermBase " +
1562 " SET DTYPE = 'NamedArea' " +
1563 " WHERE DTYPE = 'TdwgArea' OR DTYPE = 'Continent' ";
1564 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1565 stepList.add(step);
1566
1567 }
1568
1569 /**
1570 * @param stepList
1571 */
1572 private void changeUriType(List<ISchemaUpdaterStep> stepList) {
1573 //#3345
1574 String stepName;
1575 String tableName;
1576 ISchemaUpdaterStep step;
1577 String columnName;
1578
1579 stepName = "Update uri to clob for DefinedTermBase";
1580 tableName = "DefinedTermBase";
1581 columnName = "uri";
1582 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1583 stepList.add(step);
1584
1585 stepName = "Update uri to clob for TermVocabulary";
1586 tableName = "TermVocabulary";
1587 columnName = "uri";
1588 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1589 stepList.add(step);
1590
1591 //TODO are uri and termsourceuri needed ???
1592 stepName = "Update termsourceuri to clob for TermVocabulary";
1593 tableName = "TermVocabulary";
1594 columnName = "termsourceuri";
1595 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1596 stepList.add(step);
1597
1598 stepName = "Update uri to clob for Reference";
1599 tableName = "Reference";
1600 columnName = "uri";
1601 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1602 stepList.add(step);
1603
1604 stepName = "Update uri to clob for Rights";
1605 tableName = "Rights";
1606 columnName = "uri";
1607 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1608 stepList.add(step);
1609
1610 stepName = "Update uri to clob for MediaRepresentationPart";
1611 tableName = "MediaRepresentationPart";
1612 columnName = "uri";
1613 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1614 stepList.add(step);
1615
1616 //TODO still needed??
1617 stepName = "Update uri to clob for FeatureTree";
1618 tableName = "FeatureTree";
1619 columnName = "uri";
1620 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1621 stepList.add(step);
1622
1623 //Annotation.linkbackUri (change from URL to URI)
1624 stepName = "Update url to uri (->clob) for Annotation.linkbackUri";
1625 tableName = "Annotation";
1626 columnName = "linkbackUrl";
1627 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1628 stepList.add(step);
1629
1630 }
1631
1632 /**
1633 * @param stepList
1634 * @return
1635 */
1636 private void addTimeperiodToDescriptionElement(
1637 List<ISchemaUpdaterStep> stepList) {
1638 String stepName;
1639 String tableName;
1640 ISchemaUpdaterStep step;
1641 String columnName;
1642 //start #3312
1643 stepName = "Create time period start column in description element base";
1644 tableName = "DescriptionElementBase";
1645 columnName = "timeperiod_start";
1646 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1647 stepList.add(step);
1648
1649 //end #3312
1650 stepName = "Create time period end column in description element base";
1651 tableName = "DescriptionElementBase";
1652 columnName = "timeperiod_end";
1653 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1654 stepList.add(step);
1655
1656 //freetext #3312
1657 stepName = "Create time period freetext column in description element base";
1658 tableName = "DescriptionElementBase";
1659 columnName = "timeperiod_freetext";
1660 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1661 stepList.add(step);
1662
1663 return;
1664 }
1665
1666 private void updateElevationMax(List<ISchemaUpdaterStep> stepList) {
1667 //create column
1668 String stepName = "Create absoluteElevationMax column";
1669 String tableName = "GatheringEvent";
1670 String columnName = "absoluteElevationMax";
1671 ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
1672 stepList.add(step);
1673
1674 String audTableName = "GatheringEvent";
1675 //update max
1676 stepName = "Update gathering elevation max";
1677 //all audits to unknown type
1678 String query = " UPDATE GatheringEvent ge " +
1679 " SET ge.absoluteElevationMax = ge.absoluteElevation + ge.absoluteElevationError, " +
1680 " ge.absoluteElevation = ge.absoluteElevation - ge.absoluteElevationError" +
1681 " WHERE ge.absoluteElevationError is not null ";
1682 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(audTableName);
1683 stepList.add(step);
1684 //TODO same for AUD
1685
1686 //remove error column
1687 stepName = "Remove elevationErrorRadius column";
1688 tableName = "GatheringEvent";
1689 columnName = "absoluteElevationError";
1690 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1691 stepList.add(step);
1692
1693 //create column absoluteElevationText
1694 stepName = "Create absoluteElevationText column";
1695 tableName = "GatheringEvent";
1696 columnName = "absoluteElevationText";
1697 //TODO size
1698 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
1699 stepList.add(step);
1700
1701 //retype distanceToGround
1702 stepName = "Rname distanceToGround column";
1703 tableName = "GatheringEvent";
1704 String strOldColumnName = "distanceToGround";
1705 step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT);
1706 stepList.add(step);
1707
1708 //create column distanceToGroundMax
1709 stepName = "Create distanceToGroundMax column";
1710 tableName = "GatheringEvent";
1711 columnName = "distanceToGroundMax";
1712 step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
1713 stepList.add(step);
1714
1715
1716 //create column distanceToGroundText
1717 stepName = "Create distanceToGroundText column";
1718 tableName = "GatheringEvent";
1719 columnName = "distanceToGroundText";
1720 //TODO size
1721 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
1722 stepList.add(step);
1723
1724 //retype distanceToGround
1725 stepName = "Rname distanceToWaterSurface column";
1726 tableName = "GatheringEvent";
1727 strOldColumnName = "distanceToWaterSurface";
1728 step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT);
1729 stepList.add(step);
1730
1731 //create column distanceToWaterSurface
1732 stepName = "Create distanceToWaterSurfaceMax column";
1733 tableName = "GatheringEvent";
1734 columnName = "distanceToWaterSurfaceMax";
1735 step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
1736 stepList.add(step);
1737
1738
1739 //create column distanceToGroundText
1740 stepName = "Create distanceToWaterSurfaceText column";
1741 tableName = "GatheringEvent";
1742 columnName = "distanceToWaterSurfaceText";
1743 //TODO size
1744 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
1745 stepList.add(step);
1746
1747 }
1748
1749 /**
1750 * @param stepList
1751 */
1752 private void updateOriginalSourceType(List<ISchemaUpdaterStep> stepList) {
1753 String stepName;
1754 String typeAttrName = "sourceType";
1755 ISchemaUpdaterStep step;
1756 String tableName = "OriginalSourceBase";
1757
1758 //all data to unknown
1759 stepName = "Update original source type column: set all to unknown";
1760 String query = String.format("UPDATE OriginalSourceBase " +
1761 " SET %s = '%s' ", typeAttrName, OriginalSourceType.Unknown.getKey());
1762 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1763 stepList.add(step);
1764
1765 //all IMPORTS recognized by idInSOurce and by missing nameInSource
1766 stepName = "Update original source type column: set to 'import' where possible";
1767 query = String.format("UPDATE OriginalSourceBase " +
1768 " SET %s = '%s' " +
1769 " WHERE " +
1770 "((idInSource IS NOT NULL) OR (idNamespace IS NOT NULL)) AND " +
1771 "( nameUsedInSource_id IS NULL AND originalNameString IS NULL ) ", typeAttrName, OriginalSourceType.Import.getKey());
1772 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1773 stepList.add(step);
1774
1775 //all PRIMARY TAXONOMIC SOURCES recognized by missing idInSource and namespace and by existing citation
1776 stepName = "Update original source type column: set to 'primary taxonomic source' where possible";
1777 query = String.format("UPDATE OriginalSourceBase SET %s = '%s' WHERE " +
1778 "(idInSource IS NULL AND idNamespace IS NULL) AND " +
1779 "( citation_id IS NOT NULL ) ", typeAttrName, OriginalSourceType.PrimaryTaxonomicSource.getKey());
1780 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1781 stepList.add(step);
1782 }
1783
1784 /* (non-Javadoc)
1785 * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getNextUpdater()
1786 */
1787 @Override
1788 public ISchemaUpdater getNextUpdater() {
1789 return null;
1790 }
1791
1792 /* (non-Javadoc)
1793 * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getPreviousUpdater()
1794 */
1795 @Override
1796 public ISchemaUpdater getPreviousUpdater() {
1797 return SchemaUpdater_30_301.NewInstance();
1798 }
1799
1800 }