sqlite

package module
v1.0.7 Latest Latest
Warning

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

 Go to latest Published: Oct 12, 2019 License: Apache-2.0 Imports: 10 Imported by: 0

README

sqlite

CircleCI

This repository contains a higher-level interface to SQLite, to provide database persistence. It implements two components for gopi and some example programs in the cmd folder. The repository depends on golang version 1.12 and above (in order to support modules).

Components

The gopi components provided by this repository are:

Component Path Description Component Name
sys/sqlite SQL Database persisence using sqlite db/sqlite
sys/sqlite SQL Language Builder db/sqlang
sys/sqobj Lightweight object layer db/sqobj

Building and installing examples

You may not have sqlite3 installed, so on Debian (and Raspian) Linux you can install using the following commands:

bash% sudo apt install sqlite3 sqlite3-doc

There is a makefile which can be used for testing and installing bindings and examples:

bash% git clone git@github.com:djthorpe/sqlite.git
bash% cd sqlite
bash% make all

The resulting binaries are as follows. Use the -help flag to see the different options for each:

  • sq_import Imports data from CSV files into an SQLite database
  • fs_indexer Indexe files into a database to implement a full-text search

You can also build these tools separately using make sq_import and make fs_indexer respectively.

Using db/sqlite

Database persistence is implemented using the db/sqlite component. Here's an example of how to use the component:

package main

import (
  // Frameworks
  gopi "github.com/djthorpe/gopi"
  sqlite "github.com/djthorpe/sqlite"

  // Modules
  _ "github.com/djthorpe/gopi/sys/logger"
  _ "github.com/djthorpe/sqlite/sys/sqlite"
)

func Main(app *gopi.AppInstance, done chan<- struct{}) error {
  db := app.ModuleInstance("db/sqlite").(sqlite.Connection)
  if rows, err := db.QueryOnce("SELECT * FROM table"); err != nil {
    return err
  } else {
    for {
      if row := rows.Next(); row == nil {
        break
      } else {
        fmt.Println(sqlite.RowString(row))
      }
    }
  }
  return nil
}

func main() {
  os.Exit(gopi.CommandLineTool2(gopi.NewAppConfig("db/sqlite"), Main))
}
Returning database information

The Connection component includes methods for returning database information (Version, Tables and ColumnsForTable) and executing actions on the data:

type Connection interface {
  // Return version number of the underlying sqlite library
  Version() string

  // Return attached schemas
  Schemas() []string

  // Return table names for the main schema. The extended version
  // returns table names for a different schema, or can include
  // temporary tables
  Tables() []string
  TablesEx(schema string, include_temporary bool) []string
  
  // Return the columns for a table
  ColumnsForTable(name, schema string) ([]Column, error)
}

The table column interface is specified as follows:

type Column interface {
  // Name returns the column name
  Name() string
  
  // DeclType returns the declared type for the column
  DeclType() string
  
  // Nullable returns true if  a column cell can include the NULL value
  Nullable() bool
  
  // PrimaryKey returns true if the column is part of the primary key
  PrimaryKey() bool
}
Database actions

