sqlca

package module
v1.3.0 Latest Latest
Warning

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

 Go to latest Published: Nov 3, 2020 License: MIT Imports: 23 Imported by: 1

README

author

lory.li

email

civet148@126.com

QQ

93864947

sqlca

a enhancement database and cache tool based on sqlx and redigogo which based on redigo and go-redis-cluster

database schema

/*
SQLyog Ultimate
MySQL - 8.0.18 : Database - test
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

/*Table structure for table `classes` */

CREATE TABLE `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'incr id',
  `class_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'class no',
  `user_id` int(11) NOT NULL COMMENT 'student id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `classes` */

insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (1,'S-01',1,'2020-04-10 10:08:08','2020-05-12 19:39:43');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (2,'S-01',2,'2020-04-10 10:08:08','2020-05-12 19:39:44');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (3,'S-01',3,'2020-04-10 10:08:08','2020-05-12 19:39:45');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (4,'S-01',4,'2020-04-10 10:08:08','2020-05-12 19:39:45');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (5,'S-02',5,'2020-04-10 10:08:08','2020-05-12 19:39:46');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (6,'S-02',8,'2020-04-10 10:08:08','2020-05-12 19:40:00');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (7,'S-02',9,'2020-04-10 10:08:08','2020-04-10 10:08:08');
insert  into `classes`(`id`,`class_no`,`user_id`,`created_at`,`updated_at`) values (8,'S-02',10,'2020-04-10 10:08:08','2020-04-10 10:08:08');

/*Table structure for table `users` */

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'auto inc id',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'user name',
  `phone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'phone number',
  `sex` tinyint(1) NOT NULL COMMENT 'user sex',
  `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'email',
  `disable` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'disabled(0=false 1=true)',
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'balance of decimal',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

