sqlite

package module
v0.5.5 Latest Latest
Warning

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

 Go to latest Published: Feb 29, 2024 License: BSD-2-Clause Imports: 25 Imported by: 0

README

sqlite

Another take at persistence for Go Programs

Documentation

Overview

Package sqlite implements a driver over the SQLite database engine.

To get started, the Open and Conn.Exec methods should be sufficient, but do note that OpenPool is used throughout the server code.

The package API is designed to expose as possible of the underlying engine as possible, including the option to expose functions and data structures via Register.

Example

A quick tour of the most important functions in the library 

ctn, err := Open(MemoryPath)
if err != nil {
	log.Fatal("cannot open a temporary database", err)
}

ctx := context.Background()

sts := []string{
	"create table tbl1 (a primary key, b)",
	"insert into tbl1 (a, b) values ('hello','world'), ('bonjour','monde'), ('hola','mundo')",
}
for _, st := range sts {
	// Exec can be used for direct statements
	if err := ctn.Exec(ctx, st).Err(); err != nil {
		log.Fatal("cannot create table", err)
	}
}

// Scan to iterate over all results
rows := ctn.Exec(ctx, "select a, b from tbl1")
for rows.Next() {
	var a, b string
	rows.Scan(&a, &b)
	fmt.Println(a, b)
}
if rows.Err() != nil {
	log.Fatal("cannot query tbl1", rows.Err())
}

// ScanOne is a handy shortcut
var b string
if err := ctn.Exec(ctx, "select b from tbl1 where a = ?", "bonjour").ScanOne(&b); err != nil {
	log.Fatal("cannot find value matching \"bonjour\"")
}
fmt.Println(b)

Output:

hello world
bonjour monde
hola mundo
monde

Index

Examples

Constants

View Source 
const MemoryPath = "file::memory:?mode=memory"
View Source 
const NoMoreRow C.int = -1

Variables

View Source 
var BusyTransaction = errText[C.SQLITE_BUSY]
View Source 
var DBName = "main"

DBName is the name of the database used in the application (main by default).

It can be changed before a back-up is started to reflect the application specificities.

View Source 
var DuplicateRecords = errors.New("duplicate records in scanone")
View Source 
var ErrNull = errors.New("null SQL value")

ErrNull is a sentinel value that can be returned by functions that want to return (or otherwise handle) null values

View Source 
var HashSeed = maphash.MakeSeed()

HashSeed is a convenience seed to use when implementing the Hasher interface.

View Source 
var NumThreads = 32
View Source 
var ObsLastQueries = RegisterTable("obs_lastqueries", queryRecord{})

Functions

func BackupDB 

func BackupDB(pool *Connections, dest string) error

BackupDB performs an [online backup] to dest

[online backup] https://www.sqlite.org/backup.html

func GetBuffer 

func GetBuffer() *bytes.Buffer

GetBuffer returns a bytes buffer from a system-wide pool. It is useful to avoid too much allocation when serializing. The buffer must be release with ReturnBuffer.

func HashString 

func HashString(s string) int64

HashString returns a unique hash, valid as an index

func IsComplete 

func IsComplete(st string) bool

IsComplete returns true iff the statement is complete

func LoadExtensions 

func LoadExtensions(db SQLITE3)

LoadExtensions loads all registered extensions against the database db. This function is automatically called when Open is, and is made available only for modules compiled as a shared library.

func Register 

func Register(f ...func(SQLITE3))

Register adds a new statically compiled extension to register when a new SQLite connection is opened. It should be called in a sub-package init function.

Most of the time, f is going to be returned from RegisterTable or RegisterFunc.

If not, due to the way CGO handles names bug-13467, callers need to wrap this in an unsafe pointer: 

(*C.sqlite3)(unsafe.Pointer(db))

func RegisterConstant 

func RegisterConstant(name string, value any) func(SQLITE3)

RegisterConstants creates a virtual table which always returns the same value

func RegisterFunc 

func RegisterFunc(name string, t any) func(SQLITE3)

