github.com/artisanhe/tools@v1.0.1-0.20210607022958-19a8fef2eb04/sqlx/schemas.go (about)

     1  package sqlx
     2  
     3  import (
     4  	"database/sql"
     5  	"strings"
     6  	"time"
     7  
     8  	"github.com/artisanhe/tools/sqlx/builder"
     9  	"github.com/artisanhe/tools/sqlx/data_type"
    10  )
    11  
    12  func toInterfaces(list ...string) []interface{} {
    13  	s := make([]interface{}, len(list))
    14  	for i, v := range list {
    15  		s[i] = v
    16  	}
    17  	return s
    18  }
    19  
    20  func DBFromInformationSchema(db *DB, dbName string, tableNames ...string) *Database {
    21  	d := NewDatabase(dbName)
    22  
    23  	schema := SchemaDatabase.T(&Schema{})
    24  
    25  	schemaList := make([]Schema, 0)
    26  	errForSchema := db.Do(schema.Select().Where(schema.F("SCHEMA_NAME").Eq(d.Name)).Limit(1)).Scan(&schemaList).Err()
    27  	if errForSchema != nil {
    28  		return nil
    29  	}
    30  	if len(schemaList) == 0 {
    31  		return nil
    32  	}
    33  
    34  	tableColumnSchema := SchemaDatabase.T(&ColumnSchema{})
    35  	columnSchemaList := make([]ColumnSchema, 0)
    36  
    37  	err := db.Do(
    38  		tableColumnSchema.Select().
    39  			Where(
    40  				tableColumnSchema.F("TABLE_SCHEMA").Eq(d.Name).
    41  					And(tableColumnSchema.F("TABLE_NAME").In(toInterfaces(tableNames...)...)),
    42  			),
    43  	).
    44  		Scan(&columnSchemaList).
    45  		Err()
    46  
    47  	if err != nil {
    48  		panic(err)
    49  	}
    50  
    51  	for _, columnSchema := range columnSchemaList {
    52  		table := d.Table(columnSchema.TABLE_NAME)
    53  		if table == nil {
    54  			table = builder.T(d.Database, columnSchema.TABLE_NAME)
    55  			d.Database.Register(table)
    56  		}
    57  		col := builder.Col(table, columnSchema.COLUMN_NAME)
    58  		col.ColumnType = *columnSchema.ToColumnType()
    59  		table.Columns.Add(col)
    60  	}
    61  
    62  	if tableColumnSchema.Columns.Len() != 0 {
    63  		tableIndexSchema := SchemaDatabase.T(&IndexSchema{})
    64  
    65  		indexList := make([]IndexSchema, 0)
    66  
    67  		err = db.Do(
    68  			tableIndexSchema.Select().
    69  				Where(
    70  					tableIndexSchema.F("TABLE_SCHEMA").Eq(d.Name).
    71  						And(tableIndexSchema.F("TABLE_NAME").In(toInterfaces(tableNames...)...)),
    72  				).
    73  				OrderAscBy(tableIndexSchema.F("INDEX_NAME")).
    74  				OrderAscBy(tableIndexSchema.F("SEQ_IN_INDEX")),
    75  		).
    76  			Scan(&indexList).
    77  			Err()
    78  
    79  		if err != nil {
    80  			panic(err)
    81  		}
    82  
    83  		for _, indexSchema := range indexList {
    84  			table := d.Table(indexSchema.TABLE_NAME)
    85  
    86  			key, exists := table.Keys.Key(indexSchema.INDEX_NAME)
    87  			if !exists {
    88  				if indexSchema.INDEX_NAME == string(builder.PRIMARY) {
    89  					key = builder.PrimaryKey()
    90  				} else if indexSchema.NON_UNIQUE == 1 {
    91  					key = builder.Index(indexSchema.INDEX_NAME)
    92  				} else {
    93  					key = builder.UniqueIndex(indexSchema.INDEX_NAME)
    94  				}
    95  				table.Keys.Add(key)
    96  			}
    97  
    98  			key.WithCols(table.Col(indexSchema.COLUMN_NAME))
    99  		}
   100  	}
   101  
   102  	return d
   103  }
   104  
   105  var SchemaDatabase = NewDatabase("INFORMATION_SCHEMA")
   106  
   107  func init() {
   108  	SchemaDatabase.Register(&Schema{})
   109  	SchemaDatabase.Register(&TableSchema{})
   110  	SchemaDatabase.Register(&ColumnSchema{})
   111  	SchemaDatabase.Register(&IndexSchema{})
   112  }
   113  
   114  func (columnSchema ColumnSchema) ToColumnType() *data_type.ColumnType {
   115  	columnType, _ := data_type.ParseColumnType(columnSchema.COLUMN_TYPE)
   116  	columnType.NotNull = columnSchema.IS_NULLABLE == "NO"
   117  	if columnSchema.EXTRA == "auto_increment" {
   118  		columnType.AutoIncrement = true
   119  	}
   120  	if strings.HasPrefix(columnSchema.EXTRA, "on update CURRENT_TIMESTAMP") {
   121  		columnType.OnUpdateByCurrentTimestamp = true
   122  	}
   123  
   124  	if columnSchema.CHARACTER_SET_NAME.Valid {
   125  		columnType.Charset = columnSchema.CHARACTER_SET_NAME.String
   126  	}
   127  
   128  	if columnSchema.COLLATION_NAME.Valid {
   129  		if collationDefaults[columnType.Charset] != columnSchema.COLLATION_NAME.String {
   130  			columnType.Collate = columnSchema.COLLATION_NAME.String
   131  		}
   132  	}
   133  
   134  	if columnSchema.COLUMN_DEFAULT.Valid {
   135  		columnType.HasDefault = true
   136  		columnType.Default = columnSchema.COLUMN_DEFAULT.String
   137  	} else if columnSchema.IS_NULLABLE == "YES" {
   138  		columnType.HasDefault = true
   139  		columnType.Default = "NULL"
   140  	}
   141  
   142  	columnType.Comment = columnSchema.COLUMN_COMMENT
   143  
   144  	return columnType
   145  }
   146  
   147  var collationDefaults = map[string]string{
   148  	"utf8mb4":  "utf8mb4_general_ci",
   149  	"utf8":     "utf8_general_ci",
   150  	"utf32":    "utf32_general_ci",
   151  	"utf16le":  "utf16le_general_ci",
   152  	"utf16":    "utf16_general_ci",
   153  	"ujis":     "ujis_japanese_ci",
   154  	"ucs2":     "ucs2_general_ci",
   155  	"tis620":   "tis620_thai_ci",
   156  	"swe7":     "swe7_swedish_ci",
   157  	"sjis":     "sjis_japanese_ci",
   158  	"macroman": "macroman_general_ci",
   159  	"macce":    "macce_general_ci",
   160  	"latin7":   "latin7_general_ci",
   161  	"latin5":   "latin5_turkish_ci",
   162  	"latin2":   "latin2_general_ci",
   163  	"latin1":   "latin1_swedish_ci",
   164  	"koi8u":    "koi8u_general_ci",
   165  	"koi8r":    "koi8r_general_ci",
   166  	"keybcs2":  "keybcs2_general_ci",
   167  	"hp8":      "hp8_english_ci",
   168  	"hebrew":   "hebrew_general_ci",
   169  	"greek":    "greek_general_ci",
   170  	"geostd8":  "geostd8_general_ci",
   171  	"gbk":      "gbk_chinese_ci",
   172  	"gb2312":   "gb2312_chinese_ci",
   173  	"euckr":    "euckr_korean_ci",
   174  	"eucjpms":  "eucjpms_japanese_ci",
   175  	"dec8":     "dec8_swedish_ci",
   176  	"cp932":    "cp932_japanese_ci",
   177  	"cp866":    "cp866_general_ci",
   178  	"cp852":    "cp852_general_ci",
   179  	"cp850":    "cp850_general_ci",
   180  	"cp1257":   "cp1257_general_ci",
   181  	"cp1256":   "cp1256_general_ci",
   182  	"cp1251":   "cp1251_general_ci",
   183  	"cp1250":   "cp1250_general_ci",
   184  	"binary":   "binary",
   185  	"big5":     "big5_chinese_ci",
   186  	"ascii":    "ascii_general_ci",
   187  	"armscii8": "armscii8_general_ci",
   188  }
   189  
   190  type Schema struct {
   191  	SCHEMA_NAME string `db:"SCHEMA_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   192  }
   193  
   194  func (schema Schema) TableName() string {
   195  	return "SCHEMATA"
   196  }
   197  
   198  type TableSchema struct {
   199  	TABLE_SCHEMA    string         `db:"TABLE_SCHEMA" sql:"varchar(64) NOT NULL DEFAULT ''"`
   200  	TABLE_NAME      string         `db:"TABLE_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   201  	TABLE_TYPE      string         `db:"TABLE_TYPE" sql:"varchar(64) NOT NULL DEFAULT ''"`
   202  	ENGINE          sql.NullString `db:"ENGINE" sql:"varchar(64) DEFAULT NULL"`
   203  	VERSION         sql.NullInt64  `db:"VERSION" sql:"bigint(21) unsigned DEFAULT NULL"`
   204  	ROW_FORMAT      sql.NullString `db:"ROW_FORMAT" sql:"varchar(10) DEFAULT NULL"`
   205  	TABLE_ROWS      sql.NullInt64  `db:"TABLE_ROWS" sql:"bigint(21) unsigned DEFAULT NULL"`
   206  	AVG_ROW_LENGTH  sql.NullInt64  `db:"AVG_ROW_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   207  	DATA_LENGTH     sql.NullInt64  `db:"DATA_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   208  	MAX_DATA_LENGTH sql.NullInt64  `db:"MAX_DATA_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   209  	INDEX_LENGTH    sql.NullInt64  `db:"INDEX_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   210  	DATA_FREE       sql.NullInt64  `db:"DATA_FREE" sql:"bigint(21) unsigned DEFAULT NULL"`
   211  	AUTO_INCREMENT  sql.NullString `db:"AUTO_INCREMENT" sql:"bigint(21) unsigned DEFAULT NULL"`
   212  	CREATE_TIME     time.Time      `db:"CREATE_TIME" sql:"datetime DEFAULT NULL"`
   213  	UPDATE_TIME     time.Time      `db:"UPDATE_TIME" sql:"datetime DEFAULT NULL"`
   214  	CHECK_TIME      time.Time      `db:"CHECK_TIME" sql:"datetime DEFAULT NULL"`
   215  	TABLE_COLLATION sql.NullString `db:"TABLE_COLLATION" sql:"varchar(32) DEFAULT NULL"`
   216  	CHECKSUM        sql.NullInt64  `db:"CHECKSUM" sql:"bigint(21) unsigned DEFAULT NULL"`
   217  	CREATE_OPTIONS  sql.NullString `db:"CREATE_OPTIONS" sql:"varchar(255) DEFAULT NULL"`
   218  	TABLE_COMMENT   string         `db:"TABLE_COMMENT" sql:"varchar(2048) NOT NULL DEFAULT ''"`
   219  }
   220  
   221  func (tableSchema TableSchema) TableName() string {
   222  	return "TABLES"
   223  }
   224  
   225  type ColumnSchema struct {
   226  	TABLE_SCHEMA             string         `db:"TABLE_SCHEMA" sql:"varchar(64) NOT NULL DEFAULT ''"`
   227  	TABLE_NAME               string         `db:"TABLE_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   228  	COLUMN_NAME              string         `db:"COLUMN_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   229  	ORDINAL_POSITION         int32          `db:"ORDINAL_POSITION" sql:"bigint(21) unsigned NOT NULL DEFAULT '0'"`
   230  	COLUMN_DEFAULT           sql.NullString `db:"COLUMN_DEFAULT" sql:"longtext"`
   231  	IS_NULLABLE              string         `db:"IS_NULLABLE" sql:"varchar(3) NOT NULL DEFAULT ''"`
   232  	DATA_TYPE                string         `db:"DATA_TYPE" sql:"varchar(64) NOT NULL DEFAULT ''"`
   233  	CHARACTER_MAXIMUM_LENGTH sql.NullInt64  `db:"CHARACTER_MAXIMUM_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   234  	CHARACTER_OCTET_LENGTH   sql.NullInt64  `db:"CHARACTER_OCTET_LENGTH" sql:"bigint(21) unsigned DEFAULT NULL"`
   235  	NUMERIC_PRECISION        sql.NullInt64  `db:"NUMERIC_PRECISION" sql:"bigint(21) unsigned DEFAULT NULL"`
   236  	NUMERIC_SCALE            sql.NullInt64  `db:"NUMERIC_SCALE" sql:"bigint(21) unsigned DEFAULT NULL"`
   237  	DATETIME_PRECISION       sql.NullInt64  `db:"DATETIME_PRECISION" sql:"bigint(21) unsigned DEFAULT NULL"`
   238  	CHARACTER_SET_NAME       sql.NullString `db:"CHARACTER_SET_NAME" sql:"varchar(32) DEFAULT NULL"`
   239  	COLLATION_NAME           sql.NullString `db:"COLLATION_NAME" sql:"varchar(32) DEFAULT NULL"`
   240  	COLUMN_TYPE              string         `db:"COLUMN_TYPE" sql:"longtext NOT NULL"`
   241  	COLUMN_KEY               string         `db:"COLUMN_KEY" sql:"varchar(3) NOT NULL DEFAULT ''"`
   242  	EXTRA                    string         `db:"EXTRA" sql:"varchar(30) NOT NULL DEFAULT ''"`
   243  	PRIVILEGES               string         `db:"PRIVILEGES" sql:"varchar(80) NOT NULL DEFAULT ''"`
   244  	COLUMN_COMMENT           string         `db:"COLUMN_COMMENT" sql:"varchar(1024) NOT NULL DEFAULT ''"`
   245  }
   246  
   247  func (columnSchema ColumnSchema) TableName() string {
   248  	return "COLUMNS"
   249  }
   250  
   251  type IndexSchema struct {
   252  	TABLE_SCHEMA  string         `db:"TABLE_SCHEMA" sql:"varchar(64) NOT NULL DEFAULT ''"`
   253  	TABLE_NAME    string         `db:"TABLE_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   254  	NON_UNIQUE    int32          `db:"NON_UNIQUE" sql:"bigint(1) NOT NULL DEFAULT '0'"`
   255  	INDEX_SCHEMA  string         `db:"INDEX_SCHEMA" sql:"varchar(64) NOT NULL DEFAULT ''"`
   256  	INDEX_NAME    string         `db:"INDEX_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   257  	SEQ_IN_INDEX  int32          `db:"SEQ_IN_INDEX" sql:"bigint(2) NOT NULL DEFAULT '0'"`
   258  	COLUMN_NAME   string         `db:"COLUMN_NAME" sql:"varchar(64) NOT NULL DEFAULT ''"`
   259  	COLLATION     sql.NullString `db:"COLLATION" sql:"varchar(1) DEFAULT NULL"`
   260  	CARDINALITY   sql.NullInt64  `db:"CARDINALITY" sql:"bigint(21) DEFAULT NULL"`
   261  	SUB_PART      sql.NullInt64  `db:"SUB_PART" sql:"bigint(3) DEFAULT NULL"`
   262  	PACKED        sql.NullString `db:"PACKED" sql:"varchar(10) DEFAULT NULL"`
   263  	NULLABLE      string         `db:"NULLABLE" sql:"varchar(3) NOT NULL DEFAULT ''"`
   264  	INDEX_TYPE    string         `db:"INDEX_TYPE" sql:"varchar(16) NOT NULL DEFAULT ''"`
   265  	COMMENT       string         `db:"COMMENT" sql:"varchar(16) DEFAULT NULL"`
   266  	INDEX_COMMENT string         `db:"INDEX_COMMENT" sql:"varchar(1024) NOT NULL DEFAULT ''"`
   267  }
   268  
   269  func (indexSchema IndexSchema) TableName() string {
   270  	return "STATISTICS"
   271  }