623 lines
20 KiB
Go
623 lines
20 KiB
Go
package db
|
||
|
||
import (
|
||
_ "embed"
|
||
"fmt"
|
||
"github.com/ssgo/db"
|
||
"github.com/ssgo/log"
|
||
"github.com/ssgo/u"
|
||
"os"
|
||
"regexp"
|
||
"strings"
|
||
"text/template"
|
||
)
|
||
|
||
type TableFieldDesc struct {
|
||
Field string
|
||
Type string
|
||
Null string
|
||
Key string
|
||
Default string
|
||
Extra string
|
||
After string
|
||
}
|
||
|
||
type TableKeyDesc struct {
|
||
Key_name string
|
||
Column_name string
|
||
}
|
||
|
||
type TableField struct {
|
||
Name string
|
||
Type string
|
||
Index string
|
||
IndexGroup string
|
||
Default string
|
||
Comment string
|
||
IsNull bool
|
||
Extra string
|
||
desc string
|
||
Null string
|
||
}
|
||
|
||
type ERGroup struct {
|
||
Group string
|
||
Comment string
|
||
Tables []TableStruct
|
||
}
|
||
|
||
type TableStruct struct {
|
||
Name string
|
||
Comment string
|
||
Fields []TableField
|
||
}
|
||
|
||
//go:embed er.html
|
||
var erTpl string
|
||
|
||
func (field *TableField) Parse(tableType string) {
|
||
//if field.Index == "autoId" {
|
||
// field.Type += " unsigned"
|
||
//}
|
||
|
||
if field.IsNull {
|
||
field.Null = "NULL"
|
||
} else {
|
||
field.Null = "NOT NULL"
|
||
}
|
||
|
||
if tableType == "sqlite3" {
|
||
// sqlite3 不能修改字段，统一使用NULL
|
||
field.Null = "NULL"
|
||
if field.Extra == "AUTO_INCREMENT" {
|
||
field.Extra = "PRIMARY KEY AUTOINCREMENT"
|
||
field.Type = "integer"
|
||
field.Null = "NOT NULL"
|
||
}
|
||
}
|
||
|
||
a := make([]string, 0)
|
||
a = append(a, fmt.Sprintf("`%s` %s", field.Name, field.Type))
|
||
|
||
if tableType == "mysql" {
|
||
lowerType := strings.ToLower(field.Type)
|
||
if strings.Contains(lowerType, "varchar") || strings.Contains(lowerType, "text") {
|
||
a = append(a, " COLLATE utf8mb4_general_ci")
|
||
}
|
||
}
|
||
//if field.Index == "autoId" {
|
||
// a = append(a, " AUTO_INCREMENT")
|
||
// field.Index = "pk"
|
||
// //a = append(a, " NOT NULL")
|
||
//}
|
||
|
||
//if field.Index == "uniqueId" {
|
||
// field.Index = "pk"
|
||
// //a = append(a, " NOT NULL")
|
||
//}
|
||
|
||
if field.Extra != "" {
|
||
a = append(a, " "+field.Extra)
|
||
}
|
||
a = append(a, " "+field.Null)
|
||
|
||
if field.Default != "" {
|
||
if strings.Contains(field.Default, "CURRENT_TIMESTAMP") {
|
||
a = append(a, " DEFAULT "+field.Default)
|
||
} else {
|
||
a = append(a, " DEFAULT '"+field.Default+"'")
|
||
}
|
||
}
|
||
if tableType == "sqlite3" {
|
||
field.Comment = ""
|
||
field.Type = "numeric"
|
||
} else if tableType == "mysql" {
|
||
if field.Comment != "" {
|
||
a = append(a, " COMMENT '"+field.Comment+"'")
|
||
}
|
||
}
|
||
field.desc = strings.Join(a, "")
|
||
}
|
||
|
||
var ddlTableMatcher = regexp.MustCompile("(?is)^\\s*CREATE\\s+TABLE\\s+`?([\\w]+)`?\\s*\\(\\s*(.*?)\\s*\\);?\\s*$")
|
||
var ddlFieldMatcher = regexp.MustCompile("(?s)\\s*[`\\[]?([\\w]+)[`\\]]?\\s+\\[?([\\w() ]+)\\]?\\s*(.*?)(,|$)")
|
||
var ddlKeyMatcher = regexp.MustCompile("[`\\[]?([\\w]+)[`\\]]?\\s*(,|\\))")
|
||
var ddlNotNullMatcher = regexp.MustCompile("(?i)\\s+NOT NULL")
|
||
var ddlNullMatcher = regexp.MustCompile("(?i)\\s+NULL")
|
||
|
||
//var ddlDefaultMatcher = regexp.MustCompile("(?i)\\s+DEFAULT\\s+(.*?)$")
|
||
var ddlIndexMatcher = regexp.MustCompile("(?is)^\\s*CREATE\\s+([A-Za-z ]+)\\s+`?([\\w]+)`?\\s+ON\\s+`?([\\w]+)`?\\s*\\(\\s*(.*?)\\s*\\);?\\s*$")
|
||
var ddlIndexFieldMatcher = regexp.MustCompile("[`\\[]?([\\w]+)[`\\]]?\\s*(,|$)")
|
||
|
||
// MakeER 创建ER图文件
|
||
func MakeER(groups []ERGroup, outputFile *string, tplFile *string, logger *log.Logger) error {
|
||
tplStr := ""
|
||
if tplFile == nil || *tplFile == "" {
|
||
tplStr = erTpl
|
||
} else {
|
||
tplStr = *tplFile
|
||
}
|
||
erOutputFile := "er.html"
|
||
if outputFile != nil && *outputFile != "" {
|
||
erOutputFile = *outputFile
|
||
}
|
||
tpl := template.New(erOutputFile).Funcs(template.FuncMap{
|
||
"short": func(in string) string {
|
||
switch in {
|
||
case "NULL":
|
||
return "n"
|
||
case "NOT NULL":
|
||
return "nn"
|
||
case "AUTO_INCREMENT":
|
||
return "ai"
|
||
case "CURRENT_TIMESTAMP":
|
||
return "ct"
|
||
case "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP":
|
||
return "ctu"
|
||
}
|
||
return in
|
||
},
|
||
})
|
||
var err error
|
||
tpl, err = tpl.Parse(tplStr)
|
||
if err == nil {
|
||
var fp *os.File
|
||
fp, err = os.OpenFile(erOutputFile, os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0666)
|
||
if err == nil {
|
||
err = tpl.Execute(fp, map[string]interface{}{
|
||
"title": "ER",
|
||
"groups": groups,
|
||
})
|
||
_ = fp.Close()
|
||
}
|
||
}
|
||
if err != nil {
|
||
logger.Error(err.Error())
|
||
}
|
||
return err
|
||
}
|
||
|
||
func MakeTable(conn *db.DB, table *TableStruct, logger *log.Logger) ([]string, error) {
|
||
//fmt.Println(u.JsonP(ddlKeyMatcher.FindAllStringSubmatch("(`key`,id, `name` )", 100)), "====================")
|
||
fieldSets := make([]string, 0)
|
||
//fieldSetBy := make(map[string]string)
|
||
pks := make([]string, 0)
|
||
keySets := make([]string, 0)
|
||
keySetBy := make(map[string]string)
|
||
keySetFields := make(map[string]string)
|
||
for i, field := range table.Fields {
|
||
field.Parse(conn.Config.Type)
|
||
table.Fields[i] = field
|
||
|
||
if conn.Config.Type == "sqlite3" {
|
||
if field.Index == "pk" && field.Extra != "PRIMARY KEY AUTOINCREMENT" {
|
||
// sqlite3 用 unique 代替 pk
|
||
field.Index = "unique"
|
||
field.IndexGroup = "0"
|
||
field.Null = "NULL"
|
||
}
|
||
}
|
||
|
||
switch field.Index {
|
||
case "pk":
|
||
if conn.Config.Type == "sqlite3" {
|
||
if field.Extra != "PRIMARY KEY AUTOINCREMENT" {
|
||
pks = append(pks, field.Name)
|
||
}
|
||
} else {
|
||
pks = append(pks, field.Name)
|
||
}
|
||
case "unique":
|
||
keyName := fmt.Sprint("uk_", table.Name, "_", field.Name)
|
||
if field.IndexGroup != "" {
|
||
keyName = fmt.Sprint("uk_", table.Name, "_", field.IndexGroup)
|
||
}
|
||
if keySetBy[keyName] != "" {
|
||
keySetFields[keyName] += " " + field.Name
|
||
// 复合索引
|
||
if conn.Config.Type == "sqlite3" {
|
||
keySetBy[keyName] = strings.Replace(keySetBy[keyName], ")", ", `"+field.Name+"`)", 1)
|
||
} else if conn.Config.Type == "mysql" {
|
||
keySetBy[keyName] = strings.Replace(keySetBy[keyName], ") COMMENT", ", `"+field.Name+"`) COMMENT", 1)
|
||
}
|
||
} else {
|
||
keySetFields[keyName] = field.Name
|
||
keySet := ""
|
||
if conn.Config.Type == "sqlite3" {
|
||
keySet = fmt.Sprintf("CREATE UNIQUE INDEX `%s` ON `%s` (`%s`)", keyName, table.Name, field.Name)
|
||
} else if conn.Config.Type == "mysql" {
|
||
keySet = fmt.Sprintf("UNIQUE KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
|
||
}
|
||
keySets = append(keySets, keySet)
|
||
keySetBy[keyName] = keySet
|
||
}
|
||
case "fulltext":
|
||
if conn.Config.Type == "mysql" {
|
||
keyName := fmt.Sprint("tk_", table.Name, "_", field.Name)
|
||
keySet := fmt.Sprintf("FULLTEXT KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
|
||
keySets = append(keySets, keySet)
|
||
keySetBy[keyName] = keySet
|
||
}
|
||
case "index":
|
||
keyName := fmt.Sprint("ik_", table.Name, "_", field.Name)
|
||
if field.IndexGroup != "" {
|
||
keyName = fmt.Sprint("ik_", table.Name, "_", field.IndexGroup)
|
||
}
|
||
if keySetBy[keyName] != "" {
|
||
keySetFields[keyName] += " " + field.Name
|
||
// 复合索引
|
||
if conn.Config.Type == "sqlite3" {
|
||
keySetBy[keyName] = strings.Replace(keySetBy[keyName], ")", ", `"+field.Name+"`)", 1)
|
||
} else if conn.Config.Type == "mysql" {
|
||
keySetBy[keyName] = strings.Replace(keySetBy[keyName], ") COMMENT", ", `"+field.Name+"`) COMMENT", 1)
|
||
}
|
||
} else {
|
||
keySetFields[keyName] = field.Name
|
||
keySet := ""
|
||
if conn.Config.Type == "sqlite3" {
|
||
keySet = fmt.Sprintf("CREATE INDEX `%s` ON `%s` (`%s`)", keyName, table.Name, field.Name)
|
||
} else if conn.Config.Type == "mysql" {
|
||
keySet = fmt.Sprintf("KEY `%s` (`%s`) COMMENT '%s'", keyName, field.Name, field.Comment)
|
||
}
|
||
keySets = append(keySets, keySet)
|
||
keySetBy[keyName] = keySet
|
||
}
|
||
}
|
||
|
||
fieldSets = append(fieldSets, field.desc)
|
||
//fieldSetBy[field.Name] = field.desc
|
||
}
|
||
//fmt.Println(u.JsonP(table.Fields))
|
||
//fmt.Println(u.JsonP(keySetBy), 3)
|
||
//fmt.Println(u.JsonP(keySets), 4)
|
||
|
||
outSql := make([]string, 0)
|
||
|
||
var result *db.ExecResult
|
||
var tableInfo map[string]interface{}
|
||
if conn.Config.Type == "sqlite3" {
|
||
tableInfo = conn.Query("SELECT `name`, `sql` FROM `sqlite_master` WHERE `type`='table' AND `name`='" + table.Name + "'").MapOnR1()
|
||
tableInfo["comment"] = ""
|
||
} else if conn.Config.Type == "mysql" {
|
||
tableInfo = conn.Query("SELECT TABLE_NAME name, TABLE_COMMENT comment FROM information_schema.TABLES WHERE TABLE_SCHEMA='" + conn.Config.DB + "' AND TABLE_NAME='" + table.Name + "'").MapOnR1()
|
||
}
|
||
oldTableComment := u.String(tableInfo["comment"])
|
||
|
||
if tableInfo["name"] != nil && tableInfo["name"] != "" {
|
||
// 合并字段
|
||
oldFieldList := make([]*TableFieldDesc, 0)
|
||
oldFields := make(map[string]*TableFieldDesc)
|
||
oldIndexes := make(map[string]string)
|
||
oldIndexInfos := make([]*TableKeyDesc, 0)
|
||
|
||
oldComments := map[string]string{}
|
||
if conn.Config.Type == "sqlite3" {
|
||
tableM := ddlTableMatcher.FindStringSubmatch(u.String(tableInfo["sql"]))
|
||
if tableM != nil {
|
||
fieldsM := ddlFieldMatcher.FindAllStringSubmatch(tableM[2], 2000)
|
||
if fieldsM != nil {
|
||
for _, m := range fieldsM {
|
||
if m[1] == "PRIMARY" && m[2] == "KEY" {
|
||
keysM := ddlKeyMatcher.FindAllStringSubmatch(m[3], 20)
|
||
if keysM != nil {
|
||
for _, km := range keysM {
|
||
oldIndexInfos = append(oldIndexInfos, &TableKeyDesc{
|
||
Key_name: "PRIMARY",
|
||
Column_name: km[1],
|
||
})
|
||
}
|
||
}
|
||
} else {
|
||
Null := "NULL"
|
||
//fmt.Println(" =====", m[0], m[1], m[2])
|
||
if ddlNotNullMatcher.MatchString(m[2]) {
|
||
m[2] = ddlNotNullMatcher.ReplaceAllString(m[2], "")
|
||
Null = "NOT NULL"
|
||
} else if ddlNullMatcher.MatchString(m[2]) {
|
||
m[2] = ddlNullMatcher.ReplaceAllString(m[2], "")
|
||
Null = "NULL"
|
||
}
|
||
//fmt.Println(" =====", m[2], "|", Null)
|
||
|
||
oldFieldList = append(oldFieldList, &TableFieldDesc{
|
||
Field: m[1],
|
||
Type: m[2],
|
||
//Null: u.StringIf(strings.Contains(m[3], "NOT NULL"), "NO", "YES"),
|
||
Null: u.StringIf(Null == "NOT NULL", "NO", "YES"),
|
||
Key: "",
|
||
Default: "",
|
||
Extra: "",
|
||
After: "",
|
||
})
|
||
}
|
||
}
|
||
}
|
||
//fmt.Println(u.JsonP(fieldsM), 222)
|
||
}
|
||
|
||
// 读取索引信息
|
||
for _, indexInfo := range conn.Query("SELECT `name`, `sql` FROM `sqlite_master` WHERE `type`='index' AND `tbl_name`='" + table.Name + "'").StringMapResults() {
|
||
//fmt.Println(u.JsonP(indexInfo), 777)
|
||
indexM := ddlIndexMatcher.FindStringSubmatch(indexInfo["sql"])
|
||
if indexM != nil {
|
||
//fmt.Println(u.JsonP(indexM), 666)
|
||
indexFieldM := ddlIndexFieldMatcher.FindAllStringSubmatch(indexM[4], 20)
|
||
//fmt.Println(u.JsonP(indexFieldM), 555)
|
||
if indexFieldM != nil {
|
||
for _, km := range indexFieldM {
|
||
oldIndexInfos = append(oldIndexInfos, &TableKeyDesc{
|
||
Key_name: indexInfo["name"],
|
||
Column_name: km[1],
|
||
})
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
//fmt.Println(u.JsonP(oldFieldList), 1)
|
||
//fmt.Println(u.JsonP(oldIndexInfos), 2)
|
||
} else if conn.Config.Type == "mysql" {
|
||
conn.Query("SELECT column_name, column_comment FROM information_schema.columns WHERE TABLE_SCHEMA='" + conn.Config.DB + "' AND TABLE_NAME='" + table.Name + "'").ToKV(&oldComments)
|
||
_ = conn.Query("DESC `" + table.Name + "`").To(&oldFieldList)
|
||
_ = conn.Query("SHOW INDEX FROM `" + table.Name + "`").To(&oldIndexInfos)
|
||
}
|
||
//fmt.Println(u.JsonP(oldComments), 111)
|
||
|
||
for _, indexInfo := range oldIndexInfos {
|
||
if oldIndexes[indexInfo.Key_name] == "" {
|
||
oldIndexes[indexInfo.Key_name] = indexInfo.Column_name
|
||
} else {
|
||
oldIndexes[indexInfo.Key_name] += " " + indexInfo.Column_name
|
||
}
|
||
}
|
||
//fmt.Println(u.JsonP(oldIndexes), 111)
|
||
//fmt.Println(u.JsonP(keySetFields), 222)
|
||
//fmt.Println(u.JsonP(keySetBy), 333)
|
||
|
||
// 先后顺序
|
||
prevFieldId := ""
|
||
for _, field := range oldFieldList {
|
||
if conn.Config.Type == "sqlite3" {
|
||
field.Type = "numeric"
|
||
} else if conn.Config.Type == "mysql" {
|
||
field.After = prevFieldId
|
||
}
|
||
prevFieldId = field.Field
|
||
oldFields[field.Field] = field
|
||
}
|
||
//fmt.Println(111, u.JsonP(oldFields), 111)
|
||
|
||
actions := make([]string, 0)
|
||
for keyId := range oldIndexes {
|
||
if keyId != "PRIMARY" && strings.ToLower(keySetFields[keyId]) != strings.ToLower(oldIndexes[keyId]) {
|
||
if conn.Config.Type == "sqlite3" {
|
||
actions = append(actions, "DROP INDEX `"+keyId+"`")
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "DROP KEY `"+keyId+"`")
|
||
}
|
||
}
|
||
}
|
||
//fmt.Println(" =>>>>>>>>", oldIndexes, pks)
|
||
if oldIndexes["PRIMARY"] != "" && strings.ToLower(oldIndexes["PRIMARY"]) != strings.ToLower(strings.Join(pks, " ")) {
|
||
if conn.Config.Type == "sqlite3" {
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "DROP PRIMARY KEY")
|
||
}
|
||
}
|
||
//for fieldId, fieldSet := range fieldSetBy {
|
||
newFieldExists := map[string]bool{}
|
||
prevFieldId = ""
|
||
for _, field := range table.Fields {
|
||
newFieldExists[field.Name] = true
|
||
oldField := oldFields[field.Name]
|
||
|
||
// 修复部分数据库的特殊性
|
||
if oldField == nil {
|
||
if conn.Config.Type == "sqlite3" {
|
||
actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.desc)
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "ADD COLUMN "+field.desc)
|
||
}
|
||
} else {
|
||
oldField.Type = strings.TrimSpace(strings.ReplaceAll(oldField.Type, " (", "("))
|
||
fixedOldDefault := u.String(oldField.Default)
|
||
if fixedOldDefault == "CURRENT_TIMESTAMP" && strings.Contains(oldField.Extra, "on update CURRENT_TIMESTAMP") {
|
||
fixedOldDefault = "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
|
||
}
|
||
fixedOldNull := "NOT NULL"
|
||
if oldField.Null == "YES" {
|
||
fixedOldNull = "NULL"
|
||
}
|
||
//fmt.Println(" ==", field.Type, "!=", oldField.Type, "||", field.Default, "!=", fixedOldDefault, "||", field.Null, "!=", fixedOldNull, "||", oldField.After, "!=", prevFieldId, "||", oldComments[field.Name], "!=", field.Comment)
|
||
//fmt.Println(" ==", strings.ToLower(field.Type) != strings.ToLower(oldField.Type), strings.ToLower(field.Default) != strings.ToLower(fixedOldDefault), strings.ToLower(field.Null) != strings.ToLower(fixedOldNull), strings.ToLower(oldField.After) != strings.ToLower(prevFieldId), strings.ToLower(oldComments[field.Name]) != strings.ToLower(field.Comment))
|
||
if strings.ToLower(field.Type) != strings.ToLower(oldField.Type) || strings.ToLower(field.Default) != strings.ToLower(fixedOldDefault) || strings.ToLower(field.Null) != strings.ToLower(fixedOldNull) || strings.ToLower(oldField.After) != strings.ToLower(prevFieldId) || strings.ToLower(oldComments[field.Name]) != strings.ToLower(field.Comment) {
|
||
//fmt.Println(" > > > > ", u.JsonP(oldField), 1111)
|
||
// `t4f34` varchar(100) COLLATE utf8mb4_general_ci COMMENT ''
|
||
// f34, varchar(100), YES, , ,
|
||
//fmt.Println(111111, u.JsonP(field), 1111)
|
||
// 为什么Desc是空？？？？
|
||
after := ""
|
||
if conn.Config.Type == "mysql" {
|
||
if oldField.After != prevFieldId {
|
||
if prevFieldId == "" {
|
||
after = " FIRST"
|
||
} else {
|
||
after = " AFTER `" + prevFieldId + "`"
|
||
}
|
||
}
|
||
}
|
||
//DROP INDEX `uk_config_key`;
|
||
//ALTER TABLE `config` RENAME COLUMN `key` TO `keyOld`;
|
||
//ALTER TABLE `config` ADD COLUMN `key` varchar(30) NULL;
|
||
//UPDATE `config` SET `key`=`keyOld`;
|
||
//ALTER TABLE `config` DROP COLUMN `keyOld`;
|
||
//CREATE INDEX `uk_config_key` ON `config` (`key`);
|
||
if conn.Config.Type == "sqlite3" {
|
||
// 不支持修改字段，所以要先创建然后复制数据再删除
|
||
|
||
// 方案一（已放弃）重新创建表实现修改
|
||
//actions = append(actions, fmt.Sprintf("CREATE TABLE `%s_temp` (\n%s\n)", table.Name, strings.Join(fieldSets, ",\n")))
|
||
//actions = append(actions, fmt.Sprintf("INSERT INTO `%s_temp` SELECT * FROM `%s`", table.Name, table.Name))
|
||
//actions = append(actions, fmt.Sprintf("DROP TABLE `%s`", table.Name))
|
||
//actions = append(actions, fmt.Sprintf("ALTER TABLE `%s_temp` RENAME TO `%s`", table.Name, table.Name))
|
||
//INSERT INTO t1_new SELECT foo, bar, baz FROM t1;
|
||
//DROP TABLE t1;
|
||
//ALTER TABLE t1_new RENAME TO t1;
|
||
|
||
// 方案二（已放弃）创建新字段复制数据后删除（部分Sqlite不支持DROP COLUMN）
|
||
//redoIndexes := make([]string, 0)
|
||
//for oldIndexName, oldIndex := range oldIndexes {
|
||
// if u.StringIn(strings.Split(oldIndex, " "), field.Name) {
|
||
// indexSql := conn.Query("SELECT `sql` FROM `sqlite_master` WHERE `type`='index' AND `name`='" + oldIndexName + "'").StringOnR1C1()
|
||
// redoIndexes = append(redoIndexes, indexSql)
|
||
// actions = append(actions, "DROP INDEX `"+oldIndexName+"`")
|
||
// }
|
||
//}
|
||
//oldPostfix := u.UniqueId()
|
||
//actions = append(actions, "ALTER TABLE `"+table.Name+"` RENAME COLUMN `"+field.Name+"` TO `d_"+field.Name+"_"+oldPostfix+"`")
|
||
//actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.Desc+after)
|
||
//actions = append(actions, "UPDATE `"+table.Name+"` SET `"+field.Name+"`=`d_"+field.Name+"_"+oldPostfix+"`")
|
||
////actions = append(actions, "ALTER TABLE `"+table.Name+"` DROP COLUMN `"+field.Name+"Old`")
|
||
//for _, redoIndex := range redoIndexes {
|
||
// actions = append(actions, redoIndex)
|
||
//}
|
||
|
||
// 方案三 不修改字段类型，Sqlite可以兼容
|
||
|
||
//actions = append(actions, "ALTER TABLE `"+table.Name+"` ADD COLUMN "+field.desc)
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "CHANGE `"+field.Name+"` "+field.desc+after)
|
||
}
|
||
}
|
||
}
|
||
|
||
if conn.Config.Type == "mysql" {
|
||
prevFieldId = field.Name
|
||
}
|
||
}
|
||
|
||
for oldFieldName := range oldFields {
|
||
if newFieldExists[oldFieldName] != true {
|
||
if conn.Config.Type == "sqlite3" {
|
||
//actions = append(actions, "ALTER TABLE `"+table.Name+"` DROP COLUMN `"+oldFieldName+"`")
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "DROP COLUMN `"+oldFieldName+"`")
|
||
}
|
||
}
|
||
}
|
||
|
||
// sqlite3 不支持添加主键
|
||
if conn.Config.Type == "mysql" {
|
||
if len(pks) > 0 && strings.ToLower(oldIndexes["PRIMARY"]) != strings.ToLower(strings.Join(pks, " ")) {
|
||
actions = append(actions, "ADD PRIMARY KEY(`"+strings.Join(pks, "`,`")+"`)")
|
||
}
|
||
}
|
||
|
||
//fmt.Println(111, u.JsonP(oldIndexes), 222 )
|
||
//fmt.Println(222, u.JsonP(keySetBy), 222 )
|
||
for keyId, keySet := range keySetBy {
|
||
if oldIndexes[keyId] == "" || strings.ToLower(oldIndexes[keyId]) != strings.ToLower(keySetFields[keyId]) {
|
||
if conn.Config.Type == "sqlite3" {
|
||
actions = append(actions, keySet)
|
||
} else if conn.Config.Type == "mysql" {
|
||
actions = append(actions, "ADD "+keySet)
|
||
}
|
||
}
|
||
}
|
||
|
||
//fmt.Println(" =>", table.Comment, "|", oldTableComment )
|
||
if conn.Config.Type == "mysql" {
|
||
if table.Comment != oldTableComment {
|
||
actions = append(actions, "COMMENT '"+table.Comment+"'")
|
||
}
|
||
}
|
||
|
||
if len(actions) == 0 {
|
||
// 不需要更新
|
||
return outSql, nil
|
||
}
|
||
|
||
tx := conn.Begin()
|
||
defer tx.CheckFinished()
|
||
if conn.Config.Type == "sqlite3" {
|
||
for _, action := range actions {
|
||
//fmt.Println(u.Dim("\t" + strings.ReplaceAll(action, "\n", "\n\t")))
|
||
outSql = append(outSql, action)
|
||
result = tx.Exec(action)
|
||
if result.Error != nil {
|
||
break
|
||
}
|
||
}
|
||
} else if conn.Config.Type == "mysql" {
|
||
sql := "ALTER TABLE `" + table.Name + "` " + strings.Join(actions, "\n,") + ";"
|
||
//fmt.Println(u.Dim("\t" + strings.ReplaceAll(sql, "\n", "\n\t")))
|
||
outSql = append(outSql, sql)
|
||
result = tx.Exec(sql)
|
||
}
|
||
if result.Error != nil {
|
||
_ = tx.Rollback()
|
||
} else {
|
||
_ = tx.Commit()
|
||
}
|
||
} else {
|
||
// 创建新表
|
||
if len(pks) > 0 {
|
||
fieldSets = append(fieldSets, "PRIMARY KEY (`"+strings.Join(pks, "`,`")+"`)")
|
||
}
|
||
|
||
indexSets := make([]string, 0) // sqlite3 额外创建索引的sql
|
||
if conn.Config.Type == "sqlite3" {
|
||
for _, indexSql := range keySetBy {
|
||
indexSets = append(indexSets, indexSql)
|
||
}
|
||
} else if conn.Config.Type == "mysql" {
|
||
for _, key := range keySets {
|
||
fieldSets = append(fieldSets, key)
|
||
}
|
||
}
|
||
|
||
sql := ""
|
||
|
||
if conn.Config.Type == "sqlite3" {
|
||
sql = fmt.Sprintf("CREATE TABLE `%s` (\n %s\n);", table.Name, strings.Join(fieldSets, ",\n "))
|
||
} else if conn.Config.Type == "mysql" {
|
||
sql = fmt.Sprintf("CREATE TABLE `%s` (\n %s\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='%s';", table.Name, strings.Join(fieldSets, ",\n "), table.Comment)
|
||
}
|
||
tx := conn.Begin()
|
||
defer tx.CheckFinished()
|
||
//fmt.Println(u.Dim("\t" + strings.ReplaceAll(sql, "\n", "\n\t")))
|
||
outSql = append(outSql, sql)
|
||
result = tx.Exec(sql)
|
||
|
||
if result.Error == nil {
|
||
if conn.Config.Type == "sqlite3" {
|
||
for _, indexSet := range indexSets {
|
||
//fmt.Println(indexSet)
|
||
//fmt.Println(u.Dim("\t" + strings.ReplaceAll(indexSet, "\n", "\n\t")))
|
||
outSql = append(outSql, indexSet)
|
||
r := tx.Exec(indexSet)
|
||
if r.Error != nil {
|
||
result = r
|
||
}
|
||
}
|
||
}
|
||
}
|
||
if result.Error != nil {
|
||
_ = tx.Rollback()
|
||
} else {
|
||
_ = tx.Commit()
|
||
}
|
||
|
||
}
|
||
|
||
if result == nil {
|
||
return outSql, nil
|
||
}
|
||
|
||
if result.Error != nil {
|
||
logger.Error(result.Error.Error())
|
||
}
|
||
|
||
return outSql, result.Error
|
||
}
|