Documentation ¶
Overview ¶
Package sqlite provides a Go interface to SQLite 3.
The semantics of this package are deliberately close to the SQLite3 C API, so it is helpful to be familiar with http://www.sqlite.org/c3ref/intro.html.
An SQLite connection is represented by a *sqlite.Conn. Connections cannot be used concurrently. A typical Go program will create a pool of connections (using Open to create a *sqlite.Pool) so goroutines can borrow a connection while they need to talk to the database.
This package assumes SQLite will be used concurrently by the process through several connections, so the build options for SQLite enable multi-threading and the shared cache: https://www.sqlite.org/sharedcache.html
The implementation automatically handles shared cache locking, see the documentation on Stmt.Step for details.
The optional SQLite3 compiled in are: FTS5, RTree, JSON1, Session
This is not a database/sql driver.
Statement Caching ¶
Statements are prepared with the Prepare and PrepareTransient methods. When using Prepare, statements are keyed inside a connection by the original query string used to create them. This means long-running high-performance code paths can write:
stmt, err := conn.Prepare("SELECT ...")
After all the connections in a pool have been warmed up by passing through one of these Prepare calls, subsequent calls are simply a map lookup that returns an existing statement.
Streaming Blobs ¶
The sqlite package supports the SQLite incremental I/O interface for streaming blob data into and out of the the database without loading the entire blob into a single []byte. (This is important when working either with very large blobs, or more commonly, a large number of moderate-sized blobs concurrently.)
To write a blob, first use an INSERT statement to set the size of the blob and assign a rowid:
"INSERT INTO blobs (myblob) VALUES (?);"
Use BindZeroBlob or SetZeroBlob to set the size of myblob. Then you can open the blob with:
b, err := conn.OpenBlob("", "blobs", "myblob", conn.LastInsertRowID(), true)
Deadlines and Cancellation ¶
Every connection can have a done channel associated with it using the SetInterrupt method. This is typically the channel returned by a context.Context Done method.
For example, a timeout can be associated with a connection session:
ctx := context.WithTimeout(context.Background(), 100*time.Millisecond) conn.SetInterrupt(ctx.Done())
As database connections are long-lived, the SetInterrupt method can be called multiple times to reset the associated lifetime.
When using pools, the shorthand for associating a context with a connection is:
conn := dbpool.Get(ctx.Done()) if conn == nil { // ... handle error } defer dbpool.Put(c)
Transactions ¶
SQLite transactions have to be managed manually with this package by directly calling BEGIN / COMMIT / ROLLBACK or SAVEPOINT / RELEASE/ ROLLBACK. The sqliteutil has a Savepoint function that helps automate this.
A typical HTTP Handler ¶
Using a Pool to execute SQL in a concurrent HTTP handler.
var dbpool *sqlite.Pool func main() { var err error dbpool, err = sqlite.Open("file:memory:?mode=memory", 0, 10) if err != nil { log.Fatal(err) } http.Handle("/", handler) log.Fatal(http.ListenAndServe(":8080", nil)) } func handle(w http.ResponseWriter, r *http.Request) { conn := dbpool.Get(r.Context().Done()) if conn == nil { return } defer dbpool.Put(conn) stmt := conn.Prep("SELECT foo FROM footable WHERE id = $id;") stmt.SetText("$id", "_user_id_") for { if hasRow, err := stmt.Step(); err != nil { // ... handle error } else if !hasRow { break } foo := stmt.GetText("foo") // ... use foo } }
For helper functions that make some kinds of statements easier to write see the sqliteutil package.
Index ¶
- Constants
- Variables
- func ChangesetConcat(w io.Writer, r1, r2 io.Reader) error
- func ChangesetInvert(w io.Writer, r io.Reader) error
- type Blob
- func (blob *Blob) Close() error
- func (blob *Blob) Read(p []byte) (n int, err error)
- func (blob *Blob) ReadAt(p []byte, off int64) (n int, err error)
- func (blob *Blob) Seek(offset int64, whence int) (int64, error)
- func (blob *Blob) Size() int64
- func (blob *Blob) Write(p []byte) (n int, err error)
- func (blob *Blob) WriteAt(p []byte, off int64) (n int, err error)
- type Changegroup
- type ChangesetIter
- func (iter ChangesetIter) Conflict(col int) (v Value, err error)
- func (iter ChangesetIter) FKConflicts() (int, error)
- func (iter ChangesetIter) Finalize() error
- func (iter ChangesetIter) New(col int) (v Value, err error)
- func (iter ChangesetIter) Next() (rowReturned bool, err error)
- func (iter ChangesetIter) Old(col int) (v Value, err error)
- func (iter ChangesetIter) Op() (table string, numCols int, opType OpType, indirect bool, err error)
- func (iter ChangesetIter) PK() ([]bool, error)
- type ColumnType
- type ConflictAction
- type ConflictType
- type Conn
- func (conn *Conn) Changes() int
- func (conn *Conn) ChangesetApply(r io.Reader, filterFn func(tableName string) bool, ...) error
- func (conn *Conn) Close() error
- func (conn *Conn) CreateFunction(name string, deterministic bool, numArgs int, ...) error
- func (conn *Conn) CreateSession(db string) (*Session, error)
- func (conn *Conn) EnableLoadExtension(on bool) error
- func (conn *Conn) LastInsertRowID() int64
- func (conn *Conn) LoadExtension(ext, entry string) error
- func (conn *Conn) OpenBlob(dbn, table, column string, row int64, write bool) (*Blob, error)
- func (conn *Conn) Prep(query string) *Stmt
- func (conn *Conn) Prepare(query string) (*Stmt, error)
- func (conn *Conn) PrepareTransient(query string) (stmt *Stmt, trailingBytes int, err error)
- func (conn *Conn) SetBusyTimeout(d time.Duration)
- func (conn *Conn) SetInterrupt(doneCh <-chan struct{}) (oldDoneCh <-chan struct{})
- type Context
- func (ctx Context) ResultError(err error)
- func (ctx Context) ResultFloat(v float64)
- func (ctx Context) ResultInt(v int)
- func (ctx Context) ResultInt64(v int64)
- func (ctx Context) ResultNull()
- func (ctx Context) ResultText(v string)
- func (ctx Context) ResultValue(v Value)
- func (ctx Context) ResultZeroBlob(n int64)
- func (ctx Context) SetUserData(data interface{})
- func (ctx Context) UserData() interface{}
- type Error
- type ErrorCode
- type OpType
- type OpenFlags
- type Pool
- type Session
- type Stmt
- func (stmt *Stmt) BindBool(param int, value bool)
- func (stmt *Stmt) BindBytes(param int, value []byte)
- func (stmt *Stmt) BindFloat(param int, value float64)
- func (stmt *Stmt) BindInt64(param int, value int64)
- func (stmt *Stmt) BindNull(param int)
- func (stmt *Stmt) BindParamCount() int
- func (stmt *Stmt) BindText(param int, value string)
- func (stmt *Stmt) BindZeroBlob(param int, len int64)
- func (stmt *Stmt) ClearBindings() error
- func (stmt *Stmt) ColumnBytes(col int, buf []byte) int
- func (stmt *Stmt) ColumnCount() int
- func (stmt *Stmt) ColumnDatabaseName(col int) string
- func (stmt *Stmt) ColumnFloat(col int) float64
- func (stmt *Stmt) ColumnInt(col int) int
- func (stmt *Stmt) ColumnInt32(col int) int32
- func (stmt *Stmt) ColumnInt64(col int) int64
- func (stmt *Stmt) ColumnLen(col int) int
- func (stmt *Stmt) ColumnName(col int) string
- func (stmt *Stmt) ColumnReader(col int) *bytes.Reader
- func (stmt *Stmt) ColumnTableName(col int) string
- func (stmt *Stmt) ColumnText(col int) string
- func (stmt *Stmt) ColumnType(col int) ColumnType
- func (stmt *Stmt) DataCount() int
- func (stmt *Stmt) Finalize() error
- func (stmt *Stmt) GetBytes(colName string, buf []byte) int
- func (stmt *Stmt) GetFloat(colName string) float64
- func (stmt *Stmt) GetInt64(colName string) int64
- func (stmt *Stmt) GetLen(colName string) int
- func (stmt *Stmt) GetReader(colName string) *bytes.Reader
- func (stmt *Stmt) GetText(colName string) string
- func (stmt *Stmt) Reset() error
- func (stmt *Stmt) SetBool(param string, value bool)
- func (stmt *Stmt) SetBytes(param string, value []byte)
- func (stmt *Stmt) SetFloat(param string, value float64)
- func (stmt *Stmt) SetInt64(param string, value int64)
- func (stmt *Stmt) SetNull(param string)
- func (stmt *Stmt) SetText(param string, value string)
- func (stmt *Stmt) SetZeroBlob(param string, len int64)
- func (stmt *Stmt) Step() (rowReturned bool, err error)
- type Value
Constants ¶
const ( SQLITE_OK = ErrorCode(C.SQLITE_OK) // do not use in Error SQLITE_ERROR = ErrorCode(C.SQLITE_ERROR) SQLITE_INTERNAL = ErrorCode(C.SQLITE_INTERNAL) SQLITE_PERM = ErrorCode(C.SQLITE_PERM) SQLITE_ABORT = ErrorCode(C.SQLITE_ABORT) SQLITE_BUSY = ErrorCode(C.SQLITE_BUSY) SQLITE_LOCKED = ErrorCode(C.SQLITE_LOCKED) SQLITE_NOMEM = ErrorCode(C.SQLITE_NOMEM) SQLITE_READONLY = ErrorCode(C.SQLITE_READONLY) SQLITE_INTERRUPT = ErrorCode(C.SQLITE_INTERRUPT) SQLITE_IOERR = ErrorCode(C.SQLITE_IOERR) SQLITE_CORRUPT = ErrorCode(C.SQLITE_CORRUPT) SQLITE_NOTFOUND = ErrorCode(C.SQLITE_NOTFOUND) SQLITE_FULL = ErrorCode(C.SQLITE_FULL) SQLITE_CANTOPEN = ErrorCode(C.SQLITE_CANTOPEN) SQLITE_PROTOCOL = ErrorCode(C.SQLITE_PROTOCOL) SQLITE_EMPTY = ErrorCode(C.SQLITE_EMPTY) SQLITE_SCHEMA = ErrorCode(C.SQLITE_SCHEMA) SQLITE_TOOBIG = ErrorCode(C.SQLITE_TOOBIG) SQLITE_CONSTRAINT = ErrorCode(C.SQLITE_CONSTRAINT) SQLITE_MISMATCH = ErrorCode(C.SQLITE_MISMATCH) SQLITE_MISUSE = ErrorCode(C.SQLITE_MISUSE) SQLITE_NOLFS = ErrorCode(C.SQLITE_NOLFS) SQLITE_AUTH = ErrorCode(C.SQLITE_AUTH) SQLITE_FORMAT = ErrorCode(C.SQLITE_FORMAT) SQLITE_RANGE = ErrorCode(C.SQLITE_RANGE) SQLITE_NOTADB = ErrorCode(C.SQLITE_NOTADB) SQLITE_NOTICE = ErrorCode(C.SQLITE_NOTICE) SQLITE_WARNING = ErrorCode(C.SQLITE_WARNING) SQLITE_ROW = ErrorCode(C.SQLITE_ROW) // do not use in Error SQLITE_DONE = ErrorCode(C.SQLITE_DONE) // do not use in Error SQLITE_ERROR_MISSING_COLLSEQ = ErrorCode(C.SQLITE_ERROR_MISSING_COLLSEQ) SQLITE_ERROR_RETRY = ErrorCode(C.SQLITE_ERROR_RETRY) SQLITE_IOERR_READ = ErrorCode(C.SQLITE_IOERR_READ) SQLITE_IOERR_SHORT_READ = ErrorCode(C.SQLITE_IOERR_SHORT_READ) SQLITE_IOERR_WRITE = ErrorCode(C.SQLITE_IOERR_WRITE) SQLITE_IOERR_FSYNC = ErrorCode(C.SQLITE_IOERR_FSYNC) SQLITE_IOERR_DIR_FSYNC = ErrorCode(C.SQLITE_IOERR_DIR_FSYNC) SQLITE_IOERR_TRUNCATE = ErrorCode(C.SQLITE_IOERR_TRUNCATE) SQLITE_IOERR_FSTAT = ErrorCode(C.SQLITE_IOERR_FSTAT) SQLITE_IOERR_UNLOCK = ErrorCode(C.SQLITE_IOERR_UNLOCK) SQLITE_IOERR_RDLOCK = ErrorCode(C.SQLITE_IOERR_RDLOCK) SQLITE_IOERR_DELETE = ErrorCode(C.SQLITE_IOERR_DELETE) SQLITE_IOERR_BLOCKED = ErrorCode(C.SQLITE_IOERR_BLOCKED) SQLITE_IOERR_NOMEM = ErrorCode(C.SQLITE_IOERR_NOMEM) SQLITE_IOERR_ACCESS = ErrorCode(C.SQLITE_IOERR_ACCESS) SQLITE_IOERR_CHECKRESERVEDLOCK = ErrorCode(C.SQLITE_IOERR_CHECKRESERVEDLOCK) SQLITE_IOERR_LOCK = ErrorCode(C.SQLITE_IOERR_LOCK) SQLITE_IOERR_CLOSE = ErrorCode(C.SQLITE_IOERR_CLOSE) SQLITE_IOERR_DIR_CLOSE = ErrorCode(C.SQLITE_IOERR_DIR_CLOSE) SQLITE_IOERR_SHMOPEN = ErrorCode(C.SQLITE_IOERR_SHMOPEN) SQLITE_IOERR_SHMSIZE = ErrorCode(C.SQLITE_IOERR_SHMSIZE) SQLITE_IOERR_SHMLOCK = ErrorCode(C.SQLITE_IOERR_SHMLOCK) SQLITE_IOERR_SHMMAP = ErrorCode(C.SQLITE_IOERR_SHMMAP) SQLITE_IOERR_SEEK = ErrorCode(C.SQLITE_IOERR_SEEK) SQLITE_IOERR_DELETE_NOENT = ErrorCode(C.SQLITE_IOERR_DELETE_NOENT) SQLITE_IOERR_MMAP = ErrorCode(C.SQLITE_IOERR_MMAP) SQLITE_IOERR_GETTEMPPATH = ErrorCode(C.SQLITE_IOERR_GETTEMPPATH) SQLITE_IOERR_CONVPATH = ErrorCode(C.SQLITE_IOERR_CONVPATH) SQLITE_IOERR_VNODE = ErrorCode(C.SQLITE_IOERR_VNODE) SQLITE_IOERR_AUTH = ErrorCode(C.SQLITE_IOERR_AUTH) SQLITE_IOERR_BEGIN_ATOMIC = ErrorCode(C.SQLITE_IOERR_BEGIN_ATOMIC) SQLITE_IOERR_COMMIT_ATOMIC = ErrorCode(C.SQLITE_IOERR_COMMIT_ATOMIC) SQLITE_IOERR_ROLLBACK_ATOMIC = ErrorCode(C.SQLITE_IOERR_ROLLBACK_ATOMIC) SQLITE_LOCKED_SHAREDCACHE = ErrorCode(C.SQLITE_LOCKED_SHAREDCACHE) SQLITE_BUSY_RECOVERY = ErrorCode(C.SQLITE_BUSY_RECOVERY) SQLITE_BUSY_SNAPSHOT = ErrorCode(C.SQLITE_BUSY_SNAPSHOT) SQLITE_CANTOPEN_NOTEMPDIR = ErrorCode(C.SQLITE_CANTOPEN_NOTEMPDIR) SQLITE_CANTOPEN_ISDIR = ErrorCode(C.SQLITE_CANTOPEN_ISDIR) SQLITE_CANTOPEN_FULLPATH = ErrorCode(C.SQLITE_CANTOPEN_FULLPATH) SQLITE_CANTOPEN_CONVPATH = ErrorCode(C.SQLITE_CANTOPEN_CONVPATH) SQLITE_CORRUPT_VTAB = ErrorCode(C.SQLITE_CORRUPT_VTAB) SQLITE_READONLY_RECOVERY = ErrorCode(C.SQLITE_READONLY_RECOVERY) SQLITE_READONLY_CANTLOCK = ErrorCode(C.SQLITE_READONLY_CANTLOCK) SQLITE_READONLY_ROLLBACK = ErrorCode(C.SQLITE_READONLY_ROLLBACK) SQLITE_READONLY_DBMOVED = ErrorCode(C.SQLITE_READONLY_DBMOVED) SQLITE_READONLY_CANTINIT = ErrorCode(C.SQLITE_READONLY_CANTINIT) SQLITE_READONLY_DIRECTORY = ErrorCode(C.SQLITE_READONLY_DIRECTORY) SQLITE_ABORT_ROLLBACK = ErrorCode(C.SQLITE_ABORT_ROLLBACK) SQLITE_CONSTRAINT_CHECK = ErrorCode(C.SQLITE_CONSTRAINT_CHECK) SQLITE_CONSTRAINT_COMMITHOOK = ErrorCode(C.SQLITE_CONSTRAINT_COMMITHOOK) SQLITE_CONSTRAINT_FOREIGNKEY = ErrorCode(C.SQLITE_CONSTRAINT_FOREIGNKEY) SQLITE_CONSTRAINT_FUNCTION = ErrorCode(C.SQLITE_CONSTRAINT_FUNCTION) SQLITE_CONSTRAINT_NOTNULL = ErrorCode(C.SQLITE_CONSTRAINT_NOTNULL) SQLITE_CONSTRAINT_PRIMARYKEY = ErrorCode(C.SQLITE_CONSTRAINT_PRIMARYKEY) SQLITE_CONSTRAINT_TRIGGER = ErrorCode(C.SQLITE_CONSTRAINT_TRIGGER) SQLITE_CONSTRAINT_UNIQUE = ErrorCode(C.SQLITE_CONSTRAINT_UNIQUE) SQLITE_CONSTRAINT_VTAB = ErrorCode(C.SQLITE_CONSTRAINT_VTAB) SQLITE_CONSTRAINT_ROWID = ErrorCode(C.SQLITE_CONSTRAINT_ROWID) SQLITE_NOTICE_RECOVER_WAL = ErrorCode(C.SQLITE_NOTICE_RECOVER_WAL) SQLITE_NOTICE_RECOVER_ROLLBACK = ErrorCode(C.SQLITE_NOTICE_RECOVER_ROLLBACK) SQLITE_WARNING_AUTOINDEX = ErrorCode(C.SQLITE_WARNING_AUTOINDEX) SQLITE_AUTH_USER = ErrorCode(C.SQLITE_AUTH_USER) )
const ( SQLITE_INSERT = OpType(C.SQLITE_INSERT) SQLITE_DELETE = OpType(C.SQLITE_DELETE) SQLITE_UPDATE = OpType(C.SQLITE_UPDATE) )
const ( SQLITE_CHANGESET_DATA = ConflictType(C.SQLITE_CHANGESET_DATA) SQLITE_CHANGESET_NOTFOUND = ConflictType(C.SQLITE_CHANGESET_NOTFOUND) SQLITE_CHANGESET_CONFLICT = ConflictType(C.SQLITE_CHANGESET_CONFLICT) SQLITE_CHANGESET_CONSTRAINT = ConflictType(C.SQLITE_CHANGESET_CONSTRAINT) SQLITE_CHANGESET_FOREIGN_KEY = ConflictType(C.SQLITE_CHANGESET_FOREIGN_KEY) )
const ( SQLITE_CHANGESET_OMIT = ConflictAction(C.SQLITE_CHANGESET_OMIT) SQLITE_CHANGESET_ABORT = ConflictAction(C.SQLITE_CHANGESET_ABORT) SQLITE_CHANGESET_REPLACE = ConflictAction(C.SQLITE_CHANGESET_REPLACE) )
const ( SQLITE_OPEN_READONLY = OpenFlags(C.SQLITE_OPEN_READONLY) SQLITE_OPEN_READWRITE = OpenFlags(C.SQLITE_OPEN_READWRITE) SQLITE_OPEN_CREATE = OpenFlags(C.SQLITE_OPEN_CREATE) SQLITE_OPEN_URI = OpenFlags(C.SQLITE_OPEN_URI) SQLITE_OPEN_MEMORY = OpenFlags(C.SQLITE_OPEN_MEMORY) SQLITE_OPEN_MAIN_DB = OpenFlags(C.SQLITE_OPEN_MAIN_DB) SQLITE_OPEN_TEMP_DB = OpenFlags(C.SQLITE_OPEN_TEMP_DB) SQLITE_OPEN_TRANSIENT_DB = OpenFlags(C.SQLITE_OPEN_TRANSIENT_DB) SQLITE_OPEN_MAIN_JOURNAL = OpenFlags(C.SQLITE_OPEN_MAIN_JOURNAL) SQLITE_OPEN_TEMP_JOURNAL = OpenFlags(C.SQLITE_OPEN_TEMP_JOURNAL) SQLITE_OPEN_SUBJOURNAL = OpenFlags(C.SQLITE_OPEN_SUBJOURNAL) SQLITE_OPEN_MASTER_JOURNAL = OpenFlags(C.SQLITE_OPEN_MASTER_JOURNAL) SQLITE_OPEN_NOMUTEX = OpenFlags(C.SQLITE_OPEN_NOMUTEX) SQLITE_OPEN_FULLMUTEX = OpenFlags(C.SQLITE_OPEN_FULLMUTEX) SQLITE_OPEN_SHAREDCACHE = OpenFlags(C.SQLITE_OPEN_SHAREDCACHE) SQLITE_OPEN_PRIVATECACHE = OpenFlags(C.SQLITE_OPEN_PRIVATECACHE) SQLITE_OPEN_WAL = OpenFlags(C.SQLITE_OPEN_WAL) )
const ( SQLITE_INTEGER = ColumnType(C.SQLITE_INTEGER) SQLITE_FLOAT = ColumnType(C.SQLITE_FLOAT) SQLITE_TEXT = ColumnType(C.SQLITE3_TEXT) SQLITE_BLOB = ColumnType(C.SQLITE_BLOB) SQLITE_NULL = ColumnType(C.SQLITE_NULL) )
const (
SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION = C.int(C.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION)
)
Variables ¶
var Logger func(code ErrorCode, msg []byte)
Logger is written to by SQLite. The Logger must be set before any connection is opened. The msg slice is only valid for the duration of the call.
Functions ¶
func ChangesetConcat ¶
ChangesetConcat concatenates two changesets.
func ChangesetInvert ¶
ChangesetInvert inverts a changeset.
Types ¶
type Blob ¶
type Blob struct { io.ReadWriteSeeker io.ReaderAt io.WriterAt io.Closer // contains filtered or unexported fields }
Blob provides streaming access to SQLite blobs.
type Changegroup ¶
type Changegroup struct {
// contains filtered or unexported fields
}
func NewChangegroup ¶
func NewChangegroup() (*Changegroup, error)
func (Changegroup) Delete ¶
func (cg Changegroup) Delete()
Delete deletes a Changegroup.
https://www.sqlite.org/session/sqlite3changegroup_delete.html
func (Changegroup) Output ¶
func (cg Changegroup) Output(w io.Writer) (n int, err error)
https://www.sqlite.org/session/sqlite3changegroup_output.html
type ChangesetIter ¶
type ChangesetIter struct {
// contains filtered or unexported fields
}
ChangesetIter is an iterator over a changeset.
An iterator is used much like a Stmt over result rows. It is also used in the conflictFn provided to ChangesetApply. To process the changes in a changeset:
iter, err := ChangesetIterStart(r) if err != nil { // ... handle err } for { hasRow, err := iter.Next() if err != nil { // ... handle err } if !hasRow { break } // Use the Op, New, Old method to inspect the change. } if err := iter.Finalize(); err != nil { // ... handle err }
func ChangesetIterStart ¶
func ChangesetIterStart(r io.Reader) (ChangesetIter, error)
ChangesetIterStart creates an iterator over a changeset.
func (ChangesetIter) Conflict ¶
func (iter ChangesetIter) Conflict(col int) (v Value, err error)
Conflict obtains conflicting row values from an iterator. Only use this in an iterator passed to a ChangesetApply conflictFn.
https://www.sqlite.org/session/sqlite3changeset_conflict.html
func (ChangesetIter) FKConflicts ¶
func (iter ChangesetIter) FKConflicts() (int, error)
FKConflicts reports the number of foreign key constraint violations.
https://www.sqlite.org/session/sqlite3changeset_fk_conflicts.html
func (ChangesetIter) Finalize ¶
func (iter ChangesetIter) Finalize() error
Finalize deletes a changeset iterator. Do not use in iterators passed to a ChangesetApply conflictFn.
https://www.sqlite.org/session/sqlite3changeset_finalize.html
func (ChangesetIter) New ¶
func (iter ChangesetIter) New(col int) (v Value, err error)
New obtains new row values from an iterator.
func (ChangesetIter) Next ¶
func (iter ChangesetIter) Next() (rowReturned bool, err error)
Next moves a changeset iterator forward. Do not use in iterators passed to a ChangesetApply conflictFn.
func (ChangesetIter) Old ¶
func (iter ChangesetIter) Old(col int) (v Value, err error)
Old obtains old row values from an iterator.
func (ChangesetIter) PK ¶
func (iter ChangesetIter) PK() ([]bool, error)
PK reports the columns that make up the primary key.
type ColumnType ¶
type ColumnType int
ColumnType are codes for each of the SQLite fundamental datatypes:
64-bit signed integer 64-bit IEEE floating point number string BLOB NULL
https://www.sqlite.org/c3ref/c_blob.html
func (ColumnType) String ¶
func (t ColumnType) String() string
type ConflictAction ¶
type ConflictAction int
func (ConflictAction) String ¶
func (code ConflictAction) String() string
type ConflictType ¶
type ConflictType int
func (ConflictType) String ¶
func (code ConflictType) String() string
type Conn ¶
type Conn struct {
// contains filtered or unexported fields
}
Conn is an open connection to an SQLite3 database.
A Conn can only be used by goroutine at a time.
func OpenConn ¶
OpenConn opens a single SQLite database connection. A flags value of 0 defaults to:
SQLITE_OPEN_READWRITE SQLITE_OPEN_CREATE SQLITE_OPEN_WAL SQLITE_OPEN_URI SQLITE_OPEN_NOMUTEX
func (*Conn) ChangesetApply ¶
func (conn *Conn) ChangesetApply(r io.Reader, filterFn func(tableName string) bool, conflictFn func(ConflictType, ChangesetIter) ConflictAction) error
ChangesetApply applies a changeset to the database.
If a changeset will not apply cleanly then conflictFn can be used to resolve the conflict. See the SQLite documentation for full details.
func (*Conn) Close ¶
Close closes the database connection using sqlite3_close_v2. https://www.sqlite.org/c3ref/close.html
func (*Conn) CreateFunction ¶
func (conn *Conn) CreateFunction(name string, deterministic bool, numArgs int, xFunc, xStep func(Context, ...Value), xFinal func(Context)) error
CreateFunction registers a Go function with SQLite for use in SQL queries.
To define a scalar function, provide a value for xFunc and set xStep/xFinal to nil.
To define an aggregation set xFunc to nil and provide values for xStep and xFinal.
State can be stored across function calls by using the Context UserData/SetUserData methods.
func (*Conn) CreateSession ¶
CreateSession creates a new session object. If db is "", then a default of "main" is used. https://www.sqlite.org/session/sqlite3session_create.html
func (*Conn) EnableLoadExtension ¶ added in v0.1.1
EnableLoadExtension allows extensions to be loaded via LoadExtension(). The SQL interface is left disabled as recommended. https://www.sqlite.org/c3ref/enable_load_extension.html
func (*Conn) LastInsertRowID ¶
LastInsertRowID reports the rowid of the most recently successful INSERT.
func (*Conn) LoadExtension ¶ added in v0.1.1
LoadExtension attempts to load a runtime-loadable extension. https://www.sqlite.org/c3ref/load_extension.html
func (*Conn) Prep ¶
Prep returns a persistent SQL statement.
Any error in preparation will panic.
Persistent prepared statements are cached by the query string in a Conn. If Finalize is not called, then subsequent calls to Prepare will return the same statement.
func (*Conn) Prepare ¶
Prepare prepares a persistent SQL statement.
Persistent prepared statements are cached by the query string in a Conn. If Finalize is not called, then subsequent calls to Prepare will return the same statement.
If the query has any unprocessed trailing bytes, Prepare returns an error.
func (*Conn) PrepareTransient ¶
PrepareTransient prepares an SQL statement that is not cached by the Conn. Subsequent calls with the same query will create new Stmts.
The number of trailing bytes not consumed from query is returned.
To run a sequence of queries once as part of a script, the sqliteutil package provides an ExecScript function built on this.
func (*Conn) SetBusyTimeout ¶ added in v0.1.1
SetBusyTimeout sets a busy handler that sleeps for up to d to acquire a lock.
By default, a large busy timeout (10s) is set on the assumption that Go programs use a context object via SetInterrupt to control timeouts.
func (*Conn) SetInterrupt ¶
func (conn *Conn) SetInterrupt(doneCh <-chan struct{}) (oldDoneCh <-chan struct{})
SetInterrupt assigns a channel to control connection execution lifetime.
When doneCh is closed, the connection uses sqlite3_interrupt to stop long-running queries and cancels any *Stmt.Step calls that are blocked waiting for the database write lock.
Subsequent uses of the connection will return SQLITE_INTERRUPT errors until doneCh is reset with a subsequent call to SetInterrupt.
Typically, doneCh is provided by the Done method on a context.Context. For example, a timeout can be associated with a connection session:
ctx := context.WithTimeout(context.Background(), 100*time.Millisecond) conn.SetInterrupt(ctx.Done())
Any busy statements at the time SetInterrupt is called will be reset.
SetInterrupt returns the old doneCh assigned to the connection.
type Context ¶
type Context struct {
// contains filtered or unexported fields
}
Context is an *sqlite3_context. It is used by custom functions to return result values. An SQLite context is in no way related to a Go context.Context.
func (Context) ResultError ¶
func (Context) ResultFloat ¶
func (Context) ResultInt64 ¶
func (Context) ResultNull ¶
func (ctx Context) ResultNull()
func (Context) ResultText ¶
func (Context) ResultValue ¶
func (Context) ResultZeroBlob ¶
func (Context) SetUserData ¶
func (ctx Context) SetUserData(data interface{})
type Error ¶
type Error struct { Code ErrorCode // SQLite extended error code (SQLITE_OK is an invalid value) Loc string // method name that generated the error Query string // original SQL query text Msg string // value of sqlite3_errmsg, set sqlite.ErrMsg = true }
Error is an error produced by SQLite.
type ErrorCode ¶
type ErrorCode int
ErrorCode is an SQLite extended error code.
The three SQLite result codes (SQLITE_OK, SQLITE_ROW, and SQLITE_DONE), are not errors so they should not be used in an Error.
func ErrCode ¶
ErrCode extracts the SQLite error code from err. If err is not a sqlite Error, SQLITE_ERROR is returned. If err is nil, SQLITE_OK is returned.
This function supports wrapped errors that implement
interface { Cause() error }
for errors from packages like https://github.com/pkg/errors
type Pool ¶
type Pool struct {
// contains filtered or unexported fields
}
Pool is a pool of SQLite connections.
It is safe for use by multiple goroutines concurrently.
Typically, a goroutine that needs to use an SQLite *Conn Gets it from the pool and defers its return:
conn := dbpool.Get(nil) defer dbpool.Put(conn)
As Get may block, a context can be used to return if a task is cancelled. In this case the Conn returned will be nil:
conn := dbpool.Get(ctx.Done()) if conn == nil { return context.Canceled } defer dbpool.Put(conn)
func Open ¶
Open opens a fixed-size pool of SQLite connections. A flags value of 0 defaults to:
SQLITE_OPEN_READWRITE SQLITE_OPEN_CREATE SQLITE_OPEN_WAL SQLITE_OPEN_URI SQLITE_OPEN_NOMUTEX
func (*Pool) Get ¶
Get gets an SQLite connection from the pool.
If no Conn is available, Get will block until one is, or until either the Pool is closed or doneCh is closed, in which case Get returns nil.
The provided doneCh is used to control the execution lifetime of the connection. See Conn.SetInterrupt for details.
type Session ¶
type Session struct {
// contains filtered or unexported fields
}
A Session tracks database changes made by a Conn.
It is used to build changesets.
Equivalent to the sqlite3_session* C object.
func (*Session) Attach ¶
Attach attaches a table to the session object. Changes made to the table will be tracked by the session.
An empty tableName attaches all the tables in the database.
func (*Session) Changeset ¶
Changeset generates a changeset from a session. https://www.sqlite.org/session/sqlite3session_changeset.html
func (*Session) Diff ¶
Diff appends the difference between two tables (srcDB and the session DB) to the session. The two tables must have the same name and schema.
func (*Session) Disable ¶
func (s *Session) Disable()
Disable disables recording of changes by a Session.
type Stmt ¶
type Stmt struct {
// contains filtered or unexported fields
}
Stmt is an SQLite3 prepared statement.
A Stmt is attached to a particular Conn (and that Conn can only be used by a single goroutine).
When a Stmt is no longer needed it should be cleaned up by calling the Finalize method.
func (*Stmt) BindBytes ¶
BindBytes binds value to a numbered stmt parameter.
In-memory copies of value are made using this interface. For large blobs, consider using the streaming Blob object.
func (*Stmt) BindFloat ¶
BindFloat binds value to a numbered stmt parameter. Parameter indicies start at 1. https://www.sqlite.org/c3ref/bind_blob.html
func (*Stmt) BindInt64 ¶
BindInt64 binds value to a numbered stmt parameter. https://www.sqlite.org/c3ref/bind_blob.html
func (*Stmt) BindNull ¶
BindNull binds an SQL NULL value to a numbered stmt parameter. Parameter indicies start at 1. https://www.sqlite.org/c3ref/bind_blob.html
func (*Stmt) BindParamCount ¶
BindParamCount reports the number of parameters in stmt. https://www.sqlite.org/c3ref/bind_parameter_count.html
func (*Stmt) BindText ¶
BindText binds value to a numbered stmt parameter. https://www.sqlite.org/c3ref/bind_blob.html
func (*Stmt) BindZeroBlob ¶
BindNull binds a blob of zeros of length len to a numbered stmt parameter. Parameter indicies start at 1. https://www.sqlite.org/c3ref/bind_blob.html
func (*Stmt) ClearBindings ¶
ClearBindings clears all bound parameter values on a statement.
func (*Stmt) ColumnBytes ¶
ColumnBytes reads a query result into buf. It reports the number of bytes read.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnCount ¶
ColumnCount returns the number of columns in the result set returned by the prepared statement. https://sqlite.org/c3ref/column_count.html
func (*Stmt) ColumnDatabaseName ¶
func (*Stmt) ColumnFloat ¶
ColumnFloat returns a query result as a float64.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnInt ¶
ColumnInt returns a query result value as an int.
Note: this method calls sqlite3_column_int64 and then converts the resulting 64-bits to an int.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnInt32 ¶
ColumnInt32 returns a query result value as an int32.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnInt64 ¶
ColumnInt64 returns a query result value as an int64.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnLen ¶
ColumnLen returns the number of bytes in a query result.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnName ¶
ColumnName returns the name assigned to a particular column in the result set of a SELECT statement. https://sqlite.org/c3ref/column_name.html
func (*Stmt) ColumnReader ¶
ColumnReader creates a byte reader for a query result column.
The reader directly references C-managed memory that stops being valid as soon as the statement row resets.
func (*Stmt) ColumnTableName ¶
func (*Stmt) ColumnText ¶
ColumnText returns a query result as a string.
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) ColumnType ¶
func (stmt *Stmt) ColumnType(col int) ColumnType
ColumnType returns the datatype code for the initial data type of the result column. The returned value is one of:
SQLITE_INTEGER SQLITE_FLOAT SQLITE_TEXT SQLITE_BLOB SQLITE_NULL
Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html
func (*Stmt) DataCount ¶
DataCount returns the number of columns in the current row of the result set of prepared statement. https://sqlite.org/c3ref/data_count.html
func (*Stmt) Finalize ¶
Finalize deletes a prepared statement.
Be sure to always call Finalize when done with a statement created using PrepareTransient.
Do not call Finalize on a prepared statement that you intend to prepare again in the future.
func (*Stmt) GetBytes ¶
GetBytes reads a query result for colName into buf. It reports the number of bytes read.
func (*Stmt) GetReader ¶
GetReader creates a byte reader for colName.
The reader directly references C-managed memory that stops being valid as soon as the statement row resets.
func (*Stmt) Reset ¶
Reset resets a prepared statement so it can be executed again.
Note that any parameter values bound to the statement are retained. To clear bound values, call ClearBindings.
func (*Stmt) SetBytes ¶
SetBytes binds bytes to a parameter using a column name. An invalid parameter name will cause the call to Step to return an error.
func (*Stmt) SetFloat ¶
SetFloat binds a float64 to a parameter using a column name. An invalid parameter name will cause the call to Step to return an error.
func (*Stmt) SetNull ¶
SetNull binds a null to a parameter using a column name. An invalid parameter name will cause the call to Step to return an error.
func (*Stmt) SetText ¶
SetText binds text to a parameter using a column name. An invalid parameter name will cause the call to Step to return an error.
func (*Stmt) SetZeroBlob ¶
SetZeroBlob binds a zero blob of length len to a parameter using a column name. An invalid parameter name will cause the call to Step to return an error.
func (*Stmt) Step ¶
Step moves through the statement cursor using sqlite3_step.
If a row of data is available, rowReturned is reported as true. If the statement has reached the end of the available data then rowReturned is false. Thus the status codes SQLITE_ROW and SQLITE_DONE are reported by the rowReturned bool, and all other non-OK status codes are reported as an error.
If an error value is returned, then the statement has been reset.
https://www.sqlite.org/c3ref/step.html
Shared cache ¶
As the sqlite package enables shared cache mode by default and multiple writers are common in multi-threaded programs, this Step method uses sqlite3_unlock_notify to handle any SQLITE_LOCKED errors.
Without the shared cache, SQLite will block for several seconds while trying to acquire the write lock. With the shared cache, it returns SQLITE_LOCKED immediately if the write lock is held by another connection in this process. Dealing with this correctly makes for an unpleasant programming experience, so this package does it automatically by blocking Step until the write lock is relinquished.
This means Step can block for a very long time. Use SetInterrupt to control how long Step will block.
For far more details, see:
http://www.sqlite.org/unlock_notify.html
Source Files ¶
Directories ¶
Path | Synopsis |
---|---|
Package sqliteutil provides utilities for working with SQLite.
|
Package sqliteutil provides utilities for working with SQLite. |