gosqlite

package module
v1.0.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 15, 2024 License: Unlicense Imports: 23 Imported by: 0

README

go-sqlite

A Go driver for SQLite3. It covers all the basic + advanced functions.
Full documentation: TODO.

Feature Highlights

  • Reliable concurrent functionality
  • Built-in functions
    • DataTable (with built-in paging, and update(), export to CSV)
    • Turn AutoIncrement on/off
    • Copy tables to other databases
    • Rename/Drop tables
    • ...
  • Open() database wrappers (i.e. in-memory, V2,...)
  • Automatic optimization (e.g. shrinking database files).

Essentials

func CreateDatabase(dbFilePath string) error
func Open(dbFilePath string, pragma ...string) (*DB, error)
func OpenMemory(vfsName ...string) (*DB, error)
func openV2(dbFilePath string, flag C.int, vfsName string, pragma []string) (*DB, error) 
func OpenV2Exclusive(dbFilePath string, pragma ...string) (*DB, error)
func OpenV2Readonly(dbFilePath string, pragma ...string) (*DB, error)
func OpenV2FullOption(dbFilePath string, vfsName string, flags C.int, pragma ...string) (*DB, error)
func OpenV2(dbFilePath string, pragma ...string) (*DB, error)

func (rs *Rows) Columns() ([]string, error)
func (rs *Rows) Close() error
func (r *Result) Error() error
func (d *DB) Exec(query string, placeHolders ...any) Result
func (r *Result) LastInsertId() (int64, error)
func (rs *Rows) Next() bool
func (r *Result) RowsAffected() (int64, error)
func (rs *Rows) Scan(dest ...any) error
func (d *DB) Prepare(sqlx string, placeHolders []any) (
        Stmt,   /* A pointer to the prepared statement */
        string, /* (pzTail) End of parsed string (unused portion of zSql) */
        error)
func (d *DB) TxBegin() (string, error)
func (d *DB) TxRollback(txID string) error 
func (d *DB) TxCommit(txID string) error

A few things to note

Opening Databases

You can use any of the open() functions (above) to open a database.
To create a new database you must call CreateDatabase() first; as a database-file will not automatically be created by only calling an open() function.

There is no initialization via a database interface; i.e. open() will send the caller's request directory to the SQLite's C code; equivalently, Close() will directly close the database file handle.
Although no stadard interface is used, the most populare functions have been implemented (i.e. Scan(), Next(),... see above).

Asynchronous Operations

Asynchronous read operation is only implemented via GetDataTableAsync(). All other read operations are processed synchronously (FIFO).
Please, note that all write operations are processed synchronously as SQLite3 locks the database file for each write operation.

GetDataTableAsync(callback func(*DataTable), query string, placeHolders ...any) (*DataTable, error)
Backup
func BackupOnlineDB(
	onlineDB *DB, /* Pointer to an open database to back up */
	filePathBackupTo string, /* Full path of the file to back up to */
	millSecToSleepBeforeRepeating int, /* wait-time between copying pages */
	prgCallback func(xPagesCopied int, yTotalPages int, data string), /* Progress function to invoke */
	extraData string, /* extra data to send to callback */
	options ...string /*backup_raise_err_on_busy ir backup_raise_err_on_dblocked*/) error

You can take a backup of a database, while a database is online (has open file handles).
You have the option of waiting between pages or take a quick backup without waiting.
An additional parameter has been added to the callback func to pass extra data (e.g. JSON) rather than only pagesCpied and totalPages.

Execute
func (d *DB) Execute(sqlx string) (int64, error)

Execute() uses the sqlite3_exec() function in one go rather than prepare, step,...
It tends to be faster, but it does not take any prepared statements.
SQLite3 offers a callback (sqlite3_exec_callback()) to get the result-set for SQL satements executed via Execute().

You can use GetResultSet() and wait for the callback to gather all result rows:

func (d *DB) GetResultSet(sqlx string) QueryResult
SQL tail - unused portion of an SQL statement

You can add an unrelated text to the end of your SQL satement for passing additional information.
SQLite3 parses the useful part so the sql [text] and executes the SQL statement accordingly (without throwing error for the extra text).
For example, use can the following SQL statement with the Exec() func:

UPDATE MyTable SET Column_1 = 'Green Dalphin' WHERE MyTableID = 99; chicago branch
File Group

All databases are tracked and maintained in the background. Every time a database is opened, its file handle and description are added to a global list. All databases in the global list are monitored and optimized (i.e. vacuumed), when they are idle.
Opening databases can also be done via DBGrp.Get() as the following example:

fp := "/my_database.sqlite"
db, _err_ := gosqlite.DBGrp.Get(fp)
if err != nil {
    log.Fatal(err)
}

t, err := db[0].InMemory.CreateTable("CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);")
if err != nil {
    log.Fatal(err)
}
Repair Corrupted Files

There many be a chance to repair a corrupted database file, if only its header is missing and/or invalid.
You can use

func RepairSqlite3FileStub(bDBContent []byte) []byte

to repair a database file. RepairSqlite3FileStub() replaces the header of the database file with an empty stub:

const sqlite3_dbfile_emtpy_header = "53514c69746520666f726d6174203300100002020040202000017e1e00000009"

Usage Example

package main

