func (dc *sqlite3Dialect) SchemaIndexQuery(dbName, tableName string) ([]*base.Index, error) {
indexes := []*base.Index{}
q := "SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ?"
rows, err := dc.Base().Conn.Query(q, tableName)
if err != nil {
return indexes, err
}
defer rows.Close()
for rows.Next() {
var sql string
if err = rows.Scan(&sql); err != nil {
return indexes, err
}
if sql == "" {
continue
}
mat := regIndexSql.FindStringSubmatch(sql)
if len(mat) != 4 {
continue
}
//
indexName := strings.TrimSpace(mat[1])
if strings.HasPrefix(indexName, tableName+"_") {
indexName = indexName[len(tableName)+1:]
}
if strings.HasSuffix(indexName, "_idx") {
indexName = indexName[0 : len(indexName)-4]
}
//
indexType := base.IndexTypeIndex
if strings.HasPrefix(sql, "CREATE UNIQUE INDEX") {
indexType = base.IndexTypeUnique
}
//
indexCols := strings.Split(strings.Replace(mat[3], "`", "", -1), ",")
//
indexes = append(indexes, base.NewIndex(indexName, indexType).AddColumn(indexCols...))
}
return indexes, nil
}
func (dc *mysqlDialect) SchemaIndexQuery(dbName, tableName string) ([]*base.Index, error) {
indexes := []*base.Index{}
s := "SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` "
s += "FROM `INFORMATION_SCHEMA`.`STATISTICS` "
s += "WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?"
rows, err := dc.Base().Conn.Query(s, dbName, tableName)
if err != nil {
return indexes, err
}
defer rows.Close()
for rows.Next() {
var indexType int
var indexName, colName, nonUnique string
if err = rows.Scan(&indexName, &nonUnique, &colName); err != nil {
return indexes, err
}
if indexName == "PRIMARY" {
indexType = base.IndexTypePrimaryKey
} else if "YES" == nonUnique || nonUnique == "1" {
indexType = base.IndexTypeIndex
} else {
indexType = base.IndexTypeUnique
}
exist := false
for i, v := range indexes {
if v.Name == indexName {
indexes[i].AddColumn(colName)
exist = true
}
}
if !exist {
indexes = append(indexes, base.NewIndex(indexName, indexType).AddColumn(colName))
}
}
return indexes, nil
}
func (dc *sqlite3Dialect) SchemaTableQuery(dbName string) ([]*base.Table, error) {
tables := []*base.Table{}
q := "SELECT name FROM sqlite_master WHERE type='table'"
rows, err := dc.Base().Conn.Query(q)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var name string
if err = rows.Scan(&name); err != nil {
return nil, err
}
if name == "sqlite_sequence" {
continue
}
idxs, _ := dc.SchemaIndexQuery(dbName, name)
cols, _ := dc.SchemaColumnQuery(dbName, name)
// Patch for sqlite3
for _, v := range cols {
if base.ArrayContain("PRIMARY", v.Extra) {
idxs = append(idxs, base.NewIndex("PRIMARY", base.IndexTypePrimaryKey).AddColumn(v.Name))
break
}
}
tables = append(tables, &base.Table{
Name: name,
Columns: cols,
Indexes: idxs,
})
}
return tables, nil
}