sqlite

README

go-sqlite

This module provides an interface for sqlite, including:

• Opening in-memory databases and persistent file-based databases;
• Transactions (committing changes and rolling back on errors);
• Adding custom functions, authentication and authorization;
• Reflection on databases (schemas, tables, columns, indexes, etc);
• Executing arbitrary statements or building statements programmatically;
• A pool of connections to run sqlite in a highliy concurrent environment such as a webservice;
• A backend REST API for sqlite;
• A generalized importer to import data from other data sources in different formats;
• A generalized file indexer to index files in a directory tree and provide a REST API to search them;
• A frontend web application to explore and interact with databases.

Presently the module is in development and the API is subject to change.

If you want to...	Folder	Documentation
Use the lower-level sqlite3 bindings similar to the C API	sys/sqlite3	README.md
Use high-concurrency high-level interface including statement caching and connection pool	pkg/sqlite3	README.md
Implement or use a REST API to sqlite3	plugin/sqlite3	README.md
Develop or use a front-end web service to the REST API backend	npm/sqlite3	README.md
Use an "object" interface to persist structured data	pkg/sqobj	README.md
Use a statement builder to programmatically write SQL statements	pkg/lang	README.md
Implement a generalized data importer from CSV, JSON, Excel, etc	pkg/importer	README.md
Implement a search indexer	pkg/indexer	README.md
Tokenize SQL statements for syntax colouring (for example)	pkg/tokenizer	README.md
See example command-line tools	cmd	README.md

Requirements

• go1.17 or later;
• Tested on Debian Linux (32- and 64- bit) on ARM and macOS on x64 architectures.

Building

There are some examples in the cmd folder of the main repository on how to use the package. The various make targets are:

• make all will perform tests, build all examples, the backend API and the frontend web application;
• make test will perform tests;
• make cmd will build example command-line tools into the build folder;
• make server plugins will install the backend server and required plugins in the build folder;
• make npm will compile the frontend web application in a 'dist' folder for each npm module located in the npm folder;
• make clean will remove all build artifacts.

Contributing & Distribution

This module is currently in development and subject to change.

Please do file feature requests and bugs here. The license is Apache 2 so feel free to redistribute. Redistributions in either source code or binary form must reproduce the copyright notice, and please link back to this repository for more information:

Copyright (c) 2021, David Thorpe, All rights reserved.

Documentation

Overview

Package sqlite provides a go interface to sqlite.

Please see https://github.com/mutablelogic/go-sqlite/blob/master/README.md for information on this module.

Index

• Constants
• type SQAlter
• type SQAuth
• type SQAuthFlag
  • func (v SQAuthFlag) Is(q SQAuthFlag) bool
  • func (v SQAuthFlag) String() string
  • func (v SQAuthFlag) StringFlag() string
• type SQClass
• type SQColumn
• type SQConnection
• type SQDrop
• type SQExecFunc
• type SQExpr
• type SQFlag
  • func (v SQFlag) Is(q SQFlag) bool
• type SQForeignKey
• type SQImportConfig
• type SQImportDecoder
• type SQImporter
• type SQIndexView
• type SQInsert
• type SQIterator
• type SQJoin
• type SQPool
• type SQResults
• type SQSelect
• type SQSource
• type SQStatement
• type SQTable
• type SQTransaction
• type SQTrigger
• type SQTxnFunc
• type SQUpdate

Constants

const (
	// TagName defines the tag name used for struct tags
	TagName = "sqlite"
)

Types

type SQAlter

type SQAlter interface {
	SQStatement

	// Alter operation
	AddColumn(SQColumn) SQStatement
	DropColumn(SQColumn) SQStatement
}

SQAlter defines an alter table statement

type SQAuth

type SQAuth interface {
	// CanSelect is called to authenticate a SELECT
	CanSelect(context.Context) error

	// CanTransaction is called for BEGIN, COMMIT, or ROLLBACK
	CanTransaction(context.Context, SQAuthFlag) error

	// CanExec is called to authenticate an operation other then SELECT
	CanExec(context.Context, SQAuthFlag, string, ...string) error
}

SQAuth is an interface for authenticating an action

type SQAuthFlag

type SQAuthFlag uint32

