sqlite3 package - github.com/mattn/go-sqlite3 - Go Packages (original) (raw)

Package sqlite3 provides interface to SQLite3 databases.

This works as a driver for database/sql.

Installation

go get github.com/mattn/go-sqlite3

Supported Types

Currently, go-sqlite3 supports the following data types.

+------------------------------+

go sqlite3
nil null
int integer
int64 integer
float64 float
bool integer
[]byte blob
string text
time.Time timestamp/datetime
+------------------------------+

SQLite3 Extension

You can write your own extension module for sqlite3. For example, below is an extension for a Regexp matcher operation.

#include <pcre.h> #include <string.h> #include <stdio.h> #include <sqlite3ext.h>

SQLITE_EXTENSION_INIT1 static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) { if (argc >= 2) { const char *target = (const char *)sqlite3_value_text(argv[1]); const char pattern = (const char )sqlite3_value_text(argv[0]); const char errstr = NULL; int erroff = 0; int vec[500]; int n, rc; pcre re = pcre_compile(pattern, 0, &errstr, &erroff, NULL); rc = pcre_exec(re, NULL, target, strlen(target), 0, 0, vec, 500); if (rc <= 0) { sqlite3_result_error(context, errstr, 0); return; } sqlite3_result_int(context, 1); } }

#ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines api) { SQLITE_EXTENSION_INIT2(api); return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8, (void)db, regexp_func, NULL, NULL); }

It needs to be built as a so/dll shared library. And you need to register the extension module like below.

sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{ Extensions: []string{ "sqlite3_mod_regexp", }, })

Then, you can use this extension.

rows, err := db.Query("select text from mytable where name regexp '^golang'")

Connection Hook

You can hook and inject your code when the connection is established by setting ConnectHook to get the SQLiteConn.

sql.Register("sqlite3_with_hook_example", &sqlite3.SQLiteDriver{ ConnectHook: func(conn *sqlite3.SQLiteConn) error { sqlite3conn = append(sqlite3conn, conn) return nil }, })

You can also use database/sql.Conn.Raw (Go >= 1.13):

conn, err := db.Conn(context.Background()) // if err != nil { ... } defer conn.Close() err = conn.Raw(func (driverConn any) error { sqliteConn := driverConn.(*sqlite3.SQLiteConn) // ... use sqliteConn }) // if err != nil { ... }

Go SQlite3 Extensions

If you want to register Go functions as SQLite extension functions you can make a custom driver by calling RegisterFunction from ConnectHook.

regex = func(re, s string) (bool, error) { return regexp.MatchString(re, s) } sql.Register("sqlite3_extended", &sqlite3.SQLiteDriver{ ConnectHook: func(conn *sqlite3.SQLiteConn) error { return conn.RegisterFunc("regexp", regex, true) }, })

You can then use the custom driver by passing its name to sql.Open.

var i int conn, err := sql.Open("sqlite3_extended", "./foo.db") if err != nil { panic(err) } err = db.QueryRow(SELECT regexp("foo.*", "seafood")).Scan(&i) if err != nil { panic(err) }

See the documentation of RegisterFunc for more details.

View Source

