sql

package
v0.31.10 Latest Latest
Warning

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

 Go to latest Published: Feb 12, 2024 License: MIT Imports: 18 Imported by: 0

Documentation

Overview

Package sql contains helper functions that connect a standard Go database/sql object to the GoRADD system.

Most of the functionality in this package is used by database implementations. GoRADD users would not normally directly call functions in this package.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Associate added in v0.24.1 

func Associate(ctx context.Context,
	db DbI,
	table string,
	column string,
	pk interface{},
	relatedColumn string,
	relatedPks interface{})

Associate is a helper function for the sql database implementations. It sets up the many-many association pointing from the given table and column to another table and column. table is the name of the association table. column is the name of the column in the association table that contains the pk for the record we are associating. pk is the value of the primary key. relatedTable is the table the association is pointing to. relatedColumn is the column in the association table that points to the relatedTable's pk. relatedPks are the new primary keys in the relatedTable we are associating.

func ExtractOptions 

func ExtractOptions(comment string) (options map[string]interface{}, remainingComment string, err error)

ExtractOptions finds the json encoded list of options in the given string

func GenerateInsert added in v0.24.1 

func GenerateInsert(db DbI, table string, fields map[string]any) (sql string, args []any)

GenerateInsert is a helper function for database implementations to generate an insert statement.

func GenerateUpdate added in v0.24.1 

func GenerateUpdate(db DbI, table string, fields map[string]any, pkName string, pkValue any) (sql string, args []any)

GenerateUpdate is a helper function for database implementations to generate an update statement.

func GetDataDefLength 

func GetDataDefLength(description string) int

GetDataDefLength will extract the length from the definition given a data definition description of the table. If more than one number, returns the first number Example: 

bigint(21) -> 21

varchar(50) -> 50 decimal(10,2) -> 10

func NewSqlCursor 

func NewSqlCursor(rows *sql.Rows,
	columnTypes []query.GoColumnType,
	columnNames []string,
	builder *Builder,
) *sqlCursor

func SqlReceiveRows 

func SqlReceiveRows(rows *sql.Rows,
	columnTypes []query.GoColumnType,
	columnNames []string,
	builder *Builder,
) []map[string]interface{}

SqlReceiveRows gets data from a sql result set and returns it as a slice of maps.

Each column is mapped to its column name. If you provide columnNames, those will be used in the map. Otherwise it will get the column names out of the result set provided.

Types

type Builder 

type Builder struct {
	db2.QueryBuilder

	IsCount           bool
	IsDelete          bool
	RootDbTable       string                  // The database name for the table that is the root of the query
	RootJoinTreeItem  *JoinTreeItem           // The top of the join tree
	SubPrefix         string                  // The prefix for sub items. If this is a sub query, this gets updated
	SubqueryCounter   int                     // Helper to make unique prefixes for subqueries
	ColumnAliases     *JoinTreeItemSliceMap   // StdMap to go from an alias to a JoinTreeItem for columns, which can also get us to a node
	ColumnAliasNumber int                     // Helper to make unique generated aliases
	TableAliases      *JoinTreeItemSliceMap   // StdMap to go from an alias to a JoinTreeItem for tables
	NodeMap           map[NodeI]*JoinTreeItem // A map that gets us to a JoinTreeItem from a node.
	RowId             int                     // Counter for creating fake ids when doing distinct or orderby selects
	ParentBuilder     *Builder                // The parent builder of a subquery
	// contains filtered or unexported fields
}

A Builder is a helper object to organize a Query object eventually into a SQL string. It builds the join tree and creates the aliases that will eventually be used to generate the sql and then unpack it into fields and objects. It implements the QueryBuilderI interface. It is used both as the overriding controller of a query, and the controller of a subquery, so its recursive. The approach is to gather up the parameters of the query first, build the nodes into a node tree without changing the nodes themselves, build the query, execute the query, and finally return the results.

func NewSqlBuilder 

func NewSqlBuilder(ctx context.Context, db DbI) *Builder

NewSqlBuilder creates a new Builder object.

func (*Builder) Count 

func (b *Builder) Count(distinct bool, nodes ...NodeI) uint

