Blog

There are two things every web framework needs: a deceptively short "hello, world!" example and a todo list or blog app. Thus, when I set out to write pigwig, I needed to also write blogwig. Despite my obvious preference for statically generated blogs, this one needed to be dynamic and so needed a database. SQLite3 seemed like the popular choice, so away I went.

> CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    salt BLOB NOT NULL
);

According to the docs,

If the declared type contains the string "INT" then it is assigned INTEGER affinity.

Great, that's exactly what I wanted. OK, not exactly. "Contains" is not really the word I would use to describe the behavior. Unless the behavior is something really unexpected.

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

Uh. Well whatever, that has nothing to do with me.

> INSERT INTO users (username, password, salt) VALUES('a', 'a', 'a');
> INSERT INTO users (username, password, salt) VALUES('b', 'b', 'b');

> .headers on
> SELECT * FROM users;
id|username|password|salt
|a|a|a
|b|b|b

SQLFiddle

Um, excuse me? This tells me that the id for these two rows is NULL. id is my primary key. My primary key is NULL. My primary key is NULL! My primary key is NULL and SQLite doesn't even bat an eye or emit a warning. Actually, both my primary keys are NULL, so I have a non-unique primary key, but I guess in a world where my primary key can be NULL I guess who even cares anyway, right?

OK, let's calm down. Maybe I need to put AUTOINCREMENT?

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

Apparently not. But what's this about ROWID?

A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

What.