mysql

package
v0.0.0-...-90299f3 Latest Latest
Warning

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

 Go to latest Published: Sep 24, 2020 License: GPL-3.0 Imports: 12 Imported by: 0

README

go-activerecord for mysql

import  github.com/snail007/go-activerecord/mysql

0.Configure 
    var dbCfg = mysql.NewDBConfig()
    dbCfg.Password = "admin"
    db, err := mysql.NewDB(dbCfg)
    if err != nil {
            fmt.Printf("ERR:%s", err)
            return
    }
    dbCfg.xxx,"xxxx" default is below:
    Charset:                  "utf8",
    Collate:                  "utf8_general_ci",
    Database:                 "test",
    Host:                     "127.0.0.1",
    Port:                     3306,
    Username:                 "root",
    Password:                 "",
    TablePrefix:              "",
    TablePrefixSqlIdentifier: "",
    Timeout:                  3000, //ms
    SetMaxOpenConns:          500,
    SetMaxIdleConns:          50,
1.Connect to multilple Database
    group := mysql.NewDBGroup("default")
    group.Regist("default", NewDBConfigWith("127.0.0.1", 3306, "test", "root", "admin"))
    group.Regist("blog", NewDBConfigWith("127.0.0.1", 3306, "blog_db", "root", "admin"))
    group.Regist("www", NewDBConfigWith("127.0.0.1", 3306, "www_db", "root", "admin"))
    //group.DB() equal to group.DB("default")
    db := group.DB("www")
    if db != nil {
        rs, err := db.Query(db.AR().From("test"))
        if err != nil {
            t.Errorf("ERR:%s", err)
        } else {
            fmt.Println(rs.Rows())
        }
    } else {
        fmt.Printf("db group config of name %s not found", "www")
    }
2.Select
    type User{
        ID int `column:"id"`
        Name string `column:"name"`
    }
    rs, err := db.Query(db.AR().
                Select("*").
                From("log").
                Where(map[string]interface{}{
                    "id": 11,
                })
    )
    if err != nil {
		fmt.Printf("ERR:%s", err)
	} else {
		fmt.Println(rs.Rows())
	}
    //struct 
    _user :=User{}
    user,err:=rs.Struct(_user)
    if err != nil {
		fmt.Printf("ERR:%s", err)
	} else {
		fmt.Println(user)
	}
    //structs
    _user :=User{}
    users,err:=rs.Structs(_user)
    if err != nil {
		fmt.Printf("ERR:%s", err)
	} else {
		fmt.Println(users)
	}
    //Map structs
    _user :=User{}
    usersMap,err=rs.MapStructs("id",_user)
    if err != nil {
		fmt.Printf("ERR:%s", err)
	} else {
		fmt.Println(usersMap)
	}
    //db.AR() return a new *mysql.ActiveRecord,you can use it to build you sql.
    //all of db.AR() usage to see mysql_test.go

    rs is a ResultSet,all of ResultSet method and properties is :
    ResultSet.Len()
        how many rows of select
    ResultSet.MapRows(keyColumn string) (rowsMap map[string]map[string]string)
        get a map which key is each value of row[keyColumn]
    ResultSet.MapStructs(keyColumn string, strucT interface{}) (structsMap map[string]interface{},
     err error)
        get a map which key is row[keyColumn],value is strucT
    ResultSet.Rows() (rows []map[string]string)
        get rows of select
    ResultSet.Structs(strucT interface{}) (structs []interface{}, err error)
        get array of strucT of select
    ResultSet.Row() (row map[string]string)
        get first of rows
    ResultSet.Struct(strucT interface{}) (Struct interface{}, err error)
        get first strucT of select 
    ResultSet.Values(column string) (values []string)
        get an array contains each row[column] 
    ResultSet.MapValues(keyColumn, valueColumn string) (values map[string]string)
        get a map key is each row[column],value is row[valueColumn]
    ResultSet.Value(column string) (value string)
        get first row[column] of rows
    ResultSet.LastInsertId
        if sql type is insert , this is the last insert id
    ResultSet.RowsAffected
        if sql type is write , this is the count of rows affected

	
