ql

package module
v0.0.0-...-0380b65 Latest Latest
Warning

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

 Go to latest Published: Nov 6, 2018 License: MIT Imports: 13 Imported by: 0

README

ql GoDoc

This is a fork of the wonderful gocraft/dbr. I have made some changes to the package interface to better fit my needs. Before you look at this README be sure to have read the original README for gocraft/dbr.

Deprecated

This package is deprecated. The design has been reworked from the ground up in mibk/dali.

Instalation

go get github.com/mibk/ql

Differences from gocraft/dbr

No Session

All methods are available directly on *ql.Connection, there is no need to do conn.NewSession(nil). I've found it not necessary as the *dbr.Session is only a wrapper around *dbr.Connection whitch enables setting a different EventReceiver. If it is so, I would suggest a different approach for setting different EventReceiver (currently not provided).

Query

There is only Query method indstead of SelectBySql and UpdateBySql. For me, they were 2 methods doing the same thing. Query is a superior of these 2. It handles arbitrary SQL statements (not only SELECT and UPDATE, although the previous 2 were actualy capable of it).

There is Exec method for running INSERT, UPDATE, DELETE, ..., and there are methods for loading values returned by SELECT statement (will be explained later).

All and One methods

SelectBuilder and Query have new methods for loading values: All and One. They replace old methods for loading (LoadStructs, LoadStruct, LoadValues, LoadValue). All handles pointer to a slice of pointers to an arbitrary value (primitive value, or a struct), and One works likewise for just one pointer to an arbitrary value.

Methods for quick returning returning primitive types (ReturnInt64, ReturnStrings, ...) were remained.

String methods

For all builders and Query there is the String method, which returns an interpolated (and preprocessed) SQL statement. Useful for debugging (it is possible to just fmt.Println a builder).

Functions for opening DB

There are shortcut functions for opening a DB and creating new *Connection (Open, MustOpen, and MustOpenAndVerify, which performs a ping to the database).

Quoting identifiers (columns)

It is possible to escape columns using brackets.

var u User
db.Query(`SELECT [name], [age], [from] AS [birth_place]
	FROM [user]
	WHERE [id] = ?`, 15).One(&u)
// It executes:
// SELECT `name`, `age`, `from` AS `birth_place` FROM `user` WHERE `id` = 15

I have found it convenient to write SQL statements on multiple lines for readability reasons. But that is only possible using backticks that are also used for column escaping.

Null types in null package

Null* types where moved to the ql/null package. Example of usage:

import "github.com/mibk/ql/null"

type Nonsence struct {
	Number      null.Int64
	Description null.String
}
Updated Where (and Having) method

Where now accepts only string or ql.And type as the first argument. ql.And is the replacement for the original Eq type. It's because ql.And goes beyond an equality map. When an ql.And map is the input, it is only a shorthand for calling the Where (Having) method multiple times with 2 arguments: an expression string and one parameter. Example:

b.Where(ql.And{"name": "Igor", "age >": 50})
// is equivalent to:
b.Where("name", "Igor")
b.Where("age >", 50)

As you can see in these simple examples it is not required to type an operator, or a placeholder. That is so called "short notation" of the format:

column_name [operator] [?]

Default operator is = and typing a placeholder is just optional. If the expression doesn't match this format, it works as before.

Order method

OrderBy methods works as before, but there is new method Order which replaces the OrderDir. It expects the ql.By type as a parameter which is a map of column names and order directions. Example:

b.Order(ql.By{"col1:": ql.Asc, "col2:": ql.Desc})
// ORDER BY `col1` ASC, `col2` DESC
Removed objects
  • Paginate on *SelectBuilder was removed. It should probably be in another layer.
  • No NullTime as it was dependent on the mysql driver.

Quickstart

package main

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/mibk/ql"
	"github.com/mibk/ql/null"
)

type User struct {
	Id      int64
	Name    string
	Comment null.String
}

var conn *ql.Connection