The Query and QueryOnce methods return a Rows object which can be used to iterate through the set of results, whereas the Do and DoOnce methods can be used for inserting, updating and deleting table rows (and executing other database actions which don't return a set of results):

type Connection interface {
  Transaction
}

type Transaction interface {
  // Execute statement (without returning the rows)
  Do(Statement, ...interface{}) (Result, error)
  DoOnce(string, ...interface{}) (Result, error)

  // Query to return rows from result
  Query(Statement, ...interface{}) (Rows, error)
  QueryOnce(string, ...interface{}) (Rows, error)
}

The difference between Do and DoOnce are that the statements are either built through building a Statement object (which is subsequently prepared for repeated use in subsequent actions) and parsing the string into an action, and discarding when the action has been performed.

Action results

When no error is returned which calling Do or DoOnce, a Result will be returned with information about the action executed:

type Result struct {
  // LastInsertId returns the rowid on INSERT or REPLACE
  LastInsertId int64
  
  // RowsAffected returns number of affected rows on UPDATE or DELETE
  RowsAffected uint64
}
Queries and Rows

When using Query and QueryOnce a Rows object is returned, which provides details on each set of results:

type Rows interface {
  // Columns returns the columns for the rows
  Columns() []Column

  // Next returns the next row of the results, or nil otherwise
  Next() []Value
}

type Value interface {
  String() string       // Return value as string
  Int() int64           // Return value as int
  Bool() bool           // Return value as bool
  Float() float64       // Return value as float
  Timestamp() time.Time // Return value as timestamp
  Bytes() []byte        // Return value as blob

  // IsNull returns true if value is NULL
  IsNull() bool
}

When returning a timestamp, the timezone is by default the local timezone, or another timezone when it's specified by the -sqlite.tz command-line flag.

Transactions

Actions can be performed within a transaction, which can either be commited or rolled back if an error occurs.

type Connection interface {
  // Perform operations within a transaction, rollback on error
  Txn(func(Transaction) error) error
}

A transaction is specified using a function which should return an error if the actions should be rolled back:

func DeleteRows(rows []int) error {
  var db sqlite.Connection
  var sql sqlite.Language
  return db.Txn(func(txn sqlite.Transaction) error {
    // Prepare DELETE FROM
    delete := sql.Delete("table",sql.In("_rowid_",rows))
    // Execute the statement
    if _, err := txn.Do(delete); err != nil {
      // Rollback
      return err
    } else {
      // Commit
      return nil
    }
  })
}
Attaching external databases

The following methods can be used to attach external databases and query the schemas defined:

type Connection interface {
	Attach(schema, dsn string) error
	Detach(schema string) error

  // Return schemas defined, including "main" and "temp"
  Schemas() []string
}

The dsn is either a filename or the token ":memory:"for a separate database. The schema name cannot be set to main or temp as those schemas are pre-defined.

Supported types

The following go types are supported, and their associated database declared type:

Type Decltype Example
string TEXT "string"
bool BOOL true
int64 INTEGER 1234
float64 REAL 3.1416
timestamp DATETIME 2019-07-10T12:34:56Z
[]byte BLOB 123456789ABCDE

TODO:

  • Converting from bound argments...
  • Quote identifiers...
  • Quote strings...

Using db/sqlang

SQL statements can be created from a string using the NewStatement function:

type Transaction interface {
  NewStatement(string) Statement
}

However, it's also possible to use a sqlite.Language object to construct SQL statements programmatically. For example,

// Returns SELECT * FROM table LIMIT <nnn>
func NewQuery(limit uint) sqlite.Statement {
  var sql sqlite.Language
  return sql.Select("table").Limit(limit)
}

TODO:

  • Language builder

Using db/sqobj

This component implements a very lite object persisence later.

TODO:

  • Defining tables
  • Registering struct as a table, and setting the table name
  • Inserting into the table and updating
  • Querying the table
  • Deleting from the table

Example Commands

sq_import

The sq_import command line tool allows you to import and query CSV files. In order to compile the sq_import tool,

bash% git clone git@github.com:djthorpe/sqlite.git
bash% cd sqlite
bash% make sq_import

The command line arguments are:


sq_import <flags> <csv_file>...

Flags:
  -noheader
      Do not use the first row as column names
  -notnull
      Dont use NULL values for empty values
  -sqlite.dsn string
      Database source (default ":memory:")

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrUnsupportedType = errors.New("Unsupported type")
	ErrInvalidDate     = errors.New("Invalid date")
	ErrNotFound        = errors.New("Not Found")
)

Functions

func DoubleQuote 

func DoubleQuote(value string) string

DoubleQuote puts double quotes around a string and escapes existing double quotes

func IsSupportedType 

func IsSupportedType(value string) bool

IsSupportedType returns true if the value provided is a reserved type

func QuoteIdentifier 

func QuoteIdentifier(value string) string

QuoteIdentifier returns a safe version of an identifier

func QuoteIdentifiers added in v1.0.3 

func QuoteIdentifiers(values ...string) string

QuoteIdentifiers returns a safe version of a list of identifiers, separated by commas

func RowMap added in v1.0.3 

func RowMap(row []Value) map[string]Value

RowMap returns a row as <name>,<value> pairs

func RowString 

func RowString(row []Value) string

RowString returns a row as a string

func SupportedTypeForType added in v1.0.3 

func SupportedTypeForType(v interface{}) string

func SupportedTypes added in v1.0.2 

func SupportedTypes() []string

SupportedTypes returns all supported types

func SupportedTypesForValue added in v1.0.2 

func SupportedTypesForValue(value string) []string

SupportedTypesForValue returns most likely supported types for a value, in order. It will always return at least one type, with the most likely type as the zero'th element

func ToSupportedType added in v1.0.2 

func ToSupportedType(value, decltype string, notnull bool, timezone *time.Location) (interface{}, error)

ToSupportedType converts from a string to a supported type. If the string is empty, will return <nil> if notnull is false

Types

type Class added in v1.0.4 

type Class interface {
	// Return name of the class
	Name() string
}

type Column 

type Column interface {
	Name() string
	DeclType() string
	Nullable() bool
	PrimaryKey() bool
	Index() int
	Query() string
}

Column represents the specification for a table column

type Connection 

type Connection interface {
	gopi.Driver
	Transaction

	// Perform operations within a transaction, rollback on error
	Txn(func(Transaction) error) error

	// Return sqlite information
	Version() string
	Schemas() []string
	Tables() []string
	TablesEx(schema string, include_temporary bool) []string
	ColumnsForTable(name, schema string) ([]Column, error)

	// Attach and detach other databases, schema cannot be 'main' or 'temp'
	Attach(schema, dsn string) error
	Detach(schema string) error
}

Connection to a database

type CreateIndex added in v1.0.6 

