sqlite

package module
v0.1.2 Latest Latest
Warning

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

 Go to latest Published: Dec 19, 2018 License: ISC Imports: 7 Imported by: 0

README

Go interface to SQLite.

GoDoc Build Status (linux and macOS) Build status (windows)

This package provides a low-level Go interface to SQLite 3. Connections are pooled and if the SQLite shared cache mode is enabled the package takes advantage of the unlock-notify API to minimize the amount of handling user code needs for dealing with database lock contention.

It has interfaces for some of SQLite's more interesting extensions, such as incremental BLOB I/O and the session extension.

A utility package, sqliteutil, provides some higher-level tools for making it easier to perform common tasks with SQLite. In particular it provides support to make nested transactions easy to use via sqliteutil.Save.

This is not a database/sql driver.

go get -u crawshaw.io/sqlite

Example

A HTTP handler that uses a multi-threaded pool of SQLite connections via a shared cache.

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 handler(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
	}
}

https://godoc.org/crawshaw.io/sqlite

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

View Source 
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)
)
View Source 
const (
	SQLITE_INSERT = OpType(C.SQLITE_INSERT)
	SQLITE_DELETE = OpType(C.SQLITE_DELETE)
	SQLITE_UPDATE = OpType(C.SQLITE_UPDATE)
)
View Source 
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)
)
View Source 
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)
)
View Source 
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)
)
View Source 
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)
)
View Source 
const (
	SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION = C.int(C.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION)
)

Variables

View Source 
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 added in v0.1.0 

func ChangesetConcat(w io.Writer, r1, r2 io.Reader) error

ChangesetConcat concatenates two changesets.

https://www.sqlite.org/session/sqlite3changeset_concat.html

func ChangesetInvert added in v0.1.0 

func ChangesetInvert(w io.Writer, r io.Reader) error

ChangesetInvert inverts a changeset.

https://www.sqlite.org/session/sqlite3changeset_invert.html

Types

type Blob added in v0.1.0 

type Blob struct {
	io.ReadWriteSeeker
	io.ReaderAt
	io.WriterAt
	io.Closer
	// contains filtered or unexported fields
}

Blob provides streaming access to SQLite blobs.

func (*Blob) Close added in v0.1.0 

func (blob *Blob) Close() error

https://www.sqlite.org/c3ref/blob_close.html

func (*Blob) Read added in v0.1.0 

func (blob *Blob) Read(p []byte) (n int, err error)

func (*Blob) ReadAt added in v0.1.0 

func (blob *Blob) ReadAt(p []byte, off int64) (n int, err error)

https://www.sqlite.org/c3ref/blob_read.html

func (*Blob) Seek added in v0.1.0 

func (blob *Blob) Seek(offset int64, whence int) (int64, error)

func (*Blob) Size added in v0.1.0 

func (blob *Blob) Size() int64

Size returns the total size of a blob.

func (*Blob) Write added in v0.1.0 

func (blob *Blob) Write(p []byte) (n int, err error)

func (*Blob) WriteAt added in v0.1.0 

func (blob *Blob) WriteAt(p []byte, off int64) (n int, err error)

https://www.sqlite.org/c3ref/blob_write.html

type Changegroup added in v0.1.0 

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

func NewChangegroup added in v0.1.0 

func NewChangegroup() (*Changegroup, error)

https://www.sqlite.org/session/sqlite3changegroup_new.html

func (Changegroup) Add added in v0.1.0 

func (cg Changegroup) Add(r io.Reader) error

https://www.sqlite.org/session/sqlite3changegroup_add.html

func (Changegroup) Delete added in v0.1.0 

func (cg Changegroup) Delete()

Delete deletes a Changegroup.

https://www.sqlite.org/session/sqlite3changegroup_delete.html

func (Changegroup) Output added in v0.1.0 

func (cg Changegroup) Output(w io.Writer) (n int, err error)

https://www.sqlite.org/session/sqlite3changegroup_output.html

type ChangesetIter added in v0.1.0 

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 added in v0.1.0 

func ChangesetIterStart(r io.Reader) (ChangesetIter, error)

ChangesetIterStart creates an iterator over a changeset.

https://www.sqlite.org/session/sqlite3changeset_start.html

func (ChangesetIter) Conflict added in v0.1.0 

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 added in v0.1.0 

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 added in v0.1.0 

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 added in v0.1.0 

func (iter ChangesetIter) New(col int) (v Value, err error)

New obtains new row values from an iterator.

