forked from gnormal/gnorm
-
Notifications
You must be signed in to change notification settings - Fork 0
/
parse.go
304 lines (263 loc) · 8.32 KB
/
parse.go
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
package mysql // import "github.com/episub/gnorm/database/drivers/mysql"
import (
"database/sql"
"fmt"
"log"
"strings"
// mysql driver
_ "github.com/go-sql-driver/mysql"
"github.com/pkg/errors"
"github.com/episub/gnorm/database"
"github.com/episub/gnorm/database/drivers/mysql/gnorm/columns"
"github.com/episub/gnorm/database/drivers/mysql/gnorm/statistics"
"github.com/episub/gnorm/database/drivers/mysql/gnorm/tables"
)
//go:generate gnorm gen
// MySQL implements drivers.Driver interface for MySQL database.
type MySQL struct{}
// Parse reads the mysql schemas for the given schemas and converts them into
// database.Info structs.
func (MySQL) Parse(log *log.Logger, conn string, schemaNames []string, filterTables func(schema, table string) bool) (*database.Info, error) {
return parse(log, conn, schemaNames, filterTables)
}
func parse(log *log.Logger, conn string, schemaNames []string, filterTables func(schema, table string) bool) (*database.Info, error) {
log.Println("connecting to mysql with DSN", conn)
db, err := sql.Open("mysql", conn)
if err != nil {
return nil, errors.WithStack(err)
}
log.Println("querying table schemas for", schemaNames)
tables, err := tables.Query(db, tables.TableSchemaCol.In(schemaNames))
if err != nil {
return nil, err
}
schemas := make(map[string][]*database.Table, len(schemaNames))
for _, t := range tables {
if !filterTables(t.TableSchema, t.TableName) {
continue
}
schemas[t.TableSchema] = append(schemas[t.TableSchema], &database.Table{
Name: t.TableName,
Type: t.TableType,
Comment: t.TableComment,
})
}
columns, err := columns.Query(db, columns.TableSchemaCol.In(schemaNames))
if err != nil {
return nil, err
}
enums := map[string][]*database.Enum{}
for _, c := range columns {
if !filterTables(c.TableSchema, c.TableName) {
continue
}
tables, ok := schemas[c.TableSchema]
if !ok {
log.Printf("Should be impossible: column %q references unknown schema %q", c.ColumnName, c.TableSchema)
continue
}
var table *database.Table
for _, t := range tables {
if t.Name == c.TableName {
table = t
break
}
}
if table == nil {
log.Printf("Should be impossible: column %q references unknown table %q in schema %q", c.ColumnName, c.TableName, c.TableSchema)
continue
}
col, enum, terr := toDBColumn(c, log)
if terr != nil {
return nil, terr
}
table.Columns = append(table.Columns, col)
if enum != nil {
enum.Table = c.TableName
enums[c.TableSchema] = append(enums[c.TableSchema], enum)
}
}
indexes := make(map[string]map[string][]*database.Index)
statistics, err := statistics.Query(db, statistics.TableSchemaCol.In(schemaNames))
if err != nil {
return nil, err
}
for _, s := range statistics {
if !filterTables(s.TableSchema, s.TableName) {
continue
}
tables, ok := schemas[s.TableSchema]
if !ok {
log.Printf("Should be impossible: index %q references unknown schema %q", s.IndexName, s.TableSchema)
continue
}
var table *database.Table
for _, t := range tables {
if t.Name == s.TableName {
table = t
break
}
}
if table == nil {
log.Printf("Should be impossible: index %q references unknown table %q", s.IndexName, s.TableName)
continue
}
var column *database.Column
for _, c := range table.Columns {
if c.Name == s.ColumnName {
column = c
break
}
}
if column == nil {
log.Printf("Should be impossible: index %q references unknown column %q", s.IndexName, s.ColumnName)
continue
}
schemaIndex, ok := indexes[s.TableSchema]
if !ok {
schemaIndex = make(map[string][]*database.Index)
indexes[s.TableSchema] = schemaIndex
}
var index *database.Index
for _, i := range schemaIndex[s.TableName] {
if i.Name == s.IndexName {
index = i
break
}
}
if index == nil {
index = &database.Index{Name: s.IndexName, IsUnique: s.NonUnique == 0}
schemaIndex[s.TableName] = append(schemaIndex[s.TableName], index)
}
index.Columns = append(index.Columns, column)
}
foreignKeys, err := queryForeignKeys(log, db, schemaNames)
if err != nil {
return nil, err
}
for _, fk := range foreignKeys {
if !filterTables(fk.SchemaName, fk.TableName) {
log.Printf("skipping constraint %q because it is for filtered-out table %v.%v", fk.Name, fk.SchemaName, fk.TableName)
continue
}
tables, ok := schemas[fk.SchemaName]
if !ok {
log.Printf("Should be impossible: constraint %q references unknown schema %q", fk.Name, fk.SchemaName)
continue
}
var table *database.Table
for _, t := range tables {
if t.Name == fk.TableName {
table = t
break
}
}
if table == nil {
log.Printf("Should be impossible: constraint %q references unknown table %q in schema %q", fk.Name, fk.TableName, fk.SchemaName)
continue
}
for _, col := range table.Columns {
if fk.ColumnName != col.Name {
continue
}
col.IsForeignKey = true
col.ForeignKey = fk
}
}
res := &database.Info{Schemas: make([]*database.Schema, 0, len(schemas))}
for _, schema := range schemaNames {
tables := schemas[schema]
s := &database.Schema{
Name: schema,
Tables: tables,
Enums: enums[schema],
}
dbtables := make(map[string]*database.Table, len(tables))
for _, t := range tables {
dbtables[t.Name] = t
}
for tname, index := range indexes[schema] {
dbtables[tname].Indexes = index
}
res.Schemas = append(res.Schemas, s)
}
return res, nil
}
func toDBColumn(c *columns.Row, log *log.Logger) (*database.Column, *database.Enum, error) {
col := &database.Column{
Name: c.ColumnName,
Nullable: c.IsNullable == "YES",
HasDefault: c.ColumnDefault.String != "",
Type: c.DataType,
Comment: c.ColumnComment,
Ordinal: c.OrdinalPosition,
Orig: *c,
IsPrimaryKey: strings.Contains(c.ColumnKey, "PRI"),
}
// MySQL always specifies length even if it's not a part of the type. We
// only really care if it's a part of the type, so check if the size is part
// of the column_type.
if strings.HasSuffix(c.ColumnType, fmt.Sprintf("(%v)", c.CharacterMaximumLength.Int64)) {
col.Length = int(c.CharacterMaximumLength.Int64)
}
if col.Type != "enum" {
return col, nil, nil
}
// in mysql, enums are specific to a column in a table, so all their data is
// contained in the column they're used by.
// column type should be enum('foo', 'bar')
if len(c.ColumnType) < 5 {
return nil, nil, errors.New("unexpected column type: " + c.ColumnType)
}
// we'll call the enum the same as the column name.
// the function above will set the table name etc
enum := &database.Enum{
Name: col.Name,
}
// strip off the enum and parens
s := c.ColumnType[5 : len(c.ColumnType)-1]
vals := strings.Split(s, ",")
enum.Values = make([]*database.EnumValue, len(vals))
for x := range vals {
enum.Values[x] = &database.EnumValue{
// strip off the quotes
Name: vals[x][1 : len(vals[x])-1],
// enum values start at 1 in mysql
Value: x + 1,
}
}
return col, enum, nil
}
func queryForeignKeys(log *log.Logger, db *sql.DB, schemas []string) ([]*database.ForeignKey, error) {
// TODO: make this work with Gnorm generated types
const q = `SELECT lkc.TABLE_SCHEMA, lkc.TABLE_NAME, lkc.COLUMN_NAME, lkc.CONSTRAINT_NAME, lkc.POSITION_IN_UNIQUE_CONSTRAINT, lkc.REFERENCED_TABLE_NAME, lkc.REFERENCED_COLUMN_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS as rc
LEFT JOIN information_schema.KEY_COLUMN_USAGE as lkc
ON lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE rc.CONSTRAINT_SCHEMA IN (%s)`
spots := make([]string, len(schemas))
vals := make([]interface{}, len(schemas))
for x := range schemas {
spots[x] = "?"
vals[x] = schemas[x]
}
query := fmt.Sprintf(q, strings.Join(spots, ", "))
rows, err := db.Query(query, vals...)
if err != nil {
return nil, errors.WithMessage(err, "error querying foreign keys")
}
defer rows.Close()
var ret []*database.ForeignKey
for rows.Next() {
fk := &database.ForeignKey{}
if err := rows.Scan(&fk.SchemaName, &fk.TableName, &fk.ColumnName, &fk.Name, &fk.UniqueConstraintPosition, &fk.ForeignTableName, &fk.ForeignColumnName); err != nil {
return nil, errors.WithMessage(err, "error scanning foreign key constraint")
}
ret = append(ret, fk)
}
if rows.Err() != nil {
return nil, errors.WithMessage(rows.Err(), "error reading foreign keys")
}
return ret, nil
}