const ( SQLITE_FCNTL_LOCKSTATE = int(1) SQLITE_FCNTL_GET_LOCKPROXYFILE = int(2) SQLITE_FCNTL_SET_LOCKPROXYFILE = int(3) SQLITE_FCNTL_LAST_ERRNO = int(4) SQLITE_FCNTL_SIZE_HINT = int(5) SQLITE_FCNTL_CHUNK_SIZE = int(6) SQLITE_FCNTL_FILE_POINTER = int(7) SQLITE_FCNTL_SYNC_OMITTED = int(8) SQLITE_FCNTL_WIN32_AV_RETRY = int(9) SQLITE_FCNTL_PERSIST_WAL = int(10) SQLITE_FCNTL_OVERWRITE = int(11) SQLITE_FCNTL_VFSNAME = int(12) SQLITE_FCNTL_POWERSAFE_OVERWRITE = int(13) SQLITE_FCNTL_PRAGMA = int(14) SQLITE_FCNTL_BUSYHANDLER = int(15) SQLITE_FCNTL_TEMPFILENAME = int(16) SQLITE_FCNTL_MMAP_SIZE = int(18) SQLITE_FCNTL_TRACE = int(19) SQLITE_FCNTL_HAS_MOVED = int(20) SQLITE_FCNTL_SYNC = int(21) SQLITE_FCNTL_COMMIT_PHASETWO = int(22) SQLITE_FCNTL_WIN32_SET_HANDLE = int(23) SQLITE_FCNTL_WAL_BLOCK = int(24) SQLITE_FCNTL_ZIPVFS = int(25) SQLITE_FCNTL_RBU = int(26) SQLITE_FCNTL_VFS_POINTER = int(27) SQLITE_FCNTL_JOURNAL_POINTER = int(28) SQLITE_FCNTL_WIN32_GET_HANDLE = int(29) SQLITE_FCNTL_PDB = int(30) SQLITE_FCNTL_BEGIN_ATOMIC_WRITE = int(31) SQLITE_FCNTL_COMMIT_ATOMIC_WRITE = int(32) SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE = int(33) SQLITE_FCNTL_LOCK_TIMEOUT = int(34) SQLITE_FCNTL_DATA_VERSION = int(35) SQLITE_FCNTL_SIZE_LIMIT = int(36) SQLITE_FCNTL_CKPT_DONE = int(37) SQLITE_FCNTL_RESERVE_BYTES = int(38) SQLITE_FCNTL_CKPT_START = int(39) SQLITE_FCNTL_EXTERNAL_READER = int(40) SQLITE_FCNTL_CKSM_FILE = int(41) )

Standard File Control Opcodes See: https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html

Run-Time Limit Categories. See: http://www.sqlite.org/c3ref/c_limit_attached.html

View Source

const ( SQLITE_INTEGER = iota SQLITE_TEXT SQLITE_BLOB SQLITE_REAL SQLITE_NUMERIC SQLITE_TIME SQLITE_BOOL SQLITE_NULL )

ErrNoMask is mask code.

View Source

var ( ErrError = ErrNo(1)
ErrInternal = ErrNo(2)
ErrPerm = ErrNo(3)
ErrAbort = ErrNo(4)
ErrBusy = ErrNo(5)
ErrLocked = ErrNo(6)
ErrNomem = ErrNo(7)
ErrReadonly = ErrNo(8)
ErrInterrupt = ErrNo(9)
ErrIoErr = ErrNo(10) ErrCorrupt = ErrNo(11) ErrNotFound = ErrNo(12) ErrFull = ErrNo(13) ErrCantOpen = ErrNo(14) ErrProtocol = ErrNo(15) ErrEmpty = ErrNo(16) ErrSchema = ErrNo(17) ErrTooBig = ErrNo(18) ErrConstraint = ErrNo(19) ErrMismatch = ErrNo(20) ErrMisuse = ErrNo(21) ErrNoLFS = ErrNo(22) ErrAuth = ErrNo(23) ErrFormat = ErrNo(24) ErrRange = ErrNo(25) ErrNotADB = ErrNo(26) ErrNotice = ErrNo(27) ErrWarning = ErrNo(28) )

result codes from http://www.sqlite.org/c3ref/c_abort.html

View Source

