Repository: stephencelis/SQLite.swift (swift)

Issue:
Fatal error: 'try!' expression unexpectedly raised an error: Unexpected null value for column `"to"`
The following error occurs using the schema reader (specifically `columnDefinitions(table:)`) for a particular table (see below for the tables/foreign key definition that triggers this):

```
SQLite/Query.swift:1216: Fatal error: 'try!' expression unexpectedly raised an error: Unexpected null value for column `"to"`
```

https://github.com/stephencelis/SQLite.swift/blob/7a2e3cd27de56f6d396e84f63beefd0267b55ccb/Sources/SQLite/Typed/Query.swift#L1216

This is due to the fact that the `foreignKeys` function in the `SchemaReader` assumes that the `to` colum returned by running `PRAGMA foreign_key_list("table name")` contains a valid primary key column and is not null:

https://github.com/stephencelis/SQLite.swift/blob/7a2e3cd27de56f6d396e84f63beefd0267b55ccb/Sources/SQLite/Schema/SchemaReader.swift#L94-L107

The error occurs in line `100`: `row[ForeignKeyListTable.toColumn]`.

That is not necessarily true though. 

## How to reproduce

Minimal example (see below for the necessary `Package.swift` definition):

```swift
import SQLite

do {
    let db = try Connection()

    let sql = """
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);
"""

    try db.execute(sql)

    let schemaInfoArtist = try db.schema.columnDefinitions(table: "track")
    print("schemaInfoArtist: \(schemaInfoArtist)")

} catch {
    print("error: \(error)")
}
```

The table definition is taken from the last example on https://www.sqlite.org/foreignkeys.html#fk_indexes (Section 3 "Required and Suggested Database Indexes").

The result of the `PRAGMA` query is the following table:

```zsh
sqlite> PRAGMA foreign_key_list('track');
id|seq|table|from|to|on_update|on_delete|match
0|0|artist|trackartist||NO ACTION|NO ACTION|NONE
```

Here the `to` column is empty.

The SQLite source for the foreign key contains the following bits (see https://github.com/sqlite/sqlite/blob/master/src/sqliteInt.h#L2500):

```c
struct FKey {
  ...
  struct sColMap {      /* Mapping of columns in pFrom to columns in zTo */
   ....
    char *zCol;           /* Name of column in zTo.  If NULL use PRIMARY KEY */
  } aCol[1];           
};
```

So it seems valid to omit the primary key column and SQLite will automatically use the primary key column of the referenced table. On the flip side that means, `to` can in fact be null. In this example, the referenced column would be `artist. artistid` (the primary key of the `artist` table).

## Build Information

SQLite.swift version: `0.14.1` (also tested on `master` at 1b1eba0aa28046f43114ddc2805893acad647461)
Xcode version: `14.3`
macOS: `13.3.1`

SQLite.swift is integrated via the SPM:


```swift
import PackageDescription

let package = Package(
    name: "to-column-null",
    dependencies: [
        .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.14.1")
    ],
    targets: [
        // Targets are the basic building blocks of a package, defining a module or a test suite.
        // Targets can depend on other targets in this package and products from dependencies.
        .executableTarget(
            name: "to-column-null",
            dependencies: [
                .product(name: "SQLite", package: "SQLite.swift")
            ],
            path: "Sources"),
    ]
)
```

Pull request:
Closes #1199

Patch:
diff --git a/SQLite.xcodeproj/project.pbxproj b/SQLite.xcodeproj/project.pbxproj
index def32855..e6bb4fc9 100644
--- a/SQLite.xcodeproj/project.pbxproj
+++ b/SQLite.xcodeproj/project.pbxproj
@@ -211,6 +211,9 @@
 		DB7C5DA728D7C9B6006395CF /* SQLiteVersion.swift in Sources */ = {isa = PBXBuildFile; fileRef = DB7C5DA528D7C9B6006395CF /* SQLiteVersion.swift */; };
 		DB7C5DA828D7C9B6006395CF /* SQLiteVersion.swift in Sources */ = {isa = PBXBuildFile; fileRef = DB7C5DA528D7C9B6006395CF /* SQLiteVersion.swift */; };
 		DB7C5DA928D7C9B6006395CF /* SQLiteVersion.swift in Sources */ = {isa = PBXBuildFile; fileRef = DB7C5DA528D7C9B6006395CF /* SQLiteVersion.swift */; };
+		DBB93D5A2A22A373009BB96E /* SchemaReaderTests.swift in Sources */ = {isa = PBXBuildFile; fileRef = DBB93D592A22A373009BB96E /* SchemaReaderTests.swift */; };
+		DBB93D5B2A22A373009BB96E /* SchemaReaderTests.swift in Sources */ = {isa = PBXBuildFile; fileRef = DBB93D592A22A373009BB96E /* SchemaReaderTests.swift */; };
+		DBB93D5C2A22A373009BB96E /* SchemaReaderTests.swift in Sources */ = {isa = PBXBuildFile; fileRef = DBB93D592A22A373009BB96E /* SchemaReaderTests.swift */; };
 		EE247AD71C3F04ED00AE3E12 /* SQLite.h in Headers */ = {isa = PBXBuildFile; fileRef = EE247AD61C3F04ED00AE3E12 /* SQLite.h */; settings = {ATTRIBUTES = (Public, ); }; };
 		EE247ADE1C3F04ED00AE3E12 /* SQLite.framework in Frameworks */ = {isa = PBXBuildFile; fileRef = EE247AD31C3F04ED00AE3E12 /* SQLite.framework */; };
 		EE247B031C3F06E900AE3E12 /* Blob.swift in Sources */ = {isa = PBXBuildFile; fileRef = EE247AEE1C3F06E900AE3E12 /* Blob.swift */; };
@@ -340,6 +343,7 @@
 		DB58B21028FB864300F8EEA4 /* SchemaReader.swift */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.swift; path = SchemaReader.swift; sourceTree = "<group>"; };
 		DB58B21528FC7C4600F8EEA4 /* SQLiteFeature.swift */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.swift; path = SQLiteFeature.swift; sourceTree = "<group>"; };
 		DB7C5DA528D7C9B6006395CF /* SQLiteVersion.swift */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.swift; path = SQLiteVersion.swift; sourceTree = "<group>"; };
