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.

Stringed

Take a look at the create index refactoring. – Moralize

I know how to create an index, I need a way to specify the order by clause – Stringed

Have you tried appending the ordering to the column name, as in <column name="a_column_name DESC"/>? This might cause portability issues, of course. – Moralize

That does not work, it generates: CREATE INDEX idx_name ON my_table (**"m_column DESC"**) – Stringed

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>

Rearrange

was trying this out today, just make sure you update your schema/xsd in the xml file to be 3.4 or you will get an error. – Scrounge

@macalase Updated the answer accordingly – Rearrange

Weird, with XSD 3.5, the descending attribute is gone again – Jannjanna

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>

Unyoke

Well, of course this is working but the purpose of liquibase is to avoid this kind of thing and let the software generates it. – Stringed

While it's possible to leverage Liquibase's createIndex, I would not hesitate to use sql directly. If you insist on not doing so, see my answer for an alternative. – Ancell

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.

Ancell

Answer 4

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>

Melanoid

This may not work if column names are quoted, e.g. as [column_name] on SQL Server. – Ancell