Repository: stephencelis/SQLite.swift (swift)

Issue:
Creating a column affinity based on a string does not produce the correct affinity
E.g. take the example table definition from the `TestHelper`: https://github.com/stephencelis/SQLite.swift/blob/f1bee07ea9aa304d7e7024e759c4a031672c8732/Tests/SQLiteTests/TestHelpers.swift#L22-L31

According to [`3.1. Determination Of Column Affinity`](https://www.sqlite.org/datatype3.html#determination_of_column_affinity) the affinity of a column is based on the declared type. With the "parsing" following a set of five rules (see linked page).

So for the above table definition, the column affinity should be:

* `id` -> rule 1: `INTEGER` :white_check_mark:
* `email` -> rule 2: `TEXT` :white_check_mark:
* `age` -> rule 1: `INTEGER` :white_check_mark:
* `salary` -> rule 4: `REAL` :white_check_mark:
* `admin` -> rule 5: `NUMERIC` :x:
* `manager_id` -> rule 1: `INTEGER` :white_check_mark:
* `created_at` -> rule 5: `NUMERIC` :x:

but the affinity is incorrect for the following columns: `admin` and `created_at` (`TEXT` instead of `NUMERIC`).

See https://github.com/stephencelis/SQLite.swift/blob/f1bee07ea9aa304d7e7024e759c4a031672c8732/Tests/SQLiteTests/Schema/SchemaReaderTests.swift#L17-L57


A possible solution is to create the `Affinity` and implement the 5 rules, e.g. like here: https://github.com/stefansaasen/SQLite.swift/commit/fabbe8c0995aeddf343b8de949777e3e7aea5ea4

There is one important thing to consider here, the default affinity for declared types that are not covered is now `NUMERIC` instead of `TEXT`. E.g. with the gotcha mentioned here: https://www.sqlite.org/datatype3.html#determination_of_column_affinity

> And the declared type of "STRING" has an affinity of NUMERIC, not TEXT

Should probably considered to be a breaking change.

P.S.: Happy to create a PR if the approach in the commit linked above is acceptable.

## 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

Pull request description:
See https://www.sqlite.org/datatype3.html#determination_of_column_affinity for how SQLite determines column affinity.

Fixes #1215

Patch:
diff --git a/Sources/SQLite/Schema/SchemaDefinitions.swift b/Sources/SQLite/Schema/SchemaDefinitions.swift
index 2d38e1fb..3c6b0523 100644
--- a/Sources/SQLite/Schema/SchemaDefinitions.swift
+++ b/Sources/SQLite/Schema/SchemaDefinitions.swift
@@ -57,7 +57,19 @@ public struct ColumnDefinition: Equatable {
         }
 
         init(_ string: String) {
-            self = Affinity.allCases.first { $0.rawValue.lowercased() == string.lowercased() } ?? .TEXT
+            let test = string.uppercased()
+            // https://sqlite.org/datatype3.html#determination_of_column_affinity
+            if test.contains("INT") { // Rule 1
+                self = .INTEGER
+            } else if ["CHAR", "CLOB", "TEXT"].first(where: {test.contains($0)}) != nil { // Rule 2
+                self = .TEXT
+            } else if string.contains("BLOB") { // Rule 3
+                self = .BLOB
+            } else if ["REAL", "FLOA", "DOUB"].first(where: {test.contains($0)}) != nil { // Rule 4
+                self = .REAL
+            } else { // Rule 5
+                self = .NUMERIC
+            }
         }
     }
 


Test patch:
diff --git a/Tests/SQLiteTests/Schema/SchemaDefinitionsTests.swift b/Tests/SQLiteTests/Schema/SchemaDefinitionsTests.swift
index ef97b981..8b7e27e3 100644
--- a/Tests/SQLiteTests/Schema/SchemaDefinitionsTests.swift
+++ b/Tests/SQLiteTests/Schema/SchemaDefinitionsTests.swift
@@ -71,8 +71,68 @@ class AffinityTests: XCTestCase {
         XCTAssertEqual(ColumnDefinition.Affinity("NUMERIC"), .NUMERIC)
     }
 
-    func test_returns_TEXT_for_unknown_type() {
-        XCTAssertEqual(ColumnDefinition.Affinity("baz"), .TEXT)
+    // [Determination Of Column Affinity](https://sqlite.org/datatype3.html#determination_of_column_affinity)
+    // Rule 1
+    func testIntegerAffinity() {
+        let declared = [
+            "INT",
+            "INTEGER",
+            "TINYINT",
+            "SMALLINT",
+            "MEDIUMINT",
+            "BIGINT",
+            "UNSIGNED BIG INT",
+            "INT2",
+            "INT8"
+        ]
+        XCTAssertTrue(declared.allSatisfy({ColumnDefinition.Affinity($0) == .INTEGER}))
+    }
+
+    // Rule 2
+    func testTextAffinity() {
+        let declared = [
+            "CHARACTER(20)",
+            "VARCHAR(255)",
+            "VARYING CHARACTER(255)",
+            "NCHAR(55)",
+            "NATIVE CHARACTER(70)",
+            "NVARCHAR(100)",
+            "TEXT",
+            "CLOB"
+        ]
+        XCTAssertTrue(declared.allSatisfy({ColumnDefinition.Affinity($0) == .TEXT}))
+    }
+
+    // Rule 3
+    func testBlobAffinity() {
+        XCTAssertEqual(ColumnDefinition.Affinity("BLOB"), .BLOB)
+    }
+
+    // Rule 4
+    func testRealAffinity() {
+        let declared = [
+            "REAL",
+            "DOUBLE",
+            "DOUBLE PRECISION",
+            "FLOAT"
+        ]
+        XCTAssertTrue(declared.allSatisfy({ColumnDefinition.Affinity($0) == .REAL}))
+    }
+
+    // Rule 5
+    func testNumericAffinity() {
+        let declared = [
+            "NUMERIC",
+            "DECIMAL(10,5)",
+            "BOOLEAN",
+            "DATE",
+            "DATETIME"
+        ]
+        XCTAssertTrue(declared.allSatisfy({ColumnDefinition.Affinity($0) == .NUMERIC}))
+    }
+
+    func test_returns_NUMERIC_for_unknown_type() {
+        XCTAssertEqual(ColumnDefinition.Affinity("baz"), .NUMERIC)
     }
 }
 
diff --git a/Tests/SQLiteTests/Schema/SchemaReaderTests.swift b/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
index dd5ae103..b03045de 100644
--- a/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
+++ b/Tests/SQLiteTests/Schema/SchemaReaderTests.swift
@@ -40,7 +40,7 @@ class SchemaReaderTests: SQLiteTestCase {
                              references: nil),
             ColumnDefinition(name: "admin",
                              primaryKey: nil,
-                             type: .TEXT,
+                             type: .NUMERIC,
                              nullable: false,
                              defaultValue: .numericLiteral("0"),
                              references: nil),
@@ -51,7 +51,7 @@ class SchemaReaderTests: SQLiteTestCase {
                              references: .init(table: "users", column: "manager_id", primaryKey: "id", onUpdate: nil, onDelete: nil)),
             ColumnDefinition(name: "created_at",
                              primaryKey: nil,
-                             type: .TEXT,
+                             type: .NUMERIC,
                              nullable: true,
                              defaultValue: .NULL,
                              references: nil)