https://www.sqlite.org/session/sqlite3changeset_new.html

func (ChangesetIter) Next added in v0.1.0 

func (iter ChangesetIter) Next() (rowReturned bool, err error)

Next moves a changeset iterator forward. Do not use in iterators passed to a ChangesetApply conflictFn.

https://www.sqlite.org/session/sqlite3changeset_next.html

func (ChangesetIter) Old added in v0.1.0 

func (iter ChangesetIter) Old(col int) (v Value, err error)

Old obtains old row values from an iterator.

https://www.sqlite.org/session/sqlite3changeset_old.html

func (ChangesetIter) Op added in v0.1.0 

func (iter ChangesetIter) Op() (table string, numCols int, opType OpType, indirect bool, err error)

Op reports details about the current operation in the iterator.

https://www.sqlite.org/session/sqlite3changeset_op.html

func (ChangesetIter) PK added in v0.1.0 

func (iter ChangesetIter) PK() ([]bool, error)

PK reports the columns that make up the primary key.

https://www.sqlite.org/session/sqlite3changeset_pk.html

type ColumnType added in v0.1.0 

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 added in v0.1.0 

func (t ColumnType) String() string

type ConflictAction added in v0.1.0 

type ConflictAction int

func (ConflictAction) String added in v0.1.0 

func (code ConflictAction) String() string

type ConflictType added in v0.1.0 

type ConflictType int

func (ConflictType) String added in v0.1.0 

func (code ConflictType) String() string

type Conn added in v0.1.0 

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 added in v0.1.0 

func OpenConn(path string, flags OpenFlags) (*Conn, error)

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

https://www.sqlite.org/c3ref/open.html

func (*Conn) Changes added in v0.1.0 

func (conn *Conn) Changes() int

Changes reports the number of rows affected by the most recent statement.

https://www.sqlite.org/c3ref/changes.html

func (*Conn) ChangesetApply added in v0.1.0 

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.

https://www.sqlite.org/session/sqlite3changeset_apply.html

func (*Conn) Close added in v0.1.0 

func (conn *Conn) Close() error

Close closes the database connection using sqlite3_close_v2. https://www.sqlite.org/c3ref/close.html

func (*Conn) CreateFunction added in v0.1.0 

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.

https://sqlite.org/c3ref/create_function.html

func (*Conn) CreateSession added in v0.1.0 

func (conn *Conn) CreateSession(db string) (*Session, error)

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 

func (conn *Conn) EnableLoadExtension(on bool) error

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 added in v0.1.0 

func (conn *Conn) LastInsertRowID() int64

LastInsertRowID reports the rowid of the most recently successful INSERT.

https://www.sqlite.org/c3ref/last_insert_rowid.html

func (*Conn) LoadExtension added in v0.1.1 

func (conn *Conn) LoadExtension(ext, entry string) error

LoadExtension attempts to load a runtime-loadable extension. https://www.sqlite.org/c3ref/load_extension.html

func (*Conn) OpenBlob added in v0.1.0 

func (conn *Conn) OpenBlob(dbn, table, column string, row int64, write bool) (*Blob, error)

OpenBlob opens a blob in a particular {database,table,column,row}.

https://www.sqlite.org/c3ref/blob_open.html

func (*Conn) Prep added in v0.1.0 

func (conn *Conn) Prep(query string) *Stmt

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.

https://www.sqlite.org/c3ref/prepare.html

func (*Conn) Prepare added in v0.1.0 

func (conn *Conn) Prepare(query string) (*Stmt, error)

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.

https://www.sqlite.org/c3ref/prepare.html

func (*Conn) PrepareTransient added in v0.1.0 

func (conn *Conn) PrepareTransient(query string) (stmt *Stmt, trailingBytes int, err error)

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.

https://www.sqlite.org/c3ref/prepare.html

func (*Conn) SetBusyTimeout added in v0.1.1 

func (conn *Conn) SetBusyTimeout(d time.Duration)

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.

https://www.sqlite.org/c3ref/busy_timeout.html

func (*Conn) SetInterrupt added in v0.1.0 

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 added in v0.1.0 

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 added in v0.1.0 

func (ctx Context) ResultError(err error)

func (Context) ResultFloat added in v0.1.0 

func (ctx Context) ResultFloat(v float64)

func (Context) ResultInt added in v0.1.0 

func (ctx Context) ResultInt(v int)

func (Context) ResultInt64 added in v0.1.0 

func (ctx Context) ResultInt64(v int64)

func (Context) ResultNull added in v0.1.0 

