Numeric Type Column

[doop24]

update my table.

let x = Expression<Double>("x")

let db = try! Connection(dbFilePath)
let tbl = Table("test")

let dX: Double = 2000
try! db.run(tel.update(x <- dX))

update is success.

but select updated table is failure

for row in try! db.prepare(tbl.select(x)) {
    print(row[x])
}

error occurred.

fatal error: unexpectedly found nil while unwrapping an Optional value

Query.swift  line: 1025
        guard let value = values[idx] as? V.Datatype else { return nil }

please help!

[mikemee]

Thanks @doop24. Any chance you can put this into a complete snippet that we can run in a Playground? It will allow us to get to this faster and give us both something concrete that we can point to (though your snippets are an excellent start, thank you!).

E.g. something like this (but demonstrating your problem obviously):

import SQLite

let db = try! Connection()
db.trace { print($0) }

let emails = Table("emails")
let to = Expression<String>("to")
let subject = Expression<String?>("subject")

try! db.run(emails.create(ifNotExists: true) {t in t.column(to); t.column(subject)} )
try! db.run(emails.insert(to <- "stephen@example.com", subject <- "Hello, world!" ))
try! db.run(emails.insert(to <- "stella@example.com", subject <- "RE: Hello, world!" ))
try! db.run(emails.insert(to <- "chewbacca@example.com", subject <- "Wookies" ))

for user in try db.prepare(emails) {
    print("id: \(user[to])")
}

[doop24]

Thanks, mikemee.
I was wrong. This issue is [ Numeric Type Column ] problem.

My sample db file is here.
http://www.comedge.co.jp/app/test2.db

This db has one table. (name: mytbl1)
table has one column (name: x, type numeric)
table has one record (value: 2000)

source:

        let db2 = try! Connection(dbPath)
        db2.trace { print($0) }

        let tbl2 = Table("mytbl1")
        let x2 = Expression<Double>("x")

        for row in try! db2.prepare(tbl2) {
            print("x: \(row[x2])”)
        }

error:
fatal error: unexpectedly found nil while unwrapping an Optional value

But update x value 2000 to 2000.1, This code will succeed without error.

[mikemee]

Great, thanks! I'll try and find some time to put this into a self-contained Playground and then set up a test, and then work with Stephen to fix it.

[doop24]

Thanks, mikemee.
I'm looking forward to fixing this issue!

[mikemee]

Here's a standalone repro case. @stephencelis I haven't dug into the code enough yet to quickly debug this...

Anyone want to show their Swift skills and provide a PR? (Preferably include a test case, but I'll add that in a pinch).

        let db2 = try! Connection()
        db2.trace { print($0) }

        let table = "mytbl1"
        let column = "x"
        try! db2.run("create table \(table) (\(column) NUMERIC) ")
        try! db2.run("insert into \(table) (\(column)) values (2000.1)")

        let tbl2 = Table(table)
        let x2 = Expression<Double>(column)

        // works
        for row in try! db2.prepare(tbl2) {
            print("x: \(row[x2])")
        }

        try! db2.run("delete from \(table)")
        try! db2.run("insert into \(table) (\(column)) values (2000)")

        // fails with value 2000 instead of 2000.1
        for row in try! db2.prepare(tbl2) {
            print("x: \(row[x2])")
        }

This produces the output:

create table mytbl1 (x NUMERIC) 
insert into mytbl1 (x) values (2000.1)
SELECT * FROM "mytbl1"
x: 2000.1
delete from mytbl1
insert into mytbl1 (x) values (2000)
SELECT * FROM "mytbl1"
fatal error: unexpectedly found nil while unwrapping an Optional value

and stops on the return in class Row in Query.swift here:

    public func get<V: Value>(column: Expression<V>) -> V {
        return get(Expression<V?>(column))!
    }

[bijugv]

It is not just with numeric data. I am also facing the same issue even with string data columns when trying to extract the data from a row array where column value is nil.
self.description = rowdata.get(c_description)
"fatal error: unexpectedly found nil while unwrapping an Optional value"

i am new to swift. Is there a way to check for "nil" in the column value?

[hiltonc]

Cursor's subscript uses the column type to decide what kind of binding to use for the value:

public subscript(idx: Int) -> Binding? {
    switch sqlite3_column_type(handle, Int32(idx)) {
    case SQLITE_BLOB:
        return self[idx] as Blob
    case SQLITE_FLOAT:
        return self[idx] as Double
    case SQLITE_INTEGER:
        return self[idx] as Int64
    case SQLITE_NULL:
        return nil
    case SQLITE_TEXT:
        return self[idx] as String
    case let type:
        fatalError("unsupported column type: \(type)")
    }
}

In this example, 2000.1 is a SQLITE_FLOAT, so it comes in as a Double. 2000 is a SQLITE_INTEGER, so it comes in as an Int64.

Later, in Row's get, it does a guard to check for the correct type:

    func valueAtIndex(idx: Int) -> V? {
        guard let value = values[idx] as? V.Datatype else { return nil }
        return (V.fromDatatypeValue(value) as? V)!
    }

In this case, if the type is Double (true for 2000.1), it gets through. If it is not a Double (as is the case for 2000, an Int64), it returns nil.

You can get similarly bad behavior by declaring the column type to be TEXT, in which case all values come in as String, and are rejected for not being Double, even though they could be turned into Double. I would expect them to be turned into Double, because SQLite's column types are, by design, more suggestive than prescriptive.

I think the right thing to do is attempt to transform the data type in valueAtIndex:, but I'm not sure how to do that just yet. Should I look into that approach?

[hiltonc]

It's not done, but this is what I'm thinking. I really don't like how verbose it is, but I can't figure out a tighter way to make the compiler happy.

[stephencelis]

This is a problem with using the typed helpers only some of the time. If the table had been created using Table.create, it wouldn't have used the NUMERIC type, so it wouldn't have had trouble with the conversion.

@hiltonc I wonder if there's a way to tighten that logic up, but as you can probably tell looking through the code base as is, sometimes a lot of repetition is needed :(

I also wonder if the main issue here is bad error messaging. If we avoid the ! and instead call preconditionFailure("Tried to return \(expression type) but type was \(column type)"), I wonder if that would help?

[hiltonc]

I guess it depends on what use cases SQLite.swift should support. I agree, if you use SQLite.swift all the way, you won't run into trouble. But sometimes you want to use SQLite.swift to work with a database that was created somewhere else. For example, I use it in an app which consumes SQLite databases that are generated by code written in a different language altogether.

Definitely your call. I haven't run into this particular problem my self, I just want to help out and this problem looked interesting.

[groue]

If you have the opportunity to alter the schema of the database, you can work around this limitation by turning the NUMERIC column into a REAL column.

SQLite documents how to perform complex schema changes that go beyond the simple ALTER TABLE foo ADD COLUMN bar : https://www.sqlite.org/lang_altertable.html#otheralter

Basically, you temporarily disable foreign keys, and recreate your whole table in a safe transaction:

CREATE TABLE new_test (x: REAL);
INSERT INTO new_test SELECT x FROM test;
DROP TABLE test;
ALTER TABLE new_test RENAME TO test;

If the initial test table does not only contain integers, double, or nulls (numeric columns can contain strings and blobs), those would not be converted by the above process. You'd need an extra cast:

INSERT INTO new_test SELECT CAST(x AS REAL) FROM test;