sqlca

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

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

USE test;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'auto inc id',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `phone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT 1,
  `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, 'rose', '8613011112222', 2, 'rose123@hotmail.com', '2020-03-11 08:46:57', '2020-03-11 08:48:14');
INSERT INTO `users` VALUES (2, 'john', '8613100003333', 1, 'john333@hotmail.com', '2020-03-11 08:46:57', '2020-03-11 08:47:59');

SET FOREIGN_KEY_CHECKS = 1;

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"` 
}

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").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

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] and tx [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().Insert()

tx: orm and raw

user1 := UserDO{
    //Id:    0,
    Name:  "user1",
    Phone: "8618600000001",
    Sex:   1,
    Email: "user1@hotmail.com",
}

user2 := UserDO{
    //Id:    0,
    Name:  "user2",
    Phone: "8618600000002",
    Sex:   1,
    Email: "user2@hotmail.com",
}
tx1 := e.Model(&user1).Table(TABLE_NAME_USERS).ToTxInsert()
tx2 := e.Model(&user2).Table(TABLE_NAME_USERS).ToTxInsert()
if err := e.Tx(tx1, tx2); err != nil {
    log.Errorf("tx error [%v]", err.Error())
} else {
    log.Debugf("tx ok")
}

tx1 := "INSERT INTO users (`name`,`phone`,`sex`,`email`) VALUES ('user3','8618600000003','1','user3@hotmail.com')"
tx2 := "INSERT INTO users (`name`,`phone`,`sex`,`email`) VALUES ('user4','8618600000004','2','user4@hotmail.com')"

if err := e.TxRaw(tx1, tx2); err != nil {
    log.Errorf("tx raw error [%v]", err.Error())
} else {
    log.Debugf("tx raw ok")
}

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:  sqlx:cache:[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)
}