RegisterFunc binds a Go function as an application-defined SQL function. Functions can only be exposed if:

  1. Their inputs arguments are integer, text, bytes or a pointer to an arbitrary type
  2. Their name is not a registered SQL keyword
  3. They return either (1) no argument, (2) a single argument of type integer, text, bytes, error or PointerValue or (3) 2 arguments of which the first argument must be of type integer, text, bytes or PointerValue the second argument must be of type erorr.

func RegisterTable 

func RegisterTable[T Filtrer[T]](name string, vt T, opts ...VirtualTableOption) func(SQLITE3)

RegisterTable creates a virtual table at name. The virtual table definition is read from T, see Filtrer for more details.

Example

Virtual tables can represent much more than data on disk

In this example a virtual table is used to get the time in a local-dependent fashion. 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/TroutSoftware/sqlite"
)

// whatTimeIsIt defines the structure of the virtual table.
// Location is set as:
//   - filtered, so it will be made available to the Filter method
//   - hidden, so it can be called as a function in SQL
//
// Typical code will want to annotate those structures with a call to generate accessors to all interesting fields.
//
//	//go:generate constrainer whatTimeIsIt
type whatTimeIsIt struct {
	Location string `vtab:"location,filtered,hidden"`
	Time     string `vtab:"time"`
}

var epoch = time.Date(2000, time.January, 1, 0, 0, 0, 0, time.UTC)

// WhatTimeIsItAPI provides the (very important) service to know a point in time at a given location.
func WhatTimeIsItAPI(where *time.Location) string {
	return epoch.In(where).Format(time.Kitchen)
}

// This function is typically generated in its own file whatTimeIsIt_access.go.
func (r whatTimeIsIt) GetLocation(cs sqlite.Constraints) (v string, ok bool) {
	match := 0
	for _, c := range cs {
		if c.Column == "location" {
			if c.Operation == sqlite.ConstraintEQ {
				v = c.ValueString()
				match++
			} else {
				panic("Value getter with non-constrained values")
			}
		}
	}
	if match == 0 {
		return v, false
	}
	if match == 1 {
		return v, true
	}

	panic("more than one match")
}

func (wtc whatTimeIsIt) Filter(_ int, cs sqlite.Constraints) sqlite.Iter[whatTimeIsIt] {
	loc := time.UTC
	if lspec, ok := wtc.GetLocation(cs); ok {
		var err error
		if loc, err = time.LoadLocation(lspec); err != nil {
			return sqlite.FromError[whatTimeIsIt](err)
		}
	}

	now := WhatTimeIsItAPI(loc)
	return sqlite.FromOne(whatTimeIsIt{
		Location: loc.String(),
		Time:     now,
	})
}

/*
Virtual tables can represent much more than data on disk

In this example a virtual table is used to get the time in a local-dependent fashion.
*/
func main() {
	db, err := sqlite.Open(sqlite.MemoryPath, sqlite.RegisterTable("whattimeisit", whatTimeIsIt{}))
	if err != nil {
		log.Fatal(err)
	}

	var kitchenclock string
	err = db.Exec(context.Background(), "select time from whattimeisit('Europe/Dublin')").ScanOne(&kitchenclock)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("epoch time", kitchenclock)
}

Output:

epoch time 12:00AM

func RememberQuery 

func RememberQuery(q string)

RememberQuery can be used to expose any SQL query to the internal `obs_lastqueries` virtual table. The last 30 queries will be displayed there. This is particularly useful when debugging automatically generated queries.

func ReturnBuffer 

func ReturnBuffer(b *bytes.Buffer)

ReturnBuffer returns the buffer to the pool. The buffer must not be used afterwards (this also mean the bytes returned from [bytes.buffer.Bytes]).

Types

type ColStrings 

type ColStrings map[string]*string

ColStrings augments MultiString with the name of the columns. the pointer type is required to comply with Go’s addressability constraints on maps.

type Conn 

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

Conn is a connection to a given database. Conn is safe for concurrent use.

Internally, Conn maps to an sqlite3 object, and present the same characteristics when not documented otherwise.

func Open 

func Open(name string, exts ...func(SQLITE3)) (*Conn, error)

Open creates a new database connection stored at name, and load exts.

