sqinn

package
v1.2.0 Latest Latest
Warning

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

 Go to latest Published: Oct 5, 2023 License: Unlicense Imports: 7 Imported by: 2

Documentation

Overview

Package sqinn provides interface to SQLite databases in Go without cgo.

It uses Sqinn (http://github.com/cvilsmeier/sqinn) for accessing SQLite databases. It is not a database/sql driver.

Basic Usage

The following sample code opens a database, inserts some data, queries it, and closes the database. Error handling is left out for brevity. 

import "github.com/cvilsmeier/sqinn-go/sqinn"

func main() {

	// Launch sqinn.
	sq, _ := sqinn.Launch(sqinn.Options{})
	defer sq.Terminate()

	// Open database.
	sq.Open("./users.db")
	defer sq.Close()

	// Create a table.
	sq.ExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)")

	// Insert users.
	sq.ExecOne("INSERT INTO users (id, name) VALUES (1, 'Alice')")
	sq.ExecOne("INSERT INTO users (id, name) VALUES (2, 'Bob')")

	// Query users.
	rows, _ := sq.Query("SELECT id, name FROM users ORDER BY id", nil, []byte{sqinn.ValInt, sqinn.ValText})
	for _, row := range rows {
		fmt.Printf("id=%d, name=%s\n", row.Values[0].AsInt(), row.Values[1].AsString())
	}

	// Output:
	// id=1, name=Alice
	// id=2, name=Bob
}

Parameter Binding

For Exec: 

nUsers := 3
nParamsPerUser := 2
sq.Exec("INSERT INTO users (id, name) VALUES (?, ?)", nUsers, nParamsPerUser, []any{
	1, "Alice",
	2, "Bob",
	3, nil,
})

For Query: 

// Query users where id < 42.
rows, err := sq.Query(
	"SELECT id, name FROM users WHERE id < ? ORDER BY name",
	[]any{42},                   // WHERE id < 42
	[]byte{sqinn.ValInt, sqinn.ValText}, // two columns: int id and string name
)

Options

Sqinn searches the sqinn binary in the $PATH environment. You can customize that behavior by specifying the path to sqinn explicitly when launching sqinn. 

sq, _ := sqinn.Launch(sqinn.Options{
	SqinnPath: "C:/projects/wombat/bin/sqinn.exe",
})

// or, even better

sq, _ := sqinn.Launch(sqinn.Options{
	SqinnPath: os.Getenv("SQINN_PATH"),
})

The sqinn subprocess prints debug and error messages on its stderr. You can consume it by setting a sqinn.Logger. 

sq, _ := sqinn.Launch(sqinn.Options{
	Logger: sqinn.StdLogger{},
})

See the sqinn.Logger docs for more details.

Low-level Functions

Sqinn implements many of SQLite's C API low-level functions prepare(), finalize(), step(), etc. Although made available, we recommend not using them. Instead, use Exec and Query. Most (if not all) database tasks can be accomplished with Exec and Query.

Example (Basic) 
package main

import (
	"fmt"
	"log"
	"os"

	"github.com/cvilsmeier/sqinn-go/sqinn"
)

func main() {

	// Find sqinn
	sqinnPath := os.Getenv("SQINN_PATH")
	if sqinnPath == "" {
		log.Printf("SQINN_PATH not set, please install sqinn and set SQINN_PATH")
		return
	}

	// Launch sqinn. Terminate at program exit.
	sq := sqinn.MustLaunch(sqinn.Options{
		SqinnPath: sqinnPath,
	})
	defer sq.Terminate()

	// Open database. Close when we're done.
	sq.MustOpen(":memory:")
	defer sq.Close()

	// Create a table.
	sq.MustExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)")

	// Insert users.
	sq.MustExecOne("INSERT INTO users (id, name) VALUES (1, 'Alice')")
	sq.MustExecOne("INSERT INTO users (id, name) VALUES (2, 'Bob')")

	// Query users.
	rows := sq.MustQuery("SELECT id, name FROM users ORDER BY id", nil, []byte{sqinn.ValInt, sqinn.ValText})
	for _, row := range rows {
		fmt.Printf("%d %q\n", row.Values[0].AsInt(), row.Values[1].AsString())
	}
}

