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." [1]

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;

Date

field with unixtime stamp:

Show date '2024-07-20'...

"SELECT date(timestamp, 'unixepoch') ...

Show now:

SELECT date('now');
  2013-05-07

ref: https://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

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#SQLite

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/

Issues

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

Error: stepping, database disk image is malformed

Error:

Error: stepping, database disk image is malformed (11)

Repair:

sqlite3 SOMESQLITE3.db .recover > out.sql