/*Data for the table `users` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

data model

type UserDO struct {
    Id          int32           `db:"id"`  
    Name        string          `db:"name"`  
    Phone       string          `db:"phone"` 
    Sex         int8            `db:"sex"`   
    Email       string          `db:"email"` 
    Disable     int8            `db:"disable"`
    Balance     sqlca.Decimal   `db:"balance"`
}

open database/redis


e := sqlca.NewEngine()
e.Debug(true) //debug mode on

// open database driver (requred)
e.Open("mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4")

// open redis driver for cache (optional)
e.Open("redis://127.0.0.1:6379/cluster?db=0", 3600) //redis standalone mode

global variants

const (
	TABLE_NAME_USERS = "users"
)

orm: insert/upsert from data model

user := UserDO{
        Name:  "admin",
        Phone: "8618600000000",
        Sex:   1,
        Email: "admin@golang.org",
}

e.Model(&user).Table(TABLE_NAME_USERS).Insert()

user := UserDO{
    Id:    1,
    Name:  "lory",
    Phone: "8618688888888",
    Sex:   2,
    Email: "lory@gmail.com",
}

e.Model(&user).Table(TABLE_NAME_USERS).Select("name", "phone", "email", "sex").Upsert()

orm: update from data model

user := UserDO{
    Name:  "john",
    Phone: "8618699999999",
    Sex:   1,
    Email: "john@gmail.com",
}

e.Model(&user).Table(TABLE_NAME_USERS).Id(1).Select("name", "phone", "email", "sex").Update()

orm: query results into data model

user := UserDO{}

// default 'select * from ...'
e.Model(&user).Table(TABLE_NAME_USERS).Id(1).Query()

// just select 'name' and 'phone' 
e.Model(&user).Table(TABLE_NAME_USERS).Id(1).Select("name", "phone").Query()

orm: query results into data model slice

var users []UserDO

// select id, name, phone from users limit 3
//e.Model(&user).Table(TABLE_NAME_USERS).Select("id", name", "phone").Limit(3).Query();

// select * from users limit 3
if rowsAffected, err := e.Model(&users).Table(TABLE_NAME_USERS).Limit(3).Query(); err != nil {
    log.Errorf("query into data model [%+v] error [%v]", users, err.Error())
} else {
    log.Debugf("query into model [%+v] ok, rows affected [%v]", users, rowsAffected)
}

orm: update from data model

user := UserDO{
    Id:    1, 
    Name:  "john",
    Phone: "8618699999999",
    Sex:   1,
    Email: "john@gmail.com",
}
//SQL: update users set name='john', phone='8618699999999', sex='1', email='john@gmail.com' where id='1'
if rowsAffected, err := e.Model(&user).Table(TABLE_NAME_USERS).Select("name", "phone", "email", "sex").Cache("phone").Update(); err != nil {
    log.Errorf("update data model [%+v] error [%v]", user, err.Error())
} else {
    log.Debugf("update data model [%+v] ok, rows affected [%v]", user, rowsAffected)
}

raw: query results into data model

user := UserDO{}

//SQL: select * from users where id=1
if rowsAffected, err := e.Model(&user).QueryRaw("select * from users where id=?", 1); err != nil {
    log.Errorf("query into data model [%+v] error [%v]", user, err.Error())
} else {
    log.Debugf("query into model [%+v] ok, rows affected [%v]", user, rowsAffected)
}

raw: query results into data model slice

var users []UserDO

//SQL: select * from users where id < 5
if rowsAffected, err := e.Model(&users).QueryRaw("select * from %v where id < %v", TABLE_NAME_USERS, 5); err != nil {
    log.Errorf("query into data model [%+v] error [%v]", users, err.Error())
} else {
    log.Debugf("query into model [%+v] ok, rows affected [%v]", users, rowsAffected)
}

raw: query results into data model map[string]string slice

var users []map[string]string

//SQL: select * from users where id < 5
if rowsAffected, err := e.Model(&users).QueryMap("select * from %v where id < %v", TABLE_NAME_USERS, 5); err != nil {
    log.Errorf("query into map [%+v] error [%v]", users, err.Error())
} else {
    log.Debugf("query into map [%+v] ok, rows affected [%v]", users, rowsAffected)
}

raw: exec without data model

//e.ExecRaw("UPDATE %v SET name='duck' WHERE id='%v'", TABLE_NAME_USERS, 2) //it will work well as question placeholder
rowsAffected, lasteInsertId, err := e.ExecRaw("UPDATE users SET name=? WHERE id=?", "duck", 1)
if err != nil {
    log.Errorf("exec raw sql error [%v]", err.Error())
} else {
    log.Debugf("exec raw sql ok, rows affected [%v] last insert id [%v]", rowsAffected, lasteInsertId)
}

save data to cache by id or index

just for orm [insert/upsert/update] see the example of orm update

change primary key name

e.SetPkName("uuid")

use cache when orm query/update/insert/upsert


e := sqlca.NewEngine()
e.Debug(true) //debug mode on

// open database driver (requred)
e.Open("mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4")

// open redis driver for cache (requred)
e.Open("redis://127.0.0.1:6379/cluster?db=0", 3600) //redis standalone mode

user := UserDO{
    Id:    0, 
    Name:  "john",
    Phone: "8618699999999",
    Sex:   1,
    Email: "john@gmail.com",
}
e.Model(&user).Table(TABLE_NAME_USERS).Cache("phone").Insert()

tx (TxBegin...TxGet...TxExec...TxCommit)


func SqlcaTxGetAndExec(e *sqlca.Engine) (err error) {

	var tx *sqlca.Engine
	//transaction: select user id form users where phone is '8618600000000' and update users disable to 1 by user id
	if tx, err = e.TxBegin(); err != nil {
		log.Errorf("TxBegin error [%v]", err.Error())
		return
	}

	var UserId int32

	//query results into base variants
	_, err = tx.TxGet(&UserId, "SELECT id FROM users WHERE phone='%v'", "8618600000000")
	if err != nil {
		log.Errorf("TxGet error %v", err.Error())
		_ = tx.TxRollback()
		return
	}
	var lastInsertId, rowsAffected int64
	if UserId == 0 {
		log.Warnf("select id users by phone number but user not exist")
		_ = tx.TxRollback()
		return
	}
	log.Debugf("base variant of user id [%+v]", UserId)
	lastInsertId, rowsAffected, err = tx.TxExec("UPDATE users SET disable=? WHERE id=?", 1, UserId)
	if err != nil {
		log.Errorf("TxExec error %v", err.Error())
		_ = tx.TxRollback()
		return
	}
	log.Debugf("user id [%v] disabled, last insert id [%v] rows affected [%v]", UserId, lastInsertId, rowsAffected)

	//query results into a struct object or slice
	var dos []UserDO
	_, err = tx.TxGet(&dos, "SELECT * FROM users WHERE disable=1")
	if err != nil {
		log.Errorf("TxGet error %v", err.Error())
		_ = tx.TxRollback()
		return
	}
	for _, do := range dos {
		log.Debugf("struct user data object [%+v]", do)
	}

	err = tx.TxCommit()
	return
}

index record to cache

user := UserDO{
    Id:    1,
    Name:  "john3",
    Phone: "8615011111114",
    Sex:   1,
    Email: "john3@gmail.com",
}

//SQL: update users set name='john3', phone='8615011111114', sex='1', email='john3@gmail.com' where id='1'
//index: name, phone
//redis key:  sqlca:cache:[db]:[table]:[column]:[column value]
if rowsAffected, err := e.Model(&user).
    Table(TABLE_NAME_USERS).
    Select("name", "phone", "email", "sex").
    Cache("name", "phone").
    Update(); err != nil {
    log.Errorf("update data model [%+v] error [%v]", user, err.Error())
} else {
    log.Debugf("update data model [%+v] ok, rows affected [%v]", user, rowsAffected)
}

attach exist sqlx db instance

// db is a exist sqlx.DB instance
e := sqlca.NewEngine().Attach(db)

set cache update before db update

e.SetCacheBefore(true)

delete from table


user := UserDO{
		Id: 1000,
}
//delete from data model
if rows, err := e.Model(&user).Table(TABLE_NAME_USERS).Delete(); err != nil {
    log.Errorf("delete from table error [%v]", err.Error())
} else {
    log.Debugf("delete from table ok, affected rows [%v]", rows)
}

//delete from where condition (without data model)
if rows, err := e.Table(TABLE_NAME_USERS).Where("id=1001").Delete(); err != nil {
    log.Errorf("delete from table error [%v]", err.Error())
} else {
    log.Debugf("delete from table ok, affected rows [%v]", rows)
}

//delete from primary key 'id' and value (without data model)
if rows, err := e.Table(TABLE_NAME_USERS).Id(1002).Where("disable=1").Delete(); err != nil {
    log.Errorf("delete from table error [%v]", err.Error())
} else {
    log.Debugf("delete from table ok, affected rows [%v]", rows)
}

select from multiple tables

type UserClass struct {
    UserId   int32  `db:"user_id"`
    UserName string `db:"user_name"`
    Phone    string `db:"phone"`
    ClassNo  string `db:"class_no"`
}
var ucs []UserClass
//SQL: SELECT a.*, b.class_no FROM users a, classes b WHERE a.id=b.user_id AND a.id=3
_, err := e.Model(&ucs).
    Select("a.id as user_id", "a.name", "a.phone", "b.class_no").
    Table("users a", "classes b").
    Where("a.id=b.user_id").
    And("a.id=?", 3).
    Query()
if err != nil {
    log.Errorf("query error [%v]", err.Error())
} else {
    log.Debugf("user class info [%+v]", ucs)
}

custom tag

type CustomUser struct {
    Id    int32  `protobuf:"varint,1,opt,name=id,proto3" json:"id,omitempty"` // protobuf tag
    Name  string `json:"name"`   // json tag
    Phone string `db:"phone"`    // db tag
}

var users []CustomUser
//add custom tag
e.SetCustomTag("protobuf", "json")
if count, err := e.Model(&users).
    Table(TABLE_NAME_USERS).
    Where("id < ?", 5).
    Query(); err != nil {
    log.Errorf("custom tag query error [%v]", err.Error())
} else {
    log.Debugf("custom tag query results %+v rows [%v]", users, count)
}

sqlca properties tag: readonly

type UserDO struct {
    Id    int32  `db:"id"` 
    Name  string `db:"name"`   
    Phone string `db:"phone"`    
    CreatedAt string `db:"created_at" sqlca:"readonly"` //sqlca tag: readonly
}

Documentation

Index

Constants

View Source 
const (
	CACHE_INDEX_DEEP  = 1           // index deep in cache
	CACHE_REPLICATE   = "replicate" //replicate host [ip:port,...]
	CACHE_DB_INDEX    = "db"
	CAHCE_SQLX_PREFIX = "sqlca:cache"
)
View Source 
const (
	ValueType_Data  valueType = 1 // data of table
	ValueType_Index valueType = 2 // index of data
)
View Source 
const (
	JoinType_Inner = 0 //inner join
	JoinType_Left  = 1 //left join
	JoinType_Right = 2 //right join
)
View Source 
const (
	TAG_NAME_DB       = "db"
	TAG_NAME_JSON     = "json"
	TAG_NAME_PROTOBUF = "protobuf"
	TAG_NAME_SQLCA    = "sqlca"
)
View Source 
const (
	SQLCA_TAG_VALUE_AUTO_INCR = "autoincr" //auto increment
	SQLCA_TAG_VALUE_READ_ONLY = "readonly" //read only (eg. created_at)
	SQLCA_TAG_VALUE_IGNORE    = "-"        //ignore
)
View Source 
const (
	PROTOBUF_VALUE_NAME = "name"
	SQLCA_CHAR_ASTERISK = "*"
)
View Source 
const (
	DRIVER_NAME_MYSQL    = "mysql"
	DRIVER_NAME_POSTGRES = "postgres"
	DRIVER_NAME_SQLITE   = "sqlite3"
	DRIVER_NAME_MSSQL    = "mssql"
	DRIVER_NAME_REDIS    = "redis"
)
View Source 
const (
	DATABASE_KEY_NAME_WHERE      = "WHERE"
	DATABASE_KEY_NAME_UPDATE     = "UPDATE"
	DATABASE_KEY_NAME_SET        = "SET"
	DATABASE_KEY_NAME_FROM       = "FROM"
	DATABASE_KEY_NAME_DELETE     = "DELETE"
	DATABASE_KEY_NAME_SELECT     = "SELECT"
	DATABASE_KEY_NAME_DISTINCT   = "DISTINCT"
	DATABASE_KEY_NAME_IN         = "IN"
	DATABASE_KEY_NAME_NOT_IN     = "NOT IN"
	DATABASE_KEY_NAME_OR         = "OR"
	DATABASE_KEY_NAME_AND        = "AND"
	DATABASE_KEY_NAME_INSERT     = "INSERT INTO"
	DATABASE_KEY_NAME_VALUE      = "VALUE"
	DATABASE_KEY_NAME_VALUES     = "VALUES"
	DATABASE_KEY_NAME_FOR_UPDATE = "FOR UPDATE"
	DATABASE_KEY_NAME_ORDER_BY   = "ORDER BY"
	DATABASE_KEY_NAME_ASC        = "ASC"
	DATABASE_KEY_NAME_DESC       = "DESC"
	DATABASE_KEY_NAME_HAVING     = "HAVING"
	DATABASE_KEY_NAME_CASE       = "CASE"
	DATABASE_KEY_NAME_WHEN       = "WHEN"
	DATABASE_KEY_NAME_THEN       = "THEN"
	DATABASE_KEY_NAME_ELSE       = "ELSE"
	DATABASE_KEY_NAME_END        = "END"
	DATABASE_KEY_NAME_ON         = "ON"
	DATABASE_KEY_NAME_INNER_JOIN = "INNER JOIN"
	DATABASE_KEY_NAME_LEFT_JOIN  = "LEFT JOIN"
	DATABASE_KEY_NAME_RIGHT_JOIN = "RIGHT JOIN"
	DATABASE_KEY_NAME_FULL_JOIN  = "FULL OUTER JOIN" //MSSQL-SERVER
)
View Source 
const (
	ORDER_BY_ASC                  = "asc"
	ORDER_BY_DESC                 = "desc"
	DEFAULT_CAHCE_EXPIRE_SECONDS  = 24 * 60 * 60
	DEFAULT_PRIMARY_KEY_NAME      = "id"
	DEFAULT_SLOW_QUERY_ALERT_TIME = 500 //milliseconds
)
View Source 
const (
	ModelType_Struct   = 1
	ModelType_Slice    = 2
	ModelType_Map      = 3
	ModelType_BaseType = 4
)
View Source 
const (
	URL_SCHEME_SEP  = "://"
	URL_QUERY_SLAVE = "slave"
	URL_QUERY_MAX   = "max"
	URL_QUERY_IDLE  = "idle"
)

Variables

This section is empty.

Functions

This section is empty.

Types

type AdapterType 

type AdapterType int
const (
	AdapterSqlx_MySQL    AdapterType = 1  //sqlx: mysql
	AdapterSqlx_Postgres AdapterType = 2  //sqlx: postgresql
	AdapterSqlx_Sqlite   AdapterType = 3  //sqlx: sqlite
	AdapterSqlx_Mssql    AdapterType = 4  //sqlx: mssql server
	AdapterCache_Redis   AdapterType = 11 //cache: redis
)

func (AdapterType) DriverName 

func (a AdapterType) DriverName() string

func (AdapterType) GoString 

func (a AdapterType) GoString() string

func (AdapterType) String 

func (a AdapterType) String() string

type CaseWhen added in v1.2.9 

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

func (*CaseWhen) Case added in v1.2.9 

func (c *CaseWhen) Case(strThen string, strWhen string, args ...interface{}) *CaseWhen

func (*CaseWhen) Else added in v1.2.9 

func (c *CaseWhen) Else(strElse string) *CaseWhen

func (*CaseWhen) End added in v1.2.9 

func (c *CaseWhen) End(strName string) *Engine

type Decimal added in v1.1.5 

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

func NewDecimal added in v1.1.5 

func NewDecimal(v interface{}) (d Decimal)

func (Decimal) Abs added in v1.1.5 

func (d Decimal) Abs() Decimal

Abs returns the absolute value of the decimal.

func (Decimal) Add added in v1.1.5 

func (d Decimal) Add(d2 Decimal) Decimal

Add returns d + d2

func (Decimal) Cmp added in v1.1.5 

func (d Decimal) Cmp(d2 Decimal) int

Cmp compares the numbers represented by d and d2 and returns: 

-1 if d <  d2
 0 if d == d2
+1 if d >  d2

func (Decimal) Cos added in v1.1.5 

func (d Decimal) Cos() Decimal

Cos returns the cosine of the radian argument x.

func (Decimal) Div added in v1.1.5 

func (d Decimal) Div(d2 Decimal) Decimal

Div returns d / d2. If it doesn't divide exactly, the result will have DivisionPrecision digits after the decimal point.

func (Decimal) Equal added in v1.1.5 

func (d Decimal) Equal(d2 Decimal) bool

Equal returns whether the numbers represented by d and d2 are equal.

func (Decimal) Float64 added in v1.1.5 

func (d Decimal) Float64() (f float64)

Float64 returns the nearest float64 value for d and a bool indicating whether f represents d exactly.

func (*Decimal) FromFloat added in v1.1.5 

func (d *Decimal) FromFloat(v float64)

func (*Decimal) FromInt added in v1.1.5 

func (d *Decimal) FromInt(v int64)

func (*Decimal) FromString added in v1.1.5 

func (d *Decimal) FromString(v string)

func (Decimal) GreaterThan added in v1.1.5 

func (d Decimal) GreaterThan(d2 Decimal) bool

GreaterThan (GT) returns true when d is greater than d2.

func (Decimal) GreaterThanOrEqual added in v1.1.5 

func (d Decimal) GreaterThanOrEqual(d2 Decimal) bool

GreaterThanOrEqual (GTE) returns true when d is greater than or equal to d2.

func (Decimal) IntPart added in v1.1.5 

func (d Decimal) IntPart() int64

IntPart returns the integer component of the decimal.

func (Decimal) IsNegative added in v1.1.5 

func (d Decimal) IsNegative() bool

IsNegative return 

true if d < 0
false if d == 0
false if d > 0

func (Decimal) IsPositive added in v1.1.5 

func (d Decimal) IsPositive() bool

IsPositive return 

true if d > 0
false if d == 0
false if d < 0

func (Decimal) IsZero added in v1.1.5 

func (d Decimal) IsZero() bool

IsZero return 

true if d == 0
false if d > 0
false if d < 0

func (Decimal) LessThan added in v1.1.5 

func (d Decimal) LessThan(d2 Decimal) bool

LessThan (LT) returns true when d is less than d2.

func (Decimal) LessThanOrEqual added in v1.1.5 

func (d Decimal) LessThanOrEqual(d2 Decimal) bool

LessThanOrEqual (LTE) returns true when d is less than or equal to d2.

func (Decimal) MarshalBinary added in v1.1.5 

func (d Decimal) MarshalBinary() (data []byte, err error)

MarshalBinary implements the encoding.BinaryMarshaler interface.

func (Decimal) MarshalJSON added in v1.1.5 

func (d Decimal) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface.

func (Decimal) MarshalText added in v1.1.5 

func (d Decimal) MarshalText() (text []byte, err error)

MarshalText implements the encoding.TextMarshaler interface for XML serialization.

func (Decimal) Max added in v1.1.5 

func (d Decimal) Max(rest ...Decimal) Decimal

Max returns the largest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Max with 0 arguments.

func (Decimal) Min added in v1.1.5 

func (d Decimal) Min(rest ...Decimal) Decimal

Min returns the smallest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Min with 0 arguments.

func (Decimal) Mod added in v1.1.5 

func (d Decimal) Mod(d2 Decimal) Decimal

Mod returns d % d2.

func (Decimal) Mul added in v1.1.5 

func (d Decimal) Mul(d2 Decimal) Decimal

Mul returns d * d2.

func (Decimal) Neg added in v1.1.5 

func (d Decimal) Neg() Decimal

Neg returns -d.

func (Decimal) Pow added in v1.1.5 

func (d Decimal) Pow(d2 Decimal) Decimal

Pow returns d to the power d2

func (Decimal) Round added in v1.1.5 

func (d Decimal) Round(places int32) Decimal

Round rounds the decimal to places decimal places. If places < 0, it will round the integer part to the nearest 10^(-places).

Example: 

NewFromFloat(5.45).Round(1).String() // output: "5.5"
NewFromFloat(545).Round(-1).String() // output: "550"

func (*Decimal) Scan added in v1.1.5 

func (d *Decimal) Scan(src interface{}) error

Scan implements the sql.Scanner interface for database deserialization.

func (Decimal) Sign added in v1.1.5 

func (d Decimal) Sign() int

Sign returns: 

-1 if d <  0
 0 if d == 0
+1 if d >  0

func (Decimal) Sin added in v1.1.5 

func (d Decimal) Sin() Decimal

Sin returns the sine of the radian argument x.

func (Decimal) String added in v1.1.5 

func (d Decimal) String() string

String returns the string representation of the decimal with the fixed point.

Example: 

d := New(-12345, -3)
println(d.String())

Output: 

-12.345

func (Decimal) StringFixed added in v1.1.5 

func (d Decimal) StringFixed(places int32) string

StringFixed returns a rounded fixed-point string with places digits after the decimal point.

Example: 

NewFromFloat(0).StringFixed(2) // output: "0.00"
NewFromFloat(0).StringFixed(0) // output: "0"
NewFromFloat(5.45).StringFixed(0) // output: "5"
NewFromFloat(5.45).StringFixed(1) // output: "5.5"
NewFromFloat(5.45).StringFixed(2) // output: "5.45"
NewFromFloat(5.45).StringFixed(3) // output: "5.450"
NewFromFloat(545).StringFixed(-1) // output: "550"

func (Decimal) StringScaled added in v1.1.5 

func (d Decimal) StringScaled(exp int32) string

StringScaled first scales the decimal then calls .String() on it. NOTE: buggy, unintuitive, and DEPRECATED! Use StringFixed instead.

func (Decimal) Sub added in v1.1.5 

func (d Decimal) Sub(d2 Decimal) Decimal

Sub returns d - d2.

func (Decimal) Sum added in v1.1.5 

func (d Decimal) Sum(rest ...Decimal) Decimal

Sum returns the combined total of the provided first and rest Decimals

func (Decimal) Tan added in v1.1.5 

func (d Decimal) Tan() Decimal

Tan returns the tangent of the radian argument x.

func (Decimal) Truncate added in v1.1.5 

func (d Decimal) Truncate(precision int32) Decimal

Truncate truncates off digits from the number, without rounding.

NOTE: precision is the last digit that will not be truncated (must be >= 0).

Example: 

decimal.NewFromString("123.456").Truncate(2).String() // "123.45"

func (*Decimal) UnmarshalBinary added in v1.1.5 

func (d *Decimal) UnmarshalBinary(data []byte) error

UnmarshalBinary implements the encoding.BinaryUnmarshaler interface. As a string representation is already used when encoding to text, this method stores that string as []byte

func (*Decimal) UnmarshalJSON added in v1.1.5 

func (d *Decimal) UnmarshalJSON(decimalBytes []byte) error

UnmarshalJSON implements the json.Unmarshaler interface.

func (*Decimal) UnmarshalText added in v1.1.5 

func (d *Decimal) UnmarshalText(text []byte) error

UnmarshalText implements the encoding.TextUnmarshaler interface for XML deserialization.

func (Decimal) Value added in v1.1.5 

func (d Decimal) Value() (driver.Value, error)

Value implements the driver.Valuer interface for database serialization.

type Engine 

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

func NewEngine 

func NewEngine(strUrl ...interface{}) *Engine

args: a url format string for database driver eg. "mysql://root:123456@127.0.0.1/test?charset=utf8mb4" more detail see Open method

func (*Engine) And added in v1.0.5 

func (e *Engine) And(strFmt string, args ...interface{}) *Engine

func (*Engine) Asc 

func (e *Engine) Asc(strColumns ...string) *Engine

order by [field1,field2...] asc

func (*Engine) Attach added in v1.0.4 

func (e *Engine) Attach(strDatabaseName string, db *sqlx.DB) *Engine

attach from a exist sqlx db instance

func (*Engine) AutoRollback added in v1.2.2 

func (e *Engine) AutoRollback() *Engine

func (*Engine) Cache added in v1.0.2 

func (e *Engine) Cache(indexes ...string) *Engine

set cache indexes. if null, the primary key (eg. 'id') will be cached to redis

func (*Engine) Case added in v1.2.9 

func (e *Engine) Case(strThen string, strWhen string, args ...interface{}) *CaseWhen

func (*Engine) Count added in v1.1.18 

func (e *Engine) Count() (count int64, err error)

orm count records SELECT COUNT(*) FROM table WHERE ... count, err := e.Model(nil).Table("users").Where("delete=1").Count()

func (*Engine) Counter added in v1.2.9 

func (e *Engine) Counter() *counter

func (*Engine) Debug 

func (e *Engine) Debug(ok bool)

log debug mode on or off

func (*Engine) Delete added in v1.0.4 

func (e *Engine) Delete() (rowsAffected int64, err error)

orm delete record(s) from db and cache

func (*Engine) Desc 

func (e *Engine) Desc(strColumns ...string) *Engine

order by [field1,field2...] desc

func (*Engine) Distinct added in v1.0.6 

func (e *Engine) Distinct() *Engine

set distinct when select

func (*Engine) Exclude added in v1.2.6 

func (e *Engine) Exclude(strColumns ...string) *Engine

orm select/update columns

func (*Engine) ExecRaw 

func (e *Engine) ExecRaw(strQuery string, args ...interface{}) (rowsAffected, lastInsertId int64, err error)

use raw sql to insert/update database, results can not be cached to redis/memcached/memory... return rows affected and error, if err is not nil must be something wrong

func (*Engine) Find added in v1.1.3 

func (e *Engine) Find(conditions map[string]interface{}) (rowsAffected int64, err error)

orm find with customer conditions (map[string]interface{})

func (*Engine) Force added in v1.1.17 

func (e *Engine) Force() *Engine

force update/insert read only column(s)

func (*Engine) GeoHash added in v1.2.11 

func (e *Engine) GeoHash(lng, lat float64, precision int) (strGeoHash string, strNeighbors []string)

encode geo hash string (precision 1~8) returns geo hash and neighbors areas

func (*Engine) GetPkName 

func (e *Engine) GetPkName() string

func (*Engine) GroupBy 

func (e *Engine) GroupBy(strColumns ...string) *Engine

group by [field1,field2...]

func (*Engine) Having added in v1.1.15 

func (e *Engine) Having(strFmt string, args ...interface{}) *Engine

having [condition]

func (*Engine) Id 

func (e *Engine) Id(value interface{}) *Engine

set orm primary key's value

func (*Engine) In added in v1.0.4 

func (e *Engine) In(strColumn string, args ...interface{}) *Engine

`field_name` IN ('1','2',...)

func (*Engine) InnerJoin added in v1.3.0 

func (e *Engine) InnerJoin(strTableName string) *Join

func (*Engine) Insert 

func (e *Engine) Insert() (lastInsertId int64, err error)

orm insert return last insert id and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) JsonMarshal added in v1.3.0 

func (e *Engine) JsonMarshal(v interface{}) (strJson string)

func (*Engine) JsonUnmarshal added in v1.3.0 

func (e *Engine) JsonUnmarshal(strJson string, v interface{}) (err error)

func (*Engine) LeftJoin added in v1.3.0 

func (e *Engine) LeftJoin(strTableName string) *Join

func (*Engine) Limit 

func (e *Engine) Limit(args ...int) *Engine

query limit Limit(10) - query records limit 10 (mysql/postgres)

func (*Engine) Model 

func (e *Engine) Model(args ...interface{}) *Engine

orm model use to get result set, support single struct object or slice [pointer type] notice: will clone a new engine object for orm operations(query/update/insert/upsert)

func (*Engine) NearBy added in v1.2.10 

func (e *Engine) NearBy(strLngCol, strLatCol, strAS string, lng, lat, distance float64) *Engine
-- select geo point as distance where distance <= n km (float64)

SELECT 

a.*,
(
6371 * ACOS (
COS( RADIANS( a.lat ) ) * COS( RADIANS( 28.8039097230 ) ) * COS(
  RADIANS( 121.5619236231 ) - RADIANS( a.lng )
 ) + SIN( RADIANS( a.lat ) ) * SIN( RADIANS( 28.8039097230 ) )
)
) AS distance

FROM 

t_address a

HAVING distance <= 200 -- less than or equal 200km ORDER BY 

distance
LIMIT 10

func (*Engine) Not added in v1.0.5 

func (e *Engine) Not(strColumn string, args ...interface{}) *Engine

`field_name` NOT IN ('1','2',...)

func (*Engine) Offset 

func (e *Engine) Offset(offset int) *Engine

query offset (for mysql/postgres)

func (*Engine) OnConflict 

func (e *Engine) OnConflict(strColumns ...string) *Engine

set the conflict columns for upsert only for postgresql

func (*Engine) Open 

func (e *Engine) Open(strUrl string, options ...interface{}) *Engine

open a database or cache connection pool strUrl:

  1. data source name

    [mysql] Open("mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4&slave=false&max=100&idle=1") [postgres] Open("postgres://root:123456@127.0.0.1:5432/test?sslmode=disable&slave=false&max=100&idle=1") [mssql] Open("mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false&max=100&idle=1") [sqlite] Open("sqlite:///var/lib/test.db")

  2. cache config [redis-alone] Open("redis://123456@127.0.0.1:6379/cluster?db=0") [redis-cluster] Open("redis://123456@127.0.0.1:6379/cluster?db=0&replicate=127.0.0.1:6380,127.0.0.1:6381")

options:

  1. specify master or slave, MySQL/Postgres (Options)
  2. cache data expire seconds, just for redis (Integer)

func (*Engine) Or added in v1.1.17 

func (e *Engine) Or(strFmt string, args ...interface{}) *Engine

func (*Engine) OrderBy 

func (e *Engine) OrderBy(strColumns ...string) *Engine

order by [field1,field2...] [ASC]

func (*Engine) Page added in v1.2.11 

func (e *Engine) Page(pageNo, pageSize int) *Engine

page query SELECT ... FROM ... WHERE ... LIMIT (pageNo*pageSize), pageSize

func (*Engine) Ping added in v1.1.10 

func (e *Engine) Ping() (err error)

ping database

func (*Engine) Query 

func (e *Engine) Query() (rowsAffected int64, err error)

orm query return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function if slave == true, try query from a slave connection, if not exist query from master

func (*Engine) QueryMap 

func (e *Engine) QueryMap(strQuery string, args ...interface{}) (rowsAffected int64, err error)

use raw sql to query results into a map slice (model type is []map[string]string) return results and error NOTE: Model function is must be called before call this function

func (*Engine) QueryRaw 

func (e *Engine) QueryRaw(strQuery string, args ...interface{}) (rowsAffected int64, err error)

use raw sql to query results return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) RightJoin added in v1.3.0 

func (e *Engine) RightJoin(strTableName string) *Join

func (*Engine) Select 

func (e *Engine) Select(strColumns ...string) *Engine

orm select/update columns

func (*Engine) SetCacheBefore added in v1.0.4 

func (e *Engine) SetCacheBefore(ok bool)

set cache update before database

func (*Engine) SetCustomTag added in v1.0.6 

func (e *Engine) SetCustomTag(tagNames ...string) *Engine

set your customer tag for db query/insert/update (eg. go structure generated by protobuf not contain 'db' tag) this function must calls before Model()

func (*Engine) SetLogFile added in v1.0.7 

func (e *Engine) SetLogFile(strPath string)

set log file

func (*Engine) SetPkName 

func (e *Engine) SetPkName(strName string) *Engine

set orm primary key's name, default named 'id'

func (*Engine) SetReadOnly added in v1.1.14 

func (e *Engine) SetReadOnly(columns ...string)

set read only columns

func (*Engine) Slave added in v1.1.17 

func (e *Engine) Slave() *Engine

group by [field1,field2...]

func (*Engine) SlowQuery added in v1.2.9 

func (e *Engine) SlowQuery(on bool, ms int)

slow query alert on or off on -> true/false ms -> milliseconds (can be 0 if on is false)

func (*Engine) Table 

func (e *Engine) Table(strNames ...string) *Engine

set orm query table name(s) when your struct type name is not a table name

func (*Engine) ToSQL added in v1.1.4 

func (e *Engine) ToSQL(operType OperType) (strSql string)

make SQL from orm model and operation type

func (*Engine) TxBegin added in v1.0.3 

func (e *Engine) TxBegin() (*Engine, error)

func (*Engine) TxCommit added in v1.0.3 

func (e *Engine) TxCommit() error

func (*Engine) TxExec added in v1.0.3 

func (e *Engine) TxExec(strQuery string, args ...interface{}) (lastInsertId, rowsAffected int64, err error)

func (*Engine) TxFunc added in v1.2.8 

func (e *Engine) TxFunc(fn func(tx *Engine) error) (err error)

execute transaction by customize function auto rollback when function return error

func (*Engine) TxFuncContext added in v1.2.9 

func (e *Engine) TxFuncContext(ctx context.Context, fn func(ctx context.Context, tx *Engine) error) (err error)

execute transaction by customize function with context auto rollback when function return error

func (*Engine) TxGet added in v1.0.3 

func (e *Engine) TxGet(dest interface{}, strQuery string, args ...interface{}) (count int64, err error)

func (*Engine) TxHandle added in v1.2.8 

func (e *Engine) TxHandle(handler TxHandler) (err error)

execute transaction by customize handler auto rollback when handler return error

func (*Engine) TxRollback added in v1.0.3 

func (e *Engine) TxRollback() error

func (*Engine) Update 

func (e *Engine) Update() (rowsAffected int64, err error)

orm update from model strColumns... if set, columns will be updated, if none all columns in model will be updated except primary key return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) Upsert 

func (e *Engine) Upsert(strCustomizeUpdates ...string) (lastInsertId int64, err error)

orm insert or update if key(s) conflict return last insert id and error, if err is not nil must be something wrong, if your primary key is not a int/int64 type, maybe id return 0 NOTE: Model function is must be called before call this function and call OnConflict function when you are on postgresql updates -> customize updates condition when key(s) conflict [MySQL] INSERT INTO messages(id, message_type, unread_count) VALUES('10000', '2', '1', '3') ON DUPLICATE KEY UPDATE message_type=values(message_type), unread_count=unread_count+values(unread_count) --------------------------------------------------------------------------------------------------------------------------------------- e.Model(&do).Table("messages").Upsert("message_type=values(message_type)", "unread_count=unread_count+values(unread_count)") ---------------------------------------------------------------------------------------------------------------------------------------

func (*Engine) Where 

func (e *Engine) Where(strWhere string, args ...interface{}) *Engine

orm where condition

type Fetcher 

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

type Join added in v1.3.0 

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

func (*Join) On added in v1.3.0 

func (j *Join) On(strOn string, args ...interface{}) *Engine

type JoinType added in v1.3.0 

type JoinType int

func (JoinType) GoString added in v1.3.0 

func (t JoinType) GoString() string

func (JoinType) String added in v1.3.0 

func (t JoinType) String() string

func (JoinType) ToKeyWord added in v1.3.0 

func (t JoinType) ToKeyWord() string

type ModelReflector 

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

func (*ModelReflector) ToMap 

func (s *ModelReflector) ToMap(tagNames ...string) map[string]interface{}

parse struct tag and value to map

type ModelType 

type ModelType int

func (ModelType) GoString 

func (m ModelType) GoString() string

func (ModelType) String 

func (m ModelType) String() string

type OperType 

type OperType int
const (
	OperType_Query     OperType = 1  // orm: query sql
	OperType_Update    OperType = 2  // orm: update sql
	OperType_Insert    OperType = 3  // orm: insert sql
	OperType_Upsert    OperType = 4  // orm: insert or update sql
	OperType_Tx        OperType = 5  // orm: tx sql
	OperType_QueryRaw  OperType = 6  // raw: query sql into model
	OperType_ExecRaw   OperType = 7  // raw: insert/update sql
	OperType_QueryMap  OperType = 8  // raw: query sql into map
	OperType_Delete    OperType = 9  // orm: delete sql
	OperType_ForUpdate OperType = 10 // orm: select ... for update sql
)

func (OperType) GoString 

func (o OperType) GoString() string

func (OperType) String 

func (o OperType) String() string

type Options added in v1.2.3 

type Options struct {
	Max   int  //max active connections
	Idle  int  //max idle connections
	Slave bool //is a slave DSN ?
}

type TxHandler added in v1.2.8 

type TxHandler interface {
	OnTransaction(tx *Engine) error
}

type UrlInfo 

type UrlInfo struct {
	Scheme     string
	Host       string // host name and port like '127.0.0.1:3306'
	User       string
	Password   string
	Path       string
	Fragment   string
	Opaque     string
	ForceQuery bool
	Queries    map[string]string
}

func ParseUrl 

func ParseUrl(strUrl string) (ui *UrlInfo)

URL have some special characters in password(支持URL中密码包含特殊字符)

Source Files

View all Source files

Directories

Path Synopsis
cmd
db2go
db2go/cmder
db2go/dataobject
Code generated by db2go.
 Code generated by db2go.
db2go/mssql
db2go/mysql
db2go/postgres
db2go/schema
db2go/structs

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.