sqlitex

package
v1.4.0 Latest Latest
Warning

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

 Go to latest Published: Sep 23, 2024 License: ISC Imports: 12 Imported by: 51

Documentation

Overview

Package sqlitex provides utilities for working with SQLite.

Statements

To execute a statement from a string, pass an ExecOptions struct to one of the following functions:

To execute a statement from a file (typically using embed), pass an ExecOptions struct to one of the following functions:

Transactions and Savepoints

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func ExclusiveTransaction added in v0.8.0 

func ExclusiveTransaction(conn *sqlite.Conn) (endFn func(*error), err error)

ExclusiveTransaction creates an EXCLUSIVE SQLite transaction.

On success ImmediateTransaction returns an endFn that will call either COMMIT or ROLLBACK depending on whether the parameter *error points to a nil or non-nil error. This is designed to be deferred.

https://www.sqlite.org/lang_transaction.html

func Exec deprecated 

func Exec(conn *sqlite.Conn, query string, resultFn func(stmt *sqlite.Stmt) error, args ...any) error

Exec executes an SQLite query.

For each result row, the resultFn is called. Result values can be read by resultFn using stmt.Column* methods. If resultFn returns an error then iteration ceases and Exec returns the error value.

Any args provided to Exec are bound to numbered parameters of the query using the sqlite.Stmt Bind* methods. Basic reflection on args is used to map: 

integers to BindInt64
floats   to BindFloat
[]byte   to BindBytes
string   to BindText
bool     to BindBool

All other kinds are printed using fmt.Sprintf("%v", v) and passed to BindText.

Exec is implemented using the Stmt prepare mechanism which allows better interactions with Go's type system and avoids pitfalls of passing a Go closure to cgo.

As Exec is implemented using Conn.Prepare, subsequent calls to Exec with the same statement will reuse the cached statement object.

Deprecated: Use Execute. Exec skips some argument checks for compatibility with crawshaw.io/sqlite.

func ExecFS deprecated added in v0.2.0 

func ExecFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) error

ExecFS is an alias for ExecuteFS.

Deprecated: Call ExecuteFS directly.

func ExecScript 

func ExecScript(conn *sqlite.Conn, queries string) (err error)

ExecScript executes a script of SQL statements. It is the same as calling ExecuteScript without options.

func ExecScriptFS deprecated added in v0.2.0 

func ExecScriptFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) (err error)

ExecScriptFS is an alias for ExecuteScriptFS.

Deprecated: Call ExecuteScriptFS directly.

func ExecTransient deprecated 

func ExecTransient(conn *sqlite.Conn, query string, resultFn func(stmt *sqlite.Stmt) error, args ...any) (err error)

ExecTransient executes an SQLite query without caching the underlying query. The interface is exactly the same as Exec. It is the spiritual equivalent of sqlite3_exec.

Deprecated: Use ExecuteTransient. ExecTransient skips some argument checks for compatibility with crawshaw.io/sqlite.

func ExecTransientFS deprecated added in v0.2.0 

func ExecTransientFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) error

ExecTransientFS is an alias for ExecuteTransientFS.

Deprecated: Call ExecuteTransientFS directly.

func Execute added in v0.9.0 

func Execute(conn *sqlite.Conn, query string, opts *ExecOptions) error

Execute executes an SQLite query.

As Execute is implemented using sqlite.Conn.Prepare, subsequent calls to Execute with the same statement will reuse the cached statement object.

Example 
package main

import (
	"fmt"

	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

func main() {
	conn, err := sqlite.OpenConn(":memory:")
	if err != nil {
		// handle err
	}

	if err := sqlitex.Execute(conn, "CREATE TABLE t (a, b, c, d);", nil); err != nil {
		// handle err
	}

	err = sqlitex.Execute(conn, "INSERT INTO t (a, b, c, d) VALUES (?, ?, ?, ?);", &sqlitex.ExecOptions{
		Args: []any{"a1", 1, 42, 1},
	})
	if err != nil {
		// handle err
	}

	var a []string
	var b []int64
	err = sqlitex.Execute(conn, "SELECT a, b FROM t WHERE c = ? AND d = ?;", &sqlitex.ExecOptions{
		ResultFunc: func(stmt *sqlite.Stmt) error {
			a = append(a, stmt.ColumnText(0))
			b = append(b, stmt.ColumnInt64(1))
			return nil
		},
		Args: []any{42, 1},
	})
	if err != nil {
		// handle err
	}

	fmt.Println(a, b)
}

Output:

[a1] [1]

func ExecuteFS added in v0.9.0 

func ExecuteFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) error