3.Insert & Insert Batch
    Insert:
        rs, err := db.Exec(db.AR().Insert("test", map[string]interface{}{
			"id":   "id11122",
			"name": "333",
		}))
    Insert Batch:
        rs, err := db.Exec(db.AR().InsertBatch("test", []map[string]interface{}{
            map[string]interface{}{
                "id":   "id11122",
                "name": "333",
            },
            map[string]interface{}{
                "id":   "id11122",
                "name": "4444",
            },
        }))
    lastInsertId:=rs.LastInsertId
    rowsAffected:=rs.RowsAffected
    fmt.printf("last insert id : %d,rows affected : %d",lastInsertId,rowsAffected)
	
4.Update & Update Batch
    Update:
    1.common Update(table,data,where)
        rs, err := db.Exec(db.AR().Update("test", map[string]interface{}{
			"id":   "id11122",
			"name": "333",
		}),map[string]interface{}{
			"pid":   223,
		}))
    //equal sql below :
    UPDATE  `test` 
    SET `id` = ? , `name` =  ?
    WHERE `pid` = ?

    2.column operate
    Update:
        rs, err := db.Exec(db.AR().Update("test", map[string]interface{}{
			"id":   "id11122",
			"score +": "333",
		}),map[string]interface{}{
			"pid":   223,
		}))
    //equal sql below :
    UPDATE  `test` 
    SET `id` = ? , `score` = `score` + ?
    WHERE `pid` = ?

    Update Batch:
    1.common update UpdateBatch(table,data,DataWhereColumns)
    rs, err := db.Exec(db.AR().UpdateBatch("test", []map[string]interface{}{
		map[string]interface{}{
			"id":   "id1",
			"name": "333",
		},
		map[string]interface{}{
			"id":   "id2",
			"name": "4444",
		},
	}, []string{"id"}))
    rowsAffected:=rs.RowsAffected
    fmt.printf("rows affected : %d",rowsAffected)
    //equal sql below :
    UPDATE  `test` 
    SET `name` = CASE 
    WHEN `id` = ? THEN  ? 
    WHEN `id` = ? THEN  ? 
    ELSE `score` END 
    WHERE id IN (?,?)

    2.column operate
    rs, err := db.Exec(db.AR().UpdateBatch("test", []map[string]interface{}{
		map[string]interface{}{
			"id":   "id11",
			"score +": 10,
		},
		map[string]interface{}{
			"id":   "id22",
			"score +": 20,
		},
	}, , []string{"id"}))
    rowsAffected:=rs.RowsAffected
    fmt.printf("rows affected : %d",rowsAffected)
    //equal sql below :
    UPDATE  `test` 
    SET `score` = CASE 
    WHEN `id` = ? THEN `score` + ? 
    WHEN `id` = ? THEN `score` + ? 
    ELSE `score` END 
    WHERE id IN (?,?)

    3.where on more column
    rs, err := db.Exec(db.AR().UpdateBatch("test", []map[string]interface{}{
		map[string]interface{}{
			"id":      "id1",
			"gid":     22,
			"name":    "test1",
			"score +": 1,
		}, map[string]interface{}{
			"id":      "id2",
			"gid":     33,
			"name":    "test2",
			"score +": 1,
		},
	}, []string{"id", "gid"})
    rowsAffected:=rs.RowsAffected
    fmt.printf("rows affected : %d",rowsAffected)
    //equal sql below :
    UPDATE  `test` 
    SET `name` = CASE 
    WHEN `id` = ? AND `gid` = ? THEN ? 
    WHEN `id` = ? AND `gid` = ? THEN ? 
    ELSE `name` END,`score` = CASE 
    WHEN `id` = ? AND `gid` = ? THEN `score` + ? 
    WHEN `id` = ? AND `gid` = ? THEN `score` + ? 
    ELSE `score` END 
    WHERE id IN (?,?)   AND gid IN (?,?)
5.Delete
    rs, err := db.Exec(db.AR().Delete("test", map[string]interface{}{
        "pid":   223,
    }))
    rowsAffected:=rs.RowsAffected
    fmt.printf("rows affected : %d",rowsAffected)
6.Raw SQL Query
    rs, err := db.Exec(db.AR().Raw("insert into test(id,name) values (?,?)", 555,"6666"))
    if err != nil {
        fmt.Printf("ERR:%s", err)
    } else {
        fmt.Println(rs.RowsAffected, rs.LastInsertId)
    }
    //notice:
    if  dbCfg.TablePrefix="user_" 
        dbCfg.TablePrefixSqlIdentifier="{__PREFIX__}" 
    then
        db.AR().Raw("insert into {__PREFIX__}test(id,name) values (?,?)
    when execute sql,{__PREFIX__} will be replaced with "user_"
7.Cache Query
    MyCache is an example to set or get cache data . 

    var (
        cacheData = map[string][]byte{}
    )

    type MyCache struct {
    }

    func (c *MyCache) Set(key string, data []byte, expire uint) (err error) {
        cacheData[key] = data
        log.Println("set cache")
        return
    }
    func (c *MyCache) Get(key string) (data []byte, err error) {
        if v, ok := cacheData[key]; ok {
            log.Println("form cache")
            return v, nil
        }
        return nil, errors.New("key not found or expired")
    }
    func main() {
        g := mysql.NewDBGroupCache("default", &MyCache{})
        g.Regist("default", mysql.NewDBConfigWith("127.0.0.1", 3306, "test", "root", "admin"))
        fmt.Println(g.DB().Query(g.DB().AR().Cache("testkey", 30).From("test")))
        rs, _ := g.DB().Query(g.DB().AR().Cache("testkey", 30).From("test"))
        fmt.Println(rs.Row())
    }
    output like:
    2017/11/21 18:12:01 set cache
    &{0xc42000d340 0 0} 
    2017/11/21 18:12:01 form cache
    map[pid:1 id:a1 name:a1111 gid:11]

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func MapCurrent 

func MapCurrent(v map[string]interface{}) interface{}

func MapKey 

func MapKey(v map[string]interface{}) string

Types

type ActiveRecord 

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

func (*ActiveRecord) Cache 

func (ar *ActiveRecord) Cache(key string, seconds uint) *ActiveRecord

func (*ActiveRecord) Delete 

func (ar *ActiveRecord) Delete(table string, where map[string]interface{}) *ActiveRecord

func (*ActiveRecord) From 

func (ar *ActiveRecord) From(from string) *ActiveRecord

func (*ActiveRecord) FromAs 

func (ar *ActiveRecord) FromAs(from, as string) *ActiveRecord

func (*ActiveRecord) GroupBy 

func (ar *ActiveRecord) GroupBy(column string) *ActiveRecord

func (*ActiveRecord) Having 

func (ar *ActiveRecord) Having(having string) *ActiveRecord

func (*ActiveRecord) HavingWrap 

func (ar *ActiveRecord) HavingWrap(having, leftWrap, rightWrap string) *ActiveRecord

func (*ActiveRecord) Insert 

func (ar *ActiveRecord) Insert(table string, data map[string]interface{}) *ActiveRecord

func (*ActiveRecord) InsertBatch 

func (ar *ActiveRecord) InsertBatch(table string, data []map[string]interface{}) *ActiveRecord

func (*ActiveRecord) Join 

func (ar *ActiveRecord) Join(table, as, on, type_ string) *ActiveRecord

func (*ActiveRecord) Limit 

func (ar *ActiveRecord) Limit(limit ...int) *ActiveRecord

Limit Limit(offset,count) or Limit(count)

func (*ActiveRecord) OrderBy 

func (ar *ActiveRecord) OrderBy(column, type_ string) *ActiveRecord

func (*ActiveRecord) Raw 

func (ar *ActiveRecord) Raw(sql string, values ...interface{}) *ActiveRecord

func (*ActiveRecord) Replace 

func (ar *ActiveRecord) Replace(table string, data map[string]interface{}) *ActiveRecord

func (*ActiveRecord) ReplaceBatch 

func (ar *ActiveRecord) ReplaceBatch(table string, data []map[string]interface{}) *ActiveRecord

func (*ActiveRecord) Reset 

func (ar *ActiveRecord) Reset()

func (*ActiveRecord) SQL 

func (ar *ActiveRecord) SQL() string

func (*ActiveRecord) Select 

func (ar *ActiveRecord) Select(columns string) *ActiveRecord

func (*ActiveRecord) SelectNoWrap 

func (ar *ActiveRecord) SelectNoWrap(columns string) *ActiveRecord

func (*ActiveRecord) Set 

func (ar *ActiveRecord) Set(column string, value interface{}) *ActiveRecord

func (*ActiveRecord) SetNoWrap 

func (ar *ActiveRecord) SetNoWrap(column string, value interface{}) *ActiveRecord

func (*ActiveRecord) Update 

func (ar *ActiveRecord) Update(table string, data, where map[string]interface{}) *ActiveRecord

func (*ActiveRecord) UpdateBatch 

func (ar *ActiveRecord) UpdateBatch(table string, values []map[string]interface{}, whereColumn []string) *ActiveRecord

func (*ActiveRecord) Values 

func (ar *ActiveRecord) Values() []interface{}

func (*ActiveRecord) Where 

func (ar *ActiveRecord) Where(where map[string]interface{}) *ActiveRecord

func (*ActiveRecord) WhereWrap 

func (ar *ActiveRecord) WhereWrap(where map[string]interface{}, leftWrap, rightWrap string) *ActiveRecord

func (*ActiveRecord) Wrap 

func (ar *ActiveRecord) Wrap(v string) string

type Cache 

type Cache interface {
	Set(key string, val []byte, expire uint) (err error)
	Get(key string) (data []byte, err error)
}

type DB 

type DB struct {
	Config   DBConfig
	ConnPool *sql.DB
	DSN      string
}

func NewDB 

func NewDB(config DBConfig) (db DB, err error)

func (*DB) AR 

func (db *DB) AR() (ar *ActiveRecord)

func (*DB) Begin 

func (db *DB) Begin(config DBConfig) (tx *sql.Tx, err error)

func (*DB) Exec 

func (db *DB) Exec(ar *ActiveRecord) (rs *ResultSet, err error)

func (*DB) ExecSQL 

func (db *DB) ExecSQL(sqlStr string, values ...interface{}) (rs *ResultSet, err error)

func (*DB) ExecSQLTx 

func (db *DB) ExecSQLTx(sqlStr string, tx *sql.Tx, values ...interface{}) (rs *ResultSet, err error)

func (*DB) ExecTx 

func (db *DB) ExecTx(ar *ActiveRecord, tx *sql.Tx) (rs *ResultSet, err error)

func (*DB) Query 

func (db *DB) Query(ar *ActiveRecord) (rs *ResultSet, err error)

type DBConfig 

type DBConfig struct {
	Charset                  string
	Collate                  string
	Database                 string
	Host                     string
	Port                     int
	Username                 string
	Password                 string
	TablePrefix              string
	TablePrefixSqlIdentifier string
	Timeout                  int
	ReadTimeout              int
	WriteTimeout             int
	MaxIdleConns             int
	MaxOpenConns             int
	ConnMaxLifetimeSeconds   int
	Cache                    Cache
}

func NewDBConfig 

func NewDBConfig() DBConfig

func NewDBConfigWith 

func NewDBConfigWith(host string, port int, dbName, user, pass string) (cfg DBConfig)

type DBGroup 

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

func NewDBGroup 

func NewDBGroup(defaultConfigName string) (group *DBGroup)

func NewDBGroupCache 

func NewDBGroupCache(defaultConfigName string, cache Cache) (group *DBGroup)

func (*DBGroup) DB 

func (g *DBGroup) DB(name ...string) (db *DB)

func (*DBGroup) Regist 

func (g *DBGroup) Regist(name string, cfg DBConfig) (err error)

func (*DBGroup) RegistGroup 

func (g *DBGroup) RegistGroup(cfg map[string]DBConfig) (err error)

type ResultSet 

type ResultSet struct {
	LastInsertId int64
	RowsAffected int64
	// contains filtered or unexported fields
}

func NewResultSet 

func NewResultSet(rawRows *[]map[string][]byte) (rs *ResultSet)

func (*ResultSet) Len 

func (rs *ResultSet) Len() int

func (*ResultSet) MapRows 

func (rs *ResultSet) MapRows(keyColumn string) (rowsMap map[string]map[string]string)

func (*ResultSet) MapStructs 

func (rs *ResultSet) MapStructs(keyColumn string, strucT interface{}) (structsMap map[string]interface{}, err error)

func (*ResultSet) MapValues 

func (rs *ResultSet) MapValues(keyColumn, valueColumn string) (values map[string]string)

func (*ResultSet) Row 

func (rs *ResultSet) Row() (row map[string]string)

func (*ResultSet) Rows 

func (rs *ResultSet) Rows() (rows []map[string]string)

func (*ResultSet) Struct 

func (rs *ResultSet) Struct(strucT interface{}) (Struct interface{}, err error)

func (*ResultSet) Structs 

func (rs *ResultSet) Structs(strucT interface{}) (structs []interface{}, err error)

func (*ResultSet) Value 

func (rs *ResultSet) Value(column string) (value string)

func (*ResultSet) Values 

func (rs *ResultSet) Values(column string) (values []string)

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.