Output:

1 "Alice"
2 "Bob"
Example (HandlingNullValues) 
package main

import (
	"fmt"
	"os"

	"github.com/cvilsmeier/sqinn-go/sqinn"
)

func main() {
	// Launch sqinn. Env SQINN_PATH must point to sqinn binary.
	sq := sqinn.MustLaunch(sqinn.Options{
		SqinnPath: os.Getenv("SQINN_PATH"),
	})
	defer sq.Terminate()

	// Open database.
	sq.MustOpen(":memory:")
	defer sq.Close()

	// Create table
	sq.MustExecOne("CREATE TABLE names (val TEXT)")

	// Insert 2 rows, the first is non-NULL, the second is NULL
	sq.MustExecOne("BEGIN")
	sq.MustExec(
		"INSERT INTO names (val) VALUES (?)",
		2, // insert 2 rows
		1, // each row has 1 column
		[]any{
			"wombat", // first row is 'wombat'
			nil,      // second row is NULL
		},
	)
	sq.MustExecOne("COMMIT")

	// Query rows
	rows := sq.MustQuery(
		"SELECT val FROM names ORDER BY val",
		nil,                   // no query parameters
		[]byte{sqinn.ValText}, // one column of type TEXT
	)
	for _, row := range rows {
		stringValue := row.Values[0].String
		if stringValue.IsNull() {
			fmt.Printf("NULL\n")
		} else {
			fmt.Printf("%q\n", stringValue.Value)
		}
	}
}

Output:

NULL
"wombat"
Example (ParameterBinding) 
package main

import (
	"fmt"
	"log"
	"os"

	"github.com/cvilsmeier/sqinn-go/sqinn"
)

func main() {
	// Find sqinn
	sqinnPath := os.Getenv("SQINN_PATH")
	if sqinnPath == "" {
		log.Printf("SQINN_PATH not set, please install sqinn and set SQINN_PATH")
		return
	}

	// Launch sqinn.
	sq := sqinn.MustLaunch(sqinn.Options{
		SqinnPath: sqinnPath,
	})
	defer sq.Terminate()

	// Open database.
	sq.MustOpen(":memory:")
	defer sq.Close()

	// Create table
	sq.MustExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)")

	// Insert 3 rows at once
	sq.MustExecOne("BEGIN")
	sq.MustExec(
		"INSERT INTO users (id, name) VALUES (?,?)",
		3, // insert 3 rows
		2, // each row has 2 columns
		[]any{
			1, "Alice", // bind first row
			2, "Bob", // bind second row
			3, nil, // third row has no name
		},
	)
	sq.MustExecOne("COMMIT")

	// Query rows
	rows := sq.MustQuery(
		"SELECT id, name FROM users WHERE id < ? ORDER BY id ASC",
		[]any{42},                           // WHERE id < 42
		[]byte{sqinn.ValInt, sqinn.ValText}, // two columns: int id, string name
	)
	for _, row := range rows {
		fmt.Printf("%d %q\n", row.Values[0].AsInt(), row.Values[1].AsString())
	}
}

Output:

1 "Alice"
2 "Bob"
3 ""
Example (SqliteSpecialties) 
package main

import (
	"os"
	"path/filepath"

	"github.com/cvilsmeier/sqinn-go/sqinn"
)

func main() {
	// Launch sqinn. Env SQINN_PATH must point to sqinn binary.
	sq := sqinn.MustLaunch(sqinn.Options{
		SqinnPath: os.Getenv("SQINN_PATH"),
	})
	defer sq.Terminate()

	// Open database.
	sq.MustOpen(":memory:")
	defer sq.Close()

	// Enable foreign keys, see https://sqlite.org/pragma.html#pragma_foreign_keys
	sq.MustExecOne("PRAGMA foreign_keys = 1")

	// Set busy_timeout, see https://sqlite.org/pragma.html#pragma_busy_timeout
	sq.MustExecOne("PRAGMA busy_timeout = 10000")

	// Enable WAL mode, see https://sqlite.org/pragma.html#pragma_journal_mode
	sq.MustExecOne("PRAGMA journal_mode = WAL")

	// Enable NORMAL sync, see https://sqlite.org/pragma.html#pragma_synchronous
	sq.MustExecOne("PRAGMA synchronous = NORMAL")

	// Make a backup into a temp file
	filename := filepath.Join(os.TempDir(), "db_backup.sqlite")
	os.Remove(filename) // remove in case it exists, sqlite does not want to overwrite
	sq.MustExec("VACUUM INTO ?", 1, 1, []any{
		filename,
	})

}

