Kenneth: /* SQL */

2015-03-26T16:26:50Z

SQL

New page

== 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." [http://sqlite.org/]

== 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

<pre>
$ 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>
</pre>

== 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

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

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

<pre>
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);
</pre>

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

<pre>
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
</pre>

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.

<pre>
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
</pre>

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

== keywords ==

[[Category:Database]]
[[Category:Programming]]

SQLite - Revision history

Kenneth: /* SQL */