Sqlite - Study Notes 2

Flexible Typing

Type is at cell level.

Sqlite will convert the type to the declared type if it can without losing data.

 

5 data types:

 

Type Affinity

 

Contains --> Type

 

Strict Typing

 

 

Flexible typing and strict typing can be mixed:

 

Dates

sqlite> SELECT date();
2025-02-10

sqlite> SELECT time();
10:13:27

sqlite> SELECT datetime();
2025-02-10 10:06:00

sqlite> SELECT julianday();
2460716.92186773

sqlite> SELECT strftime("%d", "now");
10
sqlite> SELECT strftime("%s", "now");
1739182503

sqlite> SELECT datetime('now', 'start of month');
2025-02-01 00:00:00
sqlite> SELECT datetime('now', 'start of month', '+1 month');
2025-03-01 00:00:00
sqlite> SELECT datetime('now', 'start of month', '+1 month', '-1 day');
2025-02-28 00:00:00

 

Booleans

sqlite> SELECT true;
1
sqlite> SELECT false;
0

 

 

Floating point

 

rowid

 

 

Auto increment

 

Another table modifier: without rowid

 

Generated columns

 

Locking

Locking states:

1. Unlocked: no reading, no writing

2. Shared Lock: everyone can read it, but nobody can write to it

3. Reserved Lock: I'm going to write at some point, but I'm not writing yet. In this state, a new Shared Lock can be required.

4. Pending: In this state, nobody can require a new Shared Lock, waiting for open Shared Locks to go away.

5. Exclusive Lock: The lock owner can write to the database. Only one writer can hold this lock.

SQLite uses database-level locking rather than table-level locking. This means that when a write operation occurs, the entire database is locked, preventing other write operations from happening simultaneously. However, reads can still occur concurrently in most cases.

 

Journal Mode:

sqlite> pragma journal_mode;
memory

 

 

 

Rollback mode is the default.

 

 

Busy Timeout

In Wal mode, while a writer is writing the write-ahead-log, other writers have to wait. busy_timeout is how we can figure how long they can wait before giving up and throwing an error. The default value is 0.

 

 

 

 

 

 

 

Transaction Modes: deferred, exclusive, immediate

In Wal mode, immediate and exclusive are the same. 

Deferred is the default.

 

 

 

 

 

 

 

Vaccum

 

Deleting data didn't reduce the size of the database file.