sqlapi

package module
v0.7.0 Latest Latest
Warning

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

 Go to latest Published: Sep 11, 2018 License: BSD-2-Clause Imports: 10 Imported by: 2

README

sqlapi

sqlgen generates SQL statements and database helper functions from your Go structs. It can be used in place of a simple ORM or hand-written SQL. sqlapi (this package) supports the generated code.

See the demo directory for examples. Look in the generated files *_sql.go and the hand-crafted files (hook.go, issue.go, user.go).

Features

  • Auto-generates DAO-style table-support code for SQL databases.
  • Sophisticated parsing of a Go struct that describes records in the table.
  • Allows nesting of structs, fields that are structs or pointers to structs etc.
  • Struct tags give fine control over the semantics of each field.
  • Supports indexes and constraints.
  • Supports foreign key relationships between tables.
  • Helps you develop code for joins and views.
  • Supports JSON-encoded columns, allowing a more no-SQL model when needed.
  • Provides a builder-style API for constructing where-clauses and query constraints.
  • Allows declarative requirements on the expected result of each query, enhancing error checking.
  • Very flexible configuration.
  • Fast and easy to use.

Currently, support is included for MySQL, PostgreSQL and SQLite. Other dialects can be added relatively easy - send a Pull Request!

Install

Add to your imports some references to github.com/rickb777/sqlapi and then install with this command:

go mod tidy

If you're using an older version of Go, you'll need

go get github.com/rickb777/sqlapi

Please continue reading about sqlgen2.

Documentation

Overview

Package sqlapi contains a small API for a tool (sqlgen2) that generates SQL functions for specified struct types.

Lighter than a full-blown ORM and simpler than hand-written code, the output makes it easy to write flexible yet reliable and high-performance database code.

See the README for further details: https://github.com/rickb777/sqlapi/blob/master/README.md

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Named 

func Named(name string, value interface{}) sql.NamedArg

Named creates NamedArg values; it is synonymous with sql.Named().

func NamedArgString 

func NamedArgString(arg sql.NamedArg) string

NamedArgString converts the argument to a string of the form "name=value".

Types

type CanPostGet 

type CanPostGet interface {
	PostGet() error
}

CanPostGet is implemented by value types that need a hook to run just after their data is fetched from the database.

type CanPreInsert 

type CanPreInsert interface {
	PreInsert() error
}

CanPreInsert is implemented by value types that need a hook to run just before their data is inserted into the database.

type CanPreUpdate 

type CanPreUpdate interface {
	PreUpdate() error
}

CanPreUpdate is implemented by value types that need a hook to run just before their data is updated in the database.

type Database 

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

Database wraps a *sql.DB with a dialect and (optionally) a logger. It's safe for concurrent use by multiple goroutines.

func NewDatabase 

func NewDatabase(db Execer, dialect schema.Dialect, logger *log.Logger, wrapper interface{}) *Database

NewDatabase creates a new database handler, which wraps the core *sql.DB along with the appropriate dialect.

You can supply the logger you need, or else nil. If not nil, all queries will be logged and all database errors will be logged. Once constructed, the logger itself cannot be changed, but its output writer can (via the SetOutput method on Logger). Logging can be enabled and disabled as needed by using the TraceLogging method.

The wrapper holds some associated data your application needs for this database, if any. Otherwise this should be nil. As with the logger, it cannot be changed after construction.

func (*Database) Begin 

func (database *Database) Begin() (*sql.Tx, error)

Begin starts a transaction using default options. The default isolation level is dependent on the driver.

func (*Database) BeginTx 

func (database *Database) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

BeginTx starts a transaction.

The context is used until the transaction is committed or rolled back. If this context is cancelled, the sql package will roll back the transaction. In this case, Tx.Commit will then return an error.

The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.

Panics if the Execer is not a TxStarter.

func (*Database) DB 

func (database *Database) DB() Execer

DB gets the Execer, which is a *sql.DB (except during testing using mocks).

func (*Database) Dialect 

func (database *Database) Dialect() schema.Dialect

Dialect gets the current SQL dialect. This choice is determined when the Database is constructed and doesn't subsequently change.

func (*Database) Exec 

func (database *Database) Exec(query string, args ...interface{}) (sql.Result, error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*Database) ExecContext 

func (database *Database) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*Database) ListTables 

func (database *Database) ListTables() (util.StringList, error)

ListTables gets all the table names in the database/schema.

func (*Database) LogError 

func (database *Database) LogError(err error) error

LogError writes error info to the logger, if the logger is not nil. It returns the error.

func (*Database) LogIfError 

