通过数据库表自动生成golang model

之所以不写成struct，是不想让util工具依赖于某个包，复制即用。

仅支持postgres

package main

import (
	"bufio"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/postgres"
	"strings"
)

var FindColumnsSql = `
SELECT
    a.attnum AS column_number,
    a.attname AS column_name,
    --format_type(a.atttypid, a.atttypmod) AS column_type,
    a.attnotnull AS not_null,
    COALESCE(pg_get_expr(ad.adbin, ad.adrelid), '') AS default_value,
    COALESCE(ct.contype = 'p', false) AS  is_primary_key,
    CASE
        WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])
          AND EXISTS (
             SELECT 1 FROM pg_attrdef ad
             WHERE  ad.adrelid = a.attrelid
             AND    ad.adnum   = a.attnum
             AND    ad.adsrc = 'nextval('''
                || (pg_get_serial_sequence (a.attrelid::regclass::text
                                          , a.attname))::regclass
                || '''::regclass)'
             )
            THEN CASE a.atttypid
                    WHEN 'int'::regtype  THEN 'serial'
                    WHEN 'int8'::regtype THEN 'bigserial'
                    WHEN 'int2'::regtype THEN 'smallserial'
                 END
        WHEN a.atttypid = ANY ('{uuid}'::regtype[]) AND COALESCE(pg_get_expr(ad.adbin, ad.adrelid), '') != ''
            THEN 'autogenuuid'
        ELSE format_type(a.atttypid, a.atttypmod)
    END AS column_type
FROM pg_attribute a
JOIN ONLY pg_class c ON c.oid = a.attrelid
JOIN ONLY pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint ct ON ct.conrelid = c.oid
AND a.attnum = ANY(ct.conkey) AND ct.contype = 'p'
LEFT JOIN pg_attrdef ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
WHERE a.attisdropped = false
AND n.nspname = 'public'
AND c.relname = ?
AND a.attnum > 0
ORDER BY a.attnum
`
var findTablesSql = `
SELECT
c.relkind AS type,
c.relname AS table_name
FROM pg_class c
JOIN ONLY pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind = 'r'
ORDER BY c.relname
`

type Table struct {
	TableName string `gorm:"column:table_name"` //table name
}
type Column struct {
	ColumnNumber int    `gorm:"column_number"` // column index
	ColumnName   string `gorm:"column_name"`   // column_name
	ColumnType   string `gorm:"column_type"`   // column_type
}

// dataSource = fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=%s password=%s","localhost", "5432", "postgres", "test", "disable", "123")
func FindTables(dataSource string) []Table {
	defer func() {
		if e := recover(); e != nil {
			fmt.Println(fmt.Sprintf("recover from a fatal error : %v", e))
		}
	}()
	db, err := gorm.Open("postgres", dataSource)
	db.SingularTable(true)
	db.LogMode(true)
	if err != nil {
		panic(err)
	}
	var tables = make([]Table, 0, 10)
	db.Raw(findTablesSql).Find(&tables)
	return tables
}

// find columns' property by specific dataSource and table name
func FindColumns(dataSource string, tableName string) []Column {
	defer func() {
		if e := recover(); e != nil {
			fmt.Println(fmt.Sprintf("recover from a fatal error : %v", e))
		}
	}()
	db, err := gorm.Open("postgres", dataSource)
	db.SingularTable(true)
	db.LogMode(true)
	if err != nil {
		panic(err)
	}
	var columns = make([]Column, 0, 10)
	db.Raw(FindColumnsSql, tableName).Find(&columns)
	return columns
}

// generate go model y specific the dataSource and table name
func TableToStruct(dataSource string, tableName string) string {

	columnString := ""
	tmp := ""
	columns := FindColumns(dataSource, tableName)
	for _, column := range columns {

		tmp = fmt.Sprintf("    %s  %s\n", column.ColumnName, typeConvert(column.ColumnType))
		columnString = columnString + tmp
	}

	rs := fmt.Sprintf("type %s struct{\n%s}", UnderLineToHump(HumpToUnderLine(tableName)), columnString)
	return rs
}

