sqlite

package module
v1.0.35 Latest Latest
Warning

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

 Go to latest Published: Sep 11, 2021 License: Apache-2.0 Imports: 3 Imported by: 0

README

go-sqlite

This module provides an alternative interface for sqlite, including:

  • Opening in-memory databases and databases by file path;
  • Transactions (committing changes and rolling back on errors);
  • Reading results into a struct, map or slice.
  • Reflection on databases (schemas, tables, columns, indexes, etc);
  • Attaching and detaching databases by schema name;
  • Executing arbitrary statements or building statements programmatically;

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

Opening and creating databases

You can create a new in-memory database using the New method, or you can open an existing file-based database using the Open method. Both methods take an optional *time.Location argument which allows interpretation of time values with time zone. For example,

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  path := // ...
  db, err := sqlite.Open(path,time.UTC) // Open file database with UTC time zone
  if err != nil {
    // ...
  }
  defer db.Close()
}

Use db.Close() to release database resources.

Executing queries and transactions

Statements are executed using the db.Exec method on a query. In order to create a query from a string, use the Q() method (see below for information on building SQL statements). You can include bound parameters after the query:

For example,

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  if result,err := db.Exec(Q("CREATE TABLE test (a TEXT,b TEXT)")); err != nil {
    // ...
  } else {
    fmt.Println(result)
  }
}

The result object returned (of type SQResult) contains fields LastInsertId and RowsAffected which may or may not be set depending on the query executed. To return data, a result set is returned which should allows you to iterate across the results as a map of values, a slice of values:

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  rs,err := db.Select(Q("SELECT a,b FROM test WHERE a=?"),"foo")
  if err != nil {
    // ...
  }
  defer rs.Close()
  for {
    row := rs.Next()
    if row == nil {
      break
    }
    // ...
  }
}

You can also create a block of code, which when returning any error will rollback any database snapshot, or else commit the snapshot if no error occurred:

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  db.Do(func (txn sqlite.SQTransaction) error {
    _, err := txn.Exec(Q("..."))
    if err != nil {
      // Rollback any database changes
      return err
    }
    
    // Perform further operatins here...

    // Return success, commit transaction
    return nil
  })
}

Supported Column Types

The following types are supported, and expect the declared column types:

Scalar Type Column Type
int64 INTEGER
float64 FLOAT
string TEXT
bool BOOL
time.Time TIMESTAMP or DATETIME
[]byte BLOB

If you pass other integer and unsigned integer types into the Exec and Query functions then they are converted to one of the above types. You can also define methods MarshalSQ and UnmarshalSQ in order to convert your custom types into scalar types. For example, the following methods convert between supported scalar types:

type CustomParam struct {
	A, B string
}

func (c CustomParam) MarshalSQ() (interface{}, error) {
	if data, err := json.Marshal(c); err != nil {
		return nil, err
	} else {
		return string(data), err
	}
}

func (c *CustomParam) UnmarshalSQ(v interface{}) error {
	if data, ok := v.(string); ok {
		return json.Unmarshal([]byte(data), c)
	} else {
		return fmt.Errorf("Invalid type: %T", v)
	}
}

Attaching databases by schema name

You can load additional databases to a database by schema name. Use Attach and Detach to attach and detach databases. For example,

Database reflection

Building statements programmatically

A statement builder can be used for generating SQL statements programmatially. It is intended you use the following primitves to build your statements:

  • P is a placeholder for a value, which binds to the corresponding placeholder in Query or Exec methods;
  • V() is the value function;
  • N() is the name function, which corresponds to a table or column name;
  • Q() is the quote function, which allows insertation or execution or arbitary queries;
  • S() is the select function, which builds up a SELECT statement;

In order to use these primitives within your code, it is suggested you import the laguage namespace directly into your code. For example:

package main

import (
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  s := S(N("a"),N("b").Distinct().Where(N("a").Is(P))
  fmt.Println(s) // Prints SELECT DISTINCT * FROM a,b WHERE a=?
}

If the symbols P,V,N,Q or S clash with any symbols in your code namespace, you can import the package without the dot prefix and refer to the sumbols prefixed with lang. instead.

Reading results into a struct, map or slice

Importing data

Documentation

Overview

Package sqlite provides a go interface to sqlite.

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

Index

Constants

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

Variables

This section is empty.

Functions

This section is empty.

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 uint
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 with modification flags
	Create(SQConnection, string, SQFlag) error

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

	// Insert objects, return rowids
	Insert(SQConnection, ...interface{}) ([]SQResult, error)

	// Update objects by primary key, return rowids
	Update(SQConnection, ...interface{}) ([]SQResult, error)

	// Upsert objects by primary key, return rowids
	Upsert(SQConnection, ...interface{}) ([]SQResult, error)

	// Delete objects from the database by primary key
	Delete(SQConnection, ...interface{}) ([]SQResult, error)

	// Set a foreign key reference to parent class and columns. Panic
	// on error, and return same class
	ForeignKey(SQClass, ...string) SQClass
}

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

type SQColumn 

