func GetList(parseRow ParseRow, fn SetFn, query string, args ...interface{}) (err error) {
conn, err := connection.Get()
if err != nil {
return
}
rows, err := conn.Query(query, args...)
if err != nil {
return
}
defer rows.Close()
var list []interface{}
for rows.Next() {
rt, err := parseRow(rows)
if err != nil {
return err
}
list = append(list, rt)
}
if err != nil {
err = rows.Err()
}
fn(list)
return
}
func GetAll() (users []User, err error) {
conn, err := connection.Get()
if err != nil {
return
}
rows, err := conn.Query("SELECT id, name, permissions FROM users ORDER BY name ASC")
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var user User
var permissions []byte
err = rows.Scan(&user.ID, &user.Name, &permissions)
if err != nil {
return users, err
}
err = json.Unmarshal(permissions, &user.Permissions)
if err != nil {
return users, err
}
users = append(users, user)
}
err = rows.Err()
return
}
func Load(id int) (all Tags, err error) {
conn, err := connection.Get()
if err != nil {
return
}
rows, err := conn.Query("SELECT tag_id FROM link_tags WHERE link_id = $1", id)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var tagID int
err = rows.Scan(&tagID)
if err != nil {
return
}
tag, err := getByID(tagID)
if err != nil {
return all, err
}
all = append(all, tag)
}
err = rows.Err()
return
}
func (c *Link) Delete() (err error) {
conn, err := connection.Get()
if err != nil {
return err
}
_, err = conn.Exec("DELETE FROM links WHERE id = $1", c.ID)
return
}
func (c *Link) IncrClickCount() (err error) {
conn, err := connection.Get()
if err != nil {
return err
}
_, err = conn.Exec("UPDATE links SET click_count = click_count + 1, viewed = true, last_viewed_at =CURRENT_TIMESTAMP WHERE id = $1", c.ID)
return
}
func getByID(id int) (tag Tag, err error) {
conn, err := connection.Get()
if err != nil {
return
}
err = conn.QueryRow("SELECT id, name FROM tags WHERE id = $1", id).Scan(&tag.ID, &tag.Name)
return
}
func getByName(name string) (tag Tag, err error) {
tag.Name = name
conn, err := connection.Get()
if err != nil {
return
}
err = conn.QueryRow("SELECT id, name FROM tags WHERE name = $1", name).Scan(&tag.ID, &tag.Name)
if err == nil {
return
}
err = conn.QueryRow("INSERT INTO tags (name) VALUES ($1) RETURNING id", name).Scan(&tag.ID)
return
}
func GetByID(id string) (user User, err error) {
conn, err := connection.Get()
if err != nil {
return
}
user.ID = id
var permissions []byte
err = conn.QueryRow("SELECT name, permissions FROM users WHERE id = $1", id).Scan(&user.Name, &permissions)
if err != nil {
return
}
err = json.Unmarshal(permissions, &user.Permissions)
return
}
func (u User) Save() (err error) {
conn, err := connection.Get()
if err != nil {
return
}
_, err = conn.Exec("INSERT INTO users (id, name, permissions) VALUES ($1, $2, $3)",
u.ID,
u.Name,
utils.ToJSON(u.Permissions),
)
if err != nil {
// attempt to update
_, err = conn.Exec("UPDATE users SET permissions = $1 WHERE id = $2", utils.ToJSON(u.Permissions), u.ID)
}
return
}
func (t *Tags) Add(id int, name string) (err error) {
tag, err := getByName(name)
if err != nil {
return
}
conn, err := connection.Get()
if err != nil {
return
}
_, err = conn.Exec("INSERT INTO link_tags (tag_id, link_id) VALUES ($1, $2)", tag.ID, id)
if err != nil {
return
}
*t = append(*t, tag)
return
}
func (t *Tags) Remove(id int, name string) (err error) {
tag, err := getByName(name)
if err != nil {
return
}
conn, err := connection.Get()
if err != nil {
return
}
_, err = conn.Exec("DELETE FROM link_tags WHERE tag_id = $1 AND link_id = $2", tag.ID, id)
slice := *t
for idx, i := range slice {
if i.Name == name {
slice = append(slice[:idx], slice[idx+1:]...)
break
}
}
*t = slice
return
}
// Takes an Object and a list of primary keys and attempts to update the Object
// based on the keys
func Update(o Object) (err error) {
pkeys := o.PKeys()
if len(pkeys) <= 0 {
panic("Must provide at least one Primary Key")
}
v := reflect.ValueOf(o)
values := []interface{}{}
fields := []string{}
placeholders := []string{}
where := []WhereArg{}
// deref
if v.Kind() == reflect.Interface && !v.IsNil() {
elm := v.Elem()
if elm.Kind() == reflect.Ptr && !elm.IsNil() && elm.Elem().Kind() == reflect.Ptr {
v = elm
}
}
if v.Kind() == reflect.Ptr {
v = v.Elem()
}
for i := 0; i < v.NumField(); i++ {
valueField := v.Field(i)
if valueField.Kind() == reflect.Ptr && valueField.IsNil() {
continue
}
typeField := v.Type().Field(i)
tag := typeField.Tag
// check pkeys
pkey := false
for _, k := range pkeys {
if k == tag.Get("json") {
pkey = true
break
}
}
// if we have a pkey update the where array
placeholder := len(values)
if pkey {
arg := WhereArg{
Field: tag.Get("json"),
Placeholder: fmt.Sprintf("$%d", placeholder+1),
}
where = append(where, arg)
} else if tag.Get("pg_type") == "-" {
continue
} else {
fields = append(fields, tag.Get("json"))
placeholders = append(placeholders, fmt.Sprintf("$%d", placeholder+1))
}
if strings.HasSuffix(tag.Get("json"), "_id") {
// get embedded object id
if reflect.Struct == valueField.Kind() {
values = append(values, valueField.FieldByName("ID").Interface())
}
} else if tag.Get("pg_type") == "jsonb" {
b, err := json.Marshal(valueField.Interface())
if err != nil {
return err
}
values = append(values, &b)
} else {
values = append(values, valueField.Interface())
}
}
setArgs := []string{}
for idx, _ := range fields {
setArgs = append(
setArgs,
fmt.Sprintf("%s = %s",
fields[idx],
placeholders[idx],
),
)
}
whereArgs := []string{}
for _, arg := range where {
whereArgs = append(
whereArgs,
fmt.Sprintf("%s = %s",
arg.Field,
arg.Placeholder,
),
)
}
query := fmt.Sprintf(
"UPDATE %s SET %s WHERE %s",
o.Table(),
strings.Join(setArgs, ", "),
strings.Join(whereArgs, ", "),
)
fmt.Println(query)
fmt.Println(values)
conn, err := connection.Get()
if err != nil {
return
}
_, err = conn.Exec(query, values...)
return
}
func Insert(o Object) (err error) {
v := reflect.ValueOf(o)
if v.Kind() == reflect.Interface && !v.IsNil() {
elm := v.Elem()
if elm.Kind() == reflect.Ptr && !elm.IsNil() && elm.Elem().Kind() == reflect.Ptr {
v = elm
}
}
if v.Kind() == reflect.Ptr {
v = v.Elem()
}
var id, idIdx int
values := []interface{}{}
fields := []string{}
placeholders := []string{}
getID := false
for i := 0; i < v.NumField(); i++ {
valueField := v.Field(i)
if valueField.Kind() == reflect.Ptr && valueField.IsNil() {
continue
}
typeField := v.Type().Field(i)
tag := typeField.Tag
if tag.Get("json") == "id" {
getID = true
idIdx = i
continue
} else if strings.HasSuffix(tag.Get("json"), "_id") {
if tag.Get("pg_type") != "raw" {
// get embedded object id
if reflect.Struct == valueField.Kind() {
values = append(values, valueField.FieldByName("ID").Interface())
}
}
} else if tag.Get("pg_type") == "-" {
continue
} else if tag.Get("pg_type") == "jsonb" {
b, err := json.Marshal(valueField.Interface())
if err != nil {
return err
}
values = append(values, &b)
} else {
values = append(values, valueField.Interface())
}
fields = append(fields, tag.Get("json"))
placeholders = append(
placeholders,
fmt.Sprintf("$%d", len(values)),
)
}
query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)",
o.Table(),
strings.Join(fields, ","),
strings.Join(placeholders, ","),
)
conn, err := connection.Get()
if err != nil {
return
}
if getID {
query = query + " RETURNING id"
err = conn.QueryRow(query, values...).Scan(&id)
f := v.FieldByIndex([]int{idIdx})
if f.CanSet() {
f.SetInt(int64(id))
}
} else {
_, err = conn.Exec(query, values...)
}
return
}