sqliter

Documentation

Overview

Package sqliter provides partitioning, cached prepared statements and data conversion for SQLite3.

• SQLite3 databases are semlessly created by
• — using DSNrFactory.DataSourceNamer with an application name and
• — executing queries from github.com/haraldrudell/parl/psql.DBFactory.NewDB cached DB objects and
• — a partition ID
• statement-retry remedy for SQLite3 concurrency shortcomings
• — concurrency errors are not returned, there is a queueing implementation for retries
• — this convenience is semalessly provided by github.com/haraldrudell/parl/psql.NewDBMap

conversions for:

• — Go time.Time to SQLite3 TEXT strict iso8601 UTC time-zone, nanosecond precision TimeToDB TimeToDBNullable ToTime NullableToTime
• — Go bool to SQLite3 INTEGER BoolToDB ToBool
• — Go time.Time to SQLite3’s DATETIME TEXT iso8601-like format millisecond precision TimeToDATETIME DATETIMEtoTime
• — Go uuid.UUID to SQLite3 TEXT UUIDToDB UUIDToDB

additionally:

• retrieval of pragma, ie. SQLite3 performance-related database configuration Pragma PragmaDB PragmaSQL
• data-source namer for application name and year partition DSNrFactory.DataSourceNamer OpenDataSourceNamer
• data-source that do not create database-files OpenDataSourceNamerRO for querying existing databases
• retrieval of actionable SQLite3 error codes Code

Index

• Constants
• Variables
• func BoolToDB(b bool) (dbValue int)
• func DATETIMEtoTime(sqliteText string) (t time.Time, err error)
• func MarkDsnNotExist(err error) (err2 error)
• func NullableToTime(nullString sql.NullString) (t time.Time, err error)
• func OpenDataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)
• func OpenDataSourceNamer(appName string) (dsnr parl.DataSourceNamer, err error)
• func OpenDataSourceNamerRO(appName string) (dsn parl.DataSourceNamer, err error)
• func Pragma(dataSource parl.DataSource, ctx context.Context) (pragmas parl.Pragmas, err error)
• func PragmaDB(db parl.DB, partition parl.DBPartition, ctx context.Context) (pragmas parl.Pragmas, err error)
• func PragmaSQL(sqlDB *sql.DB, ctx context.Context) (pragmas parl.Pragmas, err error)
• func TimeToDATETIME(t time.Time) (dateTime string)
• func TimeToDB(t time.Time) (dbValue string)
• func TimeToDBNullable(t time.Time) (dbValue any)
• func ToBool(dbValue int) (b bool, err error)
• func ToTime(timeString string) (t time.Time, err error)
• func ToUUID(IDString string) (ID uuid.UUID, err error)
• func UUIDToDB(ID uuid.UUID) (dbValue string)
• type DataSource
  • func (ds *DataSource) WrapStmt(stmt *sql.Stmt) (stm psql2.Stmt)
• type DataSourceNamer
  • func (n *DataSourceNamer) DSN(year ...parl.DBPartition) (dsnr parl.DataSourceName)
  • func (n *DataSourceNamer) DataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)
• type DataSourceNamerRO
  • func (n *DataSourceNamerRO) DataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)
• type ErrorCode
  • func Code(err error) (code int, sqliteError ErrorCode)
• type Stmt
  • func (st *Stmt) ExecContext(ctx context.Context, args ...any) (sqlResult sql.Result, err error)
  • func (st *Stmt) QueryContext(ctx context.Context, args ...any) (sqlRows *sql.Rows, err error)
  • func (st *Stmt) QueryRowContext(ctx context.Context, args ...any) (sqlRow *sql.Row)

Constants

const (
	// SQLite3 special filename for in-memory databases
	//	- default [In-Memory Databases] name is “:memory:”
	//	- if two threads or a query while another query is read,
	//		a second database connection is opened which uses a different database
	//	- from SQLite3 [version] 3.7.13+, multiple connections may
	//		share in-memory database
	//	- use URI filename: “file::memory:?cache=shared”
	//	- Go package modernc.org/sqlite 1.30.1 240606
	//		is pure Go code-compatible with SQLite 3.46.0
	//	- the in-memory filename provided here does not support
	//		partitioning.
	//		It is used for testing
	//	- because Go may open parallel database connections
	//		at any time, use of legacy filename “:memory:”
	//		produces unpredictable results
	//
	// [In-Memory Databases]: https://sqlite.org/inmemorydb.html
	// [version]: https://stackoverflow.com/questions/36447766/multiple-sqlite-connections-to-a-database-in-memory#comment60508526_36447766
	SQLiteMemoryDataSourceName = "file::memory:?cache=shared"
	// name of the SQLite3 database driver
	//	- “modernc.org/sqlite”
	SQLiteDriverName = "sqlite"
)

const (
	CodeBusy                    = 5    // sqlite3.SQLITE_BUSY
	CodeConstraintFailedNOTNULL = 1299 // SQLITE_CONSTRAINT_NOTNULL
	CodeConstraintFailedUNIQUE  = 2067 // SQLITE_CONSTRAINT_UNIQUE
	CodeDatabaseIsLocked        = 261  // locked WAL file
)

