generateChangeLog doesn't generate createIndex changesLog-s on NUMBER foreign keys

General Discussion
1

Hi,


The generateChangeLog doesn’t generate the createIndex changeLog-s on NUMBER foreign keys (Long), just on unique and VARCHAR (String) keys.


If I have the next tables with two indices on Oracle:

CREATE TABLE STORED_FILE (

      ID NUMBER(19,0) NOT NULL,

      FILE_NAME VARCHAR(1000) NOT NULL,

      CREATION_USER VARCHAR(500) NOT NULL,

      CREATION_DATE TIMESTAMP(3) NOT NULL,

      CONSTRAINT STORED_FILE_PK PRIMARY KEY (ID) USING INDEX TABLESPACE s_indx) TABLESPACE s_data;


CREATE INDEX s_data.STOREDFILE_USER_IDX ON s_data.STORED_FILE(CREATION_USER) TABLESPACE s_indx;


CREATE TABLE DOCUMENT_CONTENT (

      ID NUMBER(19,0) NOT NULL,

      CONTENTS VARCHAR(4000),

      STORED_FILE_ID NUMBER(19,0) NOT NULL,

      CONSTRAINT DOCUMENT_CONTENT_PK PRIMARY KEY (ID) USING INDEX TABLESPACE s_indx) TABLESPACE s_data;


ALTER TABLE s_data.DOCUMENT_CONTENT ADD CONSTRAINT DOCCON_STOFILE_FK FOREIGN KEY (STORED_FILE_ID) REFERENCES s_data.STORED_FILE (ID);


CREATE INDEX s_data.DOCCON_STFL_IDX ON sip_data.DOCUMENT_CONTENT(STORED_FILE_ID) TABLESPACE s_indx;


Only the changeLog for STOREDFILE_USER_IDX will be generated, but the DOCCON_STFL_IDX doesn’t. 

 changeSet author=“f (generated)” id=“1301402797638-3”

  createIndex indexName=“STOREDFILE_USER_IDX” tableName=“STORED_FILE” tablespace=“S_INDX” unique=“false” column name=“CREATION_USER”

 /createIndex

/changeSet


The DOCCON_STFL_IDX is not a mandatory database constraint, the database structure is whole without it, but it’d boost the performance.


Can someone help me why the generateChangeLog doesn’t generate these performance boosting indices?

Our database are full of such indices, and creating them manually would be really time consuming…


Thanks,

   Ferenc


2

The diff/generateChangeLog support does not support all database object types since we need to support so many different databases. Improving what is supported is the focus of 2.1 and I created an issue for this particular problem:


http://liquibase.jira.com/browse/CORE-918


For now, it may be best to use a different tool to export your schema (or at least missing indexes) and move them into your changelog as blocks.


Nathan