sql

README

SQL-like datastore

The sql package defines the API for accessing a data store using SQL. The Broker interface allows reading and manipulating with data. The Watcher API provides functions for monitoring of changes in a data store.

Features

• The user of the API has full control over the SQL statements, types & bindings passed to the Broker.
• Expressions:
  • Helper functions alleviate the need to write SQL strings.
  • The user can choose to only write expressions using helper functions
  • The user can write portions of SQL statements by a hand (the sql.Exp helper function) and combine them with other expressions
• The user can optionally use reflection to simplify repetitive work with Iterators & Go structures
• The API will be reused for different databases. A specific implementation will be provided for each database.

Documentation

Overview

Package sql provides an abstraction of a data store that supports an SQL-like query language and defines the SQL data broker API. The SQL data broker API consists of the the Broker and KeyValProtoWatcher APIs for accessing data in a SQL data store.

Index

• func EntityTableName(entity interface{}) string
• func ExpsToString(exps []Expression) string
• func SliceIt(pointerToASlice interface{}, it ValIterator) error
• func ToChan(respChan chan WatchResp, options ...interface{}) func(event WatchResp)
• type Broker
• type BrokerPlugin
• type Expression
  • func AND(inside ...Expression) Expression
  • func DELETE(entity interface{}, afterKeyword Expression) Expression
  • func EQ(binding interface{}) (exp Expression)
  • func Exp(statement string, binding ...interface{}) Expression
  • func FROM(pointerToAStruct interface{}, afterKeyword Expression) Expression
  • func Field(pointerToAField interface{}, rigthOperand ...Expression) (exp Expression)
  • func FieldEQ(pointerToAField interface{}) (exp Expression)
  • func GT(binding interface{}) (exp Expression)
  • func GTE(binding interface{}) (exp Expression)
  • func IN(binding ...interface{}) (exp Expression)
  • func LT(binding interface{}) (exp Expression)
  • func LTE(binding interface{}) (exp Expression)
  • func OR(inside ...Expression) Expression
  • func PK(pointerToAField interface{}) (exp Expression)
  • func Parenthesis(inside ...Expression) (exp Expression)
  • func SELECT(entity interface{}, afterKeyword Expression, binding ...interface{}) Expression
  • func WHERE(afterKeyword ...Expression) Expression
• type FieldExpression
  • func (exp *FieldExpression) Accept(visitor Visitor)
  • func (exp *FieldExpression) GetBinding() []interface{}
  • func (exp *FieldExpression) String() string
• type PrefixedExp
  • func (exp *PrefixedExp) Accept(visitor Visitor)
  • func (exp *PrefixedExp) GetBinding() []interface{}
  • func (exp *PrefixedExp) String() string
• type SchemaName
• type TableName
• type Txn
• type ValIterator
• type Visitor
• type WatchResp
• type Watcher

Functions

func EntityTableName

func EntityTableName(entity interface{}) string

EntityTableName returns the table name, possibly prefixed with the schema name, associated with the <entity>. The function tries to cast <entity> to TableName and SchemaName in order to obtain the table name and the schema name, respectively. If table name cannot be obtained, the struct name is used instead. If schema name cannot be obtained, it is simply omitted from the result.

func ExpsToString

func ExpsToString(exps []Expression) string

ExpsToString joins (without separator) individual expression string representations

func SliceIt

func SliceIt(pointerToASlice interface{}, it ValIterator) error

SliceIt reads everything from the ValIterator & stores it to pointerToASlice It closes the iterator (since nothing left in the iterator)

func ToChan

func ToChan(respChan chan WatchResp, options ...interface{}) func(event WatchResp)

ToChan TODO (not implemented yet)

Types

type Broker

