mysqlx

package module
v0.3.0 Latest Latest
Warning

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

 Go to latest Published: Dec 20, 2020 License: BSD-3-Clause Imports: 15 Imported by: 1

README

mysqlx

Build status report Latest License

More Badages

Supported Go version

  • Go 1.11
  • Go 1.12
  • Go 1.13

Usage

// TODO

Supported MySQL data types

  • Signed Integers:
    • bigint(n), int(n), smallint(n), tinyint(n)
  • Unsigned Integers:
    • bigint(n) unsigned: Should be configured as ubigint(n)
    • int(n) unsigned: Should be configured as uint(n)
    • smallint(n) unsigned: Should be configured as usmallint(n)
    • tinyint(n) unsigned: Should be configured as utinyint(n)
  • Date / Time Types:
    • timestamp
    • datetime, datetime(n)
    • time, time(n)
    • date
    • year

Notes

Error sql: **** unsupported Scan, storing driver.Value type []uint8 into type *time.Time

reference: Stackoverflow

This is because sqlx does not parse *time.Time by default. Add "parseTime=true" parameter then opening MySQL with sqlx.

Changelog

Click here

Simple Benchmask Test Result

Please refer to benchmark test file. Temporally only SELECT is tested. Several conditions among mysqlx, sqlx and gorm(v1) are tested.

Benchmark test statement: go test -bench=. -run=none -benchmem -benchtime=10s. Test table sise: 100000.

Select by main key

Select a record by auto-increment main key. This is the most basic way to reading record. We use statements for each package conditions:

  • mysqlx: db.Select(&res, mysqlx.Condition("id", "=", id))
  • sqlx (with "= 1234"): db.Select(&res, "SELECT * FROM t_student WHERE id=" + idStr)
  • sqlx (with "= ?"): db.Select(&res, "SELECT * FROM t_student WHERE id=?", id)
  • gorm (with 'Find' function): d.Where("id = ?", id+1).Find(&res)
  • gorm (with 'First' function): d.First(&res, id)
Package nanoseconds/op bytes/op allocs/op
mysqlx 1,038,348 1696 37
sqlx (with "= 1234") 1,115,127 1039 18
sqlx (with "= ?") 2,112,185 1247 26
gorm (with 'Find' function) 2,256,562 6641 105
gorm (with 'First' function) 1,114,290 4295 97
Select By A VARCHAR Field

One of the t_student field is generated by uuid. We use statements for each packages:

  • mysqlx: db.Select(&res, Condition("name", "=", name))
  • sqlx (with "= 'Alice'"): db.Select(&res, "SELECT * FROM t_student WHERE name='" + name + "'")
  • sqlx (with "= ?"): db.Select(&res, "SELECT * FROM t_student WHERE name=?", name)
  • gorm (with 'Find' function): d.Where("name = ?", name).Find(&res)
  • gorm (with 'First' function): d.Where("name = ?", name).First(&res)
Package nanoseconds/op bytes/op allocs/op
mysqlx 1,247,630 1848 37
sqlx (with "= 'Alice'") 1,146,627 1064 18
sqlx (with "= ?") 2,023,415 1240 25
gorm (with 'Find' function) 2,073,272 6625 104
gorm (with 'First' function) 2,207,229 5377 116

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func GetQueryFromError added in v0.3.0 

func GetQueryFromError(e error) string

GetQueryFromError fetch SQL query statements in returned error type by mysqlx.

Types

type And added in v0.2.0 

type And []interface{}

And packages conditions with AND logic. Only Cond, *Cond, Or, And types are acceptable in Conds slice.

type Cond 

type Cond struct {
	Param    string
	Operator string
	Value    interface{}
}

Cond is for constructing MySQL WHERE statement

func Condition added in v0.3.0 

func Condition(param, operator string, value interface{}) *Cond

Condition return Cond data in function format

func Like added in v0.3.0 

func Like(param string, likeParts []string) *Cond

Like return Cond data with LIKE operator in function format

type DB 

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

DB is the main structure for mysqlx

func New 

func New(db *sqlx.DB) (ret *DB, err error)