Count creates a query that selects one thing, a count. If distinct is specified, only distinct items will be selected. If no columns are specified, the count will include NULL items. Otherwise, it will not include NULL results in the count. You cannot include any other select items in a count. If you want to do that, you should do a normal query and add a COUNT operation node.

func (*Builder) Delete 

func (b *Builder) Delete()

func (*Builder) GetAliasedNode added in v0.25.0 

func (b *Builder) GetAliasedNode(node AliasNodeI) NodeI

func (*Builder) GetItemFromNode 

func (b *Builder) GetItemFromNode(node NodeI) *JoinTreeItem

func (*Builder) Load 

func (b *Builder) Load() (result []map[string]interface{})

Load terminates the builder, queries the database, and returns the results as an array of interfaces similar in structure to a json structure

func (*Builder) LoadCursor 

func (b *Builder) LoadCursor() CursorI

LoadCursor terminates the builder, queries the database, and returns a cursor that can be used to step through the results.

LoadCursor is helpful when loading a large set of data that you want to output in chunks. You cannot use this with Joins that create multiple connections to other objects, like reverse FKs or Multi-multi relationships

func (*Builder) Subquery 

func (b *Builder) Subquery() *SubqueryNode

Subquery adds a subquery node, which is like a mini query builder that should result in a single value.

type DbHelper 

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

DbHelper is a mixin for SQL database drivers. It implements common code needed by all SQL database drivers.

func NewSqlDb 

func NewSqlDb(dbKey string, db *sql.DB) DbHelper

NewSqlDb creates a default DbHelper mixin.

func (*DbHelper) Begin 

func (s *DbHelper) Begin(ctx context.Context) (txid db.TransactionID)

Begin starts a transaction. You should immediately defer a Rollback using the returned transaction id. If you Commit before the Rollback happens, no Rollback will occur. The Begin-Commit-Rollback pattern is nestable.

func (*DbHelper) Commit 

func (s *DbHelper) Commit(ctx context.Context, txid db.TransactionID)

Commit commits the transaction, and if an error occurs, will panic with the error.

func (*DbHelper) DbKey 

func (s *DbHelper) DbKey() string

DbKey returns the key of the database in the global database store.

func (*DbHelper) Exec 

func (s *DbHelper) Exec(ctx context.Context, sql string, args ...interface{}) (r sql.Result, err error)

Exec executes the given SQL code, without returning any result rows.

func (*DbHelper) GetProfiles 

func (s *DbHelper) GetProfiles(ctx context.Context) []ProfileEntry

GetProfiles returns currently collected profile information TODO: Move profiles to a session variable so we can access ajax queries too

func (*DbHelper) PutBlankContext 

func (s *DbHelper) PutBlankContext(ctx context.Context) context.Context

PutBlankContext puts a blank context into the context chain to track transactions and other special database situations.

func (*DbHelper) Query 

func (s *DbHelper) Query(ctx context.Context, sql string, args ...interface{}) (r *sql.Rows, err error)

Query executes the given sql, and returns a row result set.

func (*DbHelper) Rollback 

func (s *DbHelper) Rollback(ctx context.Context, txid db.TransactionID)

Rollback will rollback the transaction if the transaction is still pointing to the given txid. This gives the effect that if you call Rollback on a transaction that has already been committed, no Rollback will happen. This makes it easier to implement a transaction management scheme, because you simply always defer a Rollback after a Begin. Pass the txid that you got from the Begin to the Rollback. To trigger a Rollback, simply panic.

func (*DbHelper) SqlDb 

func (s *DbHelper) SqlDb() *sql.DB

SqlDb returns the underlying database/sql database object.

func (*DbHelper) StartProfiling 

func (s *DbHelper) StartProfiling()

StartProfiling will start the database profiling process.

type DbI 

type DbI interface {
	// Exec executes a query that does not expect to return values
	Exec(ctx context.Context, sql string, args ...interface{}) (r sql.Result, err error)
	// Query executes a query that returns values
	Query(ctx context.Context, sql string, args ...interface{}) (r *sql.Rows, err error)
	// QuoteIdentifier will put quotes around an identifier
	QuoteIdentifier(string) string
	// FormatArgument will return the placeholder string for the n'th argument
	// in a sql string. Some sqls just use "?" while others identify an argument
	// by location, like Postgres's $1, $2, etc.
	FormatArgument(n int) string
}