func (ctx Context) ResultNull()

func (Context) ResultText added in v0.1.0 

func (ctx Context) ResultText(v string)

func (Context) ResultValue added in v0.1.0 

func (ctx Context) ResultValue(v Value)

func (Context) ResultZeroBlob added in v0.1.0 

func (ctx Context) ResultZeroBlob(n int64)

func (Context) SetUserData added in v0.1.0 

func (ctx Context) SetUserData(data interface{})

func (Context) UserData added in v0.1.0 

func (ctx Context) UserData() interface{}

type Error added in v0.1.0 

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.

func (Error) Error added in v0.1.0 

func (err Error) Error() string

type ErrorCode added in v0.1.0 

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 added in v0.1.0 

func ErrCode(err error) ErrorCode

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

func (ErrorCode) String added in v0.1.0 

func (code ErrorCode) String() string

type OpType added in v0.1.0 

type OpType int

func (OpType) String added in v0.1.0 

func (opType OpType) String() string

type OpenFlags added in v0.1.0 

type OpenFlags int

OpenFlags are flags used when opening a Conn.

https://www.sqlite.org/c3ref/c_open_autoproxy.html

type Pool added in v0.1.0 

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 

func Open(uri string, flags OpenFlags, poolSize int) (*Pool, error)

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) Close added in v0.1.0 

func (p *Pool) Close() (err error)

Close closes all the connections in the Pool.

func (*Pool) Get added in v0.1.0 

func (p *Pool) Get(doneCh <-chan struct{}) *Conn

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.

func (*Pool) Put added in v0.1.0 

func (p *Pool) Put(conn *Conn)

Put puts an SQLite connection back into the Pool. A nil conn will cause Put to panic.

type Session added in v0.1.0 

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 added in v0.1.0 

func (s *Session) Attach(tableName string) error

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 added in v0.1.0 

func (s *Session) Changeset(w io.Writer) error

Changeset generates a changeset from a session. https://www.sqlite.org/session/sqlite3session_changeset.html

func (*Session) Delete added in v0.1.0 

func (s *Session) Delete()

Delete deletes a Session object.

https://www.sqlite.org/session/sqlite3session_delete.html

func (*Session) Diff added in v0.1.0 

func (s *Session) Diff(srcDB, tableName string) error

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 added in v0.1.0 

func (s *Session) Disable()

Disable disables recording of changes by a Session.

https://www.sqlite.org/session/sqlite3session_enable.html

func (*Session) Enable added in v0.1.0 

func (s *Session) Enable()

Enable enables recording of changes by a Session. New Sessions start enabled.

https://www.sqlite.org/session/sqlite3session_enable.html

func (*Session) Patchset added in v0.1.0 

func (s *Session) Patchset(w io.Writer) error

Patchset generates a patchset from a session. https://www.sqlite.org/session/sqlite3session_patchset.html

type Stmt added in v0.1.0 

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) BindBool added in v0.1.0 

func (stmt *Stmt) BindBool(param int, value bool)

BindBool binds value (as an integer 0 or 1) to a numbered stmt parameter.

func (*Stmt) BindBytes added in v0.1.0 

func (stmt *Stmt) BindBytes(param int, value []byte)

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.

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

func (*Stmt) BindFloat added in v0.1.0 

func (stmt *Stmt) BindFloat(param int, value float64)

BindFloat binds value to a numbered stmt parameter. Parameter indicies start at 1. https://www.sqlite.org/c3ref/bind_blob.html

func (*Stmt) BindInt64 added in v0.1.0 

func (stmt *Stmt) BindInt64(param int, value int64)

BindInt64 binds value to a numbered stmt parameter. https://www.sqlite.org/c3ref/bind_blob.html

func (*Stmt) BindNull added in v0.1.0 

func (stmt *Stmt) BindNull(param int)

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 added in v0.1.0 

func (stmt *Stmt) BindParamCount() int

BindParamCount reports the number of parameters in stmt. https://www.sqlite.org/c3ref/bind_parameter_count.html

func (*Stmt) BindText added in v0.1.0 

func (stmt *Stmt) BindText(param int, value string)

BindText binds value to a numbered stmt parameter. https://www.sqlite.org/c3ref/bind_blob.html

func (*Stmt) BindZeroBlob added in v0.1.0 

func (stmt *Stmt) BindZeroBlob(param int, len int64)

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 added in v0.1.0 

func (stmt *Stmt) ClearBindings() error

ClearBindings clears all bound parameter values on a statement.

