Repository navigation
Expand file tree
/
Copy pathdb.go
More file actions
327 lines (283 loc) · 7.75 KB
/
Copy pathdb.go
File metadata and controls
327 lines (283 loc) · 7.75 KB
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
package main
import (
"database/sql"
"errors"
"log"
"regexp"
"strconv"
"strings"
_ "github.com/go-sql-driver/mysql"
)
// ObjectType : mysql object name
type ObjectType int
// DB Object Type
const (
TABLE ObjectType = iota + 1
VIEW
FUNCTION
PROCEDURE
TRIGGER
)
/*
type ConnString string
func (cs *ConnString) Scan(state fmt.ScanState, verb rune) error {
token, err := state.Token(true, unicode.IsLetter)
if err != nil {
return err
}
*cs = ConnString(token)
return nil
}
*/
// DB mysql db ..
type DB struct {
DB *sql.DB
DBName string
}
// Open : open mysql.
func (d *DB) Open(constr string) error {
db, err := sql.Open("mysql", constr)
if err != nil {
return err
}
err = db.Ping()
if err != nil {
return err
}
d.DB = db
d.DBName = constr[strings.IndexByte(constr, '/')+1:]
log.Println("open mysql db...", constr, d.DBName)
return nil
}
// Close : close mysql
func (d *DB) Close() {
if d != nil && d.DB != nil {
d.DB.Close()
}
}
// GetData : get db rows
func (d *DB) GetData(query string, columns map[string]*Column) ([]*Row, error) {
// Execute the query
rows, err := d.DB.Query(query)
if err != nil {
return nil, err
}
columnNames, err := rows.Columns()
if err != nil {
return nil, err
}
// Set column index.
cols := make(map[string]int)
for i, c := range columnNames {
cols[c] = i
}
values := make([]sql.RawBytes, len(columnNames))
scanArgs := make([]interface{}, len(columnNames))
for i := range values {
scanArgs[i] = &values[i]
}
var result []*Row
// Fetch rows
for rows.Next() {
// get RawBytes from data
err = rows.Scan(scanArgs...)
if err != nil {
return nil, err
}
// Now do something with the data.
// Here we just print each column as a string.
rowData := &Row{Cols: cols}
for idx, col := range values {
var value interface{}
if col != nil {
if columns != nil {
if strings.HasPrefix(columns[columnNames[idx]].Type, "tinyint") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "smallint") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "mediumint") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "int") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "bigint") {
value, _ = strconv.Atoi(string(col))
} else if strings.HasPrefix(columns[columnNames[idx]].Type, "float") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "double") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "decimal") {
value, _ = strconv.ParseFloat(string(col), 64)
} else if strings.HasPrefix(columns[columnNames[idx]].Type, "char") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "varchar") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "json") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "text") {
value = mysqlEscape(col)
} else if strings.HasPrefix(columns[columnNames[idx]].Type, "date") ||
strings.HasPrefix(columns[columnNames[idx]].Type, "datetime") {
value = string(col)
} else {
log.Fatalln("invalid data type", columns[columnNames[idx]].Name, columns[columnNames[idx]].Type)
}
} else {
value = string(col)
}
}
rowData.Data = append(rowData.Data, value)
}
result = append(result, rowData)
}
if err = rows.Err(); err != nil {
return nil, err
}
return result, nil
}
var reg1 = regexp.MustCompile("DEFINER=[^ ]* ")
// var reg2 = regexp.MustCompile("ENGINE=[^ ]* ")
var reg3 = regexp.MustCompile("AUTO_INCREMENT=[^ ]* ")
var reg4 = regexp.MustCompile("DEFAULT CHARSET=[^ ]* ")
var reg5 = regexp.MustCompile("ALGORITHM=[^ ]* ")
var reg6 = regexp.MustCompile("ROW_FORMAT=[^ ]* ")
func ignoreDefiner(result string) string {
result = reg1.ReplaceAllString(result, "")
// result = reg2.ReplaceAllString(result, "")
result = reg3.ReplaceAllString(result, "")
result = reg4.ReplaceAllString(result, "")
result = reg5.ReplaceAllString(result, "")
result = reg6.ReplaceAllString(result, "")
result = strings.Replace(result, "SQL SECURITY DEFINER ", "", -1)
return result
}
// GetScript : get create script
func (d *DB) GetScript(objectType ObjectType, objectName string) (string, error) {
var query string
switch objectType {
case TABLE:
query = "SHOW CREATE TABLE " + objectName
case VIEW:
query = "SHOW CREATE VIEW " + objectName
case FUNCTION:
query = "SHOW CREATE FUNCTION " + objectName
case PROCEDURE:
query = "SHOW CREATE PROCEDURE " + objectName
case TRIGGER:
query = "SHOW CREATE TRIGGER " + objectName
}
//log.Println(query)
data, err := d.GetData(query, nil)
if err != nil {
return "", err
}
if len(data) == 0 {
return "", errors.New("not found data")
}
// log.Println(data[0])
var result string
switch objectType {
case TABLE:
result = data[0].Get("Create Table").(string)
case VIEW:
result = data[0].Get("Create View").(string)
case FUNCTION:
result = data[0].Get("Create Function").(string)
case PROCEDURE:
result = data[0].Get("Create Procedure").(string)
case TRIGGER:
result = data[0].Get("SQL Original Statement").(string)
}
//log.Println(result)
return strings.TrimSpace(ignoreDefiner(result)), nil
}
// GetObjectComments get object comment
func (d *DB) GetObjectComments(objectType ObjectType, objectName string) string {
var query string
switch objectType {
case TABLE:
query = "SHOW TABLE STATUS WHERE Name='" + objectName + "'"
case VIEW, TRIGGER:
return ""
case FUNCTION:
query = "SHOW FUNCTION STATUS WHERE Db = DATABASE() AND Name='" + objectName + "'"
case PROCEDURE:
query = "SHOW PROCEDURE STATUS WHERE Db = DATABASE() AND Name='" + objectName + "'"
}
data, err := d.GetData(query, nil)
if err != nil || len(data) == 0 {
return ""
}
return data[0].Get("Comment").(string)
}
// GetObjectList get db object list.
func (d *DB) GetObjectList(objectType ObjectType, include string, exclude string) ([]string, error) {
var query string
switch objectType {
case TABLE:
query = "SHOW FULL TABLES WHERE TABLE_TYPE NOT LIKE 'VIEW'"
case VIEW:
query = "SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW'"
case FUNCTION:
query = "SHOW FUNCTION STATUS WHERE Db = DATABASE()"
case PROCEDURE:
query = "SHOW PROCEDURE STATUS WHERE Db = DATABASE()"
case TRIGGER:
query = "SHOW TRIGGERS"
}
data, err := d.GetData(query, nil)
if err != nil {
return nil, err
}
if include != "" {
include = strings.ToLower(include)
}
if exclude != "" {
exclude = strings.ToLower(exclude)
}
var result []string
for _, d := range data {
var name string
switch objectType {
case TABLE, VIEW:
name = d.Data[0].(string)
case FUNCTION, PROCEDURE:
name = d.Get("Name").(string)
case TRIGGER:
name = d.Get("Trigger").(string)
}
bIgnore := false
if include != "" && !strings.Contains(strings.ToLower(name), include) {
bIgnore = true
}
if exclude != "" && strings.Contains(strings.ToLower(name), exclude) {
bIgnore = true
}
if !bIgnore {
result = append(result, name)
}
}
return result, nil
}
// GetTableList get table info list.
func (d *DB) GetTableList(include string, exclude string) (map[string]*Table, error) {
tableNames, err := d.GetObjectList(TABLE, include, include)
if err != nil {
return nil, err
}
result := make(map[string]*Table)
for _, t := range tableNames {
table := d.GetTableInfo(t)
if table == nil {
return nil, errors.New("load table info error")
}
result[t] = table
}
return result, nil
}
// GetScriptList get script info list.
func (d *DB) GetScriptList(objectType ObjectType, include string, exclude string) (map[string]string, error) {
scriptNames, err := d.GetObjectList(objectType, include, exclude)
if err != nil {
return nil, err
}
result := make(map[string]string)
for _, t := range scriptNames {
script, err := d.GetScript(objectType, t)
if err != nil {
return nil, err
}
result[t] = script
}
return result, nil
}