Begin SQLite, Simple Database With High Performance
Recently I have been using SQLite, a simple file based database. (Usually I mainly use the combination of Amazon RDS and MariaDB (MySQL).)
I think one of the most useful aspect of SQLite is its high performance.
Actually I feel SQLite is often hard to use it for the complicated or somewhat big projects because it is simple and often fragile, and does not support simultaneous writing. In my case, I use SQLite in the cases in which the performance is very important and the modification of data is processed on the local environment and is not necessary on the production server. If you know its features correctly, SQLite is useful enough.
In another case, if you develop smartphone applications and want to use a relational database in them, you need to use SQLite.
# What is a File Based Database?
SQLite is a file based database, which means data is saved into one file per a database and this file is directly connected. In most databases, daemons for each database are running and are connected to access their data.
You can connect to a SQLite database with the following command, and if the specified file does not exists, it will be automatically created.
sqlite3 test.db
# Backup and Restoration with Copying Files
You can copy or backup all the data by just copying files because all the data in SQLite is stored in a single file. However, the file may become corrupted if the data is modified during copying.
It is safer to use the .dump command for backup or restoration as like following:
# dump the data to backup.sql
sqlite3 test.db .dump > backup.sql
# restore the data from backup.sql
sqlite3 test.db < backup.sql
2
3
4
5
In this way, however, you should remember ROWID (which is described later), may not be maintained.
I think that tables should be designed not to provoke any problems even if ROWIDs change.
# Data Types
Data types are more limited than most of other relational databases.
| Type | Description |
|---|---|
| NULL | NULL value |
| INTEGER | Signed integer value, stored in 0, 1, 2, 3, 4, 6, or 8 bytes |
| REAL | Floating point value, stored as an 8-byte IEEE floating point number |
| TEXT | Text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) |
| BLOB | Blob of data, stored exactly as it was input |
You can also designate NUMERIC in CREATE TABLE or ALTER statement, which means INTEGER or REAL.
# Non-Existant Data Type
You can also specify data types which do not exist in CREATE TABLE or ALTER statement.
CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
a HOGE,
b FUGA
);
2
3
4
5
In this case, values are stored with its original data type.
INSERT INTO test (a,b) VALUES("a",1);
SELECT id, a, typeof(a), b, typeof(b) FROM test;
2
id a typeof(a) b typeof(b)
---------- ---------- ---------- ---------- ----------
1 a text 1 integer
2
3
# Values With Different Data Type
In SQLite, you can specify values different from the definition of each fields.
CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
a INTEGER,
b REAL,
c TEXT
);
INSERT INTO test (a,b,c) VALUES(1, 1.0, "a");
INSERT INTO test (a,b,c) VALUES(3, 3, 3);
INSERT INTO test (a,b,c) VALUES(3.0, 3.0, 3.0);
INSERT INTO test (a,b,c) VALUES(3.5, 3.5, 3.5);
INSERT INTO test (a,b,c) VALUES("3", "3", "3");
INSERT INTO test (a,b,c) VALUES("3.0", "3.0", "3.0");
INSERT INTO test (a,b,c) VALUES("3.5", "3.5", "3.5");
INSERT INTO test (a,b,c) VALUES("c", "c", "c");
INSERT INTO test (a,b,c) VALUES("09", "09", "09");
INSERT INTO test (a,b,c) VALUES("0xFF", "0xFF", "0xFF");
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
The following table is values saved with the above commands.
SELECT id, a, typeof(a), b, typeof(b), c, typeof(c) FROM test;
id a typeof(a) b typeof(b) c typeof(c)
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 integer 1.0 real a text
2 3 integer 3.0 real 3 text
3 3 integer 3.0 real 3.0 text
4 3.5 real 3.5 real 3.5 text
5 3 integer 3.0 real 3 text
6 3 integer 3.0 real 3.0 text
7 3.5 real 3.5 real 3.5 text
8 c text c text c text
9 9 integer 9.0 real 09 text
10 0xFF text 0xFF text 0xFF text
2
3
4
5
6
7
8
9
10
11
12
From this, you can see the following things:
- When REAL or TEXT values are saved in a INTEGER field, they are converted to a INTEGER, REAL, or TEXT value.
- When INTEGER values are saved in a REAL field, they are converted to a REAL value.
- When TEXT values are saved in a REAL field, they are converted to a REAL or TEXT value.
- Values saved in a TEXT field are always converted to a TEXT value.
- When values are saved as an INTEGER or REAL datatype, octal or hexadecimal format is not considered, and leading 0s are ignored.
I think it is easy to understand this by thinking that basically values are converted into INTEGER, REAL, or TEXT datatype as possible, and if the modification is impossible, value datatypes are maintained.
# ROWID
In SQLite, a field named ROWID is automatically attached with each records. It is refered only when explicitly designated.
INSERT INTO test (a,b) VALUES("a",1);
INSERT INTO test (a,b) VALUES("b",2);
INSERT INTO test (a,b) VALUES("c",3);
2
3
SELECT * FROM test;
id a b
---------- ---------- ----------
1 a 1
2 b 2
3 c 3
2
3
4
5
SELECT rowid,* FROM test;
id id a b
---------- ---------- ---------- ----------
1 1 a 1
2 2 b 2
3 3 c 3
2
3
4
5
You can use AUTOINCREMENT syntax also in SQLite. ROWIDs are also attached with serial numbers as like AUTOINCREMENT, but ROWIDs are often re-attached with other numbers when dumped and restored. I think that tables should be designed not to provoke any problems even if ROWIDs change.
# LAST_INSERT_ROWID()
In SQLite, you can use LAST_INSERT_ROWID function, which gets the ROWID of the last inserted record.
INSERT INTO test (a,b) VALUES("d",4);
SELECT LAST_INSERT_ROWID();
2
LAST_INSERT_ROWID()
-------------------
4
2
3
In SQLite, there are no function which retrieve the auto-incremented primary key of the last inserted row, as like LAST_INSERT_ID() in MySQL. If you want to do this, you can use the following condition.
SELECT id FROM test WHERE rowid = LAST_INSERT_ROWID();
id
----------
4
2
3
# CREATE TABLE
CREATE TABLE statement is almost the same as other relational databases.
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0,
b TEXT NOT NULL DEFAULT ''
);
2
3
4
5
6
Some minor differences are following:
- When
AUTOINCREMENTis specified,PRIMARY KEYmust be put after the field name. (An error will be occured whenPRIMARY KEY({field_name})is put inCREATE TABLEstatement.) - When
NOT NULLis specified andDEFAULTis not specified, a non-null value must be specified inINSERTstatements. (In the case of MySQL, for example, the default value ofINTEGER NOT NULLwithoutDEFAULTis automatically set to0) WITHOUT ROWIDcan be designated afterCREATE TABLEstatement.
WITHOUT ROWID is, as its name, the optional syntax with which a table is created without ROWID. You can enhance the database performance by specifying WITHOUT ROWID syntax, but I will mention this command in another post in the future because it will make this post too long.
# ALTER TABLE
You should remember that , compared with other relational databases, there are some syntaxes in ALTER TABLE which are not supported.
ALTER DROPis not suppoted.ALTER CHANGEis not suppoted.（Although,ALTER RENAMEis suppoted.）ALTER ADDis suppoted, butAFTER, which can be used in MySQL, is not supported.RENAMEcan be used to rename a table.
The following statements are examples.
/* New fields are always added to the end. */
ALTER TABLE test ADD c INTEGER NOT NULL DEFAULT 0;
/* rename a field */
ALTER TABLE test RENAME c TO d;
/* rename a table */
ALTER TABLE test RENAME TO testb;
2
3
4
5
6
7
8
# Change the Definition of a Field
If you want to add a field other than to the end, or if you want to modify the definition of a field, you need to recreate a new table.
Suppose we have a table like the one below.
.schema test
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
a HOGE,
b FUGA
);
2
3
4
5
select * from test;
id a b
---------- ---------- ----------
1 a 1
2 b 2
3 c 3
4 d 4
2
3
4
5
6
We try to add a field named c after the field id, and additionally modify the definition of a and b, from the unknown datatypes of HOGE and FUGA to defined datatypes.
/* rename the table */
ALTER TABLE test RENAME TO test_tmp;
/* create a new table */
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0,
b TEXT NOT NULL DEFAULT ''
);
/* copy all the records in `test_tmp` into the `test` table */
INSERT INTO test (id,c,a,b) SELECT id,0,a,b FROM test_tmp;
INSERT INTO test (rowid,id,c,a,b) SELECT rowid,id,0,a,b FROM test_tmp;
/* remove the old table */
DROP TABLE test_tmp;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Then, the schema of the table is modified.
.schema test
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0,
b TEXT NOT NULL DEFAULT ''
);
2
3
4
5
6
select * from test;
id c a b
---------- ---------- ---------- ----------
1 0 a 1
2 0 b 2
3 0 c 3
4 0 d 4
2
3
4
5
6
In my environment, when I also try this operation with ROWID with jumping values, ROWIDs are maintained from the old table. Actually this is surprising for me, but it may be designed to take over ROWIDs as possible.
Anyway, when duplicated records also exist, other ROWIDs are attached, and at lease I'm not sure ROWIDs are maintained in other versions, so I think it is better to take into account that ROWIDs may be reattached with other numbers.
If the target table is created with CREATE INDEX statements, you should also execute CREATE INDEX statements after CREATE TABLE.
# DROP TABLE
DROP TABLE statement is almost the same as other relational databases.
DROP TABLE test;
# CREATE INDEX
Syntaxes for creating indexes other than PRIMARY KEY can't be included in a CREATE TABLE statement, you need to define indexes with CREATE INDEX statements.
CREATE INDEX test_a ON test(a);
/* Composite Index */
CREATE INDEX test_ab ON test(a, b);
/* A new index is not created when already exists. */
CREATE INDEX IF NOT EXISTS test_ab ON test(a, b);
/* Unique Index */
CREATE UNIQUE INDEX test_ac ON test(a, c);
/* Conditional Index */
CREATE INDEX test_b ON test(b) WHERE b % 2 = 0;
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN QUERY PLAN statement shows the plan how indexes are used for processing SQL query.
# Single column indexes are used for conditions with a single column
EXPLAIN QUERY PLAN SELECT id FROM test WHERE a = 'a';
QUERY PLAN
`--SEARCH TABLE test USING COVERING INDEX test_a (a=?)
2
# Composite indexes are given priority for conditions with multiple columns
EXPLAIN QUERY PLAN SELECT id FROM test WHERE a = 'a' AND b = 2;
QUERY PLAN
`--SEARCH TABLE test USING COVERING INDEX test_ab (a=? AND b=?)
2
# Conditional indexes are used when the conditions are met
EXPLAIN QUERY PLAN SELECT id FROM test WHERE b % 2 = 0;
QUERY PLAN
`--SCAN TABLE test USING COVERING INDEX test_b
2
# DROP INDEX
DROP INDEX statement is almost the same as other relational databases.
DROP INDEX test_ac;
# INSERT
INSERT statement is almost the same as other relational databases.
INSERT INTO test (a,b) VALUES("a",1);
INSERT OR IGNORE syntax can be used to ignore conflicts with duplicates.
INSERT OR IGNORE INTO test (a,b) VALUES("a",1);
# Bulk Insert
You can insert multiple rows at once.
INSERT INTO test (a,b) VALUES
("a",1),
("b",2),
("c",3);
2
3
4
# INSERT SELECT
SELECT statement can be used with INSERT.
INSERT INTO test (a,b)
SELECT c,d FROM test2 WHERE c > 10;
2
# UPDATE
UPDATE statement is almost the same as other relational databases.
UPDATE test SET b = 10 WHERE a = 'b';
# DELETE
DELETE statement is almost the same as other relational databases.
DELETE FROM test WHERE a = 'b';
# SELECT
SELECT statement is almost the same as other relational databases.
SELECT * FROM test WHERE a = 'b' ORDER BY b ASC;
If you need ROWIDs, you have to specify rowid.
SELECT rowid, * FROM test WHERE a = 'b' ORDER BY b ASC;
# UPSERT
# REPLACE
REPLACE statement is used to replace a record if the duplicate row exists. Internally, when a duplicate row is found, REPLACE syntax will delete the row before inserting a new record, so if there is a field not specified, its value is updated to the default value. In addition, because of the same reason, the ROWID is updated to another numbers.
Look at the following commands in which REPLACE statement is used with a duplicate value of the field a.
.schema test
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0,
b TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX test_a ON test(a);
2
3
4
5
6
7
SELECT rowid, * FROM test;
id id c a b
---------- ---------- ---------- ---------- ----------
1 1 0 a 1
2 2 0 b 2
3 3 0 c 3
4 4 0 d 4
2
3
4
5
6
REPLACE INTO test (a,b,c) VALUES("c", 5, 1);
SELECT rowid, * FROM test;
2
id id c a b
---------- ---------- ---------- ---------- ----------
1 1 0 a 1
2 2 0 b 2
4 4 0 d 4
5 5 1 c
2
3
4
5
6
The record with rowid of 3 is replaced into another record with rowid of 5.
- As mentioned above,
REPLACEdo DELETE and INSERT, andROWIDandPRIMARY KEYare updated into new values. - The value of the field
b, which is not specified, is not the original value of 3 but the default value of NULL.
# ON CONFLICT
ON CONFLICT syntax with INSERT statement will update values when a conflicted row exists, and insert a new record when not. It is similar to ON DUPLICATE KEY UPDATE syntax in MySQL. You can adjust some behavior with various syntax.
/* Ignore if conflicted */
INSERT INTO test (a,b,c) VALUES("a",5,0)
ON CONFLICT DO NOTHING;
/* `INSERT OR IGNORE` means the same as `ON CONFLICT DO NOTHING` */
INSERT OR IGNORE INTO test (a,b,c) VALUES("a",5,0);
/* UPDATE when conflicted and INSERT when not. */
/* fields which may be conflicted need to be specified. */
INSERT INTO test (a,b,c) VALUES("e",5,3)
ON CONFLICT(a) DO UPDATE SET c = 3;
/* `WHERE` defines conditions which records are modified. */
/* Remember non-conflicting records are irrelevant regardless of whether `WHERE` is specified */
INSERT INTO test (a,b,c) VALUES("e",5,3)
ON CONFLICT(a) DO UPDATE SET c = 3 WHERE b % 2 == 0;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16