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