Output:

Index

Examples

Constants

View Source 
const (

	// ValNull represents the NULL value (Go nil)
	ValNull byte = 0

	// ValInt represents a Go int
	ValInt byte = 1

	// ValInt64 represents a Go int64
	ValInt64 byte = 2

	// ValDouble represents a Go float64
	ValDouble byte = 6 // the IEEE variant

	// ValText represents a Go string
	ValText byte = 4

	// ValBlob represents a Go []byte
	ValBlob byte = 5
)

Value types for binding query parameters and retrieving column values.

Variables

This section is empty.

Functions

This section is empty.

Types

type AnyValue 

type AnyValue struct {
	Int    IntValue    // a nullable Go int
	Int64  Int64Value  // a nullable Go int64
	Double DoubleValue // a nullable Go float64
	String StringValue // a nullable Go string
	Blob   BlobValue   // a nullable Go []byte
}

An AnyValue can hold any value type.

func (AnyValue) AsBlob 

func (a AnyValue) AsBlob() []byte

AsBlob returns a []byte value or nil if it is NULL or the value is not a blob.

func (AnyValue) AsDouble 

func (a AnyValue) AsDouble() float64

AsDouble returns a double value or 0.0 if it is NULL or the value is not a double.

func (AnyValue) AsInt 

func (a AnyValue) AsInt() int

AsInt returns an int value, or 0 if it is not set (NULL), or the value is not an int.

func (AnyValue) AsInt64 

func (a AnyValue) AsInt64() int64

AsInt64 returns an int64 value or 0 if it is NULL or the value is not an int64.

func (AnyValue) AsString 

func (a AnyValue) AsString() string

AsString returns a string value or "" if it is NULL or the value is not a string.

type BlobValue 

type BlobValue struct {
	// Set is false if the value is NULL, otherwise true.
	Set bool

	// Value is the []byte value.
	Value []byte
}

A BlobValue holds a nullable []byte value. The zero value is not set (a.k.a. NULL).

func (BlobValue) IsNull added in v1.1.1 

func (v BlobValue) IsNull() bool

IsNull returns true if the value is NULL, otherwise true.

type DoubleValue 

type DoubleValue struct {

	// Set is false if the value is NULL, otherwise true.
	Set bool

	// Value is the float64 value.
	Value float64
}

A DoubleValue holds a nullable float64 value. The zero value is not set (a.k.a. NULL).

func (DoubleValue) IsNull added in v1.1.1 

func (v DoubleValue) IsNull() bool

IsNull returns true if the value is NULL, otherwise true.

type Int64Value 

type Int64Value struct {

	// Set is false if the value is NULL, otherwise true.
	Set bool

	// Value is the int64 value.
	Value int64
}

An Int64Value holds a nullable int64 value. The zero value is not set (a.k.a. NULL).

func (Int64Value) IsNull added in v1.1.1 

func (v Int64Value) IsNull() bool

IsNull returns true if the value is NULL, otherwise true.

type IntValue 

type IntValue struct {

	// Set is false if the value is NULL, otherwise true.
	Set bool

	// Value is the int value.
	Value int
}

An IntValue holds a nullable int value. The zero value is not set (a.k.a. NULL).

func (IntValue) IsNull added in v1.1.1 

func (v IntValue) IsNull() bool

IsNull returns true if the value is NULL, otherwise true.

type Logger 

type Logger interface {
	Log(s string)
}

A Logger logs error and debug messages coming from the stderr output of the sqinn child process.

type NoLogger 

type NoLogger struct{}

NoLogger does not log anything.

func (NoLogger) Log 

func (l NoLogger) Log(s string)

Log does nothing.

type Options 

