1// Copyright 2016 The Xorm Authors. All rights reserved. 2// Use of this source code is governed by a BSD-style 3// license that can be found in the LICENSE file. 4 5package builder 6 7import ( 8 sql2 "database/sql" 9 "fmt" 10) 11 12type optype byte 13 14const ( 15 condType optype = iota // only conditions 16 selectType // select 17 insertType // insert 18 updateType // update 19 deleteType // delete 20 setOpType // set operation 21) 22 23// all databasees 24const ( 25 POSTGRES = "postgres" 26 SQLITE = "sqlite3" 27 MYSQL = "mysql" 28 MSSQL = "mssql" 29 ORACLE = "oracle" 30 31 UNION = "union" 32 INTERSECT = "intersect" 33 EXCEPT = "except" 34) 35 36type join struct { 37 joinType string 38 joinTable interface{} 39 joinCond Cond 40} 41 42type setOp struct { 43 opType string 44 distinctType string 45 builder *Builder 46} 47 48type limit struct { 49 limitN int 50 offset int 51} 52 53// Builder describes a SQL statement 54type Builder struct { 55 optype 56 dialect string 57 isNested bool 58 into string 59 from string 60 subQuery *Builder 61 cond Cond 62 selects []string 63 joins []join 64 setOps []setOp 65 limitation *limit 66 insertCols []string 67 insertVals []interface{} 68 updates []UpdateCond 69 orderBy string 70 groupBy string 71 having string 72} 73 74// Dialect sets the db dialect of Builder. 75func Dialect(dialect string) *Builder { 76 builder := &Builder{cond: NewCond(), dialect: dialect} 77 return builder 78} 79 80// MySQL is shortcut of Dialect(MySQL) 81func MySQL() *Builder { 82 return Dialect(MYSQL) 83} 84 85// MsSQL is shortcut of Dialect(MsSQL) 86func MsSQL() *Builder { 87 return Dialect(MSSQL) 88} 89 90// Oracle is shortcut of Dialect(Oracle) 91func Oracle() *Builder { 92 return Dialect(ORACLE) 93} 94 95// Postgres is shortcut of Dialect(Postgres) 96func Postgres() *Builder { 97 return Dialect(POSTGRES) 98} 99 100// SQLite is shortcut of Dialect(SQLITE) 101func SQLite() *Builder { 102 return Dialect(SQLITE) 103} 104 105// Where sets where SQL 106func (b *Builder) Where(cond Cond) *Builder { 107 if b.cond.IsValid() { 108 b.cond = b.cond.And(cond) 109 } else { 110 b.cond = cond 111 } 112 return b 113} 114 115// From sets from subject(can be a table name in string or a builder pointer) and its alias 116func (b *Builder) From(subject interface{}, alias ...string) *Builder { 117 switch subject.(type) { 118 case *Builder: 119 b.subQuery = subject.(*Builder) 120 121 if len(alias) > 0 { 122 b.from = alias[0] 123 } else { 124 b.isNested = true 125 } 126 case string: 127 b.from = subject.(string) 128 129 if len(alias) > 0 { 130 b.from = b.from + " " + alias[0] 131 } 132 } 133 134 return b 135} 136 137// TableName returns the table name 138func (b *Builder) TableName() string { 139 if b.optype == insertType { 140 return b.into 141 } 142 return b.from 143} 144 145// Into sets insert table name 146func (b *Builder) Into(tableName string) *Builder { 147 b.into = tableName 148 return b 149} 150 151// Union sets union conditions 152func (b *Builder) Union(distinctType string, cond *Builder) *Builder { 153 return b.setOperation(UNION, distinctType, cond) 154} 155 156// Intersect sets intersect conditions 157func (b *Builder) Intersect(distinctType string, cond *Builder) *Builder { 158 return b.setOperation(INTERSECT, distinctType, cond) 159} 160 161// Except sets except conditions 162func (b *Builder) Except(distinctType string, cond *Builder) *Builder { 163 return b.setOperation(EXCEPT, distinctType, cond) 164} 165 166func (b *Builder) setOperation(opType, distinctType string, cond *Builder) *Builder { 167 168 var builder *Builder 169 if b.optype != setOpType { 170 builder = &Builder{cond: NewCond()} 171 builder.optype = setOpType 172 builder.dialect = b.dialect 173 builder.selects = b.selects 174 175 currentSetOps := b.setOps 176 // erase sub setOps (actually append to new Builder.unions) 177 b.setOps = nil 178 179 for e := range currentSetOps { 180 currentSetOps[e].builder.dialect = b.dialect 181 } 182 183 builder.setOps = append(append(builder.setOps, setOp{opType, "", b}), currentSetOps...) 184 } else { 185 builder = b 186 } 187 188 if cond != nil { 189 if cond.dialect == "" && builder.dialect != "" { 190 cond.dialect = builder.dialect 191 } 192 193 builder.setOps = append(builder.setOps, setOp{opType, distinctType, cond}) 194 } 195 196 return builder 197} 198 199// Limit sets limitN condition 200func (b *Builder) Limit(limitN int, offset ...int) *Builder { 201 b.limitation = &limit{limitN: limitN} 202 203 if len(offset) > 0 { 204 b.limitation.offset = offset[0] 205 } 206 207 return b 208} 209 210// Select sets select SQL 211func (b *Builder) Select(cols ...string) *Builder { 212 b.selects = cols 213 if b.optype == condType { 214 b.optype = selectType 215 } 216 return b 217} 218 219// And sets AND condition 220func (b *Builder) And(cond Cond) *Builder { 221 b.cond = And(b.cond, cond) 222 return b 223} 224 225// Or sets OR condition 226func (b *Builder) Or(cond Cond) *Builder { 227 b.cond = Or(b.cond, cond) 228 return b 229} 230 231// Update sets update SQL 232func (b *Builder) Update(updates ...Cond) *Builder { 233 b.updates = make([]UpdateCond, 0, len(updates)) 234 for _, update := range updates { 235 if u, ok := update.(UpdateCond); ok && u.IsValid() { 236 b.updates = append(b.updates, u) 237 } 238 } 239 b.optype = updateType 240 return b 241} 242 243// Delete sets delete SQL 244func (b *Builder) Delete(conds ...Cond) *Builder { 245 b.cond = b.cond.And(conds...) 246 b.optype = deleteType 247 return b 248} 249 250// WriteTo implements Writer interface 251func (b *Builder) WriteTo(w Writer) error { 252 switch b.optype { 253 /*case condType: 254 return b.cond.WriteTo(w)*/ 255 case selectType: 256 return b.selectWriteTo(w) 257 case insertType: 258 return b.insertWriteTo(w) 259 case updateType: 260 return b.updateWriteTo(w) 261 case deleteType: 262 return b.deleteWriteTo(w) 263 case setOpType: 264 return b.setOpWriteTo(w) 265 } 266 267 return ErrNotSupportType 268} 269 270// ToSQL convert a builder to SQL and args 271func (b *Builder) ToSQL() (string, []interface{}, error) { 272 w := NewWriter() 273 if err := b.WriteTo(w); err != nil { 274 return "", nil, err 275 } 276 277 // in case of sql.NamedArg in args 278 for e := range w.args { 279 if namedArg, ok := w.args[e].(sql2.NamedArg); ok { 280 w.args[e] = namedArg.Value 281 } 282 } 283 284 var sql = w.String() 285 var err error 286 287 switch b.dialect { 288 case ORACLE, MSSQL: 289 // This is for compatibility with different sql drivers 290 for e := range w.args { 291 w.args[e] = sql2.Named(fmt.Sprintf("p%d", e+1), w.args[e]) 292 } 293 294 var prefix string 295 if b.dialect == ORACLE { 296 prefix = ":p" 297 } else { 298 prefix = "@p" 299 } 300 301 if sql, err = ConvertPlaceholder(sql, prefix); err != nil { 302 return "", nil, err 303 } 304 case POSTGRES: 305 if sql, err = ConvertPlaceholder(sql, "$"); err != nil { 306 return "", nil, err 307 } 308 } 309 310 return sql, w.args, nil 311} 312 313// ToBoundSQL generated a bound SQL string 314func (b *Builder) ToBoundSQL() (string, error) { 315 w := NewWriter() 316 if err := b.WriteTo(w); err != nil { 317 return "", err 318 } 319 320 return ConvertToBoundSQL(w.String(), w.args) 321} 322