func main() {
	// It panics on error and after successful opening it tries to ping the database
	// to test the connection.
	conn = ql.MustOpenAndVerify("mysql", "root@/your_database")

	var u User
	b := conn.Select("id, [title]").From("user").Where("id = ?", 13)

	// This will print the interpolated query:
	//     SELECT id, `title` FROM user WHERE (id = 13)
	fmt.Println(b)

	// Method One will execute the query and load the result to the u struct.
	// If it was not set before, it also sets LIMIT to 1 as there is no need
	// for returning multiple rows.
	//     SELECT id, `title` FROM user WHERE (id = 13) LIMIT 1
	if err := b.One(&u); err != nil {
		panic(err)
	}
	fmt.Printf("User: %+v", u)
}

Driver support

Currently only MySQL is supported. I am planning to move all driver dependent parts to gl/dialect package.

Authors:

Documentation

Index

Constants

View Source 
const (
	Asc  direction = false
	Desc direction = true
)

These constant are used to indicate a direction of an ORDER clause. They are used as a value in the column/direction map in Order method.

Variables

View Source 
var (
	ErrNotFound           = errors.New("not found")
	ErrNotUTF8            = errors.New("invalid UTF-8")
	ErrInvalidSliceLength = errors.New("length of slice is 0. length must be >= 1")
	ErrInvalidSliceValue  = errors.New("trying to interpolate invalid slice value into query")
	ErrInvalidValue       = errors.New("trying to interpolate invalid value into query")
	ErrArgumentMismatch   = errors.New("mismatch between ? (placeholders) and arguments")
	ErrInvalidSyntax      = errors.New("SQL syntax error")
)
View Source 
var NameMapping = camelCaseToSnakeCase

NameMapping is the routine to use when mapping column names to struct properties

Functions

func Date 

func Date(t time.Time) date

Date turns time into date type. It is supposed to be used in queries to use 2006-01-02 time format.

func Expr 

func Expr(sql string, values ...interface{}) *expr

Expr is a SQL fragment with placeholders, and a slice of args to replace them with.

func Preprocess 

func Preprocess(sql string, vals []interface{}) (string, error)

Preprocess takes an SQL string with placeholders and a list of arguments to replace them with. It returns a blank string and error if the number of placeholders does not match the number of arguments.

Types

type And 

type And map[string]interface{}

And is a map column -> value pairs which must be matched in a query.

type By 

type By map[string]direction

By is a map of columns and order directions used in builders' Order methods. Example of usage: 

b.Order(ql.By{"col1": ql.Asc,"col2": ql.Desc})

type Connection 

type Connection struct {
	DB *sql.DB
	EventReceiver
}

Connection is a connection to the database with an EventReceiver to send events, errors, and timings to.

func MustOpen 

func MustOpen(driverName, dataSourceName string) *Connection

MustOpen is like Open but panics on error.

func MustOpenAndVerify 

func MustOpenAndVerify(driverName, dataSourceName string) *Connection

MustOpenAndVerify is like MustOpen but it verifies the connection and panics on error.

func NewConnection 

func NewConnection(db *sql.DB, log EventReceiver) *Connection

NewConnection instantiates a Connection for a given database/sql connection and event receiver.

func Open 

func Open(driverName, dataSourceName string) (*Connection, error)

Open opens a database by calling sql.Open. It returns new Connection with nil EventReceiver.

func (*Connection) Begin 

func (db *Connection) Begin() (*Tx, error)

Begin creates a transaction for the given connection.

func (*Connection) Close 

func (c *Connection) Close() error

Close closes the database, releasing any open resources.

func (*Connection) DeleteFrom 

func (db *Connection) DeleteFrom(from string) *DeleteBuilder

DeleteFrom creates a new DeleteBuilder for the given table.

func (*Connection) InsertInto 

func (db *Connection) InsertInto(into string) *InsertBuilder

InsertInto instantiates a InsertBuilder for the given table.

