Last active
July 14, 2022 14:19
-
-
Save Indribell/91c7504460732e4958bec2a80ab7b3f6 to your computer and use it in GitHub Desktop.
Extension for sql package
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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 | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I was having issues with MySQL representing ID as uint8. So i modified the script and came up with this.
Thanks for showing me the way to fix this.