1package gorp
2
3import (
4	"errors"
5	"fmt"
6	"reflect"
7	"strings"
8)
9
10// The Dialect interface encapsulates behaviors that differ across
11// SQL databases.  At present the Dialect is only used by CreateTables()
12// but this could change in the future
13type Dialect interface {
14
15	// adds a suffix to any query, usually ";"
16	QuerySuffix() string
17
18	// ToSqlType returns the SQL column type to use when creating a
19	// table of the given Go Type.  maxsize can be used to switch based on
20	// size.  For example, in MySQL []byte could map to BLOB, MEDIUMBLOB,
21	// or LONGBLOB depending on the maxsize
22	ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string
23
24	// string to append to primary key column definitions
25	AutoIncrStr() string
26
27	// string to bind autoincrement columns to. Empty string will
28	// remove reference to those columns in the INSERT statement.
29	AutoIncrBindValue() string
30
31	AutoIncrInsertSuffix(col *ColumnMap) string
32
33	// string to append to "create table" statement for vendor specific
34	// table attributes
35	CreateTableSuffix() string
36
37	// string to truncate tables
38	TruncateClause() string
39
40	// bind variable string to use when forming SQL statements
41	// in many dbs it is "?", but Postgres appears to use $1
42	//
43	// i is a zero based index of the bind variable in this statement
44	//
45	BindVar(i int) string
46
47	// Handles quoting of a field name to ensure that it doesn't raise any
48	// SQL parsing exceptions by using a reserved word as a field name.
49	QuoteField(field string) string
50
51	// Handles building up of a schema.database string that is compatible with
52	// the given dialect
53	//
54	// schema - The schema that <table> lives in
55	// table - The table name
56	QuotedTableForQuery(schema string, table string) string
57
58	// Existance clause for table creation / deletion
59	IfSchemaNotExists(command, schema string) string
60	IfTableExists(command, schema, table string) string
61	IfTableNotExists(command, schema, table string) string
62}
63
64// IntegerAutoIncrInserter is implemented by dialects that can perform
65// inserts with automatically incremented integer primary keys.  If
66// the dialect can handle automatic assignment of more than just
67// integers, see TargetedAutoIncrInserter.
68type IntegerAutoIncrInserter interface {
69	InsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error)
70}
71
72// TargetedAutoIncrInserter is implemented by dialects that can
73// perform automatic assignment of any primary key type (i.e. strings
74// for uuids, integers for serials, etc).
75type TargetedAutoIncrInserter interface {
76	// InsertAutoIncrToTarget runs an insert operation and assigns the
77	// automatically generated primary key directly to the passed in
78	// target.  The target should be a pointer to the primary key
79	// field of the value being inserted.
80	InsertAutoIncrToTarget(exec SqlExecutor, insertSql string, target interface{}, params ...interface{}) error
81}
82
83func standardInsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error) {
84	res, err := exec.Exec(insertSql, params...)
85	if err != nil {
86		return 0, err
87	}
88	return res.LastInsertId()
89}
90
91///////////////////////////////////////////////////////
92// sqlite3 //
93/////////////
94
95type SqliteDialect struct {
96	suffix string
97}
98
99func (d SqliteDialect) QuerySuffix() string { return ";" }
100
101func (d SqliteDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
102	switch val.Kind() {
103	case reflect.Ptr:
104		return d.ToSqlType(val.Elem(), maxsize, isAutoIncr)
105	case reflect.Bool:
106		return "integer"
107	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64, reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
108		return "integer"
109	case reflect.Float64, reflect.Float32:
110		return "real"
111	case reflect.Slice:
112		if val.Elem().Kind() == reflect.Uint8 {
113			return "blob"
114		}
115	}
116
117	switch val.Name() {
118	case "NullInt64":
119		return "integer"
120	case "NullFloat64":
121		return "real"
122	case "NullBool":
123		return "integer"
124	case "Time":
125		return "datetime"
126	}
127
128	if maxsize < 1 {
129		maxsize = 255
130	}
131	return fmt.Sprintf("varchar(%d)", maxsize)
132}
133
134// Returns autoincrement
135func (d SqliteDialect) AutoIncrStr() string {
136	return "autoincrement"
137}
138
139func (d SqliteDialect) AutoIncrBindValue() string {
140	return "null"
141}
142
143func (d SqliteDialect) AutoIncrInsertSuffix(col *ColumnMap) string {
144	return ""
145}
146
147// Returns suffix
148func (d SqliteDialect) CreateTableSuffix() string {
149	return d.suffix
150}
151
152// With sqlite, there technically isn't a TRUNCATE statement,
153// but a DELETE FROM uses a truncate optimization:
154// http://www.sqlite.org/lang_delete.html
155func (d SqliteDialect) TruncateClause() string {
156	return "delete from"
157}
158
159// Returns "?"
160func (d SqliteDialect) BindVar(i int) string {
161	return "?"
162}
163
164func (d SqliteDialect) InsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error) {
165	return standardInsertAutoIncr(exec, insertSql, params...)
166}
167
168func (d SqliteDialect) QuoteField(f string) string {
169	return `"` + f + `"`
170}
171
172// sqlite does not have schemas like PostgreSQL does, so just escape it like normal
173func (d SqliteDialect) QuotedTableForQuery(schema string, table string) string {
174	return d.QuoteField(table)
175}
176
177func (d SqliteDialect) IfSchemaNotExists(command, schema string) string {
178	return fmt.Sprintf("%s if not exists", command)
179}
180
181func (d SqliteDialect) IfTableExists(command, schema, table string) string {
182	return fmt.Sprintf("%s if exists", command)
183}
184
185func (d SqliteDialect) IfTableNotExists(command, schema, table string) string {
186	return fmt.Sprintf("%s if not exists", command)
187}
188
189///////////////////////////////////////////////////////
190// PostgreSQL //
191////////////////
192
193type PostgresDialect struct {
194	suffix string
195}
196
197func (d PostgresDialect) QuerySuffix() string { return ";" }
198
199func (d PostgresDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
200	switch val.Kind() {
201	case reflect.Ptr:
202		return d.ToSqlType(val.Elem(), maxsize, isAutoIncr)
203	case reflect.Bool:
204		return "boolean"
205	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Uint8, reflect.Uint16, reflect.Uint32:
206		if isAutoIncr {
207			return "serial"
208		}
209		return "integer"
210	case reflect.Int64, reflect.Uint64:
211		if isAutoIncr {
212			return "bigserial"
213		}
214		return "bigint"
215	case reflect.Float64:
216		return "double precision"
217	case reflect.Float32:
218		return "real"
219	case reflect.Slice:
220		if val.Elem().Kind() == reflect.Uint8 {
221			return "bytea"
222		}
223	}
224
225	switch val.Name() {
226	case "NullInt64":
227		return "bigint"
228	case "NullFloat64":
229		return "double precision"
230	case "NullBool":
231		return "boolean"
232	case "Time":
233		return "timestamp with time zone"
234	}
235
236	if maxsize > 0 {
237		return fmt.Sprintf("varchar(%d)", maxsize)
238	} else {
239		return "text"
240	}
241
242}
243
244// Returns empty string
245func (d PostgresDialect) AutoIncrStr() string {
246	return ""
247}
248
249func (d PostgresDialect) AutoIncrBindValue() string {
250	return "default"
251}
252
253func (d PostgresDialect) AutoIncrInsertSuffix(col *ColumnMap) string {
254	return " returning " + col.ColumnName
255}
256
257// Returns suffix
258func (d PostgresDialect) CreateTableSuffix() string {
259	return d.suffix
260}
261
262func (d PostgresDialect) TruncateClause() string {
263	return "truncate"
264}
265
266// Returns "$(i+1)"
267func (d PostgresDialect) BindVar(i int) string {
268	return fmt.Sprintf("$%d", i+1)
269}
270
271func (d PostgresDialect) InsertAutoIncrToTarget(exec SqlExecutor, insertSql string, target interface{}, params ...interface{}) error {
272	rows, err := exec.query(insertSql, params...)
273	if err != nil {
274		return err
275	}
276	defer rows.Close()
277
278	if rows.Next() {
279		err := rows.Scan(target)
280		return err
281	}
282
283	return errors.New("No serial value returned for insert: " + insertSql + " Encountered error: " + rows.Err().Error())
284}
285
286func (d PostgresDialect) QuoteField(f string) string {
287	return `"` + strings.ToLower(f) + `"`
288}
289
290func (d PostgresDialect) QuotedTableForQuery(schema string, table string) string {
291	if strings.TrimSpace(schema) == "" {
292		return d.QuoteField(table)
293	}
294
295	return schema + "." + d.QuoteField(table)
296}
297
298func (d PostgresDialect) IfSchemaNotExists(command, schema string) string {
299	return fmt.Sprintf("%s if not exists", command)
300}
301
302func (d PostgresDialect) IfTableExists(command, schema, table string) string {
303	return fmt.Sprintf("%s if exists", command)
304}
305
306func (d PostgresDialect) IfTableNotExists(command, schema, table string) string {
307	return fmt.Sprintf("%s if not exists", command)
308}
309
310///////////////////////////////////////////////////////
311// MySQL //
312///////////
313
314// Implementation of Dialect for MySQL databases.
315type MySQLDialect struct {
316
317	// Engine is the storage engine to use "InnoDB" vs "MyISAM" for example
318	Engine string
319
320	// Encoding is the character encoding to use for created tables
321	Encoding string
322}
323
324func (d MySQLDialect) QuerySuffix() string { return ";" }
325
326func (d MySQLDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
327	switch val.Kind() {
328	case reflect.Ptr:
329		return d.ToSqlType(val.Elem(), maxsize, isAutoIncr)
330	case reflect.Bool:
331		return "boolean"
332	case reflect.Int8:
333		return "tinyint"
334	case reflect.Uint8:
335		return "tinyint unsigned"
336	case reflect.Int16:
337		return "smallint"
338	case reflect.Uint16:
339		return "smallint unsigned"
340	case reflect.Int, reflect.Int32:
341		return "int"
342	case reflect.Uint, reflect.Uint32:
343		return "int unsigned"
344	case reflect.Int64:
345		return "bigint"
346	case reflect.Uint64:
347		return "bigint unsigned"
348	case reflect.Float64, reflect.Float32:
349		return "double"
350	case reflect.Slice:
351		if val.Elem().Kind() == reflect.Uint8 {
352			return "mediumblob"
353		}
354	}
355
356	switch val.Name() {
357	case "NullInt64":
358		return "bigint"
359	case "NullFloat64":
360		return "double"
361	case "NullBool":
362		return "tinyint"
363	case "Time":
364		return "datetime"
365	}
366
367	if maxsize < 1 {
368		maxsize = 255
369	}
370	return fmt.Sprintf("varchar(%d)", maxsize)
371}
372
373// Returns auto_increment
374func (d MySQLDialect) AutoIncrStr() string {
375	return "auto_increment"
376}
377
378func (d MySQLDialect) AutoIncrBindValue() string {
379	return "null"
380}
381
382func (d MySQLDialect) AutoIncrInsertSuffix(col *ColumnMap) string {
383	return ""
384}
385
386// Returns engine=%s charset=%s  based on values stored on struct
387func (d MySQLDialect) CreateTableSuffix() string {
388	if d.Engine == "" || d.Encoding == "" {
389		msg := "gorp - undefined"
390
391		if d.Engine == "" {
392			msg += " MySQLDialect.Engine"
393		}
394		if d.Engine == "" && d.Encoding == "" {
395			msg += ","
396		}
397		if d.Encoding == "" {
398			msg += " MySQLDialect.Encoding"
399		}
400		msg += ". Check that your MySQLDialect was correctly initialized when declared."
401		panic(msg)
402	}
403
404	return fmt.Sprintf(" engine=%s charset=%s", d.Engine, d.Encoding)
405}
406
407func (d MySQLDialect) TruncateClause() string {
408	return "truncate"
409}
410
411// Returns "?"
412func (d MySQLDialect) BindVar(i int) string {
413	return "?"
414}
415
416func (d MySQLDialect) InsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error) {
417	return standardInsertAutoIncr(exec, insertSql, params...)
418}
419
420func (d MySQLDialect) QuoteField(f string) string {
421	return "`" + f + "`"
422}
423
424func (d MySQLDialect) QuotedTableForQuery(schema string, table string) string {
425	if strings.TrimSpace(schema) == "" {
426		return d.QuoteField(table)
427	}
428
429	return schema + "." + d.QuoteField(table)
430}
431
432func (d MySQLDialect) IfSchemaNotExists(command, schema string) string {
433	return fmt.Sprintf("%s if not exists", command)
434}
435
436func (d MySQLDialect) IfTableExists(command, schema, table string) string {
437	return fmt.Sprintf("%s if exists", command)
438}
439
440func (d MySQLDialect) IfTableNotExists(command, schema, table string) string {
441	return fmt.Sprintf("%s if not exists", command)
442}
443
444///////////////////////////////////////////////////////
445// Sql Server //
446////////////////
447
448// Implementation of Dialect for Microsoft SQL Server databases.
449// Tested on SQL Server 2008 with driver: github.com/denisenkom/go-mssqldb
450
451type SqlServerDialect struct {
452	suffix string
453}
454
455func (d SqlServerDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
456	switch val.Kind() {
457	case reflect.Ptr:
458		return d.ToSqlType(val.Elem(), maxsize, isAutoIncr)
459	case reflect.Bool:
460		return "bit"
461	case reflect.Int8:
462		return "tinyint"
463	case reflect.Uint8:
464		return "smallint"
465	case reflect.Int16:
466		return "smallint"
467	case reflect.Uint16:
468		return "int"
469	case reflect.Int, reflect.Int32:
470		return "int"
471	case reflect.Uint, reflect.Uint32:
472		return "bigint"
473	case reflect.Int64:
474		return "bigint"
475	case reflect.Uint64:
476		return "bigint"
477	case reflect.Float32:
478		return "real"
479	case reflect.Float64:
480		return "float(53)"
481	case reflect.Slice:
482		if val.Elem().Kind() == reflect.Uint8 {
483			return "varbinary"
484		}
485	}
486
487	switch val.Name() {
488	case "NullInt64":
489		return "bigint"
490	case "NullFloat64":
491		return "float(53)"
492	case "NullBool":
493		return "tinyint"
494	case "Time":
495		return "datetime"
496	}
497
498	if maxsize < 1 {
499		maxsize = 255
500	}
501	return fmt.Sprintf("varchar(%d)", maxsize)
502}
503
504// Returns auto_increment
505func (d SqlServerDialect) AutoIncrStr() string {
506	return "identity(0,1)"
507}
508
509// Empty string removes autoincrement columns from the INSERT statements.
510func (d SqlServerDialect) AutoIncrBindValue() string {
511	return ""
512}
513
514func (d SqlServerDialect) AutoIncrInsertSuffix(col *ColumnMap) string {
515	return ""
516}
517
518// Returns suffix
519func (d SqlServerDialect) CreateTableSuffix() string {
520
521	return d.suffix
522}
523
524func (d SqlServerDialect) TruncateClause() string {
525	return "delete from"
526}
527
528// Returns "?"
529func (d SqlServerDialect) BindVar(i int) string {
530	return "?"
531}
532
533func (d SqlServerDialect) InsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error) {
534	return standardInsertAutoIncr(exec, insertSql, params...)
535}
536
537func (d SqlServerDialect) QuoteField(f string) string {
538	return `"` + f + `"`
539}
540
541func (d SqlServerDialect) QuotedTableForQuery(schema string, table string) string {
542	if strings.TrimSpace(schema) == "" {
543		return table
544	}
545	return schema + "." + table
546}
547
548func (d SqlServerDialect) QuerySuffix() string { return ";" }
549
550func (d SqlServerDialect) IfSchemaNotExists(command, schema string) string {
551	s := fmt.Sprintf("if not exists (select name from sys.schemas where name = '%s') %s", schema, command)
552	return s
553}
554
555func (d SqlServerDialect) IfTableExists(command, schema, table string) string {
556	var schema_clause string
557	if strings.TrimSpace(schema) != "" {
558		schema_clause = fmt.Sprintf("table_schema = '%s' and ", schema)
559	}
560	s := fmt.Sprintf("if exists (select * from information_schema.tables where %stable_name = '%s') %s", schema_clause, table, command)
561	return s
562}
563
564func (d SqlServerDialect) IfTableNotExists(command, schema, table string) string {
565	var schema_clause string
566	if strings.TrimSpace(schema) != "" {
567		schema_clause = fmt.Sprintf("table_schema = '%s' and ", schema)
568	}
569	s := fmt.Sprintf("if not exists (select * from information_schema.tables where %stable_name = '%s') %s", schema_clause, table, command)
570	return s
571}
572
573///////////////////////////////////////////////////////
574// Oracle //
575///////////
576
577// Implementation of Dialect for Oracle databases.
578type OracleDialect struct{}
579
580func (d OracleDialect) QuerySuffix() string { return "" }
581
582func (d OracleDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
583	switch val.Kind() {
584	case reflect.Ptr:
585		return d.ToSqlType(val.Elem(), maxsize, isAutoIncr)
586	case reflect.Bool:
587		return "boolean"
588	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Uint8, reflect.Uint16, reflect.Uint32:
589		if isAutoIncr {
590			return "serial"
591		}
592		return "integer"
593	case reflect.Int64, reflect.Uint64:
594		if isAutoIncr {
595			return "bigserial"
596		}
597		return "bigint"
598	case reflect.Float64:
599		return "double precision"
600	case reflect.Float32:
601		return "real"
602	case reflect.Slice:
603		if val.Elem().Kind() == reflect.Uint8 {
604			return "bytea"
605		}
606	}
607
608	switch val.Name() {
609	case "NullInt64":
610		return "bigint"
611	case "NullFloat64":
612		return "double precision"
613	case "NullBool":
614		return "boolean"
615	case "NullTime", "Time":
616		return "timestamp with time zone"
617	}
618
619	if maxsize > 0 {
620		return fmt.Sprintf("varchar(%d)", maxsize)
621	} else {
622		return "text"
623	}
624
625}
626
627// Returns empty string
628func (d OracleDialect) AutoIncrStr() string {
629	return ""
630}
631
632func (d OracleDialect) AutoIncrBindValue() string {
633	return "default"
634}
635
636func (d OracleDialect) AutoIncrInsertSuffix(col *ColumnMap) string {
637	return " returning " + col.ColumnName
638}
639
640// Returns suffix
641func (d OracleDialect) CreateTableSuffix() string {
642	return ""
643}
644
645func (d OracleDialect) TruncateClause() string {
646	return "truncate"
647}
648
649// Returns "$(i+1)"
650func (d OracleDialect) BindVar(i int) string {
651	return fmt.Sprintf(":%d", i+1)
652}
653
654func (d OracleDialect) InsertAutoIncr(exec SqlExecutor, insertSql string, params ...interface{}) (int64, error) {
655	rows, err := exec.query(insertSql, params...)
656	if err != nil {
657		return 0, err
658	}
659	defer rows.Close()
660
661	if rows.Next() {
662		var id int64
663		err := rows.Scan(&id)
664		return id, err
665	}
666
667	return 0, errors.New("No serial value returned for insert: " + insertSql + " Encountered error: " + rows.Err().Error())
668}
669
670func (d OracleDialect) QuoteField(f string) string {
671	return `"` + strings.ToUpper(f) + `"`
672}
673
674func (d OracleDialect) QuotedTableForQuery(schema string, table string) string {
675	if strings.TrimSpace(schema) == "" {
676		return d.QuoteField(table)
677	}
678
679	return schema + "." + d.QuoteField(table)
680}
681
682func (d OracleDialect) IfSchemaNotExists(command, schema string) string {
683	return fmt.Sprintf("%s if not exists", command)
684}
685
686func (d OracleDialect) IfTableExists(command, schema, table string) string {
687	return fmt.Sprintf("%s if exists", command)
688}
689
690func (d OracleDialect) IfTableNotExists(command, schema, table string) string {
691	return fmt.Sprintf("%s if not exists", command)
692}
693