func (*Connection) Ping 

func (c *Connection) Ping() error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (*Connection) Query 

func (db *Connection) Query(sql string, args ...interface{}) *Query

Query creates Query by the raw SQL query and args.

func (*Connection) Select 

func (db *Connection) Select(cols ...string) *SelectBuilder

Select creates a new SelectBuilder that select that given columns.

func (*Connection) Update 

func (db *Connection) Update(table string) *UpdateBuilder

Update creates a new UpdateBuilder for the given table.

type DeleteBuilder 

type DeleteBuilder struct {
	From string
	// contains filtered or unexported fields
}

DeleteBuilder contains the clauses for a DELETE statement.

func (DeleteBuilder) Exec 

func (e DeleteBuilder) Exec() (sql.Result, error)

Exec executes the query. It returns the raw database/sql Result and an error if there is one.

func (*DeleteBuilder) Limit 

func (b *DeleteBuilder) Limit(limit uint64) *DeleteBuilder

Limit sets a LIMIT clause for the statement; overrides any existing LIMIT.

func (DeleteBuilder) MustExec 

func (e DeleteBuilder) MustExec() sql.Result

MustExec is like Exec but panics on error.

func (*DeleteBuilder) Offset 

func (b *DeleteBuilder) Offset(offset uint64) *DeleteBuilder

Offset sets an OFFSET clause for the statement; overrides any existing OFFSET.

func (*DeleteBuilder) Order 

func (b *DeleteBuilder) Order(by By) *DeleteBuilder

Order accepts By map of columns and directions to ORDER the statement by.

func (*DeleteBuilder) OrderBy 

func (b *DeleteBuilder) OrderBy(expr string) *DeleteBuilder

OrderBy appends an ORDER BY clause to the statement.

func (*DeleteBuilder) String 

func (b *DeleteBuilder) String() string

String returns a string representing a preprocessed, interpolated, query.

func (*DeleteBuilder) ToSql 

func (b *DeleteBuilder) ToSql() (string, []interface{})

ToSql serialized the DeleteBuilder to a SQL string. It returns the string with placeholders and a slice of query arguments.

func (*DeleteBuilder) Where 

func (b *DeleteBuilder) Where(whereSqlOrMap interface{}, args ...interface{}) *DeleteBuilder

Where appends a WHERE clause to the statement whereSqlOrMap can be a string or map. If it's a string, args wil replaces any places holders.

type Dialect 

type Dialect interface {
	EscapeIdent(w query.Writer, ident string)
	EscapeBool(w query.Writer, b bool)
	EscapeString(w query.Writer, s string)
	EscapeTime(w query.Writer, t time.Time)
	ApplyLimitAndOffset(w query.Writer, limit, offset uint64)
}

Dialect is an interface that wraps the diverse properties of individual SQL drivers. 

var D Dialect = dialect.Mysql{}

type EventReceiver 

type EventReceiver interface {
	Event(eventName string)
	EventKv(eventName string, kvs map[string]string)
	EventErr(eventName string, err error) error
	EventErrKv(eventName string, err error, kvs map[string]string) error
	Timing(eventName string, nanoseconds int64)
	TimingKv(eventName string, nanoseconds int64, kvs map[string]string)
}

EventReceiver gets events from dbr methods for logging purposes.

type InsertBuilder 

type InsertBuilder struct {
	Into string
	Cols []string
	Vals [][]interface{}
	Recs []interface{}
	// contains filtered or unexported fields
}

InsertBuilder contains the clauses for an INSERT statement.

func (*InsertBuilder) Columns 

func (b *InsertBuilder) Columns(columns ...string) *InsertBuilder

Columns appends columns to insert in the statement.

func (InsertBuilder) Exec 

func (e InsertBuilder) Exec() (sql.Result, error)

Exec executes the query. It returns the raw database/sql Result and an error if there is one.

func (InsertBuilder) MustExec 

func (e InsertBuilder) MustExec() sql.Result

