Documentation ¶
Overview ¶
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. database/sql doesn't provide a way to get native go-sqlite3 interfaces. So if you want, you need to set ConnectHook and get the SQLiteConn.
sql.Register("sqlite3_with_hook_example", &sqlite3.SQLiteDriver{ ConnectHook: func(conn *sqlite3.SQLiteConn) error { sqlite3conn = append(sqlite3conn, conn) return nil }, })
Go SQlite3 Extensions ¶
If you want to register Go functions as SQLite extension functions, call RegisterFunction from ConnectHook.
regex = func(re, s string) (bool, error) { return regexp.MatchString(re, s) } sql.Register("sqlite3_with_go_func", &sqlite3.SQLiteDriver{ ConnectHook: func(conn *sqlite3.SQLiteConn) error { return conn.RegisterFunc("regexp", regex, true) }, })
See the documentation of RegisterFunc for more details.
Index ¶
- Constants
- Variables
- func Version() (libVersion string, libVersionNumber int, sourceID string)
- type ErrNo
- type ErrNoExtended
- type Error
- type SQLiteBackup
- type SQLiteConn
- func (c *SQLiteConn) AutoCommit() bool
- func (c *SQLiteConn) Backup(dest string, conn *SQLiteConn, src string) (*SQLiteBackup, error)
- func (c *SQLiteConn) Begin() (driver.Tx, error)
- func (c *SQLiteConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)
- func (c *SQLiteConn) Close() error
- func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error)
- func (c *SQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)
- func (c *SQLiteConn) LoadExtension(lib string, entry string) error
- func (c *SQLiteConn) Ping(ctx context.Context) error
- func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error)
- func (c *SQLiteConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)
- func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error)
- func (c *SQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
- func (c *SQLiteConn) RegisterCommitHook(callback func() int)
- func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error
- func (c *SQLiteConn) RegisterRollbackHook(callback func())
- func (c *SQLiteConn) RegisterUpdateHook(callback func(int, string, string, int64))
- type SQLiteContext
- func (c *SQLiteContext) ResultBlob(b []byte)
- func (c *SQLiteContext) ResultBool(b bool)
- func (c *SQLiteContext) ResultDouble(d float64)
- func (c *SQLiteContext) ResultInt(i int)
- func (c *SQLiteContext) ResultInt64(i int64)
- func (c *SQLiteContext) ResultNull()
- func (c *SQLiteContext) ResultText(s string)
- func (c *SQLiteContext) ResultZeroblob(n int)
- type SQLiteDriver
- type SQLiteResult
- type SQLiteRows
- func (rc *SQLiteRows) Close() error
- func (rc *SQLiteRows) ColumnTypeDatabaseTypeName(i int) string
- func (rc *SQLiteRows) ColumnTypeNullable(i int) (nullable, ok bool)
- func (rc *SQLiteRows) ColumnTypeScanType(i int) reflect.Type
- func (rc *SQLiteRows) Columns() []string
- func (rc *SQLiteRows) DeclTypes() []string
- func (rc *SQLiteRows) Next(dest []driver.Value) error
- type SQLiteStmt
- func (s *SQLiteStmt) Close() error
- func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error)
- func (s *SQLiteStmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)
- func (s *SQLiteStmt) NumInput() int
- func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error)
- func (s *SQLiteStmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)
- type SQLiteTx
Constants ¶
const ( SQLITE_DELETE = C.SQLITE_DELETE SQLITE_INSERT = C.SQLITE_INSERT SQLITE_UPDATE = C.SQLITE_UPDATE )
const ErrNoMask C.int = 0xff
ErrNoMask is mask code.
Variables ¶
var ( ErrError = ErrNo(1) /* SQL error or missing database */ ErrInternal = ErrNo(2) /* Internal logic error in SQLite */ ErrPerm = ErrNo(3) /* Access permission denied */ ErrAbort = ErrNo(4) /* Callback routine requested an abort */ ErrBusy = ErrNo(5) /* The database file is locked */ ErrLocked = ErrNo(6) /* A table in the database is locked */ ErrNomem = ErrNo(7) /* A malloc() failed */ ErrReadonly = ErrNo(8) /* Attempt to write a readonly database */ ErrInterrupt = ErrNo(9) /* Operation terminated by sqlite3_interrupt() */ ErrIoErr = ErrNo(10) /* Some kind of disk I/O error occurred */ ErrCorrupt = ErrNo(11) /* The database disk image is malformed */ ErrNotFound = ErrNo(12) /* Unknown opcode in sqlite3_file_control() */ ErrFull = ErrNo(13) /* Insertion failed because database is full */ ErrCantOpen = ErrNo(14) /* Unable to open the database file */ ErrProtocol = ErrNo(15) /* Database lock protocol error */ ErrEmpty = ErrNo(16) /* Database is empty */ ErrSchema = ErrNo(17) /* The database schema changed */ ErrTooBig = ErrNo(18) /* String or BLOB exceeds size limit */ ErrConstraint = ErrNo(19) /* Abort due to constraint violation */ ErrMismatch = ErrNo(20) /* Data type mismatch */ ErrMisuse = ErrNo(21) /* Library used incorrectly */ ErrNoLFS = ErrNo(22) /* Uses OS features not supported on host */ ErrAuth = ErrNo(23) /* Authorization denied */ ErrFormat = ErrNo(24) /* Auxiliary database format error */ ErrRange = ErrNo(25) /* 2nd parameter to sqlite3_bind out of range */ ErrNotADB = ErrNo(26) /* File opened that is not a database file */ ErrNotice = ErrNo(27) /* Notifications from sqlite3_log() */ ErrWarning = ErrNo(28) /* Warnings from sqlite3_log() */ )
result codes from http://www.sqlite.org/c3ref/c_abort.html
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) 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
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.
Functions ¶
Types ¶
type ErrNoExtended ¶
type ErrNoExtended int
ErrNoExtended is extended errno.
func (ErrNoExtended) Error ¶
func (err ErrNoExtended) Error() string
Error return error message that is extended code.
type Error ¶
type Error struct { Code ErrNo /* The error code returned by SQLite */ ExtendedCode ErrNoExtended /* The extended error code returned by SQLite */ // contains filtered or unexported fields }
Error implement sqlite error code.
type SQLiteBackup ¶
type SQLiteBackup struct {
// contains filtered or unexported fields
}
SQLiteBackup implement interface of Backup.
func (*SQLiteBackup) PageCount ¶
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.
func (*SQLiteBackup) Step ¶
func (b *SQLiteBackup) Step(p int) (bool, error)
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 ¶
type SQLiteConn struct {
// contains filtered or unexported fields
}
SQLiteConn implement sql.Conn.
func (*SQLiteConn) AutoCommit ¶
func (c *SQLiteConn) AutoCommit() bool
AutoCommit return which currently auto commit or not.
func (*SQLiteConn) Backup ¶
func (c *SQLiteConn) Backup(dest string, conn *SQLiteConn, src string) (*SQLiteBackup, error)
Backup make backup from src to dest.
func (*SQLiteConn) ExecContext ¶
func (c *SQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)
ExecContext implement ExecerContext.
func (*SQLiteConn) LoadExtension ¶
func (c *SQLiteConn) LoadExtension(lib string, entry string) error
LoadExtension load the sqlite3 extension.
func (*SQLiteConn) Ping ¶
func (c *SQLiteConn) Ping(ctx context.Context) error
Ping implement Pinger.
func (*SQLiteConn) Prepare ¶
func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error)
Prepare the query string. Return a new statement.
func (*SQLiteConn) PrepareContext ¶
PrepareContext implement ConnPrepareContext.
func (*SQLiteConn) QueryContext ¶
func (c *SQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
QueryContext implement QueryerContext.
func (*SQLiteConn) RegisterCommitHook ¶
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.
func (*SQLiteConn) RegisterFunc ¶
func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error
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 interface{}. interface{} 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 (*SQLiteConn) RegisterRollbackHook ¶
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.
func (*SQLiteConn) RegisterUpdateHook ¶
func (c *SQLiteConn) RegisterUpdateHook(callback func(int, string, string, int64))
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.
type SQLiteContext ¶
type SQLiteContext C.sqlite3_context
SQLiteContext behave sqlite3_context
func (*SQLiteContext) ResultBlob ¶
func (c *SQLiteContext) ResultBlob(b []byte)
ResultBlob sets the result of an SQL function. See: sqlite3_result_blob, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultBool ¶
func (c *SQLiteContext) ResultBool(b bool)
ResultBool sets the result of an SQL function.
func (*SQLiteContext) ResultDouble ¶
func (c *SQLiteContext) ResultDouble(d float64)
ResultDouble sets the result of an SQL function. See: sqlite3_result_double, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultInt ¶
func (c *SQLiteContext) ResultInt(i int)
ResultInt sets the result of an SQL function. See: sqlite3_result_int, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultInt64 ¶
func (c *SQLiteContext) ResultInt64(i int64)
ResultInt64 sets the result of an SQL function. See: sqlite3_result_int64, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultNull ¶
func (c *SQLiteContext) ResultNull()
ResultNull sets the result of an SQL function. See: sqlite3_result_null, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultText ¶
func (c *SQLiteContext) ResultText(s string)
ResultText sets the result of an SQL function. See: sqlite3_result_text, http://sqlite.org/c3ref/result_blob.html
func (*SQLiteContext) ResultZeroblob ¶
func (c *SQLiteContext) ResultZeroblob(n int)
ResultZeroblob sets the result of an SQL function. See: sqlite3_result_zeroblob, http://sqlite.org/c3ref/result_blob.html
type SQLiteDriver ¶
type SQLiteDriver struct { Extensions []string ConnectHook func(*SQLiteConn) error }
SQLiteDriver implement sql.Driver.
func (*SQLiteDriver) Open ¶
func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error)
Open database and return a new connection. You can specify a DSN string using a URI as the filename.
test.db file:test.db?cache=shared&mode=memory :memory: file::memory:
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". _busy_timeout=XXX Specify value for sqlite3_busy_timeout. _txlock=XXX Specify locking behavior for transactions. XXX can be "immediate", "deferred", "exclusive". _foreign_keys=X Enable or disable enforcement of foreign keys. X can be 1 or 0. _recursive_triggers=X Enable or disable recursive triggers. X can be 1 or 0.
type SQLiteResult ¶
type SQLiteResult struct {
// contains filtered or unexported fields
}
SQLiteResult implement sql.Result.
func (*SQLiteResult) LastInsertId ¶
func (r *SQLiteResult) LastInsertId() (int64, error)
LastInsertId teturn last inserted ID.
func (*SQLiteResult) RowsAffected ¶
func (r *SQLiteResult) RowsAffected() (int64, error)
RowsAffected return how many rows affected.
type SQLiteRows ¶
type SQLiteRows struct {
// contains filtered or unexported fields
}
SQLiteRows implement sql.Rows.
func (*SQLiteRows) ColumnTypeDatabaseTypeName ¶
func (rc *SQLiteRows) ColumnTypeDatabaseTypeName(i int) string
ColumnTypeDatabaseTypeName implement RowsColumnTypeDatabaseTypeName.
func (*SQLiteRows) ColumnTypeNullable ¶
func (rc *SQLiteRows) ColumnTypeNullable(i int) (nullable, ok bool)
ColumnTypeNullable implement RowsColumnTypeNullable.
func (*SQLiteRows) ColumnTypeScanType ¶
func (rc *SQLiteRows) ColumnTypeScanType(i int) reflect.Type
ColumnTypeScanType implement RowsColumnTypeScanType.
func (*SQLiteRows) DeclTypes ¶
func (rc *SQLiteRows) DeclTypes() []string
DeclTypes return column types.
type SQLiteStmt ¶
type SQLiteStmt struct {
// contains filtered or unexported fields
}
SQLiteStmt implement sql.Stmt.
func (*SQLiteStmt) ExecContext ¶
func (s *SQLiteStmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)
ExecContext implement ExecerContext.
func (*SQLiteStmt) NumInput ¶
func (s *SQLiteStmt) NumInput() int
NumInput return a number of parameters.
func (*SQLiteStmt) QueryContext ¶
func (s *SQLiteStmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)
QueryContext implement QueryerContext.