const (
	SQLITE_AUTH_TABLE       SQAuthFlag = 1 << iota // Table Object
	SQLITE_AUTH_INDEX                              // Index Object
	SQLITE_AUTH_VIEW                               // View Object
	SQLITE_AUTH_TRIGGER                            // Trigger Object
	SQLITE_AUTH_VTABLE                             // Virtual Table Object
	SQLITE_AUTH_TEMP                               // Temporary Object
	SQLITE_AUTH_TRANSACTION                        // Transaction
	SQLITE_AUTH_CREATE                             // Create operation
	SQLITE_AUTH_DROP                               // Drop operation
	SQLITE_AUTH_INSERT                             // Insert operation
	SQLITE_AUTH_DELETE                             // Delete operation
	SQLITE_AUTH_ALTER                              // Alter operation
	SQLITE_AUTH_ANALYZE                            // Analyze  operation
	SQLITE_AUTH_PRAGMA                             // Pragma operation
	SQLITE_AUTH_READ                               // Read column operation
	SQLITE_AUTH_UPDATE                             // Update column operation
	SQLITE_AUTH_FUNCTION                           // Execute function operation
	SQLITE_AUTH_BEGIN                              // Begin txn operation
	SQLITE_AUTH_COMMIT                             // Commit txn operation
	SQLITE_AUTH_ROLLBACK                           // Rollback txn operation
	SQLITE_AUTH_MIN                    = SQLITE_AUTH_TABLE
	SQLITE_AUTH_MAX                    = SQLITE_AUTH_ROLLBACK
	SQLITE_AUTH_NONE        SQAuthFlag = 0
)

func (SQAuthFlag) Is

func (v SQAuthFlag) Is(q SQAuthFlag) bool

Is any of the flags in q

func (SQAuthFlag) String

func (v SQAuthFlag) String() string

func (SQAuthFlag) StringFlag

func (v SQAuthFlag) StringFlag() string

type SQClass

type SQClass interface {
	// Create class in the named database schema
	Create(SQTransaction, string) error

	// Read all objects from the class and return the iterator
	// TODO: Need sort, filter, limit, offset
	Read(SQTransaction) (SQIterator, error)

	// Insert objects, return rowids
	Insert(SQTransaction, ...interface{}) ([]int64, error)

	// Delete rows in table based on rowid. Returns number of deleted rows
	DeleteRows(SQTransaction, []int64) (int, error)

	// Delete keys in table based on primary keys. Returns number of deleted rows
	DeleteKeys(SQTransaction, ...interface{}) (int, error)

	// Update objects by primary key, return number of updated rows
	UpdateKeys(SQTransaction, ...interface{}) (int, error)

	// Upsert (insert or update) objects by primary key, return rowids
	UpsertKeys(SQTransaction, ...interface{}) ([]int64, error)
}

SQClass is a class definition, which can be a table or view

type SQColumn

type SQColumn interface {
	SQExpr

	// Properties
	Name() string
	Type() string
	Nullable() bool
	Primary() string

	// Modifiers
	NotNull() SQColumn
	WithType(string) SQColumn
	WithAlias(string) SQSource
	WithPrimary() SQColumn
	WithAutoIncrement() SQColumn
	WithDefault(v interface{}) SQColumn
	WithDefaultNow() SQColumn
}

SQColumn represents a column definition

type SQConnection

type SQConnection interface {
	SQTransaction

	// Execute a transaction with context, rollback on any errors
	// or cancelled context
	Do(context.Context, SQFlag, func(SQTransaction) error) error

	// Execute a statement outside transacton
	Exec(SQStatement, SQExecFunc) error

	// Return a unique counter number for the connection
	Counter() int64
}

SQConnection is an sqlite connection to one or more databases

type SQDrop

type SQDrop interface {
	SQStatement

	IfExists() SQDrop
}

SQDrop defines a drop for tables, views, indexes, and triggers

type SQExecFunc

type SQExecFunc func(row, col []string) bool

type SQExpr

type SQExpr interface {
	String() string
}

SQExpr defines any expression

type SQFlag

type SQFlag uint32

const (
	SQLITE_NONE                          SQFlag = 0
	SQLITE_TXN_DEFAULT                   SQFlag = (1 << 16) // Default transaction flag
	SQLITE_TXN_IMMEDIATE                 SQFlag = (1 << 17) // Immediate transaction
	SQLITE_TXN_EXCLUSIVE                 SQFlag = (1 << 18) // Exclusive transaction
	SQLITE_TXN_NO_FOREIGNKEY_CONSTRAINTS SQFlag = (1 << 19) // Drop foreign key constraints on this transaction
	SQLITE_OPEN_CACHE                    SQFlag = (1 << 20) // Cache prepared statements
	SQLITE_OPEN_OVERWRITE                SQFlag = (1 << 21) // Overwrite objects
	SQLITE_OPEN_FOREIGNKEYS              SQFlag = (1 << 22) // Enable foreign key support
)

