Golang DB.PrepareNamed方法代碼示例

func GetTmUserByName(username string, db *sqlx.DB) (TmUser, error) {
	ret := []TmUser{}
	// this works in pq - $1
	// err := db.GlobalDB.Get(&ret, "select * from tm_user where username=$1", username)
	// if err != nil {
	// 	log.Println(err)
	// }

	// this works in mysql - ?
	// err := db.GlobalDB.Get(&ret, "select * from tm_user where username=?", username)
	// if err != nil {
	// 	log.Println(err)
	// }

	// this works in both
	arg := TmUser{Username: null.StringFrom(username)}
	nstmt, err := db.PrepareNamed(`select * from tm_user where username=:username`)
	if err != nil {
		log.Println(err)
		return TmUser{}, err
	}
	defer nstmt.Close()

	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return TmUser{}, err
	}
	if len(ret) != 1 {
		err = errors.New("Username " + username + " is not unique!")
		return TmUser{}, err
	}

	return ret[0], err
}

func newRepository(db *sqlx.DB) (*repository, error) {
	r := repository{db: db}

	stmts := map[string]**sqlx.NamedStmt{
		findMood:       &r.findMood,
		setMood:        &r.setMood,
		deleteMood:     &r.deleteMood,
		insertConvo:    &r.insertConvo,
		getConvo:       &r.getConvo,
		deleteConvo:    &r.deleteConvo,
		findConvoLines: &r.findConvoLines,
		insertLine:     &r.insertLine,
		getLine:        &r.getLine,
		deleteLine:     &r.deleteLine,

		fmt.Sprintf(listConvos, ">", "ASC"):  &r.listConvosAsc,
		fmt.Sprintf(listConvos, "<", "DESC"): &r.listConvosDesc,
		fmt.Sprintf(listMoods, ">", "ASC"):   &r.listMoodsAsc,
		fmt.Sprintf(listMoods, "<", "DESC"):  &r.listMoodsDesc,
	}

	for sqlStr, stmt := range stmts {
		prepped, err := db.PrepareNamed(sqlStr)
		*stmt = prepped
		if err != nil {
			return nil, errors.Annotatef(err, "preparing statement %s", sqlStr)
		}
		r.closers = append(r.closers, prepped)
	}

	return &r, nil
}

func (r Rock) Create(db *sqlx.DB, m *rock.Model) error {
	stmt, err := db.PrepareNamed(`INSERT INTO rocks (latitude, longitude) VALUES (:latitude, :longitude) RETURNING *`)
	if err != nil {
		log.Fatal(err)
	}

	err = stmt.Get(m, m)

	return err
}