type CreateIndex interface {
	Statement

	Schema(string) CreateIndex
	Unique() CreateIndex
	IfNotExists() CreateIndex
}

CreateIndex statement

type CreateTable 

type CreateTable interface {
	Statement

	Schema(string) CreateTable
	IfNotExists() CreateTable
	Temporary() CreateTable
	WithoutRowID() CreateTable
	Unique(...string) CreateTable
}

CreateTable statement

type Delete added in v1.0.6 

type Delete interface {
	Statement

	Schema(string) Delete
	Where(Expression) Delete
}

Delete statement

type Drop added in v1.0.6 

type Drop interface {
	Statement

	Schema(string) Drop
	IfExists() Drop
}

Drop (table,index,trigger,view) statement

type Expression added in v1.0.6 

type Expression interface {
	Query() string
}

Expression represents an expression used in Select

type Flag added in v1.0.5 

type Flag uint
const (
	FLAG_INSERT Flag = (1 << iota)
	FLAG_UPDATE
	FLAG_NONE Flag = 0
)

type InsertOrReplace added in v1.0.2 

type InsertOrReplace interface {
	Statement

	Schema(string) InsertOrReplace
	DefaultValues() InsertOrReplace
}

InsertOrReplace represents an insert or replace

type Language added in v1.0.5 

type Language interface {
	gopi.Driver

	// Create
	NewCreateTable(string, ...Column) CreateTable
	NewCreateIndex(string, string, ...string) CreateIndex

	// Drop
	DropTable(string) Drop
	DropIndex(string) Drop
	DropTrigger(string) Drop
	DropView(string) Drop

	// Insert, replace and update
	Insert(string, ...string) InsertOrReplace
	Replace(string, ...string) InsertOrReplace
	NewDelete(string) Delete
	NewUpdate(string, ...string) Update

	// Select
	NewSelect(Source) Select

	// Return named data source
	NewSource(name string) Source

	// Build expressions
	Null() Expression
	Arg() Expression
	Value(interface{}) Expression
	Equals(string, Expression) Expression
	NotEquals(string, Expression) Expression
	And(...Expression) Expression
	Or(...Expression) Expression
}

Language component to build statements

type Object added in v1.0.6 

type Object struct {
	RowId int64
}

func (*Object) String added in v1.0.6 

func (this *Object) String() string

type Objects added in v1.0.4 

type Objects interface {
	gopi.Driver

	// RegisterStruct registers a struct against a database table
	RegisterStruct(interface{}) (StructClass, error)

	// ReflectStruct returns SQL table columns from a struct
	ReflectStruct(v interface{}) ([]Column, error)

	// Insert, replace and update structs, rollback on error
	// and return number of affected rows
	Write(Flag, ...interface{}) (uint64, error)

	// Delete structs  or by rowid, rollback on error
	// and return number of affected rows
	Delete(...interface{}) (uint64, error)
}

type Result 

type Result struct {
	LastInsertId int64
	RowsAffected uint64
}

Result of an insert

func (Result) String 

func (r Result) String() string

type Rows 

type Rows interface {
	// Return column names
	Columns() []Column

	// Return next row of values, or nil if no more rows
	Next() []Value
}

Rows increments over returned rows from a query

type Select 

type Select interface {
	Statement

	Distinct() Select
	Where(...Expression) Select
	LimitOffset(uint, uint) Select
}

Select statement

type Source added in v1.0.3 

type Source interface {
	Statement

	Schema(string) Source
	Alias(string) Source
}

Source represents a simple table source (schema, name and table alias)

type Statement 

type Statement interface {
	Query() string
}

Statement that can be executed

type StructClass added in v1.0.4 

type StructClass interface {
	Class
}

type Transaction added in v1.0.5 

type Transaction interface {
	// Return statement anc column
	NewStatement(string) Statement
	NewColumn(name, decltype string, nullable, primary bool) Column
	NewColumnWithIndex(name, decltype string, nullable, primary bool, index int) Column

	// Execute statement (without returning the rows)
	Do(Statement, ...interface{}) (Result, error)
	DoOnce(string, ...interface{}) (Result, error)

	// Query to return the rows
	Query(Statement, ...interface{}) (Rows, error)
	QueryOnce(string, ...interface{}) (Rows, error)
}

Transaction that can be committed/rolled back

type Update added in v1.0.6 

type Update interface {
	Statement

	Schema(string) Update
	Where(Expression) Update
}

Update represents an update

type Value 

type Value interface {
	String() string       // Return value as string
	Int() int64           // Return value as int
	Bool() bool           // Return value as bool
	Float() float64       // Return value as float
	Timestamp() time.Time // Return value as timestamp
	Bytes() []byte        // Return value as blob

	// Return the column associated with the value
	Column() Column

	// Return true if value is NULL
	IsNull() bool
}

Value represents a typed value in a table

Source Files

View all Source files

Directories

Path Synopsis
cmd
fs_indexer command
sq_import command
sys
sqlite
sqobj

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.