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.

fn new_connection_pool(config string, size int) !ConnectionPool

new_connection_pool creates a new connection pool with the given size and configuration.

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 Sqlite3TransactionLevel {
	deferred
	immediate
	exclusive
}

enum SyncMode {
	off
	normal
	full
}

struct C.sqlite3 {
}

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_stmt {
}

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 ConnectionPool {
mut:
	connections chan DB
	config      string
}

fn (mut pool ConnectionPool) acquire() !DB

acquire gets a connection from the pool

fn (mut pool ConnectionPool) release(conn DB)

release returns a connection back to the pool.

fn (mut pool ConnectionPool) close()

close closes all connections in the pool.

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

fn (mut db DB) begin(param Sqlite3TransactionParam) !

begin begins a new transaction.

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 (mut db DB) commit() !

commit commits the current transaction.

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

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

fn (mut 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

exec executes the query on the given db, and returns an array of all the results, or an error on failure

fn (db &DB) exec_map(query string) ![]map[string]string

exec_map executes the query on the given db, and returns an array of maps of strings, or an error on failure

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) exec_param(query string, param string) ![]Row

exec_param executes a query with one parameter provided as a ?, and returns either an error on failure, or the full result set on success

fn (db &DB) exec_param_many(query string, params []string) ![]Row

exec_param_many executes a query with parameters provided as ?, and returns either an error on failure, or the full result set on success

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. If the command fails to execute an error is returned .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, or an error on failure

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, or an error on failure

fn (mut db DB) rollback() !

rollback rollbacks the current transaction.

fn (mut db DB) rollback_to(savepoint string) !

rollback_to rollbacks to a specified savepoint.

fn (mut db DB) savepoint(savepoint string) !

savepoint create a new savepoint.

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) 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. If the command fails to execute an error is returned .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 Sqlite3TransactionParam {
	transaction_level Sqlite3TransactionLevel = .deferred
}

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