Description

sqlite is a thin wrapper for the SQLite library, which in turn is "a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine."

Install SQLite Dependency

Before you can use this module, you must first have the SQLite development library installed on your system.

Fedora 31:

sudo dnf -y install sqlite-devel

Ubuntu 20.04:

sudo apt install -y libsqlite3-dev

Windows:

• Download the source zip from SQLite Downloads
• Create a new sqlite subfolder inside v/thirdparty
• Extract the zip into that folder

Performance Tips

When performing a large amount of database calls (i.e. INSERTS), significant performance increase can be obtained by controlling the synchronization and journal modes.

For instance:

import sqlite

db := sqlite.connect('foo.db') or { panic(err) }
db.synchronization_mode(sqlite.SyncMode.off)
db.journal_mode(sqlite.JournalMode.memory)

fn connect(path string) ?DB

connect Opens the connection with a database.

enum JournalMode {
	off
	delete
	truncate
	persist
	memory
}

enum Result {
	ok = 0
	error = 1
	internal = 2
	perm = 3
	abort = 4
	busy = 5
	locked = 6
	nomem = 7
	readonly = 8
	interrupt = 9
	ioerr = 10
	corrupt = 11
	notfound = 12
	full = 13
	cantopen = 14
	protocol = 15
	empty = 16
	schema = 17
	toobig = 18
	constraint = 19
	mismatch = 20
	misuse = 21
	nolfs = 22
	auth = 23
	format = 24
	range = 25
	notadb = 26
	notice = 27
	warning = 28
	row = 100
	done = 101
	ok_load_permanently = 256
	error_missing_collseq = 257
	busy_recovery = 261
	locked_sharedcache = 262
	readonly_recovery = 264
	ioerr_read = 266
	corrupt_vtab = 267
	cantopen_notempdir = 270
	constraint_check = 275
	notice_recover_wal = 283
	warning_autoindex = 284
	error_retry = 513
	abort_rollback = 516
	busy_snapshot = 517
	locked_vtab = 518
	readonly_cantlock = 520
	ioerr_short_read = 522
	corrupt_sequence = 523
	cantopen_isdir = 526
	constraint_commithook = 531
	notice_recover_rollback = 539
	error_snapshot = 769
	busy_timeout = 773
	readonly_rollback = 776
	ioerr_write = 778
	corrupt_index = 779
	cantopen_fullpath = 782
	constraint_foreignkey = 787
	readonly_dbmoved = 1032
	ioerr_fsync = 1034
	cantopen_convpath = 1038
	constraint_function = 1043
	readonly_cantinit = 1288
	ioerr_dir_fsync = 1290
	cantopen_dirtywal = 1294
	constraint_notnull = 1299
	readonly_directory = 1544
	ioerr_truncate = 1546
	cantopen_symlink = 1550
	constraint_primarykey = 1555
	ioerr_fstat = 1802
	constraint_trigger = 1811
	ioerr_unlock = 2058
	constraint_unique = 2067
	ioerr_rdlock = 2314
	constraint_vtab = 2323
	ioerr_delete = 2570
	constraint_rowid = 2579
	ioerr_blocked = 2826
	constraint_pinned = 2835
	ioerr_nomem = 3082
	ioerr_access = 3338
	ioerr_checkreservedlock = 3594
	ioerr_lock = 3850
	ioerr_close = 4106
	ioerr_dir_close = 4362
	ioerr_shmopen = 4618
	ioerr_shmsize = 4874
	ioerr_shmlock = 5130
	ioerr_shmmap = 5386
	ioerr_seek = 5642
	ioerr_delete_noent = 5898
	ioerr_mmap = 6154
	ioerr_gettemppath = 6410
	ioerr_convpath = 6666
	ioerr_vnode = 6922
	ioerr_auth = 7178
	ioerr_begin_atomic = 7434
	ioerr_commit_atomic = 7690
	ioerr_rollback_atomic = 7946
	ioerr_data = 8202
}

https://www.sqlite.org/rescode.html

fn (r Result) is_error() bool

enum SyncMode {
	off
	normal
	full
}

struct DB {
pub mut:
	is_open bool
mut:
	conn &C.sqlite3 = unsafe { nil }
}

fn (db DB) @select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ?[][]orm.Primitive

sql expr

fn (db &DB) busy_timeout(ms int) int

Set a busy timeout in milliseconds.
Sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated.
(see https://www.sqlite.org/c3ref/busy_timeout.html)

fn (mut db DB) close() ?bool

close Closes the DB.
TODO: For all functions, determine whether the connection is closed first, and determine what to do if it is

fn (db DB) create(table string, fields []orm.TableField) ?

table

fn (db &DB) create_table(table_name string, columns []string)

Issue a "create table if not exists" command to the db.
Creates table named 'table_name', with columns generated from 'columns' array.
Default columns type will be TEXT.

fn (db DB) delete(table string, where orm.QueryData) ?

fn (db DB) drop(table string) ?

fn (db &DB) error_message(code int, query string) IError

fn (db &DB) exec(query string) ([]Row, int)

Execute the query on db, return an array of all the results, alongside any result code.
Result codes: https://www.sqlite.org/rescode.html

fn (db &DB) exec_none(query string) int

Execute a query returning only the result code.
In case you don't expect any row results, but still want a result code.
e.g. INSERT INTO ... VALUES (...)

fn (db &DB) exec_one(query string) ?Row

Execute a query, handle error code Return the first row from the resulting table

fn (db DB) insert(table string, data orm.QueryData) ?

sql stmt

fn (db &DB) journal_mode(journal_mode JournalMode)

Controls how the journal file is stored and processed.
off: No journal record is kept. (fastest) memory: Journal record is held in memory, rather than on disk.
delete: At the conclusion of a transaction, journal file is deleted.
truncate: Journal file is truncated to a length of zero bytes.
persist: Journal file is left in place, but the header is overwritten to indicate journal is no longer valid.

fn (db DB) last_id() orm.Primitive

fn (db &DB) last_insert_rowid() i64

Returns last insert rowid https://www.sqlite.org/c3ref/last_insert_rowid.html

fn (db &DB) q_int(query string) int

Returns a single cell with value int.

fn (db &DB) q_string(query string) string

Returns a single cell with value string.

fn (db &DB) str() string

fn (db &DB) synchronization_mode(sync_mode SyncMode)

Sets disk synchronization mode, which controls how aggressively SQLite will write data to physical storage.
off: No syncs at all. (fastest) normal: Sync after each sequence of critical disk operations.
full: Sync after each critical disk operation (slowest).

fn (db DB) update(table string, data orm.QueryData, where orm.QueryData) ?

struct Row {
pub mut:
	vals []string
}