type Options struct {

	// Path to Sqinn executable. Can be an absolute or relative path.
	// Empty is the same as "sqinn". Default is empty.
	SqinnPath string

	// Logger logs the debug and error messages that the sinn subprocess will output
	// on its stderr. Default is nil, which does not log anything.
	Logger Logger

	// Log the binary io protocol. Only for debugging. Should normally be false. Default is false.
	LogBinary bool
}

Options for launching a Sqinn instance.

type Row 

type Row struct {
	Values []AnyValue
}

A Row represents a query result row and holds a slice of values, one value per requested column.

type Sqinn 

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

Sqinn is a running sqinn instance.

func Launch 

func Launch(options Options) (*Sqinn, error)

Launch launches a new Sqinn subprocess. The options specify the path to the sqinn executable, among others. See docs for Options for details. If an error occurs, it returns (nil, err).

func MustLaunch added in v1.1.1 

func MustLaunch(options Options) *Sqinn

MustLaunch is like Launch except it panics on error.

func (*Sqinn) Bind 

func (sq *Sqinn) Bind(iparam int, value any) error

Bind binds the iparam'th parameter with the specified value. The value can be an int, int64, float64, string, []byte or nil. Not that iparam starts at 1 (not 0):

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/bind_blob.html.

func (*Sqinn) Changes 

func (sq *Sqinn) Changes() (int, error)

Changes counts the number of rows modified by the last SQL operation.

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/changes.html.

func (*Sqinn) Close 

func (sq *Sqinn) Close() error

Close closes the database connection that has been opened with Open. After Close has been called, this Sqinn instance can be terminated, or another database can be opened with Open.

For further details, see https://www.sqlite.org/c3ref/close.html.

func (*Sqinn) Column 

func (sq *Sqinn) Column(icol int, colType byte) (AnyValue, error)

Column retrieves the value of the icol'th column. The colType specifies the expected type of the column value. Note that icol starts at 0 (not 1).

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/column_blob.html.

func (*Sqinn) Exec 

func (sq *Sqinn) Exec(sql string, niterations, nparams int, values []any) ([]int, error)

Exec executes a SQL statement multiple times and returns the number of modified rows for each iteration. It supports bind parmeters. Exec is used to execute SQL statements that do not return results (see Query for those).

The niterations tells Exec how often to run the sql. It must be >= 0 and should be >= 1. If niterations is zero, the statement is not run at all, and the method call is a waste of CPU cycles.

Binding sql parameters is possible with the nparams and values arguments. The nparams argument tells Exec how many parameters to bind per iteration. nparams must be >= 0.

The values argument holds the parameter values. Parameter values can be of the following type: int, int64, float64, string, blob or nil. The length of values must always be niterations * nparams.

Internally, Exec preapres a statement, binds nparams parameters, steps the statement, resets the statement, binds the next nparams parameters, and so on, until niterations is reached.

Exec returns, for each iteration, the count of modified rows. The resulting int slice will always be of length niterations.

If an error occurs, it will return (nil, err).

func (*Sqinn) ExecOne 

func (sq *Sqinn) ExecOne(sql string) (int, error)

ExecOne executes a SQL statement and returns the number of modified rows. It is used primarily for short, simple statements that have no parameters and do not query rows. A good use case is for beginning and committing a transaction: 

_, err = sq.ExecOne("BEGIN");
// do stuff in tx
_, err = sq.ExecOne("COMMIT");

Another use case is for DDL statements: 

_, err = sq.ExecOne("DROP TABLE users");
_, err = sq.ExecOne("CREATE TABLE foo (name VARCHAR)");

ExecOne(sql) has the same effect as Exec(sql, 1, 0, nil).

If a error occurs, ExecOne will return (0, err).

func (*Sqinn) Finalize 

func (sq *Sqinn) Finalize() error

Finalize finalizes a statement that has been prepared with Prepare. To avoid memory leaks, each statement has to be finalized. Moreover, since Sqinn allows only one statement at a time, each statement must be finalized before a new statement can be prepared.

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/finalize.html.

func (*Sqinn) IoVersion 

func (sq *Sqinn) IoVersion() (byte, error)

