Golang Schema.GetDbTableName方法代码示例

//StateFetch fetches the state of the specified resource
func (tx *Transaction) StateFetch(s *schema.Schema, filter transaction.Filter) (state transaction.ResourceState, err error) {
	if !s.StateVersioning() {
		err = fmt.Errorf("Schema %s does not support state versioning.", s.ID)
		return
	}
	cols := makeStateColumns(s)
	q := sq.Select(cols...).From(quote(s.GetDbTableName()))
	q, _ = addFilterToQuery(s, q, filter, true)
	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	logQuery(sql, args...)
	rows, err := tx.transaction.Queryx(sql, args...)
	if err != nil {
		return
	}
	defer rows.Close()
	if !rows.Next() {
		err = fmt.Errorf("No resource found")
		return
	}
	data := map[string]interface{}{}
	rows.MapScan(data)
	err = decodeState(data, &state)
	return
}

//GenTableDef generates create table sql
func (db *DB) GenTableDef(s *schema.Schema, cascade bool) string {
	schemaManager := schema.GetManager()
	cols, relations := db.genTableCols(s, cascade, nil)

	if s.Parent != "" {
		foreignSchema, _ := schemaManager.Schema(s.Parent)
		cascadeString := ""
		if cascade || s.OnParentDeleteCascade {
			cascadeString = "on delete cascade"
		}
		relations = append(relations, fmt.Sprintf("foreign key(`%s_id`) REFERENCES `%s`(id) %s",
			s.Parent, foreignSchema.GetDbTableName(), cascadeString))
	}

	if s.StateVersioning() {
		cols = append(cols, quote(configVersionColumnName)+"int not null default 1")
		cols = append(cols, quote(stateVersionColumnName)+"int not null default 0")
		cols = append(cols, quote(stateErrorColumnName)+"text not null default ''")
		cols = append(cols, quote(stateColumnName)+"text not null default ''")
		cols = append(cols, quote(stateMonitoringColumnName)+"text not null default ''")
	}

	cols = append(cols, relations...)
	tableSQL := fmt.Sprintf("create table `%s` (%s);\n", s.GetDbTableName(), strings.Join(cols, ","))
	log.Debug("Creating table: " + tableSQL)
	return tableSQL
}

//Delete delete resource from db
func (tx *Transaction) Delete(s *schema.Schema, resourceID interface{}) error {
	sql, args, err := sq.Delete(quote(s.GetDbTableName())).Where(sq.Eq{"id": resourceID}).ToSql()
	if err != nil {
		return err
	}
	return tx.Exec(sql, args...)
}

func addFilterToQuery(s *schema.Schema, q sq.SelectBuilder, filter map[string]interface{}, join bool) (sq.SelectBuilder, error) {
	if filter == nil {
		return q, nil
	}
	for key, value := range filter {
		property, err := s.GetPropertyByID(key)

		if err != nil {
			return q, err
		}

		var column string
		if join {
			column = makeColumn(s.GetDbTableName(), *property)
		} else {
			column = quote(key)
		}

		queryValues, ok := value.([]string)
		if ok && property.Type == "boolean" {
			v := make([]bool, len(queryValues))
			for i, j := range queryValues {
				v[i], _ = strconv.ParseBool(j)
			}
			q = q.Where(sq.Eq{column: v})
		} else {
			q = q.Where(sq.Eq{column: value})
		}
	}
	return q, nil
}

//DropTable drop table definition
func (db *DB) DropTable(s *schema.Schema) error {
	if s.IsAbstract() {
		return nil
	}
	sql := fmt.Sprintf("drop table if exists %s\n", quote(s.GetDbTableName()))
	_, err := db.DB.Exec(sql)
	return err
}