type Broker interface {
	// Put puts single value <inBinding> into the data store
	// Example usage:
	//
	//    err = db.Put("ID='James Bond'", &User{"James Bond", "James", "Bond"})
	//
	Put(where Expression, inBinding interface{}) error

	// NewTxn creates a transaction / batch.
	NewTxn() Txn

	// GetValue retrieves one item based on the <query>. If the item exists,
	// it is un-marshaled into the <outBinding>.
	//
	// Example usage 1:
	//
	//    query := sql.FROM(UserTable, sql.WHERE(sql.Field(&UserTable.ID, sql.EQ("Bond")))
	//    user := &User{}
	//    found, err := db.GetValue(query, user)
	//
	// Example usage 2:
	//
	//    query := sql.FROM(JamesBond, sql.WHERE(sql.PK(&JamesBond.ID))
	//    user := &User{}
	//    found, err := db.GetValue(query, user)
	//
	GetValue(query Expression, outBinding interface{}) (found bool, err error)

	// ListValues returns an iterator that enables to traverse all items
	// returned by the <query>.
	// Use utilities to:
	// - generate query string
	// - fill slice by values from iterator (SliceIt).
	//
	// Example usage 1 (fill slice by values from iterator):
	//
	//    query := sql.FROM(UserTable, sql.WHERE(sql.Field(&UserTable.LastName, sql.EQ("Bond")))
	//    iterator := db.ListValues(query)
	//    users := &[]User{}
	//    err := sql.SliceIt(users, iterator)
	//
	// Example usage 2:
	//
	//    query := sql.FROM(UserTable, sql.WHERE(sql.Exec("last_name='Bond'")))
	//    iterator := db.ListValues(query)
	//    users := &[]User{}
	//    err := sql.SliceIt(users, iterator)
	//
	// Example usage 3:
	//
	//    iterator := db.ListValues("select ID, first_name, last_name from User where last_name='Bond'")
	//    user := map[string]interface{}
	//    stop := iterator.GetNext(user)
	//
	ListValues(query Expression) ValIterator

	// Delete removes data from the data store
	// Example usage 1:
	//
	//    query := sql.FROM(JamesBond, sql.WHERE(sql.PK(&JamesBond.ID))
	//    err := datasync.Delete(query)
	//
	// Example usage 2:
	//
	//    err := datasync.Delete("from User where ID='James Bond'")
	//
	// Example usage 3:
	//
	//    query := sql.FROM(UserTable, sql.WHERE(sql.Field(&UserTable.LastName, sql.EQ("Bond")))
	//    err := datasync.Delete(query)
	//
	Delete(fromWhere Expression) error

	// Executes the SQL statement (can be used for example to create
	// "table/type" if not exits...)
	// Example usage:
	//
	//  	 err := db.Exec("CREATE INDEX IF NOT EXISTS...")
	Exec(statement string, bindings ...interface{}) error
}

Broker executes SQL statements in the data store. It marshals/un-marshals go structures.

type BrokerPlugin

type BrokerPlugin interface {
	// NewBroker returns a Broker instance that work with Data Base
	NewBroker() Broker
}

BrokerPlugin provides unifying interface for different SQL like datastore implementations.

type Expression

type Expression interface {
	// Stringer prints default representation of SQL to String
	// Different implementations can override this using package specific
	// func ExpToString().
	String() string

	// Binding are values referenced ("?") from the statement.
	GetBinding() []interface{}

	// Accepts calls the methods on Visitor.
	Accept(Visitor)
}

Expression represents part of SQL statement and optional binding ("?")

func AND

func AND(inside ...Expression) Expression

AND keyword of SQL expression

Example usage (alternative 1 - spare sequence of partenthesis):

WHERE(FieldEQ(&JamesBond.FirstName), AND(), FieldEQ(&JamesBond.LastName))

Example usage (alternative 2 - useful for nesting):

WHERE(AND(FieldEQ(&JamesBond.FirstName), FieldEQ(&JamesBond.LastName)))

func DELETE

func DELETE(entity interface{}, afterKeyword Expression) Expression

DELETE keyword of SQL statement

func EQ

func EQ(binding interface{}) (exp Expression)

EQ operator "=" used in SQL expressions

func Exp

func Exp(statement string, binding ...interface{}) Expression

Exp function creates instance of sql.Expression from string statement & optional binding. Useful for: - rarely used parts of SQL statements - create if not exists... statements

func FROM

func FROM(pointerToAStruct interface{}, afterKeyword Expression) Expression

FROM keyword of SQL expression Note, pointerToAStruct is assigned to Expression.binding. The implementation is supposed try to cast to the sql.TableName & sql.SchemaName.

func Field

func Field(pointerToAField interface{}, rigthOperand ...Expression) (exp Expression)

Field is a helper function to address field of a structure

Example usage:

