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 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>

Answer 2

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>