By Oliver Merk – Principal Consultant

Been doing a lot with SQLite lately (sqlite.org) on the PlayBook and elsewhere and wanted to pass on a few quick notes.

First, SQLite columns are typeless.

What?

Yup. Even though you may declare fields as VARCHAR or DATETIME, you may actually store any type of data you want in them. The exception to this rule is for PRIMARY KEY fields declared as INTEGER.

I’ve found that typeless fields can lead to some problems when storing and retrieving date values. I suspect that minor version differences in SQLite are returning different CURRENT_TIMESTAMP values.

Here’s an example of some SQL which creates a table with a date field:

CREATE TABLE location (
    location_ID INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR,
    latitude NUMERIC NOT NULL,
    longitude NUMERIC NOT NULL,
    addDate DATETIME DEFAULT CURRENT_TIMESTAMP);

If I INSERT a new record without specifying the addDate value, SQLite will default to inserting its CURRENT_TIMESTAMP value to populate the addDate field and I get something like 2011-08-11 12:05:10.

But if I decide to explicitly use CURRENT_TIMESTAMP in my insert statement, the value gets stored in an odd-looking decimal format. For example, 2455769.105023148.

Depending on the SQLite version, I may or may not be able to convert the returned date field using JavaScript’s date parsing functions for fields inserted with CURRENT_TIMESTAMP:

My concern is that the code which now works with the PlayBook might break when it gets its next SQLite update. Guess I’ll burn that bridge when I get to it.

I should also mention a great Firefox add-on called SQLite Manager that allows you to manage these databases.

Oh, and how did I get the SQLite version?

SELECT DISTINCT sqlite_version() AS version FROM sqlite_master;

I’ll leave the rest up to you…