MustExec is like Exec but panics on error.

func (*InsertBuilder) Pair 

func (b *InsertBuilder) Pair(column string, value interface{}) *InsertBuilder

Pair adds a key/value pair to the statement.

func (*InsertBuilder) Record 

func (b *InsertBuilder) Record(record interface{}) *InsertBuilder

Record pulls in values to match Columns from the record.

func (*InsertBuilder) String 

func (b *InsertBuilder) String() string

String returns a string representing a preprocessed, interpolated, query.

func (*InsertBuilder) ToSql 

func (b *InsertBuilder) ToSql() (string, []interface{})

ToSql serialized the InsertBuilder to a SQL string. It returns the string with placeholders and a slice of query arguments.

func (*InsertBuilder) Values 

func (b *InsertBuilder) Values(vals ...interface{}) *InsertBuilder

Values appends a set of values to the statement.

type NullEventReceiver 

type NullEventReceiver struct{}

NullEventReceiver is a sentinel EventReceiver; use it if the caller doesn't supply one.

func (*NullEventReceiver) Event 

func (n *NullEventReceiver) Event(eventName string)

Event receives a simple notification when various events occur.

func (*NullEventReceiver) EventErr 

func (n *NullEventReceiver) EventErr(eventName string, err error) error

EventErr receives a notification of an error if one occurs.

func (*NullEventReceiver) EventErrKv 

func (n *NullEventReceiver) EventErrKv(eventName string, err error, kvs map[string]string) error

EventErrKv receives a notification of an error if one occurs along with optional key/value data.

func (*NullEventReceiver) EventKv 

func (n *NullEventReceiver) EventKv(eventName string, kvs map[string]string)

EventKv receives a notification when various events occur along with optional key/value data.

func (*NullEventReceiver) Timing 

func (n *NullEventReceiver) Timing(eventName string, nanoseconds int64)

Timing receives the time an event took to happen.

func (*NullEventReceiver) TimingKv 

func (n *NullEventReceiver) TimingKv(eventName string, nanoseconds int64, kvs map[string]string)

TimingKv receives the time an event took to happen along with optional key/value data.

type Query 

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

Query represent an arbitrary SQL statement.

func (Query) All 

func (l Query) All(dest interface{}) (n int, err error)

All executes the query and loads the resulting data into the dest, which can be a slice of either structs, or primitive values. It returns n found items (which is not necessarily the number of items set).

func (Query) Exec 

func (e Query) Exec() (sql.Result, error)

Exec executes the query. It returns the raw database/sql Result and an error if there is one.

func (Query) MustExec 

func (e Query) MustExec() sql.Result

MustExec is like Exec but panics on error.

func (Query) One 

func (l Query) One(dest interface{}) error

One executes the query and loads the resulting data into the dest, which can be either a struct, or a primitive value. Returns ErrNotFound if no item was found, and it was therefore not set.

func (Query) ReturnInt64 

func (l Query) ReturnInt64() (int64, error)

ReturnInt64 executes the SelectBuilder and returns the value as an int64.

func (Query) ReturnInt64s 

func (l Query) ReturnInt64s() ([]int64, error)

ReturnInt64s executes the SelectBuilder and returns the value as a slice of int64s.

func (Query) ReturnString 

func (l Query) ReturnString() (string, error)

ReturnString executes the SelectBuilder and returns the value as a string.

func (Query) ReturnStrings 

func (l Query) ReturnStrings() ([]string, error)

ReturnStrings executes the SelectBuilder and returns the value as a slice of strings.

func (Query) ReturnUint64 

func (l Query) ReturnUint64() (uint64, error)

ReturnUint64 executes the SelectBuilder and returns the value as an uint64.

func (Query) ReturnUint64s 

func (l Query) ReturnUint64s() ([]uint64, error)

ReturnUint64s executes the SelectBuilder and returns the value as a slice of uint64s.

func (*Query) String 

