{"id":642,"date":"2020-02-27T11:41:30","date_gmt":"2020-02-27T10:41:30","guid":{"rendered":"http:\/\/www.renaudpradenc.com\/?p=642"},"modified":"2020-02-27T11:58:10","modified_gmt":"2020-02-27T10:58:10","slug":"sqlite-debugging-with-gwendal-roue","status":"publish","type":"post","link":"https:\/\/www.renaudpradenc.com\/?p=642","title":{"rendered":"SQLite debugging with Gwendal Rou\u00e9"},"content":{"rendered":"\n

The crash<\/h2>\n\n\n\n

A surprising crash was reported in our app. Some users would write a number in a text field, for instance \"7\"<\/code>. We expected the field to be saved as a string in the database, and read back as a string, but this is not what was happening. <\/p>\n\n\n\n

I wrote a unit test to reproduce the bug. The Product record is saved to the database and read back:<\/p>\n\n\n\n

    func testStoresQuantityUnitAsString() {\n        modelDatabase.dbQueue.inDatabase { (db) in\n            let product = PFProduct()\n            product.uuid = \"TEST\"\n            product.quantityUnit = \"7\" \/\/ This is a string\n            try! product.insert(in: db)\n            \n            let reloaded = PFProduct.load(withPrimaryKey: product.uuid, from: db)\n            XCTAssertEqual(reloaded?.quantityUnit, \"7\")\n        }\n    }<\/code><\/pre>\n\n\n\n
XCTAssertEqual failed: throwing \"-[__NSCFNumber length]: unrecognized selector sent to instance<\/pre>\n\n\n\n
Our bug was reproduced: a string was written in the quantityUnit<\/code> field as a string, but we got a NSNumber<\/code> when it was read back from the database.<\/p>\n\n\n\n
I ask Gwendal for help<\/h2>\n\n\n\n
I am very lucky: since my employer incwo<\/a><\/code> shares its premises with Pierlis<\/a>, I only had to walk 10 meters to talk to Gwendal Rou\u00e9<\/a>, who is the author of GRDB<\/a> and knows a thing or two about databases and SQLite in particular.<\/p>\n\n\n\n
What’s reassuring is that, in the beginning, Gwendal was perplex as well. The database migration did create the column as a STRING<\/code>, and we could verify that by opening the database in DBBrowser For SQLite<\/a>. And then, he had an idea. He opened a Terminal window and launched sqlite3:<\/p>\n\n\n\n
> sqlite3\nSQLite version 3.28.0 2019-04-15 14:49:49\nEnter \".help\" for usage hints.\nConnected to a transient in-memory database.\nUse \".open FILENAME\" to reopen on a persistent database.<\/code><\/pre>\n\n\n\n
With an in-memory database, it is now possible to execute SQL queries. Gwendal began by creating a table with a STRING<\/code> column:<\/p>\n\n\n\n
sqlite> CREATE TABLE t1(a STRING);<\/code><\/pre>\n\n\n\n
Then he inserted three rows with different types (a string which contains digits, a alphabetic string, and a number):<\/p>\n\n\n\n
sqlite> INSERT INTO t1 (a) VALUES ('7');\nsqlite> INSERT INTO t1 (a) VALUES ('TOTO');\nsqlite> INSERT INTO t1 (a) VALUES (12);<\/code><\/pre>\n\n\n\n
The typeof() operator gives the actual type of storage of a field:<\/p>\n\n\n\n
sqlite> SELECT typeof(a) FROM t1;\ninteger\ntext\ninteger<\/code><\/pre>\n\n\n\n
What? '7'<\/code> \u2014a string \u2014 was stored as an integer! <\/p>\n\n\n\n
This actually confirms Gwendal’s intuition. In this page of SQLite’s documentation<\/a>, you can see that STRING<\/code> is not a storage class of SQLite. TEXT<\/code> is. But as Gwendal tried to explain to me, SQLite tries to be compatible with SQL queries of other databases and will not complain if we use column types it does not know; instead it will do a guess, based on the actual type of the data. This is called \u201cType Affinity\u201d in the document.<\/p>\n\n\n\n
The fix<\/h2>\n\n\n\n
So we knew the problem: we ought not use STRING<\/code> as a type for a column in SQLite. And the solution: use TEXT<\/code> instead. But how can we fix the code? In SQLite, the type of a column can not be changed after its creation. This is a very usual problem and I know the steps:<\/p>\n\n\n\n