github.com/sereiner/library@v0.0.0-20200518095232-1fa3e640cc5f/db/tpl/tpl.analyze.go (about)

     1  package tpl
     2  
     3  import (
     4  	"fmt"
     5  	"regexp"
     6  	"strings"
     7  )
     8  
     9  func isNil(input interface{}) bool {
    10  	return input == nil || fmt.Sprintf("%v", input) == ""
    11  }
    12  
    13  type tplCache struct {
    14  	sql    string
    15  	params []interface{}
    16  	names  []string
    17  }
    18  
    19  //AnalyzeTPLFromCache 从缓存中获取已解析的SQL语句
    20  func AnalyzeTPLFromCache(name string, tpl string, input map[string]interface{}, prefix func() string) (sql string, params []interface{}) {
    21  	sql, params, _ = AnalyzeTPL(tpl, input, prefix)
    22  	return
    23  	/*key := fmt.Sprintf("%s_%s", name, tpl)
    24  	b, cache, _ := tplCaches.SetIfAbsentCb(key, func(i ...interface{}) (interface{}, error) {
    25  		sql, params, names := AnalyzeTPL(tpl, input, prefix)
    26  		return &tplCache{sql: sql, params: params, names: names}, nil
    27  	})
    28  	value := cache.(*tplCache)
    29  	if b {
    30  		return value.sql, value.params
    31  	}
    32  	params = make([]interface{}, 0, len(value.names))
    33  	for _, v := range value.names {
    34  		va := input[v]
    35  		if !isNil(va) {
    36  			params = append(params, va)
    37  		} else {
    38  			params = append(params, nil)
    39  		}
    40  	}
    41  	return value.sql, params*/
    42  }
    43  
    44  // AnalyzeTPL 解析模板内容，并返回解析后的SQL语句，入输入参数
    45  // @ 表达式，替换为参数化字符如: :1,:2,:3
    46  // # 表达式，替换为指定值，值为空时返回 NULL
    47  // $ 表达式，替换为指定值，值为空时返回 ""
    48  // [ 大于等于表达式,值为空时返回"",否则返回: and name >= value
    49  // ] 小于等于表达式,值为空时返回"",否则返回: and name <= value
    50  // ~ 表达式，检查值，值为空时返回"",否则返回: , name=value
    51  // & 条件表达式，检查值，值为空时返回"",否则返回: and name=value
    52  // | 条件表达式，检查值，值为空时返回"", 否则返回: or name=value
    53  func AnalyzeTPL(tpl string, input map[string]interface{}, prefix func() string) (sql string, params []interface{}, names []string) {
    54  	params = make([]interface{}, 0)
    55  	names = make([]string, 0)
    56  	defer func() {
    57  		sql = strings.Replace(strings.Replace(strings.Replace(sql, "  ", " ", -1), "where and ", "where ", -1), "where or ", "where ", -1)
    58  		sql = strings.Replace(strings.Replace(sql, "WHERE and ", "WHERE ", -1), "WHERE or ", "WHERE ", -1)
    59  	}()
    60  	word, _ := regexp.Compile(`[\\]?[@|#|&|~|\||!|\$|\?\[\]]\w{0,}[\.]?\w+`)
    61  	//@变量, 将数据放入params中
    62  	sql = word.ReplaceAllStringFunc(tpl, func(s string) string {
    63  		fullKey := s[1:]
    64  		key := s[1:]
    65  		name := s[1:]
    66  		if strings.Index(fullKey, ".") > 0 {
    67  			name = strings.Split(fullKey, ".")[1]
    68  		}
    69  		pre := s[:1]
    70  		value := input[name]
    71  		switch pre {
    72  		case "@":
    73  			if !isNil(value) {
    74  				names = append(names, key)
    75  				params = append(params, value)
    76  			} else {
    77  				names = append(names, key)
    78  				params = append(params, nil)
    79  			}
    80  			return prefix()
    81  		case "#":
    82  			if !isNil(value) {
    83  				return fmt.Sprintf("%v", value)
    84  			}
    85  			return "NULL"
    86  		case "?":
    87  			if !isNil(value) {
    88  				names = append(names, key)
    89  				params = append(params, value)
    90  				return "and " + key + " like '%" + prefix() + "%'"
    91  			}
    92  			return ""
    93  		case "[":
    94  			if !isNil(value) {
    95  				names = append(names, key)
    96  				params = append(params, value)
    97  				return fmt.Sprintf("and %s>=%s", key, prefix())
    98  			}
    99  			return ""
   100  		case "]":
   101  			if !isNil(value) {
   102  				names = append(names, key)
   103  				params = append(params, value)
   104  				return fmt.Sprintf("and %s<=%s", key, prefix())
   105  			}
   106  			return ""
   107  		case "$":
   108  			if !isNil(value) {
   109  				return fmt.Sprintf("%v", value)
   110  			}
   111  			return ""
   112  		case "&":
   113  			if !isNil(value) {
   114  				names = append(names, key)
   115  				params = append(params, value)
   116  				return fmt.Sprintf("and %s=%s", key, prefix())
   117  			}
   118  			return ""
   119  		case "|":
   120  			if !isNil(value) {
   121  				names = append(names, key)
   122  				params = append(params, value)
   123  				return fmt.Sprintf("or %s=%s", key, prefix())
   124  			}
   125  			return ""
   126  		case "~":
   127  			if !isNil(value) {
   128  				names = append(names, key)
   129  				params = append(params, value)
   130  				return fmt.Sprintf(",%s=%s", key, prefix())
   131  			}
   132  			return ""
   133  		default:
   134  			return s
   135  		}
   136  	})
   137  
   138  	word2, _ := regexp.Compile(`[\\][@|#|&|~|\||!|\$|\?|>=|<=]`)
   139  	//@变量, 将数据放入params中
   140  	sql = word2.ReplaceAllStringFunc(sql, func(s string) string {
   141  		return s[1:]
   142  	})
   143  	return
   144  }