// AddJSONFormGormTag 添加json格式
func AddJSONFormGormTag(in string) string {
	var result string
	scanner := bufio.NewScanner(strings.NewReader(in))
	var oldLineTmp = ""
	var lineTmp = ""
	var propertyTmp = ""
	var seperateArr []string
	for scanner.Scan() {
		oldLineTmp = scanner.Text()
		lineTmp = strings.Trim(scanner.Text(), " ")
		if strings.Contains(lineTmp, "{") || strings.Contains(lineTmp, "}") {
			result = result + oldLineTmp + "\n"
			continue
		}
		seperateArr = Split(lineTmp, " ")
		// 接口或者父类声明不参与tag, 自带tag不参与tag
		if len(seperateArr) == 1 || len(seperateArr) == 3 {
			continue
		}
		propertyTmp = HumpToUnderLine(seperateArr[0])
		oldLineTmp = oldLineTmp + fmt.Sprintf("    `gorm:\"column:%s\" json:\"%s\" form:\"%s\"`", propertyTmp, propertyTmp, propertyTmp)
		result = result + oldLineTmp + "\n"
	}
	return result
}

// Split 增强型Split，对  a,,,,,,,b,,c     以","进行切割成[a,b,c]
func Split(s string, sub string) []string {
	var rs = make([]string, 0, 20)
	tmp := ""
	Split2(s, sub, &tmp, &rs)
	return rs
}

// Split2 附属于Split，可独立使用
func Split2(s string, sub string, tmp *string, rs *[]string) {
	s = strings.Trim(s, sub)
	if !strings.Contains(s, sub) {
		*tmp = s
		*rs = append(*rs, *tmp)
		return
	}
	for i := range s {
		if string(s[i]) == sub {
			*tmp = s[:i]
			*rs = append(*rs, *tmp)
			s = s[i+1:]
			Split2(s, sub, tmp, rs)
			return
		}
	}
}

// FindUpperElement 找到字符串中大写字母的列表,附属于HumpToUnderLine
func FindUpperElement(s string) []string {
	var rs = make([]string, 0, 10)
	for i := range s {
		if s[i] >= 65 && s[i] <= 90 {
			rs = append(rs, string(s[i]))
		}
	}
	return rs
}

// HumpToUnderLine 驼峰转下划线
func HumpToUnderLine(s string) string {
	if s == "ID" {
		return "id"
	}
	var rs string
	elements := FindUpperElement(s)
	for _, e := range elements {
		s = strings.Replace(s, e, "_"+strings.ToLower(e), -1)
	}
	rs = strings.Trim(s, " ")
	rs = strings.Trim(rs, "\t")
	return strings.Trim(rs, "_")
}

// UnderLineToHump 下划线转驼峰
func UnderLineToHump(s string) string {
	arr := strings.Split(s, "_")
	for i, v := range arr {
		arr[i] = strings.ToUpper(string(v[0])) + string(v[1:])
	}
	return strings.Join(arr, "")
}

// 类型转换pg->go
func typeConvert(s string) string {
	if strings.Contains(s, "char") || in(s, []string{
		"text",
	}) {
		return "string"
	}
	if in(s, []string{"bigint", "bigserial", "integer", "smallint", "serial", "big serial"}) {
		return "int"
	}
	if in(s, []string{"numeric", "decimal", "real"}) {
		return "decimal.Decimal"
	}
	if in(s, []string{"bytea"}) {
		return "[]byte"
	}
	if strings.Contains(s, "time") || in(s, []string{"date"}) {
		return "time.Time"
	}
	if in(s, []string{"bigint", "bigserial", ""}) {
		return "json.RawMessage"
	}
	return "interface{}"
}
// 包含
func in(s string, arr []string) bool {
	for _, v := range arr {
		if v == s {
			return true
		}
	}
	return false
}