func (SQFlag) Is

func (v SQFlag) Is(q SQFlag) bool

Is any of the flags in q

type SQForeignKey

type SQForeignKey interface {
	// Modifiers
	OnDeleteCascade() SQForeignKey
}

SQForeignKey represents a foreign key constraint

type SQImportConfig

type SQImportConfig struct {
	// Schema defines the table schema to import into. Optional.
	Schema string `sqlite:"schema"`

	// Name defines the table name to import into, if empty will be inferred
	// from the import source URL
	Name string `sqlite:"name"`

	// Ext defines the extension to infer the mimetype from. Optional.
	Ext string `sqlite:"ext"`

	// Header when true indicates the first line of a CSV file is a header
	Header bool `sqlite:"header"`

	// TrimSpace when true indicates the CSV file should be trimmed of whitespace
	// for each field
	TrimSpace bool `sqlite:"trimspace"`

	// Comment defines the character which indicates a line is a comment. Optional.
	Comment rune `sqlite:"comment"`

	// Delimiter defines the character which indicates a field delimiter. Optional.
	Delimiter rune `sqlite:"delimiter"`

	// LazyQuotes when true indicates the CSV file should allow non-standard quotes.
	LazyQuotes bool `sqlite:"lazyquotes"`

	// Overwrite existing table (will append data otherwise)
	Overwrite bool `sqlite:"overwrite"`
}

type SQImportDecoder

type SQImportDecoder interface {
	// Read from the source, return column names and values. May
	// return nil values to skip a write. Returns io.EOF when no
	// more data is available.
	Read() ([]string, []interface{}, error)
}

type SQImporter

type SQImporter interface {
	// ReadWrite will read from the source, and write to destination. This function
	// should be called multiple times until io.EOF is returned, indicating that
	// no more data is available.
	ReadWrite(SQImportDecoder) error

	// Return the URL of the source
	URL() *url.URL

	// Return the Table name for the destination
	Name() string

	// Return a decoder for a reader, mimetype or file extension (when starts with a .)
	// Will return nil if no decoder is available. The mimetype can include
	// the character set (e.g. text/csv; charset=utf-8)
	Decoder(io.Reader, string) (SQImportDecoder, error)
}

type SQIndexView

type SQIndexView interface {
	SQStatement
	SQSource

	// Return properties
	Unique() bool
	Table() string
	Columns() []string
	Auto() bool

	// Modifiers
	IfNotExists() SQIndexView
	WithTemporary() SQIndexView
	WithUnique() SQIndexView
	WithAuto() SQIndexView
	Options(...string) SQIndexView
}

SQIndexView defines a create index or view statement

type SQInsert

type SQInsert interface {
	SQStatement

	DefaultValues() SQInsert
	WithConflictDoNothing(...string) SQInsert
	WithConflictUpdate(...string) SQInsert
}

SQInsert defines an insert or replace statement

type SQIterator

type SQIterator interface {
	// Next returns the next object in the iterator, or nil if there are no more
	Next() interface{}

	// RowId returns the last read row, should be called after Next()
	RowId() int64
}

SQIterator is an iterator for a Read operation

type SQJoin

type SQJoin interface {
	SQExpr

	Join(...SQExpr) SQJoin
	LeftJoin(...SQExpr) SQJoin
	LeftInnerJoin(...SQExpr) SQJoin
	Using(...string) SQJoin
}

SQJoin defines one or more joins

type SQPool

type SQPool interface {
	// Close waits for all connections to be released and then releases resources
	Close() error

	// Get a connection from the pool. If there are no
	// connections available or an error occurs, nil is returned.
	Get() SQConnection

	// Return connection to the pool
	Put(SQConnection)

	// Cur returns the current number of used connections
	Cur() int

	// Max returns the maximum allowed number of used connections
	Max() int

	// SetMax allowed connections released from pool. Note this does not change
	// the maximum instantly, it will settle to this value over time.
	SetMax(int)
}

SQPool is an sqlite connection pool

type SQResults

