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
|
|||
|
|
}
|