Module db.sqlite stdlib

db.sqlite
Version:
0.3.3
License:
MIT
Dependencies from vmod:
0
Imports:
2
Imported by:
2
Repository:
OS-specific
Show selected OS-specific symbols.
Backend-specific
Show selected Backend-specific symbols.

Dependencies defined in v.mod

This section is empty.

Imports

Imported by

Overview

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)

Aliases

#type Sqlite3_file

type Sqlite3_file = C.sqlite3_file

#type Sqlite3_io_methods

type Sqlite3_io_methods = C.sqlite3_io_methods

#type Sqlite3_vfs

type Sqlite3_vfs = C.sqlite3_vfs

#fn (&Sqlite3_vfs) register_as_nondefault

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 (&Sqlite3_vfs) unregister

fn (mut v &Sqlite3_vfs) unregister() ?

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

Constants

#constant sqlite_ok

sqlite_ok                 = 0

#constant sqlite_error

sqlite_error              = 1

#constant sqlite_row

sqlite_row                = 100

#constant sqlite_done

sqlite_done               = 101

#constant sqlite_cantopen

sqlite_cantopen           = 14

#constant sqlite_ioerr_read

sqlite_ioerr_read         = 266

#constant sqlite_ioerr_short_read

sqlite_ioerr_short_read   = 522

#constant sqlite_ioerr_write

sqlite_ioerr_write        = 778

#constant sqlite_ioerr_fsync

sqlite_ioerr_fsync        = 1034

#constant sqlite_ioerr_fstat

sqlite_ioerr_fstat        = 1802

#constant sqlite_ioerr_delete

sqlite_ioerr_delete       = 2570

#constant sqlite_open_main_db

sqlite_open_main_db       = 0x00000100

#constant sqlite_open_temp_db

sqlite_open_temp_db       = 0x00000200

#constant sqlite_open_transient_db

sqlite_open_transient_db  = 0x00000400

#constant sqlite_open_main_journal

sqlite_open_main_journal  = 0x00000800

#constant sqlite_open_temp_journal

sqlite_open_temp_journal  = 0x00001000

#constant sqlite_open_subjournal

sqlite_open_subjournal    = 0x00002000

#constant sqlite_open_super_journal

sqlite_open_super_journal = 0x00004000

#constant sqlite_open_wal

sqlite_open_wal           = 0x00080000

Sum types

This section is empty.

Functions

#fn connect

fn connect(path string) !DB

connect Opens the connection with a database.

#fn connect_full

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

fn get_default_vfs() ?&Sqlite3_vfs

get_default_vfs Asks sqlite for default VFS instance

#fn get_vfs

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

fn is_error(code int) bool

is_error checks if code is an error code.

Structs

#struct Stmt

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

#fn (Stmt) sqlite_select_column

fn (stmt Stmt) sqlite_select_column(idx int, typ int) !orm.Primitive

Selects column in result and converts it to an orm.Primitive

#fn (&Stmt) bind_int

fn (stmt &Stmt) bind_int(idx int, v int) int

#fn (&Stmt) bind_i64

fn (stmt &Stmt) bind_i64(idx int, v i64) int

#fn (&Stmt) bind_f64

fn (stmt &Stmt) bind_f64(idx int, v f64) int

#fn (&Stmt) bind_text

fn (stmt &Stmt) bind_text(idx int, s string) int

#fn (&Stmt) get_int

fn (stmt &Stmt) get_int(idx int) int

#fn (&Stmt) get_i64

fn (stmt &Stmt) get_i64(idx int) i64

#fn (&Stmt) get_f64

fn (stmt &Stmt) get_f64(idx int) f64

#fn (&Stmt) get_text

fn (stmt &Stmt) get_text(idx int) string

#fn (&Stmt) get_count

fn (stmt &Stmt) get_count() int

#fn (&Stmt) step

fn (stmt &Stmt) step() int

#fn (&Stmt) orm_step

fn (stmt &Stmt) orm_step(query string) !

#fn (&Stmt) finalize

fn (stmt &Stmt) finalize()

#struct DB

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

#fn (DB) @select

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) insert

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

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

#fn (DB) update

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

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

#fn (DB) delete

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

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

#fn (DB) last_id

fn (db DB) last_id() int

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

#fn (DB) create

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) drop

fn (db DB) drop(table string) !

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

#fn (&DB) str

fn (db &DB) str() string

str returns a text representation of the DB

#fn (&DB) close

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) last_insert_rowid

fn (db &DB) last_insert_rowid() i64

#fn (&DB) get_affected_rows_count

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) q_int

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) q_string

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) exec

manualfree
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) exec_one

manualfree
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) error_message

manualfree
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) exec_none

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) create_table

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

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 (&DB) synchronization_mode

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

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) init_stmt

fn (db &DB) init_stmt(query string) (&C.sqlite3_stmt, int)

Only for V ORM

#fn (&DB) new_init_stmt

fn (db &DB) new_init_stmt(query string) !Stmt

#struct Row

pub struct Row {
pub mut:
	vals []string
}

#struct C.sqlite3_vfs

heap
pub 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
}

Interfaces

This section is empty.

Enums

#enum Result

pub 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 (Result) is_error

fn (r Result) is_error() bool

is_error checks if it is an error code.

#enum SyncMode

pub enum SyncMode {
	off
	normal
	full
}

#enum JournalMode

pub enum JournalMode {
	off
	delete
	truncate
	persist
	memory
}

#enum OpenModeFlag

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