SQLite

SQLite
SQLite Home Page - http://sqlite.org/

"SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain." 

Command Line
Show help: sqlite3 mydb.db ".help"

SQL sqlite3 mydb.db "select * from mytable;"

Dump data from table 't1' to new table 't2': sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db

SQLite Commands
Help: .help

List tables: .tables

Describe tables: select * from sqlite_master;

Dump all data, and table creates: .dump

Show table schema: .schema [table]

Linux
Install: yum install sqlite3

Man: man sqlite3

$ sqlite3 mydata.db      SQLite version 3.1.3 Enter ".help" for instructions sqlite> create table memos(text, priority INTEGER); sqlite> insert into memos values('deliver project description', 10); sqlite> insert into memos values('lunch with Christine', 100); sqlite> select * from memos; deliver project description|10 lunch with Christine|100 sqlite>

SQL
Query Language Understood by SQLite - https://www.sqlite.org/lang.html

Create Table
Note: type definitions do not appear to be strictly enforced, unless primary key

Expression Affinity	Column Declared Type TEXT			"TEXT" NUMERIC			"NUM" INTEGER			"INT" REAL			"REAL" NONE			"" (empty string)

CREATE TABLE t1(a, b NOT NULL);	# can't be null CREATE TABLE t1(a, b UNIQUE);		# must be unique CREATE TABLE t1(a, b PRIMARY KEY);	# primary key same as unique, but can only have one CREATE TABLE t1(a, b INTEGER NOT NULL, c INTEGER PRIMARY KEY);		# auto increments 'c'

https://www.sqlite.org/lang_createtable.html

CREATE TABLE android_metadata (locale TEXT); CREATE TABLE attendees (meeting_id INTEGER, scout_id INTEGER, FOREIGN KEY(meeting_id) REFERENCES meetings(_id), FOREIGN KEY (scout_id) REFERENCES scouts(_id)); CREATE TABLE completions (_id INTEGER PRIMARY KEY AUTOINCREMENT, scout_id INTEGER NOT NULL, part_id INTEGER NOT NULL, timestamp DATETIME, reported DATETIME, FOREIGN KEY (scout_id) REFERENCES scouts(_id), FOREIGN KEY (part_id) REFERENCES parts(_id)); CREATE TABLE dens (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, rank_id INTEGER, FOREIGN KEY (rank_id) REFERENCES ranks(_id)); CREATE TABLE groups (_id INTEGER PRIMARY KEY AUTOINCREMENT, item_id INTEGER NOT NULL, name TEXT, number TEXT, qty_required INTEGER, notes TEXT, part_count int, FOREIGN KEY (item_id) REFERENCES items(_id)); CREATE TABLE item_completions (_id INTEGER PRIMARY KEY AUTOINCREMENT, scout_id INTEGER NOT NULL, item_id INTEGER NOT NULL, completed DATETIME, reported DATETIME, FOREIGN KEY (scout_id) REFERENCES scouts(_id), FOREIGN KEY (item_id) REFERENCES items(_id)); CREATE TABLE items (_id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, rank_id INTEGER, number TEXT, title TEXT, notes TEXT, image BLOB, group_count int, FOREIGN KEY (rank_id) REFERENCES ranks(_id)); CREATE TABLE meetings (_id INTEGER PRIMARY KEY AUTOINCREMENT, rank_id INTEGER, meeting_date DATETIME, notes TEXT, reported DATETIME, den_id INTEGER REFERENCES dens(_id), FOREIGN KEY (rank_id) REFERENCES ranks(_id)); CREATE TABLE parts (_id INTEGER PRIMARY KEY AUTOINCREMENT, group_id INTEGER NOT NULL, number TEXT, description TEXT, notes TEXT, FOREIGN KEY (group_id) REFERENCES groups(_id)); CREATE TABLE ranks (_id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE scouts (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, den_id INTEGER REFERENCES dens(_id), contacts TEXT, photo BLOB, email_address text, phone_number text); CREATE TABLE settings (setting_key char(50) primary key, setting_value text);

Alter Table
ALTER TABLE t1 ADD COLUMN hostid TEXT;

Destroy Table
DROP TABLE t1;

Create
INSERT INTO t1 VALUES('test', 1); INSERT INTO t1 VALUES('test');

Read
SELECT * FROM t1; SELECT count(*) FROM t1;

Update
UPDATE t1 SET a = 'test', b = 1 WHERE c = 1;

Destroy
DELETE FROM t1; DELETE FROM t1 WHERE c = 1;

Auto increment
Create table with auto inc primary key: sqlite3 test.db "create table t1 (t1key INTEGER                   PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

the primary key "t1key" auto increments;

SQL
SQLite Query Language: SELECT - http://www.sqlite.org/lang_select.html

core functions: (SQLite Query Language: Core Functions - http://www.sqlite.org/lang_corefunc.html)
 * abs, length
 * length, lower, ltrim, rtrim, substr, trim, upper
 * random, round

SQLite Query Language: Aggregate Functions - http://www.sqlite.org/lang_aggfunc.html
 * count, min, max
 * avg, sum

Distinct (aka unique) select distinct name from test;

Limit: select * from test limit 1;

Python
See Python

Tutorials

 * SQLite Tutorial: Common Commands and Triggers LG #109 - http://linuxgazette.net/109/chirico1.html
 * SQLite Tutorial - http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
 * SQLite YoLinux.com Embedded Database - http://www.yolinux.com/TUTORIALS/SQLite.html

Compiling
Building python 2.6 w/ sqlite3 module if sqlite is installed in non-standard location - Stack Overflow - http://stackoverflow.com/questions/1677666/building-python-2-6-w-sqlite3-module-if-sqlite-is-installed-in-non-standard-loc

./configure LDFLAGS='-L/path/to/lib' CPPFLAGS="-I/path/to/include'

Note: This didn't work.

Amalgamation Method
wget http://www.sqlite.org/sqlite-amalgamation-3071100.zip unzip sqlite-amalgamation-3071100.zip cd sqlite-amalgamation-3071100 gcc shell.c sqlite3.c -lpthread -ldl

References:
 * How To Compile SQLite - http://www.sqlite.org/howtocompile.html
 * The SQLite Amalgamation - http://www.sqlite.org/amalgamation.html

Autoconf Method
wget http://www.sqlite.org/sqlite-autoconf-3070500.tar.gz tar xvzf sqlite-autoconf-3070500.tar.gz cd sqlite-autoconf-3070500 ./configure make sudo make install

Source: http://eveningsamurai.wordpress.com/2011/02/18/installing-sqlite3-ruby-on-centos-5/

---

Although CentOS 5 has SQLite3 installed out of the box it is an older version so we must build a new one.

cd /tmp wget http://www.sqlite.org/sqlite-autoconf-3070800.tar.gz tar -zxvf sqlite-autoconf-3070800.tar.gz cd sqlite-autoconf-3070800 ./configure --prefix=/opt/sqlite3 && make && make install

Source: http://www.geekytidbits.com/ruby-on-rails-in-centos-5/

attempt to write a readonly database
The database needs write permission

chmod g+w .data/sql.db chown :apache .data/sql.db

References:
 * django - Why do I get sqlite error, "unable to open database file"? - Server Fault - http://serverfault.com/questions/57596/why-do-i-get-sqlite-error-unable-to-open-database-file

unable to open database file
The directory needs write permissions. (assuming for temporary files?)

chmod g+w .data chown :apache .data

References:
 * django - Why do I get sqlite error, "unable to open database file"? - Server Fault - http://serverfault.com/questions/57596/why-do-i-get-sqlite-error-unable-to-open-database-file