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 }