sql

package
v0.1.5 Latest Latest
Warning

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

 Go to latest Published: Nov 23, 2023 License: Apache-2.0 Imports: 16 Imported by: 2

Documentation

Overview

package sqlx provides a is a wrap of golang database/sql package to hide logic for different drivers, the three main functions of this package are: 1. generate query from HTTP input 2. execute query against different SQL databases 3. provide helper functions to get meta information from database

Index

Constants

View Source 
const (
	UNKNOWN = iota
	QUESTION
	DOLLAR
)

Bindvar types supported by Rebind, BindMap and BindStruct.

View Source 
const (
	// PG errors
	// https://www.postgresql.org/docs/current/errcodes-appendix.html
	PGIntegrityConstraintViolation = "23"
	PGSyntaxError                  = "42"

	// MySQL errors
	// https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
	MYErrNoDefaultForField = 1364

	// SQLite errors
	// https://www.sqlite.org/rescode.html
	SQLiteConstraintNotNULL    = 1299
	SQLiteConstraintPrimaryKey = 1555
	SQLiteConstraintUnique     = 2067
)
View Source 
const DefaultTimeout = 2 * time.Minute

DefaultTimeout for all database operations

Variables

View Source 
var (

	// TODO: benchmark performance of regexp match VS map access
	// the code below could be simplified by using regexp, but declare it in a
	// map should result in better performance in theory.
	Types = map[string]func() any{
		"TINYINT":     func() any { return new(sql.NullInt64) },
		"SMALLINT":    func() any { return new(sql.NullInt64) },
		"SMALLSERIAL": func() any { return new(sql.NullInt64) },
		"SERIAL":      func() any { return new(sql.NullInt64) },
		"INT":         func() any { return new(sql.NullInt64) },
		"INTEGER":     func() any { return new(sql.NullInt64) },
		"BIGINT":      func() any { return new(sql.NullInt64) },
		"BIGSERIAL":   func() any { return new(sql.NullInt64) },

		"DEC":              func() any { return new(sql.NullFloat64) },
		"DECIMAL":          func() any { return new(sql.NullFloat64) },
		"NUMERIC":          func() any { return new(sql.NullFloat64) },
		"FLOAT":            func() any { return new(sql.NullFloat64) },
		"REAL":             func() any { return new(sql.NullFloat64) },
		"DOUBLE":           func() any { return new(sql.NullFloat64) },
		"DOUBLE PRECISION": func() any { return new(sql.NullFloat64) },

		"BOOL":    func() any { return new(sql.NullBool) },
		"BOOLEAN": func() any { return new(sql.NullBool) },

		"JSON": func() any { return new(sql.NullString) },

		"CHAR":      func() any { return new(sql.NullString) },
		"VARCHAR":   func() any { return new(sql.NullString) },
		"NVARCHAR":  func() any { return new(sql.NullString) },
		"TEXT":      func() any { return new(sql.NullString) },
		"UUID":      func() any { return new(sql.NullString) },
		"ENUM":      func() any { return new(sql.NullString) },
		"BLOB":      func() any { return new(sql.NullString) },
		"BINARY":    func() any { return new(sql.NullString) },
		"XML":       func() any { return new(sql.NullString) },
		"DATE":      func() any { return new(sql.NullString) },
		"DATETIME":  func() any { return new(sql.NullString) },
		"TIMESTAMP": func() any { return new(sql.NullString) },
	}

	TypeConverters = map[string]TypeConverter{
		"TINYINT":     func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SMALLINT":    func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SMALLSERIAL": func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SERIAL":      func(i any) any { return i.(*sql.NullInt64).Int64 },
		"INT":         func(i any) any { return i.(*sql.NullInt64).Int64 },
		"INTEGER":     func(i any) any { return i.(*sql.NullInt64).Int64 },
		"BIGINT":      func(i any) any { return i.(*sql.NullInt64).Int64 },
		"BIGSERIAL":   func(i any) any { return i.(*sql.NullInt64).Int64 },

		"DEC":              func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DECIMAL":          func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"NUMERIC":          func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"FLOAT":            func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"REAL":             func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DOUBLE":           func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DOUBLE PRECISION": func(i any) any { return i.(*sql.NullFloat64).Float64 },

		"BOOL":    func(i any) any { return i.(*sql.NullBool).Bool },
		"BOOLEAN": func(i any) any { return i.(*sql.NullBool).Bool },

		"CHAR":      func(i any) any { return i.(*sql.NullString).String },
		"VARCHAR":   func(i any) any { return i.(*sql.NullString).String },
		"NVARCHAR":  func(i any) any { return i.(*sql.NullString).String },
		"TEXT":      func(i any) any { return i.(*sql.NullString).String },
		"UUID":      func(i any) any { return i.(*sql.NullString).String },
		"ENUM":      func(i any) any { return i.(*sql.NullString).String },
		"BLOB":      func(i any) any { return i.(*sql.NullString).String },
		"BINARY":    func(i any) any { return i.(*sql.NullString).String },
		"XML":       func(i any) any { return i.(*sql.NullString).String },
		"DATE":      func(i any) any { return i.(*sql.NullString).String },
		"DATETIME":  func(i any) any { return i.(*sql.NullString).String },
		"TIMESTAMP": func(i any) any { return i.(*sql.NullString).String },

		"JSON": func(i any) any {
			rawData := i.(*sql.NullString).String
			if s, err := strconv.ParseFloat(rawData, 64); err == nil {
				return s
			}
			if s, err := strconv.ParseBool(rawData); err == nil {
				return s
			}
			return rawData
		},
	}

	Operators = map[string]string{
		"eq":    " = ",
		"ne":    " <> ",
		"gt":    " > ",
		"lt":    " < ",
		"gte":   " >= ",
		"lte":   " <= ",
		"like":  " like ",
		"ilike": " ilike ",
		"is":    " is ",
		"in":    " in ",
		"cs":    " @> ",
		"cd":    " <@ ",
	}

	ReservedWords = map[string]struct{}{
		"select": {},
		"order":  {},
		"count":  {},
	}
)