func (q *Query) String() string

String returns a string representing a preprocessed, interpolated, query.

func (*Query) ToSql 

func (q *Query) ToSql() (string, []interface{})

ToSql returns the raw SQL query and args.

type SelectBuilder 

type SelectBuilder struct {
	IsDistinct      bool
	Columns         []string
	FromTable       string
	GroupBys        []string
	HavingFragments []*whereFragment
	// contains filtered or unexported fields
}

SelectBuilder contains the clauses for a SELECT statement.

func (SelectBuilder) All 

func (l SelectBuilder) All(dest interface{}) (n int, err error)

All executes the query and loads the resulting data into the dest, which can be a slice of either structs, or primitive values. It returns n found items (which is not necessarily the number of items set).

func (*SelectBuilder) Distinct 

func (b *SelectBuilder) Distinct() *SelectBuilder

Distinct marks the statement as a DISTINCT SELECT.

func (*SelectBuilder) From 

func (b *SelectBuilder) From(from string) *SelectBuilder

From sets the table to SELECT FROM.

func (*SelectBuilder) GroupBy 

func (b *SelectBuilder) GroupBy(group string) *SelectBuilder

GroupBy appends a column to group the statement.

func (*SelectBuilder) Having 

func (b *SelectBuilder) Having(exprOrMap interface{}, args ...interface{}) *SelectBuilder

Having appends a HAVING clause to the statement.

func (*SelectBuilder) Limit 

func (b *SelectBuilder) Limit(limit uint64) *SelectBuilder

Limit sets a limit for the statement; overrides any existing LIMIT.

func (*SelectBuilder) Offset 

func (b *SelectBuilder) Offset(offset uint64) *SelectBuilder

Offset sets an offset for the statement; overrides any existing OFFSET.

func (*SelectBuilder) One 

func (b *SelectBuilder) One(dest interface{}) error

One executes the query and loads the resulting data into the dest, which can be either a struct, or a primitive value. Returns ErrNotFound if no item was found, and it was therefore not set.

func (*SelectBuilder) Order 

func (b *SelectBuilder) Order(by By) *SelectBuilder

Order accepts By map of columns and directions to ORDER the statement by.

func (*SelectBuilder) OrderBy 

func (b *SelectBuilder) OrderBy(expr string) *SelectBuilder

OrderBy appends a column to ORDER the statement by.

func (SelectBuilder) ReturnInt64 

func (l SelectBuilder) ReturnInt64() (int64, error)

ReturnInt64 executes the SelectBuilder and returns the value as an int64.

func (SelectBuilder) ReturnInt64s 

func (l SelectBuilder) ReturnInt64s() ([]int64, error)

ReturnInt64s executes the SelectBuilder and returns the value as a slice of int64s.

func (SelectBuilder) ReturnString 

func (l SelectBuilder) ReturnString() (string, error)

ReturnString executes the SelectBuilder and returns the value as a string.

func (SelectBuilder) ReturnStrings 

func (l SelectBuilder) ReturnStrings() ([]string, error)

ReturnStrings executes the SelectBuilder and returns the value as a slice of strings.

func (SelectBuilder) ReturnUint64 

func (l SelectBuilder) ReturnUint64() (uint64, error)

ReturnUint64 executes the SelectBuilder and returns the value as an uint64.

func (SelectBuilder) ReturnUint64s 

func (l SelectBuilder) ReturnUint64s() ([]uint64, error)

ReturnUint64s executes the SelectBuilder and returns the value as a slice of uint64s.

func (*SelectBuilder) String 

func (b *SelectBuilder) String() string

String returns a string representing a preprocessed, interpolated, query.

func (*SelectBuilder) ToSql 

func (b *SelectBuilder) ToSql() (string, []interface{})

ToSql serialized the SelectBuilder to a SQL string. It returns the string with placeholders and a slice of query arguments.

func (*SelectBuilder) Where 