IoVersion returns the protocol version for this Sqinn instance. The version is >= 1. If an error occurs, it returns (0, err).

func (*Sqinn) MustExec 

func (sq *Sqinn) MustExec(sql string, niterations, nparams int, values []any) []int

MustExec is like Exec except it panics on error.

func (*Sqinn) MustExecOne 

func (sq *Sqinn) MustExecOne(sql string) int

MustExecOne is like ExecOne except it panics on error.

func (*Sqinn) MustOpen added in v1.1.1 

func (sq *Sqinn) MustOpen(filename string)

MustOpen is like Open except it panics on error.

func (*Sqinn) MustQuery 

func (sq *Sqinn) MustQuery(sql string, values []any, colTypes []byte) []Row

MustQuery is like Query except it panics on error.

func (*Sqinn) Open 

func (sq *Sqinn) Open(filename string) error

Open opens a database. The filename can be ":memory:" or any filesystem path, e.g. "/tmp/test.db". Sqinn keeps the database open until Close is called. After Close has been called, this Sqinn instance can be terminated with Terminate, or Open can be called again, either on the same database or on a different one. For every Open there should be a Close call.

For further details, see https://www.sqlite.org/c3ref/open.html.

func (*Sqinn) Prepare 

func (sq *Sqinn) Prepare(sql string) error

Prepare prepares a statement, using the provided sql string. To avoid memory leaks, each prepared statement must be finalized after use. Sqinn allows only one prepared statement at at time, preparing a statement while another statement is still active (not yet finalized) will result in a error.

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/prepare.html.

func (*Sqinn) Query 

func (sq *Sqinn) Query(sql string, params []any, colTypes []byte) ([]Row, error)

Query executes a SQL statement and returns all rows. Query is used for SELECT statements.

The params argument holds a list of bind parameters. Values must be of type int, int64, float64, string, []byte or nil.

The colTypes argument holds a list of column types that the query yields.

Query returns all resulting rows at once. There is no way to interrupt a Query while it is running. If a Query yields more data than can fit into memory, the behavior is undefined, most likely an out-of-memory condition will crash your program. It is up to the caller to make sure that all queried data fits into memory. The sql 'LIMIT' operator may be helpful.

Each returned Row contains a slice of values. The number of values per row is equal to the length of colTypes.

If an error occurs, it will return (nil, err).

func (*Sqinn) Reset 

func (sq *Sqinn) Reset() error

Reset resets the current statement to its initial state.

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/reset.html.

func (*Sqinn) SqinnVersion 

func (sq *Sqinn) SqinnVersion() (string, error)

SqinnVersion returns the version of the Sqinn executable. If an error occurs, it returns ("", err).

func (*Sqinn) SqliteVersion 

func (sq *Sqinn) SqliteVersion() (string, error)

SqliteVersion returns the SQLite library version Sqinn was built with. If an error occurs, it returns ("", err).

func (*Sqinn) Step 

func (sq *Sqinn) Step() (bool, error)

Step advances the current statement to the next row or to completion. It returns true if there are more rows available, false if not.

This is a low-level function. Use Exec/Query instead.

For further details, see https://www.sqlite.org/c3ref/step.html.

func (*Sqinn) Terminate 

func (sq *Sqinn) Terminate() error

Terminate terminates a running Sqinn instance. Each launched Sqinn instance should be terminated with Terminate. After Terminate has been called, this Sqinn instance must not be used any more.

type StdLogger 

type StdLogger struct {

	// Logger will be used for writing log outputs.
	// If Logger is nil, the log package default output will be used.
	Logger *log.Logger
}

A StdLogger logs to a stdlib log.Logger or to the log's standard logger.

func (StdLogger) Log 

func (l StdLogger) Log(s string)

Log logs s to a log.Logger or to the default log output.

type StringValue 

type StringValue struct {

	// Set is false if the value is NULL, otherwise true.
	Set bool

	// Value is the string value.
	Value string
}

A StringValue holds a nullable string value. The zero value is not set (a.k.a. NULL).

func (StringValue) IsNull added in v1.1.1 

func (v StringValue) IsNull() bool

IsNull returns true if the value is NULL, otherwise true.

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.