func (database *Database) LogIfError(err error) error

LogIfError writes error info to the logger, if both the logger and the error are non-nil. It returns the error.

func (*Database) LogQuery 

func (database *Database) LogQuery(query string, args ...interface{})

LogQuery writes query info to the logger, if it is not nil.

func (*Database) Logger 

func (database *Database) Logger() *log.Logger

Logger gets the trace logger. Note that you can use this to rotate the output writer via its SetOutput method. Also, it can even disable it completely (via ioutil.Discard).

func (*Database) Ping 

func (database *Database) Ping() error

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

func (*Database) PingContext 

func (database *Database) PingContext(ctx context.Context) error

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

func (*Database) Prepare 

func (database *Database) Prepare(query string) (*sql.Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (*Database) PrepareContext 

func (database *Database) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

The provided context is used for the preparation of the statement, not for the execution of the statement.

func (*Database) Query 

func (database *Database) Query(query string, args ...interface{}) (*sql.Rows, error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*Database) QueryContext 

func (database *Database) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*Database) QueryRow 

func (database *Database) QueryRow(query string, args ...interface{}) *sql.Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Database) QueryRowContext 

func (database *Database) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Database) ScanFloat32List 

func (database *Database) ScanFloat32List(req require.Requirement, rows *sql.Rows) ([]float32, error)

ScanFloat32List processes result rows to extract a list of float32s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanFloat64List 

func (database *Database) ScanFloat64List(req require.Requirement, rows *sql.Rows) ([]float64, error)

ScanFloat64List processes result rows to extract a list of float64s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanInt16List 

func (database *Database) ScanInt16List(req require.Requirement, rows *sql.Rows) ([]int16, error)

ScanInt16List processes result rows to extract a list of int32s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanInt32List 

func (database *Database) ScanInt32List(req require.Requirement, rows *sql.Rows) ([]int32, error)

ScanInt32List processes result rows to extract a list of int32s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanInt64List 

func (database *Database) ScanInt64List(req require.Requirement, rows *sql.Rows) ([]int64, error)

ScanInt64List processes result rows to extract a list of int64s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanInt8List 

func (database *Database) ScanInt8List(req require.Requirement, rows *sql.Rows) ([]int8, error)

ScanInt8List processes result rows to extract a list of int8s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanIntList 

func (database *Database) ScanIntList(req require.Requirement, rows *sql.Rows) ([]int, error)

ScanIntList processes result rows to extract a list of ints. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanStringList 

func (database *Database) ScanStringList(req require.Requirement, rows *sql.Rows) ([]string, error)

ScanStringList processes result rows to extract a list of strings. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanUint16List 

func (database *Database) ScanUint16List(req require.Requirement, rows *sql.Rows) ([]uint16, error)

ScanUint16List processes result rows to extract a list of uint16s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanUint32List 

func (database *Database) ScanUint32List(req require.Requirement, rows *sql.Rows) ([]uint32, error)

ScanUint32List processes result rows to extract a list of uint32s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanUint64List 

func (database *Database) ScanUint64List(req require.Requirement, rows *sql.Rows) ([]uint64, error)

ScanUint64List processes result rows to extract a list of uint64s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanUint8List 

func (database *Database) ScanUint8List(req require.Requirement, rows *sql.Rows) ([]uint8, error)

ScanUint8List processes result rows to extract a list of uint8s. The result set should have been produced via a SELECT statement on just one column.

func (*Database) ScanUintList 

func (database *Database) ScanUintList(req require.Requirement, rows *sql.Rows) ([]uint, error)

ScanUintList processes result rows to extract a list of uints. The result set should have been produced via a SELECT statement on just one column.

func (*Database) Stats 

func (database *Database) Stats() sql.DBStats

Stats returns database statistics.

func (*Database) TableExists 

func (database *Database) TableExists(name TableName) (yes bool, err error)

DoesTableExist gets all the table names in the database/schema.

func (*Database) TraceLogging 

func (database *Database) TraceLogging(on bool)

TraceLogging turns query trace logging on or off. This has no effect unless the Database was created with a non-nil logger.

func (*Database) Wrapper 

func (database *Database) Wrapper() interface{}

Wrapper gets whatever structure is present, as needed.

type Execer 