New initialize a *DB object with a given *sqlx.DB, which should be connect a certain database.

func Open 

func Open(param Param) (ret *DB, err error)

Open initialize a *DB object with a valid *sqlx.DB

func (*DB) AutoCreateTable 

func (d *DB) AutoCreateTable()

AutoCreateTable enables auto table creation. DB will check if table is created previously before each access to the DB.

Please do NOT invoke this unless you need to automatically create table in runtime.

Note 1: if a table was created once by mysqlx.DB after it was initialized, it will be noted as "created" and cached. Then mysqlx.DB will not check into MySQL DB again.

Note 2: auto-table-creation will NOT be activated in Select() function!

func (*DB) CreateOrAlterTableStatements added in v0.2.0 

func (d *DB) CreateOrAlterTableStatements(v interface{}, opts ...Options) (exists bool, statements []string, err error)

CreateOrAlterTableStatements returns 'CREATE TABLE ... IF NOT EXISTS ...' or 'ALTER TABLE ...' statements, but will not execute them. If the table does not exists, 'CREATE TABLE ...' statement will be returned. If the table exists and needs no alteration, an empty string slice would be returned. Otherwise, a string slice with 'ALTER TABLE ...' statements would be returned.

The returned exists identifies if the table exists in database.

func (*DB) CreateTable 

func (d *DB) CreateTable(v interface{}, opts ...Options) error

CreateTable creates a table if not exist. If the table exists, it will alter it if necessary

func (*DB) CurrentDatabase added in v0.2.0 

func (d *DB) CurrentDatabase() (string, error)

CurrentDatabase gets current operating database

func (*DB) Database 

func (d *DB) Database() string

Database returns database name in DB

func (*DB) Delete 

func (d *DB) Delete(prototype interface{}, args ...interface{}) (sql.Result, error)

Delete executes SQL DELETE statement with given conditions

func (*DB) Insert 

func (d *DB) Insert(v interface{}, opts ...Options) (result sql.Result, err error)

Insert insert a given structure. auto-increment fields will be ignored

func (*DB) InsertFields 

func (d *DB) InsertFields(s interface{}, backQuoted bool) (keys []string, values []string, err error)

InsertFields return keys and values for inserting. Auto-increment fields will be ignored

func (*DB) InsertIfNotExists 

func (d *DB) InsertIfNotExists(insert interface{}, conds ...interface{}) (res sql.Result, err error)

InsertIfNotExists is the same as SelectOrInsert but lacking select statement

func (*DB) InsertMany added in v0.3.0 

func (d *DB) InsertMany(records interface{}, opts ...Options) (result sql.Result, err error)

InsertMany insert multiple records into table. If additional option with table name is not given, mysqlx will use the FIRST table name in records for all.

func (*DB) InsertOnDuplicateKeyUpdate added in v0.3.0 

func (d *DB) InsertOnDuplicateKeyUpdate(
	v interface{}, updates map[string]interface{}, opts ...Options,
) (result sql.Result, err error)

InsertOnDuplicateKeyUpdate executes 'INSERT ... ON DUPLICATE KEY UPDATE ...' statements. This function is a combination of Insert and Update, without WHERE conditions.

func (*DB) KeepAlive 

func (d *DB) KeepAlive()

KeepAlive automatically keeps alive with database

func (*DB) MustCreateTable 

func (d *DB) MustCreateTable(v interface{}, opts ...Options)

MustCreateTable is same as CreateTable. But is panics if error.

func (*DB) ReadStructFields 

func (d *DB) ReadStructFields(s interface{}) (ret []*Field, err error)

ReadStructFields returns all valid SQL fields by given structure and will buffer it

func (*DB) ReadTableFields 

func (d *DB) ReadTableFields(table string) (ret []*Field, err error)

ReadTableFields returns all fields in given table

func (*DB) ReadTableIndexes 

func (d *DB) ReadTableIndexes(table string) (map[string]*Index, map[string]*Unique, error)

ReadTableIndexes returns all indexes and uniques of given table name

func (*DB) Select 

func (d *DB) Select(dst interface{}, args ...interface{}) error