func (*Conn) Close 

func (c *Conn) Close() error

func (*Conn) CreateBLOB 

func (cn *Conn) CreateBLOB(database, table, column string, size int) (*DirectBLOB, error)

CreateBLOB creates a new incremental I/O buffer. The database, table, and column are only quoted, and therefore should not be from untrusted inputs.

func (*Conn) Exec 

func (c *Conn) Exec(ctx context.Context, cmd string, args ...any) *Rows

Exec executes cmd, optionally binding args to parameters in the query. Rows are a a cursor over the results, and the first row is already executed: commands needs not call Rows.Next afterwards. Arguments are matched by position.

This function will panic if the command is invalid SQL. This is intented for static SQL statements (written in your code), not for untrusted SQL.

func (*Conn) OpenBLOB 

func (cn *Conn) OpenBLOB(database, table, column string, rownum int) (*DirectBLOB, error)

func (*Conn) Release 

func (ctn *Conn) Release(ctx context.Context) error

Release returns the given savepoint. It is safe to call this after Rollback

func (*Conn) Rollback 

func (ctn *Conn) Rollback(ctx context.Context) error

Rollback returns the given savepoint. It is safe to call this after Rollback

func (*Conn) Savepoint 

func (ctn *Conn) Savepoint(ctx context.Context) (context.Context, error)

Savepoint creates a new savepoint in transaction (think about begin). It is the responsibility of the caller to Conn.Release it.

Most of the time, the implementation of [Pool.Savepoint] should be preferred.

type Connections 

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

func OpenPool 

func OpenPool(name string, exts ...func(SQLITE3)) (*Connections, error)

OpenPool ceates a new connection pool TODO(rdo) check if WAL by default is right

func (*Connections) Close 

func (p *Connections) Close() error

Close closes all connections in the pool. It can be safely called concurrently Connections.Savepoint, Connections.Exec and Connections.Release but note that calls to Connections.Savepoint or Connections.Exec that happen after Close might block forever. The mechanism to terminate other connections has to be done out of band.

func (*Connections) Exec 

func (p *Connections) Exec(ctx context.Context, cmd string, args ...any) *Rows

func (*Connections) Release 

func (p *Connections) Release(ctx context.Context) error

func (*Connections) Rollback 

func (p *Connections) Rollback(ctx context.Context) error

Rollback rolls back all changes to the current changepoint. The rollback will not happen if the savepoint is already released; it is safe to call this from a defer.

func (*Connections) Savepoint 

func (p *Connections) Savepoint(ctx context.Context) (context.Context, error)

Savepoint creates a new savepoint in transaction (think about begin). If the connection does not exist, it is taken from the pool.

type Constraint 

type Constraint struct {
	Column    string
	Operation Op
	Value     any
	// contains filtered or unexported fields
}

A constraint is a filter (technically can also be a join, …) passed by the engine to the API implementation. The Value is only set at filter stage, not when the plan is created. Following SQLite weakly typed system, it is not possible to know the Value without checking its type explicitly, or instead using the accessor functions.

func (Constraint) ValueBool 

func (v Constraint) ValueBool() bool

func (Constraint) ValueBytes 

func (v Constraint) ValueBytes() []byte

func (Constraint) ValueInt 

func (v Constraint) ValueInt() int

func (Constraint) ValueString 

func (v Constraint) ValueString() string

type Constraints 

type Constraints []Constraint

Constraints is a type wrapper around an array of constraints.

type DirectBLOB 

type DirectBLOB struct {
	RowNumber int
	// contains filtered or unexported fields
}

ReadWriterAt is an incremental I/O buffer. It is mostly useful to work directly with binary formats in SQLite – so akin to mmap.

func (*DirectBLOB) Close 

func (r *DirectBLOB) Close() error

func (*DirectBLOB) ReadAt 

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

func (*DirectBLOB) Size 

func (r *DirectBLOB) Size() int

func (*DirectBLOB) WriteAt 

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

type Filtrer 

type Filtrer[T any] interface {
	Filter(int, Constraints) Iter[T]
}

A resource represents an abstraction over a signal that can be represented as a virtual table.

