SQLite

About SQLite

• SQLite the most popular embedded relational database.

• No need for external server process. The client directly accesses the file containing the data.

• It has an in-memory version.

• Command-line tool is called sqlite3

Using SQLite in programming languages

• Rust
• Python
• Perl
• ...

Install SQLite command-line tool

• Download page

Debian/Ubuntu Linux:

$ sudo apt install sqlite3

Version of SQLite

$ echo .version | sqlite3
SQLite 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1
zlib version 1.3
gcc-13.2.0 (64-bit)

Help with SQLite

echo .help | sqlite3

SQLite interactive shell

$ sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .version
SQLite 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1
zlib version 1.3
gcc-13.2.0 (64-bit)

sqlite> .help
...

sqlite> .quit

SQLite interactive shell - CREATE-INSERT-SELECT manually

• Using in-memory database

sqlite3

• Using a database file

sqlite3 my.db

CREATE INSERT SELECT

• CREATE
• INSERT
• SELECT

CREATE TABLE person (
    name TEXT,
    email TEXT
);

INSERT INTO person (name, email) VALUES ('Foo', 'foo@example.com');
INSERT INTO person (name, email) VALUES ('Bar', 'bar@example.com');
SELECT * from person;

• In memory:

$ sqlite3 < create-insert-select.sql
Foo|foo@example.com
Bar|bar@example.com

• In file:

$ sqlite3 demo.db < create-insert-select.sql
Foo|foo@example.com
Bar|bar@example.com

$ rm -f demo.db

AUTOINCREMENT

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT
);

INSERT INTO people (username) VALUES ('foo');
INSERT INTO people (username) VALUES ('bar');
SELECT * from people;

$ sqlite3 demo.db < autoincrement.sql
1|foo
2|bar

$ rm -f demo.db

Missing value

CREATE TABLE qa (
    question TEXT,
    answer TEXT
);

INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');
INSERT INTO qa (question) VALUES ('Meaning of life?');
SELECT * from qa;

$ sqlite3 demo.db < missing-text-value.sql ; rm -f demo.db
Language?|SQL
Database?|SQLite
Meaning of life?|

CREATE TABLE qa (
    question TEXT,
    answer INTEGER
);

INSERT INTO qa (question, answer) VALUES ('2+2', 4);
INSERT INTO qa (question, answer) VALUES ('2-2', 0);
INSERT INTO qa (question) VALUES ('Meaning of life?');
SELECT * from qa;

$ sqlite3 demo.db < missing-integer-value.sql ; rm -f demo.db
2+2|4
2-2|0
Meaning of life?|

Field with DEFAULT value

CREATE TABLE qa (
    question TEXT,
    answer TEXT DEFAULT 42
);

INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');
INSERT INTO qa (question) VALUES ('Meaning of life?');
SELECT * from qa;

$ sqlite3 demo.db < default-value.sql ; rm -f demo.db
Language?|SQL
Database?|SQLite
Meaning of life?|42

FOREIGN KEY

• SQLite Foreign key

-- turn on FOREIGN KEY checking
PRAGMA foreign_keys = ON;

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT

);


CREATE TABLE groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    manager INTEGER,
    FOREIGN KEY(manager) REFERENCES people(id)
);

INSERT INTO people (name) VALUES ('Natalie Portman ');
INSERT INTO people (name) VALUES ('Gal Gadot');
INSERT INTO people (name) VALUES ('Cole Lawrence');
INSERT INTO people (name) VALUES ('Lior Raz');
INSERT INTO people (name) VALUES ('Erick Tryzelaar');
INSERT INTO people (name) VALUES ('Ernest Kissiedu');
SELECT * FROM people;
SELECT "-----";

INSERT INTO groups (name, manager) VALUES ('Rust London', (SELECT id FROM people WHERE name = 'Ernest Kissiedu'));
INSERT INTO groups (name, manager) VALUES ('Rust NYC', (SELECT id FROM people WHERE name = 'Cole Lawrence'));
INSERT INTO groups (name, manager) VALUES ('Rust Bay Area', (SELECT id FROM people WHERE name = 'Erick Tryzelaar'));
INSERT INTO groups (name, manager) VALUES ('Other Group', 42);
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

DELETE FROM people WHERE name = 'Cole Lawrence';
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

PRAGMA

PRAGMA

UPDATE

CREATE TABLE people (
    name TEXT,
    grade INTEGER
);

INSERT INTO people (name, grade) VALUES ('Joe', 40);
INSERT INTO people (name, grade) VALUES ('Jane', 60);
SELECT * from people;
SELECT "";

UPDATE people SET grade = 44 WHERE name = "Joe";
SELECT * from people;
SELECT "";

UPDATE people SET grade = (SELECT grade FROM people WHERE name = "Joe") + 1 WHERE name = "Joe";
SELECT * from people;
SELECT "";

SQLite Transaction - in a bank

• Setup Bank accounts with some initial money.
• Move some money from one account to another - two separate steps. Worked.
• Move some money from one account to another - two separate steps - stop in the middle. Failed.
• Move some money from one account to another - Transaction - stop in the middle. Worked. (money stayed where it was)
• Remove bank

CREATE TABLE bank (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL
);

INSERT INTO bank (name, balance) VALUES ("Jane", 0);
INSERT INTO bank (name, balance) VALUES ("Mary", 1000);
INSERT INTO bank (name, balance) VALUES ("Ann", 1000);

SELECT * FROM bank;
SELECT "Total", SUM(balance) FROM bank;
SELECT "-----";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";

BEGIN TRANSACTION;
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";
COMMIT;

sqlite3 bank.db < setup_bank.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < transfer.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < without_transaction.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < with_transaction.sql
sqlite3 bank.db < show.sql

rm -f bank.db

Jane|0
Mary|1000
Ann|1000
Total|2000
-----
Jane|100
Mary|900
Ann|1000
Total|2000
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----

• TODO: loading a large CSV file into the database and running queries.
• TODO: creating a multi-tabe database, dumping it and then loading it and running queries against it.
• TODO: FOREIGN KEY - cascading deletition?