/
model_index.go
341 lines (324 loc) · 9.03 KB
/
model_index.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
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
package sqld
import (
"context"
"database/sql"
"fmt"
"github.com/godaddy-x/freego/ormx/sqlc"
"github.com/godaddy-x/freego/utils"
"github.com/godaddy-x/freego/zlog"
"go.mongodb.org/mongo-driver/bson"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
"reflect"
"sort"
)
type IndexInfo struct {
Table string // 索引所属的表名
NonUnique int // 索引是否是唯一索引。如果值为 0,则表示索引是唯一索引;如果值为 1,则表示索引是普通索引
KeyName string // 索引的名称
SeqInIndex int // 索引中的列的顺序号
ColumnName string // 索引的列名
Collation string // 索引列的排序规则
Cardinality interface{} // 索引列的基数,即不重复的索引值数量
SubPart sql.NullString // 索引的子部分长度。通常用于前缀索引,以指示索引的前缀长度
Packed sql.NullString // 索引存储的方式
Null interface{} // 索引列是否可以包含 NULL 值
IndexType string // 索引的类型,如 BTREE、HASH 等
Comment string // 索引的注释信息
IndexComment string // 索引的额外注释信息
}
func readyCollection(object sqlc.Object) {
db, err := NewMongo(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
if err := db.Save(object); err != nil {
panic(err)
}
if err := db.Delete(object); err != nil {
panic(err)
}
}
func dropMongoIndex(object sqlc.Object, index []sqlc.Index) bool {
readyCollection(object)
db, err := NewMongo(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
coll, err := db.GetDatabase(object.GetTable())
if err != nil {
panic(err)
}
cur, err := coll.Indexes().List(context.Background())
if err != nil {
panic(err)
}
var list []map[string]interface{}
if err := cur.All(context.Background(), &list); err != nil {
panic(err)
}
oldKey := ""
for _, v := range list {
key := v["name"].(string)
if key == "_id_" {
continue
}
oldKey += key
}
newKey := ""
for _, v := range index {
newKey += v.Name
}
if oldKey == newKey {
return false
}
if _, err := coll.Indexes().DropAll(context.Background()); err != nil {
panic(err)
}
return true
}
func dropMysqlIndex(object sqlc.Object, index []sqlc.Index) bool {
db, err := NewMysql(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
// 执行查询获取索引信息
rows, err := db.Db.Query("SHOW INDEX FROM " + object.GetTable())
if err != nil {
panic(err)
}
defer rows.Close()
// 获取查询结果的字段名称
columns, err := rows.Columns()
if err != nil {
panic(err)
}
// 创建一个动态映射,用于存储字段名和对应的值
result := make(map[string]interface{})
values := make([]interface{}, len(columns))
for i := range columns {
values[i] = new(sql.RawBytes)
}
var indexes []IndexInfo
for rows.Next() {
if err := rows.Scan(values...); err != nil {
panic(err)
}
for i, column := range columns {
if values[i] == nil {
result[column] = nil // 或者设置为其他默认值
continue
}
result[column] = values[i]
}
var index IndexInfo
index.Table = string(*result["Table"].(*sql.RawBytes))
index.KeyName = string(*result["Key_name"].(*sql.RawBytes))
index.ColumnName = string(*result["Column_name"].(*sql.RawBytes))
index.IndexType = string(*result["Index_type"].(*sql.RawBytes))
nonUnique, err := utils.StrToInt(string(*result["Non_unique"].(*sql.RawBytes)))
if err != nil {
panic(err)
}
index.NonUnique = nonUnique
indexes = append(indexes, index)
}
check := map[string][]string{}
for _, v := range indexes {
key := v.KeyName
if key == "PRIMARY" {
continue
}
m, b := check[key]
if b {
check[v.KeyName] = append(m, v.ColumnName)
} else {
check[v.KeyName] = []string{v.ColumnName}
}
}
var drop bool
for _, v := range index {
if len(v.Name) == 0 || len(v.Key) == 0 {
panic("table index name/key invalid: " + object.GetTable())
}
key, b := check[v.Name]
if b {
sort.Strings(key)
sort.Strings(v.Key)
if reflect.DeepEqual(key, v.Key) {
continue
}
}
drop = true
break
}
if !drop {
return false
}
for k, _ := range check { // 确定删除表所有索引
if _, err := db.Db.Exec("DROP INDEX `" + k + "` ON " + object.GetTable()); err != nil {
panic(err)
}
}
return true
}
func addMongoIndex(object sqlc.Object, index sqlc.Index) error {
db, err := NewMongo(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
coll, err := db.GetDatabase(object.GetTable())
if err != nil {
panic(err)
}
bsonD := bson.D{}
for _, v := range index.Key {
bsonD = append(bsonD, bson.E{Key: v, Value: 1})
}
modelIndex := mongo.IndexModel{
Keys: bsonD, Options: &options.IndexOptions{Name: &index.Name, Unique: &index.Unique},
}
if _, err := coll.Indexes().CreateOne(context.Background(), modelIndex); err != nil {
panic(err)
}
return nil
}
func addMysqlIndex(object sqlc.Object, index sqlc.Index) error {
if len(index.Key) == 0 {
zlog.Warn("addMysqlIndex keys is nil", 0, zlog.Any("object", object))
return nil
}
if len(index.Name) == 0 {
panic("index key name is nil: " + object.GetTable())
}
var columns string
for _, v := range index.Key {
if len(v) == 0 {
panic("index key field is nil: " + object.GetTable())
}
columns += utils.AddStr(",`", v, "`")
}
sql := "CREATE"
if index.Unique {
sql = utils.AddStr(sql, " UNIQUE ")
}
sql = utils.AddStr(sql, " INDEX ")
sql = utils.AddStr(sql, "`", index.Name, "`")
sql = utils.AddStr(sql, " ON ", object.GetTable(), " (")
sql = utils.AddStr(sql, columns[1:], ")")
db, err := NewMysql(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
if _, err := db.Db.Exec(sql); err != nil {
panic(err)
}
return nil
}
// RebuildMongoDBIndex 先删除所有表索引,再按配置新建(线上慎用功能)
func RebuildMongoDBIndex() error {
for _, model := range modelDrivers {
index := model.Object.NewIndex()
if index == nil {
continue
}
if !dropMongoIndex(model.Object, index) {
fmt.Println(fmt.Sprintf("********* [%s] index consistent, skipping *********", model.Object.GetTable()))
continue
}
fmt.Println(fmt.Sprintf("********* [%s] delete all index *********", model.Object.GetTable()))
for _, v := range index {
addMongoIndex(model.Object, v)
fmt.Println(fmt.Sprintf("********* [%s] add index [%s] *********", model.Object.GetTable(), v.Name))
}
}
return nil
}
func checkMysqlTable(tableName string) (bool, error) {
db, err := NewMysql(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
var result string
if err := db.Db.QueryRow("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1", tableName).Scan(&result); err != nil {
if err == sql.ErrNoRows {
return false, nil // 表不存在
}
return false, err // 查询出错
}
return true, nil // 表存在
}
func isInt(s string) bool {
if s == "int64" || s == "int" {
return true
}
return false
}
func createTable(model *MdlDriver) error {
sql := utils.AddStr("CREATE TABLE ", model.TableName, "( ")
var fields string
for _, v := range model.FieldElem {
if len(v.FieldDBType) == 0 {
if isInt(v.FieldType) {
fields = utils.AddStr(fields, ",`", v.FieldJsonName, "` ", "BIGINT")
} else {
fields = utils.AddStr(fields, ",`", v.FieldJsonName, "` ", "VARCHAR(255)")
}
} else {
fields = utils.AddStr(fields, ",`", v.FieldJsonName, "` ", v.FieldDBType)
}
if v.Primary {
fields = utils.AddStr(fields, " NOT NULL PRIMARY KEY")
}
if len(v.FieldComment) > 0 {
fields = utils.AddStr(fields, " COMMENT '", v.FieldComment, "'")
}
}
sql = utils.AddStr(sql, fields[1:], ")")
sql = utils.AddStr(sql, " ENGINE=InnoDB DEFAULT CHARSET=", model.Charset, " COLLATE=", model.Collate, ";")
db, err := NewMysql(Option{Timeout: 120000})
if err != nil {
panic(err)
}
defer db.Close()
if _, err := db.Db.Exec(sql); err != nil {
return err
}
zlog.Info("create table success", 0, zlog.String("table", model.TableName))
return nil
}
// RebuildMysqlDBIndex 先删除所有表索引,再按配置新建(线上慎用功能)
func RebuildMysqlDBIndex() error {
for _, model := range modelDrivers {
index := model.Object.NewIndex()
if len(index) == 0 {
continue
}
exist, err := checkMysqlTable(model.Object.GetTable())
if err != nil {
panic(err)
}
if !exist {
zlog.Warn("mysql table not exist", 0, zlog.String("table", model.Object.GetTable()))
if err := createTable(model); err != nil {
panic(err)
}
}
if !dropMysqlIndex(model.Object, index) {
fmt.Println(fmt.Sprintf("********* [%s] index consistent, skipping *********", model.Object.GetTable()))
continue
}
fmt.Println(fmt.Sprintf("********* [%s] delete all index *********", model.Object.GetTable()))
for _, v := range index {
addMysqlIndex(model.Object, v)
fmt.Println(fmt.Sprintf("********* [%s] add index [%s] *********", model.Object.GetTable(), v.Name))
}
}
return nil
}