sqlBits

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

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

 Go to latest Published: Jun 25, 2021 License: Apache-2.0 Imports: 6 Imported by: 0

Documentation

Index

Constants

View Source 
const FIELD_LIST_HINT_END string = `/* /FIELDLIST */`

FIELD_LIST_HINT_END Sometimes we have a nested query in field list. So in order for sql.Builder::getQueryTotals() to work automatically, we need to supply a comment hint to end the field list.

View Source 
const FIELD_LIST_HINT_START string = `/* FIELDLIST */`

FIELD_LIST_HINT_START Sometimes we have a nested query in field list. So in order for sql.Builder::getQueryTotals() to work automatically, we need to supply a comment hint to start the field list.

View Source 
const OPERATOR_NOT_EQUAL string = "<>"

OPERATOR_NOT_EQUAL Standard SQL specifies '<>' as NOT EQUAL.

View Source 
const ORDER_BY_ASCENDING string = "ASC"

ORDER_BY_ASCENDING The SQL element meaning ascending order when sorting.

View Source 
const ORDER_BY_DESCENDING string = "DESC"

ORDER_BY_DESCENDING The SQL element meaning descending order when sorting.

View Source 
const SQLSTATE_NO_DATA string = "02000"

SQLSTATE_NO_DATA 5 digit code meaning "no data"; e.g. UPDATE/DELETE failed due to record(s) defined by WHERE clause returned no rows at all.

View Source 
const SQLSTATE_SUCCESS string = "00000"

SQLSTATE_SUCCESS 5 digit code meaning "successful completion/no error".

View Source 
const SQLSTATE_TABLE_DOES_NOT_EXIST string = "42S02"

SQLSTATE_TABLE_DOES_NOT_EXIST 5 digit ANSI SQL code meaning a table referenced in the SQL does not exist.

Variables

View Source 
var DefaultFieldNameStrConvFunc = strings.ToLower

DefaultFieldNameStrConvFunc String-conversion func for struct field name to query field name

View Source 
var DriverMeta map[reflect.Type]*DriverInfo
View Source 
var FieldNameTag = ""

FieldNameTag Custom tag to use for DetermineFieldsFromTableStruct

View Source 
var TotalRowCount = RowCountAggregate{
	// contains filtered or unexported fields
}

Functions

func DetermineFieldsFromTableStruct 

func DetermineFieldsFromTableStruct(aTableStruct interface{}) []string

DetermineFieldsFromTableStruct Returns the array of publicly defined fields available.

func GetSanitizedFieldList 

func GetSanitizedFieldList(aTableStruct interface{}, aFieldList []string) []string

GetSanitizedFieldList Prune the field list to remove any invalid fields.

func IsFieldSortable 

func IsFieldSortable(aTableStruct interface{}, aFieldName string) bool

IsFieldSortable Returns TRUE if the fieldname specified is sortable. Set public field tag to `sortable:"false"` if its not sortable.

func IsStructFieldExported 

func IsStructFieldExported(f reflect.StructField) bool

IsStructFieldExported IsExported reports whether the struct field is exported.

func RegisterDriverInfo 

func RegisterDriverInfo(driverName string, dbDriver interface{})

Types

type Aggregate 

type Aggregate map[string]string

Aggregate field names as keys mapped to values on SQL used to calc it.

func (Aggregate) GetAggregateDefinition 

func (a Aggregate) GetAggregateDefinition() Aggregate

type Aggregater 

type Aggregater interface {
	GetAggregateDefinition() Aggregate
}

Aggregater Obtain an aggregate definition to use.

type Builder 

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

Builder Use this class to help build SQL queries. Supports: MySQL and Postgres.

func NewBuilder 

func NewBuilder(aDbModeler DbModeler) *Builder

NewBuilder Models can use this package to help build their SQL queries.

func (*Builder) Add 

func (sqlbldr *Builder) Add(aStr string) *Builder

Add Adds a string to the SQL prefixed with a space (just in case). *DO NOT* use this method to write values gathered from user input directly into a query. *ALWAYS* use the .AddParam() or similar methods, or pre-sanitize the data value before writing it into the query.

func (*Builder) AddFieldList 

func (sqlbldr *Builder) AddFieldList(aFieldList *[]string) *Builder

AddFieldList Adds the list of fields (columns) to the SQL string.

func (*Builder) AddParamForColumnIfDefined 

func (sqlbldr *Builder) AddParamForColumnIfDefined(aParamKey string, aColumnName string) *Builder

AddParamForColumnIfDefined Parameter only gets added to the SQL string if data IS NOT NULL.

func (*Builder) AddParamIfDefined 