var ( ErrIoErrRead = ErrIoErr.Extend(1) ErrIoErrShortRead = ErrIoErr.Extend(2) ErrIoErrWrite = ErrIoErr.Extend(3) ErrIoErrFsync = ErrIoErr.Extend(4) ErrIoErrDirFsync = ErrIoErr.Extend(5) ErrIoErrTruncate = ErrIoErr.Extend(6) ErrIoErrFstat = ErrIoErr.Extend(7) ErrIoErrUnlock = ErrIoErr.Extend(8) ErrIoErrRDlock = ErrIoErr.Extend(9) ErrIoErrDelete = ErrIoErr.Extend(10) ErrIoErrBlocked = ErrIoErr.Extend(11) ErrIoErrNoMem = ErrIoErr.Extend(12) ErrIoErrAccess = ErrIoErr.Extend(13) ErrIoErrCheckReservedLock = ErrIoErr.Extend(14) ErrIoErrLock = ErrIoErr.Extend(15) ErrIoErrClose = ErrIoErr.Extend(16) ErrIoErrDirClose = ErrIoErr.Extend(17) ErrIoErrSHMOpen = ErrIoErr.Extend(18) ErrIoErrSHMSize = ErrIoErr.Extend(19) ErrIoErrSHMLock = ErrIoErr.Extend(20) ErrIoErrSHMMap = ErrIoErr.Extend(21) ErrIoErrSeek = ErrIoErr.Extend(22) ErrIoErrDeleteNoent = ErrIoErr.Extend(23) ErrIoErrMMap = ErrIoErr.Extend(24) ErrIoErrGetTempPath = ErrIoErr.Extend(25) ErrIoErrConvPath = ErrIoErr.Extend(26) ErrLockedSharedCache = ErrLocked.Extend(1) ErrBusyRecovery = ErrBusy.Extend(1) ErrBusySnapshot = ErrBusy.Extend(2) ErrCantOpenNoTempDir = ErrCantOpen.Extend(1) ErrCantOpenIsDir = ErrCantOpen.Extend(2) ErrCantOpenFullPath = ErrCantOpen.Extend(3) ErrCantOpenConvPath = ErrCantOpen.Extend(4) ErrCorruptVTab = ErrCorrupt.Extend(1) ErrReadonlyRecovery = ErrReadonly.Extend(1) ErrReadonlyCantLock = ErrReadonly.Extend(2) ErrReadonlyRollback = ErrReadonly.Extend(3) ErrReadonlyDbMoved = ErrReadonly.Extend(4) ErrAbortRollback = ErrAbort.Extend(2) ErrConstraintCheck = ErrConstraint.Extend(1) ErrConstraintCommitHook = ErrConstraint.Extend(2) ErrConstraintForeignKey = ErrConstraint.Extend(3) ErrConstraintFunction = ErrConstraint.Extend(4) ErrConstraintNotNull = ErrConstraint.Extend(5) ErrConstraintPrimaryKey = ErrConstraint.Extend(6) ErrConstraintTrigger = ErrConstraint.Extend(7) ErrConstraintUnique = ErrConstraint.Extend(8) ErrConstraintVTab = ErrConstraint.Extend(9) ErrConstraintRowID = ErrConstraint.Extend(10) ErrNoticeRecoverWAL = ErrNotice.Extend(1) ErrNoticeRecoverRollback = ErrNotice.Extend(2) ErrWarningAutoIndex = ErrWarning.Extend(1) )

result codes from http://www.sqlite.org/c3ref/c_abort_rollback.html

View Source

var SQLiteTimestampFormats = []string{

"2006-01-02 15:04:05.999999999-07:00",
"2006-01-02T15:04:05.999999999-07:00",
"2006-01-02 15:04:05.999999999",
"2006-01-02T15:04:05.999999999",
"2006-01-02 15:04:05",
"2006-01-02T15:04:05",
"2006-01-02 15:04",
"2006-01-02T15:04",
"2006-01-02",

}

SQLiteTimestampFormats is timestamp formats understood by both this module and SQLite. The first format in the slice will be used when saving time values into the database. When parsing a string from a timestamp or datetime column, the formats are tried in order.

CryptEncoderSHA1 encodes a password with SHA1

CryptEncoderSHA256 encodes a password with SHA256