+		DBB93D592A22A373009BB96E /* SchemaReaderTests.swift */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.swift; path = SchemaReaderTests.swift; sourceTree = "<group>"; };
 		EE247AD31C3F04ED00AE3E12 /* SQLite.framework */ = {isa = PBXFileReference; explicitFileType = wrapper.framework; includeInIndex = 0; path = SQLite.framework; sourceTree = BUILT_PRODUCTS_DIR; };
 		EE247AD61C3F04ED00AE3E12 /* SQLite.h */ = {isa = PBXFileReference; lastKnownFileType = sourcecode.c.h; path = SQLite.h; sourceTree = "<group>"; };
 		EE247AD81C3F04ED00AE3E12 /* Info.plist */ = {isa = PBXFileReference; lastKnownFileType = text.plist.xml; path = Info.plist; sourceTree = "<group>"; };
@@ -483,6 +487,7 @@
 		19A17B56FBA20E7245BC8AC0 /* Schema */ = {
 			isa = PBXGroup;
 			children = (
+				DBB93D592A22A373009BB96E /* SchemaReaderTests.swift */,
 				19A176174862D0F0139B3987 /* SchemaDefinitionsTests.swift */,
 				19A17FDB0B0CFB8987906FD0 /* SchemaChangerTests.swift */,
 				19A17CA1DF7D0F7C9A94C51C /* Connection+SchemaTests.swift */,
@@ -998,6 +1003,7 @@
 				19A17FACE8E4D54A50BA934E /* FTS5Tests.swift in Sources */,
 				19A177909023B7B940C5805E /* FTSIntegrationTests.swift in Sources */,
 				19A17E1DD976D5CE80018749 /* FTS4Tests.swift in Sources */,
+				DBB93D5C2A22A373009BB96E /* SchemaReaderTests.swift in Sources */,
 				19A17411403D60640467209E /* ExpressionTests.swift in Sources */,
 				19A17CA4D7B63D845428A9C5 /* StatementTests.swift in Sources */,
 				19A17885B646CB0201BE4BD5 /* QueryTests.swift in Sources */,
@@ -1119,6 +1125,7 @@
 				19A178DA2BB5970778CCAF13 /* FTS5Tests.swift in Sources */,
 				19A1755C49154C87304C9146 /* FTSIntegrationTests.swift in Sources */,
 				19A17444861E1443143DEB44 /* FTS4Tests.swift in Sources */,
+				DBB93D5A2A22A373009BB96E /* SchemaReaderTests.swift in Sources */,
 				19A17DD33C2E43DD6EE05A60 /* ExpressionTests.swift in Sources */,
 				19A17D6EC40BC35A5DC81BA8 /* StatementTests.swift in Sources */,
 				19A17E3F47DA087E2B76D087 /* QueryTests.swift in Sources */,
@@ -1199,6 +1206,7 @@
 				19A1776BD5127DFDF847FF1F /* FTS5Tests.swift in Sources */,
 				19A173088B85A7E18E8582A7 /* FTSIntegrationTests.swift in Sources */,
 				19A178767223229E61C5066F /* FTS4Tests.swift in Sources */,
+				DBB93D5B2A22A373009BB96E /* SchemaReaderTests.swift in Sources */,
 				19A1781CBA8968ABD3E00877 /* ExpressionTests.swift in Sources */,
 				19A17923494236793893BF72 /* StatementTests.swift in Sources */,
 				19A17A52BF29D27C9AA229E7 /* QueryTests.swift in Sources */,
diff --git a/Sources/SQLite/Schema/SchemaDefinitions.swift b/Sources/SQLite/Schema/SchemaDefinitions.swift
index 2d38e1fb..9f4c126f 100644
--- a/Sources/SQLite/Schema/SchemaDefinitions.swift
+++ b/Sources/SQLite/Schema/SchemaDefinitions.swift
@@ -107,7 +107,7 @@ public struct ColumnDefinition: Equatable {
     public struct ForeignKey: Equatable {
         let table: String
         let column: String
-        let primaryKey: String
+        let primaryKey: String?
         let onUpdate: String?
         let onDelete: String?
     }
@@ -365,7 +365,7 @@ extension ColumnDefinition.ForeignKey {
         ([
             "REFERENCES",
             table.quote(),
-            "(\(primaryKey.quote()))",
+            primaryKey.map { "(\($0.quote()))" },
             onUpdate.map { "ON UPDATE \($0)" },
             onDelete.map { "ON DELETE \($0)" }
         ] as [String?]).compactMap { $0 }
diff --git a/Sources/SQLite/Schema/SchemaReader.swift b/Sources/SQLite/Schema/SchemaReader.swift
index 58a9b1bd..eb31888b 100644
--- a/Sources/SQLite/Schema/SchemaReader.swift
+++ b/Sources/SQLite/Schema/SchemaReader.swift
@@ -187,7 +187,7 @@ private enum ForeignKeyListTable {
     static let seqColumn = Expression<Int64>("seq")
     static let tableColumn = Expression<String>("table")
     static let fromColumn = Expression<String>("from")
-    static let toColumn = Expression<String>("to")
+    static let toColumn = Expression<String?>("to") // when null, use primary key
     static let onUpdateColumn = Expression<String>("on_update")
     static let onDeleteColumn = Expression<String>("on_delete")
     static let matchColumn = Expression<String>("match")


Test patch:
diff --git a/Tests/SQLiteTests/Schema/SchemaReaderTests.swift b/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
index dd5ae103..e90578e7 100644
--- a/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
+++ b/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
@@ -135,6 +135,44 @@ class SchemaReaderTests: SQLiteTestCase {
         ])
     }
 
+    func test_foreignKeys_references_column() throws {
+        let sql = """
+          CREATE TABLE artist(
+            artistid    INTEGER PRIMARY KEY,
+            artistname  TEXT
+          );
+          CREATE TABLE track(
+            trackid     INTEGER,
+            trackname   TEXT,
+            trackartist INTEGER REFERENCES artist(artistid)
+          );
+          """
+        try db.execute(sql)
+        let trackColumns = try db.schema.foreignKeys(table: "track")
+        XCTAssertEqual(trackColumns.map { $0.toSQL() }.joined(separator: "\n"), """
+            REFERENCES "artist" ("artistid")
+            """)
+    }
+
+    func test_foreignKeys_references_null_column() throws {
+        let sql = """
+          CREATE TABLE artist(
+            artistid    INTEGER PRIMARY KEY,
+            artistname  TEXT
+          );
+          CREATE TABLE track(
+            trackid     INTEGER,
+            trackname   TEXT,
+            trackartist INTEGER REFERENCES artist
+          );
+          """
+        try db.execute(sql)
+        let trackColumns = try db.schema.foreignKeys(table: "track")
+        XCTAssertEqual(trackColumns.map { $0.toSQL() }.joined(separator: "\n"), """
+            REFERENCES "artist"
+            """)
+    }
+
     func test_tableDefinitions() throws {
         let tables = try schemaReader.tableDefinitions()
         XCTAssertEqual(tables.count, 1)