func (sqlbldr *Builder) AddParamIfDefined(aParamKey string) *Builder

AddParamIfDefined Parameter only gets added to the SQL string if data IS NOT NULL.

func (*Builder) AddQueryLimit 

func (sqlbldr *Builder) AddQueryLimit(aLimit int, aOffset int) *Builder

AddQueryLimit Return the SQL "LIMIT" expression for our model's database type.

func (*Builder) AddSubQueryForColumn 

func (sqlbldr *Builder) AddSubQueryForColumn(aSubQuery *Builder, aColumnName string) *Builder

AddSubQueryForColumn Sub-query gets added to the SQL string.

func (*Builder) AppendParam 

func (sqlbldr *Builder) AppendParam(aParamKey string, aParamValue string) *Builder

AppendParam Parameter must go into the SQL string regardless of NULL status of data.

func (*Builder) ApplyFilter 

func (sqlbldr *Builder) ApplyFilter(aFilter *Builder) *Builder

ApplyFilter Apply an externally defined set of WHERE field clauses and param values to our SQL (excludes the "WHERE" keyword).

func (*Builder) ApplyOrderByList 

func (sqlbldr *Builder) ApplyOrderByList(aOrderByList *OrderByList) *Builder

ApplyOrderByList If order by list is defined, then apply the sort order as neccessary.

func (*Builder) ApplySortList 

func (sqlbldr *Builder) ApplySortList(aSortList *OrderByList) *Builder

ApplySortList If sort list is defined and its contents are also contained in the non-empty $aFieldList, then apply the sort order as neccessary. @see ApplyOrderByList() which this method is an alias of.

func (*Builder) BeginTransaction 

func (sqlbldr *Builder) BeginTransaction() *Builder

BeginTransaction If we are not already in a transaction, start one.

func (*Builder) CloneAsAggregate 

func (sqlbldr *Builder) CloneAsAggregate(aSqlAggragates Aggregater) *Builder

CloneAsAggregate Sometimes we want to aggregate the query somehow rather than return data from it.

func (*Builder) CommitTransaction 

func (sqlbldr *Builder) CommitTransaction() *Builder

CommitTransaction If we started a transaction earlier, commit it.

func (*Builder) EndSetClause 

func (sqlbldr *Builder) EndSetClause() *Builder

EndSetClause Resets the SET clause flag.

func (*Builder) EndWhereClause 

func (sqlbldr *Builder) EndWhereClause() *Builder

EndWhereClause Resets the WHERE clause flag.

func (*Builder) GetParam 

func (sqlbldr *Builder) GetParam(aParamKey string) *string

GetParam Gets the current value of a param that has been added.

func (*Builder) GetParamSet 

func (sqlbldr *Builder) GetParamSet(aParamKey string) *[]string

GetParamSet Gets the current value of a param that has been added.

func (*Builder) GetQuoted 

func (sqlbldr *Builder) GetQuoted(aIdentifier string) string

GetQuoted Quoted identifiers are DB vendor specific so providing a helper method to just return a properly quoted string for MySQL vs MSSQL vs Oracle, etc. is handy.

func (*Builder) GetSQLStatement 

func (sqlbldr *Builder) GetSQLStatement() string

GetSQLStatement Return our currently built SQL statement.

func (*Builder) GetUniqueParamKey 

func (sqlbldr *Builder) GetUniqueParamKey(aParamKey string) string

GetUniqueParamKey Some SQL drivers require all query parameters be unique. This poses an issue when multiple datakeys with the same name are needed in the query (especially true for MERGE queries). This method will check for any existing parameters named aParamKey and will return a new name with a number for a suffix to ensure its uniqueness.

func (*Builder) IsParamASet 

func (sqlbldr *Builder) IsParamASet(aParamKey string) bool

IsParamASet Inquire if the data that will be used for a particular param is a set or not.

func (*Builder) MustAddParam 

func (sqlbldr *Builder) MustAddParam(aParamKey string) *Builder

MustAddParam Parameter must go into the SQL string regardless of NULL status of data.

func (*Builder) MustAddParamForColumn 

func (sqlbldr *Builder) MustAddParamForColumn(aParamKey string, aColumnName string) *Builder

MustAddParamForColumn Parameter must go into the SQL string regardless of NULL status of data. This is a "shortcut" designed to combine calls to setParamValue, and addParam.

func (*Builder) ReplaceSelectFieldsWith 

func (sqlbldr *Builder) ReplaceSelectFieldsWith(aSelectFields *[]string) *Builder