CryptEncoderSHA384 encodes a password with SHA384

CryptEncoderSHA512 encodes a password with SHA512

CryptEncoderSSHA1 encodes a password with SHA1 with the configured salt.

CryptEncoderSSHA256 encodes a password with SHA256 with the configured salt

CryptEncoderSSHA384 encodes a password with SHA384 with the configured salt

CryptEncoderSSHA512 encodes a password with SHA512 with the configured salt

Version returns SQLite library version information.

ErrNo inherit errno.

Error return error message from errno.

func (err ErrNo) Extend(by int) ErrNoExtended

Extend return extended errno.

ErrNoExtended is extended errno.

Error return error message that is extended code.

Error implement sqlite error code.

type SQLiteBackup struct {

}

SQLiteBackup implement interface of Backup.

func (b *SQLiteBackup) PageCount() int

PageCount return count of pages.

func (*SQLiteBackup) Remaining

func (b *SQLiteBackup) Remaining() int

Remaining return whether have the rest for backup.

Step to backs up for one step. Calls the underlying `sqlite3_backup_step` function. This function returns a boolean indicating if the backup is done and an error signalling any other error. Done is returned if the underlying C function returns SQLITE_DONE (Code 101)

type SQLiteConn struct {

}

SQLiteConn implements driver.Conn.

func (c *SQLiteConn) AuthEnabled() (exists bool)

AuthEnabled checks if the database is protected by user authentication

AuthUserAdd can be used (by an admin user only) to create a new user. When called on a no-authentication-required database, this routine converts the database into an authentication- required database, automatically makes the added user an administrator, and logs in the current connection as that user. The AuthUserAdd only works for the "main" database, not for any ATTACH-ed databases. Any call to AuthUserAdd by a non-admin user results in an error.

AuthUserChange can be used to change a users login credentials or admin privilege. Any user can change their own login credentials. Only an admin user can change another users login credentials or admin privilege setting. No user may change their own admin privilege setting.

AuthUserDelete can be used (by an admin user only) to delete a user. The currently logged-in user cannot be deleted, which guarantees that there is always an admin user and hence that the database cannot be converted into a no-authentication-required database.

Authenticate will perform an authentication of the provided username and password against the database.

If a database contains the SQLITE_USER table, then the call to Authenticate must be invoked with an appropriate username and password prior to enable read and write access to the database.

Return SQLITE_OK on success or SQLITE_ERROR if the username/password combination is incorrect or unknown.

If the SQLITE_USER table is not present in the database file, then this interface is a harmless no-op returnning SQLITE_OK.

func (c *SQLiteConn) AutoCommit() bool

AutoCommit return which currently auto commit or not.

Backup make backup from src to dest.

BeginTx implement ConnBeginTx.

ExecContext implement ExecerContext.

GetFilename returns the absolute path to the file containing the requested schema. When passed an empty string, it will instead use the database's default schema: "main". See: sqlite3_db_filename, https://www.sqlite.org/c3ref/db_filename.html

LoadExtension load the sqlite3 extension.

Prepare the query string. Return a new statement.

PrepareContext implement ConnPrepareContext.

Query implements Queryer.

QueryContext implement QueryerContext.

RegisterAggregator makes a Go type available as a SQLite aggregation function.

Because aggregation is incremental, it's implemented in Go with a type that has 2 methods: func Step(values) accumulates one row of data into the accumulator, and func Done() ret finalizes and returns the aggregate value. "values" and "ret" may be any type supported by RegisterFunc.

RegisterAggregator takes as implementation a constructor function that constructs an instance of the aggregator type each time an aggregation begins. The constructor must return a pointer to a type, or an interface that implements Step() and Done().

The constructor function and the Step/Done methods may optionally return an error in addition to their other return values.

See _example/go_custom_funcs for a detailed example.

RegisterAuthorizer sets the authorizer for connection.

