sqlf

package module
v0.0.0-...-e3ce0f0 Latest Latest
Warning

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

 Go to latest Published: Jul 19, 2016 License: MIT Imports: 10 Imported by: 0

README

sqlf: Formatting SQL statements

Package sqlf provides assistance in creating SQL statements.

GoDoc License GoReportCard

NOTE: This package is an earlier version of the sqlstmt package. It is not being maintained and will be deleted at some point.

Overview

Package sqlf attempts to make the generation of SQL statements easier by using a Printf-style API that will build SQL statements based on the contents of Go language structures.

Documentation

Overview

Package sqlf assists writing SQL statements. It is intended for programmers who are comfortable with writing SQL, but would like assistance with the tedious process of preparing SELECT, INSERT and UPDATE statements for tables that have a large number of columns.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func ToDBName 

func ToDBName(name string) string

ToDBName convert string to db name

Types

type ColumnList 

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

ColumnList represents a list of columns associated with a table for use in a specific SQL clause.

Each ColumnList represents a subset of the columns in the table. For example a column list for the WHERE clause in a row update statement will only contain the columns for the primary key. However any ColumnList can return a different subset of the columns in the table. For example calling the All method on any ColumnList will return a ColumnList with all of the columns in the table.

func (ColumnList) All 

func (cil ColumnList) All() ColumnList

All returns a column list of all of the columns in the associated table.

func (ColumnList) Exclude 

func (cil ColumnList) Exclude(names ...string) ColumnList

Exclude returns a column list that excludes the nominated columns. This method can be appended to another method. For example: 

table.Update.Columns.Updateable().Except("Name", "Age")

will specify all updateable columns (ie non-primary key and non-auto-increment) except for the columns corresponding to the "Name" and "Age" fields.

When specifying columns, use the name of field in the Go struct, not the column name in the database table.

func (ColumnList) Include 

func (cil ColumnList) Include(names ...string) ColumnList

Include returns a column list of all columns corresponding to the list of names. When specifying columns, use the name of field in the Go struct, not the column name in the database table.

func (ColumnList) Insertable 

func (cil ColumnList) Insertable() ColumnList

Insertable returns a column list of all columns in the associated table that can be inserted. This list includes all columns except an auto-increment column, if the table has one.

func (ColumnList) PrimaryKey 

func (cil ColumnList) PrimaryKey() ColumnList

PrimaryKey returns a column list containing all primary key columns in the associated table.

func (ColumnList) String 

func (cil ColumnList) String() string

String prints the columns in the list in the appropriate form for the part of the SQL statement that this column list applies to. Because ColumnList implements the fmt.Stringer interface, it can be formatted using "%s" in fmt.Sprintf.

func (ColumnList) Updateable 

func (cil ColumnList) Updateable() ColumnList

Updateable returns a column list of all columns that can be updated in the associated table. This list excludes any primary key columns and any auto-increment column.

type DeleteInfo 

type DeleteInfo struct {
	TableName    TableName
	WhereColumns ColumnList
}

DeleteInfo contains information about a table that can be formatted for a DELETE statement.

func (DeleteInfo) Placeholder 

func (di DeleteInfo) Placeholder() *Placeholder

Placeholder returns a placeholder for the DELETE statement.

type Dialect 

type Dialect interface {
	// Quote a table name or column name so that it does
	// not clash with any reserved words. The SQL-99 standard
	// specifies double quotes (eg "table_name"), but many
	// dialects, including MySQL use the backtick (eg `table_name`).
	// SQL server uses square brackets (eg [table_name]).
	Quote(name string) string

	// Return the placeholder for binding a variable value.
	// Most SQL dialects support a single question mark (?), but
	// PostgreSQL uses numbered placeholders (eg $1).
	Placeholder(n int) string
}

Dialect is an interface used to handle differences in SQL dialects. 

var (
	DefaultDialect Dialect // Default dialect
	DialectMySQL   Dialect // MySQL dialect
	DialectMSSQL   Dialect // Microsoft SQL Server dialect
	DialectPG      Dialect // PostgreSQL
	DialectSQLite  Dialect
)

