使用名称作为参数在Oracle、Postgre和Mysql中。
type (
TisqlMode int32
TisqlAt struct {
ParamName string
Pos int
ParamVal any
}
Tisql struct {
Sql string
ResultSql string
DB *sql.DB
Params map[string]any
At []TisqlAt
}
)
const (
Oracle TisqlMode = iota
Mysql
Postgre
)
var ParamReplacingMode TisqlMode = -1
func indexAt(pStr, pSubStr string, pFrom ...int) int {
pos := 0
if len(pFrom) > 0 {
pos = pFrom[0]
}
if pos >= len(pStr) {
return -1
}
if pos < 0 {
pos = 0
}
idx := strings.Index(pStr[pos:], pSubStr)
if idx > -1 {
idx += pos
}
return idx
}
func InitSql(db *sql.DB, sql string) *Tisql {
if ParamReplacingMode < 0 {
_, err := db.Exec("?")
if err != nil {
s := strings.ToLower(fmt.Sprint(err))
if indexAt(s, "mysql") > 0 {
ParamReplacingMode = 1
} else {
ParamReplacingMode = 0
}
}
}
var isql Tisql
isql.Sql = sql
isql.DB = db
isql.Params = make(map[string]any)
return &isql
}
func AddParam(isql *Tisql, pParam string, pValue any) bool {
pParam = strings.TrimSpace(pParam)
if len(pParam) > 1 {
isql.Params[pParam] = pValue
return true
}
return false
}
func Params(isql *Tisql, pParam ...any) (added int) {
c := len(pParam)
if c%2 == 1 {
c = c - 1
}
cnt, i, s := 0, 0, ""
for i < c {
s = strings.TrimSpace(pParam[i].(string))
if len(s) > 1 {
isql.Params[s] = pParam[i+1]
cnt++
}
i += 2
}
return cnt
}
func paramOrder(isql *Tisql, pCheckParamCount bool) error {
var at TisqlAt
isql.ResultSql = isql.Sql
t := ""
b := strings.ToLower(isql.Sql) + " "
mMode := ParamReplacingMode
var p, p1, p2 int
for name, v := range isql.Params {
p1 = 0
for p1 >= 0 {
p = indexAt(b, strings.ToLower(name), p1)
if p < 0 {
p1 = -1
continue
} else {
p2 = p + len(name)
t = b[p2 : p2+1]
if indexAt(" :,;!?%$<>^*+-/()[]{}=|'`\"\r\n\t", t) < 0 {
p1 = p + 1
continue
}
p1 = -1
}
}
if p >= 0 {
at.Pos = p
at.ParamVal = v
at.ParamName = name
isql.At = append(isql.At, at)
}
}
if pCheckParamCount && len(isql.At) != len(isql.Params) {
return fmt.Errorf("Different count of params %d / %d", len(isql.At), len(isql.Params))
}
if len(isql.At) > 1 {
sort.Slice(isql.At,
func(i, j int) bool {
return isql.At[i].Pos < isql.At[j].Pos
})
}
mLen := len(isql.Sql)
switch mMode {
case 1:
{
p1, p2, s := 0, 0, ""
for _, at := range isql.At {
p2 = at.Pos
if p2 >= 0 && p2 <= mLen {
if p2 > p1 {
s += isql.Sql[p1:p2] + "?"
}
p1 = p2 + len(at.ParamName)
}
}
if p1 < len(isql.Sql) {
s += isql.Sql[p1:len(isql.Sql)]
}
isql.ResultSql = s
}
case 2:
{
p1, p2, s := 0, 0, ""
for i, at := range isql.At {
p2 = at.Pos
if p2 >= 0 && p2 <= mLen {
if p2 > p1 {
s += isql.Sql[p1:p2] + "$" + fmt.Sprint(i+1)
}
p1 = p2 + len(at.ParamName)
}
}
if p1 < len(isql.Sql) {
s += isql.Sql[p1:len(isql.Sql)]
}
isql.ResultSql = s
}
}
return nil
}
func ParamsStr(isql *Tisql) string {
s := ""
for i, at := range isql.At {
s += "[" + fmt.Sprint(i+1) + ". " + at.ParamName + "=\"" + fmt.Sprint(at.ParamVal) + "\"]"
}
return s
}
func SqlStr(isql *Tisql) string {
s := " SQL:[" + isql.ResultSql + "]"
if len(isql.At) > 0 {
s += " Params:" + ParamsStr(isql)
}
return s
}
func SqlExec(isql *Tisql, opt ...bool) (sql.Result, error) {
checkParamCount := false
if len(opt) > 0 {
checkParamCount = opt[0]
}
err := paramOrder(isql, checkParamCount)
if err != nil {
return nil, err
}
mLen := len(isql.At)
mVal := make([]any, mLen)
for i := range mVal {
mVal[i] = isql.At[i].ParamVal
}
return isql.DB.Exec(isql.ResultSql, mVal...)
}
func SqlQuery(isql *Tisql, opt ...bool) (*sql.Rows, error) {
checkParamCount := false
if len(opt) > 0 {
checkParamCount = opt[0]
}
err := paramOrder(isql, checkParamCount)
if err != nil {
return nil, err
}
mLen := len(isql.At)
mVal := make([]any, mLen)
for i := range mVal {
mVal[i] = isql.At[i].ParamVal
}
return isql.DB.Query(isql.ResultSql, mVal...)
}