func main() {
	dataSouce := fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=%s password=%s", "localhost", "5432", "postgres", "test", "disable", "123")
	// 获取指定数据库内所有的表名
	tables := FindTables(dataSouce)
	fmt.Println(tables)

	// 获取指定数据库指定表内所有的列属性
	tableName := "football_match"
	columns := FindColumns(dataSouce, tableName)
	fmt.Println(columns)

	// 指定数据源和表，生成go结构体
	goModel := TableToStruct(dataSouce, tableName)
	fmt.Println(goModel)
	// 生成带tag的结构体
	goModelWithTag := AddJSONFormGormTag(goModel)
	fmt.Println(goModelWithTag)
}

结果:(对齐需要ide自带go fmt,ctrl alt L,或者执行go fmt main.go)

type FootballMatch struct{
    Id  string
    ExternalId  string
    Code  string
    Color  string
    Weekday  int
    StartDate  time.Time
    StartAt  time.Time
    LeagueShort  string
    LeagueLong  string
    HomeTeam  string
    AwayTeam  string
    State  int
    SpfPassType  int
    RqspfPassType  int
    BfPassType  int
    ZjqPassType  int
    BqcPassType  int
    RqCount  string
    SpfResult  string
    RqspfResult  string
    BfResult  string
    ZjqResult  string
    BqcResult  string
    HalfScore  string
    FullScore  string
    CreatedAt  time.Time
    UpdatedAt  time.Time
    F  string
}
type FootballMatch struct{
    Id  string    `gorm:"column:id" json:"id" form:"id"`
    ExternalId  string    `gorm:"column:external_id" json:"external_id" form:"external_id"`
    Code  string    `gorm:"column:code" json:"code" form:"code"`
    Color  string    `gorm:"column:color" json:"color" form:"color"`
    Weekday  int    `gorm:"column:weekday" json:"weekday" form:"weekday"`
    StartDate  time.Time    `gorm:"column:start_date" json:"start_date" form:"start_date"`
    StartAt  time.Time    `gorm:"column:start_at" json:"start_at" form:"start_at"`
    LeagueShort  string    `gorm:"column:league_short" json:"league_short" form:"league_short"`
    LeagueLong  string    `gorm:"column:league_long" json:"league_long" form:"league_long"`
    HomeTeam  string    `gorm:"column:home_team" json:"home_team" form:"home_team"`
    AwayTeam  string    `gorm:"column:away_team" json:"away_team" form:"away_team"`
    State  int    `gorm:"column:state" json:"state" form:"state"`
    SpfPassType  int    `gorm:"column:spf_pass_type" json:"spf_pass_type" form:"spf_pass_type"`
    RqspfPassType  int    `gorm:"column:rqspf_pass_type" json:"rqspf_pass_type" form:"rqspf_pass_type"`
    BfPassType  int    `gorm:"column:bf_pass_type" json:"bf_pass_type" form:"bf_pass_type"`
    ZjqPassType  int    `gorm:"column:zjq_pass_type" json:"zjq_pass_type" form:"zjq_pass_type"`
    BqcPassType  int    `gorm:"column:bqc_pass_type" json:"bqc_pass_type" form:"bqc_pass_type"`
    RqCount  string    `gorm:"column:rq_count" json:"rq_count" form:"rq_count"`
    SpfResult  string    `gorm:"column:spf_result" json:"spf_result" form:"spf_result"`
    RqspfResult  string    `gorm:"column:rqspf_result" json:"rqspf_result" form:"rqspf_result"`
    BfResult  string    `gorm:"column:bf_result" json:"bf_result" form:"bf_result"`
    ZjqResult  string    `gorm:"column:zjq_result" json:"zjq_result" form:"zjq_result"`
    BqcResult  string    `gorm:"column:bqc_result" json:"bqc_result" form:"bqc_result"`
    HalfScore  string    `gorm:"column:half_score" json:"half_score" form:"half_score"`
    FullScore  string    `gorm:"column:full_score" json:"full_score" form:"full_score"`
    CreatedAt  time.Time    `gorm:"column:created_at" json:"created_at" form:"created_at"`
    UpdatedAt  time.Time    `gorm:"column:updated_at" json:"updated_at" form:"updated_at"`
    F  string    `gorm:"column:f" json:"f" form:"f"`
}