Select execute a SQL select statement

func (*DB) SelectFields 

func (d *DB) SelectFields(s interface{}) (string, error)

SelectFields returns all valid SQL fields in given structure

func (*DB) SelectOrInsert 

func (d *DB) SelectOrInsert(insert interface{}, selectResult interface{}, conds ...interface{}) (res sql.Result, err error)

SelectOrInsert executes update-if-not-exists statement

func (*DB) Sqlx 

func (d *DB) Sqlx() *sqlx.DB

Sqlx return the *sqlx.DB object

func (*DB) StopKeepAlive 

func (d *DB) StopKeepAlive()

StopKeepAlive stops the keep-alive operation

func (*DB) StructFields 

func (*DB) StructFields(s interface{}) (ret []*Field, err error)

StructFields is the same as ReadStructFields

func (*DB) Update 

func (d *DB) Update(
	prototype interface{}, fields map[string]interface{}, args ...interface{},
) (sql.Result, error)

Update execute UPDATE SQL statement with given structure and conditions

type Error added in v0.3.0 

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

Error is the error type identified by mysqlx

func Errorf added in v0.3.0 

func Errorf(format string, args ...interface{}) *Error

Errorf generates an formatted error object

func (*Error) Error added in v0.3.0 

func (e *Error) Error() string

Error returns error message of the error

func (*Error) Query added in v0.3.0 

func (e *Error) Query() string

Query returns MySQL query statements stored in Error object

func (*Error) String added in v0.3.0 

func (e *Error) String() string

String returns detail description of the error

type Field 

type Field struct {
	Name          string
	Type          string
	Nullable      bool
	Default       string
	Comment       string
	AutoIncrement bool
	OnUpdate      string
	// contains filtered or unexported fields
}

Field shows information of a field

func ReadStructFields 

func ReadStructFields(s interface{}) (ret []*Field, err error)

ReadStructFields is the same as StructFields

func StructFields 

func StructFields(s interface{}) (ret []*Field, err error)

StructFields returns all valid SQL fields by given structure

type Index 

type Index struct {
	Name   string
	Fields []string
}

Index shows the information of an index setting

func (*Index) Check 

func (i *Index) Check() error

Check checks if an index object is valid

type Limit 

type Limit struct {
	Limit int
}

Limit is for MySQL limit statement

type Offset 

type Offset struct {
	Offset int
}

Offset is for MySQL offset statement

type Options 

type Options struct {
	// TableName defines the table name of this object
	TableName string
	// TableDescption defines the description of the table, it is used in create table statement
	TableDescption string
	// Indexes defines the indexes of the table
	Indexes []Index
	// Uniques defines the uniques of the table
	Uniques []Unique
	// CreateTableParams defines additional variables in create table statements.
	// There are three default variaments, which could be replaced:
	// ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
	CreateTableParams map[string]string
	// DoNotExec stop the actual database executing process if it is set as true. Instead, CURD
	// functions would return an Error object with SQL query statement. This could used for troubleshot.
	// Please use GetQueryFromError() function to get the query statement.
	DoNotExec bool
}

Options identifies options and parameters for a structure

type Or added in v0.2.0 

type Or []interface{}

Or packages conditions with OR logic. Only Cond, *Cond, Or, And types are acceptable in the slice.

type Order 

type Order struct {
	Param string
	Seq   string
}

Order is for MySQL ORDER BY statement

type Param 

type Param struct {
	Host   string
	Port   int
	User   string
	Pass   string
	DBName string
}

Param identifies connect parameters to a database

type RawStatement added in v0.3.0 

type RawStatement string

RawStatement identifies raw MySQL statement, which will be directly added into sql statements. Now RawStatement is available in Update function.

type Unique 

type Unique struct {
	Name   string
	Fields []string
}

Unique shows the information of a unique setting

func (*Unique) Check 

func (u *Unique) Check() error

Check checks if an unique object is valid

Source Files

View all Source files

Directories

Path Synopsis
Package structs is a simple test package for mysqlx.
 Package structs is a simple test package for mysqlx.

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.