9.7.2     Before you begin

This is NOT the latest copy of this book; click here for the latest version.

There are some "unique" aspects to SQLite that you should be aware of, of which the most important is its handling of field types. SQLite does not distinguish between data types beyond "string" and "number" - CHAR(255), for example, is the same as VARCHAR(20) is the same as TEXT, which makes it typeless like PHP. This might sound confusing, but it essentially boils down to "if your data type has CHAR, TEXT, BLOB, or CLOB in it, it is text; otherwise, it is a number". Note that this is fuzzy matching - VARCHAR has "CHAR" in, thus it is considered to be a text field.

There is one exception to this state of affairs, and that is when you want an auto-incrementing primary key value. If you define a field as being "INTEGER PRIMARY KEY", it must contain a 32-bit signed integer - equivalent to an INT data type in MySQL - and, if you do not fill this value when you insert a row, SQLite will automatically fill it with an integer one higher than the highest in there already. If the value is already at 2147483647, which is the highest number it can hold, SQLite will hand out random numbers. Note that the data type must be INTEGER and not INT - INT will be treated as a normal number field.

Finally, note that because SQLite stores its data in files, it is not able to handle multiple simultaneous writes to the same table. Essentially, when a write query comes in, SQLite locks the database (a file), performs the write, then unlocks the file - during the locked time, no other queries can write to that database. This is clearly a problem if you want your database to scale, or if you are using a system that does not have a reliable file locking mechanism such as NFS.