Example #1
0
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
}
Example #2
0
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
}
Example #3
0
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
}