This post discusses SQLite. applications. The post forms part of background documentation developed for a customer, but is sufficiently general to form a blog post.
SQLite is an open-source database which provides a useful alternative to an Oracle database for small applications.
SQLite is a transactional database which implements the database ACID properties
SQLite has quietly become one of the most widely used databases in the world, mainly because it is included on Android devices.
SQLite is distributed with Linux. Enterprise Linux 5U6 ships with sqlite-3.3; 6U4 ships with sqlite-3.6.
To run SQLite use:
[user@server]$ sqlite3 SQLite version 3.8.6 2014-08-15 11:46:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>
The above command starts an interactive shell. With parameters this shell will operate on a transient database.
To create a new database or to access an existing one, specify the name of the database on the command line. For example:
[user@server]$ sqlite3 test.db
It is not necessary to explicitly create a SQLite database.
The command shell supports both DML and DDL statements. It also provides other commands which are preceded with a period (.). A summary of available commands can be printed using .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
.eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN
.exit Exit this program
.explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.fullschema Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
If TABLE specified, only show indices for tables
matching LIKE pattern TABLE.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Use STRING in place of NULL values
.once FILENAME Output for the next SQL command only to FILENAME
.open ?FILENAME? Close existing database and reopen FILENAME
.output ?FILENAME? Send output to FILENAME or stdout
.print STRING... Print literal STRING
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator STRING ?NL? Change separator used by output mode and .import
NL is the end-of-line mark for CSV
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats on|off Turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace FILE|off Output each SQL statement as it is run
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify
To exit the command shell use .quit or .exit.
DDL statements are terminiated by a semicolon
DDL statements can be entered directly at the command line. For example:
sqlite> CREATE TABLE driver ( key NUMBER, name VARCHAR2(30), team VARCHAR2(30), points NUMBER );
Alternatively statements can be added to a script file which is then passed as standard input to sqlite3. For example we can create a file called c1.sql containing the following:
CREATE TABLE driver ( key NUMBER, name VARCHAR2(30), team VARCHAR2(30), points NUMBER );
This can be executed as follows:
[user@server]$ sqlite3 test.db < c1.sql
For example:
DROP TABLE driver;
DML statements can also be entered directly at the command line or executed using script files.
The INSERT statement syntax is similar to Oracle. For example:
INSERT INTO driver VALUES (1,'Sebastial Vettel','Red Bull',374); INSERT INTO driver VALUES (2,'Jenson Button','McLaren',240); INSERT INTO driver VALUES (3,'Fernando Alonso','Ferrari',227); INSERT INTO driver VALUES (4,'Mark Webber','Red Bull',221);
This is an example of an UPDATE statement:
UPDATE driver SET points = 399 WHERE key = 1;
This is an example of an DELETE statement:
DELETE FROM driver WHERE key = 3;
The contents of the table can be displayed using a SELECT statement. For example:
sqlite> SELECT * FROM driver; 1|Sebastial Vettel|Red Bull|399 2|Jenson Button|McLaren|240 4|Mark Webber|Red Bull|221
By default the output is separated by pipe symbols. The separator can be changed using the .separator command. For example to change the separator to a semicolon:
sqlite> . separator ; sqlite> SELECT * FROM driver; 1;Sebastial Vettel;Red Bull;399 2;Jenson Button;McLaren;240 4;Mark Webber;Red Bull;221
By default auto-commit is enabled in SQLite. So there is an implicit commit after each DML statement.
To disable auto-commit a transaction must start with BEGIN TRANSACTION. Following a BEGIN TRANSACTION all subsequent DML statements are considered part of the same transaction until a COMMIT or ROLLBACK is issued.
For example:
sqlite> SELECT * FROM driver; 1;Sebastial Vettel;Red Bull;399 2;Jenson Button;McLaren;240 4;Mark Webber;Red Bull;221
If BEGIN TRANSACTION is issued then ROLLBACK will discard any changes:
sqlite> BEGIN TRANSACTION; sqlite> DELETE FROM driver WHERE key = 1; sqlite> ROLLBACK; sqlite> SELECT * FROM driver; 1;Sebastial Vettel;Red Bull;399 2;Jenson Button;McLaren;240 4;Mark Webber;Red Bull;221
If BEGIN TRANSACTION; is issued then COMMIT will save any changes:
sqlite> BEGIN TRANSACTION; sqlite> DELETE FROM driver WHERE key = 1; sqlite> COMMIT; sqlite> SELECT * FROM driver; 2;Jenson Button;McLaren;240 4;Mark Webber;Red Bull;221
BEGIN TRANSACTION can be abbreviated to BEGIN
SQLite supports several types of constraint including:
Constraints can only be created using the CREATE TABLE statement. Constraints cannot be added after a table has been created.
Note that by default SQLite does not support foreign key constraints.
The primary key uniquely identifies every row in the table.
The following is an example of the declaration of a table with a primary key constraint
CREATE TABLE funds ( fundkey NUMBER PRIMARY KEY, fundcode VARCHAR2(8), fundtype NUMBER, description VARCHAR2(30) );
In SQLite primary key columns can be NULL. Specify an additional NOT NULL constraint to force non-null values.
Multi-column primary keys can also be created. For example:
CREATE TABLE order_items (\t order_number NUMBER, item_number NUMBER, quantity NUMBER, PRIMARY KEY (order_number,item_number) );
A unique constraint ensures that all non-null values in a column are unique.
The following is an example of the declaration of a table with a unique constraint
CREATE TABLE funds ( fundkey NUMBER, fundcode VARCHAR2(8) UNIQUE, fundtype NUMBER, description VARCHAR2(30) );
Ensures that a column cannot have a NULL value.
The following is an example of the declaration of a table with NOT NULL constraints
CREATE TABLE funds ( fundkey NUMBER NOT NULL, fundcode VARCHAR2(8) NOT NULL, fundtype NUMBER NOT NULL, description VARCHAR2(30) NOT NULL );
A default constraint provides a default value for a column where none has been specified.
The following is an example of the declaration of a table with a default constraint
CREATE TABLE funds ( fundkey NUMBER, fundcode VARCHAR2(8), fundtype NUMBER DEFAULT 1, description VARCHAR2(30) );
A check constraint ensures that all values in a column satisfy specified conditions
The following is an example of the declaration of a table with a CHECK constraint
CREATE TABLE funds ( fundkey NUMBER, fundcode VARCHAR2(8), fundtype NUMBER CHECK(fundtype IN (1,2)), description VARCHAR2(30) );
In the above example, the value of the fundtype column must be 1 or 2.
SQLite does not have a DUAL table
Single-row SELECT statements do not require a FROM clause. For example:
sqlite> SELECT UPPER ('Ferrari');
FERRARI
sqlite> SELECT 400 / 10 40
sqlite> SELECT SUBSTR ('Silverstone',1,6)
Silver
The SQLite data dictionary consists of a table containing DDL statements as opposed to using a relational data dictionary.
The .tables command lists all tables in the database. For example:
sqlite> .tables driver
The entire contents of the data dictionary can be listed using the .schema command.
Alternative DDL for individual tables can be listed using .schema The .schema command returns the result of a join between sqlite_master
and sqlite_temp_master
The .indices command reports all indexes created for a specfied table
In the above example system-generated indexes have been created for
the primary key and the unique key
sqlite> .schema driver
CREATE TABLE driver
(
key NUMBER PRIMARY KEY,
name VARCHAR2(30) UNIQUE,
team VARCHAR2(30),
points NUMBER
);
sqlite> .indices driver
sqlite_autoindex_driver_1
sqlite_autoindex_driver_2