// @Title getGooseDbVersionById
// @Description retrieves the goose_db_version information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    GooseDbVersion
// @Resource /api/2.0
// @Router /api/2.0/goose_db_version/{id} [get]
func getGooseDbVersionById(id int, db *sqlx.DB) (interface{}, error) {
	ret := []GooseDbVersion{}
	arg := GooseDbVersion{Id: int64(id)}
	nstmt, err := db.PrepareNamed(`select * from goose_db_version where id=:id`)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

// \todo add port (Servers PK is a compound key, host_name and port
func getCSConfigRemap(serverName string, db *sqlx.DB) ([]CsconfigRemap, error) {
	ret := []CsconfigRemap{}
	arg := api.Servers{HostName: serverName}
	nstmt, err := db.PrepareNamed(`select * from csconfig_remap where host_name=:host_name`)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

func getCSConfigParams(profile int64, db *sqlx.DB) ([]CsconfigParam, error) {
	ret := []CsconfigParam{}
	arg := CsconfigParam{Profile: profile}
	nstmt, err := db.PrepareNamed(`select * from csconfig_params where profile=:profile`)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

func getCSConfigRemap(serverId int64, db *sqlx.DB) ([]CsconfigRemap, error) {
	ret := []CsconfigRemap{}
	arg := api.Server{Id: serverId}
	nstmt, err := db.PrepareNamed(`select * from csconfig_remap where server_id=:id`)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

// @Title getTypeById
// @Description retrieves the type information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    Type
// @Resource /api/2.0
// @Router /api/2.0/type/{id} [get]
func getTypeById(id int, db *sqlx.DB) (interface{}, error) {
	ret := []Type{}
	arg := Type{}
	arg.Id = int64(id)
	queryStr := "select *, concat('" + API_PATH + "type/', id) as self "
	queryStr += " from type where id=:id"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

func GetServerByName(serverName string, db *sqlx.DB) (Server, error) {

	ret := []Server{}
	arg := Server{HostName: serverName}
	nstmt, err := db.PrepareNamed(`select * from server where host_name=:host_name`)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
	}
	if len(ret) != 1 {
		err = errors.New("Host name " + serverName + " is not unique!")
	}
	nstmt.Close()
	return ret[0], err
}

// @Title getFederationResolversById
// @Description retrieves the federation_resolvers information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    FederationResolvers
// @Resource /api/2.0
// @Router /api/2.0/federation_resolvers/{id} [get]
func getFederationResolver(id int64, db *sqlx.DB) (interface{}, error) {
	ret := []FederationResolvers{}
	arg := FederationResolvers{}
	arg.Id = id
	queryStr := "select *, concat('" + API_PATH + "federation_resolvers/', id) as self"
	queryStr += " from federation_resolvers WHERE id=:id"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

// @Title getLogById
// @Description retrieves the log information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    Log
// @Resource /api/2.0
// @Router /api/2.0/log/{id} [get]
func getLog(id int64, db *sqlx.DB) (interface{}, error) {
	ret := []Log{}
	arg := Log{}
	arg.Id = id
	queryStr := "select *, concat('" + API_PATH + "log/', id) as self"
	queryStr += " from log WHERE id=:id"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

func (h *Hub) Delete(db *sqlx.DB) error {
	nstmt, err := db.PrepareNamed(`DELETE FROM hubs
	WHERE slug = (:slug)
	RETURNING *;
	`)
	if err != nil {
		return err
	}
	defer nstmt.Close()

	err = nstmt.QueryRow(h).StructScan(h)
	// TODO?: Handle possible error cases? Eg...
	// hub record_not_found error would have already been caught in Get Method above.
	return err
}

// @Title getRolesById
// @Description retrieves the roles information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    Roles
// @Resource /api/2.0
// @Router /api/2.0/roles/{id} [get]
func getRole(name string, db *sqlx.DB) (interface{}, error) {
	ret := []Roles{}
	arg := Roles{}
	arg.Name = name
	queryStr := "select *, concat('" + API_PATH + "roles/', name) as self"
	queryStr += " from roles WHERE name=:name"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

// @Title getDeliveryservicesUsersById
// @Description retrieves the deliveryservices_users information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    DeliveryservicesUsers
// @Resource /api/2.0
// @Router /api/2.0/deliveryservices_users/{id} [get]
func getDeliveryservicesUser(deliveryservice string, username string, db *sqlx.DB) (interface{}, error) {
	ret := []DeliveryservicesUsers{}
	arg := DeliveryservicesUsers{}
	arg.Deliveryservice = deliveryservice
	arg.Username = username
	queryStr := "select *, concat('" + API_PATH + "deliveryservices_users', '/deliveryservice/', deliveryservice, '/username/', username) as self"
	queryStr += " from deliveryservices_users WHERE deliveryservice=:deliveryservice AND username=:username"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}

// @Title getFederationUsersById
// @Description retrieves the federation_users information for a certain id
// @Accept  application/json
// @Param   id              path    int     false        "The row id"
// @Success 200 {array}    FederationUsers
// @Resource /api/2.0
// @Router /api/2.0/federation_users/{id} [get]
func getFederationUser(federationId int64, username string, db *sqlx.DB) (interface{}, error) {
	ret := []FederationUsers{}
	arg := FederationUsers{}
	arg.FederationId = federationId
	arg.Username = username
	queryStr := "select *, concat('" + API_PATH + "federation_users', '/federation_id/', federation_id, '/username/', username) as self"
	queryStr += " from federation_users WHERE federation_id=:federation_id AND username=:username"
	nstmt, err := db.PrepareNamed(queryStr)
	err = nstmt.Select(&ret, arg)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	nstmt.Close()
	return ret, nil
}