type SQResults interface {
	// Return next row, returns nil when all rows consumed
	// if types are provided, then returned row is cast to
	// appopriate types. The returned row needs to be copied
	// if not transient
	Next(...reflect.Type) []interface{}

	// Close results and discard when done
	Close() error

	// NextQuery executes the next query or returns io.EOF
	NextQuery(...interface{}) error

	// Return the SQL for the last statement
	ExpandedSQL() string

	// Return Last RowID inserted of last statement
	LastInsertId() int64

	// Return number of changes made of last statement
	RowsAffected() int

	// Columns returns the column definitions
	Columns() []SQColumn

	// ColumnTable returns the schema, table and column name for a column index
	ColumnSource(int) (string, string, string)
}

SQResults increments over returned rows from a query

type SQSelect

type SQSelect interface {
	SQStatement

	// Set select flags
	WithDistinct() SQSelect
	WithLimitOffset(limit, offset uint) SQSelect

	// Destination expressions for results
	To(...SQExpr) SQSelect

	// Where and order clauses
	Where(...interface{}) SQSelect
	Order(...SQSource) SQSelect
}

SQSelect defines a select statement

type SQSource

type SQSource interface {
	SQExpr

	// Return name, schema, type
	Name() string
	Schema() string
	Alias() string

	// Modify the source
	WithName(string) SQSource
	WithSchema(string) SQSource
	WithType(string) SQColumn
	WithAlias(string) SQSource
	WithDesc() SQSource

	// Insert, replace or upsert a row with named columns
	Insert(...string) SQInsert
	Replace(...string) SQInsert

	// Drop objects
	DropTable() SQDrop
	DropIndex() SQDrop
	DropTrigger() SQDrop
	DropView() SQDrop

	// Create objects
	CreateTable(...SQColumn) SQTable
	CreateVirtualTable(string, ...string) SQIndexView
	CreateIndex(string, ...string) SQIndexView
	CreateTrigger(string, ...SQStatement) SQTrigger
	CreateView(SQSelect, ...string) SQIndexView
	ForeignKey(...string) SQForeignKey

	// Alter objects
	AlterTable() SQAlter

	// Update and delete data
	Update(...string) SQUpdate
	Delete(...interface{}) SQStatement
}

SQSource defines a table or column name

type SQStatement

type SQStatement interface {
	SQExpr
	Query() string
}

SQStatement is any statement which can be prepared or executed

type SQTable

type SQTable interface {
	SQStatement

	IfNotExists() SQTable
	WithTemporary() SQTable
	WithoutRowID() SQTable
	WithIndex(...string) SQTable
	WithUnique(...string) SQTable
	WithForeignKey(SQForeignKey, ...string) SQTable
}

SQTable defines a table of columns and indexes

type SQTransaction

type SQTransaction interface {
	// Query and return a set of results
	Query(SQStatement, ...interface{}) (SQResults, error)

	// Schemas returns a list of all the schemas in the database
	Schemas() []string

	// Tables returns a list of tables in a schema
	Tables(string) []string

	// Count returns the number of rows in a table and schema
	Count(string, string) int64

	// Filename returns a filename for a schema, returns empty
	// string if in-memory database
	Filename(string) string

	// ColumnsForTable returns the columns in a schema and table
	ColumnsForTable(string, string) []SQColumn

	// ColumnsForIndex returns the column names associated with schema and index
	ColumnsForIndex(string, string) []string

	// IndexesForTable returns the indexes associated with a schema and table
	IndexesForTable(string, string) []SQIndexView

	// Views returns a list of view names in a schema
	Views(string) []string

	// Modules returns a list of modules. If an argument is
	// provided, then only modules with those name prefixes
	// matched
	Modules(...string) []string

	// Return flags for transaction or'd with connection flags
	Flags() SQFlag
}

SQTransaction is an sqlite transaction

type SQTrigger

type SQTrigger interface {
	SQStatement

	// Modifiers
	IfNotExists() SQTrigger
	WithTemporary() SQTrigger
	Before() SQTrigger
	After() SQTrigger
	InsteadOf() SQTrigger
	Delete() SQTrigger
	Insert() SQTrigger
	Update(...string) SQTrigger
}

SQTrigger defines a create trigger statement

type SQTxnFunc

type SQTxnFunc func(SQTransaction) error

type SQUpdate

type SQUpdate interface {
	SQStatement

	// Modifiers
	WithAbort() SQUpdate
	WithFail() SQUpdate
	WithIgnore() SQUpdate
	WithReplace() SQUpdate
	WithRollback() SQUpdate

	// Where clause
	Where(...interface{}) SQUpdate
}

SQUpdate defines an update statement