func (db *sqlite3) GetColumns(tableName string) ([]string, map[string]*core.Column, error) { args := []interface{}{tableName} s := "SELECT sql FROM sqlite_master WHERE type='table' and name = ?" db.LogSQL(s, args) rows, err := db.DB().Query(s, args...) if err != nil { return nil, nil, err } defer rows.Close() var name string for rows.Next() { err = rows.Scan(&name) if err != nil { return nil, nil, err } break } if name == "" { return nil, nil, errors.New("no table named " + tableName) } nStart := strings.Index(name, "(") nEnd := strings.LastIndex(name, ")") reg := regexp.MustCompile(`[^\(,\)]*(\([^\(]*\))?`) colCreates := reg.FindAllString(name[nStart+1:nEnd], -1) cols := make(map[string]*core.Column) colSeq := make([]string, 0) for _, colStr := range colCreates { reg = regexp.MustCompile(`,\s`) colStr = reg.ReplaceAllString(colStr, ",") fields := strings.Fields(strings.TrimSpace(colStr)) col := new(core.Column) col.Indexes = make(map[string]bool) col.Nullable = true col.DefaultIsEmpty = true for idx, field := range fields { if idx == 0 { col.Name = strings.Trim(field, "`[] ") continue } else if idx == 1 { col.SQLType = core.SQLType{field, 0, 0} } switch field { case "PRIMARY": col.IsPrimaryKey = true case "AUTOINCREMENT": col.IsAutoIncrement = true case "NULL": if fields[idx-1] == "NOT" { col.Nullable = false } else { col.Nullable = true } case "DEFAULT": col.Default = fields[idx+1] col.DefaultIsEmpty = false } } if !col.SQLType.IsNumeric() && !col.DefaultIsEmpty { col.Default = "'" + col.Default + "'" } cols[col.Name] = col colSeq = append(colSeq, col.Name) } return colSeq, cols, nil }
func (db *oracle) GetColumns(tableName string) ([]string, map[string]*core.Column, error) { args := []interface{}{tableName} s := "SELECT column_name,data_default,data_type,data_length,data_precision,data_scale," + "nullable FROM USER_TAB_COLUMNS WHERE table_name = :1" rows, err := db.DB().Query(s, args...) if db.Logger != nil { db.Logger.Info("[sql]", s, args) } if err != nil { return nil, nil, err } defer rows.Close() cols := make(map[string]*core.Column) colSeq := make([]string, 0) for rows.Next() { col := new(core.Column) col.Indexes = make(map[string]bool) var colName, colDefault, nullable, dataType, dataPrecision, dataScale *string var dataLen int err = rows.Scan(&colName, &colDefault, &dataType, &dataLen, &dataPrecision, &dataScale, &nullable) if err != nil { return nil, nil, err } col.Name = strings.Trim(*colName, `" `) if colDefault != nil { col.Default = *colDefault col.DefaultIsEmpty = false } if *nullable == "Y" { col.Nullable = true } else { col.Nullable = false } var ignore bool var dt string var len1, len2 int dts := strings.Split(*dataType, "(") dt = dts[0] if len(dts) > 1 { lens := strings.Split(dts[1][:len(dts[1])-1], ",") if len(lens) > 1 { len1, _ = strconv.Atoi(lens[0]) len2, _ = strconv.Atoi(lens[1]) } else { len1, _ = strconv.Atoi(lens[0]) } } switch dt { case "VARCHAR2": col.SQLType = core.SQLType{core.Varchar, len1, len2} case "NVARCHAR2": col.SQLType = core.SQLType{core.NVarchar, len1, len2} case "TIMESTAMP WITH TIME ZONE": col.SQLType = core.SQLType{core.TimeStampz, 0, 0} case "NUMBER": col.SQLType = core.SQLType{core.Double, len1, len2} case "LONG", "LONG RAW": col.SQLType = core.SQLType{core.Text, 0, 0} case "RAW": col.SQLType = core.SQLType{core.Binary, 0, 0} case "ROWID": col.SQLType = core.SQLType{core.Varchar, 18, 0} case "AQ$_SUBSCRIBERS": ignore = true default: col.SQLType = core.SQLType{strings.ToUpper(dt), len1, len2} } if ignore { continue } if _, ok := core.SqlTypes[col.SQLType.Name]; !ok { return nil, nil, errors.New(fmt.Sprintf("unkonw colType %v %v", *dataType, col.SQLType)) } col.Length = dataLen if col.SQLType.IsText() || col.SQLType.IsTime() { if !col.DefaultIsEmpty { col.Default = "'" + col.Default + "'" } } cols[col.Name] = col colSeq = append(colSeq, col.Name) } return colSeq, cols, nil }
func (db *tidb) GetColumns(tableName string) ([]string, map[string]*core.Column, error) { args := []interface{}{db.DbName, tableName} s := "SELECT `COLUMN_NAME`, `IS_NULLABLE`, `COLUMN_DEFAULT`, `COLUMN_TYPE`," + " `COLUMN_KEY`, `EXTRA` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?" rows, err := db.DB().Query(s, args...) db.LogSQL(s, args) if err != nil { return nil, nil, err } defer rows.Close() cols := make(map[string]*core.Column) colSeq := make([]string, 0) for rows.Next() { col := new(core.Column) col.Indexes = make(map[string]int) var columnName, isNullable, colType, colKey, extra string var colDefault *string err = rows.Scan(&columnName, &isNullable, &colDefault, &colType, &colKey, &extra) if err != nil { return nil, nil, err } col.Name = strings.Trim(columnName, "` ") if "YES" == isNullable { col.Nullable = true } if colDefault != nil { col.Default = *colDefault if col.Default == "" { col.DefaultIsEmpty = true } } cts := strings.Split(colType, "(") colName := cts[0] colType = strings.ToUpper(colName) var len1, len2 int if len(cts) == 2 { idx := strings.Index(cts[1], ")") if colType == core.Enum && cts[1][0] == '\'' { //enum options := strings.Split(cts[1][0:idx], ",") col.EnumOptions = make(map[string]int) for k, v := range options { v = strings.TrimSpace(v) v = strings.Trim(v, "'") col.EnumOptions[v] = k } } else if colType == core.Set && cts[1][0] == '\'' { options := strings.Split(cts[1][0:idx], ",") col.SetOptions = make(map[string]int) for k, v := range options { v = strings.TrimSpace(v) v = strings.Trim(v, "'") col.SetOptions[v] = k } } else { lens := strings.Split(cts[1][0:idx], ",") len1, err = strconv.Atoi(strings.TrimSpace(lens[0])) if err != nil { return nil, nil, err } if len(lens) == 2 { len2, err = strconv.Atoi(lens[1]) if err != nil { return nil, nil, err } } } } if colType == "FLOAT UNSIGNED" { colType = "FLOAT" } col.Length = len1 col.Length2 = len2 if _, ok := core.SqlTypes[colType]; ok { col.SQLType = core.SQLType{colType, len1, len2} } else { return nil, nil, errors.New(fmt.Sprintf("unkonw colType %v", colType)) } if colKey == "PRI" { col.IsPrimaryKey = true } if colKey == "UNI" { //col.is } if extra == "auto_increment" { col.IsAutoIncrement = true } if col.SQLType.IsText() || col.SQLType.IsTime() { if col.Default != "" { col.Default = "'" + col.Default + "'" } else { if col.DefaultIsEmpty { col.Default = "''" } } } cols[col.Name] = col colSeq = append(colSeq, col.Name) } return colSeq, cols, nil }