ReplaceSelectFieldsWith Replace the currently formed SELECT fields with the param. If you have nested queries, you will need to use the FIELD_LIST_HINT_* consts in the SQL like so: "SELECT /* FIELDLIST */ field1, field2, (SELECT blah) AS field3 /* /FIELDLIST */ FROM"

func (*Builder) Reset 

func (sqlbldr *Builder) Reset() *Builder

Reset Resets the object so it can be resused without creating a new instance.

func (*Builder) RollbackTransaction 

func (sqlbldr *Builder) RollbackTransaction() *Builder

RollbackTransaction If we started a transaction earlier, roll it back.

func (*Builder) SQL 

func (sqlbldr *Builder) SQL() string

SQL Return our currently built SQL statement.

func (*Builder) SQLargs 

func (sqlbldr *Builder) SQLargs() []interface{}

SQLargs Return SQL query arguments IFF the driver does not support named parameters.

func (*Builder) SQLnamedArgs 

func (sqlbldr *Builder) SQLnamedArgs() map[string]interface{}

SQLnamedArgs Return SQL query arguments as named parameters.

func (*Builder) SQLparamSets 

func (sqlbldr *Builder) SQLparamSets() map[string]*[]string

SQLparamSets Return our current SQL param sets in use.

func (*Builder) SQLparams 

func (sqlbldr *Builder) SQLparams() map[string]*string

SQLparams Return our current SQL params in use.

func (*Builder) SetDataSource 

func (sqlbldr *Builder) SetDataSource(aDataSource IDataSource) *Builder

SetDataSource Set our param value source.

func (*Builder) SetNullableParam 

func (sqlbldr *Builder) SetNullableParam(aParamKey string, aParamValue *string) *Builder

SetNullableParam Sets the param value and param type, but does not affect the SQL string.

func (*Builder) SetParam 

func (sqlbldr *Builder) SetParam(aParamKey string, aParamValue string) *Builder

SetParam Sets the param value and param type, but does not affect the SQL string.

func (*Builder) SetParamOperator 

func (sqlbldr *Builder) SetParamOperator(aStr string) *Builder

SetParamOperator Operator string to use in all subsequent calls to addParam methods. "=" is default, " LIKE " is a popular operator as well.

func (*Builder) SetParamPrefix 

func (sqlbldr *Builder) SetParamPrefix(aStr string) *Builder

SetParamPrefix Sets the "glue" string that gets prepended to all subsequent calls to AddParam kinds of methods. Spacing is important here, so add what is needed!

func (*Builder) SetParamSet 

func (sqlbldr *Builder) SetParamSet(aParamKey string, aParamValues *[]string) *Builder

SetParamSet Sets the param value set, but does not affect the SQL string.

func (*Builder) SetParamValueIfEmpty 

func (sqlbldr *Builder) SetParamValueIfEmpty(aParamKey string, aNewValue string) *Builder

SetParamValueIfEmpty Set a value for a param when its data value is empty(). e.g. null|""|0

func (*Builder) SetParamValueIfNull 

func (sqlbldr *Builder) SetParamValueIfNull(aParamKey string, aNewValue string) *Builder

SetParamValueIfNull Set a value for a param when its data value is NULL.

func (*Builder) StartFilter 

func (sqlbldr *Builder) StartFilter() *Builder

StartFilter Some operators require alternate handling during WHERE clauses (e.g. "=" with NULLs). Similar to StartWhereClause(), this method is specific to building a filter that consists entirely of a partial WHERE clause which will get appended to the main SqlBuilder using ApplyFilter().

func (*Builder) StartSetClause 

func (sqlbldr *Builder) StartSetClause() *Builder

StartSetClause Some operators require alternate handling during a SET clause (e.g. "=" with NULLs).

func (*Builder) StartWhereClause 

func (sqlbldr *Builder) StartWhereClause() *Builder

StartWhereClause Some operators require alternate handling during WHERE clauses (e.g. "=" with NULLs). This will setParamPrefix(" WHERE ") which will apply to the next AddParam.

func (*Builder) StartWith 

func (sqlbldr *Builder) StartWith(aSql string) *Builder

StartWith Sets the SQL string to this value to build upon.

func (*Builder) WithModel 

func (sqlbldr *Builder) WithModel(aDbModeler DbModeler) *Builder

WithModel Initializer like NewBuilder. e.g.: new(Builder).WithModel(aDbModeler)

type DbMetatater 

type DbMetatater interface {
	GetDbMeta() *DriverInfo
}

type DbModeler 

type DbModeler interface {
	DbMetatater
	DbTransactioner
}

type DbTransactioner 

type DbTransactioner interface {
	InTransaction() bool
	BeginTransaction()
	CommitTransaction()
	RollbackTransaction()
}

