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; # BUG: This kills mariadb when running as non-root in cluster mode # - 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 inner join acce on acce.id=accessionstorage.accessionId; insert into accession_storage (accessionId, storage) select accessionId, storage from accessionstorageh inner join acce on acce.id=accessionstorageh.accessionId; - 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 = ''; - changeSet: id: 1517579783779-1 author: mborodenko changes: - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: uuid type: BINARY(16) - column: constraints: nullable: false name: version type: INT - column: constraints: nullable: false name: active type: boolean - column: name: dateCreated type: VARCHAR(100) - column: name: description type: LONGTEXT - column: name: published type: boolean - column: name: publisher type: VARCHAR(250) - column: name: rights type: VARCHAR(100) - column: constraints: nullable: false name: title type: VARCHAR(250) - column: constraints: nullable: false name: wiews_code type: VARCHAR(8) - column: constraints: nullable: false name: institute_id type: BIGINT - column: constraints: nullable: false name: accession_count type: INT - column: name: createdBy type: BIGINT - column: name: createdDate type: datetime - column: name: lastModifiedBy type: BIGINT - column: name: lastModifiedDate type: datetime tableName: subset - addUniqueConstraint: columnNames: uuid constraintName: UK_7dqvpc7p1sj2gd5yqpp2esrim tableName: subset - addForeignKeyConstraint: baseColumnNames: institute_id baseTableName: subset constraintName: FK_i4bhde33tt9c8u4uuikwdtu2u deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: faoinstitute - createIndex: columns: - column: name: institute_id indexName: FK_i4bhde33tt9c8u4uuikwdtu2u tableName: subset - changeSet: id: 1517579783779-2 author: mborodenko changes: - createTable: columns: - column: constraints: nullable: false name: subset_id type: BIGINT - column: constraints: nullable: false name: acce_id type: BIGINT tableName: subset_accession - createIndex: columns: - column: name: subset_id indexName: FK_deitjtix5v97n7jrh6vmbr9qc tableName: subset_accession - createIndex: columns: - column: name: acce_id indexName: FK_katykqajfv00xx2l9rds8244a tableName: subset_accession - addForeignKeyConstraint: baseColumnNames: acce_id baseTableName: subset_accession constraintName: FK_95rt3vjsm7s4j10s28p0qgf1y deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - addForeignKeyConstraint: baseColumnNames: subset_id baseTableName: subset_accession constraintName: FK_cdu46dj9nbw21wi40rky6jy93 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: subset - changeSet: id: 1531941193570-18 author: mobreza (generated) comment: Changes for file-repository@1.0-SNAPSHOT changes: - renameTable: newTableName: repository_document oldTableName: repositorydocument - renameTable: newTableName: repository_file oldTableName: repositoryfile - renameTable: newTableName: repository_gallery oldTableName: repositorygallery - renameTable: newTableName: repository_gallery_image oldTableName: repositorygalleryimage - renameTable: newTableName: repository_image oldTableName: repositoryimage # Catalog imported to genesys - changeSet: id: 1531942280911-1 author: mobreza (generated) changes: - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: accessions type: INT - column: name: created type: VARCHAR(200) - column: name: description type: LONGTEXT - column: name: descriptors type: INT - column: name: format type: VARCHAR(255) - column: name: language type: VARCHAR(255) - column: constraints: nullable: false name: published type: BIT(1) - column: name: rights type: VARCHAR(255) - column: name: source type: VARCHAR(255) - column: name: subject type: VARCHAR(255) - column: name: title type: VARCHAR(255) - column: name: versionTag type: VARCHAR(255) - column: constraints: nullable: false name: partnerId type: BIGINT - column: constraints: nullable: false name: versionsId type: BIGINT tableName: dataset - createTable: columns: - column: constraints: nullable: false name: datasetId type: BIGINT - column: constraints: nullable: false name: acceNumb type: VARCHAR(50) - column: name: doi type: VARCHAR(100) - column: constraints: nullable: false name: genus type: VARCHAR(100) - column: constraints: nullable: false name: instCode type: VARCHAR(10) - column: name: species type: VARCHAR(100) tableName: dataset_accessions - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: email type: VARCHAR(255) - column: name: fax type: VARCHAR(255) - column: constraints: nullable: false name: fullName type: VARCHAR(200) - column: name: instituteAddress type: VARCHAR(255) - column: name: institutionalAffiliation type: VARCHAR(255) - column: name: phoneNumber type: VARCHAR(255) - column: name: role type: VARCHAR(255) - column: constraints: nullable: false name: datasetId type: BIGINT tableName: dataset_creator - createTable: columns: - column: constraints: nullable: false name: datasetId type: BIGINT - column: constraints: nullable: false name: crop type: VARCHAR(20) tableName: dataset_crops - createTable: columns: - column: constraints: nullable: false name: datasetId type: BIGINT - column: constraints: nullable: false name: descriptorId type: BIGINT - column: constraints: nullable: false name: position type: INT tableName: dataset_descriptor - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: decimalLatitude type: DOUBLE - column: name: decimalLongitude type: DOUBLE - column: name: mapCountry type: VARCHAR(255) - column: name: stateProvince type: VARCHAR(255) - column: name: userCountry type: VARCHAR(255) - column: name: verbatimLocality type: VARCHAR(255) - column: constraints: nullable: false name: datasetId type: BIGINT tableName: dataset_location - createTable: columns: - column: constraints: nullable: false name: datasetId type: BIGINT - column: constraints: nullable: false name: repositoryfileId type: BIGINT - column: constraints: nullable: false name: position type: INT tableName: dataset_repositoryfile - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: currentVersion_id type: BIGINT tableName: dataset_version - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: bibliographicCitation type: LONGTEXT - column: constraints: nullable: false name: category type: VARCHAR(20) - column: name: columnName type: VARCHAR(50) - column: name: crop type: VARCHAR(100) - column: constraints: nullable: false name: dataType type: VARCHAR(255) - column: name: description type: LONGTEXT - column: name: integerOnly type: BIT(1) - column: constraints: nullable: false name: keyDescriptor type: BIT(1) - column: name: max_value type: DOUBLE - column: name: min_value type: DOUBLE - column: constraints: nullable: false name: published type: BIT(1) - column: name: publisher type: VARCHAR(200) - column: constraints: nullable: false name: title type: VARCHAR(255) - column: name: uom type: VARCHAR(20) - column: constraints: nullable: false name: versionTag type: VARCHAR(255) - column: constraints: nullable: false name: partnerId type: BIGINT - column: name: vocabularyId type: BIGINT tableName: descriptor - createTable: columns: - column: constraints: nullable: false name: descriptorId type: BIGINT - column: constraints: nullable: false name: termId type: BIGINT - column: constraints: nullable: false name: idx type: INT tableName: descriptor_term - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: bibliographicCitation type: LONGTEXT - column: name: crop type: VARCHAR(100) - column: name: description type: LONGTEXT - column: constraints: nullable: false name: published type: BIT(1) - column: name: publisher type: VARCHAR(255) - column: constraints: nullable: false name: title type: VARCHAR(255) - column: name: url type: VARCHAR(255) - column: constraints: nullable: false name: versionTag type: VARCHAR(255) - column: constraints: nullable: false name: partnerId type: BIGINT tableName: descriptorlist - createTable: columns: - column: constraints: nullable: false name: descriptorListId type: BIGINT - column: constraints: nullable: false name: descriptorId type: BIGINT - column: constraints: nullable: false name: position type: INT tableName: descriptorlist_descriptor - createTable: columns: - column: constraints: nullable: false name: descriptorListId type: BIGINT - column: name: v type: LONGTEXT - column: constraints: nullable: false name: extra type: VARCHAR(25) tableName: descriptorlist_extra - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: address type: VARCHAR(500) - column: name: description type: LONGTEXT - column: name: email type: VARCHAR(100) - column: constraints: nullable: false name: name type: VARCHAR(200) - column: name: phone type: VARCHAR(100) - column: constraints: nullable: false name: shortName type: VARCHAR(20) tableName: partner - createTable: columns: - column: constraints: nullable: false name: partnerId type: BIGINT - column: constraints: nullable: false name: code type: VARCHAR(3) tableName: partner_country - createTable: columns: - column: constraints: nullable: false name: partnerId type: BIGINT - column: name: urls type: VARCHAR(255) tableName: partner_url - createTable: columns: - column: constraints: nullable: false name: partnerId type: BIGINT - column: name: wiews type: VARCHAR(255) tableName: partner_wiews - createTable: columns: - column: constraints: nullable: false name: id type: BIGINT - column: constraints: nullable: false name: code type: VARCHAR(50) - column: constraints: nullable: false name: json type: LONGTEXT tableName: short_filter - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: code type: VARCHAR(50) - column: name: description type: LONGTEXT - column: constraints: nullable: false name: title type: VARCHAR(255) tableName: term - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: description type: LONGTEXT - column: name: published type: BIT(1) - column: name: publisher type: VARCHAR(200) - column: constraints: nullable: false defaultValueNumeric: 0 name: termCount type: INT - column: name: termUrlPrefix type: VARCHAR(255) - column: constraints: nullable: false name: title type: VARCHAR(255) - column: name: url type: VARCHAR(255) - column: name: versionTag type: VARCHAR(255) - column: constraints: nullable: false name: partnerId type: BIGINT tableName: vocabulary - createTable: columns: - column: constraints: nullable: false name: vocabularyId type: BIGINT - column: constraints: nullable: false name: termId type: BIGINT - column: constraints: nullable: false name: idx type: INT tableName: vocabulary_term - changeSet: id: 1531942280911-22 author: mobreza (generated) comment: Catalog constraints changes: - addPrimaryKey: columnNames: datasetId, acceNumb, genus, instCode constraintName: PRIMARY tableName: dataset_accessions - addPrimaryKey: columnNames: datasetId, crop constraintName: PRIMARY tableName: dataset_crops - addPrimaryKey: columnNames: datasetId, position constraintName: PRIMARY tableName: dataset_descriptor - addPrimaryKey: columnNames: datasetId, position constraintName: PRIMARY tableName: dataset_repositoryfile - addPrimaryKey: columnNames: descriptorId, idx constraintName: PRIMARY tableName: descriptor_term - addPrimaryKey: columnNames: descriptorListId, position constraintName: PRIMARY tableName: descriptorlist_descriptor - addPrimaryKey: columnNames: descriptorListId, extra constraintName: PRIMARY tableName: descriptorlist_extra - addPrimaryKey: columnNames: partnerId, code constraintName: PRIMARY tableName: partner_country - addPrimaryKey: columnNames: id constraintName: PRIMARY tableName: short_filter - addPrimaryKey: columnNames: vocabularyId, idx constraintName: PRIMARY tableName: vocabulary_term - addUniqueConstraint: columnNames: uuid constraintName: UK_1w2nmu0pi16cmaxp0aogot5ks tableName: dataset_creator - addUniqueConstraint: columnNames: uuid constraintName: UK_2n8cfl3a70has6yiorok5fhrm tableName: dataset_version - addUniqueConstraint: columnNames: descriptorId, termId constraintName: UK_56uyj2k064ypqnw54sm0x71wp tableName: descriptor_term - addUniqueConstraint: columnNames: code constraintName: UK_5iw96l9o44pn8hqmibxvea2sb tableName: short_filter - addUniqueConstraint: columnNames: repositoryfileId constraintName: UK_784vqyhclprqdm8kdk8joxt3i tableName: dataset_repositoryfile - addUniqueConstraint: columnNames: uuid constraintName: UK_95jpd7a0tb2bhuwsv5dy046mm tableName: dataset - addUniqueConstraint: columnNames: termId constraintName: UK_cei74610lyxn2i2l09ddowi0t tableName: vocabulary_term - addUniqueConstraint: columnNames: termId constraintName: UK_cvxk7is87jyulu54r4g9skacj tableName: descriptor_term - addUniqueConstraint: columnNames: uuid constraintName: UK_ec0pkl59b3n85rl52c2hi8faw tableName: descriptor - addUniqueConstraint: columnNames: uuid constraintName: UK_f4fgce8ol2dj3ryuwyksc1uyp tableName: partner - addUniqueConstraint: columnNames: shortName constraintName: UK_igns1i08mqs2go1erg46iu1ue tableName: partner - addUniqueConstraint: columnNames: uuid constraintName: UK_m17b26remm20m1ss6mg9qaviw tableName: dataset_location - addUniqueConstraint: columnNames: vocabularyId, termId constraintName: UK_pdr1eibltojao1f42d8t4in1t tableName: vocabulary_term - addUniqueConstraint: columnNames: uuid constraintName: UK_qcreerw48sehmhc14fri13rru tableName: descriptorlist - addUniqueConstraint: columnNames: uuid constraintName: UK_rdxulvplyh308ah2si3dt91x4 tableName: vocabulary - createIndex: columns: - column: name: partnerId indexName: FK_2irofo8utsk9u1x3kq0a7f0w2 tableName: partner_url - createIndex: columns: - column: name: vocabularyId indexName: FK_5yevwjhmev0nlh26y11uggrki tableName: descriptor - createIndex: columns: - column: name: partnerId indexName: FK_6eaa9g24pwchq4p31qyitpqd5 tableName: partner_wiews - createIndex: columns: - column: name: partnerId indexName: FK_90we99sg4819gi5jdn837b11c tableName: descriptor - createIndex: columns: - column: name: currentVersion_id indexName: FK_a0ia1t434yw8y70j225d3f1w6 tableName: dataset_version - createIndex: columns: - column: name: datasetId indexName: FK_alqw34hdqrvmsyay2n4v6ir9p tableName: dataset_location - createIndex: columns: - column: name: datasetId indexName: FK_eewnes8lrlh8mpi3t4hqgdf6i tableName: dataset_creator - createIndex: columns: - column: name: versionsId indexName: FK_fcnsfcwah38ausg8w02vjhowb tableName: dataset - createIndex: columns: - column: name: descriptorId indexName: FK_l4566lnkqfin0qy2ue5nh9fjm tableName: dataset_descriptor - createIndex: columns: - column: name: partnerId indexName: FK_leqc9ao32kq5okd2t2sgnx0hd tableName: descriptorlist - createIndex: columns: - column: name: partnerId indexName: FK_mvfh5b1mtxu06tlaem6uvai71 tableName: vocabulary - createIndex: columns: - column: name: descriptorId indexName: FK_ofuspiexn9y8yvkt24qaapqb6 tableName: descriptorlist_descriptor - createIndex: columns: - column: name: partnerId indexName: FK_qpx0htu9noqcpmip0tbgc65h9 tableName: dataset - createIndex: columns: - column: name: datasetId - column: name: genus indexName: UK_eb9fueogpi0ieykh0u9iu2jnm tableName: dataset_accessions - createIndex: columns: - column: name: datasetId - column: name: instCode - column: name: acceNumb indexName: UK_oypeq87pc81ahdgamtjwcakp5 tableName: dataset_accessions # Catalog foreign keys - changeSet: id: 1531942280911-64 author: mobreza (generated) comment: Catalog foreign keys changes: - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: partner_url constraintName: FK_2irofo8utsk9u1x3kq0a7f0w2 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: descriptorListId baseTableName: descriptorlist_extra constraintName: FK_5d8s1pm4o1hd3o2c06c9wkyrw deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: descriptorlist - addForeignKeyConstraint: baseColumnNames: descriptorListId baseTableName: descriptorlist_descriptor constraintName: FK_5qael9pjqjuquixphl275hhgl deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: descriptorlist - addForeignKeyConstraint: baseColumnNames: vocabularyId baseTableName: descriptor constraintName: FK_5yevwjhmev0nlh26y11uggrki deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: vocabulary - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: partner_wiews constraintName: FK_6eaa9g24pwchq4p31qyitpqd5 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: descriptorId baseTableName: descriptor_term constraintName: FK_7okd5em82og7itc5o0ixrhy5p deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: descriptor - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: descriptor constraintName: FK_90we99sg4819gi5jdn837b11c deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: partner_country constraintName: FK_9oxiot0h5wchgnxiqopqyyn7g deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: currentVersion_id baseTableName: dataset_version constraintName: FK_a0ia1t434yw8y70j225d3f1w6 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_crops constraintName: FK_aimr6eamsjcv1v4xa7myp93aq deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_location constraintName: FK_alqw34hdqrvmsyay2n4v6ir9p deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: termId baseTableName: vocabulary_term constraintName: FK_cei74610lyxn2i2l09ddowi0t deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: term - addForeignKeyConstraint: baseColumnNames: termId baseTableName: descriptor_term constraintName: FK_cvxk7is87jyulu54r4g9skacj deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: term - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_creator constraintName: FK_eewnes8lrlh8mpi3t4hqgdf6i deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: versionsId baseTableName: dataset constraintName: FK_fcnsfcwah38ausg8w02vjhowb deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset_version - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_repositoryfile constraintName: FK_ixe9wrqwvd9591de5a8mqn5kd deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: descriptorId baseTableName: dataset_descriptor constraintName: FK_l4566lnkqfin0qy2ue5nh9fjm deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: descriptor - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: descriptorlist constraintName: FK_leqc9ao32kq5okd2t2sgnx0hd deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: vocabulary constraintName: FK_mvfh5b1mtxu06tlaem6uvai71 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: descriptorId baseTableName: descriptorlist_descriptor constraintName: FK_ofuspiexn9y8yvkt24qaapqb6 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: descriptor - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: dataset constraintName: FK_qpx0htu9noqcpmip0tbgc65h9 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - addForeignKeyConstraint: baseColumnNames: vocabularyId baseTableName: vocabulary_term constraintName: FK_qtmhrhsfqajdbslvrodwax3n7 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: vocabulary - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_accessions constraintName: FK_r2q07jo0s90fd4alq0t4grn6e deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - addForeignKeyConstraint: baseColumnNames: datasetId baseTableName: dataset_descriptor constraintName: FK_soaqnlfrlrmm40bh6tqqomynt deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: dataset - changeSet: id: 1531942280911-65 author: mobreza comment: Set {} filter to have '' as code changes: - sql: sql: >- INSERT INTO short_filter (id, code, json) values (0, '', '{}'); - sql: sql: >- CREATE INDEX UK_1my8xep8hi5fv42o3ivu0t41o ON short_filter(json(250)) - changeSet: id: 1533223466241-1 author: vpavlov comment: Added Dataset location timing changes: - addColumn: tableName: dataset columns: - column: name: startDate type: varchar(8) - column: name: endDate type: varchar(8) - addColumn: tableName: dataset_location columns: - column: name: startDate type: varchar(8) - column: name: endDate type: varchar(8) - changeSet: id: 1533138107794-1 author: mborodenko comment: Add column `accessionId` to dataset_accessions table changes: - addColumn: columns: - column: constraints: nullable: true name: accessionId type: BIGINT(20) tableName: dataset_accessions - changeSet: id: 1533138107794-2 author: mborodenko comment: Add constraints changes: - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: dataset_accessions constraintName: FK_ekss0g19xg9h2tsp1zl8g9h2t deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - createIndex: columns: - column: name: accessionId indexName: FK_ekss0g19xg9h2tsp1zl8g9h2t tableName: dataset_accessions - changeSet: id: 1532610825317-1 author: mborodenko comment: Add column `state` to descriptor table changes: - addColumn: columns: - column: defaultValueNumeric: 0 constraints: nullable: false name: state type: INT tableName: descriptor - changeSet: id: 1532610825317-2 author: mborodenko comment: Add column `state` to descriptorlist table changes: - addColumn: columns: - column: defaultValueNumeric: 0 constraints: nullable: false name: state type: INT tableName: descriptorlist - changeSet: id: 1532610825317-3 author: mborodenko comment: Add column `state` to dataset table changes: - addColumn: columns: - column: defaultValueNumeric: 0 constraints: nullable: false name: state type: INT tableName: dataset - changeSet: id: 1532610825317-4 author: mborodenko comment: Migrate data to new column changes: - sql: sql: >- UPDATE descriptor SET state = 0 WHERE published = 0; UPDATE descriptor SET state = 1 WHERE published = 1; - dropColumn: tableName: descriptor columnName: published - changeSet: id: 1532610825317-5 author: mborodenko comment: Migrate data to new column changes: - sql: sql: >- UPDATE descriptorlist SET state = 0 WHERE published = 0; UPDATE descriptorlist SET state = 1 WHERE published = 1; - dropColumn: tableName: descriptorlist columnName: published - changeSet: id: 1532610825317-6 author: mborodenko comment: Migrate data to new column changes: - sql: sql: >- UPDATE dataset SET state = 0 WHERE published = 0; UPDATE dataset SET state = 1 WHERE published = 1; - dropColumn: tableName: dataset columnName: published - changeSet: id: 1533210002102-1 author: aprendetskiy changes: - addColumn: tableName: accession columns: - column: name: tileIndex type: BIGINT - sql: sql: >- update accession a right join acce ac on ac.id = a.id right join accession_geo geo on geo.id = ac.geoId set a.tileIndex = geo.tileIndex; - createIndex: columns: - column: name: tileIndex indexName: UK_21geycvlx21bswf9t89s2r99n tableName: accession - addColumn: tableName: accession_historic columns: - column: name: tileIndex type: BIGINT - sql: sql: >- update accession_historic ah right join acce ac on ac.id = ah.id right join accession_geo geo on geo.id = ac.geoId set ah.tileIndex = geo.tileIndex; - changeSet: id: 1535727409888-1 author: mborodenko comment: Add column `state` to subset table changes: - addColumn: columns: - column: defaultValueNumeric: 0 constraints: nullable: false name: state type: INT tableName: subset - changeSet: id: 1535727409888-2 author: mborodenko comment: Migrate data to new column changes: - sql: sql: >- UPDATE subset SET state = published; - dropColumn: tableName: subset columnName: published - changeSet: id: 1533127815588-66 author: aprendetskiy comment: Add new columns to faoinstitute table changes: - addColumn: tableName: faoinstitute columns: - column: name: pdciMin type: double - column: name: pdciMax type: double - column: name: pdciAvg type: double - column: name: pdciHistogram type: VARCHAR(255) - changeSet: id: 1536756475431-67 author: vpavlov comment: Add subset creators changes: - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - 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: uuid type: BINARY(16) - column: name: email type: VARCHAR(255) - column: name: fax type: VARCHAR(255) - column: constraints: nullable: false name: fullName type: VARCHAR(200) - column: name: instituteAddress type: VARCHAR(255) - column: name: institutionalAffiliation type: VARCHAR(255) - column: name: phoneNumber type: VARCHAR(255) - column: name: role type: VARCHAR(255) - column: constraints: nullable: false name: subsetId type: BIGINT tableName: subset_creator - addUniqueConstraint: comment: Add subset creators index columnNames: uuid constraintName: UK_ae1e6d44dd2367f354823336e tableName: subset_creator - addForeignKeyConstraint: comment: Add subset creators foreign key baseColumnNames: subsetId baseTableName: subset_creator constraintName: FK_597934690b24e049cd56efa10 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: subset - changeSet: id: 1536945927974-1 author: mobreza (generated) comment: Fix crop shortName (code) to 50 changes: - modifyDataType: columnName: crop newDataType: varchar(50) tableName: dataset_crops - modifyDataType: columnName: crop newDataType: varchar(50) tableName: descriptor - modifyDataType: columnName: crop newDataType: varchar(50) tableName: descriptorlist - changeSet: id: 1536945927974-4 author: vpavlov comment: Add crops to Subset changes: - addColumn: columns: - column: name: date type: VARCHAR(8) tableName: subset - addColumn: columns: - column: name: source type: VARCHAR(200) tableName: subset - createTable: columns: - column: constraints: primaryKey: true name: subsetId type: BIGINT - column: constraints: primaryKey: true name: crop type: VARCHAR(50) tableName: subset_crops - addForeignKeyConstraint: baseColumnNames: subsetId baseTableName: subset_crops constraintName: FK_hny3je51ro3jv72dubq9lt4o2 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: subset validate: true - changeSet: id: 1537463144763-1 author: mobreza (generated) comment: file-repository:1.1-SNAPSHOT changes: - createTable: columns: - column: autoIncrement: true constraints: primaryKey: true name: id type: BIGINT - column: constraints: nullable: false name: active type: BIT(1) - column: constraints: nullable: false name: version type: INT - column: name: createdBy type: BIGINT - column: name: createdDate type: datetime - column: name: lastModifiedBy type: BIGINT - column: name: lastModifiedDate type: datetime - column: constraints: nullable: false unique: true name: uuid type: BINARY(16) - column: name: description type: LONGTEXT - column: constraints: nullable: false name: name type: VARCHAR(255) - column: constraints: nullable: false unique: true name: path type: VARCHAR(255) - column: name: title type: VARCHAR(255) - column: name: parent_id type: BIGINT tableName: repositoryfolder - createIndex: columns: - column: name: parent_id indexName: FK_fn20kyy1g7xwt937r9syc8ga9 tableName: repositoryfolder - addForeignKeyConstraint: baseColumnNames: parent_id baseTableName: repositoryfolder constraintName: FK_fn20kyy1g7xwt937r9syc8ga9 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: repositoryfolder validate: true - changeSet: id: 1537463144763-6 author: mobreza (generated) comment: file-repository:1.1-SNAPSHOT changes: - addColumn: columns: - column: name: folder_id type: BIGINT(19) tableName: repository_file - addUniqueConstraint: columnNames: folder_id, originalFilename constraintName: UK_1fvb3vmhblefrc0wsnukdbc56 tableName: repository_file - addForeignKeyConstraint: baseColumnNames: folder_id baseTableName: repository_file constraintName: FK_eb66ofna7jfc78e35p51qvxdk deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: repositoryfolder validate: true - changeSet: id: 1537463144763-7 author: mobreza (generated) comment: file-repository:1.1-SNAPSHOT changes: - addColumn: columns: - column: name: folder_id type: BIGINT(19) tableName: repository_image - addUniqueConstraint: columnNames: folder_id, originalFilename constraintName: UK_dynu7yuj2xn19qlp387inexuh tableName: repository_image - addForeignKeyConstraint: baseColumnNames: folder_id baseTableName: repository_image constraintName: FK_2ma70p16s23l9b4gvqp51qw7f deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: repositoryfolder validate: true - changeSet: id: 1537463144763-5 author: mobreza (generated) comment: file-repository:1.1-SNAPSHOT changes: - addColumn: columns: - column: name: folder_id type: BIGINT tableName: repository_document - addUniqueConstraint: columnNames: folder_id, originalFilename constraintName: UK_1bckkd8l1n6n2hsl1895utixh tableName: repository_document - addForeignKeyConstraint: baseColumnNames: folder_id baseTableName: repository_document constraintName: FK_trk7cw1r3dsxgu8jlia1ecrgj deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: repositoryfolder validate: true - changeSet: id: 1537463144763-no-null author: mobreza comment: path is no longer required changes: - dropIndex: indexName: UK_27590ja5w3amn2yj2dryogm6n tableName: repository_file - dropNotNullConstraint: columnName: path columnDataType: varchar(255) tableName: repository_file - dropIndex: indexName: UK_iubmgqa5xckodr6fbg6vnne0d tableName: repository_document - dropNotNullConstraint: columnName: path columnDataType: varchar(255) tableName: repository_document - dropIndex: indexName: UK_88rxxsqrm09geyh5go9ert6qe tableName: repository_image - dropNotNullConstraint: columnName: path columnDataType: varchar(255) tableName: repository_image - changeSet: id: 1537976738000-1 author: mobreza comment: Fix GeoRegion#isoCode changes: - modifyDataType: columnName: isoCode newDataType: varchar(3) tableName: georegion - addNotNullConstraint: columnDataType: varchar(3) columnName: isoCode tableName: georegion - changeSet: id: 1537976738000-2 author: mobreza comment: Fix GeoRegion#name changes: - modifyDataType: columnName: name newDataType: varchar(250) tableName: georegion - addNotNullConstraint: columnDataType: varchar(250) columnName: name tableName: georegion - changeSet: id: 1537984143000-1 author: mobreza comment: Remove blank duplSite from the database changes: - sql: comment: Delete empty accession_duplsite entries sql: delete from accession_duplsite where duplSite = ''; - changeSet: id: 1538209535000-1 author: mobreza comment: file-repository:1.1-SNAPSHOT changes: - addColumn: columns: - column: name: folder_id type: BIGINT tableName: repository_gallery - addUniqueConstraint: columnNames: folder_id constraintName: UK_1bckkd8l1n6n2hsl1895utix1 tableName: repository_gallery - addForeignKeyConstraint: baseColumnNames: folder_id baseTableName: repository_gallery constraintName: FK_trk7cw1r3dsxgu8jlia1ecrgk deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: repositoryfolder validate: true - dropIndex: indexName: UK_3sfjllps5cq9307ksvnhctcc9 tableName: repository_gallery - dropNotNullConstraint: columnName: path columnDataType: varchar(255) tableName: repository_gallery - changeSet: id: 1538647037444-1 author: vpavlov comment: added country code to dataset locatiom changes: - addColumn: columns: - column: name: countryCode type: varchar(3) tableName: dataset_location - changeSet: id: 1538647037444-2 author: mobreza comment: added description to dataset locatiom changes: - addColumn: columns: - column: name: description type: LONGTEXT tableName: dataset_location - changeSet: id: 1538732843596-1 author: vpavlov comment: added subset_accessions table changes: - createTable: columns: - column: constraints: nullable: false name: subsetId type: BIGINT - column: constraints: nullable: false name: acceNumb type: VARCHAR(50) - column: name: doi type: VARCHAR(100) - column: constraints: nullable: false name: genus type: VARCHAR(100) - column: constraints: nullable: false name: instCode type: VARCHAR(10) - column: name: species type: VARCHAR(100) - column: name: accessionId type: BIGINT tableName: subset_accessions - changeSet: id: 1538732843596-2 author: vpavlov comment: added primary key and indexes to subset_accessions changes: - addPrimaryKey: columnNames: subsetId, acceNumb, genus, instCode constraintName: PRIMARY tableName: subset_accessions - createIndex: columns: - column: name: subsetId - column: name: genus indexName: UK_236937d8c1da541bfae76f2ae tableName: subset_accessions - createIndex: columns: - column: name: subsetId - column: name: instCode - column: name: acceNumb indexName: UK_d273e974802dc26af12a4ce13 tableName: subset_accessions - changeSet: id: 1538732843596-3 author: vpavlov comment: Add foreign keys to subset_accessions changes: - addForeignKeyConstraint: baseColumnNames: accessionId baseTableName: subset_accessions constraintName: FK_734a34092b6ced6a54dc33d71 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: acce - addForeignKeyConstraint: baseColumnNames: subsetId baseTableName: subset_accessions constraintName: FK_da741f60ee904f97d65f42e17 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: subset - createIndex: columns: - column: name: accessionId indexName: FK_da741f60ee904f97d65f42e17 tableName: subset_accessions - changeSet: id: 1538732843596-4 author: vpavlov comment: Migrated data from subset_accession to subset_accessions changes: - sql: sql: >- insert into subset_accessions (subsetId, acceNumb, doi, genus, instCode, species, accessionId) select s_a.subset_id, a.acceNumb, a.doi, t2.genus, a.instCode, t2.species, s_a.acce_id from subset_accession s_a join accession a on s_a.acce_id = a.id join taxonomy2 t2 on a.taxonomyId2 = t2.id; - changeSet: id: 1538732843596-5 author: vpavlov comment: drop subset_accession changes: - dropTable: tableName: subset_accession - changeSet: id: 1539510451000-1 author: mobreza comment: Add index on accession_alias#name changes: - sql: sql: >- CREATE INDEX UK_cus8xep8hi5fv42o3ivu0name ON accession_alias(name(250)) - changeSet: id: 1539710994000-1 author: mobreza comment: Index on ID3 changes: - createIndex: columns: - column: name: instCode - column: name: genus - column: name: acceNumb indexName: IX_id3 tableName: accession - changeSet: id: 1539794655476-1 author: vpavlov comment: added annex1 to crop changes: - addColumn: columns: - column: name: annex1 type: boolean defaultValue: false tableName: crop - changeSet: id: 1539975792-1 author: mobreza comment: delete unreferenced terms changes: - sql: - sql: delete t from term t left outer join vocabulary_term vt on vt.termId=t.id left outer join descriptor_term dt on dt.termId=t.id where dt.descriptorId is null and vt.vocabularyId is null; # KPI upgrade - changeSet: id: 1540406374693-2 author: mobreza (generated) comment: upgraded KPI for average with stddev changes: - addColumn: columns: - column: name: stddev1 type: DOUBLE(22) tableName: kpiobservation - addColumn: columns: - column: constraints: nullable: false name: property type: VARCHAR(30 BYTE) tableName: kpiexecution - addColumn: columns: - column: constraints: nullable: false name: type type: INT(10) tableName: kpiexecution - addUniqueConstraint: columnNames: name constraintName: UK_cphsjvpj9r9fldrj78p4oct0n tableName: kpidimension - addUniqueConstraint: columnNames: title constraintName: UK_dtcixjrwdh7maj1rooh1300m6 tableName: kpidimension - dropPrimaryKey: constraintName: PRIMARY tableName: kpiobservationdimension - modifyDataType: columnName: field newDataType: varchar(100) tableName: kpidimension - changeSet: id: 1540486364926-1 author: vpavlov changes: - dropColumn: tableName: subset columnName: rights - changeSet: id: 1540821453000-5 author: mobreza changes: - addColumn: tableName: repositoryfolder columns: - column: name: parentOid_id type: bigint - addForeignKeyConstraint: constraintName: FK_1l4ttk3kctjy2nedwwlh8xx8p baseTableName: repositoryfolder baseColumnNames: parentOid_id referencedTableName: acl_object_identity referencedColumnNames: id - changeSet: id: 1539274052727-1 author: vpavlov comment: partnerId to faoinstitute changes: - addColumn: columns: - column: name: partnerId type: bigint(20) tableName: faoinstitute - changeSet: id: 1539274052727-2 author: vpavlov comment: added foreign key faoinstitute(partnerId)-partner(id) changes: - addForeignKeyConstraint: baseColumnNames: partnerId baseTableName: faoinstitute constraintName: FK_103537ceb0f6f88fee69bbf23 deferrable: false initiallyDeferred: false onDelete: NO ACTION onUpdate: NO ACTION referencedColumnNames: id referencedTableName: partner - changeSet: id: 1539274052727-3 author: vpavlov comment: Migrate data from partner_wiews to faoinstitute (partnerId) changes: - sql: sql: >- update faoinstitute f inner join partner_wiews pw on f.code = pw.wiews set f.partnerId = pw.partnerId; - changeSet: id: 1539274052727-4 author: vpavlov comment: drop partner_wiews changes: - dropTable: tableName: partner_wiews # ENABLE AFTER SOME TIME # - changeSet: # id: 1537463144763-folder # author: mobreza # comment: Require folder_id # changes: # - addNotNullConstraint: # columnDataType: BIGINT # columnName: folder_id # tableName: repository_document # - addNotNullConstraint: # columnDataType: BIGINT # columnName: folder_id # tableName: repository_image # - addNotNullConstraint: # columnDataType: BIGINT # columnName: folder_id # tableName: repository)file