//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 = ?",
func makeColumnID(s *schema.Schema, property schema.Property) string {
return fmt.Sprintf("%s__%s", s.GetDbTableName(), property.ID)
}
func makeColumn(s *schema.Schema, property schema.Property) string {
return fmt.Sprintf("%s.%s", s.GetDbTableName(), quote(property.ID))
}
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, s.GetDbTableName(), 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(