SQLite errors are of type sqlite.Error

• sqlite.Error has a code int value
• Some code values are not exported
• For calling code to not have to import the driver itself, frequent error-code values are here

Variables

var DSNrFactory = &dSNrFactory{}

DSNrFactory provides an abstract factory method for an SQLite3 data-source namer

var ErrDsnNotExist = &dsnNotExist{error: errors.New("ErrDsnNotExist")}

ErrDsnNotExist can detect DSN not exist errors

Usage:

if errors.Is(err, sqliter.ErrDsnNotExist) {

var MemDsnr = &memDsnr{}

MemDsnr is a data source namer always returning an in-memory data source

Functions

func BoolToDB

func BoolToDB(b bool) (dbValue int)

BoolToDB converts Go bool to SQLite INTEGER

• Go reflect type is bool
• SQLite storage class for Boolean Datatype is INTEGER
• SQLite values used are 0 or 1

func DATETIMEtoTime

func DATETIMEtoTime(sqliteText string) (t time.Time, err error)

DATETIMEtoTime converts SQLite DATETIME to Go time.Time with location time.Local

• better to use is TimeToDB and ToTime using iso8601 utc time-zone with nanosecond precision
• database stores as TEXT in UTC, millisecond precision
• “2019-01-01 21:30:42.000 +00:00”

func MarkDsnNotExist

func MarkDsnNotExist(err error) (err2 error)

MarkDsnNotExist marks error as cased by read-only DSN

func NullableToTime

func NullableToTime(nullString sql.NullString) (t time.Time, err error)

NullableToTime parses SQLite TEXT to Go time.Time in Local location

• nullable database column
• SQLite TEXT ISO8601 nano-second resolution UTC time zone
• SQLite TEXT: “2022-01-01T08:00:00.000000000Z”
• NULL corresponds to time.Time{} time.Time.IsZero true

func OpenDataSource

func OpenDataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)

OpenDataSource creates a database-file in the file-system and returns its database implementation

• dataSourceName: a filename specifying a SQLite3 database file
• — for an in-memory database, SQLiteMemoryDataSourceName or ":memory:" is used
• dataSource: wraps a sql.DB value

func OpenDataSourceNamer

func OpenDataSourceNamer(appName string) (dsnr parl.DataSourceNamer, err error)

OpenDataSourceNamer is a parl.DSNrFactory function that returns a SQLite3 data-source names that returns:

• SQLite3 database filenames based on a partition key
• SQLite3 implemented data sources providing generic SQL query execution

func OpenDataSourceNamerRO

func OpenDataSourceNamerRO(appName string) (dsn parl.DataSourceNamer, err error)

OpenDataSourceNamerRO returns a SQLIte3 parl.DataSourceNamer that does not create databases or write to the file-system

• errors can be checked to be cause by read-only:

Usage:

