SchemaReader column definitions are incomplete for composite primary keys

[stefansaasen]

Given the following CREATE TABLE statement:

CREATE TABLE t (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    PRIMARY KEY (col1, col2)
);

A call to db.schema.columnDefinitions(table: "t") should return three ColumnDefinitions where the column definitions for col1 and col2 are marked as being part of the primary key.

Instead, only the first column is marked as being part of the primary key.

Example test case (e.g. in SchemaReaderTests):

    func test_columnDefinitions_composite_primary_keys() throws {
        try db.run("""
        CREATE TABLE t (
          col1 INTEGER,
          col2 INTEGER,
          col3 INTEGER,
          PRIMARY KEY (col1, col2)
        );
        """)

        XCTAssertEqual(
            try schemaReader.columnDefinitions(table: "t"), [
            ColumnDefinition(
                    name: "col1",
                    primaryKey: .init(autoIncrement: false),
                    type: .INTEGER,
                    nullable: true,
                    defaultValue: .NULL,
                    references: nil),
            ColumnDefinition(
                    name: "col2",
                    primaryKey: .init(autoIncrement: false),
                    type: .INTEGER,
                    nullable: true,
                    defaultValue: .NULL,
                    references: nil),
            ColumnDefinition(
                    name: "col3",
                    primaryKey: nil,
                    type: .INTEGER,
                    nullable: true,
                    defaultValue: .NULL,
                    references: nil)
            ]
        )
    }

The test fails, as the second column is not detected as being part of the composite primary key.

This is due to the following line:

SQLite.swift/Sources/SQLite/Schema/SchemaReader.swift

Line 28 in f1bee07

primaryKey: row[TableInfoTable.primaryKeyColumn] == 1 ?

The PRAGMA table_info that is used to return the column definitions, returns one row for each defined column. The pk column contains:

... either zero for columns that are not part of the primary key, or the 1-based index of the column within the primary key).

See https://www.sqlite.org/pragma.html#pragma_table_info

A possible fix could be to change that line to:

    primaryKey: (row[TableInfoTable.primaryKeyColumn] ?? 0) > 0 ?

Build Information

• Include the SQLite.swift version: 0.14.1
• Mention Xcode and OS X versions affected: Xcode 14.3 and macOS Ventura 13.4
• How do do you integrate SQLite.swift in your project: Swift Package manager

[stefansaasen]

See #1217 for a possible fix.

[stefansaasen]

#1217 was merged to master, so I guess this can be closed.

cc @nathanfallet

[nathanfallet]

You're right. I didn't see your PR was not marking this issue for auto fix (using "Fix #..." in the PR description)

[stefansaasen]

Good point. I've created another issue and a possible fix and linked them in both directions now.