Skip to content

Instantly share code, notes, and snippets.

@Indribell
Last active July 14, 2022 14:19
Show Gist options
  • Save Indribell/91c7504460732e4958bec2a80ab7b3f6 to your computer and use it in GitHub Desktop.
Save Indribell/91c7504460732e4958bec2a80ab7b3f6 to your computer and use it in GitHub Desktop.
Extension for sql package
// Fixing some of the errors in the:
// https://gist.github.com/PumpkinSeed/b4993c6ad20ea90e3da8c991a90a91e1
//
// 1. It was only able to extract database information, based upon a Struct.
// The code now can deal with a struct or a slice with structs.
//
// 2. The code relied on the json tag in the struct.
// You need to match the data with database fields, not the output fields.
// This will match up more 1 to 1 as you are in controle of your Database naming and selecting of that data.
// If a client expect different named exported json fields, ...
//
// 3. The code while designed for a single struct, kept looping over all the rows.
// So if you tried to extract one result and did not use a limitor ( LIMIT ) in your SQL code, it will have looped the column information ..N times
//
// 4. Some data types like Int had been missing
//
// 5. Fix a few bugs
type User struct {
ID int `db:"id"`
Firstname string `db:"firstname" json:"first_name"`
Lastname string `db:"lastname" json:"last_name"`
Age int `db:"age" json:"age,omitempty"`
}
// QueryStruct allows a Struct or Slice with Struct as result
//
// Example:
//
// contacts := []Contact{}
// QueryStruct(&contacts, "SELECT * FROM contact")
//
// contact := Contact{}
// QueryOne(&contact, "SELECT * FROM contact")
//
func (connection *Connection) QueryStruct(dest interface{}, query string, args ...interface{}) bool {
rows, err := connection.sql.Query(query, args...)
if err != nil {
log.Println("DB: QueryAll failed: " + err.Error())
return false
}
defer rows.Close()
err = structScan(rows, dest)
if err != nil {
log.Println("DB: QueryAll structScan failed: " + err.Error())
return false
}
return true
}
// structScan will scan the results of a database query and try to merge then with the supplied struct
//
// Example: []Users, User
//
// Are both valid structs as the routing will automatically figure out if the results are a slice with struct or a pure struct
func structScan(rows *sql.Rows, model interface{}) error {
v := reflect.ValueOf(model)
if v.Kind() != reflect.Ptr {
return errors.New("must pass a pointer, not a value, to StructScan destination") // @todo add new error message
}
v = reflect.Indirect(v)
t := v.Type()
// Find out if what we are using is a Struct ( Query One ) or a Slice with Structs ( QueryAll )
isStruct, isSlice := false, false
if t.Kind() == reflect.Slice {
isSlice = true
} else if t.Kind() == reflect.Struct {
isStruct = true
}
// Ensure we only get the column information one time!
cols, _ := rows.Columns()
columns := make([]interface{}, len(cols))
columnPointers := make([]interface{}, len(cols))
for i := range columns {
columnPointers[i] = &columns[i]
}
var m []map[string]interface{}
for rows.Next() {
if err := rows.Scan(columnPointers...); err != nil {
return err
}
x := make(map[string]interface{})
for i, colName := range cols {
val := columnPointers[i].(*interface{})
x[colName] = *val
}
m = append(m, x)
// If we are dealing with a struct. There is no point in looping over all the results, if they are more then one!
if isStruct == true {
break
}
}
if isStruct == true {
if len(m) > 0 { // Ensure we have data fields!
changeStruct(v, t, m[0])
}
}
if isSlice == true {
if len(m) > 0 { // Ensure we have data in the slice!
var elem reflect.Value
for _, d := range m {
typ := v.Type().Elem()
elem = reflect.New(typ).Elem()
changeStruct(elem, typ, d)
v.Set(reflect.Append(v, elem))
}
}
}
return nil
}
func changeStruct(v reflect.Value, t reflect.Type, m map[string]interface{}) {
for i := 0; i < v.NumField(); i++ {
field := strings.Split(t.Field(i).Tag.Get("db"), ",")[0]
if item, ok := m[field]; ok {
if v.Field(i).CanSet() {
if item != nil {
switch v.Field(i).Kind() {
case reflect.String:
v.Field(i).SetString(item.(string)) // s := bytesToString(item.([]uint8))
case reflect.Float32, reflect.Float64:
v.Field(i).SetFloat(item.(float64))
case reflect.Int, reflect.Int32, reflect.Int64:
v.Field(i).SetInt(item.(int64))
case reflect.Bool:
v.Field(i).Set(reflect.ValueOf(!(item.(int64) == 0)))
case reflect.Ptr:
if reflect.ValueOf(item).Kind() == reflect.Bool {
itemBool := item.(bool)
v.Field(i).Set(reflect.ValueOf(&itemBool))
}
case reflect.Struct:
v.Field(i).Set(reflect.ValueOf(item))
default:
fmt.Println(t.Field(i).Name, ": ", v.Field(i).Kind(), " - > - ", reflect.ValueOf(item).Kind()) // @todo remove after test out the Get methods
}
}
}
}
}
}
@deye9
Copy link

deye9 commented Dec 11, 2020

I was having issues with MySQL representing ID as uint8. So i modified the script and came up with this.

func changeStruct(v reflect.Value, t reflect.Type, m map[string]interface{}) {

	for i := 0; i < v.NumField(); i++ {

		field := strings.Split(t.Field(i).Tag.Get("json"), ",")[0]
		item, ok := m[field]

		if ok {
			if v.Field(i).CanSet() {
				if item != nil {

					switch v.Field(i).Kind() {

					case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
						uinttext := (item.(interface{}).([]uint8))
						if intVal, err := strconv.ParseUint(string(uinttext), 10, 64); err == nil {
							v.Field(i).SetUint(uint64(intVal))
						}

					case reflect.String:
						v.Field(i).SetString(fmt.Sprintf("%s", item))

					case reflect.Float32, reflect.Float64:
						v.Field(i).SetFloat(item.(float64))

					case reflect.Int, reflect.Int32, reflect.Int64:
						v.Field(i).SetInt(item.(int64))

					case reflect.Bool:
						v.Field(i).Set(reflect.ValueOf(!(item.(int64) == 0)))

					case reflect.Ptr:
						if reflect.ValueOf(item).Kind() == reflect.Bool {
							itemBool := item.(bool)
							v.Field(i).Set(reflect.ValueOf(&itemBool))
						}

					case reflect.Struct:
						v.Field(i).Set(reflect.ValueOf(item))

					default:
						
					}
				}
			}
		}
	}
}

Thanks for showing me the way to fix this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment