How to inspect the condition on a partial index?

[grantneufeld]

I am creating a partial index.

try db.create(
    index: "myIndex",
    on: "myTable",
    columns: ["someColumn", "anotherColumn"],
    condition: "WHERE someColumn IS NOT NULL"
)

When I get IndexInfo for my table, that info does not seem to provide a way to get the condition.

// code fragment from using swift-testing in to validate:
let indexes = try db.indexes(on: "myTable")
for index in indexes {
    if index.name == "myIndex" {
        #expect(index.isUnique == false) // passes
        #expect(index.columns == ["someColumn", "anotherColumn"]) // passes
        // But there is no `index.condition`, or such, to use here.
        // The only other property available on index is `origin`,
        // which, in my testing, just evaluates to `"c"`.
    }
}

So, is there a way I can find the condition of a partial index?

[groue]

Hello @grantneufeld,

SQLite does not provide this information:

sqlite> create table player(id integer primary key, name text, score int);
sqlite> create index myIndex on player(name) where score is not null;
sqlite> pragma index_info(myIndex);
| seqno | cid | name |
|-------|-----|------|
| 0     | 1   | name |
sqlite> pragma index_xinfo(myIndex);
| seqno | cid | name | desc |  coll  | key |
|-------|-----|------|------|--------|-----|
| 0     | 1   | name | 0    | BINARY | 1   |
| 1     | -1  | NULL | 0    | BINARY | 0   |

In order to test the schema, I'd instead suggest to test the content of sqlite_master:

let sql = try String.fetchOne(db, sql: "select sql from sqlite_master where name = 'myIndex'")
#expect(sql == "CREATE INDEX myIndex on player(name) where score is not null")

You can also use the companion library GRDBSnapshotTesting.

[grantneufeld]

That does the job—thanks for the clear example!