ExecuteFS executes the single statement in the given SQL file. ExecuteFS is implemented using sqlite.Conn.Prepare, so subsequent calls to ExecuteFS with the same statement will reuse the cached statement object.

func ExecuteScript added in v0.9.0 

func ExecuteScript(conn *sqlite.Conn, queries string, opts *ExecOptions) (err error)

ExecuteScript executes a script of SQL statements. The script is wrapped in a SAVEPOINT transaction, which is rolled back on any error.

opts.ResultFunc is ignored.

func ExecuteScriptFS added in v0.9.0 

func ExecuteScriptFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) (err error)

ExecuteScriptFS executes a script of SQL statements from a file. The script is wrapped in a SAVEPOINT transaction, which is rolled back on any error.

func ExecuteTransient added in v0.9.0 

func ExecuteTransient(conn *sqlite.Conn, query string, opts *ExecOptions) (err error)

ExecuteTransient executes an SQLite query without caching the underlying query. It is the spiritual equivalent of sqlite3_exec: https://www.sqlite.org/c3ref/exec.html

func ExecuteTransientFS added in v0.9.0 

func ExecuteTransientFS(conn *sqlite.Conn, fsys fs.FS, filename string, opts *ExecOptions) error

ExecuteTransientFS executes the single statement in the given SQL file without caching the underlying query.

func ImmediateTransaction added in v0.8.0 

func ImmediateTransaction(conn *sqlite.Conn) (endFn func(*error), err error)

ImmediateTransaction creates an IMMEDIATE SQLite transaction.

On success ImmediateTransaction returns an endFn that will call either COMMIT or ROLLBACK depending on whether the parameter *error points to a nil or non-nil error. This is designed to be deferred.

https://www.sqlite.org/lang_transaction.html

func InsertRandID 

func InsertRandID(stmt *sqlite.Stmt, param string, min, max int64) (int64, error)

InsertRandID executes stmt with a random value in the range [min, max) for $param.

func PrepareTransientFS added in v0.2.0 

func PrepareTransientFS(conn *sqlite.Conn, fsys fs.FS, filename string) (*sqlite.Stmt, error)

PrepareTransientFS prepares an SQL statement from a file that is not cached by the Conn. Subsequent calls with the same query will create new Stmts. The caller is responsible for calling sqlite.Stmt.Finalize on the returned Stmt when the Stmt is no longer needed.

func ResultBool added in v0.2.0 

func ResultBool(stmt *sqlite.Stmt) (bool, error)

ResultBool reports whether the first column of the first and only row produced by running stmt is non-zero. It returns an error if there is not exactly one result row.

func ResultBytes added in v1.4.0 

func ResultBytes(stmt *sqlite.Stmt, buf []byte) (int, error)

ResultBytes reads the first column of the first and only row produced by running stmt into buf, returning the number of bytes read. It returns an error if there is not exactly one result row.

func ResultFloat 

func ResultFloat(stmt *sqlite.Stmt) (float64, error)

ResultFloat returns the first column of the first and only row produced by running stmt as a real number. It returns an error if there is not exactly one result row.

func ResultInt 

func ResultInt(stmt *sqlite.Stmt) (int, error)

ResultInt returns the first column of the first and only row produced by running stmt as an integer. It returns an error if there is not exactly one result row.

func ResultInt64 

func ResultInt64(stmt *sqlite.Stmt) (int64, error)

ResultInt64 returns the first column of the first and only row produced by running stmt as an integer. It returns an error if there is not exactly one result row.

func ResultText 

func ResultText(stmt *sqlite.Stmt) (string, error)

ResultText returns the first column of the first and only row produced by running stmt as text. It returns an error if there is not exactly one result row.

func Save 

func Save(conn *sqlite.Conn) (releaseFn func(*error))

Save creates a named SQLite transaction using SAVEPOINT.

On success Savepoint returns a releaseFn that will call either RELEASE or ROLLBACK depending on whether the parameter *error points to a nil or non-nil error. This is designed to be deferred.

https://www.sqlite.org/lang_savepoint.html

Example 
package main