type SQColumn interface {
	SQStatement

	// 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 SQComparison 

type SQComparison interface {
	SQStatement

	// Negate the comparison
	Not() SQComparison
}

SQComparison defines a comparison between two expressions

type SQConnection 

type SQConnection interface {

	// Execute a transaction with context, rollback on any errors
	// or cancelled context
	Do(context.Context, SQTxnFlag, func(SQTransaction) error) 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

	// 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
}

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 SQExpr 

type SQExpr interface {
	SQStatement

	// And, Or, Not
	Or(interface{}) SQExpr
}

SQExpr defines any expression

type SQFlag 

type SQFlag uint
const (
	// Create flags
	SQLITE_FLAG_DELETEIFEXISTS SQFlag = 1 << iota // Delete existing database objects if they already exist
	SQLITE_FLAG_UPDATEONINSERT                    // Update existing object if a unique constraint fails

	// Other constants
	SQLITE_FLAG_NONE SQFlag = 0
	SQLITE_FLAG_MIN         = SQLITE_FLAG_DELETEIFEXISTS
	SQLITE_FLAG_MAX         = SQLITE_FLAG_UPDATEONINSERT
)

func (SQFlag) FlagString 

func (v SQFlag) FlagString() string

func (SQFlag) String 

func (f SQFlag) String() string

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 to skip a write. Returns io.EOF when no more data is available.
	Read() ([]string, []interface{}, error)
}

type SQImportWriter 

type SQImportWriter interface {
	// Begin the writer process for a destination and return a writer callback
	Begin(name, schema string) (SQImportWriterFunc, error)

	// End the transaction with success (true) or failure (false). On failure, rollback
	End(bool) error
}

SQImportWriter is an interface for writing decoded rows to a destination

type SQImportWriterFunc 

type SQImportWriterFunc func([]string, []interface{}) error

SQWriterFunc callback invoked for each array of columns and values from decoder

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, SQImportWriter) error

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

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

	// Return a decoder for a 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(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
}

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

	// Close releases any resources associated with the iterator
	Close() error
}

SQIterator is an iterator for a Read operation

type SQKey 

type SQKey uint
const (
	SQKeyNone SQKey = iota
	SQKeyInsert
	SQKeySelect
	SQKeyWrite
	SQKeyDelete
	SQKeyGetRowId
	SQKeyMax
)

type SQObjects 

type SQObjects interface {
	SQConnection

	// Create classes with named database and modification flags
	Create(string, SQFlag, ...SQClass) error

	// Write objects to database
	Write(v ...interface{}) ([]SQResult, error)

	// Read objects from database
	Read(SQClass) (SQIterator, error)

	// Write objects to database, call hook after each write
	WriteWithHook(SQWriteHook, ...interface{}) ([]SQResult, error)

	// Delete objects from the database
	Delete(v ...interface{}) ([]SQResult, error)
}

SQObjects is an sqlite connection but adds ability to read, write and delete

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, and return it to the pool when the context
	// is cancelled or it is put back using the Put method. If there are no
	// connections available or an error occurs, nil is returned.
	Get(context.Context) SQConnection

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

	// Return connection to the pool
	Put(SQConnection)

	// Max returns the maximum number of connections allowed
	Max() int64

	// SetMax allowed connections released from pool. Note this does not change
	// the maximum instantly, it will settle to this value over time. Set as value
	// zero to disable opening new connections
	SetMax(int64)
}

SQPool is an sqlite connection pool

type SQResult 

type SQResult interface {
	// Return next row, returns nil when all rows consumed
	Next() []interface{}

	// Return next map of values, or nil if no more rows
	NextMap() map[string]interface{}

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

	// Close the rows, and free up any resources
	Close() error

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

	// Return number of changes made of last statement
	RowsAffected() uint64
}

SQResult increments over returned rows from a query

type SQSelect 

type SQSelect interface {
	SQStatement

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

	// Destination columns for results
	To(...SQSource) SQSelect

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

SQSelect defines a select statement

type SQSource 

type SQSource interface {
	SQStatement
	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
	//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 {
	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{}) (SQResult, error)
}

SQTransaction is an sqlite transaction

type SQTxnFlag 

type SQTxnFlag uint
const (
	SQLITE_TXN_DEFAULT SQTxnFlag = (1 << iota)
	SQLITE_TXN_IMMEDIATE
	SQLITE_TXN_EXCLUSIVE
	SQLITE_TXN_SNAPSHOT
	SQLITE_TXN_NO_FOREIGNKEY_CONSTRAINTS
)

func (SQTxnFlag) Is 

func (v SQTxnFlag) Is(q SQTxnFlag) bool

Is any of the flags in q

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

type SQWriteHook 

type SQWriteHook func(SQResult, interface{}) error

Source Files

View all Source files

Directories

Path Synopsis
cmd
importer command
indexer command
pkg
importer
indexer
lang
quote
Package quote provides sqlite quoting functions for strings and identifiers
 Package quote provides sqlite quoting functions for strings and identifiers
sqlite
sqlite3
Package sqlite3 provides a high level interface for sqlite3, including pooled connections object serialization and transactions
 Package sqlite3 provides a high level interface for sqlite3, including pooled connections object serialization and transactions
sqobj
plugin
sqlite3 command
sys
sqlite3
Package sqlite3 provides bindings for sqlite 3.
 Package sqlite3 provides bindings for sqlite 3.

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.