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 db.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.

fn connect_full(path string, mode_flags []OpenModeFlag, vfs_name string) !DB

connect_full Opens connection to sqlite database. It gives more control than open.
Flags give control over readonly and create decisions. Specific VFS can be chosen.

fn get_default_vfs() !&Sqlite3_vfs

get_default_vfs Asks sqlite for default VFS instance

fn get_vfs(name string) !&Sqlite3_vfs

get_vfs Requests sqlite to return instance of VFS with given name.
when such vfs is not known, none is returned

fn is_error(code int) bool

is_error checks if code is an error code.

type Sqlite3_file = C.sqlite3_file

type Sqlite3_io_methods = C.sqlite3_io_methods

type Sqlite3_vfs = C.sqlite3_vfs

fn (mut v Sqlite3_vfs) register_as_nondefault() !

register_as_nondefault Asks sqlite to register VFS passed in receiver argument as the known VFS.
more info about VFS: https://www.sqlite.org/c3ref/vfs.html 'not TODOs' to prevent corruption: https://sqlite.org/howtocorrupt.html example VFS: https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c

fn (mut v Sqlite3_vfs) unregister() !

unregister Requests sqlite to stop using VFS as passed in receiver argument

enum JournalMode {
	off
	delete
	truncate
	persist
	memory
}

enum OpenModeFlag {
	readonly = 0x00000001
	readwrite = 0x00000002
	create = 0x00000004
	uri = 0x00000040
	memory = 0x00000080
	nomutex = 0x00008000
	fullmutex = 0x00010000
	sharedcache = 0x00020000
	privatecache = 0x00040000
	exrescode = 0x02000000
	nofollow = 0x01000000
}

https://www.sqlite.org/c3ref/c_open_autoproxy.html

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
}

Result represents Sqlite Result and Error Codes see https://www.sqlite.org/rescode.html

fn (r Result) is_error() bool

is_error checks if it is an error code.

enum SyncMode {
	off
	normal
	full
}

struct C.sqlite3_file {
pub mut:
	pMethods &C.sqlite3_io_methods // Methods for an open file
}

https://www.sqlite.org/c3ref/file.html

struct C.sqlite3_io_methods {
mut:
// version 1 and later fields
	iVersion int

	xClose                 fn (&Sqlite3_file) int
	xRead                  fn (&Sqlite3_file, voidptr, int, i64) int
	xWrite                 fn (&Sqlite3_file, voidptr, int, i64) int
	xTruncate              fn (&Sqlite3_file, i64) int
	xSync                  fn (&Sqlite3_file, int) int
	xFileSize              Sig1
	xLock                  fn (&Sqlite3_file, int) int
	xUnlock                fn (&Sqlite3_file, int) int
	xCheckReservedLock     Sig2
	xFileControl           fn (&Sqlite3_file, int, voidptr) int
	xSectorSize            fn (&Sqlite3_file) int
	xDeviceCharacteristics fn (&Sqlite3_file) int
// version 2 and later fields
	xShmMap     fn (&Sqlite3_file, int, int, int, &voidptr) int
	xShmLock    fn (&Sqlite3_file, int, int, int) int
	xShmBarrier fn (&Sqlite3_file)
	xShmUnmap   fn (&Sqlite3_file, int) int
// version 3 and later fields
	xFetch   fn (&Sqlite3_file, i64, int, &voidptr) int
	xUnfetch fn (&Sqlite3_file, i64, voidptr) int
}

https://www.sqlite.org/c3ref/io_methods.html

struct C.sqlite3_vfs {
pub mut:
// version 1 and later fields
	iVersion   int // Structure version number (currently 3)
	szOsFile   int // Size of subclassed sqlite3_file
	mxPathname int // Maximum file pathname length
	pNext      &Sqlite3_vfs // Next registered VFS
	zName      &char        // Name of this virtual file system
	pAppData   voidptr      // Pointer to application-specific data

	xOpen   fn (&Sqlite3_vfs, &char, &Sqlite3_file, int, &int) int
	xDelete fn (&Sqlite3_vfs, &char, int) int

	xAccess       fn (&Sqlite3_vfs, &char, int, &int) int
	xFullPathname fn (&Sqlite3_vfs, &char, int, &char) int
	xDlOpen       fn (&Sqlite3_vfs, &char) voidptr
	xDlError      fn (&Sqlite3_vfs, int, &char)
	xDlSym        fn (&Sqlite3_vfs, voidptr, &char) voidptr // to fn accepting void and returning
	xDlClose      fn (&Sqlite3_vfs, voidptr)
	xRandomness   fn (&Sqlite3_vfs, int, &char) int
	xSleep        fn (&Sqlite3_vfs, int) int
	xCurrentTime  fn (&Sqlite3_vfs, &f64) int
	xGetLastError fn (&Sqlite3_vfs, int, &char) int
// version two and later only fields
	xCurrentTimeInt64 fn (&Sqlite3_vfs, &i64) int
// version three and later only fields
	xSetSystemCall  fn (&Sqlite3_vfs, &char, Fn_sqlite3_syscall_ptr) int
	xGetSystemCall  fn (&Sqlite3_vfs, &char) Fn_sqlite3_syscall_ptr
	xNextSystemCall fn (&Sqlite3_vfs, &char) &char
}

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

@select is used internally by V's ORM for processing SELECT queries

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

busy_timeout sets 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) !

create is used internally by V's ORM for processing table creation queries (DDL)

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

create_table issues a "create table if not exists" command to the db.
It creates the table named 'table_name', with columns generated from 'columns' array.
The default columns type will be TEXT.

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

delete is used internally by V's ORM for processing DELETE queries

fn (db DB) drop(table string) !

drop is used internally by V's ORM for processing table destroying queries (DDL)

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

error_message returns a proper V error, given an integer error code received from SQLite, and a query string

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

exec executes the query on the given db, and returns 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

exec_none executes a query, and returns the integer SQLite result code.
Use it, in case you don't expect any row results, but still want a result code.
e.g. for queries like these: INSERT INTO ... VALUES (...)

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

exec_one executes a query on the given db.
It returns either the first row from the result, if the query was successful, or an error.

fn (db &DB) get_affected_rows_count() int

get_affected_rows_count returns sqlite changes() meaning amount of rows affected by most recent sql query

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

insert is used internally by V's ORM for processing INSERT queries

fn (db &DB) journal_mode(journal_mode JournalMode)

journal_mode 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() int

last_id is used internally by V's ORM for post-processing INSERT queries

fn (db &DB) last_insert_rowid() i64

last_insert_rowid returns last inserted rowid https://www.sqlite.org/c3ref/last_insert_rowid.html

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

q_int returns a single integer value, from the first column of the result of executing query

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

q_string returns a single string value, from the first column of the result of executing query

fn (db &DB) str() string

str returns a text representation of the DB

fn (db &DB) synchronization_mode(sync_mode SyncMode)

synchronization_mode 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) !

update is used internally by V's ORM for processing UPDATE queries

struct Row {
pub mut:
	vals []string
}

struct Stmt {
	stmt &C.sqlite3_stmt = unsafe { nil }
	db   &DB = unsafe { nil }
}