如何实现动态生成数据库表并修改字段

Question

场景描述：
需要实现一个CMS的管理系统，其中有个模型管理。 可以根据用户设置动态生产数据表，并添加修改和删除表里的字段。由于ORM都是采用结构体的方式提前定义好字段， 如何实现这个需求？

Answer 1

原理：反射StructOf动态创建一个结构体，然后gorm.AutoMigrate动态创建表。

StructOf以前随便写的，DataType就动态创建的结构体类型，你自己参考一下，这个是数据库查询的类型创建struct，你换成表单输入创建struct再AutoMigrate。

type Table struct {
        TableName    string
        SelectFields string
        FieldNames   []string
        FieldTypes   []string
        FieldDefault []string
        FieldIsNull  []bool
        DataType     reflect.Type
    }
    
func newTable(db *sql.DB, object interface{}, name string) Table {
    // get fields
    rows, err := db.Query("SELECT COLUMN_NAME, data_type,column_default,is_nullable FROM information_schema.COLUMNS WHERE TABLE_NAME = '" + name + "' ORDER BY ordinal_position;")
    if err != nil {
        panic(err)
    }
    tb := Table{TableName: name}
    var field, typ, coldf, isnil string
    for rows.Next() {
        rows.Scan(&field, &typ, &coldf, &isnil)
        tb.FieldNames = append(tb.FieldNames, field)
        tb.FieldTypes = append(tb.FieldTypes, tabletypes[typ])
        tb.FieldDefault = append(tb.FieldDefault, coldf)
        tb.FieldIsNull = append(tb.FieldIsNull, isnil == "YES")
    }
    tb.SelectFields = `"` + strings.Join(tb.FieldNames, `","`) + `"`

    // get struct type
    iValue := reflect.ValueOf(object)
    for iValue.Kind() == reflect.Ptr || iValue.Kind() == reflect.Interface {
        iValue = iValue.Elem()
    }
    if iValue.Kind() == reflect.Struct || iValue.Kind() == reflect.Map {
        tb.DataType = iValue.Type()
    } else {
        var sf []reflect.StructField = make([]reflect.StructField, len(tb.FieldNames))
        for i := 0; i < len(tb.FieldNames); i++ {
            name := strings.ToLower(tb.FieldNames[i])
            sf[i] = reflect.StructField{
                Name: strings.ToTitle(name),
                Type: tableReflectTypes[tb.FieldTypes[i]],
                Tag:  reflect.StructTag(fmt.Sprintf("alias:\"%s\" json:\"%s\"", name, name)),
            }
        }
        tb.DataType = reflect.StructOf(sf)
    }
    return tb
}

var tabletypes = map[string]string{
    "boolean":                     "Bool",
    "integer":                     "Int",
    "real":                        "Float",
    "character":                   "Byte",
    "character varying":           "String",
    "text":                        "String",
    "date":                        "Date",
    "time without time zone":      "Time",
    "timestamp without time zone": "DateTime",
}

var tableReflectTypes = map[string]reflect.Type{
    "Bool":     reflect.TypeOf((*bool)(nil)).Elem(),
    "Int":      reflect.TypeOf((*int)(nil)).Elem(),
    "Float":    reflect.TypeOf((*float64)(nil)).Elem(),
    "Byte":     reflect.TypeOf((*byte)(nil)).Elem(),
    "String":   reflect.TypeOf((*string)(nil)).Elem(),
    "Date":     reflect.TypeOf((*time.Time)(nil)).Elem(),
    "Time":     reflect.TypeOf((*time.Time)(nil)).Elem(),
    "DateTime": reflect.TypeOf((*time.Time)(nil)).Elem(),
}