databaseChangeLog: # Application Blocks: upgrade user to BasicUser - changeSet: id: 1495959628118-1 author: matijaobreza (generated) changes: - renameTable: newTableName: userrole oldTableName: user_role - changeSet: id: 1495959628118-3 author: matijaobreza (generated) changes: - renameColumn: columnDataType: varchar(255) newColumnName: fullName oldColumnName: name remarks: Full name of user tableName: user - changeSet: id: 1495959628118-5 author: matijaobreza (generated) changes: - addColumn: tableName: user columns: - column: constraints: nullable: false name: active type: bit - column: name: createdBy type: bigint - column: name: lastModifiedBy type: bigint - column: name: lastModifiedDate type: datetime - column: name: passwordExpires type: datetime - column: name: shortName type: varchar(20) - column: name: version type: integer - changeSet: id: 1495959628118-6 author: mobreza changes: - sql: comment: Migrate user.enabled to user.active = user.enabled; sql: update user set active = enabled; - changeSet: id: 1495959628118-7 author: matijaobreza (generated) changes: - renameColumn: columnDataType: varchar(20) newColumnName: accountType oldColumnName: loginType remarks: Account type tableName: user - sql: comment: loginType#PASSWORD is changed to accountType#LOCAL sql: update user set accountType = 'LOCAL' where accountType = 'PASSWORD'; - changeSet: id: 1495959628118-8 author: mobreza changes: - sql: comment: Assign user.version sql: update user set version = 1 where version is null; - changeSet: id: 1495959628118-9 author: matijaobreza (generated) changes: - addColumn: tableName: user columns: - column: name: createdDate type: datetime - column: name: accountExpires type: datetime - changeSet: id: 1495959628118-10 author: matijaobreza (generated) changes: - addUniqueConstraint: columnNames: shortName constraintName: UC_USERSHORTNAME_COL tableName: user - changeSet: id: 1495959628118-11 author: matijaobreza (generated) changes: - renameColumn: columnDataType: bigint oldColumnName: user_id newColumnName: userId tableName: userrole - renameColumn: columnDataType: varchar(50) oldColumnName: user_role newColumnName: role tableName: userrole # ClassPK - changeSet: id: 1496418311575-1 author: matijaobreza changes: - renameTable: newTableName: classpk oldTableName: classname - changeSet: id: 1496418311575-2 author: matijaobreza (generated) changes: - renameColumn: columnDataType: varchar(250) oldColumnName: className newColumnName: classname tableName: classpk # Country Boundingbox - changeSet: id: 1496779094288-1 author: matijaobreza (generated) changes: - addColumn: tableName: country columns: - column: name: minLongitude type: double - column: name: maxLongitude type: double - column: name: minLatitude type: double - column: name: maxLatitude type: double # System accounts - changeSet: id: 1497558768000-1 author: matijaobreza (generated) changes: - sql: comment: if user#sys==1 convert to accountType#SYSTEM sql: update user set accountType = 'SYSTEM' where sys = 1; - dropColumn: tableName: user columnName: sys rollback: - addColumn: tableName: user columnName: sys type: boolean - sql: comment: accountType#SYSTEM to user#sys==1 sql: update user set sys = 1 where accountType = 'SYSTEM'; - changeSet: id: 1497558768000-2 author: maxymborodenko (generated) changes: - addColumn: tableName: accession columns: - column: name: doi type: varchar(255) rollback: - dropColumn: tableName: accession columnName: doi - changeSet: id: 1497558768000-3 author: maxymborodenko (generated) changes: - addUniqueConstraint: columnNames: doi constraintName: UQ_accession_doi tableName: accession rollback: - dropUniqueConstraint: tableName: accession constraintName: UQ_accession_doi - changeSet: id: 1497558768000-4 author: maxymborodenko (generated) changes: - addColumn: tableName: accessionhistoric columns: - column: name: doi type: varchar(255) - changeSet: id: 1505830865186-1 author: maxymborodenko (generated) changes: - addColumn: tableName: user columns: - column: name: ftpPassword type: varchar(60) # file-repository:0.9-SNAPSHOT - changeSet: id: 1506155384557-1 author: mobreza (generated) changes: - addColumn: columns: - column: constraints: nullable: false name: active type: BIT(1) - column: constraints: nullable: false name: size type: INT(10) - column: name: lastModifiedBy type: BIGINT(19) - column: name: createdBy type: BIGINT(19) tableName: repositorydocument - addNotNullConstraint: columnDataType: varchar(32) columnName: md5Sum tableName: repositorydocument - addNotNullConstraint: columnDataType: varchar(40) columnName: sha1Sum tableName: repositorydocument - addUniqueConstraint: columnNames: path, originalFilename constraintName: UK_iubmgqa5xckodr6fbg6vnne0d tableName: repositorydocument rollback: - dropUniqueConstraint: tableName: repositorydocument constraintName: UK_iubmgqa5xckodr6fbg6vnne0d - dropNotNullConstraint: tableName: repositorydocument columnName: sha1Sum - dropNotNullConstraint: tableName: repositorydocument columnName: md5Sum - dropColumn: tableName: repositorydocument columnName: active - dropColumn: tableName: repositorydocument columnName: size - dropColumn: tableName: repositorydocument columnName: lastModifiedBy - dropColumn: tableName: repositorydocument columnName: createdBy - changeSet: id: 1506155384557-2 author: mobreza (generated) changes: - addColumn: columns: - column: constraints: nullable: false name: active type: BIT(1) - column: constraints: nullable: false name: size type: INT(10) - column: name: lastModifiedBy type: BIGINT(19) - column: name: createdBy type: BIGINT(19) tableName: repositoryfile - addNotNullConstraint: columnDataType: varchar(32) columnName: md5Sum tableName: repositoryfile - addNotNullConstraint: columnDataType: varchar(40) columnName: sha1Sum tableName: repositoryfile - addUniqueConstraint: columnNames: path, originalFilename constraintName: UK_27590ja5w3amn2yj2dryogm6n tableName: repositoryfile rollback: - dropUniqueConstraint: tableName: repositoryfile constraintName: UK_27590ja5w3amn2yj2dryogm6n - dropNotNullConstraint: tableName: repositoryfile columnName: sha1Sum - dropNotNullConstraint: tableName: repositoryfile columnName: md5Sum - dropColumn: tableName: repositoryfile columnName: active - dropColumn: tableName: repositoryfile columnName: size - dropColumn: tableName: repositoryfile columnName: lastModifiedBy - dropColumn: tableName: repositoryfile columnName: createdBy - changeSet: id: 1506155384557-3 author: mobreza (generated) changes: - addColumn: columns: - column: constraints: nullable: false name: active type: BIT(1) - column: name: createdDate type: DATETIME(19) - column: name: createdBy type: BIGINT(19) - column: name: lastModifiedDate type: DATETIME(19) - column: name: lastModifiedBy type: BIGINT(19) tableName: repositorygallery rollback: - dropColumn: tableName: repositorygallery columnName: active - dropColumn: tableName: repositorygallery columnName: createdDate - dropColumn: tableName: repositorygallery columnName: createdBy - dropColumn: tableName: repositorygallery columnName: lastModifiedDate - dropColumn: tableName: repositorygallery columnName: lastModifiedBy - changeSet: id: 1506155384557-4 author: mobreza (generated) changes: - addColumn: columns: - column: constraints: nullable: false name: active type: BIT(1) - column: constraints: nullable: false name: size type: INT(10) - column: name: lastModifiedBy type: BIGINT(19) - column: name: createdBy type: BIGINT(19) tableName: repositoryimage - addNotNullConstraint: columnDataType: varchar(32) columnName: md5Sum tableName: repositoryimage - addNotNullConstraint: columnDataType: varchar(40) columnName: sha1Sum tableName: repositoryimage - addUniqueConstraint: columnNames: path, originalFilename constraintName: UK_88rxxsqrm09geyh5go9ert6qe tableName: repositoryimage rollback: - dropUniqueConstraint: tableName: repositoryimage constraintName: UK_88rxxsqrm09geyh5go9ert6qe - dropNotNullConstraint: tableName: repositoryimage columnName: sha1Sum - dropNotNullConstraint: tableName: repositoryimage columnName: md5Sum - dropColumn: tableName: repositoryimage columnName: active - dropColumn: tableName: repositoryimage columnName: size - dropColumn: tableName: repositoryimage columnName: lastModifiedBy - dropColumn: tableName: repositoryimage columnName: createdBy - changeSet: id: 1506155384557-5 author: mobreza (generated) changes: - dropForeignKeyConstraint: baseTableName: repositorygalleryimage constraintName: FK_rl4ttk3kctjy2nedwwlh8os3p - addAutoIncrement: tableName: repositorygallery columnName: id columnDataType: BIGINT(20) - addForeignKeyConstraint: constraintName: FK_rl4ttk3kctjy2nedwwlh8os3p baseTableName: repositorygalleryimage baseColumnNames: galleryId referencedTableName: repositorygallery referencedColumnNames: id # OAuth from app-blocks-1.3-SNAPSHOT - changeSet: id: 1506594404000-2 author: matijaobreza (generated) comment: naming strategy change for oauthclient changes: - renameColumn: columnDataType: int newColumnName: accessTokenValidity oldColumnName: accessTokenValiditySeconds tableName: oauthclient - renameColumn: columnDataType: int newColumnName: refreshTokenValidity oldColumnName: refreshTokenValiditySeconds tableName: oauthclient - renameColumn: columnDataType: varchar(200) newColumnName: redirect oldColumnName: redirectUris tableName: oauthclient - renameColumn: columnDataType: varchar(200) newColumnName: resource oldColumnName: resourceIds tableName: oauthclient - renameColumn: columnDataType: varchar(200) newColumnName: scope oldColumnName: scope tableName: oauthclient - addColumn: tableName: oauthclient columns: - column: name: grants type: varchar(200) - column: name: autoApprove type: bit constraints: nullable: false - column: name: autoApproveScope type: varchar(200) - dropColumn: tableName: oauthclient columnName: additional_information - changeSet: id: application-blocks-security-1506594404000-2 author: matijaobreza comment: Create 'oauthclientrole' changes: - createTable: tableName: oauthclientrole remarks: OAuth Client role list columns: - column: name: clientId type: bigint constraints: nullable: false - column: name: oauthclientrole type: varchar(200) constraints: nullable: false - addForeignKeyConstraint: constraintName: FK_m2da63y1tfs7mmuv9rht8udt7 baseTableName: oauthclientrole baseColumnNames: clientId referencedTableName: oauthclient referencedColumnNames: id onDelete: CASCADE onUpdate: CASCADE - changeSet: id: 1507131481000-6 author: mobreza changes: - dropColumn: columnName: grants tableName: oauthclient - dropColumn: columnName: authorities tableName: oauthclient - renameColumn: columnDataType: varchar(200) newColumnName: grants oldColumnName: authorizedGrantTypes tableName: oauthclient - sql: comment: Migrate oauthclient , separator to ; sql: update oauthclient set scope = replace(scope, ',', ';'), grants = replace(grants, ',', ';'), redirect = replace(redirect, ',', ';'); - sql: comment: Create oauthclientrole entries sql: insert into oauthclientrole select id, 'USER' from oauthclient; - changeSet: id: 1507131481000-7 author: mobreza changes: - sql: comment: Create oauthclientrole entries sql: update oauthclientrole set oauthclientrole='CLIENT' where oauthclientrole='USER'; - changeSet: id: 1506605582245-23 author: auto-generated changes: - dropTable: cascadeConstraints: true tableName: oauthaccesstoken - createTable: columns: - column: constraints: nullable: false name: tokenId type: VARCHAR(100) - column: name: authentication type: LONGBLOB - column: name: authenticationId type: VARCHAR(100) - column: name: clientId type: VARCHAR(100) - column: name: refreshToken type: VARCHAR(100) - column: name: token type: LONGBLOB - column: name: username type: VARCHAR(100) tableName: oauthaccesstoken - addPrimaryKey: columnNames: tokenId constraintName: PRIMARY tableName: oauthaccesstoken - addUniqueConstraint: columnNames: authenticationId constraintName: UK_mk5dwyhm8t56m9ood899ec3hx tableName: oauthaccesstoken - changeSet: id: 1506605582245-26 author: auto-generated changes: - dropTable: cascadeConstraints: true tableName: oauthrefreshtoken - createTable: columns: - column: constraints: nullable: false name: tokenId type: VARCHAR(100) - column: name: authentication type: LONGBLOB - column: name: token type: LONGBLOB tableName: oauthrefreshtoken - addPrimaryKey: columnNames: tokenId constraintName: PRIMARY tableName: oauthrefreshtoken - changeSet: id: application-blocks-security-1507137884-1 author: matijaobreza comment: Add refreshToken#clientId changes: - addColumn: tableName: oauthrefreshtoken columns: - column: name: clientId type: varchar(100) constraints: nullable: false - changeSet: id: application-blocks-security-1507137884-2 author: matijaobreza comment: Extend OAuth Tokens changes: - addColumn: tableName: oauthaccesstoken columns: - column: name: expiration type: datetime - addColumn: tableName: oauthrefreshtoken columns: - column: name: expiration type: datetime - column: name: username type: varchar(100) - changeSet: id: 1506155384557-6 author: a.lugovskiy (generated) changes: - addColumn: tableName: article columns: - column: name: template type: boolean defaultValue: false - changeSet: id: 1508141056000-1 author: mobreza comment: mysql doesn't really like the float type changes: - renameColumn: columnDataType: double newColumnName: seqNo oldColumnName: seqNo tableName: accession - renameColumn: columnDataType: double newColumnName: seqNo oldColumnName: seqNo tableName: accessionhistoric - changeSet: id: 1508141056000-2 author: mobreza comment: AccessionData extends VersionedModel (adds active) changes: - addColumn: tableName: accession columns: - column: constraints: nullable: false defaultValue: true name: active type: BIT(1) - addColumn: tableName: accessionhistoric columns: - column: constraints: nullable: false defaultValue: true name: active type: BIT(1) - changeSet: id: 1508309640296-1 author: mobreza comment: Add auditlog table changes: - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: action type: VARCHAR(10) - column: name: createdBy type: BIGINT - column: name: entityId type: BIGINT - column: constraints: nullable: false name: logdate type: datetime(6) - column: name: newState type: LONGTEXT - column: name: previousState type: LONGTEXT - column: constraints: nullable: false name: prop type: VARCHAR(50) - column: constraints: nullable: false name: classPk type: BIGINT - column: name: entityClassPk type: BIGINT tableName: auditlog - createIndex: columns: - column: name: classPk indexName: FK_ebwh7n4mlnm1e8ty78h6n279x tableName: auditlog - createIndex: columns: - column: name: entityClassPk indexName: FK_p30btngbfm13fwebfkmklu4wn tableName: auditlog - createIndex: columns: - column: name: entityId - column: name: classPk - column: name: prop indexName: UK_4jxfygboql2y3pd68nmudrgw tableName: auditlog - addForeignKeyConstraint: baseColumnNames: classPk baseTableName: auditlog constraintName: FK_ebwh7n4mlnm1e8ty78h6n279x deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: classpk - addForeignKeyConstraint: baseColumnNames: entityClassPk baseTableName: auditlog constraintName: FK_p30btngbfm13fwebfkmklu4wn deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: classpk - changeSet: id: 1508316829948-1 author: m.borodenko changes: - sql: comment: if article#slug=='smtp.email-new-password' update to article#slug='smtp-email-new-password' sql: update article set slug = 'smtp-email-new-password' where slug = 'smtp.email-new-password'; - sql: comment: if article#slug=='smtp.email-password' update to article#slug='smtp-email-password' sql: update article set slug = 'smtp-email-password' where slug = 'smtp.email-password'; - sql: comment: if article#slug=='smtp.email-verification' update to article#slug='smtp-email-verification' sql: update article set slug = 'smtp-email-verification' where slug = 'smtp.email-verification'; - sql: comment: if article#slug=='smtp.material-request' update to article#slug='smtp-material-request' sql: update article set slug = 'smtp-material-request' where slug = 'smtp.material-request'; - sql: comment: if article#slug=='smtp.material-confirm-no-pid' update to article#slug='smtp-material-confirm-no-pid' sql: update article set slug = 'smtp-material-confirm-no-pid' where slug = 'smtp.material-confirm-no-pid'; - sql: comment: if article#slug=='smtp.material-confirm' update to article#slug='smtp-material-confirm' sql: update article set slug = 'smtp-material-confirm' where slug = 'smtp.material-confirm'; - sql: comment: if article#slug=='user.password-reset' update to article#slug='user-password-reset' sql: update article set slug = 'user-password-reset' where slug = 'user.password-reset'; - sql: comment: if article#slug=='user.password-reset-email-sent' update to article#slug='user-password-reset-email-sent' sql: update article set slug = 'user-password-reset-email-sent' where slug = 'user.password-reset-email-sent'; - sql: comment: if article#slug=='user.reset-password-instructions' update to article#slug='user-reset-password-instructions' sql: update article set slug = 'user-reset-password-instructions' where slug = 'user.reset-password-instructions'; # User and OAuthClient extend AclSid - changeSet: id: 1509390480000-1 author: matijaobreza comment: Migrate ACL to app-blocks-1.3-SNAPSHOT changes: - dropForeignKeyConstraint: baseTableName: acl_entry constraintName: FK_fhuoesmjef3mrv0gpja4shvcr - dropForeignKeyConstraint: baseTableName: acl_entry constraintName: FK_i6xyfccd4y3wlwhgwpo4a9rm1 - dropForeignKeyConstraint: baseTableName: acl_object_identity constraintName: FK_nxv5we2ion9fwedbkge7syoc3 - dropUniqueConstraint: tableName: acl_entry constraintName: UK_gh5egfpe4gaqokya6s0567b0l - renameTable: oldTableName: acl_sid newTableName: acl_sid_backup - renameTable: oldTableName: acl_entry newTableName: acl_entry_backup - createTable: columns: - column: constraints: nullable: true name: type type: INT - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) defaultValue: true - column: constraints: nullable: false name: version type: INT - column: name: createdBy type: BIGINT - column: name: createdDate type: datetime(6) - column: name: lastModifiedBy type: BIGINT - column: name: lastModifiedDate type: datetime(6) - column: constraints: nullable: false name: principal type: BIT(1) - column: constraints: nullable: false name: sid type: VARCHAR(100) tableName: acl_sid - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: ace_order type: BIGINT - column: constraints: nullable: false name: audit_failure type: BIT(1) - column: constraints: nullable: false name: audit_success type: BIT(1) - column: constraints: nullable: false name: granting type: BIT(1) - column: constraints: nullable: false name: mask type: BIGINT - column: constraints: nullable: false name: acl_object_identity type: BIGINT - column: constraints: nullable: false name: sid type: BIGINT tableName: acl_entry - addUniqueConstraint: columnNames: acl_object_identity, ace_order constraintName: UK_gh5egfpe4gaqokya6s0567b0l tableName: acl_entry # Migrate User data to AclSid - sql: comment: Migrate all users with their existing IDs to acl_sid sql: >- insert into acl_sid (type, id, active, version, createdBy, createdDate, lastModifiedBy, lastModifiedDate, principal, sid) select 1, id, active, version, createdBy, createdDate, lastModifiedBy, lastModifiedDate, true, uuid from user; - addForeignKeyConstraint: baseColumnNames: id baseTableName: user constraintName: FK_8qtpnv06elxuryeuv1ac4ximm deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acl_sid - dropColumn: tableName: user columnName: active - dropColumn: tableName: user columnName: version - dropColumn: tableName: user columnName: createdBy - dropColumn: tableName: user columnName: createdDate - dropColumn: tableName: user columnName: lastModifiedBy - dropColumn: tableName: user columnName: lastModifiedDate # Migrate OAuthClient data to AclSid - sql: comment: Migrate OAuthClient to AclSid, they get new IDs, but we can find them with clientId as acl_sid.sid sql: >- insert into acl_sid (type, active, version, createdBy, createdDate, lastModifiedBy, lastModifiedDate, principal, sid) select 2, active, version, createdBy, createdDate, lastModifiedBy, lastModifiedDate, true, clientId from oauthclient; - sql: comment: Update OAuthClient#id values to their new IDs as per acl_sid sql: >- update oauthclient oa join acl_sid sid on sid.sid=oa.clientId set oa.id=sid.id - addForeignKeyConstraint: baseColumnNames: id baseTableName: oauthclient constraintName: FK_j9t6kj0254t7knyn57orqyaxk deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acl_sid - dropColumn: tableName: oauthclient columnName: active - dropColumn: tableName: oauthclient columnName: version - dropColumn: tableName: oauthclient columnName: createdBy - dropColumn: tableName: oauthclient columnName: createdDate - dropColumn: tableName: oauthclient columnName: lastModifiedBy - dropColumn: tableName: oauthclient columnName: lastModifiedDate # Migrate acl_object_identity#owner_sid because these have changed - sql: comment: Migrate acl_object_identity#owner_sid because these have changed sql: >- update acl_object_identity oid join acl_sid_backup oldsid on oldsid.id=oid.owner_sid join acl_sid newsid on newsid.sid=oldsid.sid set oid.owner_sid=newsid.id - addForeignKeyConstraint: baseColumnNames: owner_sid baseTableName: acl_object_identity constraintName: FK_nxv5we2ion9fwedbkge7syoc3 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acl_sid # Migrate acl_sid_backup data for authorities (roles) -- i.e. everything that has not moved - sql: comment: Delete invalid authority records from acl_sid_backup (users and oauthclients) sql: >- delete oldsid from acl_sid_backup oldsid inner join acl_sid newsid on newsid.sid=oldsid.sid where oldsid.principal = 0; - sql: comment: Generate new acl_sid for ROLE entries (AclSid.type==0) sql: >- insert into acl_sid (type, active, version, createdBy, createdDate, lastModifiedBy, lastModifiedDate, principal, sid) select 0, 1, 0, null, now(), null, now(), false, sid from acl_sid_backup where principal = 0; # Migrate acl_entry data - sql: comment: Migrate acl_entry_backup data to acl_entry, using new acl_sid#id instead of the old one sql: >- insert into acl_entry (ace_order, audit_failure, audit_success, granting, mask, acl_object_identity, sid) select e.ace_order, e.audit_failure, e.audit_success, e.granting, e.mask, e.acl_object_identity, newsid.id from acl_entry_backup e inner join acl_sid_backup oldsid on oldsid.id=e.sid inner join acl_sid newsid on newsid.sid=oldsid.sid; - dropTable: tableName: acl_sid_backup - dropTable: tableName: acl_entry_backup # Activate FK constraints - addForeignKeyConstraint: baseColumnNames: acl_object_identity baseTableName: acl_entry constraintName: FK_fhuoesmjef3mrv0gpja4shvcr deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acl_object_identity - addForeignKeyConstraint: baseColumnNames: sid baseTableName: acl_entry constraintName: FK_i6xyfccd4y3wlwhgwpo4a9rm1 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acl_sid # Use user#email as acl_sid#sid - changeSet: id: 1513620354000-1 author: matijaobreza comment: Use user#email as acl_sid#sid changes: - sql: comment: Inner join acl_sid with user on id and update acl_sid#sid with user#email sql: >- update acl_sid inner join user on user.id=acl_sid.id set acl_sid.sid = user.email; # DS2 - changeSet: id: 1525172348000-1 author: Matija Obreza comment: Renaming DS2 tables changes: - renameTable: newTableName: ds2column oldTableName: ds2descriptor - renameTable: newTableName: ds2descriptor oldTableName: descriptor - renameColumn: columnDataType: BIGINT newColumnName: dsc oldColumnName: dsd remarks: Dataset column tableName: ds2value # Upgrade to appliation-blocks:1.4-SNAPSHOT - changeSet: id: 1526423063000-1 author: mobreza comment: Add lastLogin to User changes: - addColumn: columns: - column: name: lastLogin type: datetime(6) tableName: user - changeSet: id: 1530443149000-2 author: mobreza comment: Replace blanks with null and delete empty records changes: - sql: sql: update accession set acceUrl = null where acceUrl = ''; - sql: sql: update accession set acqDate = null where acqDate = ''; - sql: sql: update accessionalias set usedBy = null where usedBy = ''; - sql: sql: delete from accessionalias where name = '' and (usedBy is null or instCode is null); - sql: sql: update accessionbreeding set breederCode = NULL where breederCode = ''; - sql: sql: update accessionbreeding set pedigree = NULL where pedigree = ''; - sql: sql: delete from accessionbreeding where pedigree is null and breederCode is null; - sql: sql: update accessioncollect set collDate = null where collDate = ''; - sql: sql: update accessioncollect set collSite = null where collSite = ''; - sql: sql: update accessioncollect set collCode = null where collCode = ''; - sql: sql: update accessioncollect set collNumb = null where collNumb = ''; - sql: sql: update accessioncollect set collMissId = null where collMissId = ''; - sql: sql: update accessioncollect set collName = null where collName = ''; - sql: sql: update accessioncollect set collInstAddress = null where collInstAddress = ''; - sql: sql: delete from accessioncollect where collDate = null and collSite = null and collCode = null and collNumb = null and collMissId = null and collName = null and collInstAddress = null and collSrc = null; - sql: sql: update accessionexchange set acceNumb = null where acceNumb = ''; - sql: sql: update accessionexchange set donorInst = null where donorInst = ''; - sql: sql: update accessionexchange set donorName = null where donorName = ''; - sql: sql: delete from accessionexchange where acceNumb is null and donorInst is null and donorName is null; - sql: sql: delete from auditlog where classPk = (select id from classpk where classname='org.genesys2.server.model.genesys.PDCI'); # # Upgrade Genesys model: relationships flips # - changeSet: id: 1531216028000-1 author: mobreza (generated) changes: - renameTable: newTableName: accession_alias oldTableName: accessionalias - renameTable: newTableName: accession_collect oldTableName: accessioncollect - renameTable: newTableName: accession_geo oldTableName: accessiongeo - renameTable: newTableName: accession_historic oldTableName: accessionhistoric - renameTable: newTableName: accession_list oldTableName: accelist - renameTable: newTableName: accession_listitem oldTableName: accelistitems - renameTable: newTableName: accession_remark oldTableName: accessionremark - renameTable: newTableName: grin_taxonomy oldTableName: GrinTaxonomy - changeSet: id: 1531216028024-2 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: accessionId type: BIGINT - column: constraints: nullable: false name: breederCode type: VARCHAR(9) tableName: accession_breedercode - addPrimaryKey: columnNames: accessionId, breederCode constraintName: PRIMARY tableName: accession_breedercode - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: accession_breedercode constraintName: FK_i24m3cu3iutcu874affqlph62 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - changeSet: id: 1531216028024-2b author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: accessionId type: BIGINT - column: constraints: nullable: false name: breederName type: VARCHAR(250) tableName: accession_breedername - addPrimaryKey: columnNames: accessionId, breederName constraintName: PRIMARY tableName: accession_breedername - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: accession_breedername constraintName: FK_k4abe2rmtbjbvs1uxv1gv9vyj deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - changeSet: id: 1531216028024-4 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: collectId type: BIGINT - column: constraints: nullable: false name: collCode type: VARCHAR(128) tableName: accession_collect_code - addPrimaryKey: columnNames: collectId, collCode constraintName: PRIMARY tableName: accession_collect_code - addForeignKeyConstraint: baseColumnNames: collectId baseTableName: accession_collect_code constraintName: FK_p16on5ehgaa05ut41f87umkfa deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: accession_collect - changeSet: id: 1531216028024-5 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: collectId type: BIGINT - column: constraints: nullable: false name: collInstAddress type: VARCHAR(128) tableName: accession_collect_instaddr - addPrimaryKey: columnNames: collectId, collInstAddress constraintName: PRIMARY tableName: accession_collect_instaddr - addForeignKeyConstraint: baseColumnNames: collectId baseTableName: accession_collect_instaddr constraintName: FK_t9qrwws135bnmm0kh8fywg5j2 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: accession_collect - changeSet: id: 1531216028024-6 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: collectId type: BIGINT - column: constraints: nullable: false name: collName type: VARCHAR(250) tableName: accession_collect_name - addPrimaryKey: columnNames: collectId, collName constraintName: PRIMARY tableName: accession_collect_name - addForeignKeyConstraint: baseColumnNames: collectId baseTableName: accession_collect_name constraintName: FK_g6h5uuvq16105ljx8r1jjxojr deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: accession_collect - changeSet: id: 1531216028024-7 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: accessionId type: BIGINT - column: constraints: nullable: false name: duplSite type: VARCHAR(9) tableName: accession_duplsite - addPrimaryKey: columnNames: accessionId, duplSite constraintName: PRIMARY tableName: accession_duplsite - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: accession_duplsite constraintName: FK_2se3c4vyb38ci81qch7siopbu deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - changeSet: id: 1531216028024-13 author: mobreza (generated) changes: - createTable: columns: - column: constraints: nullable: false name: accessionId type: BIGINT - column: constraints: nullable: false name: storage type: INT tableName: accession_storage - addPrimaryKey: columnNames: accessionId, storage constraintName: PRIMARY tableName: accession_storage - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: accession_storage constraintName: FK_2lk4y987jla0fka5nafkwmrbq deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - changeSet: id: 1531216028024-15 author: mobreza changes: - addColumn: tableName: accession columns: - column: # constraints: # nullable: false name: genus type: VARCHAR(100) - column: name: ancest type: LONGTEXT - column: name: donorCode type: VARCHAR(9) - column: name: donorName type: VARCHAR(300) - column: name: donorNumb type: VARCHAR(200) - column: name: names type: LONGTEXT - column: name: otherIds type: LONGTEXT - changeSet: id: 1531216028024-15a author: mobreza (generated) changes: - renameColumn: columnDataType: varchar(100) newColumnName: duplSiteStr oldColumnName: duplSite remarks: MCPD DUPLSITE as string tableName: accession - renameColumn: columnDataType: varchar(100) newColumnName: storageStr oldColumnName: storage remarks: MCPD STORAGE as string tableName: accession - renameColumn: columnDataType: varchar(3) newColumnName: origCty oldColumnName: orgCty remarks: MCPD storage as string tableName: accession - changeSet: id: 1531216028024-15b author: mobreza (generated) changes: - addColumn: tableName: accession_historic columns: - column: # constraints: # nullable: false name: genus type: VARCHAR(100) - column: name: ancest type: LONGTEXT - column: name: donorCode type: VARCHAR(9) - column: name: donorName type: VARCHAR(300) - column: name: donorNumb type: VARCHAR(200) - column: name: names type: LONGTEXT - column: name: otherIds type: LONGTEXT - changeSet: id: 1531216028024-15c author: mobreza (generated) changes: - renameColumn: columnDataType: varchar(100) newColumnName: duplSiteStr oldColumnName: duplSite remarks: MCPD DUPLSITE as string tableName: accession_historic - renameColumn: columnDataType: varchar(100) newColumnName: storageStr oldColumnName: storage remarks: MCPD STORAGE as string tableName: accession_historic - renameColumn: columnDataType: varchar(3) newColumnName: origCty oldColumnName: orgCty remarks: MCPD storage as string tableName: accession_historic - changeSet: id: 1531216028024-16 author: mobreza (generated) changes: - addColumn: columns: - column: name: collId type: BIGINT - column: name: geoId type: BIGINT - column: name: pdciId type: BIGINT tableName: acce - addUniqueConstraint: columnNames: collId constraintName: UK_8qrr86qim9fyaevm0gmneylnw tableName: acce - addUniqueConstraint: columnNames: geoId constraintName: UK_a4cq6l63j6xie0fwv806lfyhu tableName: acce - addUniqueConstraint: columnNames: pdciId constraintName: UK_ncmba94f9sebh9h056jqhukum tableName: acce - changeSet: id: 1531219203066-4 author: mobreza (generated) changes: - createIndex: columns: - column: name: acceNumb indexName: IX_acceNumb tableName: accession - changeSet: id: 1531219203066-7 author: mobreza (generated) changes: - dropForeignKeyConstraint: baseTableName: accession_collect constraintName: FK_3xjhvjcunn2ievi36gmuvf6nk - dropUniqueConstraint: constraintName: UK_3xjhvjcunn2ievi36gmuvf6nk tableName: accession_collect - addForeignKeyConstraint: baseColumnNames: collId baseTableName: acce constraintName: FK_8qrr86qim9fyaevm0gmneylnw deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: accession_collect - changeSet: id: 1531219203066-8 author: mobreza (generated) changes: - dropForeignKeyConstraint: baseTableName: accession_geo constraintName: FK_mrtltk1iiohn7x6p6b9saxfee - dropUniqueConstraint: constraintName: UK_mrtltk1iiohn7x6p6b9saxfee tableName: accession_geo - addForeignKeyConstraint: baseColumnNames: geoId baseTableName: acce constraintName: FK_a4cq6l63j6xie0fwv806lfyhu deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: accession_geo - changeSet: id: 1531219203066-9 author: mobreza (generated) changes: - dropForeignKeyConstraint: baseTableName: pdci constraintName: FK_c3fnk9quh9lou6hd1v5hpanbw - dropUniqueConstraint: constraintName: UK_offskcy4kgtcg89hcd8w9phmh tableName: pdci - addForeignKeyConstraint: baseColumnNames: pdciId baseTableName: acce constraintName: FK_ncmba94f9sebh9h056jqhukum deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: pdci - changeSet: id: 1531219203066-35 author: mobreza (generated) changes: - modifyDataType: columnName: datum newDataType: varchar(100) tableName: accession_geo - modifyDataType: columnName: method newDataType: varchar(100) tableName: accession_geo - createIndex: columns: - column: name: latitude - column: name: longitude indexName: UK_98cgokk6e16eqowc7iemvshid tableName: accession_geo - createIndex: columns: - column: name: tileIndex indexName: UK_25vrycbl69i8q41ewm4p9m13r tableName: accession_geo - changeSet: id: 1531219203066-43 author: mobreza (generated) changes: - addNotNullConstraint: columnDataType: clob columnName: remark tableName: accession_remark - changeSet: id: 1531219203099-1 author: mobreza comment: Migrate data to new model changes: - sql: sql: >- drop table if exists t_collCode; create table if not exists t_collCode as select @num_e := 1 + LENGTH(collCode) - LENGTH(REPLACE(collCode, ';', '')) AS num_e, id, collCode, SUBSTRING_INDEX(collCode,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(collCode,';',3),';',-1), null) coll4 from accession_collect where collCode is not null; insert into accession_collect_code (collectId,collCode) (select distinct id, coll1 from (select id, coll1 from t_collCode where coll1 is not null union all select id, coll2 from t_collCode where coll2 is not null union all select id, coll3 from t_collCode where coll3 is not null union all select id, coll4 from t_collCode where coll4 is not null) Q); drop table t_collCode; - dropColumn: columnName: collCode tableName: accession_collect - changeSet: id: 1531219203099-2 author: mobreza comment: Migrate data to new model changes: - sql: sql: >- drop table if exists t_collInstAddress; create table if not exists t_collInstAddress as select @num_e := 1 + LENGTH(collInstAddress) - LENGTH(REPLACE(collInstAddress, ';', '')) AS num_e, id, collInstAddress, SUBSTRING_INDEX(collInstAddress,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(collInstAddress,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(collInstAddress,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(collInstAddress,';',3),';',-1), null) coll4 from accession_collect where collInstAddress is not null; insert into accession_collect_instaddr (collectId,collInstAddress) (select distinct id, coll1 from (select id, coll1 from t_collInstAddress where coll1 is not null union all select id, coll2 from t_collInstAddress where coll2 is not null union all select id, coll3 from t_collInstAddress where coll3 is not null union all select id, coll4 from t_collInstAddress where coll4 is not null) Q); drop table t_collInstAddress; - dropColumn: columnName: collInstAddress tableName: accession_collect - changeSet: id: 1531219203099-3 author: mobreza comment: Migrate data to new model changes: - sql: sql: >- drop table if exists t_collName; create table if not exists t_collName as select @num_e := 1 + LENGTH(collName) - LENGTH(REPLACE(collName, ';', '')) AS num_e, id, collName, SUBSTRING_INDEX(collName,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',3),';',-1), null) coll4 from accession_collect where collName is not null; insert into accession_collect_name (collectId,collName) (select distinct id, coll1 from (select id, coll1 from t_collName where coll1 is not null union all select id, coll2 from t_collName where coll2 is not null union all select id, coll3 from t_collName where coll3 is not null union all select id, coll4 from t_collName where coll4 is not null) Q); drop table t_collName; - dropColumn: columnName: collName tableName: accession_collect - changeSet: id: 1531219203099-3 author: mobreza comment: Migrate data to new model changes: - sql: sql: >- drop table if exists t_collName; create table if not exists t_collName as select @num_e := 1 + LENGTH(collName) - LENGTH(REPLACE(collName, ';', '')) AS num_e, id, collName, SUBSTRING_INDEX(collName,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(collName,';',3),';',-1), null) coll4 from accession_collect where collName is not null; insert into accession_collect_name (collectId,collName) (select distinct id, coll1 from (select id, coll1 from t_collName where coll1 is not null union all select id, coll2 from t_collName where coll2 is not null union all select id, coll3 from t_collName where coll3 is not null union all select id, coll4 from t_collName where coll4 is not null) Q); drop table t_collName; - dropColumn: columnName: collName tableName: accession_collect - changeSet: id: 1531221360983-16 author: mobreza (generated) changes: - sql: sql: >- update accessionbreeding B inner join accession A on A.id = B.accessionId set A.ancest = B.pedigree; - sql: sql: >- update accessionbreeding B inner join accession_historic A on A.id = B.accessionId set A.ancest = B.pedigree; - changeSet: id: 1531221360983-16b author: mobreza (generated) changes: - sql: sql: >- drop table if exists t_breederCode; create table if not exists t_breederCode as select @num_e := 1 + LENGTH(breederCode) - LENGTH(REPLACE(breederCode, ';', '')) AS num_e, accessionId, breederCode, SUBSTRING_INDEX(breederCode,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(breederCode,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(breederCode,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(breederCode,';',3),';',-1), null) coll4 from accessionbreeding where breederCode is not null; insert into accession_breedercode (accessionId,breederCode) (select distinct accessionId, coll1 from (select accessionId, coll1 from t_breederCode where coll1 is not null union all select accessionId, coll2 from t_breederCode where coll2 is not null union all select accessionId, coll3 from t_breederCode where coll3 is not null union all select accessionId, coll4 from t_breederCode where coll4 is not null) Q); drop table t_breederCode; - dropTable: tableName: accessionbreeding - changeSet: id: 1531221360983-37 author: mobreza (generated) changes: - sql: sql: >- update accession a inner join taxonomy2 t on t.id=a.taxonomyId2 set a.genus = t.genus; - sql: sql: >- update accession_historic a inner join taxonomy2 t on t.id=a.taxonomyId2 set a.genus = t.genus; - changeSet: id: 1531221360983-37a author: mobreza (generated) comment: Move duplicates to accession_historic changes: - sql: sql: >- drop table if exists t_accessiondup; create table t_accessiondup select min(id) minId from accession group by instituteId, genus, acceNumb having count(*) > 1; insert into accession_historic (id, acceNumb, seqNo, acqDate, acqSrc, available, duplSiteStr, inSGSV, inTrust, instCode, mlsStat, origCty, sampStat, storageStr, orgCtyId, instituteId, taxonomyId2, taxGenus, historic, createdDate, lastModifiedDate, createdBy, lastModifiedBy, version, acceurl, cropName, cropId, doi, active, genus, ancest, donorCode, donorName, donorNumb, names, otherIds) select id, acceNumb, seqNo, acqDate, acqSrc, available, duplSiteStr, inSGSV, inTrust, instCode, mlsStat, origCty, sampStat, storageStr, orgCtyId, instituteId, taxonomyId2, taxGenus, historic, createdDate, lastModifiedDate, createdBy, lastModifiedBy, version, acceurl, cropName, cropId, doi, active, genus, ancest, donorCode, donorName, donorNumb, names, otherIds from accession where id in (select minId from t_accessiondup); delete from accession where id in (select minId from t_accessiondup); drop table t_accessiondup; - changeSet: id: 1531221360983-37b1 author: mobreza (generated) changes: - dropUniqueConstraint: constraintName: UK_975ha8nh9k5itsuvg4bd0853i tableName: accession - changeSet: id: 1531221360983-37b2 author: mobreza (generated) changes: - dropUniqueConstraint: constraintName: UQ_accession_genus_inst tableName: accession - changeSet: id: 1531221360983-37c author: mobreza (generated) changes: - addNotNullConstraint: columnDataType: varchar(100) columnName: genus tableName: accession - addUniqueConstraint: columnNames: instituteId, genus, acceNumb constraintName: UQ_accession_genus_inst tableName: accession - addNotNullConstraint: columnDataType: varchar(100) columnName: genus tableName: accession_historic - dropColumn: columnName: taxGenus tableName: accession - dropColumn: columnName: taxGenus tableName: accession_historic - changeSet: id: 1531221360983-36 author: mobreza (generated) changes: - dropIndex: indexName: I_accessionalias_name tableName: accession_alias - dropColumn: columnName: instCode tableName: accession_alias - changeSet: id: 1531221360983-29 author: mobreza (generated) changes: - sql: sql: >- update accession_collect C inner join acce A on A.id = C.accessionId set A.collId = C.id; - dropColumn: columnName: accessionId tableName: accession_collect - changeSet: id: 1531221360983-30 author: mobreza (generated) changes: - sql: sql: >- update accession_geo G inner join acce A on A.id = G.accessionId set A.geoId = G.id; - dropColumn: columnName: accessionId tableName: accession_geo - changeSet: id: 1531221360983-31 author: mobreza (generated) changes: - sql: sql: >- update pdci P inner join acce A on A.id = P.accessionId set A.pdciId = P.id; - dropColumn: columnName: accessionId tableName: pdci - changeSet: id: 1531221360983-116 author: mobreza (generated) changes: - modifyDataType: columnName: uniqueAcceNumbs newDataType: boolean tableName: faoinstitute - addDefaultValue: columnDataType: boolean columnName: uniqueAcceNumbs defaultValueBoolean: false tableName: faoinstitute - modifyDataType: columnName: allowMaterialRequests newDataType: boolean tableName: faoinstitute - addDefaultValue: columnDataType: boolean columnName: allowMaterialRequests defaultValueBoolean: false tableName: faoinstitute - changeSet: id: 1531221360983-18 author: mobreza (generated) changes: - sql: sql: >- insert into accession_storage (accessionId, storage) select accessionId, storage from accessionstorage; insert into accession_storage (accessionId, storage) select accessionId, storage from accessionstorageh; - dropTable: tableName: accessionstorage - dropTable: tableName: accessionstorageh - changeSet: id: 1531221360983-17 author: mobreza (generated) changes: - sql: sql: >- update accessionexchange E inner join accession A on A.id=E.accessionId set A.donorCode=E.donorInst, A.donorName=E.donorName, A.donorNumb=E.acceNumb; - sql: sql: >- update accessionexchange E inner join accession_historic A on A.id=E.accessionId set A.donorCode=E.donorInst, A.donorName=E.donorName, A.donorNumb=E.acceNumb; - dropTable: tableName: accessionexchange - changeSet: id: 1531221360981-1 author: mobreza (generated) changes: - sql: sql: >- update accession set duplSiteStr = null where duplSiteStr like '%:%'; - sql: sql: >- drop table if exists t_duplsite; create table if not exists t_duplsite as select @num_e := 1 + LENGTH(duplSiteStr) - LENGTH(REPLACE(duplSiteStr, ';', '')) AS num_e, id, duplSiteStr, SUBSTRING_INDEX(duplSiteStr,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',3),';',-1), null) coll4 from accession where duplSiteStr is not null; insert into accession_duplsite (accessionId, duplSite) (select distinct id, coll1 from (select id, coll1 from t_duplsite where coll1 is not null union all select id, coll2 from t_duplsite where coll2 is not null union all select id, coll3 from t_duplsite where coll3 is not null union all select id, coll4 from t_duplsite where coll4 is not null) Q); drop table t_duplsite; - changeSet: id: 1531221360981-2 author: mobreza (generated) changes: - sql: sql: >- update accession_historic set duplSiteStr = null where duplSiteStr like '%:%'; - sql: sql: >- drop table if exists t_duplsite; create table if not exists t_duplsite as select @num_e := 1 + LENGTH(duplSiteStr) - LENGTH(REPLACE(duplSiteStr, ';', '')) AS num_e, id, duplSiteStr, SUBSTRING_INDEX(duplSiteStr,';',1) as coll1, IF(@num_e > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',2),';',-1), null) coll2, IF(@num_e > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',3),';',-1), null) coll3, IF(@num_e > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(duplSiteStr,';',3),';',-1), null) coll4 from accession_historic where duplSiteStr is not null; insert into accession_duplsite (accessionId, duplSite) (select distinct id, coll1 from (select id, coll1 from t_duplsite where coll1 is not null union all select id, coll2 from t_duplsite where coll2 is not null union all select id, coll3 from t_duplsite where coll3 is not null union all select id, coll4 from t_duplsite where coll4 is not null) Q); drop table t_duplsite; - changeSet: id: 1531483642000-1 author: mobreza changes: - createIndex: columns: - column: name: instituteId - column: name: seqNo indexName: IX_inst_seq tableName: accession - createIndex: columns: - column: name: orgCtyId - column: name: seqNo indexName: IX_cty_seq tableName: accession - createIndex: columns: - column: name: taxonomyId2 - column: name: seqNo indexName: IX_taxa_seq tableName: accession - changeSet: id: 1531483642000-2 author: mobreza comment: Remove trailing whitespace in taxonomy2 table changes: - sql: sql: >- update taxonomy2 set genus=trim(genus) where genus like '% '; update taxonomy2 set species=trim(species) where species like '% '; update taxonomy2 set subtaxa=trim(subtaxa) where subtaxa like '% '; - changeSet: id: 1531483642000-3 author: mobreza comment: Remove blanks in accession table changes: - sql: sql: >- update accession set acqSrc = null where acqSrc = ''; - changeSet: id: 1531483642000-4 author: mobreza comment: Remove blanks in accession table changes: - sql: sql: >- update accession set duplSiteStr = null where duplSiteStr = ''; update accession set storageStr = null where storageStr = '';