初衷
市面上優秀的ORM已經很多了,例如gorm,xorm,sqlx等,已經足夠滿足我們日常使用的各種場景了。但對於像我這樣喜歡簡單好用的人來說gorm,xorm就像一個龐然大物,裡面很多功能我都用不上。再加上由於工作的原因,我經常在PHP和Golang之間切換,PHP用的Laravel框架,已經習慣了Laravel的DB操作方式,故此有了自己寫一個類似Laravel DB操作的ORM,希望能通過本文拋磚引玉,大家都能打造一款符合自己業務場景,簡單好用的ORM
目標
在動手編寫代碼之前,我們先暢想一下實現完以後,最終的使用方式是什麼樣子,有目標才知道要怎麼做。下面是我想實現的效果
//註冊數據庫
kdb.RegisterDatabase(cfg)
通過手寫SQL操作數據庫
//查詢操作
kdb.Select("select * from user where id = ?", ?)
//插入操作
kdb.Insert("insert into user(uid,username) values(?, ?)", 1, "nopsky")
//更新操作
kdb.Insert("update user set username = ? where uid = ?", "nopsky", 1)
//刪除操作
kdb.Delete("delete from user where uid = ?", 1)
通過鏈式操作數據庫
//查詢
kdb.Table("user").Where("uid",1)
//通過map或者struct插入數據
kdb.Table("user").Insert(user)
//批量插入
kdb.Table("user").MultiInsert(users)
//通過map更新數據
kdb.Table("user").Where("uid",1).update(map[string]interface{}{"username":"nopsky"})
//刪除
kdb.Table("user").Where("uid",1).Delete()
操作數據庫目標已經有了,我們還需要考慮返回值的操作,我們希望返回值能有以下的映射方式
1.返回Slice
.ToArray()
2.返回Map
.ToMap()
3.返回Struct
.ToStruct(&Users)
例如:
//返回單條數據, 並按照map[string][string]格式返回
kdb.Table("user").where("uid", 1).First().ToMap()
//返回單條數據, 並按照[]string的格式返回
kdb.Table("user").where("uid", 1).First().ToArray()
//返回單挑數據,並按照struct的格式返回
kdb.Table("user").where("uid", 1).First().ToStruct()
//返回多條數據, 並按照[]map[string]string格式返回
kdb.Table("user").where("sex", 1).Get().ToMap()
//返回多條數據, 並按照[]string的格式返回
kdb.Table("user").where("sex", 1).Get().ToArray()
//返回多條數據,並按照[]struct的格式返回
kdb.Table("user").where("sex", 1).Get().ToStruct(&Users)
//也可以指定字段返回
kdb.Table("user").where("sex",1).Get("uid", "name").ToStruct(&Users)
還有對應的事務操作
tx := kdb.BeginTransaction()
tx.Select(....)
tx.Table("user")...
tx.Update(...)
tx.Commit()
tx.Rollback()
OK,我們有了最終實現的ORM需要提供的功能,接下來就是逐步來實現了
第一步: 註冊數據庫
在實際業務場景當中,我們連接數據庫的情況一般分為
1.單個數據庫 2.主從數據庫 3.根據功能業務模塊不一樣,連接不同的數據庫
所以我們第一步要做的事就先把數據庫註冊並管理起來,以便我們再使用的時候可以方便的使用指定的數據庫。
我們先創建一個config.go文件,用來存儲我們的數據配置
package kdb
import "time"
type DBConfig struct {
Name string //數據庫連接別名
IsMaster bool //是否是主庫
Driver string
Dsn string
MaxLifetime time.Duration
MaxIdleConns int
MaxOpenConns int
}
type KConfig struct {
DBConfigList []DBConfig
}
創建一個manager.go,用於管理我們的數據庫
package kdb
import (
"database/sql"
"fmt"
"math/rand"
"strings"
"time"
)
const defaultGroupName = "mysql"
var m = newManager()
type manager struct {
dbs map[string]map[string][]*sql.DB
}
func newManager() *manager {
m := new(manager)
m.dbs = make(map[string]map[string][]*sql.DB)
return m
}
//添加數據庫
func (m *manager) addDB(groupName string, isMaster bool, db *sql.DB) {
dc := "master"
if !isMaster {
dc = "slave"
}
group, ok := m.dbs[groupName]
if !ok {
group = make(map[string][]*sql.DB)
}
if _, ok := group[dc]; ok {
group[dc] = append(group[dc], db)
} else {
group[dc] = []*sql.DB{db}
}
m.dbs[groupName] = group
}
//獲取數據庫
func (m *manager) getDB(names ...string) (*sql.DB, error) {
groupName := defaultGroupName
dc := "master"
if len(names) > 0 {
name := names[0]
segment := strings.Split(name, "::")
groupName = segment[0]
if len(segment) > 1 {
dc = segment[1]
}
}
if dbs, ok := m.dbs[groupName][dc]; ok {
max := len(dbs)
rand.Seed(time.Now().UnixNano())
i := rand.Intn(max)
return dbs[i], nil
}
return nil, fmt.Errorf("DataBase `%s::%s` not found", groupName, dc)
}
//獲取從庫
func (m *manager) getReadDB(names ...string) (*sql.DB, error) {
groupName := defaultGroupName
if len(names) > 0 {
groupName = names[0]
}
name := fmt.Sprintf("%s::%s", groupName, "slave")
return m.getDB(name)
}
創建一個kdb.go文件,實現RegisterDataBase功能
package kdb
import "database/sql"
func RegisterDataBase(kConf KConfig) {
for _, dbConf := range kConf.DBConfigList {
db, err := sql.Open(dbConf.Driver, dbConf.Dsn)
if err != nil {
panic(err)
}
if dbConf.MaxLifetime > 0 {
db.SetConnMaxLifetime(dbConf.MaxLifetime)
}
if dbConf.MaxIdleConns > 0 {
db.SetMaxIdleConns(dbConf.MaxIdleConns)
}
if dbConf.MaxOpenConns > 0 {
db.SetMaxOpenConns(dbConf.MaxOpenConns)
}
if dbConf.Name == "" {
dbConf.Name = defaultGroupName
}
m.addDB(dbConf.Name, dbConf.IsMaster, db)
}
}
第二步: 實現原生SQL查詢
在實現原生SQL查詢之前,我們先增加一個result.go文件,用來出來查詢結果處理,後面將會在這個文件中實現toArrray(), toMap(), toStruct()等功能
type Rows struct {
}
func (rs *Rows) ToArray() {
}
func (rs *Rows) ToMap() {
}
func (rs *Rows) ToStruct(sts interface{}) {
}
具體的實現我們後面再進行補充 我們再增加一個connection.go用於管理DB的connection
package kdb
import (
"context"
"database/sql"
"errors"
"log"
)
type Connection struct {
ctx context.Context
conn *sql.Conn
tx *sql.Tx
name string
}
func newConnection() *Connection {
c := new(Connection)
c.ctx = context.Background()
return c
}
func (c *Connection) WithDB(name string) *Connection {
c.name = name
return c
}
func (c *Connection) WithContext(ctx context.Context) *Connection {
c.ctx = ctx
return c
}
func (c *Connection) Select(query string, bindings []interface{}) *Rows {
rows, err := c.queryRows(query, bindings)
if err != nil {
return &Rows{rs: nil, lastError: err}
}
return &Rows{rs: rows, lastError: err}
}
func (c *Connection) Insert(query string, bindings []interface{}) (int64, error) {
rs, err := c.exec(query, bindings)
if err != nil {
return 0, err
}
return rs.LastInsertId()
}
func (c *Connection) MultiInsert(query string, bindingsArr [][]interface{}) ([]int64, error) {
var stmt *sql.Stmt
var err error
if c.tx != nil {
stmt, err = c.tx.PrepareContext(c.ctx, query)
} else {
var conn *sql.Conn
conn, err = c.getConn()
if err != nil {
return nil, err
}
stmt, err = conn.PrepareContext(c.ctx, query)
}
if err != nil {
return nil, err
}
defer stmt.Close()
lastInsertIds := make([]int64, 0)
for _, bindings := range bindingsArr {
rs, err := stmt.ExecContext(c.ctx, bindings...)
if err != nil {
return nil, err
}
lastInsertId, err := rs.LastInsertId()
if err != nil {
return nil, err
}
lastInsertIds = append(lastInsertIds, lastInsertId)
}
return lastInsertIds, nil
}
func (c *Connection) Update(query string, bindings []interface{}) (int64, error) {
rs, err := c.exec(query, bindings)
if err != nil {
return 0, err
}
return rs.RowsAffected()
}
func (c *Connection) Delete(query string, bindings []interface{}) (int64, error) {
rs, err := c.exec(query, bindings)
if err != nil {
return 0, err
}
return rs.RowsAffected()
}
func (c *Connection) BeginTransaction() error {
if c.tx == nil {
conn, err := c.getConn()
if err != nil {
return err
}
tx, err := conn.BeginTx(c.ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
if err != nil {
return err
}
c.tx = tx
}
return nil
}
func (c *Connection) Commit() error {
if c.tx == nil {
return errors.New("no beginTx")
}
return c.tx.Commit()
}
func (c *Connection) RollBack() error {
if c.tx == nil {
return errors.New("no beginTx")
}
return c.tx.Rollback()
}
func (c *Connection) queryRows(query string, bindings []interface{}) (rows *sql.Rows, err error) {
log.Println("query:", query, "| bindings:", bindings)
if c.tx != nil {
rows, err = c.tx.QueryContext(c.ctx, query, bindings...)
return
}
var conn *sql.Conn
conn, err = c.getConn()
if err != nil {
return nil, err
}
rows, err = conn.QueryContext(c.ctx, query, bindings...)
return
}
func (c *Connection) exec(query string, bindings []interface{}) (rs sql.Result, err error) {
log.Println("exec:", query, "| bindings:", bindings)
if c.tx != nil {
rs, err = c.tx.ExecContext(c.ctx, query, bindings...)
return
}
var conn *sql.Conn
conn, err = c.getConn()
if err != nil {
return nil, err
}
rs, err = conn.ExecContext(c.ctx, query, bindings...)
return
}
func (c *Connection) getConn() (*sql.Conn, error) {
var err error
var db *sql.DB
if c.conn != nil {
return c.conn, nil
}
if c.name != "" {
db, err = m.getDB(c.name)
} else {
db, err = m.getDB()
}
if err != nil {
return nil, err
}
conn, err := db.Conn(c.ctx)
if err != nil {
return nil, err
}
c.conn = conn
return c.conn, nil
}
根據我們期望實現的原生SQL查詢的目標,我們需要在kdb.go裡增加Select, Insert, Update, Delete, BeginTransaction等方法
package kdb
import (
"context"
"database/sql"
)
func RegisterDataBase(kConf KConfig) {
for _, dbConf := range kConf.DBConfigList {
db, err := sql.Open(dbConf.Driver, dbConf.Dsn)
if err != nil {
panic(err)
}
if dbConf.MaxLifetime > 0 {
db.SetConnMaxLifetime(dbConf.MaxLifetime)
}
if dbConf.MaxIdleConns > 0 {
db.SetMaxIdleConns(dbConf.MaxIdleConns)
}
if dbConf.MaxOpenConns > 0 {
db.SetMaxOpenConns(dbConf.MaxOpenConns)
}
if dbConf.Name == "" {
dbConf.Name = defaultGroupName
}
m.addDB(dbConf.Name, dbConf.IsMaster, db)
}
}
func Select(query string, bindings ...interface{}) *Rows {
return newConnection().Select(query, bindings)
}
func Insert(query string, bindings ...interface{}) (LastInsertId int64, err error) {
return newConnection().Insert(query, bindings)
}
func MultiInsert(query string, bindingsArr [][]interface{}) (LastInsertId []int64, err error) {
return newConnection().MultiInsert(query, bindingsArr)
}
func Update(query string, bindings ...interface{}) (RowsAffected int64, err error) {
return newConnection().Update(query, bindings)
}
func Delete(query string, bindings ...interface{}) (RowsAffected int64, err error) {
return newConnection().Delete(query, bindings)
}
func WithDB(name string) *Connection {
return newConnection().WithDB(name)
}
func WithContext(ctx context.Context) *Connection {
return newConnection().WithContext(ctx)
}
func BeginTransaction() (conn *Connection, err error) {
conn = newConnection()
err = conn.BeginTransaction()
if err != nil {
return nil, err
}
return conn, nil
}
至此,原生SQL的操作方式就已經完成了,並且可以通過kdb.WithDB("mysql::master")這樣的方式來選擇指定的DB。接下來我們來實現查詢結果轉換。
第三步: 實現三種返回結果
查詢結果並轉換成我們想要的結構,從本質上來說,首先,我們通過rows.Columns得到查詢語句中會返回的字段都有哪些,知道有哪些字段以後,我們就可以知道需要 傳入到rows.Scan中需要幾個參數,我們先來看看ToArray()的實現
func (r *Rows) ToArray() (data [][]string, err error) {
if r.rs == nil {
return nil, r.lastError
}
defer r.rs.Close()
//獲取查詢的字段
fields, err := r.rs.Columns()
if err != nil {
r.lastError = err
return nil, err
}
data = make([][]string, 0)
num := len(fields)
//根據查詢字段的數量,生成[num]interface{}用於存儲Scan的結果
refs := make([]interface{}, num)
for i := 0; i < num; i++ {
var ref interface{}
refs[i] = &ref
}
for r.rs.Next() {
result := make([]string, len(fields))
if err := r.rs.Scan(refs...); err != nil {
return nil, err
}
for i := range fields {
//把*interface{}轉換成strings返回
if val, err := toString(refs[i]); err == nil {
result[i] = val
} else {
return nil, err
}
}
if err != nil {
r.lastError = err
return nil, err
}
data = append(data, result)
}
return data, nil
}
我們再增加一個convert.go文件,實現toString()的方法
package kdb
import (
"fmt"
"reflect"
"strconv"
"time"
)
//轉換成string
func toString(src interface{}) (dst string, err error) {
inf := reflect.Indirect(reflect.ValueOf(src)).Interface()
if inf == nil {
return "", nil
}
switch v := inf.(type) {
case string:
dst = v
return
case []byte:
dst = string(v)
return
}
val := reflect.ValueOf(inf)
typ := reflect.TypeOf(inf)
switch typ.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
dst = strconv.FormatInt(val.Int(), 10)
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
dst = strconv.FormatUint(val.Uint(), 10)
case reflect.Float32, reflect.Float64:
dst = strconv.FormatFloat(val.Float(), 'f', -1, 64)
case reflect.Bool:
dst = strconv.FormatBool(val.Bool())
case reflect.Complex64, reflect.Complex128:
dst = fmt.Sprintf("%v", val.Complex())
case reflect.Struct:
//time.Time
var timeType time.Time
if typ.ConvertibleTo(reflect.TypeOf(timeType)) {
dst = val.Convert(reflect.TypeOf(timeType)).Interface().(time.Time).Format(time.RFC3339Nano)
} else {
err = fmt.Errorf("unsupported struct type %v", val.Type())
}
default:
err = fmt.Errorf("unsupported struct type %v", val.Type())
}
return
}
再增加toMap的實現,原理跟toArray的一樣的,
func (r *Rows) ToMap() (data []map[string]string, err error) {
if r.rs == nil {
return nil, r.lastError
}
defer r.rs.Close()
fields, err := r.rs.Columns()
if err != nil {
r.lastError = err
return nil, err
}
data = make([]map[string]string, 0)
num := len(fields)
result := make(map[string]string)
refs := make([]interface{}, num)
for i := 0; i < num; i++ {
var ref interface{}
refs[i] = &ref
}
for r.rs.Next() {
if err := r.rs.Scan(refs...); err != nil {
return nil, err
}
for i, field := range fields {
if val, err := toString(refs[i]); err == nil {
result[field] = val
} else {
return nil, err
}
}
data = append(data, result)
}
return data, nil
}
接下來我們要實現toStruct方法,再實現toStruct之前,我們先來梳理一下邏輯。1.傳入參數必須是指針 2.傳入的類型必須是slice 3.傳入的slice類型必須的struct 4.提取struct中tag,並過濾出指定的tag,假設我們定義的tag名為db,則只提取tag為db的內容,例如
type user struct {
Id int `db:"id"`
Name string `db:"name"`
Sex int
}
我們提取的內容為id,name 5.遍歷查詢的字段,再tag中查找是否存在,如果存在則使用結構體成員的變量地址進行Scan。
func (r *Rows) ToStruct(st interface{}) error {
//st->&[]user
//獲取變量的類型,類型為指針
stType := reflect.TypeOf(st)
//獲取變量的值
stVal := reflect.ValueOf(st)
stValInd := reflect.Indirect(stVal)
//1.參數必須是指針
if stType.Kind() != reflect.Ptr {
return fmt.Errorf("the variable type is %v, not a pointer", stType.Kind())
}
//指針指向的類型:slice
stTypeInd := stType.Elem()
//2.傳入的類型必須是slice,slice的成員類型必須是struct
if stTypeInd.Kind() != reflect.Slice || stTypeInd.Elem().Kind() != reflect.Struct {
return fmt.Errorf("the variable type is %v, not a slice struct", stType.Elem().Kind())
}
if r.Rs == nil {
return r.LastError
}
defer r.Rs.Close()
//初始化struct
v := reflect.New(stTypeInd.Elem())
//提取結構體中的tag
tagList, err := extractTagInfo(v)
if err != nil {
return err
}
fields, err := r.Rs.Columns()
if err != nil {
r.LastError = err
return err
}
refs := make([]interface{}, len(fields))
for i, field := range fields {
//如果對應的字段在結構體中有映射,則使用結構體成員變量的地址
if f, ok := tagList[field]; ok {
refs[i] = f.Addr().Interface()
} else {
refs[i] = new(interface{})
}
}
for r.Rs.Next() {
if err := r.Rs.Scan(refs...); err != nil {
return err
}
stValInd = reflect.Append(stValInd, v.Elem())
}
stVal.Elem().Set(stValInd)
return nil
}
我們再convert.go裡增加extractTagInfo的實現
//提取tag信息
func extractTagInfo(st reflect.Value) (tagList map[string]reflect.Value, err error) {
stVal := reflect.Indirect(st)
if stVal.Kind() != reflect.Struct {
return nil, fmt.Errorf("the variable type is %v, not a struct", stVal.Kind())
}
tagList = make(map[string]reflect.Value)
for i := 0; i < stVal.NumField(); i++ {
//獲取結構體成員
v := stVal.Field(i)
if v.Kind() == reflect.Ptr {
//如果沒有初始化,則需要先初始化
if v.IsNil() {
var typ reflect.Type
if v.Type().Kind() == reflect.Ptr {
typ = v.Type().Elem()
} else {
typ = v.Type()
}
vv := reflect.New(typ)
v.Set(vv)
}
//如果是結構體指針,則在進行提取
if v.Elem().Kind() == reflect.Struct {
t, err := extractTagInfo(v.Elem())
if err != nil {
return nil, err
}
for k, ptr := range t {
if _, ok := tagList[k]; ok {
return nil, fmt.Errorf("%s:%s is exists", "db", k)
}
tagList[k] = ptr
}
}
} else if v.Kind() == reflect.Map && v.IsNil() {
//如果是map類型,並且沒有初始化,則需要初始化一下
v.Set(reflect.MakeMap(v.Type()))
} else if v.Kind() == reflect.Struct {
var ignore bool
//以下的類型,會再scan的執行轉換,所以不需要二次處理
switch v.Interface().(type) {
case time.Time:
ignore = true
case sql.NullTime:
ignore = true
case sql.NullString:
ignore = true
case sql.NullBool:
ignore = true
case sql.NullInt64:
ignore = true
case sql.NullInt32:
ignore = true
case sql.NullFloat64:
ignore = true
}
if !ignore {
t, err := extractTagInfo(v)
if err != nil {
return nil, err
}
for k, ptr := range t {
if _, ok := tagList[k]; ok {
return nil, fmt.Errorf("%s:%s is exists", "db", k)
}
tagList[k] = ptr
}
}
}
tagName := stVal.Type().Field(i).Tag.Get("db")
if tagName != "" {
//tag內容通過";"進行分割
attr := strings.Split(tagName, ";")
column := attr[0]
if _, ok := tagList[column]; ok {
return nil, fmt.Errorf("%s:%s is exists", "db", tagName)
}
//字段對應結構體成員地址
tagList[column] = v
}
}
return
}
至此,我們支持了ToArray(),ToMap(),ToStruct(),我們還支持結構體以下方式
type user struct {
Id int `db:"id"`
Name string `db:"name"`
Area areaInfo
}
type areaInfo struct {
province string `db:"province"`
city string `db:"city"`
}
如果你不需要鏈式操作,那麼到這裡就已經夠了。
第四步: 打造SQL構造器和解析器
我們先創建builder.go,grammar.go用於實現構造器和解析器 builder.go
type Builder struct {
table string
conn *Connection
grammar *Grammar
}
func newBuilder(conn *Connection, grammar *Grammar) *Builder {
b := new(Builder)
b.conn = conn
b.grammar = grammar
return b
}
func (b *Builder) Table(table string) *Builder {
b.table = table
return b
}
grammar.go
type Grammar struct {
}
func NewGrammar() *Grammar {
return new(Grammar)
}
鏈式操作的起始都是以Table開始,所以我們再connection.go和kdb.go增加Table的操作 connection.go
func (c *Connection) Table(table string) *Builder {
return c.query().Table(table)
}
func (c *Connection) query() *Builder {
g := NewGrammar()
b := newBuilder(c, g)
return b
}
kdb.go
func Table(table string) *Builder {
return newConnection().Table(table)
}
Ok,現在我們可以通過kdb.Table("user")進行操作了。在實現Builder和Grammar之前,我們先來分析一下簡單的SQL語句
Select * From User Where id = 1;
我們可以把上面的語句拆分成
1.SQL類型:Select/Insert/Update/Delete 2.字段:"*"3.表:"FROM User" 4.條件:"Where id = 1"
所以需要在builder.go中增加Select/Insert/Update/Delete的方法,並且需要存儲table,字段,和where條件,我們原生的SQL是採用參數綁定的方式,所以我們還需要增加 addBindings和getBingding是方法 builder.go的實現
type Builder struct {
table string
conn *Connection
grammar *Grammar
bindings map[string][]interface{}
columns []string
wheres []where
}
type where struct {
typ string //類型,用來表示where的種類
column interface{} //字段
operator string //操作符
value interface{} //值
glue string //連接符
}
func newBuilder(conn *Connection, grammar *Grammar) *Builder {
b := new(Builder)
b.conn = conn
b.grammar = grammar
b.bindings = make(map[string][]interface{})
return b
}
func (b *Builder) Table(table string) *Builder {
b.table = table
return b
}
func (b *Builder) Select(columns ...string) *Builder {
if len(columns) == 0 {
columns = append(columns, "*")
}
b.columns = columns
return b
}
func (b *Builder) Where(column interface{}, args ...interface{}) *Builder {
if len(args) == 0 {
return b.WhereIsNull(column)
}
w := new(where)
w.column = column
w.glue = "and"
w.typ = "basic"
switch len(args) {
case 1:
w.operator = "="
w.value = args[0]
case 2:
w.operator = args[0].(string)
w.value = args[1]
case 3:
w.operator = args[0].(string)
w.value = args[1]
w.glue = args[2].(string)
case 4:
w.operator = args[0].(string)
w.value = args[1]
w.glue = args[2].(string)
w.typ = args[3].(string)
}
b.addBinding("where", []interface{}{w.value})
b.wheres = append(b.wheres, *w)
return b
}
func (b *Builder) WhereIsNull(column interface{}) *Builder {
w := new(where)
w.column = column
w.glue = "and"
w.typ = "null"
w.operator = "is"
w.value = "null"
b.wheres = append(b.wheres, *w)
return b
}
func (b *Builder) Get(columns ...string) *Rows {
if len(columns) > 0 {
b.Select(columns...)
}
return b.runSelect()
}
func (b *Builder) addBinding(typ string, value []interface{}) {
if _, ok := b.bindings[typ]; ok {
b.bindings[typ] = append(b.bindings[typ], value...)
} else {
b.bindings[typ] = value
}
}
func (b *Builder) getBindings() (bindings []interface{}) {
bindings = make([]interface{}, 0)
if v, ok := b.bindings["where"]; ok {
bindings = append(bindings, v...)
}
return
}
func (b *Builder) toSQL() string {
return b.grammar.compileSelect(b)
}
func (b *Builder) runSelect() *Rows {
return b.conn.Select(b.toSQL(), b.getBindings())
}
實現grammer用來分析構造器中的select,columns,from,wheres grammer.go
package kdb
import (
"fmt"
"strings"
)
type Grammar struct {
}
func NewGrammar() *Grammar {
return new(Grammar)
}
func (g *Grammar) compileSelect(b *Builder) string {
if len(b.columns) == 0 {
b.columns = []string{"*"}
}
return fmt.Sprintf("select %s", strings.TrimSpace(strings.Join(g.compileComponents(b), " ")))
}
func (g *Grammar) compileComponents(b *Builder) []string {
sql := make([]string, 0)
if len(b.columns) > 0 {
sql = append(sql, g.compileColumns(b))
}
if b.table != "" {
sql = append(sql, g.compileFrom(b))
}
if len(b.wheres) > 0 {
whereSql := g.compileWheres(b)
if whereSql != "" {
sql = append(sql, whereSql)
}
}
return sql
}
func (g *Grammar) compileColumns(b *Builder) string {
return g.wrapColumn(b.columns...)
}
func (g *Grammar) compileFrom(b *Builder) string {
return fmt.Sprintf("from %s", g.wrapTable(b.table))
}
func (g *Grammar) compileWheres(b *Builder) string {
var sql string
for k, w := range b.wheres {
if k == 0 {
w.glue = ""
}
switch w.typ {
case "basic":
sql = fmt.Sprintf("%s %s %s %s %s", strings.TrimSpace(sql), w.glue, g.wrapColumn(w.column.(string)), w.operator, "?")
case "null":
sql = fmt.Sprintf("%s %s %s %s %s", strings.TrimSpace(sql), w.glue, g.wrapColumn(w.column.(string)), w.operator, w.value)
}
}
return fmt.Sprintf("where %s", strings.TrimSpace(sql))
}
func (g *Grammar) wrapTable(table string) string {
return fmt.Sprintf("%s%s", "", table)
}
func (g *Grammar) wrapColumn(columns ...string) string {
for i, column := range columns {
segments := strings.Split(column, ".")
if len(segments) > 1 {
segments[0] = g.wrapTable(segments[0])
if segments[1] != "*" && !strings.Contains(segments[0], "->") {
segments[1] = fmt.Sprintf("`%s`", segments[1])
}
} else {
if segments[0] != "*" && !strings.Contains(segments[0], "->") {
segments[0] = fmt.Sprintf("`%s`", segments[0])
}
}
column = strings.Join(segments, ".")
columns[i] = column
}
return fmt.Sprintf("%s", strings.Join(columns, ", "))
}
現在我們簡單SQL的構造器和解析器就已經實現了。剩下的就是去實現update,insert,delete,join操作,union操作,whereIn等內容了,如果大家感興趣,可以到 https://github.com/nopsky/kdb 查看完整版。
希望本文對你所有幫助,由於本人才疏學淺,如果有什麼不對的地方,歡迎大家指出。
閱讀更多 Go語言中文網 的文章