Where(Field(&UsersTable.LastName, UsersTable, EQ('Bond'))
// generates for example "WHERE last_name='Bond'"

func FieldEQ

func FieldEQ(pointerToAField interface{}) (exp Expression)

FieldEQ is combination of Field & EQ on same pointerToAField

Example usage:

FROM(JamesBond, Where(FieldEQ(&JamesBond.LastName))
// generates for example "WHERE last_name='Bond'"
// because JamesBond is a pointer to an instance of a structure that in field LastName contains "Bond"

func GT

func GT(binding interface{}) (exp Expression)

GT operator ">" used in SQL expressions

func GTE

func GTE(binding interface{}) (exp Expression)

GTE operator "=>" used in SQL expressions

func IN

func IN(binding ...interface{}) (exp Expression)

IN operator of SQL expression

FROM(UserTable,WHERE(FieldEQ(&UserTable.FirstName, IN(JamesBond.FirstName, PeterBond.FirstName)))

func LT

func LT(binding interface{}) (exp Expression)

LT operator "<" used in SQL expressions

func LTE

func LTE(binding interface{}) (exp Expression)

LTE operator "=<" used in SQL expressions

func OR

func OR(inside ...Expression) Expression

OR keyword of SQL expression

Example usage 1 (generated string does not contains parenthesis surrounding OR):

WHERE(FieldEQ(&PeterBond.FirstName), OR(), FieldEQ(&JamesBond.FirstName))

Example usage 2 (generated string does not contains parenthesis surrounding OR):

WHERE(FieldEQ(&PeterBond.LastName), OR(FieldEQ(&PeterBond.FirstName), FieldEQ(&JamesBond.FirstName)))

func PK

func PK(pointerToAField interface{}) (exp Expression)

PK is alias FieldEQ (user for better readability)

Example usage:

FROM(JamesBond, Where(PK(&JamesBond.LastName))
// generates for example "WHERE last_name='Bond'"
// because JamesBond is a pointer to an instance of a structure that in field LastName contains "Bond"

func Parenthesis

func Parenthesis(inside ...Expression) (exp Expression)

Parenthesis expression that surrounds "inside Expression" with "(" and ")"

func SELECT

func SELECT(entity interface{}, afterKeyword Expression, binding ...interface{}) Expression

SELECT keyword of SQL expression.

func WHERE

func WHERE(afterKeyword ...Expression) Expression

WHERE keyword of SQL statement

type FieldExpression

type FieldExpression struct {
	PointerToAField interface{}
	AfterField      Expression
}

FieldExpression for addressing field of an entity in SQL expression.

func (*FieldExpression) Accept

func (exp *FieldExpression) Accept(visitor Visitor)

Accept calls VisitFieldExpression(...) & Accept(AfterField)

func (*FieldExpression) GetBinding

func (exp *FieldExpression) GetBinding() []interface{}

GetBinding is a getter...

func (*FieldExpression) String

func (exp *FieldExpression) String() string

String returns Prefix + " " + AfterPrefix.

type PrefixedExp

type PrefixedExp struct {
	Prefix      string
	AfterPrefix []Expression
	Suffix      string
	Binding     []interface{}
}

PrefixedExp covers many SQL constructions. It implements sql.Expression interface. Instance of this structure is returned by many helper functions below.

func (*PrefixedExp) Accept

func (exp *PrefixedExp) Accept(visitor Visitor)

Accept calls VisitPrefixedExp(...) & Accept(AfterPrefix)

func (*PrefixedExp) GetBinding

func (exp *PrefixedExp) GetBinding() []interface{}

GetBinding is a getter...

func (*PrefixedExp) String

func (exp *PrefixedExp) String() string

String returns Prefix + " " + AfterPrefix.

type SchemaName

type SchemaName interface {
	// SchemaName returns sql schema name where the table resides
	SchemaName() string
}

SchemaName interface is used to specify custom schema name for SQL statements

type TableName

type TableName interface {
	// TableName returns sql table name.
	TableName() string
}

TableName interface is used to specify custom table name for SQL statements.

type Txn

type Txn interface {
	// Put adds put operation into the transaction.
	Put(where Expression, data interface{}) Txn
	// Delete adds delete operation into the transaction.
	Delete(fromWhere Expression) Txn
	// Commit tries to commit the transaction.
	Commit() error
}

Txn allows to group operations into the transaction or batch (depending on a particular data store). Transaction executes usually multiple operations in a more efficient way in contrast to executing them one by one.

type ValIterator

type ValIterator interface {
	// GetNext retrieves the current "row" from query result.
	// GetValue is un-marshaled into the provided argument.
	// The stop=true will be returned if there is no more record or if error
	// occurred (to get the error call Close()).
	// When the stop=true is returned the outBinding was not updated.
	GetNext(outBinding interface{}) (stop bool)

	// Closer is used to retrieve error (if occurred) & releases the cursor
	io.Closer
}

ValIterator is an iterator returned by ListValues call.

type Visitor

type Visitor interface {
	VisitPrefixedExp(*PrefixedExp)
	VisitFieldExpression(*FieldExpression)
}

Visitor is used for traversing expression tree.

type WatchResp

type WatchResp interface {
	// GetChangeType return the type of the change.
	GetChangeType() datasync.PutDel

	// GetValue returns the changed value.
	GetValue(outBinding interface{}) error
}

WatchResp represents a notification about change. It is passed to the Watch callback.

type Watcher

type Watcher interface {
	// Watch starts to monitor changes in a data store.
	// Watch events will be delivered to the <callback>.
	Watch(callback func(WatchResp), statement ...string) error
}

Watcher defines API for monitoring changes in a datastore.