bug: createIndex 'associatedWith' attribute not handled consistently in Oracle

General Discussion
1

Hi!

I’m using liquibase 2.0-rc5.  I’m working towards migrating an existing schema to a new database, and taking the opportunity to ‘liquibase-ify’ our db scripts.
The generated changelog (which I then heavily hand-tweaked) includes a changeset that looks like this:

                                                                                                                                           

Note the two createIndex refactorings.  My understanding is liquibase now creates these, but only issues corresponding DML if the dbms does not automatically create indexes with the associated constraint/primary-key/foreign-key.  In my case, using Oracle, it doesn’t issue create-index statements:

    -- ********************************************************************* -- Update 1 Change Sets Database Script -- ********************************************************************* -- Change Log: initial.changelog.xml -- Ran at: 8/9/10 3:27 PM -- Against: ETIMESHEET@jdbc:oracle:thin:@// -- Liquibase version: 2.0-rc5 -- *********************************************************************

    – Lock Database
    – Changeset initial.changelog.xml::create-Administrator::Matt Drees::(Checksum: 2:6e693e6020d74e13e07da1fb71c0eae5)
    CREATE TABLE ETIMESHEET.ADMINISTRATOR (ID NUMBER(19,0) NOT NULL, EMPLOYEEID VARCHAR2(12 CHAR) NOT NULL, ROLE VARCHAR2(40 CHAR), STATUS VARCHAR2(3 CHAR), START_DATE DATE, END_DATE DATE, CONSTRAINT ADMINISTRATOR_PK PRIMARY KEY (ID) USING INDEX TABLESPACE ETIME_IDX) TABLESPACE ETIME_DATA;

    ALTER TABLE ETIMESHEET.ADMINISTRATOR ADD CONSTRAINT ADMINISTRATOR_EMPLOYEEID_UC UNIQUE (EMPLOYEEID) USING INDEX TABLESPACE ETIME_IDX;

    CREATE SEQUENCE ETIMESHEET.ADMIN_SEQUENCE;

    INSERT INTO ETIMESHEET.DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES (‘Matt Drees’, NULL, SYSTIMESTAMP, ‘Create Table, Add Unique Constraint, Create Index (x2), Create Sequence’, ‘EXECUTED’, ‘initial.changelog.xml’, ‘create-Administrator’, ‘2.0-rc5’, ‘2:6e693e6020d74e13e07da1fb71c0eae5’, 1);

    – Release Database Lock

However, the generated rollback script seems to forget this, and tries to drop these indexes by name:

    – *********************************************************************
    – Rollback 1 Change(s) Script
    – *********************************************************************
    – Change Log: initial.changelog.xml
    – Ran at: 8/9/10 3:32 PM
    – Against: ETIMESHEET@jdbc:oracle:thin:@//
    – Liquibase version: 2.0-rc5
    – *********************************************************************

    – Lock Database
    – Rolling Back ChangeSet: initial.changelog.xml::create-Administrator::Matt Drees::(Checksum: 2:6e693e6020d74e13e07da1fb71c0eae5)
    DROP SEQUENCE ETIMESHEET.ADMIN_SEQUENCE;

    DROP INDEX ETIMESHEET.IDX_ADMINISTRATOR_EMPLOYEEID;

    DROP INDEX ETIMESHEET.IDX_ADMIN_U1;

    ALTER TABLE ETIMESHEET.ADMINISTRATOR DROP CONSTRAINT ADMINISTRATOR_EMPLOYEEID_UC DROP INDEX;

    DROP TABLE ETIMESHEET.ADMINISTRATOR;

    DELETE FROM ETIMESHEET.DATABASECHANGELOG  WHERE ID=‘create-Administrator’ AND AUTHOR=‘Matt Drees’ AND FILENAME=‘initial.changelog.xml’;

    – Release Database Lock

This, of course, doesn’t work since the indexes that were created are given system-generated names.

I’m pretty sure this is not intended behavior.  :slight_smile:

Let me know if I need to clarify.

Thanks,
-Matt

2

No, that is not the intended behaviour.  Could you create an issue for it on http://liquibase.jira.com?

I’m going to have to push it off to 2.1, however.  The logic around the diff/changelog generation will be the main focus of that release.  The associatedWith was contributed code for 2.0 which I like the idea behind, but I have not had the chance to really go through where it all needs to be addressed.

Nathan

3

Issue created: http://liquibase.jira.com/browse/CORE-690

Bummer to hear it won’t be fixed until 2.1.

Thanks for your work on this project!
-Matt

4

Thanks for creating it.

Yes, it would be nice to get it fixed, but I really need to get 2.0 out and there is a lot to do with the diff tool to make it more robust.  It seemed a natural breaking point between in-2.0 vs. not in 2.0.  I tend to think of the diff tool as secondary functionality and so having it incomplete isn’t as big of a deal. 

Hopefully with the 2.1 release, I will be able to clean the diff support up a lot.

Nathan

5

Yep, I understand the need to get 2.0 out the door, so I won’t question your decision there.  I can live with that.

I’d like to make a quick note, though, that this issue is more with the ‘associatedWith’ handling than it is with the diff tool.  Just so things are clear.

-Matt