1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
package testfixtures
import (
"database/sql"
"fmt"
"strings"
)
// SQLServer is the helper for SQL Server for this package.
// SQL Server >= 2008 is required.
type SQLServer struct {
baseHelper
tables []string
}
func (h *SQLServer) init(db *sql.DB) error {
var err error
h.tables, err = h.tableNames(db)
if err != nil {
return err
}
return nil
}
func (*SQLServer) paramType() int {
return paramTypeQuestion
}
func (*SQLServer) quoteKeyword(s string) string {
parts := strings.Split(s, ".")
for i, p := range parts {
parts[i] = fmt.Sprintf(`[%s]`, p)
}
return strings.Join(parts, ".")
}
func (*SQLServer) databaseName(q queryable) (string, error) {
var dbName string
err := q.QueryRow("SELECT DB_NAME()").Scan(&dbName)
return dbName, err
}
func (*SQLServer) tableNames(q queryable) ([]string, error) {
rows, err := q.Query("SELECT table_schema + '.' + table_name FROM information_schema.tables")
if err != nil {
return nil, err
}
defer rows.Close()
var tables []string
for rows.Next() {
var table string
if err = rows.Scan(&table); err != nil {
return nil, err
}
tables = append(tables, table)
}
if err = rows.Err(); err != nil {
return nil, err
}
return tables, nil
}
func (h *SQLServer) tableHasIdentityColumn(q queryable, tableName string) bool {
sql := `
SELECT COUNT(*)
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_ID = OBJECT_ID(?)
`
var count int
q.QueryRow(sql, h.quoteKeyword(tableName)).Scan(&count)
return count > 0
}
func (h *SQLServer) whileInsertOnTable(tx *sql.Tx, tableName string, fn func() error) (err error) {
if h.tableHasIdentityColumn(tx, tableName) {
defer func() {
_, err2 := tx.Exec(fmt.Sprintf("SET IDENTITY_INSERT %s OFF", h.quoteKeyword(tableName)))
if err2 != nil && err == nil {
err = err2
}
}()
_, err := tx.Exec(fmt.Sprintf("SET IDENTITY_INSERT %s ON", h.quoteKeyword(tableName)))
if err != nil {
return err
}
}
return fn()
}
func (h *SQLServer) disableReferentialIntegrity(db *sql.DB, loadFn loadFunction) (err error) {
// ensure the triggers are re-enable after all
defer func() {
var sql string
for _, table := range h.tables {
sql += fmt.Sprintf("ALTER TABLE %s WITH CHECK CHECK CONSTRAINT ALL;", h.quoteKeyword(table))
}
if _, err2 := db.Exec(sql); err2 != nil && err == nil {
err = err2
}
}()
var sql string
for _, table := range h.tables {
sql += fmt.Sprintf("ALTER TABLE %s NOCHECK CONSTRAINT ALL;", h.quoteKeyword(table))
}
if _, err := db.Exec(sql); err != nil {
return err
}
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
if err = loadFn(tx); err != nil {
return err
}
return tx.Commit()
}
// splitter is a batchSplitter interface implementation. We need it for
// SQL Server because commands like a `CREATE SCHEMA...` and a `CREATE TABLE...`
// could not be executed in the same batch.
// See https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175502(v=sql.105)#rules-for-using-batches
func (*SQLServer) splitter() []byte {
return []byte("GO\n")
}
|
