Kenneth: /* SQLite Commands */

2025-10-11T19:42:07Z

SQLite Commands

← Older revision		Revision as of 19:42, 11 October 2025
Line 18:		Line 18:
	== SQLite Commands ==		== SQLite Commands ==

	Help:		=== Help ===
	.help		.help

	List tables:		=== List tables ===
	.tables		.tables

	Describe tables:		=== Describe tables ===
	select * from sqlite_master;		select * from sqlite_master;

	Dump all data, and table creates:		=== Dump all data, and table creates ===
	.dump		.dump

	Show table schema:		=== Show table schema ===
	.schema [table]		.schema [table]

Kenneth: /* Error: stepping, database disk image is malformed */

2024-07-29T06:20:37Z

Error: stepping, database disk image is malformed

← Older revision		Revision as of 06:20, 29 July 2024
Line 244:		Line 244:

	Repair:		Repair:
	sqlite3 SOMESQLITE3.db .recover		sqlite3 SOMESQLITE3.db .recover > out.sql

	== keywords ==		== keywords ==

Kenneth: /* Date */

2024-07-29T06:19:32Z

Date

← Older revision		Revision as of 06:19, 29 July 2024
Line 134:		Line 134:
	Show date '2024-07-20'...		Show date '2024-07-20'...
	"SELECT date(timestamp, 'unixepoch') ...		"SELECT date(timestamp, 'unixepoch') ...

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

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

	== SQL ==		== SQL ==

Kenneth: /* Auto increment */

2024-07-29T06:18:48Z

Auto increment

← Older revision		Revision as of 06:18, 29 July 2024
Line 126:		Line 126:
	PRIMARY KEY,data TEXT,num double,timeEnter DATE);"		PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

	the primary key "t1key" auto increments;		the primary key "t1key" auto increments;

			== Date ==

			field with unixtime stamp:

			Show date '2024-07-20'...
			"SELECT date(timestamp, 'unixepoch') ...

	== SQL ==		== SQL ==

Kenneth: /* Issues */

2024-07-29T06:17:16Z

Issues

← Older revision		Revision as of 06:17, 29 July 2024
Line 224:		Line 224:
	References:		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		* 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

	== keywords ==		== keywords ==

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: /* SQLite Commands */

Kenneth: /* Error: stepping, database disk image is malformed */

Kenneth: /* Date */

Kenneth: /* Auto increment */

Kenneth: /* Issues */

Kenneth: /* SQL */