$Id: sqlite.txt 1869 2008-06-01 19:29:34Z mjs $

HOWTO: Compile

sqlite's configure script has a problem with the readline library being installed in an unusual location. (It also doesn't seem to respect LDFLAGS, CPPFLAGS.) To deal with this, configure in the usual way (it will complain that it can't find readline), then edit the resultant Makefile like so:

# Compiler options needed for programs that use the readline() library.         
#       
READLINE_FLAGS = -DHAVE_READLINE=1 -I/home/ithinkihaveacat/local/include        

# The library that programs using readline() must link against.  
#              
LIBREADLINE = -L/home/ithinkihaveacat/local-linux/lib -lreadline -lncurses

HOWTO: Backup

"Hot" backup, with schema:

$ sqlite3 foo.db .dump | sqlite3 bar.db

"Hot" backup, without schema (raw INSERT statements only):

$ echo -e ".mode insert\nselect * from table;" | sqlite3 database.db

HOWTO: Add column

sqlite> ALTER TABLE customers ADD COLUMN email TEXT

HOWTO: Rename column

As of 3.4.1 there isn't any good way. One way is to achieve this is to dump the database with .dump, edit the schema by hand, then re-import:

$ sqlite3 foo.db .dump > foo.sql
$ $EDITOR foo.sql # rename column in schema (DON'T re-order, add or delete columns!)
$ sqlite3 bar.db < foo.sql

HOWTO: Remove column

? I don't think this is possible--you have to dump and restore.

HOWTO: Rename table

Use [ALTER TABLE](http://www.sqlite.org/lang_altertable.html).

ERROR: "attempt to write a readonly database"

Make sure that the directory is writable as well. (So that the journal file can be created.)

If running under some system that caches database connections such as PHP, also make sure that after fixing any permission problems, you restart Apache, or at least make sure that any persistent connections to the database have been cleared.

HOWTO: Create an in-memory database

The special name ":memory:" creates an in memory database:

$db = new PDO("sqlite::memory:");