Golang sqlx.DB类代码示例

// GetAgeModelData does the search and modifies the results struct pointer
// Not placed in the above function to allow this to be called by stand along apps like ocdBulk
func GetAgeModelData(db *sqlx.DB, sqlstring string, results **[]AgeModel) {
	db.MapperFunc(strings.ToUpper)
	err := db.Select(*results, sqlstring)
	if err != nil {
		log.Printf(`Error v2 with: %s`, err)
	}
}

func InsertAnswer(db *sqlx.DB, userId int, questionId int, message string) (*Answer, error) {
	a := Answer{}
	a.AuthorId = userId
	a.QuestionId = questionId
	a.Message = message
	var err error

	if err = ValidateAnswer(a); err != nil {
		return nil, err
	}

	qry := sq.Insert("answers").
		Columns("author_id", "question_id", "message").
		Values(a.AuthorId, a.QuestionId, a.Message).
		Suffix("RETURNING *").
		PlaceholderFormat(sq.Dollar)

	sql, params, err := qry.ToSql()
	if err != nil {
		return nil, err
	}

	err = db.Get(&a, sql, params...)
	dbErr := dbError(err)
	if dbErr != nil {
		return nil, dbErr
	} else {
		return &a, nil
	}
}

// @Title putJob
// @Description modify an existing jobentry
// @Accept  application/json
// @Param   id              path    int     true        "The row id"
// @Param                 Body body     Job   true "Job object that should be added to the table"
// @Success 200 {object}    output_format.ApiWrapper
// @Resource /api/2.0
// @Router /api/2.0/job/{id}  [put]
func putJob(id int, payload []byte, db *sqlx.DB) (interface{}, error) {
	var v Job
	err := json.Unmarshal(payload, &v)
	v.Id = int64(id) // overwrite the id in the payload
	if err != nil {
		log.Println(err)
		return nil, err
	}
	v.LastUpdated = time.Now()
	sqlString := "UPDATE job SET "
	sqlString += "job_agent = :job_agent"
	sqlString += ",object_type = :object_type"
	sqlString += ",object_name = :object_name"
	sqlString += ",keyword = :keyword"
	sqlString += ",parameters = :parameters"
	sqlString += ",asset_url = :asset_url"
	sqlString += ",asset_type = :asset_type"
	sqlString += ",status = :status"
	sqlString += ",start_time = :start_time"
	sqlString += ",entered_time = :entered_time"
	sqlString += ",tm_user = :tm_user"
	sqlString += ",last_updated = :last_updated"
	sqlString += ",deliveryservice = :deliveryservice"
	sqlString += " WHERE id=:id"
	result, err := db.NamedExec(sqlString, v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return result, err
}

// UpdateNode updates the given Node.
func UpdateNode(db *sqlx.DB, n models.Node) error {
	if n.RXDelay > 15 {
		return errors.New("max value of RXDelay is 15")
	}

	res, err := db.Exec(`
		update node set
			app_eui = $2,
			app_key = $3,
			used_dev_nonces = $4,
			rx_delay = $5,
			rx1_dr_offset = $6,
			channel_list_id = $7
		where dev_eui = $1`,
		n.DevEUI[:],
		n.AppEUI[:],
		n.AppKey[:],
		n.UsedDevNonces,
		n.RXDelay,
		n.RX1DROffset,
		n.ChannelListID,
	)
	if err != nil {
		return fmt.Errorf("update node %s error: %s", n.DevEUI, err)
	}
	ra, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if ra == 0 {
		return fmt.Errorf("node %s does not exist", n.DevEUI)
	}
	log.WithField("dev_eui", n.DevEUI).Info("node updated")
	return nil
}

func connectMySQL() {
	var db *sqlx.DB
	c, err := dockertest.ConnectToMySQL(15, time.Second, func(url string) bool {
		var err error
		db, err = sqlx.Open("mysql", url)
		if err != nil {
			log.Printf("Got error in mysql connector: %s", err)
			return false
		}
		return db.Ping() == nil
	})

	if err != nil {
		log.Fatalf("Could not connect to database: %s", err)
	}

	containers = append(containers, c)
	s := NewSQLManager(db, nil)

	if err = s.CreateSchemas(); err != nil {
		log.Fatalf("Could not create mysql schema: %v", err)
	}

	managers["mysql"] = s
}

// @Title postStatsSummary
// @Description enter a new stats_summary
// @Accept  application/json
// @Param                 Body body     StatsSummary   true "StatsSummary object that should be added to the table"
// @Success 200 {object}    output_format.ApiWrapper
// @Resource /api/2.0
// @Router /api/2.0/stats_summary [post]
func postStatsSummary(payload []byte, db *sqlx.DB) (interface{}, error) {
	var v StatsSummary
	err := json.Unmarshal(payload, &v)
	if err != nil {
		log.Println(err)
	}
	sqlString := "INSERT INTO stats_summary("
	sqlString += "cdn_name"
	sqlString += ",deliveryservice_name"
	sqlString += ",stat_name"
	sqlString += ",stat_value"
	sqlString += ",summary_time"
	sqlString += ",stat_date"
	sqlString += ") VALUES ("
	sqlString += ":cdn_name"
	sqlString += ",:deliveryservice_name"
	sqlString += ",:stat_name"
	sqlString += ",:stat_value"
	sqlString += ",:summary_time"
	sqlString += ",:stat_date"
	sqlString += ")"
	result, err := db.NamedExec(sqlString, v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return result, err
}

// @Title postFederationUsers
// @Description enter a new federation_users
// @Accept  application/json
// @Param                 Body body     FederationUsers   true "FederationUsers object that should be added to the table"
// @Success 200 {object}    output_format.ApiWrapper
// @Resource /api/2.0
// @Router /api/2.0/federation_users [post]
func postFederationUser(payload []byte, db *sqlx.DB) (interface{}, error) {
	var v FederationUsers
	err := json.Unmarshal(payload, &v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	sqlString := "INSERT INTO federation_users("
	sqlString += "federation_id"
	sqlString += ",username"
	sqlString += ",role"
	sqlString += ",created_at"
	sqlString += ") VALUES ("
	sqlString += ":federation_id"
	sqlString += ",:username"
	sqlString += ",:role"
	sqlString += ",:created_at"
	sqlString += ")"
	result, err := db.NamedExec(sqlString, v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return result, err
}

func QueryQuestions(db *sqlx.DB, authorId int, query string, offset int) ([]Question, error) {
	qs := []Question{}
	qry := sq.Select("*").From("questions")

	if authorId != 0 {
		qry = qry.Where("author_id = ?", authorId)
	}
	if query != "" {
		word := fmt.Sprint("%", query, "%")
		qry = qry.Where("(title LIKE ? OR question LIKE ?)", word, word)
	}
	if offset > 0 {
		qry = qry.Offset(uint64(offset))
	}

	qry = qry.OrderBy("created_at DESC")
	qry = qry.PlaceholderFormat(sq.Dollar)
	sql, params, err := qry.ToSql()

	if err != nil {
		return qs, err
	} else {
		err := db.Select(&qs, sql, params...)
		dbErr := dbError(err)
		return qs, dbErr
	}
}

// @Title putDeliveryservice
// @Description modify an existing deliveryserviceentry
// @Accept  application/json
// @Param   id              path    int     true        "The row id"
// @Param                 Body body     Deliveryservice   true "Deliveryservice object that should be added to the table"
// @Success 200 {object}    output_format.ApiWrapper
// @Resource /api/2.0
// @Router /api/2.0/deliveryservice/{id}  [put]
func putDeliveryservice(id int, payload []byte, db *sqlx.DB) (interface{}, error) {
	var v Deliveryservice
	err := json.Unmarshal(payload, &v)
	v.Id = int64(id) // overwrite the id in the payload
	if err != nil {
		log.Println(err)
		return nil, err
	}
	v.LastUpdated = time.Now()
	sqlString := "UPDATE deliveryservice SET "
	sqlString += "xml_id = :xml_id"
	sqlString += ",active = :active"
	sqlString += ",dscp = :dscp"
	sqlString += ",signed = :signed"
	sqlString += ",qstring_ignore = :qstring_ignore"
	sqlString += ",geo_limit = :geo_limit"
	sqlString += ",http_bypass_fqdn = :http_bypass_fqdn"
	sqlString += ",dns_bypass_ip = :dns_bypass_ip"
	sqlString += ",dns_bypass_ip6 = :dns_bypass_ip6"
	sqlString += ",dns_bypass_ttl = :dns_bypass_ttl"
	sqlString += ",org_server_fqdn = :org_server_fqdn"
	sqlString += ",type = :type"
	sqlString += ",profile = :profile"
	sqlString += ",cdn = :cdn"
	sqlString += ",ccr_dns_ttl = :ccr_dns_ttl"
	sqlString += ",global_max_mbps = :global_max_mbps"
	sqlString += ",global_max_tps = :global_max_tps"
	sqlString += ",long_desc = :long_desc"
	sqlString += ",long_desc_1 = :long_desc_1"
	sqlString += ",long_desc_2 = :long_desc_2"
	sqlString += ",max_dns_answers = :max_dns_answers"
	sqlString += ",info_url = :info_url"
	sqlString += ",miss_lat = :miss_lat"
	sqlString += ",miss_long = :miss_long"
	sqlString += ",check_path = :check_path"
	sqlString += ",last_updated = :last_updated"
	sqlString += ",protocol = :protocol"
	sqlString += ",ssl_key_version = :ssl_key_version"
	sqlString += ",ipv6_routing_enabled = :ipv6_routing_enabled"
	sqlString += ",range_request_handling = :range_request_handling"
	sqlString += ",edge_header_rewrite = :edge_header_rewrite"
	sqlString += ",origin_shield = :origin_shield"
	sqlString += ",mid_header_rewrite = :mid_header_rewrite"
	sqlString += ",regex_remap = :regex_remap"
	sqlString += ",cacheurl = :cacheurl"
	sqlString += ",remap_text = :remap_text"
	sqlString += ",multi_site_origin = :multi_site_origin"
	sqlString += ",display_name = :display_name"
	sqlString += ",tr_response_headers = :tr_response_headers"
	sqlString += ",initial_dispersion = :initial_dispersion"
	sqlString += ",dns_bypass_cname = :dns_bypass_cname"
	sqlString += ",tr_request_headers = :tr_request_headers"
	sqlString += " WHERE id=:id"
	result, err := db.NamedExec(sqlString, v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return result, err
}

func contentServersSection(cdnName string, ccrDomain string, db *sqlx.DB) (map[string]ContentServer, error) {
	csQuery := "select * from content_servers where cdn='" + cdnName + "'"
	cServers := []CrContentServer{}
	err := db.Select(&cServers, csQuery)
	if err != nil {
		log.Println(err)
		err = fmt.Errorf("contentServersSection error selecting content_servers: %v", err)
		return nil, err
	}
	dsServerQuery := "select * from cr_deliveryservice_server"
	dsServers := []CrDeliveryserviceServer{}
	err = db.Select(&dsServers, dsServerQuery)
	if err != nil {
		log.Println("ERROR: >> ", err)
		err = fmt.Errorf("contentServersSection error selecting cr_deliveryservice_server: %v", err)
		return nil, err
	}
	dsMap := make(map[string]ContentServerDsMap)
	for _, row := range dsServers {
		if dsMap[row.ServerName] == nil {
			dsMap[row.ServerName] = make(ContentServerDsMap)
		}
		// if dsMap[row.ServerName][row.Name] == nil {
		// 	dsMap[row.ServerName][row.Name] = make(ContentServerDomainList, 0, 10)
		// }
		pattern := row.Pattern
		if strings.HasSuffix(pattern, "\\..*") {
			pattern = strings.Replace(pattern, ".*\\.", "", 1)
			pattern = strings.Replace(pattern, "\\..*", "", 1)
			if strings.HasPrefix(row.DsType, "HTTP") {
				pattern = row.ServerName + "." + pattern + "." + ccrDomain
			} else {
				pattern = "edge." + pattern + "." + ccrDomain
			}
		}
		dsMap[row.ServerName][row.Name] = append(dsMap[row.ServerName][row.Name], pattern)
	}

	retMap := make(map[string]ContentServer)
	for _, row := range cServers {
		hCount, _ := strconv.Atoi(row.HashCount)
		hCount = hCount * 1000 // TODO JvD
		retMap[row.HostName] = ContentServer{
			Fqdn:             row.Fqdn,
			HashCount:        hCount,
			HashID:           row.HostName,
			InterfaceName:    row.InterfaceName,
			IP:               row.Ip,
			IP6:              row.Ip6,
			LocationID:       row.CacheGroup,
			Port:             row.Port,
			Profile:          row.Profile,
			Status:           row.Status,
			Type:             row.Status,
			DeliveryServices: dsMap[row.HostName],
		}
	}

	return retMap, nil
}

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 validateMasterPassword(db *sqlx.DB) {
	c := dbConfig{}

	db.Get(&c, "SELECT `key`, `value` FROM `config` WHERE `key` = 'teststring'")

	if c.Key == "" {
		panic("Could not read the teststring from the config table. Your database is broken.")
	}

	// not yet initialized, so store the ciphertext
	if len(c.Value) == 0 {
		ciphertext, err := Encrypt([]byte(TestString))
		if err != nil {
			panic(err)
		}

		_, err = db.Exec("UPDATE `config` SET `value` = ? WHERE `key` = ?", ciphertext, c.Key)
		if err != nil {
			panic("Could not write initial password marker: " + err.Error())
		}
	} else {
		plaintext, err := Decrypt(c.Value)
		if err != nil {
			panic("The configured password is not usable for the configured database.")
		}

		// this should never happen: a wrong password should always yield an error in Decrypt()
		if TestString != string(plaintext) {
			panic("The configured password is not usable for the configured database.")
		}
	}
}

// savePeopleContext records contextual information about people being
// discussed, enabling Abot to replace things like "him", "her", or "they" with
// the names the pronouns represent.
func savePeopleContext(db *sqlx.DB, in *dt.Msg) error {
	if len(in.StructuredInput.People) == 0 {
		return nil
	}
	byt, err := json.Marshal(in.StructuredInput.People)
	if err != nil {
		return err
	}
	if in.User.ID > 0 {
		q := `INSERT INTO states (key, value, userid, pluginname)
		      VALUES ($1, $2, $3, '')
		      ON CONFLICT (userid, pluginname, key)
		      DO UPDATE SET value=$2`
		_, err = db.Exec(q, keyContextPeople, byt, in.User.ID)
	} else {
		q := `INSERT INTO states
		      (key, value, flexid, flexidtype, pluginname)
		      VALUES ($1, $2, $3, $4, '')
		      ON CONFLICT (flexid, flexidtype, pluginname, key)
		      DO UPDATE SET value=$2`
		_, err = db.Exec(q, keyContextPeople, byt, in.User.FlexID,
			in.User.FlexIDType)
	}
	if err != nil {
		return err
	}
	return nil
}

// addTimeContext adds a time context to a Message if the word "then" is found.
func addTimeContext(db *sqlx.DB, in *dt.Msg) error {
	var addContext bool
	for _, stem := range in.Stems {
		if stem == "then" {
			addContext = true
			break
		}
	}
	if !addContext {
		return nil
	}
	var byt []byte
	var err error
	if in.User.ID > 0 {
		q := `SELECT value FROM states WHERE userid=$1 AND key=$2`
		err = db.Get(&byt, q, in.User.ID, keyContextTime)
	} else {
		q := `SELECT value FROM states
		      WHERE flexid=$1 AND flexidtype=$2 AND key=$3`
		err = db.Get(&byt, q, in.User.FlexID, in.User.FlexIDType,
			keyContextTime)
	}
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	var times []time.Time
	if err = json.Unmarshal(byt, &times); err != nil {
		return err
	}
	in.StructuredInput.Times = times
	return nil
}

// @Title putToExtension
// @Description modify an existing to_extensionentry
// @Accept  application/json
// @Param   id              path    int     true        "The row id"
// @Param                 Body body     ToExtension   true "ToExtension object that should be added to the table"
// @Success 200 {object}    output_format.ApiWrapper
// @Resource /api/2.0
// @Router /api/2.0/to_extension/{id}  [put]
func putToExtension(id int, payload []byte, db *sqlx.DB) (interface{}, error) {
	var v ToExtension
	err := json.Unmarshal(payload, &v)
	v.Id = int64(id) // overwrite the id in the payload
	if err != nil {
		log.Println(err)
		return nil, err
	}
	v.LastUpdated = time.Now()
	sqlString := "UPDATE to_extension SET "
	sqlString += "name = :name"
	sqlString += ",version = :version"
	sqlString += ",info_url = :info_url"
	sqlString += ",script_file = :script_file"
	sqlString += ",isactive = :isactive"
	sqlString += ",additional_config_json = :additional_config_json"
	sqlString += ",description = :description"
	sqlString += ",servercheck_short_name = :servercheck_short_name"
	sqlString += ",servercheck_column_name = :servercheck_column_name"
	sqlString += ",type = :type"
	sqlString += ",last_updated = :last_updated"
	sqlString += " WHERE id=:id"
	result, err := db.NamedExec(sqlString, v)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return result, err
}