godb/crud.go

package db

import "fmt"

func (db DB) Insert(obj interface{}) error {
	tx := db.orm.Begin()

	err := tx.Create(obj).Error
	if err != nil {
		tx.Rollback()
		return err
	}

	err = tx.Commit().Error
	if err != nil {
		tx.Rollback()
		return err
	}
	return nil
}
func (db DB) Get(objs interface{}, where map[string]interface{}, order string, limit uint64, offset uint64, deleted bool) error {
	con := db.orm
	if deleted {
		con = con.Unscoped()
	}

	keys, values := whereExactKeyValues(where)
	con = con.Where(keys, values)
	if deleted {
		con = con.Where("deleted_at is not null")
	}

	if order != "" {
		con = con.Order(order)
	}
	if limit != 0 {
		con = con.Limit(limit)
	}
	if offset != 0 {
		con = con.Offset(offset)
	}
	err := con.Find(objs).Error
	if err != nil {
		return err
	}
	return nil
}

// get by id
func (db DB) GetById(obj interface{}, id uint64) error {
	err := db.orm.First(obj, id).Error
	if err != nil {
		return err
	}
	return nil
}
func (db DB) Update(obj interface{}, para map[string]interface{}) error {
	tx := db.orm.Begin()

	err := tx.Model(obj).Updates(para).Error
	if err != nil {
		tx.Rollback()
		return err
	}

	err = tx.Commit().Error
	if err != nil {
		tx.Rollback()
		return err
	}
	return nil
}
func (db DB) Delete(obj interface{}, para map[string]interface{}) error {
	tx := db.orm.Begin()

	err := tx.Where(para).Delete(obj).Error
	if err != nil {
		tx.Rollback()
		return err
	}

	err = tx.Commit().Error
	if err != nil {
		tx.Rollback()
		return err
	}
	return nil
}

// get tables
func (db DB) GetTables() ([]interface{}, error) {
	var tables []interface{}
	if db.driver == SQLITE_DRIVER {
		ts, err := db.sqliteTables()
		if err != nil {
			return ts, err
		}
		tables = ts
	}
	if db.driver == POSTGRES_DRIVER {
		tp, err := db.Select("select table_name from information_schema.tables", Filter{Field: "table_type", Compare: "=", Value: "BASE TABLE"})
		if err != nil {
			return tp, err
		}
		tables = tp
	}
	return tables, nil
}
func (db DB) GetMeta(tableName interface{}) (interface{}, error) {
	var meta interface{}
	if db.driver == SQLITE_DRIVER {
		tm, err := db.sqliteMeta(tableName)
		if err != nil {
			return meta, err
		}
		meta = tm
	}
	if db.driver == POSTGRES_DRIVER {
		pm, err := db.postgresMeta(fmt.Sprint(tableName))
		if err != nil {
			return pm, err
		}
		meta = pm
	}
	return meta, nil
}
func (db DB) postgresMeta(tableName string) ([]interface{}, error) {
	q := "SELECT columns.table_name,columns.column_name,columns.data_type,columns.column_default,columns.is_nullable FROM information_schema.columns"
	mp, err := db.Select(q, Filter{Field: "columns.table_name", Compare: "=", Value: tableName})
	if err != nil {
		return nil, err
	}
	return mp, nil
}

// get table meta data
func (db DB) sqliteMeta(tableName interface{}) ([]TableInfo, error) {
	var info []TableInfo
	str := fmt.Sprint(tableName)
	q := "pragma table_info(" + str + ")"
	row, err := db.orm.DB().Query(q)
	if err != nil {
		return info, err
	}
	var cid uint
	var t string
	var name string
	var notnull string
	var pk int
	var dfltValue interface{}
	for row.Next() {
		err = row.Scan(&cid, &name, &t, &notnull, &dfltValue, &pk)
		if err != nil {
			return info, err
		}
		table_info := TableInfo{
			Cid:       cid,
			Name:      name,
			Type:      t,
			Notnull:   notnull,
			DfltValue: dfltValue,
			Pk:        pk,
		}
		info = append(info, table_info)
	}
	return info, nil
}
func (db DB) Select(para string, filter Filter) ([]interface{}, error) {
	var tables []interface{}
	row, err := db.orm.Select(para).Where(filter.Field+" "+filter.Compare+" ?", filter.Value).Rows()
	if err != nil {
		return tables, err
	}
	columns, err := row.Columns()
	if err != nil {
		return tables, err
	}
	for row.Next() {
		values := make([]interface{}, len(columns))
		record := make([]interface{}, len(columns))
		for i, _ := range columns {
			values[i] = &record[i]
		}
		err = row.Scan(values...)
		if err != nil {
			return tables, err
		}
		for _, v := range record {
			var str string
			switch value := v.(type) {
			case int64:
				str = fmt.Sprint(value)
			case float64:
				str = fmt.Sprintf("%f", value)
			default:
				str = fmt.Sprintf("%s", value)
			}
			tables = append(tables, str)
		}
	}
	return tables, nil
}
func (db DB) sqliteTables() ([]interface{}, error) {
	var tables []interface{}
	var sqlMaster []SqliteMaster
	err := db.Get(&sqlMaster, nil, "", 0, 0, false)
	if err != nil {
		return tables, err
	}
	for _, t := range sqlMaster {
		if t.Type == "table" {
			tables = append(tables, t.TblName)
		}
	}
	return tables, nil
}