SQL Dialects. The DefaultDialect value can be set and will be assumed for all subsequent tables. If not set explicitly, then the default dialect is obtained by looking at the first driver in the list of SQL drivers. For programs where only one database driver is loaded, this is a pretty good guess. If multiple drivers are loaded, then the program should set DefaultDialect explicitly.

type ExecCommand 

type ExecCommand interface {
	// Command returns the SQL statement with placeholders for arguments..
	Command() string

	// Exec executes the SQL statement with the arguments given.
	Exec(db sqlx.Execer, args ...interface{}) (sql.Result, error)
}

ExecCommand contains all the information required to perform an operation against the database that does not return rows. (For example, insert, update or delete). Commands accept an arbitrary number of arguments, each of which should be a scalar value.

func Execf 

func Execf(format string, args ...interface{}) ExecCommand

Execf formats an SQL command that does not return any rows.

type InsertInfo 

type InsertInfo struct {
	// Table name for use in INSERT INTO clause.
	TableName TableName

	// Columns that should appear in the parentheses
	// after  INSERT INTO table(...). By default these
	// are all columns except for any auto-increment columns.
	Columns ColumnList

	// Placeholders that match the Columns list.
	Values ColumnList
}

InsertInfo contains information about a table that can be formatted in an INSERT statement.

func (InsertInfo) Placeholder 

func (ii InsertInfo) Placeholder() *Placeholder

Placeholder returns a placeholder for the INSERT statement.

type InsertRowCommand 

type InsertRowCommand interface {
	// Command returns the SQL insert statement with placeholders for arguments..
	Command() string

	// Args returns an array of arguments for the SQL command
	// based on the contents of the row.
	Args(row interface{}) ([]interface{}, error)

	// Exec executes the SQL insert statement with the arguments
	// appropriate for the contents of the row. If the row has
	// an auto-increment column, it will be populated with the value
	// generated by the database server.
	Exec(db sqlx.Execer, row interface{}) error
}

InsertRowCommand contains all the information required to insert a single row into a database table based on the contents of a Go struct..

func InsertRowf 

func InsertRowf(format string, args ...interface{}) InsertRowCommand

InsertRowf builds up a command for inserting a single row in the database using a familiar "printf" style syntax.

TODO: need an example

type Placeholder 

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

Placeholder represents a placeholder in an SQL query that represents a variable that will be bound to the query at execution time. Different SQL dialects have varied formats for placeholders, but most will accept a single question mark ("?"). PostgreSQL is a notable exception as it requires a numberd placeholde (eg "$1").

func (*Placeholder) String 

func (p *Placeholder) String() string

type QueryCommand 

type QueryCommand interface {
	// Command returns the SQL select statement with placeholders for arguments..
	Command() string

	// Query executes the query with the arguments given.
	Query(db sqlx.Queryer, args ...interface{}) (*sqlx.Rows, error)

	// QueryRow executes the query, which is expected to return at most one row.
	// QueryRow always returns a non-nil value. Errors are deferred until the Scan
	// method is called on the Row.
	QueryRow(db sqlx.Queryer, args ...interface{}) *sqlx.Row

	// Select executes a query using the provided Queryer, and StructScans each
	// row into dest, which must be a slice. If the slice elements are scannable,
	// then the result set must have only one column. Otherwise StructScan is
	// used. The *sql.Rows are closed automatically.
	Select(db sqlx.Queryer, dest interface{}, args ...interface{}) error
}

QueryCommand contains all the information required to perform an operation against the database that returns rows. (ie select statements). Commands accept an arbitrary number of arguments, each of which should be a scalar value.

func Queryf 

func Queryf(format string, args ...interface{}) QueryCommand

Queryf builds a command to query one or more rows from the database using a familiar "printf"-style syntax.

TODO: example needed.

type SelectInfo 

type SelectInfo struct {
	TableName TableName
	Columns   ColumnList
	OrderBy   ColumnList
}

SelectInfo contains information about a table that can be formatted for a SELECT statement or a select clause in an INSERT statement.

