cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.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.v33_34;
  12
  13 import java.util.ArrayList;
  14 import java.util.List;
  15
  16 import org.apache.log4j.Logger;
  17
  18 import eu.etaxonomy.cdm.database.update.ColumnAdder;
  19 import eu.etaxonomy.cdm.database.update.ColumnNameChanger;
  20 import eu.etaxonomy.cdm.database.update.ColumnRemover;
  21 import eu.etaxonomy.cdm.database.update.ColumnTypeChanger;
  22 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
  23 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
  24 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
  25 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
  26 import eu.etaxonomy.cdm.database.update.TableCreator;
  27 import eu.etaxonomy.cdm.database.update.TableDroper;
  28 import eu.etaxonomy.cdm.database.update.v31_33.SchemaUpdater_33_331;
  29
  30 /**
  31  * @author a.mueller
  32  * @created Jan 14, 2014
  33  */
  34 public class SchemaUpdater_331_34 extends SchemaUpdaterBase {
  35
  36         @SuppressWarnings("unused")
  37         private static final Logger logger = Logger.getLogger(SchemaUpdater_331_34.class);
  38         private static final String startSchemaVersion = "3.3.1.0.201401140000";
  39         private static final String endSchemaVersion = "3.4.0.0.201407010000";
  40
  41         // ********************** FACTORY METHOD
  42         // *******************************************
  43
  44         public static SchemaUpdater_331_34 NewInstance() {
  45                 return new SchemaUpdater_331_34();
  46         }
  47
  48         /**
  49          * @param startSchemaVersion
  50          * @param endSchemaVersion
  51          */
  52         protected SchemaUpdater_331_34() {
  53                 super(startSchemaVersion, endSchemaVersion);
  54         }
  55
  56         @Override
  57         protected List<ISchemaUpdaterStep> getUpdaterList() {
  58
  59                 String stepName;
  60                 String tableName;
  61                 ISchemaUpdaterStep step;
  62                 String columnName;
  63
  64                 List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
  65
  66                 //TODO H2 / PostGreSQL / SQL Server
  67                 //UserAccount unique
  68                 stepName = "Update User unique indexes";
  69                 tableName = "UserAccount";
  70                 columnName = "username";
  71                 step = UsernameConstraintUpdater.NewInstance(stepName, tableName, columnName);
  72                 stepList.add(step);
  73
  74                 //TODO H2 / PostGreSQL / SQL Server
  75                 //PermissionGroup unique
  76                 stepName = "Update Group unique indexes";
  77                 tableName = "PermissionGroup";
  78                 columnName = "name";
  79                 step = UsernameConstraintUpdater.NewInstance(stepName, tableName, columnName);
  80                 stepList.add(step);
  81
  82                 //TODO H2 / PostGreSQL / SQL Server
  83                 //GrantedAuthority unique
  84                 stepName = "Update User unique indexes";
  85                 tableName = "GrantedAuthorityImpl";
  86                 columnName = "authority";
  87                 step = UsernameConstraintUpdater.NewInstance(stepName, tableName, columnName);
  88                 stepList.add(step);
  89
  90                 //TODO H2 / PostGreSQL / SQL Server
  91                 stepName = "Add label column to derived unit";
  92                 tableName = "SpecimenOrObservationBase";
  93                 columnName = "originalLabelInfo";
  94                 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
  95                 stepList.add(step);
  96
  97
  98                 //TODO test with data and H2 / PostGreSQL / SQL Server
  99                 //set default value to true where required
 100                 stepName = "Set publish to true if null";
 101                 String query = " UPDATE @@TaxonBase@@ " +
 102                                         " SET publish = @TRUE@ " +
 103                                         " WHERE DTYPE IN ('Synonym') AND publish IS NULL ";
 104                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonBase", 99);
 105                 stepList.add(step);
 106
 107
 108                 addIdentifierTables(stepList);
 109
 110
 111                 //remove series from Reference  #4293
 112                 stepName = "Copy series to series part";
 113                 String sql = " UPDATE Reference r " +
 114                                 " SET r.seriespart = r.series " +
 115                                 " WHERE r.series is NOT NULL AND r.seriesPart IS NULL ";
 116                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "Reference", 99);
 117                 stepList.add(step);
 118
 119                 stepName = "Set series to NULL";
 120                 sql = " UPDATE Reference r " +
 121                                 " SET r.series = NULL " +
 122                                 " WHERE r.series = r.seriesPart ";
 123                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "Reference", 99);
 124                 stepList.add(step);
 125
 126                 //TODO check all series are null
 127
 128                 stepName = "Remove series column";
 129                 tableName = "Reference";
 130                 String oldColumnName = "series";
 131                 step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT);
 132                 stepList.add(step);
 133
 134                 //authorTeam -> authorship
 135                 stepName = "Rename Reference.authorTeam column";
 136                 tableName = "Reference";
 137                 oldColumnName = "authorTeam_id";
 138                 String newColumnName = "authorship_id";
 139                 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
 140                 stepList.add(step);
 141
 142                 //remove CDM_VIEW #4316
 143                 stepName = "Remove CDM_VIEW_CDM_VIEW table";
 144                 tableName = "CDM_VIEW_CDM_VIEW";
 145                 boolean ifExists = true;
 146                 step = TableDroper.NewInstance(stepName, tableName, ! INCLUDE_AUDIT, ifExists);
 147                 stepList.add(step);
 148
 149                 stepName = "Remove CDM_VIEW table";
 150                 tableName = "CDM_VIEW";
 151                 ifExists = true;
 152                 step = TableDroper.NewInstance(stepName, tableName, ! INCLUDE_AUDIT, ifExists);
 153                 stepList.add(step);
 154
 155                 //change size of AgentBase_contact_urls.contact_urls_element  #3920
 156                 stepName = "Change length of AgentBase_contact_urls.contact_urls_element";
 157                 tableName = "AgentBase_contact_urls";
 158                 columnName = "contact_urls_element";
 159                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName,
 160                                 columnName, 330, INCLUDE_AUDIT);
 161                 stepList.add(step);
 162
 163                 //TODO not null on username, groupname and authority  #4382
 164
 165                 //DnaQuality #4434
 166                 //Identifier
 167                 stepName = "Create dna quality";
 168                 boolean includeCdmBaseAttributes = true;
 169                 tableName = "DnaQuality";
 170                 String[] columnNames = new String[]{"concentration","ratioofabsorbance230_260", "ratioofabsorbance260_280","qualityterm_id"};
 171                 String[] columnTypes = new String[]{"double","double","double","int"};
 172                 String[] referencedTables = new String[]{null,null,null,"DefinedTermBase"};
 173                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 174                 stepList.add(step);
 175
 176                 //DnaQuality in TissueSample
 177                 //TODO H2 / PostGreSQL / SQL Server
 178                 stepName = "Add foreign key to dna quality";
 179                 tableName = "SpecimenOrObservationBase";
 180                 newColumnName = "dnaQuality_id";
 181                 boolean notNull = false;
 182                 String referencedTable = "DnaQuality";
 183                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, referencedTable);
 184                 stepList.add(step);
 185
 186                 //time scope for classifications
 187                 //TODO H2 / PostGreSQL / SQL Server
 188                 stepName = "Add time scope (start) for classifications";
 189                 tableName = "Classification";
 190                 newColumnName = "timeperiod_start";
 191                 int length = 255;
 192                 step = ColumnAdder.NewStringInstance(stepName, tableName, newColumnName, length, INCLUDE_AUDIT);
 193                 stepList.add(step);
 194
 195                 //TODO H2 / PostGreSQL / SQL Server
 196                 stepName = "Add time scope (end) for classifications";
 197                 tableName = "Classification";
 198                 newColumnName = "timeperiod_end";
 199                 length = 255;
 200                 step = ColumnAdder.NewStringInstance(stepName, tableName, newColumnName, length, INCLUDE_AUDIT);
 201                 stepList.add(step);
 202
 203                 //TODO H2 / PostGreSQL / SQL Server
 204                 stepName = "Add time scope (freetext) for classifications";
 205                 tableName = "Classification";
 206                 newColumnName = "timeperiod_freetext";
 207                 length = 255;
 208                 step = ColumnAdder.NewStringInstance(stepName, tableName, newColumnName, length, INCLUDE_AUDIT);
 209                 stepList.add(step);
 210
 211                 //Classification_GeoScope
 212                 //TODO
 213                 stepName = "Create Classification_GeoScope table";
 214                 includeCdmBaseAttributes = false;
 215                 tableName = "Classification_GeoScope";
 216                 columnNames = new String[]{"Classification_id","geoScopes_id"};
 217                 columnTypes = new String[]{"int","int"};
 218                 referencedTables = new String[]{"Classification","DefinedTermBase"};
 219                 TableCreator creator = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 220                 creator.setPrimaryKeyParams("Classification_id,geoScopes_id", "REV,Classification_id,geoScopes_id");
 221                 stepList.add(creator);
 222
 223
 224                 return stepList;
 225
 226         }
 227
 228         private void addIdentifierTables(List<ISchemaUpdaterStep> stepList) {
 229
 230                 //Identifier
 231                 String stepName = "Create Identifier table";
 232                 boolean includeCdmBaseAttributes = true;
 233                 String tableName = "Identifier";
 234                 String[] columnNames = new String[]{"identifier","identifiedObj_type", "identifiedObj_id","type_id"};
 235                 String[] columnTypes = new String[]{"string_800","string_255","int","int"};
 236                 String[] referencedTables = new String[]{null,null,null,"DefinedTermBase"};
 237                 TableCreator step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 238                 stepList.add(step);
 239
 240                 //AgentBase_Identifier
 241                 stepName = "Create AgentBase_Identifier table";
 242                 includeCdmBaseAttributes = false;
 243                 tableName = "AgentBase_Identifier";
 244                 columnNames = new String[]{"AgentBase_id","identifiers_id","sortIndex"};
 245                 columnTypes = new String[]{"int","int","int"};
 246                 referencedTables = new String[]{"AgentBase","Identifier",null};
 247                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 248                 step.setPrimaryKeyParams("AgentBase_id,identifiers_id", "REV,AgentBase_id,identifiers_id");
 249                 stepList.add(step);
 250
 251                 //Classification_Identifier
 252                 stepName = "Create Classification_Identifier table";
 253                 includeCdmBaseAttributes = false;
 254                 tableName = "Classification_Identifier";
 255                 columnNames = new String[]{"Classification_id","identifiers_id","sortIndex"};
 256                 columnTypes = new String[]{"int","int","int"};
 257                 referencedTables = new String[]{"Classification","Identifier",null};
 258                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 259                 step.setPrimaryKeyParams("Classification_id,identifiers_id", "REV,Classification_id,identifiers_id");
 260                 stepList.add(step);
 261
 262                 //Collection_Identifier
 263                 stepName = "Create Collection_Identifier table";
 264                 includeCdmBaseAttributes = false;
 265                 tableName = "Collection_Identifier";
 266                 columnNames = new String[]{"Collection_id","identifiers_id","sortIndex"};
 267                 columnTypes = new String[]{"int","int","int"};
 268                 referencedTables = new String[]{"Collection","Identifier",null};
 269                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 270                 step.setPrimaryKeyParams("Collection_id,identifiers_id", "REV,Collection_id,identifiers_id");
 271                 stepList.add(step);
 272
 273                 //DefinedTermBase_Identifier
 274                 stepName = "Create DefinedTermBase_Identifier table";
 275                 includeCdmBaseAttributes = false;
 276                 tableName = "DefinedTermBase_Identifier";
 277                 columnNames = new String[]{"DefinedTermBase_id","identifiers_id","sortIndex"};
 278                 columnTypes = new String[]{"int","int","int"};
 279                 referencedTables = new String[]{"DefinedTermBase","Identifier",null};
 280                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 281                 step.setPrimaryKeyParams("DefinedTermBase_id,identifiers_id", "REV,DefinedTermBase_id,identifiers_id");
 282                 stepList.add(step);
 283
 284                 //DescriptionBase_Identifier
 285                 stepName = "Create DescriptionBase_Identifier table";
 286                 includeCdmBaseAttributes = false;
 287                 tableName = "DescriptionBase_Identifier";
 288                 columnNames = new String[]{"DescriptionBase_id","identifiers_id","sortIndex"};
 289                 columnTypes = new String[]{"int","int","int"};
 290                 referencedTables = new String[]{"DescriptionBase","Identifier",null};
 291                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 292                 step.setPrimaryKeyParams("DescriptionBase_id,identifiers_id", "REV,DescriptionBase_id,identifiers_id");
 293                 stepList.add(step);
 294
 295                 //FeatureTree_Identifier
 296                 stepName = "Create FeatureTree_Identifier table";
 297                 includeCdmBaseAttributes = false;
 298                 tableName = "FeatureTree_Identifier";
 299                 columnNames = new String[]{"FeatureTree_id","identifiers_id","sortIndex"};
 300                 columnTypes = new String[]{"int","int","int"};
 301                 referencedTables = new String[]{"FeatureTree","Identifier",null};
 302                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 303                 step.setPrimaryKeyParams("FeatureTree_id,identifiers_id", "REV,FeatureTree_id,identifiers_id");
 304                 stepList.add(step);
 305
 306                 //Media_Identifier
 307                 stepName = "Create Media_Identifier table";
 308                 includeCdmBaseAttributes = false;
 309                 tableName = "Media_Identifier";
 310                 columnNames = new String[]{"Media_id","identifiers_id","sortIndex"};
 311                 columnTypes = new String[]{"int","int","int"};
 312                 referencedTables = new String[]{"Media","Identifier",null};
 313                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 314                 step.setPrimaryKeyParams("Media_id,identifiers_id", "REV,Media_id,identifiers_id");
 315                 stepList.add(step);
 316
 317                 //PolytomousKey_Identifier
 318                 stepName = "Create PolytomousKey_Identifier table";
 319                 includeCdmBaseAttributes = false;
 320                 tableName = "PolytomousKey_Identifier";
 321                 columnNames = new String[]{"PolytomousKey_id","identifiers_id","sortIndex"};
 322                 columnTypes = new String[]{"int","int","int"};
 323                 referencedTables = new String[]{"PolytomousKey","Identifier",null};
 324                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 325                 step.setPrimaryKeyParams("PolytomousKey_id,identifiers_id", "REV,PolytomousKey_id,identifiers_id");
 326                 stepList.add(step);
 327
 328                 //Reference_Identifier
 329                 stepName = "Create Reference_Identifier table";
 330                 includeCdmBaseAttributes = false;
 331                 tableName = "Reference_Identifier";
 332                 columnNames = new String[]{"Reference_id","identifiers_id","sortIndex"};
 333                 columnTypes = new String[]{"int","int","int"};
 334                 referencedTables = new String[]{"Reference","Identifier",null};
 335                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 336                 step.setPrimaryKeyParams("Reference_id,identifiers_id", "REV,Reference_id,identifiers_id");
 337                 stepList.add(step);
 338
 339                 //SpecimenOrObservationBase_Identifier
 340                 stepName = "Create SpecimenOrObservationBase_Identifier table";
 341                 includeCdmBaseAttributes = false;
 342                 tableName = "SpecimenOrObservationBase_Identifier";
 343                 columnNames = new String[]{"SpecimenOrObservationBase_id","identifiers_id","sortIndex"};
 344                 columnTypes = new String[]{"int","int","int"};
 345                 referencedTables = new String[]{"SpecimenOrObservationBase","Identifier",null};
 346                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 347                 step.setPrimaryKeyParams("SpecimenOrObservationBase_id,identifiers_id", "REV,SpecimenOrObservationBase_id,identifiers_id");
 348                 stepList.add(step);
 349
 350                 //TaxonBase_Identifier
 351                 stepName = "Create TaxonBase_Identifier table";
 352                 includeCdmBaseAttributes = false;
 353                 tableName = "TaxonBase_Identifier";
 354                 columnNames = new String[]{"TaxonBase_id","identifiers_id","sortIndex"};
 355                 columnTypes = new String[]{"int","int","int"};
 356                 referencedTables = new String[]{"TaxonBase","Identifier",null};
 357                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 358                 step.setPrimaryKeyParams("TaxonBase_id,identifiers_id", "REV,TaxonBase_id,identifiers_id");
 359                 stepList.add(step);
 360
 361                 //TaxonNameBase_Identifier
 362                 stepName = "Create TaxonNameBase_Identifier table";
 363                 includeCdmBaseAttributes = false;
 364                 tableName = "TaxonNameBase_Identifier";
 365                 columnNames = new String[]{"TaxonNameBase_id","identifiers_id","sortIndex"};
 366                 columnTypes = new String[]{"int","int","int"};
 367                 referencedTables = new String[]{"TaxonNameBase","Identifier",null};
 368                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 369                 step.setPrimaryKeyParams("TaxonNameBase_id,identifiers_id", "REV,TaxonNameBase_id,identifiers_id");
 370                 stepList.add(step);
 371
 372                 //TermVocabulary_Identifier
 373                 stepName = "Create TermVocabulary_Identifier table";
 374                 includeCdmBaseAttributes = false;
 375                 tableName = "TermVocabulary_Identifier";
 376                 columnNames = new String[]{"TermVocabulary_id","identifiers_id","sortIndex"};
 377                 columnTypes = new String[]{"int","int","int"};
 378                 referencedTables = new String[]{"TermVocabulary","Identifier",null};
 379                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes, referencedTables, INCLUDE_AUDIT, includeCdmBaseAttributes);
 380                 step.setPrimaryKeyParams("TermVocabulary_id,identifiers_id", "REV,TermVocabulary_id,identifiers_id");
 381                 stepList.add(step);
 382
 383         }
 384
 385         @Override
 386         public ISchemaUpdater getNextUpdater() {
 387                 return null;
 388         }
 389
 390         @Override
 391         public ISchemaUpdater getPreviousUpdater() {
 392                 return SchemaUpdater_33_331.NewInstance();
 393         }
 394
 395 }