https://www.sqlite.org/c3ref/clear_bindings.html

func (*Stmt) ColumnBytes added in v0.1.0 

func (stmt *Stmt) ColumnBytes(col int, buf []byte) int

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 added in v0.1.0 

func (stmt *Stmt) ColumnCount() int

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 added in v0.1.0 

func (stmt *Stmt) ColumnDatabaseName(col int) string

func (*Stmt) ColumnFloat added in v0.1.0 

func (stmt *Stmt) ColumnFloat(col int) float64

ColumnFloat returns a query result as a float64.

Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html

func (*Stmt) ColumnInt added in v0.1.0 

func (stmt *Stmt) ColumnInt(col int) int

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 added in v0.1.0 

func (stmt *Stmt) ColumnInt32(col int) int32

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 added in v0.1.0 

func (stmt *Stmt) ColumnInt64(col int) int64

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 added in v0.1.0 

func (stmt *Stmt) ColumnLen(col int) int

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 added in v0.1.0 

func (stmt *Stmt) ColumnName(col int) string

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 added in v0.1.0 

func (stmt *Stmt) ColumnReader(col int) *bytes.Reader

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 added in v0.1.0 

func (stmt *Stmt) ColumnTableName(col int) string

func (*Stmt) ColumnText added in v0.1.0 

func (stmt *Stmt) ColumnText(col int) string

ColumnText returns a query result as a string.

Column indicies start at 0. https://www.sqlite.org/c3ref/column_blob.html

func (*Stmt) ColumnType added in v0.1.0 

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 added in v0.1.0 

func (stmt *Stmt) DataCount() int

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 added in v0.1.0 

func (stmt *Stmt) Finalize() error

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.

https://www.sqlite.org/c3ref/finalize.html

func (*Stmt) GetBytes added in v0.1.0 

func (stmt *Stmt) GetBytes(colName string, buf []byte) int

GetBytes reads a query result for colName into buf. It reports the number of bytes read.

func (*Stmt) GetFloat added in v0.1.0 

func (stmt *Stmt) GetFloat(colName string) float64

GetFloat returns a query result value for colName as a float64.

func (*Stmt) GetInt64 added in v0.1.0 

func (stmt *Stmt) GetInt64(colName string) int64

GetInt64 returns a query result value for colName as an int64.

func (*Stmt) GetLen added in v0.1.0 

func (stmt *Stmt) GetLen(colName string) int

GetLen returns the number of bytes in a query result for colName.

func (*Stmt) GetReader added in v0.1.0 

func (stmt *Stmt) GetReader(colName string) *bytes.Reader

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) GetText added in v0.1.0 

func (stmt *Stmt) GetText(colName string) string

GetText returns a query result value for colName as a string.

func (*Stmt) Reset added in v0.1.0 

func (stmt *Stmt) Reset() error

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.

https://www.sqlite.org/c3ref/reset.html

func (*Stmt) SetBool added in v0.1.0 

func (stmt *Stmt) SetBool(param string, value bool)

SetBool binds a value (as a 0 or 1) to a parameter using a column name.

func (*Stmt) SetBytes added in v0.1.0 

func (stmt *Stmt) SetBytes(param string, value []byte)

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 added in v0.1.0 

func (stmt *Stmt) SetFloat(param string, value float64)

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) SetInt64 added in v0.1.0 

func (stmt *Stmt) SetInt64(param string, value int64)

SetInt64 binds an int64 to a parameter using a column name.

func (*Stmt) SetNull added in v0.1.0 

func (stmt *Stmt) SetNull(param string)

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 added in v0.1.0 

func (stmt *Stmt) SetText(param string, value string)

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 added in v0.1.0 

func (stmt *Stmt) SetZeroBlob(param string, len int64)

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 added in v0.1.0 

func (stmt *Stmt) Step() (rowReturned bool, err error)

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

type Value added in v0.1.0 

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

func (Value) Blob added in v0.1.0 

func (v Value) Blob() []byte

func (Value) Float added in v0.1.0 

func (v Value) Float() float64

func (Value) Int added in v0.1.0 

func (v Value) Int() int

func (Value) Int64 added in v0.1.0 

func (v Value) Int64() int64

func (Value) Len added in v0.1.0 

func (v Value) Len() int

func (Value) Text added in v0.1.0 

func (v Value) Text() string

Source Files

View all Source files

Directories

Path Synopsis
Package sqliteutil provides utilities for working with SQLite.
 Package sqliteutil provides utilities for working with SQLite.

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.