The T type is bound in the RegisterTable method to the values returned in the iterator. We use tags to associate values automatically with virtual table columns: 

// Field will be bound as column “myName”
Field int `vtab:"myName"`

// Field will be bound as “myName” and
// the query will fail if “myName” is not specified in the WHERE clause
Field int `vtab:"myName,required"

// Field is ignored by this package
Field int `vtab:"-"

The columns values allocated by standard SQLite affinity.

Query plan

Resources interact with query plan generation through a simple algorithm: each column filtered reduces by a constant factor the cost of the query (thus having multiple columns filtered increases the chance of the plan being selected). Filtered, required and hidden field are considered equal for the query plan. Furthermore, only ConstraintEQ match are accepted.

For more control over the plan, implement directly Indexer instead.

Concurrency

Filter is called against the global variable used in registering the virtual table. The global variable can be used to set constant values, including channels if needed.

Mutation in the global variable should be avoided in the general case; the implementation does not try to limit or otherwise protect concurrent access.

Avoid blocking in the Filter method, as this would impact all virtual tables. Instead, if a form of rate limit is required, prefer returning a well-known error.

type Hasher 

type Hasher interface {
	Hash64() int64
}

Hasher can be implemented by resources that exhibit referential transparency.

Databases use the idea of a primary key (or row ID), a unique identifier for the relation. When the relation is dynamic, it is not generally possible to express this identity; and by default, all relations in a virtual table are considered to have a different primary key. Implementations that want to overwrite this behavior should implement this interface.

type IndexState 

type IndexState interface {
	Use(constraint int)
	SelectIndex(int)
}

IndexState is used by the indexer to specify fields for a given access

type Indexer 

type Indexer interface {
	// The [IndexState] parameter will configure which constraints are passed to [Filter]:
	//   - the index number (arbitrary from the point of the of the driver) set by [IndexState.SelectIndex]
	//   - the arguments from columns in the order from [IndexState.Use]
	BestIndex(IndexState, Constraints) (estimatedCost, estimatedRows int64)
}

Indexer allow types to implement their own index access method.

type Iter 

type Iter[T any] interface {
	Next() bool
	Err() error
	Value() T
}

Iter represents an iterator over multiple results of an API. Implementations must be safe for concurrent use. The iterator is going to be primed by calling Next before any value is requested.

func FromArray 

func FromArray[T any](values []T) Iter[T]

FromArray returns an iterator over values

func FromError 

func FromError[T any](err error) Iter[T]

FromError returns an iterator with err

func FromOne 

func FromOne[T any](v T) Iter[T]

FromOne returns an iterator with a single value v

func None 

func None[T any]() Iter[T]

None returns the empty iterator

func TransformArray 

func TransformArray[T, U any](values []T, trans func(T) U) Iter[U]

TransformArray iterates over applying trans to values

type MultiString 

type MultiString []string

MultiString is used to read all values of a statement as string. This is useful if you don’t know ahead of time the values returned.

type NullString 

type NullString string

NullString as a marker type: strings of length 0 will be set as null instead

type Op 

type Op uint8

Op are the type of filtering operations that the engine can ask 

const (
	ConstraintEQ        Op = 2
	ConstraintGT        Op = 4
	ConstraintLE        Op = 8
	ConstraintLT        Op = 16
	ConstraintGE        Op = 32
	ConstraintMATCH     Op = 64
	ConstraintLIKE      Op = 65
	ConstraintGLOB      Op = 66
	ConstraintREGEXP    Op = 67
	ConstraintNE        Op = 68
	ConstraintISNOT     Op = 69
	ConstraintISNOTNULL Op = 70
	ConstraintISNULL    Op = 71
	ConstraintIS        Op = 72
	ConstraintLIMIT     Op = 73
	ConstraintOFFSET    Op = 74
	ConstraintFUNCTION  Op = 150
)

func (*Op) Scan 

func (op *Op) Scan(st fmt.ScanState, verb rune) error

func (Op) String 

func (op Op) String() string

type PointerValue 

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

Marker type for values that should be passed as pointer. Most users will prefer AsPointer.

func AsPointer 