func (b *SelectBuilder) Where(whereSqlOrMap interface{}, args ...interface{}) *SelectBuilder

Where appends a WHERE clause to the statement for the given string and args or map of column/value pairs.

type Tx 

type Tx struct {
	*Connection
	*sql.Tx
}

Tx is a transaction for the given Session.

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit finishes the transaction.

func (*Tx) DeleteFrom 

func (tx *Tx) DeleteFrom(from string) *DeleteBuilder

DeleteFrom creates a new DeleteBuilder for the given table in the context for a transaction.

func (*Tx) InsertInto 

func (tx *Tx) InsertInto(into string) *InsertBuilder

InsertInto instantiates a InsertBuilder for the given table bound to a transaction.

func (*Tx) Query 

func (tx *Tx) Query(sql string, args ...interface{}) *Query

Query creates Query by the raw SQL query and args. Query is bound to the transaction.

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback cancels the transaction.

func (*Tx) RollbackUnlessCommitted 

func (tx *Tx) RollbackUnlessCommitted()

RollbackUnlessCommitted rollsback the transaction unless it has already been committed or rolled back. Useful to defer tx.RollbackUnlessCommitted() -- so you don't have to handle N failure cases. Keep in mind the only way to detect an error on the rollback is via the event log.

func (*Tx) Select 

func (tx *Tx) Select(cols ...string) *SelectBuilder

Select creates a new SelectBuilder that select that given columns bound to the transaction.

func (*Tx) Update 

func (tx *Tx) Update(table string) *UpdateBuilder

Update creates a new UpdateBuilder for the given table bound to a transaction.

type UpdateBuilder 

type UpdateBuilder struct {
	Table      string
	SetClauses []*setClause
	// contains filtered or unexported fields
}

UpdateBuilder contains the clauses for an UPDATE statement.

func (UpdateBuilder) Exec 

func (e UpdateBuilder) Exec() (sql.Result, error)

Exec executes the query. It returns the raw database/sql Result and an error if there is one.

func (*UpdateBuilder) Limit 

func (b *UpdateBuilder) Limit(limit uint64) *UpdateBuilder

Limit sets a limit for the statement; overrides any existing LIMIT.

func (UpdateBuilder) MustExec 

func (e UpdateBuilder) MustExec() sql.Result

MustExec is like Exec but panics on error.

func (*UpdateBuilder) Offset 

func (b *UpdateBuilder) Offset(offset uint64) *UpdateBuilder

Offset sets an offset for the statement; overrides any existing OFFSET.

func (*UpdateBuilder) Order 

func (b *UpdateBuilder) Order(by By) *UpdateBuilder

Order accepts By map of columns and directions to ORDER the statement by.

func (*UpdateBuilder) OrderBy 

func (b *UpdateBuilder) OrderBy(expr string) *UpdateBuilder

OrderBy appends a column to ORDER the statement by.

func (*UpdateBuilder) Set 

func (b *UpdateBuilder) Set(column string, value interface{}) *UpdateBuilder

Set appends a column/value pair for the statement.

func (*UpdateBuilder) SetMap 

func (b *UpdateBuilder) SetMap(clauses map[string]interface{}) *UpdateBuilder

SetMap appends the elements of the map as column/value pairs for the statement.

func (*UpdateBuilder) String 

func (b *UpdateBuilder) String() string

String returns a string representing a preprocessed, interpolated, query.

func (*UpdateBuilder) ToSql 

func (b *UpdateBuilder) ToSql() (string, []interface{})

ToSql serialized the UpdateBuilder to a SQL string. It returns the string with placeholders and a slice of query arguments.

func (*UpdateBuilder) Where 

func (b *UpdateBuilder) Where(whereSqlOrMap interface{}, args ...interface{}) *UpdateBuilder

Where appends a WHERE clause to the statement.

Source Files

View all Source files

Directories

Path Synopsis
Package null defines Null variants of the primitive types.
 Package null defines Null variants of the primitive types.

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.