Functions

func Rebind 

func Rebind(driverName, query string) string

Rebind a query from the default driverName(mysql) to the target driverName.

Types

type Column 

type Column struct {
	ColumnName string `json:"column_name"`
	DataType   string `json:"data_type"`
	NotNull    bool   `json:"notnull"`
	Pk         bool   `json:"pk"`
}

Column represents a table column with name and type

func (*Column) String 

func (c *Column) String() string

type DB 

type DB struct {
	*stdSQL.DB
	DriverName string
}

DB is a wrapper of the golang database/sql DB struct with a DriverName to handle generic logic against different SQL database

func Open 

func Open(url string) (*DB, error)

Open connects to database by specify database url and ping it

func (*DB) Close added in v0.1.1 

func (db *DB) Close()

func (*DB) ExecQuery 

func (db *DB) ExecQuery(ctx context.Context, query string, args ...any) (int64, error)

ExecQuery execute and query in database and return rows affected or an error

func (*DB) FetchData 

func (db *DB) FetchData(ctx context.Context, query string, args ...any) ([]map[string]any, error)

FetchData execute query and fetch data from database, it always return an array or error

func (*DB) FetchOne 

func (db *DB) FetchOne(ctx context.Context, query string, args ...any) (map[string]any, error)

FetchOne execute query and fetch data from database, it returns one row or error

func (*DB) FetchTables 

func (db *DB) FetchTables() map[string]*Table

FetchTables return all the tables in current database along with all the columns name and datatype

type DriverType 

type DriverType int
const (
	Postgres DriverType = iota
	MySQL
	SQLite
)

type Error 

type Error struct {
	Code int
	Msg  string
}

Error converts database error to http code

func NewError 

func NewError(code int, msg string) Error

func (Error) Error 

func (e Error) Error() string

type Helper 

type Helper interface {
	GetTablesSQL() string
	GetColumnsSQL(string) string
}

type MyHelper 

type MyHelper struct{}

func (MyHelper) GetColumnsSQL 

func (h MyHelper) GetColumnsSQL(tableName string) string

func (MyHelper) GetTablesSQL 

func (h MyHelper) GetTablesSQL() string

type PGHelper 

type PGHelper struct{}

func (PGHelper) GetColumnsSQL 

func (h PGHelper) GetColumnsSQL(tableName string) string

func (PGHelper) GetTablesSQL 

func (h PGHelper) GetTablesSQL() string

type PostData 

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

func (*PostData) Set 

func (pd *PostData) Set(column string, val any)

Set sets custom column and val to each objects

func (*PostData) SetQuery 

func (pd *PostData) SetQuery(index uint) (*SetQuery, error)

SetQuery return set sql for update TODO: bulk update

func (*PostData) UnmarshalJSON 

func (pd *PostData) UnmarshalJSON(b []byte) error

UnmarshalJSON implements json.Unmarshaler

func (*PostData) ValuesQuery 

func (pd *PostData) ValuesQuery() (*ValuesQuery, error)

valuesQuery convert post data to values query for insertion

type SQLiteHelper 

type SQLiteHelper struct{}

func (SQLiteHelper) GetColumnsSQL 

func (h SQLiteHelper) GetColumnsSQL(tableName string) string

func (SQLiteHelper) GetTablesSQL 

func (h SQLiteHelper) GetTablesSQL() string

type SetQuery 

type SetQuery struct {
	Index uint // index for next field, args number plus 1
	Query string
	Args  []any
}

e.g. UPDATE table SET a="a",b="b" index=3 sql="a=$1, b=$2" args=["a", "b"]

type Table 

type Table struct {
	Name       string
	PrimaryKey string
	Columns    []*Column
}

Table represents a table in database with name and columns

func (*Table) String 

func (t *Table) String() string

type TypeConverter 

type TypeConverter func(any) any

type URLQuery 

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

func NewURLQuery 

func NewURLQuery(values url.Values, driver string) *URLQuery

func (*URLQuery) IsCount 

func (q *URLQuery) IsCount() bool

func (*URLQuery) IsDebug 

func (q *URLQuery) IsDebug() bool

func (*URLQuery) IsMine 

func (q *URLQuery) IsMine() bool

func (*URLQuery) IsSingular 

func (q *URLQuery) IsSingular() bool

func (*URLQuery) OrderQuery 

func (q *URLQuery) OrderQuery() string

OrderQuery returns sql order query string

func (*URLQuery) Page 

func (q *URLQuery) Page() (page, pageSize int)

func (*URLQuery) SelectQuery 

func (q *URLQuery) SelectQuery() (string, error)

SelectQuery return sql projection string

func (*URLQuery) Set 

func (q *URLQuery) Set(key, value string)

func (*URLQuery) WhereQuery 

func (q *URLQuery) WhereQuery(index uint) (newIndex uint, query string, args []any)

WhereQuery returns sql and args for where clause

type ValuesQuery 

type ValuesQuery struct {
	Index        uint // index for next field, args number plus 1
	Columns      []string
	Placeholders []string
	Args         []any
}

e.g. INSERT INTO a (c1, c2) VALUES (v1,v2),(v3,v4) index=4 columns=["c1", "c2"] vals=["$1,$2", "$3,$4"] args=[v1,v2,v3,v4]

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.