import (
	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

func main() {
	doWork := func(conn *sqlite.Conn) (err error) {
		defer sqlitex.Save(conn)(&err)

		// ... do work in the transaction
		return nil
	}
	_ = doWork
}

Output:

func Transaction added in v0.8.0 

func Transaction(conn *sqlite.Conn) (endFn func(*error))

Transaction creates a DEFERRED SQLite transaction.

On success Transaction returns an endFn that will call either COMMIT or ROLLBACK depending on whether the parameter *error points to a nil or non-nil error. This is designed to be deferred.

https://www.sqlite.org/lang_transaction.html

Types

type ConnPrepareFunc added in v1.1.0 

type ConnPrepareFunc func(conn *sqlite.Conn) error

A ConnPrepareFunc is called for each connection in a pool to set up connection-specific state. It must be safe to call from multiple goroutines.

If the ConnPrepareFunc returns an error, then it will be called the next time the connection is about to be used. Once ConnPrepareFunc returns nil for a given connection, it will not be called on that connection again.

type ExecOptions added in v0.2.0 

type ExecOptions struct {
	// Args is the set of positional arguments to bind to the statement.
	// The first element in the slice is ?1.
	// See https://sqlite.org/lang_expr.html for more details.
	//
	// Basic reflection on Args is used to map:
	//
	//	integers to BindInt64
	//	floats   to BindFloat
	//	[]byte   to BindBytes
	//	string   to BindText
	//	bool     to BindBool
	//
	// All other kinds are printed using fmt.Sprint(v) and passed to BindText.
	Args []any

	// Named is the set of named arguments to bind to the statement. Keys must
	// start with ':', '@', or '$'. See https://sqlite.org/lang_expr.html for more
	// details.
	//
	// Basic reflection on Named is used to map:
	//
	//	integers to BindInt64
	//	floats   to BindFloat
	//	[]byte   to BindBytes
	//	string   to BindText
	//	bool     to BindBool
	//
	// All other kinds are printed using fmt.Sprint(v) and passed to BindText.
	Named map[string]any

	// ResultFunc is called for each result row.
	// If ResultFunc returns an error then iteration ceases
	// and the execution function returns the error value.
	ResultFunc func(stmt *sqlite.Stmt) error
}

ExecOptions is the set of optional arguments executing a statement.

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.

Example 
package main

import (
	"context"

	"zombiezen.com/go/sqlite/sqlitex"
)

func main() {
	// Open a pool.
	dbpool, err := sqlitex.NewPool("foo.db", sqlitex.PoolOptions{})
	if err != nil {
		// handle err
	}
	defer func() {
		if err := dbpool.Close(); err != nil {
			// handle err
		}
	}()

	// While handling a request:
	ctx := context.TODO()
	conn, err := dbpool.Take(ctx)
	if err != nil {
		// handle err
	}
	defer dbpool.Put(conn)
}

Output:

func NewPool added in v1.1.0 

func NewPool(uri string, opts PoolOptions) (pool *Pool, err error)

NewPool opens a fixed-size pool of SQLite connections.

func Open deprecated 

func Open(uri string, flags sqlite.OpenFlags, poolSize int) (pool *Pool, err error)

Open opens a fixed-size pool of SQLite connections.

Deprecated: NewPool supports more options.

func (*Pool) Close 

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

Close interrupts and closes all the connections in the Pool, blocking until all connections are returned to the Pool.

func (*Pool) Get deprecated 

func (p *Pool) Get(ctx context.Context) *sqlite.Conn

Get returns an SQLite connection from the Pool.

Deprecated: Use Pool.Take instead. Get is an alias for backward compatibility.

func (*Pool) Put 

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

Put puts an SQLite connection back into the Pool.

Put will panic if the conn was not originally created by p. Put(nil) is a no-op.

Applications must ensure that all non-nil Conns returned from Pool.Get are returned to the same Pool with Put.

func (*Pool) Take added in v1.2.0 

func (p *Pool) Take(ctx context.Context) (*sqlite.Conn, error)

Take returns an SQLite connection from the Pool.

If no connection is available, Take will block until at least one connection is returned with Pool.Put, or until either the Pool is closed or the context is canceled. If no connection can be obtained or an error occurs while preparing the connection, an error is returned.

The provided context is also used to control the execution lifetime of the connection. See sqlite.Conn.SetInterrupt for details.

Applications must ensure that all non-nil Conns returned from Take are returned to the same Pool with Pool.Put.

type PoolOptions added in v1.1.0 

type PoolOptions struct {
	// Flags is interpreted the same way as the argument to [sqlite.Open].
	// A Flags value of 0 defaults to:
	//
	//  - SQLITE_OPEN_READWRITE
	//  - SQLITE_OPEN_CREATE
	//  - SQLITE_OPEN_WAL
	//  - SQLITE_OPEN_URI
	Flags sqlite.OpenFlags

	// PoolSize sets an explicit size to the pool.
	// If less than 1, a reasonable default is used.
	PoolSize int

	// PrepareConn is called for each connection in the pool to set up functions
	// and other connection-specific state.
	PrepareConn ConnPrepareFunc
}

PoolOptions is the set of optional arguments to NewPool.

Source Files

View all Source files

Jump to

Keyboard shortcuts

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