A new topic, 'bug: createIndex 'associatedWith' attribute not handled
consistently in Oracle', has been made on a board you are watching.
You can see it at
http://liquibase.org/forum/index.php?topic=634.new#new
The text of the topic is shown below:
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:
Code:
<changeSet author="Matt Drees" id="create-Administrator">
<createTable tableName="ADMINISTRATOR"
tablespace="ETIME_DATA">
<column name="ID" type="NUMBER(19,0)">
<constraints nullable="false"
primaryKey="true"
primaryKeyName="ADMINISTRATOR_PK"
primaryKeyTablespace="ETIME_IDX" />
</column>
<column name="EMPLOYEEID" type="VARCHAR2(12 CHAR)">
<constraints nullable="false" />
</column>
<column name="ROLE" type="VARCHAR2(40 CHAR)" />
<column name="STATUS" type="VARCHAR2(3 CHAR)" />
<column name="START_DATE" type="DATE" />
<column name="END_DATE" type="DATE" />
</createTable>
<addUniqueConstraint columnNames="EMPLOYEEID"
constraintName="ADMINISTRATOR_EMPLOYEEID_UC"
tableName="ADMINISTRATOR"
tablespace="ETIME_IDX" />
<createIndex associatedWith="primaryKey"
indexName="IDX_ADMIN_U1"
tableName="ADMINISTRATOR"
tablespace="ETIME_IDX"
unique="true">
<column name="ID" />
</createIndex>
<createIndex associatedWith="uniqueConstraint"
indexName="IDX_ADMINISTRATOR_EMPLOYEEID"
tableName="ADMINISTRATOR"
tablespace="ETIME_IDX"
unique="true">
<column name="EMPLOYEEID" />
</createIndex>
<createSequence
sequenceName="ADMIN_SEQUENCE" />
</changeSet>
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:
Code:
-- *********************************************************************
-- Update 1 Change Sets Database Script
-- *********************************************************************
-- Change Log: initial.changelog.xml
-- Ran at: 8/9/10 3:27 PM
-- Against: etimesh...@jdbc:oracle:thin:@//<removed>
-- 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:
Code:
-- *********************************************************************
-- Rollback 1 Change(s) Script
-- *********************************************************************
-- Change Log: initial.changelog.xml
-- Ran at: 8/9/10 3:32 PM
-- Against: etimesh...@jdbc:oracle:thin:@//<removed>
-- 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. :-)
Let me know if I need to clarify.
Thanks,
-Matt
Unsubscribe to new topics from this board by clicking here:
http://liquibase.org/forum/index.php?action=notifyboard;board=1.0
Regards,
The Liquibase Community Forum Team.------------------------------------------------------------------------------
This SF.net email is sponsored by
Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev
_______________________________________________
Liquibase-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/liquibase-user
