Creating indexes with descending key columns with Liquibase

Question

I am wondering if there is a generic way to create an "ordered index" with liquibase. Something that will produce these kind of SQL statement:

CREATE INDEX idx_name ON my_table (m_column DESC)

I need that for oracle, postgresql, mysql, and sql server.

If not, I'll have to do it manually with the SQL tag for each RDBM.

Answer 1

I've just looked through liquibase source code and haven't found any handling of column ordering for indexes. So I would recommend you to use sql and modifySql blocks (I believe most DBMSes have the same syntax for create index, so probably you don't need modifySql):

<changeSet id="1">
    <sql>
<![CDATA[
CREATE INDEX idx_name ON my_table (m_column DESC)
]]>
    </sql>
    <!-- just for example -->
    <modifySql dbms="mssql">
        <replace replace="CREATE INDEX" with="CREATE NONCLUSTERED INDEX"/>
    </modifySql>
</changeSet>

Answer 2

There is still unresolved feature request for liquibase index ordering at https://liquibase.jira.com/browse/CORE-419.

One possible workaround (since 1.9):

<createIndex tableName="my_table" indexName="my_index">
    <column name="COL1NAME"/>
    <column name="COL2NAME"/>
</createIndex>
<modifySql>
    <replace replace="COL1NAME" with="COL1NAME ASC"/>
    <replace replace="COL2NAME" with="COL2NAME DESC"/>
</modifySql>

Note that there were even shorter workaround avaliable until validation hardened in liquibase 3.2:

<createIndex tableName="my_table" indexName="my_index">
    <column name="registration_time DESC"/>
    <column name="id ASC"/>
</createIndex>

Answer 3

To leverage Liquibase createIndex and support different RDBMSes, you will need to use modifySql (until https://liquibase.jira.com/browse/CORE-419 is fixed, as pointed out by @Vadzim.

For Oracle and PostgreSQL the syntax will be simple (unless PostgreSQL is used in "always quote column names" mode):

<modifySql>
    <replace replace="COL1NAME" with="COL1NAME ASC"/>
    <replace replace="COL2NAME" with="COL2NAME DESC"/>
</modifySql>

Howver, this won't work on SQL Server. Liquibase wraps column names in [...] on SQL Server, so you would need something like

<modifySql>
    <regExpReplace replace="\bCOL1NAME\b[^,) ]*" with="$0 ASC" />
    <regExpReplace replace="\bCOL2NAME\b[^,) ]*" with="$0 DESC" />
</modifySql>

(tested on oracle, postgresql, sql server and h2)

However, the above is as ugly as it looks. I suppose using simple <sql> yields more readable results and, honestly, it does not seem less portable.

Answer 4

I submitted a pull request that made it into Liquibase 3.4.0 that made it possible to specify descending key columns not only for indexes, but also for primary keys and unique constraints. This even works on databases with quoted column names like Microsoft SQL Server.

Example of how it works

<createIndex tableName="my_table" indexName="my_index">
    <column name="col1"/>
    <column name="col2" descending="true"/>
</createIndex>

<addPrimaryKey tableName="my_table" columnNames="col1, col2 DESC"/>

<addUniqueConstraint tableName="my_table" columnNames="col1, col2 DESC"/>

Try it out

Download a 3.4.1 or later package here.

OR

Use this Maven dependency

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.4.1</version>
</dependency>

AND

Be sure to update the referenced XSD to 3.4

<?xml version="1.0" ?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

...

</databaseChangeLog>