The parameters to the callback are the operation (one of the constants SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), and 1 to 3 arguments, depending on operation. More details see:https://www.sqlite.org/c3ref/c_alter_table.html

RegisterCollation makes a Go function available as a collation.

cmp receives two UTF-8 strings, a and b. The result should be 0 if a==b, -1 if a < b, and +1 if a > b.

cmp must always return the same result given the same inputs. Additionally, it must have the following properties for all strings A, B and C: if A==B then B==A; if A==B and B==C then A==C; if AA; if A<B and B<C then A<C.

If cmp does not obey these constraints, sqlite3's behavior is undefined when the collation is used.

func (c *SQLiteConn) RegisterCommitHook(callback func() int)

RegisterCommitHook sets the commit hook for a connection.

If the callback returns non-zero the transaction will become a rollback.

If there is an existing commit hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

RegisterFunc makes a Go function available as a SQLite function.

The Go function can have arguments of the following types: any numeric type except complex, bool, []byte, string and any. any arguments are given the direct translation of the SQLite data type: int64 for INTEGER, float64 for FLOAT, []byte for BLOB, string for TEXT.

The function can additionally be variadic, as long as the type of the variadic argument is one of the above.

If pure is true. SQLite will assume that the function's return value depends only on its inputs, and make more aggressive optimizations in its queries.

See _example/go_custom_funcs for a detailed example.

func (c *SQLiteConn) RegisterPreUpdateHook(callback func(SQLitePreUpdateData))

RegisterPreUpdateHook sets the pre-update hook for a connection.

The callback is passed a SQLitePreUpdateData struct with the data for the update, as well as methods for fetching copies of impacted data.

If there is an existing preupdate hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

func (c *SQLiteConn) RegisterRollbackHook(callback func())

RegisterRollbackHook sets the rollback hook for a connection.

If there is an existing rollback hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

RegisterUpdateHook sets the update hook for a connection.

The parameters to the callback are the operation (one of the constants SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), the database name, the table name, and the rowid.

If there is an existing update hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

SetFileControlInt invokes the xFileControl method on a given database. The dbName is the name of the database. It will default to "main" if left blank. The op is one of the opcodes prefixed by "SQLITE_FCNTL_". The arg argument and return code are both opcode-specific. Please see the SQLite documentation.

This method is not thread-safe as the returned error code can be changed by another call if invoked concurrently.

Use SetFileControlInt64 instead if the argument for the opcode is documented as a pointer to a sqlite3_int64.

See: sqlite3_file_control, https://www.sqlite.org/c3ref/file_control.html

SetFileControlInt64 invokes the xFileControl method on a given database. The dbName is the name of the database. It will default to "main" if left blank. The op is one of the opcodes prefixed by "SQLITE_FCNTL_". The arg argument and return code are both opcode-specific. Please see the SQLite documentation.

This method is not thread-safe as the returned error code can be changed by another call if invoked concurrently.

Only use this method if the argument for the opcode is documented as a pointer to a sqlite3_int64.

See: sqlite3_file_control, https://www.sqlite.org/c3ref/file_control.html

SQLiteContext behave sqlite3_context

func (c *SQLiteContext) ResultBool(b bool)

ResultBool sets the result of an SQL function.

type SQLiteDriver struct { Extensions []string ConnectHook func(*SQLiteConn) error }

SQLiteDriver implements driver.Driver.

Open database and return a new connection.

A pragma can take either zero or one argument. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of:

1 yes true on 0 no false off

You can specify a DSN string using a URI as the filename.

test.db file:test.db?cache=shared&mode=memory :memory: file::memory:

mode Access mode of the database. https://www.sqlite.org/c3ref/open.html Values:

cache SQLite Shared-Cache Mode https://www.sqlite.org/sharedcache.html Values: - shared - private

