gitee.com/h79/goutils@v1.22.10/dao/wrapper/sql.go (about)

     1  package wrapper
     2  
     3  import (
     4  	commonoption "gitee.com/h79/goutils/common/option"
     5  	"gitee.com/h79/goutils/dao/option"
     6  	"strings"
     7  )
     8  
     9  var _ IFrom = (*SQL)(nil)
    10  
    11  type SQL struct {
    12  	From     IFrom
    13  	Selector ISelect
    14  	Where    IQuery
    15  	Join     IBuilder
    16  	GroupBy  IBuilder
    17  	OrderBy  IBuilder
    18  	Limit    ILimit
    19  	Opts     []commonoption.Option
    20  }
    21  
    22  func (s *SQL) SetAs(as string) {
    23  	if s.From == nil {
    24  		panic("from is empty")
    25  	}
    26  	s.From.SetAs(as)
    27  }
    28  
    29  func (s *SQL) Is() bool {
    30  	return s.From != nil
    31  }
    32  
    33  // NewSQL From 表名
    34  func NewSQL(from IFrom, opts ...commonoption.Option) *SQL {
    35  	return &SQL{
    36  		From: from,
    37  		Opts: opts,
    38  	}
    39  }
    40  
    41  func (s *SQL) WithFrom(from IFrom) *SQL {
    42  	s.From = from
    43  	return s
    44  }
    45  
    46  func (s *SQL) WithSelector(sel ISelect) *SQL {
    47  	s.Selector = sel
    48  	return s
    49  }
    50  
    51  func (s *SQL) WithJoin(join IBuilder) *SQL {
    52  	s.Join = join
    53  	return s
    54  }
    55  
    56  func (s *SQL) WithWhere(where IQuery) *SQL {
    57  	s.Where = where
    58  	return s
    59  }
    60  
    61  func (s *SQL) WithGroupBy(by IBuilder) *SQL {
    62  	s.GroupBy = by
    63  	return s
    64  }
    65  
    66  func (s *SQL) WithLimit(limit ILimit) *SQL {
    67  	s.Limit = limit
    68  	return s
    69  }
    70  
    71  func (s *SQL) WithOrderBy(by IBuilder) *SQL {
    72  	s.OrderBy = by
    73  	return s
    74  }
    75  
    76  // AsSubQuery 作为子查询使用
    77  func (s *SQL) AsSubQuery(as string) *SQL {
    78  	s.Opts = append(s.Opts, option.WithSubQuerySQL(as))
    79  	return s
    80  }
    81  
    82  // AsChild 作为子源来使用
    83  func (s *SQL) AsChild(as string, opts ...commonoption.Option) From {
    84  	return From{From: s.Build(opts...), As: as}
    85  }
    86  
    87  func (s *SQL) Build(opts ...commonoption.Option) string {
    88  	opts = append(opts, option.WithFullSQL())
    89  	return s.BuildOpt(opts...)
    90  }
    91  
    92  func (s *SQL) BuildOpt(opts ...commonoption.Option) string {
    93  	opts = append(opts, s.Opts...)
    94  	as, sq := option.SubQueryExist(opts...)
    95  	builder := strings.Builder{}
    96  
    97  	if sq {
    98  		builder.WriteByte('(')
    99  	}
   100  	if s.Selector != nil {
   101  		builder.WriteString(s.Selector.Build(opts...))
   102  	}
   103  	if s.From != nil {
   104  		builder.WriteString(s.From.Build(opts...))
   105  	}
   106  	if s.Join != nil {
   107  		builder.WriteString(s.Join.Build(opts...))
   108  	}
   109  	if s.Where != nil {
   110  		builder.WriteString(s.Where.Build(opts...))
   111  	}
   112  	if s.GroupBy != nil {
   113  		builder.WriteString(s.GroupBy.Build(opts...))
   114  	}
   115  	// order by ...limit...
   116  	if s.OrderBy != nil {
   117  		builder.WriteString(s.OrderBy.Build(opts...))
   118  	}
   119  	if s.Limit != nil {
   120  		builder.WriteString(s.Limit.Build(opts...))
   121  	}
   122  	if sq {
   123  		builder.WriteByte(')')
   124  		if as != "" {
   125  			builder.WriteString(" AS ")
   126  			builder.WriteString(as)
   127  		}
   128  	}
   129  	return builder.String()
   130  }
   131  
   132  func SQLFunc(flag int) string {
   133  	if flag <= KSQLFunc {
   134  		return ""
   135  	}
   136  	if fn, ok := sqlFuncMap[flag]; ok {
   137  		return fn
   138  	}
   139  	return ""
   140  }
   141  
   142  const (
   143  	KSQLFunc = iota
   144  	KSQLCount
   145  	KSQLMax
   146  	KSQLMin
   147  	KSQLWeek
   148  	KSQLYear
   149  	KSQLMonth
   150  	KSQLSum
   151  	KSQLAbs
   152  	KSQLFloor
   153  	KSQLGreatest
   154  	KSQLLeast
   155  	KSQLUpper
   156  	KSQLLower
   157  	KSQLTrim
   158  	KSQLRTrim
   159  	KSQLLTrim
   160  	KSQLCharLength
   161  	KSQLConcat
   162  	KSQLReverse
   163  )
   164  
   165  var (
   166  	sqlFuncMap = map[int]string{
   167  		KSQLCount:      "COUNT",
   168  		KSQLMax:        "MAX",
   169  		KSQLMin:        "MIN",
   170  		KSQLWeek:       "WEEK",
   171  		KSQLYear:       "YEAR",
   172  		KSQLMonth:      "MONTH",
   173  		KSQLSum:        "SUM",
   174  		KSQLAbs:        "ABS",
   175  		KSQLFloor:      "FLOOR", //返回小于或等于 x 的最大整数
   176  		KSQLGreatest:   "GREATEST",
   177  		KSQLLeast:      "LEAST",
   178  		KSQLUpper:      "UPPER", //字符串大写
   179  		KSQLLower:      "LOWER",
   180  		KSQLTrim:       "TRIM",
   181  		KSQLRTrim:      "RTRIM",
   182  		KSQLLTrim:      "LTRIM",
   183  		KSQLCharLength: "CHAR_LENGTH",
   184  		KSQLConcat:     "CONCAT",
   185  		KSQLReverse:    "REVERSE", //将字符串s的顺序反过来
   186  	}
   187  )
   188  
   189  func AddSQLFunc(flag int, fn string) {
   190  	if _, ok := sqlFuncMap[flag]; ok {
   191  		return
   192  	}
   193  	sqlFuncMap[flag] = fn
   194  }