//GenTableDef generates table create sql
func (db *DB) GenTableDef(s *schema.Schema, cascade bool) string {
	schemaManager := schema.GetManager()
	var cols []string
	var relations []string
	cascadeString := ""
	if cascade {
		cascadeString = "on delete cascade"
	}
	for _, property := range s.Properties {
		handler := db.handlers[property.Type]
		dataType := property.SQLType
		if db.sqlType == "sqlite3" {
			dataType = strings.Replace(dataType, "auto_increment", "autoincrement", 1)
		}
		if dataType == "" {
			dataType = handler.dataType(&property)
			if property.ID == "id" {
				dataType += " primary key"
			} else {
				if property.Nullable {
					dataType += " null"
				} else {
					dataType += " not null"
				}
				if property.Unique {
					dataType += " unique"
				}
			}
		}
		sql := "`" + property.ID + "`" + dataType

		cols = append(cols, sql)
		if property.Relation != "" {
			foreignSchema, _ := schemaManager.Schema(property.Relation)
			if foreignSchema != nil {
				relations = append(relations, fmt.Sprintf("foreign key(`%s`) REFERENCES `%s`(id) %s",
					property.ID, foreignSchema.GetDbTableName(), cascadeString))
			}
		}
	}
	if s.Parent != "" {
		foreignSchema, _ := schemaManager.Schema(s.Parent)
		relations = append(relations, fmt.Sprintf("foreign key(`%s_id`) REFERENCES `%s`(id) %s",
			s.Parent, foreignSchema.GetDbTableName(), cascadeString))
	}
	if s.StateVersioning() {
		cols = append(cols, quote(configVersionColumnName)+"int not null default 1")
		cols = append(cols, quote(stateVersionColumnName)+"int not null default 0")
		cols = append(cols, quote(stateErrorColumnName)+"text not null default ''")
		cols = append(cols, quote(stateColumnName)+"text not null default ''")
		cols = append(cols, quote(stateMonitoringColumnName)+"text not null default ''")
	}
	cols = append(cols, relations...)
	tableSQL := fmt.Sprintf("create table `%s` (%s);\n", s.GetDbTableName(), strings.Join(cols, ","))
	log.Debug("Creating table: " + tableSQL)
	return tableSQL
}

func makeStateColumns(s *schema.Schema) (cols []string) {
	dbTableName := s.GetDbTableName()
	cols = append(cols, dbTableName+"."+configVersionColumnName+" as "+quote(configVersionColumnName))
	cols = append(cols, dbTableName+"."+stateVersionColumnName+" as "+quote(stateVersionColumnName))
	cols = append(cols, dbTableName+"."+stateErrorColumnName+" as "+quote(stateErrorColumnName))
	cols = append(cols, dbTableName+"."+stateColumnName+" as "+quote(stateColumnName))
	cols = append(cols, dbTableName+"."+stateMonitoringColumnName+" as "+quote(stateMonitoringColumnName))
	return cols
}

func (db *DB) getTable(s *schema.Schema) []interface{} {
	rawTable, ok := db.data[s.GetDbTableName()]
	if ok {
		return rawTable.([]interface{})
	}
	newTable := []interface{}{}
	db.data[s.GetDbTableName()] = newTable
	return newTable
}

func makeJoin(s *schema.Schema, q sq.SelectBuilder) sq.SelectBuilder {
	manager := schema.GetManager()
	for _, property := range s.Properties {
		if property.RelationProperty == "" {
			continue
		}
		relatedSchema, _ := manager.Schema(property.Relation)
		q = q.LeftJoin(
			quote(relatedSchema.GetDbTableName()) + fmt.Sprintf(" on %s.%s = %s.id", s.GetDbTableName(), property.ID, relatedSchema.GetDbTableName()))
		q = makeJoin(relatedSchema, q)
	}
	return q
}

//Delete delete resource from db
func (tx *Transaction) Delete(s *schema.Schema, resourceID interface{}) error {
	db := tx.db
	db.load()
	table := db.getTable(s)
	newTable := []interface{}{}
	for _, rawDataInDB := range table {
		dataInDB := rawDataInDB.(map[string]interface{})
		if dataInDB["id"] != resourceID {
			newTable = append(newTable, dataInDB)
		}
	}
	db.data[s.GetDbTableName()] = newTable
	db.write()
	return nil
}