func (SelectInfo) Placeholder 

func (si SelectInfo) Placeholder() *Placeholder

Placeholder returns a placeholder for the SELECT statement.

type Settings 

type Settings struct {
	Dialect        Dialect
	ColumnNameFunc func(name string) string
}
var Default Settings

Default contains the default settings, which can be overriden. The default settings choose the SQL dialect based on the database driver loaded. If the program uses more than one database driver, this will not work.

func (Settings) Merge 

func (s Settings) Merge(settings Settings) Settings

Merge returns a new settings object which is a copy of s, but with non-nil values from settings merged in.

func (Settings) Table 

func (settings Settings) Table(name string, row interface{}) *TableInfo

Table creates a TableInfo with the specified table name and schema as defined by the struct that is pointed to by row.

This function wil panic if row is not a struct or a pointer to a struct. The contents of row are ignored, only the structure fields and field tags are used.

type TableInfo 

type TableInfo struct {
	Name   string
	Select SelectInfo
	Insert InsertInfo
	Update UpdateInfo
	Delete DeleteInfo
	// contains filtered or unexported fields
}

TableInfo contains enough information about a database table to assist with generating SQL strings.

func Table 

func Table(name string, row interface{}) *TableInfo

Table creates a TableInfo with the specified table name and schema as defined by the struct that is pointed to by row. The dialect and column name mapping functions are defined in the default settings.

This function wil panic if row is not a struct or a pointer to a struct. The contents of row are ignored, only the structure fields and field tags are used.

func (*TableInfo) Dialect 

func (ti *TableInfo) Dialect() Dialect

Dialect returns the SQL dialect to use with this table.

func (*TableInfo) WithAlias 

func (ti *TableInfo) WithAlias(alias string) *TableInfo

WithAlias creates a clone of the table with the specified alias. Any SQL statements produced with this table will include the alias name for all references of the table. Note that alias should be a valid SQL identier, as it is not quoted in any SQL statements produced.

func (*TableInfo) WithDialect 

func (ti *TableInfo) WithDialect(dialect Dialect) *TableInfo

type TableName 

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

TableName represents the name of a table for formatting in an SQL statement. The format will depend on where the table appears in the SQL statement. For example, in a SELECT FROM clause, the table may include an alias, but in an INSERT INTO statement the table will not have an alias. (TODO: INSERT x INTO x ... FROM x, y, etc)

func (TableName) String 

func (tn TableName) String() string

String prints the table name in the appropriate form for the part of the SQL statement that this TableName applies to. Because TableName implements the Stringer interface, it can be formatted using "%s" in fmt.Sprintf.

type UpdateInfo 

type UpdateInfo struct {
	TableName    TableName
	SetColumns   ColumnList
	WhereColumns ColumnList
}

UpdateInfo contains information about a table that can be formatted for an UPDATE statement.

func (UpdateInfo) Placeholder 

func (ui UpdateInfo) Placeholder() *Placeholder

Placeholder returns a placeholder for the UPDATE statement.

type UpdateRowCommand 

type UpdateRowCommand interface {
	// Command returns the SQL update/delete statement with placeholders for arguments..
	Command() string

	// Args returns an array of arguments for the SQL update/delete command
	// based on the contents of the row.
	Args(row interface{}) ([]interface{}, error)

	// Exec executes the SQL update/delete statement with the arguments
	// appropriate for the contents of the row. Returns the number
	// of rows updated, which should be zero or one. The contents of the
	// row struct are unchanged.
	Exec(db sqlx.Execer, row interface{}) (rowCount int, err error)
}

UpdateRowCommand contains all the information required to update or delete a single row in a database table based on the contents of a associated Go struct.

func UpdateRowf 

func UpdateRowf(format string, args ...interface{}) UpdateRowCommand

UpdateRowf builds a command to update a single row in the database using a familiar "printf"-style syntax.

TODO: example needed.

Source Files

View all Source files

Directories

Path Synopsis
Package scan implements a simple lexical scanner for SQL statements.
 Package scan implements a simple lexical scanner for SQL statements.

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.