type Execer interface {
	// ExecContext executes a query without returning any rows.
	// The args are for any placeholder parameters in the query.
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// PrepareContext creates a prepared statement for later queries or executions.
	// Multiple queries or executions may be run concurrently from the
	// returned statement.
	// The caller must call the statement's Close method
	// when the statement is no longer needed.
	//
	// The provided context is used for the preparation of the statement, not for the
	// execution of the statement.
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

	// QueryContext executes a query that returns rows, typically a SELECT.
	// The args are for any placeholder parameters in the query.
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

	// QueryRowContext executes a query that is expected to return at most one row.
	// QueryRowContext always returns a non-nil value. Errors are deferred until
	// Row's Scan method is called.
	// If the query selects no rows, the *Row's Scan will return ErrNoRows.
	// Otherwise, the *Row's Scan scans the first selected row and discards
	// the rest.
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Execer describes the methods of the core database API. See database/sql/DB and database/sql/Tx.

type NamedArgList 

type NamedArgList []sql.NamedArg

NamedArgList holds a slice of NamedArgs

func (NamedArgList) Assignments 

func (list NamedArgList) Assignments(d schema.Dialect, from int) []string

Assignments gets the assignment expressions.

func (NamedArgList) Contains 

func (list NamedArgList) Contains(name string) bool

Contains tests whether anything in the list has a certain name.

func (NamedArgList) Exists 

func (list NamedArgList) Exists(fn func(sql.NamedArg) bool) bool

Exists verifies that one or more elements of NamedArgList return true for the passed func.

func (NamedArgList) Find 

func (list NamedArgList) Find(fn func(sql.NamedArg) bool) (sql.NamedArg, bool)

Find returns the first sql.NamedArg that returns true for some function. False is returned if none match.

func (NamedArgList) FindByName 

func (list NamedArgList) FindByName(name string) (sql.NamedArg, bool)

FindByName finds the first item with a particular name.

func (NamedArgList) MkString 

func (list NamedArgList) MkString(sep string) string

MkString produces a string ontainin all the values separated by sep.

func (NamedArgList) Names 

func (list NamedArgList) Names() []string

Names gets all the names.

func (NamedArgList) String 

func (list NamedArgList) String() string

String produces a string ontainin all the values separated by comma.

func (NamedArgList) Values 

func (list NamedArgList) Values() []interface{}

Values gets all the valules

type RowData 

type RowData struct {
	Columns     []string
	ColumnTypes []*sql.ColumnType
	Data        util.StringAnyMap
}

RowData holds a single row result from the database.

type Rows 

type Rows struct {
	Rows *sql.Rows
	// contains filtered or unexported fields
}

Rows provides a tool for scanning result *sql.Rows of arbitrary or varying length. The internal *sql.Rows field is exported and is usable as per normal via its Next and Scan methods, or the Next and ScanToMap methods can be used instead.

func WrapRows 

func WrapRows(rows *sql.Rows) (*Rows, error)

WrapRows wraps a *sql.Rows result so that its data can be scanned into a series of maps, one for each row.

func (*Rows) Close 

func (rams *Rows) Close() error

Close closes the Rows, preventing further enumeration. If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

func (*Rows) ColumnTypes 

func (rams *Rows) ColumnTypes() ([]*sql.ColumnType, error)

ColumnTypes returns column information such as column type, length, and nullable. Some information may not be available from some drivers.

func (*Rows) Columns 

func (rams *Rows) Columns() ([]string, error)

Columns returns the column names.

func (*Rows) Err 

func (rams *Rows) Err() error

Err returns the error, if any, that was encountered during iteration. Err may be called after an explicit or implicit Close.

func (*Rows) Next 

func (rams *Rows) Next() bool

Next prepares the next result row for reading with the Scan method. It returns true on success, or false if there is no next result row or an error happened while preparing it. Err should be consulted to distinguish between the two cases.

Every call to Scan, even the first one, must be preceded by a call to Next.

func (*Rows) Scan 

func (rams *Rows) Scan(dest ...interface{}) error

Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in the wrapped Rows.

func (*Rows) ScanToMap 

func (rams *Rows) ScanToMap() (RowData, error)

ScanToMap copies all the column data of the current row into a map. The map is keyed by column name.

The result describes a single row from the database, consisting of the column names, types and data.

type Table 

type Table interface {
	// Name gets the table name. without prefix
	Name() TableName

	// Database gets the shared database information.
	Database() *Database

	// Execer gets the wrapped database or transaction handle.
	Execer() Execer

	// DB gets the wrapped database handle, provided this is not within a transaction.
	// Panics if it is in the wrong state - use IsTx() if necessary.
	DB() *sql.DB

	// Tx gets the wrapped transaction handle, provided this is within a transaction.
	// Panics if it is in the wrong state - use IsTx() if necessary.
	Tx() *sql.Tx

	// IsTx tests whether this is within a transaction.
	IsTx() bool

	// Ctx gets the current request context.
	Ctx() context.Context

	// Dialect gets the database dialect.
	Dialect() schema.Dialect

	// Logger gets the trace logger.
	Logger() *log.Logger

	// Query is the low-level request method for this table. The query is logged using whatever logger is
	// configured. If an error arises, this too is logged.
	//
	// If you need a context other than the background, use WithContext before calling Query.
	//
	// The args are for any placeholder parameters in the query.
	//
	// The caller must call rows.Close() on the result.
	Query(query string, args ...interface{}) (*sql.Rows, error)
}

Table provides the generic features of each generated table handler.

type TableCreator 

type TableCreator interface {
	Table

	// CreateTable creates the database table.
	CreateTable(ifNotExists bool) (int64, error)

	// DropTable drops the database table.
	DropTable(ifExists bool) (int64, error)

	// Truncate empties the table
	Truncate(force bool) (err error)
}

type TableName 

type TableName struct {
	// Prefix on the table name. It can be used as the schema name, in which case
	// it should include the trailing dot. Or it can be any prefix as needed.
	Prefix string

	// The principal name of the table.
	Name string
}

TableName holds a two-part name. The prefix part is optional.

func (TableName) PrefixWithoutDot 

func (tn TableName) PrefixWithoutDot() string

PrefixWithoutDot return the prefix; if this ends with a dot, the dot is removed.

func (TableName) String 

func (tn TableName) String() string

String gets the full table name.

type TableWithCrud 

type TableWithCrud interface {
	Table

	// QueryOneNullString is a low-level access method for one string. This can be used for function queries and
	// such like. If the query selected many rows, only the first is returned; the rest are discarded.
	// If not found, the result will be invalid.
	QueryOneNullString(req require.Requirement, query string, args ...interface{}) (result sql.NullString, err error)

	// QueryOneNullInt64 is a low-level access method for one int64. This can be used for 'COUNT(1)' queries and
	// such like. If the query selected many rows, only the first is returned; the rest are discarded.
	// If not found, the result will be invalid.
	QueryOneNullInt64(req require.Requirement, query string, args ...interface{}) (result sql.NullInt64, err error)

	// QueryOneNullFloat64 is a low-level access method for one float64. This can be used for 'AVG(...)' queries and
	// such like. If the query selected many rows, only the first is returned; the rest are discarded.
	// If not found, the result will be invalid.
	QueryOneNullFloat64(req require.Requirement, query string, args ...interface{}) (result sql.NullFloat64, err error)

	// Exec executes a query.
	//
	// It places a requirement, which may be nil, on the number of affected rows: this
	// controls whether an error is generated when this expectation is not met.
	//
	// It returns the number of rows affected (if the DB supports that).
	Exec(req require.Requirement, query string, args ...interface{}) (int64, error)

	// CountWhere counts records that match a 'where' predicate.
	CountWhere(where string, args ...interface{}) (count int64, err error)

	// Count counts records that match a 'where' predicate.
	Count(where where.Expression) (count int64, err error)

	// UpdateFields writes new values to the specified columns for rows that match the 'where' predicate.
	// It returns the number of rows affected (if the DB supports that).
	UpdateFields(req require.Requirement, where where.Expression, fields ...sql.NamedArg) (int64, error)

	// Delete deletes rows that match the 'where' predicate.
	// It returns the number of rows affected (if the DB supports that).
	Delete(req require.Requirement, wh where.Expression) (int64, error)
}

type TableWithIndexes 

type TableWithIndexes interface {
	TableCreator

	// CreateIndexes creates the indexes for the database table.
	CreateIndexes(ifNotExist bool) (err error)

	// DropIndexes executes a query that drops all the indexes on the database table.
	DropIndexes(ifExist bool) (err error)

	// CreateTableWithIndexes creates the database table and its indexes.
	CreateTableWithIndexes(ifNotExist bool) (err error)
}

type TxStarter 

type TxStarter interface {
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
}

TxStarter is able to begin transactions. See database/sql/DB.

Source Files

View all Source files

Directories

Path Synopsis
Package constraint provides types and methods to support foreign-key relationshipd between database tables.
 Package constraint provides types and methods to support foreign-key relationshipd between database tables.
Package require provides simple constraints to assist with detecting errors in database queries that arise from the wrong number of result (for example no result or too many results).
 Package require provides simple constraints to assist with detecting errors in database queries that arise from the wrong number of result (for example no result or too many results).
parse
Updated automatically
 Updated automatically
Package vanilla provides a re-usable table API.
 Package vanilla provides a re-usable table API.

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.