func AsPointer(v any) PointerValue

AsPointer is used to pass the value using the SQLite pointer passing interface. This interface is only useful for reading the value in virtual tables or functions. The “pointer type” parameter will be derived from the underlying data type name.

type Rows 

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

Rows are iterator structure over the underlying database statement results.

Rows are lightweigth object, and should not be reused after Rows.Err or Rows.ScanOne calls.

func (*Rows) Bytecode 

func (r *Rows) Bytecode() string

Bytecodes outputs the detailed query plan through the SQLite bytecode operands. This is a fairly low-level operation, and is only really useful for troubleshooting. Even then, Rows.ExplainQueryPlan is usually a much better option.

func (*Rows) ColumnName 

func (rows *Rows) ColumnName(i int) string

ColumnName return the ith (zero-based) column name. This is mostly convenient in a loop: 

for i := 0; i < st.NumColumn(); i++ {
	buf.WriteString(st.ColumnName(i) + "\t")
}

func (*Rows) Err 

func (r *Rows) Err() error

Err finalizes the statement, and return an error if any. Rows should not be used after this.

func (*Rows) ExplainQueryPlan 

func (r *Rows) ExplainQueryPlan() string

QueryPlan returns the query plan of the existing statement under a graphical form.

func (*Rows) IsExplain 

func (r *Rows) IsExplain() bool

IsExplain returns true iff the statement is an explain query plan command. This is the most important interface for debugging.

func (*Rows) Next 

func (rows *Rows) Next() bool

Next advances the cursor to the next result in the set. It returns false if there are no more results, or if an error, or a timeout occur. Use Rows.Err to disambiguate between those cases.

func (*Rows) NumColumn 

func (rows *Rows) NumColumn() int

NumColumn returns the count of columns returned by the current statement. Use Rows.ColumnName if the name is useful.

func (*Rows) Scan 

func (rows *Rows) Scan(dst ...any)

Scan unmarshals the underlying SQLite value into a Go value. Values in dst are matched by position against the columns in the query, e.g. 

 rows := ctn.Exec(ctx, "select a, b from t")
 for rows.Next() {
	var c, d string
	rows.Scan(&c, &d)
	// c -> column a
	// d -> column b
}

Scan defers errors to the Rows.Err method (but note that Rows.Next will stop at the first error).

Conversion is done depending on the SQLite type affinity and the type in Go. Richer Go types (e.g. bytes.Buffer, or time.Time) are automatically read too. Read the code for the full map – but if you’re relying on this, you are probably already doing something too smart.

func (*Rows) ScanOne 

func (r *Rows) ScanOne(dst ...any) error

ScanOne is a convenient shortcut over Rows.Scan, returning the first value. DuplicateRecords will be returned if more than one record match.

type SQLITE3 

type SQLITE3 *C.sqlite3

SQLITE3 wraps a C pointer type for export. See Register for information about use.

type Updater 

type Updater[T any] interface {
	Hasher
	Update(_ context.Context, index int64, value T) error
}

Updater exposes types to modifications through SQL commands.

Depending on the operation, not all fields are present:

  • during an insert, only the value is set
  • during a delete, only the index is set
  • during an update, both the index and the value is set

The first context carries the current connection, it must be used if the update requires writing to the DB.

type VirtualTableOption 

type VirtualTableOption func(*rVM)

VirtualTableOption allows customisation of the virtual table behavior.

func OverloadFunc 

func OverloadFunc(name string, fn any) VirtualTableOption

OverloadFunc permits function overloading in a virtual table.

If the function takes two arguments, and returns either a boolean, or a boolean or an error, it will be used as a hint to filtering. In this case, the operation in the constraint will be equal to ConstraintFunc + index, with index the position in the overloaded functions (_including_ non-indexing ones).

Source Files

View all Source files

Directories

Path Synopsis
cmd
constrainer
Constrainer is a tool to generate acces methods from virtual tables types definitions.
 Constrainer is a tool to generate acces methods from virtual tables types definitions.
stability
Package stability provides an API to validate the stability of persisted data structures.
 Package stability provides an API to validate the stability of persisted data structures.

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.