Using liquibase how can I add a ngram index to an existing table?

A mysql database has existing tables maintained with liquibase. There is a need now to search using a ngram enabled fuzzy search. How can I specify in my changeset to use the ngram parser or fuzzy search? I'm not an expert at liquibase, but I read the docs and the examples were basic, and ngram did not appear in the docs at all. Some discussions on stack overflow seemed to imply if I could do something in my database I could do it using liquibase. Thanks!

    <changeSet id="202210110300-add-index-on-product-guid-for-document" author="chn7d1w" dbms="mysql">
        <createIndex tableName="REFERENCE_CHEMICAL" indexName="reference_chemical_fulltext">
            <column name="NAME" />
            <column name="CHEMICAL_TYPE_NAME" />
        </createIndex>
        <createIndex tableName="REFERENCE_VARIETY" indexName="reference_chemical_fulltext">
            <column name="NAME" />
            <column name="CROP_TYPE" />
        </createIndex>
        <createIndex tableName="REFERENCE_FERTILIZER" indexName="reference_chemical_fulltext">
            <column name="NAME" />
            <column name="FERTILIZER_TYPE_NAME" />
        </createIndex>
    </changeSet>

ALTER TABLE REFERENCE_CHEMICAL ADD FULLTEXT INDEX `reference_chemical_fulltext_index (NAME, CHEMICAL_TYPE_NAME)` WITH PARSER ngram;
ALTER TABLE REFERENCE_VARIETY ADD FULLTEXT INDEX `reference_variety_fulltext_index (NAME, CROP_TYPE)` WITH PARSER ngram;
ALTER TABLE REFERENCE_FERTILIZER ADD FULLTEXT INDEX `reference_fertilize_fulltext_index (NAME, FERTILIZER_TYPE_NAME)` WITH PARSER ngram;

Solution

The available "change commands" like <createIndex> are there to provide standard and cross-database functionality, and don't try to be a replacement for SQL. So there will always be things that your database supports that require just specifying the SQL yourself.

For example, you can add an additional changeset of:

    <changeSet id="2" author="example" dbms="mysql">
        <sql>ALTER TABLE REFERENCE_CHEMICAL ADD FULLTEXT INDEX `reference_chemical_fulltext_index (NAME, CHEMICAL_TYPE_NAME)` WITH PARSER ngram;</sql>
        <sql>ALTER TABLE REFERENCE_VARIETY ADD FULLTEXT INDEX `reference_variety_fulltext_index (NAME, CROP_TYPE)` WITH PARSER ngram;</sql>
        <sql>ALTER TABLE REFERENCE_FERTILIZER ADD FULLTEXT INDEX `reference_fertilize_fulltext_index (NAME, FERTILIZER_TYPE_NAME)` WITH PARSER ngram;</sql>
    </changeSet>

that adds the fulltext indexes. That's usually the easiest way, although Liquibase does have <modifySql> that lets you tweak the sql from a <createIndex> or whatever change function to add or replace what is needed beyond what the createIndex function supports.

NOTE: You should always have separate changeSets for each auto-committing change or you could get stuck in a weird state if the 2nd or 3rd change fails. For example, if your first createIndex succeeds and the 2nd fails for some reason, the database doesn't rollback the 1st index and the next time you run liquibase it will try to re-create the index and it will fail. So it would be safest to have a separate changeSet around each createIndex and around each of the <sql> calls from above.