import (
    "fmt"
    "log"
    "strings"
    "time"
    gosqlite "github.com/kambahr/go-sqlite"
)
func main() {

	// ------------- in memory -------------

	db, err := gosqlite.OpenMemory()
	if err != nil {
		log.Fatal("OpenMemory() ", strings.Repeat(".", 3), " failed ", err.Error())
	}
	defer db.Close()

	sqlx := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS my_city (
        ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        Name TEXT NOT NULL,
        LastVisited TEXT NULL);
        CREATE UNIQUE INDEX IF NOT EXISTS INX_my_city ON my_city (Name);
        insert into my_city
        select 1,'Chicago','%s' where not exists(select 1 from my_city where Name='Chicago'); 
    `, time.Now().Add((-12820)*time.Hour))

	_, err = db.Execute(sqlx)
	if err != nil {
		log.Println("Execute() => creat table:", err)
		return
	}
	fmt.Println("create-table", strings.Repeat(".", 22), "pass")

	sqlx = fmt.Sprintf(`select * from my_city`)
	rs := db.GetResultSet(sqlx)
	if rs.Err != nil {
		log.Fatal("GetResultSet()", strings.Repeat(".", 19), " failed ", err.Error())
	}

	if len(rs.ResultTable) > 0 {
		cityName := rs.ResultTable[0]["Name"].(string)
		lstVisted := rs.ResultTable[0]["LastVisited"].(string)
		lstVistedShortDT := strings.Split(lstVisted, ".")[0]
		lstVistedShortDT = fmt.Sprintf("%s %s", lstVistedShortDT, strings.Split(lstVisted, " ")[3])

		fmt.Println("GetResultSet()", strings.Repeat(".", 20), "pass")
		fmt.Println(" City LastVisited")
		fmt.Println("", cityName, lstVistedShortDT)
	} else {
		fmt.Println("GetResultSet()", "failed result-table is empty")
		return
	}

	// ------------- with file -------------

	dbFilePath := ".../db/guitar-is-the-song.sqlite"
	_, err = os.Stat(dbFilePath)
	if os.IsNotExist(err) {
		gosqlite.CreateDatabase(dbFilePath)
	}

	sqlx = `
	CREATE TABLE IF NOT EXISTS Logs (
		LogID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
		LogType TEXT NOT NULL,
		Content BLOB NOT NULL,
		DateTimeCreated TEXT NOT NULL);`
	dbx, err := gosqlite.DBGrp.Get(dbFilePath)
	_, err = dbx[0].Execute(sqlx)
	if err != nil {
		log.Fatal(err)
	}

	var rowsAffected int64
	var res gosqlite.Result
	for i := 0; i < 100; i++ {
		sqlx = `INSERT INTO Logs(LogType,Content,DateTimeCreated)VALUES(?,?,?)`
		res = dbx[0].Exec(sqlx, "BLOB", []byte("hello world"), time.Now().String())
		rowsAffectedOne, _ := res.RowsAffected()
		rowsAffected += rowsAffectedOne
		fmt.Printf("\rinserted %d", rowsAffected)
	}
	lastID, _ := res.LastInsertId()
	fmt.Println("\nrowsAffected:", rowsAffected, "LastInsertedID:", lastID)	

	// ------------- database description -------------

	desc := dbx[0].Describe()
	fmt.Println("db size:", desc.Size, "last modified:", desc.LastModified.String()[:19])

	fmt.Printf("--- tables (%d) ---\n", len(desc.Tables))
	for i := 0; i < len(desc.Tables); i++ {
		fmt.Printf("%s%s\n", strings.Repeat(" ", 3), desc.Tables[i].Name)
		for j := 0; j < len(desc.Tables[i].Columns); j++ {
			isPrimary := ""
			if desc.Tables[i].Columns[j].IsPrimaryKey {
				isPrimary = "Primary Key"
			}
			isNull := "NULL"
			if desc.Tables[i].Columns[j].NotNULL {
				isNull = "NOT NULL"
			}
			fmt.Printf("%s%s %s %s %s\n", strings.Repeat(" ", 6),
				desc.Tables[i].Columns[j].Name, isPrimary,
				desc.Tables[i].Columns[j].DataType, isNull)
		}
	}
}

Documentation

Index

Constants

View Source 
const (
	NON_QUERY  uint8 = 0
	DATA_TABLE uint8 = 1
)
View Source 
const (
	SQLITE_INTEGER = 1
	SQLITE_FLOAT   = 2
	SQLITE_BLOB    = 4
	SQLITE_NULL    = 5
	SQLITE_TEXT    = 3
	SQLITE3_TEXT   = 3
)
View Source 
const (
	SQLITE_OK         = 0   /* Successful result */
	SQLITE_ERROR      = 1   /* Generic error */
	SQLITE_INTERNAL   = 2   /* Internal logic error in SQLite */
	SQLITE_PERM       = 3   /* Access permission denied */
	SQLITE_ABORT      = 4   /* Callback routine requested an abort */
	SQLITE_BUSY       = 5   /* The database file is locked */
	SQLITE_LOCKED     = 6   /* A table in the database is locked */
	SQLITE_NOMEM      = 7   /* A malloc() failed */
	SQLITE_READONLY   = 8   /* Attempt to write a readonly database */
	SQLITE_INTERRUPT  = 9   /* Operation terminated by sqlite3_interrupt()*/
	SQLITE_IOERR      = 10  /* Some kind of disk I/O error occurred */
	SQLITE_CORRUPT    = 11  /* The database disk image is malformed */
	SQLITE_NOTFOUND   = 12  /* Unknown opcode in sqlite3_file_control() */
	SQLITE_FULL       = 13  /* Insertion failed because database is full */
	SQLITE_CANTOPEN   = 14  /* Unable to open the database file */
	SQLITE_PROTOCOL   = 15  /* Database lock protocol error */
	SQLITE_EMPTY      = 16  /* Internal use only */
	SQLITE_SCHEMA     = 17  /* The database schema changed */
	SQLITE_TOOBIG     = 18  /* String or BLOB exceeds size limit */
	SQLITE_CONSTRAINT = 19  /* Abort due to constraint violation */
	SQLITE_MISMATCH   = 20  /* Data type mismatch */
	SQLITE_MISUSE     = 21  /* Library used incorrectly */
	SQLITE_NOLFS      = 22  /* Uses OS features not supported on host */
	SQLITE_AUTH       = 23  /* Authorization denied */
	SQLITE_FORMAT     = 24  /* Not used */
	SQLITE_RANGE      = 25  /* 2nd parameter to sqlite3_bind out of range */
	SQLITE_NOTADB     = 26  /* File opened that is not a database file */
	SQLITE_NOTICE     = 27  /* Notifications from sqlite3_log() */
	SQLITE_WARNING    = 28  /* Warnings from sqlite3_log() */
	SQLITE_ROW        = 100 /* sqlite3_step() has another row ready */
	SQLITE_DONE       = 101 /* sqlite3_step() has finished executing */
)

error messages

View Source 
const (
	SQLITE_OPEN_READONLY      = 0x00000001 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_READWRITE     = 0x00000002 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_CREATE        = 0x00000004 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_DELETEONCLOSE = 0x00000008 /* VFS only */
	SQLITE_OPEN_EXCLUSIVE     = 0x00000010 /* VFS only */
	SQLITE_OPEN_AUTOPROXY     = 0x00000020 /* VFS only */
	SQLITE_OPEN_URI           = 0x00000040 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_MEMORY        = 0x00000080 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_MAIN_DB       = 0x00000100 /* VFS only */
	SQLITE_OPEN_TEMP_DB       = 0x00000200 /* VFS only */
	SQLITE_OPEN_TRANSIENT_DB  = 0x00000400 /* VFS only */
	SQLITE_OPEN_MAIN_JOURNAL  = 0x00000800 /* VFS only */
	SQLITE_OPEN_TEMP_JOURNAL  = 0x00001000 /* VFS only */
	SQLITE_OPEN_SUBJOURNAL    = 0x00002000 /* VFS only */
	SQLITE_OPEN_SUPER_JOURNAL = 0x00004000 /* VFS only */
	SQLITE_OPEN_NOMUTEX       = 0x00008000 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_FULLMUTEX     = 0x00010000 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_SHAREDCACHE   = 0x00020000 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_PRIVATECACHE  = 0x00040000 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_WAL           = 0x00080000 /* VFS only */
	SQLITE_OPEN_NOFOLLOW      = 0x01000000 /* Ok for sqlite3_open_v2() */
	SQLITE_OPEN_EXRESCODE     = 0x02000000 /* Extended result codes */
)

open flags

Variables

View Source 
var ExecSeqNo int
View Source 
var ExececuteNonQuerySeqNo int
View Source 
var ExececuteScalreSeqNo int
View Source 
var ExecuteSeqNo int
View Source 
var GetDataTableSeqNo int

maxConcurrentRequest is a global counter, of all connections (single and multi-threaded) for the DateDateTable(). It helps keep track of freqeuent calls, and also helps in minimizing null- pointer situations where releasing resources could go out-of-synce between sqlite3 and that of Go. The caller can also use this counter to keep track of its results when calls are made concurrently. The following is an example of how a caller can create frequent/ multiple threads. 

for i:=0; i < n; i++{
  go db.GetDataTable("<some query with a fairly large result-set")
}
View Source 
var GetResultSetSeqNo uint

GetResultSetSeqNo is incremented every time a request comes in for GetDataTable. It keep track of the order of the request. It is useful to track requests; it is also returned to the caller. This is sepcially helpful when the caller is using multiple threads where result are returned out-of-order (not by the order the request.

View Source 
var QuerySeqNo int

Functions

func AddEmptyHeaderToFileContent 

func AddEmptyHeaderToFileContent(bDBContent []byte) []byte

AddEmptyHeaderToFileContent adds the the sqlite3 file header to a db-file.

func BackupOnlineDB 

func BackupOnlineDB(
	onlineDB *DB,
	filePathBackupTo string,
	millSecToSleepBeforeRepeating int,
	prgCallback func(xPagesCopied int, yTotalPages int, data string),
	extraData string,
	options ...string) error

BackupOnlineDB takes a backup of an online database. It's done page-by-page. The progress is written to a callback. see: https://www.sqlite.org/backup.html. Note that filePathBackupTo must alrady exist; use CreateDatabase() to create an empty database, if file does not exist. millSecToSleepBeforeRepeating can be set to zero (with no delays), although the recommanded delay time is 250 milliseconds.

func ConvertStringToTime 

func ConvertStringToTime(dateTimeString string) (time.Time, error)

ConvertStringToTime converts string to time. It uses a custom layout to get the <date><time>+<milliseconds>+<zone offset>. The following is an example: 2023-03-21 11:11:03.608230911 +0100 BST => time.Time

func CreateDatabase 

func CreateDatabase(dbFilePath string) error

CreateDatabase creates a new database with a default user table.

func CreateGroup 

func CreateGroup(dirPath string) error

CreateGroup creates a new directory on the file system. Group is actually a directory in which db files reside in.

func DecryptDBFile 

func DecryptDBFile(encFilePath string, desFile string, pwdPhrs string) error

func DecryptLight 

func DecryptLight(data []byte, passphrase string) ([]byte, error)

func DeserializeToFile 

func DeserializeToFile(bDBContent []byte, dbFilePath string) error

DeserializeToFile creates an SQLite database file from a byte array.

func EncryptDBFile 

func EncryptDBFile(dbPath string, encFilePath string, pwdPhrs string) error

EncryptDBFile encrypts a database file.

func EncryptLight 

func EncryptLight(data []byte, passphrase string) ([]byte, error)

func GetErrText 

func GetErrText(errN int) string

func GetProcessID 

func GetProcessID() int

func GetSQLiteDataType 

func GetSQLiteDataType(fieldValue any) string

setColumnType sets the column type according to that of its value. see: see: https://www.sqlite.org/c3ref/column_decltype.html: "SQLite uses dynamic run-time typing. So just because a column is declared to contain a particular type does not mean that the data stored in that column is of the declared type. SQLite is strongly typed, but the typing is dynamic not static. Type is associated with individual values, not with the containers used to hold those values."

also see: see: https://www.sqlite.org/datatype3.html

func GetTableNameFromSQLQuery 

func GetTableNameFromSQLQuery(sqlQuery string) string

--------- other public ----------

func IsFileSQLiteFormat 

func IsFileSQLiteFormat(bDBContent []byte) bool

IsFileSQLiteFormat reads the first bytes of an sqlite3 database file...if those bytes begin with "SQLite format 3", then the db file was created by the sqlite3_open() function. Note that the database may still work without this string.

func RemoveFileHeaderFromFileContent 

func RemoveFileHeaderFromFileContent(bDBContent []byte) []byte

RemoveFileHeaderFromFileContent removes a db-file's file header; making it unreadable for most SQLite tools.

func RepairSqlite3FileStub 

func RepairSqlite3FileStub(bDBContent []byte) []byte

RepairSqlite3FileStub replaces the sqlite3 stub of a db-file.

func SaveInMemoryDBToFile 

func SaveInMemoryDBToFile(pInMemory *DB, schema string, dbFilePath string) error

SaveInMemoryDBToFile backs up a schema into a database file. See: sqlite3_dbfile_stub.

func Serialize 

func Serialize(db *DB, schema string) ([]byte, error)

Serialize saves an opened database as array of []bytes.

Types

type AttachedDB 

type AttachedDB struct {
	Name       string `json:"Name"`
	DBName     string `json:"DBName"`
	DBFilePath string `json:"DBFilePath"`
}

type CCmdParam 

type CCmdParam struct {
	CollationList      string
	CheckPointFullSync string
	DataVersion        string
	ListDatabase       string
	PageCount          string
	Encoding           string
	ModuleList         string
	TableList          string
	PageSize           string
	SchemaVersion      string
}

func CmdParam 

func CmdParam() CCmdParam

type CollectionInfo 

type CollectionInfo struct {
	RecordCount  int
	TotalPages   int
	PageSize     int
	PageNo       int
	PositionFrom int
	PositionTo   int
}

type Column 

type Column struct {
	Name              string `json:"Name"`
	DataType          string `json:"DataType"`
	IsPrimaryKey      bool   `json:"IsPrimaryKey"`
	IsAutoIncrement   bool   `json:"IsAutoIncrement"`
	IsGeneratedAlways bool   `json:"IsGeneratedAlways"`
	NotNULL           bool   `json:"NotNULL"`
	Ordinal           int    `json:"Ordinal"`
	DefaultValue      any    `json:"DefaultValue"`
}

type Connection 

type Connection struct {
	TimeConnected time.Time
	ID            string
	SeqNo         uint
}

type ConnectionPool 

type ConnectionPool struct {
	MaxOpenConns          int           // 0 means unlimited
	MaxIdleConns          int           // 0 means unlimited
	MaxLifetime           time.Duration // maximum amount of time a connection may be reused
	MaxIdleTime           time.Duration // maximum amount of time a connection may be idle before being closed
	MaxConcurrentRequests int
}

type DB 

type DB struct {
	// DBHwnd is a pointer to the sqlite3 structure.
	DBHwnd *C.sqlite3

	// not unique; e.g. name can be used to get a list of
	// databse by name. See IDBGroup.Get()
	Name        string
	JournalMode string
	UniqueName  string // hash of the db file path
	ConnPool    ConnectionPool
	Closed      bool
	ConnString  string

	// InMemory are objects created in-memory regarless of how
	// a database was opened. e.g. a database opened via a vile
	// can create in-memory tables.
	// See PRAGMA temp_store on https://www.sqlite.org/pragma.html
	InMemory InMemoryObjects

	BackupProgress func(xPagesCopied int, yTotalPages int, data string)

	Connections []Connection

	TimeOpened time.Time
	// contains filtered or unexported fields
}

func DeserializeToInMemoryDB 

func DeserializeToInMemoryDB(bDBContent []byte, schema string) (*DB, error)

DeserializeToInMemoryDB opens an in-memory database from []bytes of a database file. This is usually array of bytes read from a database on file.

func Open 

func Open(dbFilePath string, pragma ...string) (*DB, error)

Open opens an SQLite database and returns a pointer to DB. pragma is a list of PRAGMA command to be applied before the database is ready for operation, typically journal-mode PRAGMAs. See https://sqlite.org/pragma.html#pragma_journal_mode.

func OpenMemory 

func OpenMemory(vfsName ...string) (*DB, error)

func OpenV2 

func OpenV2(dbFilePath string, pragma ...string) (*DB, error)

Open opens an SQLite database and returns a pointer to DB. pragma is a list of PRAGMA command to be applied before the database is ready for operation, typically journal-mode PRAGMAs.

func OpenV2Exclusive 

func OpenV2Exclusive(dbFilePath string, pragma ...string) (*DB, error)

func OpenV2FullOption 

func OpenV2FullOption(dbFilePath string, vfsName string, flags C.int, pragma ...string) (*DB, error)

func OpenV2Readonly 

func OpenV2Readonly(dbFilePath string, pragma ...string) (*DB, error)

Open opens a read-only SQLite database and returns a pointer to DB. pragma is a list of PRAGMA commands to be applied before the database is ready for operation, typically journal-mode PRAGMAs.

func (*DB) AttachDB 

func (d *DB) AttachDB(dbFilePathToAttach string, attchName string) (bool, error)

func (*DB) Base 

func (d *DB) Base() *DB

func (*DB) Busy 

func (d *DB) Busy() bool

func (*DB) CloneDB 

func (d *DB) CloneDB(destPath string) error

CloneDB creates a copy of the database using the backup utility.

func (*DB) Close 

func (d *DB) Close() error

func (*DB) CopyDatabase 

func (d *DB) CopyDatabase(dbFileToCopyTo string) error

func (*DB) CopyTableToDatabase 

func (d *DB) CopyTableToDatabase(tbleNameToCopy string, targetDBFilePath string,
	dropIfExists bool, append bool, recreateAutoIncrementCol bool) (string, error)

CopyTableToDatabase copies a table from the current database to another. If append is set to true, all rows will be appended to the target table, otherwise a new table is created; if the table exists it will be re-name (as <table name>_n). If recreateAutoIncrementCol is set to true the auto-increment column will be re-create so that the integer numbers are reset form 1 to n.

func (*DB) DataTableToJSON 

func (d *DB) DataTableToJSON(dt DataTable) string

func (*DB) DeleteJournalfiles 

func (d *DB) DeleteJournalfiles(dbFilePath string)

func (*DB) Describe 

func (d *DB) Describe() DBStat

func (*DB) DetachDB 

func (d *DB) DetachDB(attachName string) error

func (*DB) DropTable 

func (d *DB) DropTable(tableName string, vaccumAfter ...bool) error

func (*DB) DropView 

func (d *DB) DropView(viewName string) error

func (*DB) EncryptTable 

func (d *DB) EncryptTable(tName string) error

func (*DB) Exec 

func (d *DB) Exec(query string, placeHolders ...any) Result

func (*DB) Execute 

func (d *DB) Execute(sqlx string) (int64, error)

func (*DB) ExecuteNonQuery 

func (d *DB) ExecuteNonQuery(query string, placeHolders ...any) (int64, error)

func (*DB) ExecuteScalare 

func (d *DB) ExecuteScalare(query string, placeHolders ...any) (any, error)

Execute executes an sql statement and returns a single value the error. see: https://sqlite.org/cintro.html.

func (*DB) ExportDataTableToCSV 

func (d *DB) ExportDataTableToCSV(dt DataTable, separator ...string) []byte

func (*DB) FilePath 

func (d *DB) FilePath() string

func (*DB) Get 

func (d *DB) Get(cmdParam string) ([]map[string]any, error)

Get fetches a desriptive PRAGMA result from the database The cmdParam must already be listed. Use CmdParam() to select a cmd param. Example: 

Get(gosqlite.CmdParam().CollationList)

func (*DB) GetAttachedDatabases 

func (d *DB) GetAttachedDatabases() []AttachedDB

func (*DB) GetBlob 

func (d *DB) GetBlob(tblName string, colName string, rowid int) ([]byte, error)

func (*DB) GetDBHandleUsers 

func (d *DB) GetDBHandleUsers() ([]string, error)

GetDBHandleUsers [linux only] gets the path of programs that are using the database.

func (*DB) GetDataTable 

func (d *DB) GetDataTable(query string, placeHolders ...any) (*DataTable, error)

func (*DB) GetDataTableAsync 

func (d *DB) GetDataTableAsync(callback func(*DataTable), query string, placeHolders ...any) (*DataTable, error)

func (*DB) GetInsertSQLFromDataTableRow 

func (d *DB) GetInsertSQLFromDataTableRow(dtRow map[string]interface{}, tableName string) (string, error)

func (*DB) GetPage 

func (d *DB) GetPage(pageSize int, pageNo int, tableName string, filter string, orderBy string, sortOrder string) (DataTable, error)

func (*DB) GetPageOffset 

func (d *DB) GetPageOffset(recordCount int, pageSize int, pageNo int) (int, int, int)

GetPageOffset returns totalPages, offset, pageNo

func (*DB) GetPagingInfo 

func (d *DB) GetPagingInfo(pageSize int, pageNo int, tableName string, filter string) (CollectionInfo, error)

GetPagingInfo returns the collectionInfo on a table

func (*DB) GetResultSet 

func (d *DB) GetResultSet(sqlx string) QueryResult

func (*DB) IsColumnAutoIncrement 

func (d *DB) IsColumnAutoIncrement(colName string, tableName string) (bool, error)

func (*DB) IsIdle 

func (d *DB) IsIdle() bool

IsIdle reutrns true if a database is "busy" with onr (or more( of the following operations Exec(), GetDataTable(), Execute(), ExececuteNonQuery(), or ExececuteScalre()

func (*DB) IsInMemory 

func (d *DB) IsInMemory() bool

func (*DB) Ping 

func (d *DB) Ping() int

func (*DB) Prepare 

func (d *DB) Prepare(
	sqlx string,
	placeHolders []any) (
	Stmt,
	string,
	error)

Prepare binds sql statement values to its place holders. See: https://www.sqlite.org/lang_expr.html#varparam, 

and https://www.sqlite.org/c3ref/bind_blob.html

The return is: 

--a pointer to the prepared statement
--unused portion of the sql statement (at the end)
--error

func (*DB) Query 

func (d *DB) Query(query string, placeHolders ...any) (*Rows, error)

func (*DB) ReOpen 

func (d *DB) ReOpen()

func (*DB) RemoveAllAttachedDBs 

func (d *DB) RemoveAllAttachedDBs() error

func (*DB) RemoveAttachedDB 

func (d *DB) RemoveAttachedDB(attachName string) error

func (*DB) RenameTable 

func (d *DB) RenameTable(tableName string, newtableName string) error

func (*DB) SaveSchemaToFile 

func (d *DB) SaveSchemaToFile(schema string, dbFilePath string) error

SaveSchemaToFile saves a database schema to file. The database can in-memory or attached to a file.

func (*DB) Shrink 

func (d *DB) Shrink() error

func (*DB) ShrinkMemory 

func (d *DB) ShrinkMemory() error

func (*DB) TableExists 

func (d *DB) TableExists(tableName string) bool

func (*DB) TurnOffAutoIncrement 

func (d *DB) TurnOffAutoIncrement(tableName string) error

func (*DB) TurnOnAutoIncrement 

func (d *DB) TurnOnAutoIncrement(tableName string, colName string, reoderAutoInrecValues ...bool) error

func (*DB) TxBegin 

func (d *DB) TxBegin() (string, error)

func (*DB) TxCommit 

func (d *DB) TxCommit(txID string) error

func (*DB) TxRollback 

func (d *DB) TxRollback(txID string) error

func (*DB) UpdateBlob 

func (d *DB) UpdateBlob(data []byte, tblName string, colName string, rowid int) Result

UpdateBlob writes an array of []byte to a BLOB column. Note that BLOBs can also be updated via an sql statement as the following exmaple: 

data := []byte("Hello World")
sqlSmt := fmt.Sprintf("UPDATE <table name> SET <column name> = x'%x'", data)

also see: Update() in DataTable.

func (*DB) Vacuum 

func (d *DB) Vacuum(dbVacuumInto ...string) error

type DBGroup 

type DBGroup struct {
	Verbose       bool
	OpenDatabases []*DB
}

DBGroup keeps track of databases opened by the client app.

func (*DBGroup) Add 

func (g *DBGroup) Add(db *DB)

func (*DBGroup) Base 

func (m *DBGroup) Base() *DBGroup

Base exposes all properties of DBGroup to the caller (methods and variables).

func (*DBGroup) Count 

func (m *DBGroup) Count() int

func (*DBGroup) Get 

func (m *DBGroup) Get(srchTxt string) ([]*DB, error)

func (*DBGroup) Ping 

func (m *DBGroup) Ping(db *DB) error

func (*DBGroup) Remove 

func (g *DBGroup) Remove(db *DB)

Remove closes the database and removes it from the array of OpenDatabase.

type DBStat 

type DBStat struct {
	Size            string // i.e. 1,350KB, 2,535MB, 1.45GB
	Name            string
	FilePath        string
	FilePathHidden  bool
	DateTimeCreated time.Time
	LastModified    time.Time
	Tables          []Table
	Indexes         []Index
	Views           []View
	Triggers        []Trigger
}

type DSQLiteDataType 

type DSQLiteDataType struct {
	NULL    string
	TEXT    string
	INTEGER string
	REAL    string
	BLOB    string
	VARIANT string
}

func SQLiteDataType 

func SQLiteDataType() DSQLiteDataType

type DataRow 

type DataRow []map[string]any

type DataTable 

type DataTable struct {
	Name    string   `json:"name"`
	Columns []Column `json:"column"`
	Rows    DataRow  `json:"data-row"`

	// SQLTail is the end of parsed string
	// (unused portion of the sql statement)
	SQLTail string `json:"sql-tail"`

	// SeqNo is a global counter for the GetDataTable().
	// It can be used to track/verify multi-treaded calls.
	SeqNo int `json:"seq-no"`

	// QueryID  string
	TimeStarted time.Time `json:"time-started"`
	TimeEnded   time.Time `json:"time-ended"`

	// Err is the error recieved at the time
	// the data is fetched.
	Err error `json:"err"`

	CollInfo CollectionInfo
	// contains filtered or unexported fields
}

func (*DataTable) DescribeDatabase 

func (dt *DataTable) DescribeDatabase() DBStat

func (*DataTable) ExportToCSV 

func (dt *DataTable) ExportToCSV(destFilePath string) error

func (*DataTable) Update 

func (dt *DataTable) Update() Result

Update updates rows/cols of a table as they appear in the DataTable. The table and its rows must exist in a single table and not resulted from multiple joins to other tables and/or include aliases.

type IDB 

type IDB interface {

	// <!--
	// Base exposes all properties of DB to the caller
	// (methods and variables).
	Base() *DB

	// <!--
	// Busy lets the caller know that the database is "busy" with something
	// essential and it cannot operations such as "write" or "vacuum."
	// A database is "busy" if it is not Idle, which means that one (or more)
	// of the following operations is running: Exec(), GetDataTable(),
	// Execute(), ExececuteNonQuery(), or ExececuteScalre()
	Busy() bool

	// <!--
	// AttachDB attaches a database to the current open database.
	// Note that the same database can attached sevarl times using differnt
	// attach names. Note that the default max number of attached databases
	// is 10, but it can be increased to 125, see https://www.sqlite.org/limits.html
	//
	// ** dbFilePathToAttach ... full local path to the database to attach to
	// ** attchName ............ must start with a character
	AttachDB(dbFilePathToAttach string, attchName string) (bool, error)

	// <!-- CloneDB copies a database using SQLite's backup feature.
	CloneDB(destPath string) error

	// <!--
	// Close drops all connections to the database
	// and releases its file handle.
	Close() error

	// <!--
	// CopyDatabase copies a database file into antoher.
	// The destination file must not exist.
	CopyDatabase(dbFilePath string) error

	// <!--
	// CopyTableToDatabase copies a table from the current database to another.
	// If append is set to true, all rows will be appended to the target table,
	// otherwise a new table is created; if the table exists it will be re-name
	// (as <table name>_n). If recreateAutoIncrementCol is set to true the
	// auto-increment column will be re-create so that the integer numbers are
	// reset form 1 to n.
	CopyTableToDatabase(tbleNameToCopy string, targetDBFilePath string, dropIfExists bool, append bool, CopyTableToDatabase bool) (string, error)

	// <!--
	// Describe returns a full description of a database.
	Describe() DBStat

	DetachDB(attchName string) error

	// <!--
	// DropView closes the current connection first,
	// opens it with exclusive connection, and then reopens
	// the regular connection.
	DropView(viewName string) error

	// <!--
	// DropTable closes the current connection first,
	// opens it with exclusive connection, and then reopens
	// the regular connection.
	DropTable(tableName string, vaccumAfter ...bool) error

	// <!--
	// GetAttachedDatabases returns a list of database attached to the current database.
	GetAttachedDatabases() []AttachedDB

	// <!--
	// Exec executes an query and returns a Result type.
	Exec(query string, placeHolders ...any) Result

	// <!--
	// Execute executes an sql statement and returns the error.
	// see: https://sqlite.org/cintro.html.
	// It does not process prepared statements; only exists satements
	// as they are.
	Execute(sqlx string) (int64, error)

	// <!--
	// ExecuteScalare returns one value of type any (interface{}).
	ExecuteScalare(query string, placeHolders ...any) (any, error)

	// <!--
	// ExecuteNonQuery executes a query and returns rowsAffected.
	ExecuteNonQuery(query string, placeHolders ...any) (int64, error)

	// <!--
	// FilePath returns the value of filePath. It is
	// read-only as it is required for attaching datbases.
	FilePath() string

	// <!--
	// GetDataTable returns query result in forms of rows/columns.
	// Due to locking the entire func, only one instance of
	// this func will be processed at a time. Since the
	// max-concurrent connections will always be 1, checking on concurrent
	// connections would not be necessary; i.e.
	//
	//	if d.ConnPool.MaxOpenConns > 0 && GetDataTableSeqNo >= d.ConnPool.MaxOpenConns {
	//		wrkRes.Err = fmt.Errorf("open connections exceeded the maximum of %d", d.ConnPool.MaxOpenConns)
	//		log.Fatal(wrkRes.Err)
	//		return &wrkRes, wrkRes.Err
	//	}
	//
	// *** on FIFO ***
	// Sequential calls to GetDataTable() are returned as FIFO,
	// however, if the caller creates multiple threads (i.e.
	//
	//	 go func() {
	//		   <calls to GetDataTable()>
	//	 }()
	//
	// then GetDataTable() returns the result as soons as it
	// receives it from sqlite3.
	// For example, with the following query:
	//
	// "select _rowid_ as RowID from my-table limit 1 offset 0"
	//
	// results feteched sequentially [ordered]
	// (i.e getMyTable() waits for result):
	//
	//	First
	//	Second
	//	Thrid
	//
	// results feteched non-sequentially [un-ordered]
	// (i.e go getMyTable() does not wait for result):
	//
	//	Second
	//	Thrid
	//	First
	//
	// ** Frequent calls and releasing unsafe poninters **
	// The process for the entire func is locked so that releasing
	// of [unsafe] pointers between sqlite3 and Go is not interrupted.
	// If this is not done, frequent calls could crash the process.
	// As the following example:
	//
	//	func myFunc (db *gosqlite.DB)
	//	/*risk on out-of-sync pointer on rapid calls*/
	//
	//	where as the following would be safe:
	//	func myFunc (db gosqlite.DB) // passed by-value
	GetDataTable(query string, placeHolders ...any) (*DataTable, error)

	// <!--
	// GetPage returns a DataTable of a table; using LIMIT and OFFSET to
	// query on a spacific range, hence PageSize and Page Number. The result-set
	// can be filtered by a parital SQL statement and also have sort directions.
	GetPage(pageSize int, pageNo int, tableName string, filter string, sortBy string, sortOrder string) (DataTable, error)

	// <!--
	// GetResultSet places the caller's request on a queue, and waits for it
	// to be done. If the caller calls this func via a thread, then the result
	// is sent back as soon as the query finishes. Although, call to this
	// package may come in concurrently, SQLite itself maintians the FIFO order.
	GetResultSet(sqlx string) QueryResult

	// <!--
	// IsInMemory returns true if the database was created in-memeory.
	IsInMemory() bool

	// IsColumnAutoIncrement returns whether a column is set as
	// auto-increment. It will return error if it does not exist.
	IsColumnAutoIncrement(colName string, tableName string) (bool, error)

	// Ping sends the state of the database to the caller.
	// 0 => open, 1 => closed.
	Ping() int

	// <!--
	// Query executes an sql statement and returns a pointer to
	// Rows for the caller to iterate.
	Query(query string, placeHolders ...any) (*Rows, error)

	// <!--
	// RemoveAttachedDB removes currenlty attached database from the currnt open database.
	//
	// ** attachName string ... must start with a character
	RemoveAttachedDB(attachName string) error

	// <!--
	// RemoveAllAttachedDBs removes all attached databases.
	RemoveAllAttachedDBs() error

	RenameTable(tableName string, newtableName string) error

	TableExists(tableName string) bool

	// <!--
	// TurnOffAutoIncrement removes the auto-increment attribute of a primary-key
	// column defined as auto-increment.
	TurnOffAutoIncrement(tableName string) error

	// <!--.
	// TurnOnAutoIncrement will turn a column into an auto-increment (primary-key) column
	TurnOnAutoIncrement(tableName string, colName string, reoderAutoInrecValues ...bool) error

	// <!--
	// TxBegin returns a txID for the transaction to be
	// committed or rolled back to.
	TxBegin() (string, error)

	// <!--
	// TxRollback restores a savepoint to a txID.
	TxRollback(txID string) error

	// <!--
	// TxCommit commits a transaction from a savepoint.
	TxCommit(txID string) error

	// <!--
	// Vacuum shrinks a database.
	Vacuum(dbVacuumInto ...string) error
}

IDB is an instance for a single database.

type IDBGroup 

type IDBGroup interface {
	Add(db *DB)
	Remove(db *DB)
	Ping(db *DB) error
	Base() *DBGroup

	// GetDB pointes to open databases based
	// on their unique names, partial or complete filePath
	// or the friendly name. If partial, there has to be only one match
	// found, otherwise it will return an error.
	// The return []*DB is a list of open databases.
	// Note:
	//   ** a database on can multiple connection, so  []*DB can
	//      have duplicate databases.
	Get(s string) ([]*DB, error)

	// Count returns count of open databases
	Count() int
	// contains filtered or unexported methods
}
var DBGrp IDBGroup = &DBGroup{}

type IDataTable 

type IDataTable interface {

	// NewRow returns an empty row.
	NewRow() DataRow

	// InsertRow inserts row into table.
	InsertRow(row DataRow) error

	// Clears the table; it does not remove data
	// from database; only from the cache.
	Clear()

	DescribeDatabase() DBStat

	// Update upates rows according to rows/columns
	// in the table.
	Update() error

	ExportToCSV(destFilePath string) error
}

type IResult 

type IResult interface {
	// LastInsertId returns the last max-value of the built-in _rowid_.
	LastInsertId() (int64, error)

	// RowsAffected returns the number of rows affected by an
	// update, insert, or delete.
	RowsAffected() (int64, error)

	Error() error
}

A Result summarizes an executed SQL command.

type IRows 

type IRows interface {
	Columns() ([]string, error)
	Close() error
	// Next gets the next step of a query.
	Next() bool
	Scan(dest ...any) error
}

type InMemoryObjects 

type InMemoryObjects struct {
	Tables []Table
	// contains filtered or unexported fields
}

InMemoryObjects holds a pointer to a database and array of tables. Note that a database opened via file can create in-memory tables.

func (*InMemoryObjects) CreateTable 

func (i *InMemoryObjects) CreateTable(sqlx string) (Table, error)

CreateTable creates a table in-memory, regardless of how the its database was opened; as a database opened via a file can still create in-memory tables.

type Index 

type Index struct {
	Name      string
	CreateSQL string
	Unique    bool
	RootPage  uint
	Columns   []string
}

type JMode 

type JMode struct {
	Delete   string
	Truncate string
	Off      string
	Persist  string
	Memory   string
	Wal      string
}

func JounalMode 

func JounalMode() JMode

type QueryResult 

type QueryResult struct {
	// SeqNo is the order of the request recieved
	// by the database instance.
	SeqNo uint

	// ResultTable is table of query results.
	ResultTable []map[string]any

	Columns []Column

	// QueryID is a unique id assiged to
	// the query. TODO: this id can be used to track a query
	// transaction logs; once implemented.
	QueryID string

	// Err is any error recevied from the
	// exec func.
	Err error

	// TimeStarted is the time the query started.
	TimeStarted time.Time

	// TimeEnded is the time query completed.
	TimeEnded time.Time
}

QueryResult is used by the Exec callbck to return query-result to the caller.

type Result 

type Result struct {
	// contains filtered or unexported fields
}

func (*Result) Error 

func (r *Result) Error() error

func (*Result) LastInsertId 

func (r *Result) LastInsertId() (int64, error)

func (*Result) RowsAffected 

func (r *Result) RowsAffected() (int64, error)

type Rows 

type Rows struct {
	// contains filtered or unexported fields
}

func (*Rows) Close 

func (rs *Rows) Close() error

func (*Rows) Columns 

func (rs *Rows) Columns() ([]string, error)

func (*Rows) Next 

func (rs *Rows) Next() bool

func (*Rows) Scan 

func (rs *Rows) Scan(dest ...any) error

type SQLiteVersion 

type SQLiteVersion struct {
	Version       string
	VersionNumber uint
	SourceID      string
}

func GetVersion 

func GetVersion() SQLiteVersion

type Stmt 

type Stmt struct {
	Query string
	// contains filtered or unexported fields
}

type Table 

type Table struct {
	Name      string
	Columns   []Column
	CreateSQL string
	RootPage  uint
}

type Trigger 

type Trigger struct {
	Name      string
	RootPage  uint
	CreateSQL string
}

type Value 

type Value any

type View 

type View struct {
	Name      string
	RootPage  uint
	CreateSQL string
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.