The DbI interface describes the interface that a sql database needs to implement so that it will work with the Builder object. If you know a DatabaseI object is standard Go database/sql database, you can cast it to this type to gain access to the low level SQL driver and send it raw SQL commands.

type JoinTreeItem 

type JoinTreeItem struct {
	Node            query.NodeI
	Parent          *JoinTreeItem
	ChildReferences []*JoinTreeItem // TableNodeI objects
	Leafs           []*JoinTreeItem
	JoinCondition   query.NodeI
	Alias           string
	Expanded        bool
	IsPK            bool
}

JoinTreeItem is used to build the join tree. The join tree creates a hierarchy of joined nodes that let us generate aliases, serialize the query, and afterwards unpack the results.

type JoinTreeItemSliceMap 

type JoinTreeItemSliceMap = maps.SliceMap[string, *JoinTreeItem]

type ProfileEntry 

type ProfileEntry struct {
	DbKey     string
	BeginTime time.Time
	EndTime   time.Time
	Typ       string
	Sql       string
}

ProfileEntry contains the data collected during sql profiling

type SqlReceiver 

type SqlReceiver struct {
	R interface{}
}

SqlReceiver is an encapsulation of a way of receiving data from sql queries as interface{} pointers. This allows you to get data without knowing the type of data you are asking for ahead of time, and is easier for dealing with NULL fields. Some database drivers (MySql for one) return different results in fields depending on how you call the query (using a prepared statement can return different results than without one), or if the data does not quite fit (UInt64 in particular will return a string if the returned value is bigger than MaxInt64, but smaller than MaxUint64.)

Pass the address of the R member to the sql.Scan method when using an object of this type, because there are some idiosyncrasies with how Go treats return values that prevents returning an address of R from a function

func (SqlReceiver) BoolI 

func (r SqlReceiver) BoolI() interface{}

BoolI returns the value as an interface to a boolean

func (SqlReceiver) DoubleI 

func (r SqlReceiver) DoubleI() interface{}

DoubleI returns the value as a float64 interface

func (SqlReceiver) FloatI 

func (r SqlReceiver) FloatI() interface{}

FloatI returns the value as an interface to a float32 value.

func (SqlReceiver) Int64I 

func (r SqlReceiver) Int64I() interface{}

Int64I returns the given value as an interface to an Int64

func (SqlReceiver) IntI 

func (r SqlReceiver) IntI() interface{}

IntI returns the receiver as an interface to an int.

func (SqlReceiver) StringI 

func (r SqlReceiver) StringI() interface{}

StringI returns the value as an interface to a string

func (SqlReceiver) TimeI 

func (r SqlReceiver) TimeI() interface{}

TimeI returns the value as a time.Time value in UTC, or in the case of CURRENT_TIME, a string "now".

func (SqlReceiver) Uint64I 

func (r SqlReceiver) Uint64I() interface{}

Uint64I returns a value as an interface to a UInt64.

Some drivers (like MySQL) return all integers as Int64. This converts to uint64. Its up to you to make sure you only use this on 64-bit uints or smaller.

func (SqlReceiver) UintI 

func (r SqlReceiver) UintI() interface{}

UintI converts the value to an interface to a GO uint.

Some drivers (like MySQL) return all integers as Int64. Its up to you to make sure you only use this on 32-bit uints or smaller.

func (SqlReceiver) Unpack 

func (r SqlReceiver) Unpack(typ GoColumnType) interface{}

Unpack converts a SqlReceiver to a type corresponding to the given GoColumnType

func (SqlReceiver) UnpackDefaultValue added in v0.20.0 

func (r SqlReceiver) UnpackDefaultValue(typ GoColumnType) interface{}

UnpackDefaultValue converts a SqlReceiver used to get the default value to a type corresponding to the given GoColumnType.

Source Files

View all Source files

Directories

Path Synopsis

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.