func (tx *Transaction) decodeRows(s *schema.Schema, rows *sqlx.Rows, list []*schema.Resource) ([]*schema.Resource, error) {
	for rows.Next() {
		resourceData := map[string]interface{}{}
		data := map[string]interface{}{}
		rows.MapScan(data)

		var resource *schema.Resource
		tx.decode(s, s.GetDbTableName(), data, resourceData)
		resource, err := schema.NewResource(s, resourceData)
		if err != nil {
			return nil, fmt.Errorf("Failed to decode rows")
		}
		list = append(list, resource)
	}
	return list, nil
}

//AlterTableDef generates alter table sql
func (db *DB) AlterTableDef(s *schema.Schema, cascade bool) (string, error) {
	var existing []string
	rows, err := db.DB.Query(fmt.Sprintf("select * from `%s`;", s.GetDbTableName()))
	if err == nil {
		defer rows.Close()
		existing, err = rows.Columns()
	}

	if err != nil {
		return "", err
	}

	cols, relations := db.genTableCols(s, cascade, existing)
	cols = append(cols, relations...)
	if len(cols) == 0 {
		return "", nil
	}
	alterTable := fmt.Sprintf("alter table`%s` add (%s);\n", s.GetDbTableName(), strings.Join(cols, ","))
	log.Debug("Altering table: " + alterTable)
	return alterTable, nil
}

//count count all matching resources in the db
func (tx *Transaction) count(s *schema.Schema, filter transaction.Filter) (res uint64, err error) {
	q := sq.Select("Count(id) as count").From(quote(s.GetDbTableName()))
	//Filter get already tested
	q, _ = addFilterToQuery(s, q, filter, false)
	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	result := map[string]interface{}{}
	err = tx.transaction.QueryRowx(sql, args...).MapScan(result)
	if err != nil {
		return
	}
	count, _ := result["count"]
	decoder := &numberHandler{}
	decoded, decodeErr := decoder.decode(nil, count)
	if decodeErr != nil {
		err = fmt.Errorf("SQL List decoding error: %s", decodeErr)
		return
	}
	res = uint64(decoded.(int))
	return
}

//List resources in the db
func (tx *Transaction) List(s *schema.Schema, filter map[string]interface{}, pg *pagination.Paginator) (list []*schema.Resource, total uint64, err error) {
	cols := MakeColumns(s, true)
	q := sq.Select(cols...).From(quote(s.GetDbTableName()))
	q = addFilterToQuery(s, q, filter, true)

	if pg != nil {
		property, err := s.GetPropertyByID(pg.Key)
		if err == nil {
			q = q.OrderBy(makeColumn(s, *property) + " " + pg.Order)
			if pg.Limit > 0 {
				q = q.Limit(pg.Limit)
			}
			if pg.Offset > 0 {
				q = q.Offset(pg.Offset)
			}
		}
	}
	q = makeJoin(s, q)

	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	logQuery(sql, args...)
	rows, err := tx.transaction.Queryx(sql, args...)
	if err != nil {
		return
	}
	defer rows.Close()
	list, err = tx.decodeRows(s, rows, list)
	if err != nil {
		return nil, 0, err
	}
	total, err = tx.count(s, filter)
	return
}

	Describe("Query", func() {
		var s *schema.Schema

		BeforeEach(func() {
			manager := schema.GetManager()
			var ok bool
			s, ok = manager.Schema("test")
			Expect(ok).To(BeTrue())
		})

		Context("Without place holders", func() {
			It("Returns resources", func() {
				query := fmt.Sprintf(
					"SELECT %s FROM %s",
					strings.Join(MakeColumns(s, false), ", "),
					s.GetDbTableName(),
				)
				results, err := tx.Query(s, query, []interface{}{})
				Expect(err).ToNot(HaveOccurred())
				Expect(results[0].Get("tenant_id")).To(Equal("tenant0"))
				Expect(results[0].Get("test_string")).To(Equal("obj0"))
				Expect(results[2].Get("tenant_id")).To(Equal("tenant1"))
				Expect(results[2].Get("test_string")).To(Equal("obj2"))
				Expect(len(results)).To(Equal(4))
			})
		})

		Context("With a place holder", func() {
			It("Replace the place holder and returns resources", func() {
				query := fmt.Sprintf(
					"SELECT %s FROM %s WHERE tenant_id = ?",