immutable=Boolean The immutable parameter is a boolean query parameter that indicates that the database file is stored on read-only media. When immutable is set, SQLite assumes that the database file cannot be changed, even by a process with higher privilege, and so the database is opened read-only and all locking and change detection is disabled. Caution: Setting the immutable property on a database file that does in fact change can result in incorrect query results and/or SQLITE_CORRUPT errors.

go-sqlite3 adds the following query parameters to those used by SQLite:

_loc=XXX Specify location of time format. It's possible to specify "auto".

_mutex=XXX Specify mutex mode. XXX can be "no", "full".

_txlock=XXX Specify locking behavior for transactions. XXX can be "immediate", "deferred", "exclusive".

_auto_vacuum=X | _vacuum=X 0 | none - Auto Vacuum disabled 1 | full - Auto Vacuum FULL 2 | incremental - Auto Vacuum Incremental

_busy_timeout=XXX"| _timeout=XXX Specify value for sqlite3_busy_timeout.

_case_sensitive_like=Boolean | _cslike=Boolean https://www.sqlite.org/pragma.html#pragma_case_sensitive_like Default or disabled the LIKE operation is case-insensitive. When enabling this options behaviour of LIKE will become case-sensitive.

_defer_foreign_keys=Boolean | _defer_fk=Boolean Defer Foreign Keys until outermost transaction is committed.

_foreign_keys=Boolean | _fk=Boolean Enable or disable enforcement of foreign keys.

_ignore_check_constraints=Boolean This pragma enables or disables the enforcement of CHECK constraints. The default setting is off, meaning that CHECK constraints are enforced by default.

_journal_mode=MODE | _journal=MODE Set journal mode for the databases associated with the current connection. https://www.sqlite.org/pragma.html#pragma_journal_mode

_locking_mode=X | _locking=X Sets the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE. https://www.sqlite.org/pragma.html#pragma_locking_mode

_query_only=Boolean The query_only pragma prevents all changes to database files when enabled.

_recursive_triggers=Boolean | _rt=Boolean Enable or disable recursive triggers.

_secure_delete=Boolean|FAST When secure_delete is on, SQLite overwrites deleted content with zeros. https://www.sqlite.org/pragma.html#pragma_secure_delete

_synchronous=X | _sync=X Change the setting of the "synchronous" flag. https://www.sqlite.org/pragma.html#pragma_synchronous

_writable_schema=Boolean When this pragma is on, the SQLITE_MASTER tables in which database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file.

SQLitePreUpdateData represents all of the data available during a pre-update hook call.

type SQLiteResult struct {

}

SQLiteResult implements sql.Result.

LastInsertId return last inserted ID.

RowsAffected return how many rows affected.

type SQLiteRows struct {

}

SQLiteRows implements driver.Rows.

func (*SQLiteRows) ColumnTypeDatabaseTypeName added in v1.2.0

func (rc *SQLiteRows) ColumnTypeDatabaseTypeName(i int) string

ColumnTypeDatabaseTypeName implement RowsColumnTypeDatabaseTypeName.

func (*SQLiteRows) ColumnTypeNullable added in v1.2.0

func (rc *SQLiteRows) ColumnTypeNullable(i int) (nullable, ok bool)

ColumnTypeNullable implement RowsColumnTypeNullable.

func (*SQLiteRows) ColumnTypeScanType added in v1.2.0

ColumnTypeScanType implement RowsColumnTypeScanType.

func (*SQLiteRows) Columns

Columns return column names.

DeclTypes return column types.

Next move cursor to next. Attempts to honor context timeout from QueryContext call.

type SQLiteStmt struct {

}

SQLiteStmt implements driver.Stmt.

Exec execute the statement with arguments. Return result object.

ExecContext implement ExecerContext.

func (s *SQLiteStmt) NumInput() int

NumInput return a number of parameters.

Query the statement with arguments. Return records.

QueryContext implement QueryerContext.

SQLiteTx implements driver.Tx.