if errors.Is(err, sqliter.ErrDsnNotExist) {

func Pragma

func Pragma(dataSource parl.DataSource, ctx context.Context) (pragmas parl.Pragmas, err error)

Pragma returns SQLite3 pragma database-settings based on a data source

• parl.DataSource is a caching of prepared statements obtained from OpenDataSource
• When Pragma is used to retieve database pragmas, the issued queries will be cached in dataSource

func PragmaDB

func PragmaDB(db parl.DB, partition parl.DBPartition, ctx context.Context) (pragmas parl.Pragmas, err error)

Pragma returns some common SQLite3 database settings

• parl.DB is a map of multiple DB objects facilitating partitioning
• in normal use, parl.DB is not available from [psql.NewDBMap]. The DB value is cached internally and retrieved via its data source name
• When PragmaDB is used to retieve database pragmas, the issued queries will be cached in the parl.DB data source

func PragmaSQL

func PragmaSQL(sqlDB *sql.DB, ctx context.Context) (pragmas parl.Pragmas, err error)

PragmaSQL returns common SQLite3 database settings

• PragmaSQL uses native sql.DB and sql.DB.QueryContext functions
• sqlDB is obtained from sql.Open
• — sqlDB does not cache the queries issued by PragmaSQL
• in-memory database parl.Pragmas.String: map[foreignKeys:0 journal:memory timeout:0]

func TimeToDATETIME

func TimeToDATETIME(t time.Time) (dateTime string)

TimeToDATETIME converts Go time.Time to SQLite DATETIME UTC millisecond precision

• better to use is TimeToDB and ToTime using iso8601 utc time-zone with nanosecond precision
• “2019-01-01 21:30:42.000 +00:00”

func TimeToDB

func TimeToDB(t time.Time) (dbValue string)

TimeToDB converts ns-resolution Go time.Time in any time.Location to SQLite TEXT ISO8601 nano-second resolution UTC time zone

• SQLite TEXT: “2022-01-01T08:00:00.000000000Z”

func TimeToDBNullable

func TimeToDBNullable(t time.Time) (dbValue any)

TimeToDBNullable converts ns-resolution Go time.Time in any time.Location to SQLite TEXT ISO8601 nano-second resolution UTC time zone

• for nullable database column
• SQLite TEXT: “2022-01-01T08:00:00.000000000Z”
• NULL corresponds to time.Time{} time.Time.IsZero true

func ToBool

func ToBool(dbValue int) (b bool, err error)

ToBool converts SQLite INTEGER 0 or 1 to Go bool

• Go reflect type is bool
• SQLite INTEGER values are 0 or 1

func ToTime

func ToTime(timeString string) (t time.Time, err error)

ToTime parses SQLite TEXT to Go time.Time in Local location

• SQLite TEXT ISO8601 nano-second resolution UTC time zone
• SQLite TEXT: “2022-01-01T08:00:00.000000000Z”

func ToUUID

func ToUUID(IDString string) (ID uuid.UUID, err error)

ToUUID converts an SQLite TEXT value to Go uuid.UUID

• err: non-nil on failure
• ID: valid or zero-value on failure
• Go reflect type: [16]byte uuid.UUID from package github.com/google/uuid
• — 16×8 is 128 bits
• SQLite value: 36-character TEXT “01234567-89ab-cdef-0123-456789abcdef”
• — 32×4 is 128 bits

func UUIDToDB

func UUIDToDB(ID uuid.UUID) (dbValue string)

UUIDToDB stores a Go 128-bit UUID as 36 characters SQLite TEXT

• Go reflect type: array [16]byte from package github.com/google/uuid
• — 16×8 is 128 bits
• SQLite type: 36-character string TEXT “01234567-89ab-cdef-0123-456789abcdef”
• — 32×4 is 128 bits
• — SQLite Storage Classes: NULL INTEGER REAL TEXT BLOB

Types

type DataSource

type DataSource struct {
	// DB represents a generic SQL database that can:
	//	- offer connections
	//	- execute generuic SQL queries
	*sql.DB
	// contains filtered or unexported fields
}

DataSource represents a SQL database that can prepare generic SQL queries

• implements parl.DataSource for SQLite3

func (*DataSource) WrapStmt

func (ds *DataSource) WrapStmt(stmt *sql.Stmt) (stm psql2.Stmt)

PrepareContext returns a sql.Stmt that does retries on 5 SQLITE_BUSY

• this is used by [parl.psql]

type DataSourceNamer

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

DataSourceNamer provides partitioned SQLite3 database files within the user’s home directory

func (*DataSourceNamer) DSN

func (n *DataSourceNamer) DSN(year ...parl.DBPartition) (dsnr parl.DataSourceName)

DSN returns a data source name, ie. a filename from application name and a partition key like year

• effectyively an absolute path of a writable SQLite3 “.db” database file
• implements parl’s [DatasourceNamer.DSN]

func (*DataSourceNamer) DataSource

func (n *DataSourceNamer) DataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)

DataSource returns a data-source that can execute generic SQL queries based on a data-source name

• implements parl’s [DatasourceNamer.DataSource]

type DataSourceNamerRO

type DataSourceNamerRO struct {
	DataSourceNamer
}

DataSourceNamerRO is a SQLite3 data source that does not create files or directories

func (*DataSourceNamerRO) DataSource

func (n *DataSourceNamerRO) DataSource(dataSourceName parl.DataSourceName) (dataSource parl.DataSource, err error)

DataSource does not create database files

• dataSourceName is the path toa file-syste database file
• make sure it already exists

type ErrorCode

type ErrorCode interface {
	error
	Code() (code int)
}

ErrorCode is the SQLite error implementation

• ErrorCode is an error instance
• The Code method provides an int error code

func Code

func Code(err error) (code int, sqliteError ErrorCode)

GetErrorCode traverses an error chain looking for an SQLite error

• If an SQLite error is found, it is returned in sqliteError
• — code is the int error code
• If no SQLite error exists in the error chain, sqliteError is nil and code is 0

type Stmt

type Stmt struct {
	*sql.Stmt
	// contains filtered or unexported fields
}

Stmt implements retries for a SQLite3 table

• the code here implements retries due to SQLite3 producing various concurrency errors
• this is legacy code from 2022-06

func (*Stmt) ExecContext

func (st *Stmt) ExecContext(ctx context.Context, args ...any) (sqlResult sql.Result, err error)

ExecContext executes a SQL statements that does not return any rows

• for performance reasons cached prepared statements are used
• the code here implements retries due to SQLite3 producing various concurrency errors
• this is legacy code from 2022-06

func (*Stmt) QueryContext

func (st *Stmt) QueryContext(ctx context.Context, args ...any) (sqlRows *sql.Rows, err error)

QueryContext executes a SQL statements that may return multiple rows

• for performance reasons cached prepared statements are used
• the code here implements retries due to SQLite3 producing various concurrency errors
• this is legacy code from 2022-06

func (*Stmt) QueryRowContext

func (st *Stmt) QueryRowContext(ctx context.Context, args ...any) (sqlRow *sql.Row)

QueryRowContext executes a SQL statements that returns exactly one row

• for performance reasons cached prepared statements are used
• the code here implements retries due to SQLite3 producing various concurrency errors
• this is legacy code from 2022-06