type DriverInfo 

type DriverInfo struct {
	// The database/sql API doesn't provide a way to get the registry name for
	// a driver from the driver type.
	Name DriverName
	Type reflect.Type
	// The rune used around table/field names in case of spaces and keyword clashes.
	// Determined by the database type being used (MySQL vs Oracle, etc.).
	IdentifierDelimiter rune
	// Not all drivers support named parameters; otherwise restricted to "$1" or "?".
	SupportsNamedParams bool
}

func GetDriverMeta 

func GetDriverMeta(dbDriver interface{}) *DriverInfo

func (*DriverInfo) SetDriverName 

func (d *DriverInfo) SetDriverName(driverName string) *DriverInfo

type DriverName 

type DriverName string
const (
	MySQL      DriverName = "MySQL"
	PostgreSQL DriverName = "PostgreSQL"
	SQLite     DriverName = "SQLite3"
)

func SqlDriverToDriverName 

func SqlDriverToDriverName(driver driver.Driver) DriverName

SqlDriverToDriverName THANKS TO rbranson: https://github.com/golang/go/issues/12600#issuecomment-378363201 The database/sql API doesn't provide a way to get the registry name for a driver from the driver type.

type IDataSource 

type IDataSource interface {
	IsKeyDefined(aKey string) bool
	IsKeyValueAList(aKey string) bool
	GetValueForKey(aKey string) *string
	GetValueListForKey(aKey string) *[]string
}

type IPagedResults 

type IPagedResults interface {
	// IsTotalRowCountDesired Pagers and Long processes may desire a total regardless of pager use.
	IsTotalRowCountDesired() bool
	// GetPagerPageSize Get the defined maximum row count for a page in a pager. 0 means no limit.
	GetPagerPageSize() int64
	// GetPagerQueryOffset Get the SQL query offset based on pager page size and page desired.
	GetPagerQueryOffset() int64
}

IPagedResults UI defined values like sort order, pager info, and requested fields desired can be an attack vector (SQL Injection) if not properly sanitized. If your class can protect against such attack vectors, define this interface so that a SqlBuilder instance can query your object for sanitization methods.

type ISqlSanitizer 

type ISqlSanitizer interface {
	// GetDefinedFields Returns the array of defined fields available.
	GetDefinedFields() []string
	// IsFieldSortable Returns TRUE if the fieldname specified is sortable.
	IsFieldSortable(aFieldName string) bool
	// GetDefaultSort Return the default sort definition.
	GetDefaultSort() OrderByList
	// GetSanitizedOrderByList Providing click-able headers in tables to easily sort them
	// by a particular field is a great UI feature. However, in order to prevent SQL injection
	// attacks, we must double-check that a supplied field name to order the query by is
	// something we can sort on; this method makes use of the IsFieldSortable()
	// method to determine if the browser supplied field name is one of our possible
	// headers that can be clicked on for sorting purposes.
	GetSanitizedOrderByList(aList OrderByList) OrderByList
	// GetSanitizedFieldList Prune the field list to remove any invalid fields.
	GetSanitizedFieldList(aFieldList []string) []string
}

ISqlSanitizer UI defined values like sort order, pager info, and requested fields desired can be an attack vector (SQL Injection) if not properly sanitized. If your class can protect against such attack vectors, define this interface so that a SqlBuilder instance can query your object for sanitization methods.

type OrderByList 

type OrderByList map[string]string

OrderByList Keys are field names, values are either ORDER_BY_* consts: 'ASC' or 'DESC'.

func GetSanitizedOrderByList 

func GetSanitizedOrderByList(aTableStruct interface{}, aList OrderByList) OrderByList

GetSanitizedOrderByList Providing click-able headers in tables to easily sort them by a particular field is a great UI feature. However, in order to prevent SQL injection attacks, we must double-check that a supplied field name to order the query by is something we can sort on; this method makes use of the IsFieldSortable() method to determine if the browser supplied field name is one of our possible headers that can be clicked on for sorting purposes.

type ResultsWithRowCounter 

type ResultsWithRowCounter interface {
	// SetTotalRowCount Set the query total regardless of paging or not.
	SetTotalRowCount(aTotalRowCount int64)
}

type RowCountAggregate 

type RowCountAggregate struct {
	Rowcount int64
	// contains filtered or unexported fields
}

RowCountAggregate Simple example used to count total rows returned by a SQL statement.

func (RowCountAggregate) GetAggregateDefinition 

func (a RowCountAggregate) GetAggregateDefinition() Aggregate

Source Files

View all Source files

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.