// UpdateWithdrawalStatusToProcessing update withdrawal status to processing if status = pending
func (s Storage) UpdateWithdrawalStatusToProcessing(ids []int64) error {
rawSQL, args, err := sqlx.In(
"UPDATE `withdrawals` SET `status` = ? WHERE `id` IN (?) AND `status` = ?",
models.WithdrawalStatusProcessing,
ids,
models.WithdrawalStatusPending,
)
if err != nil {
return fmt.Errorf("update withdrawal status to processing build sql with in: %v", err)
}
logrus.WithFields(logrus.Fields{
"sql": rawSQL,
"args": args,
}).Info("sql update withdrawals status to processing")
result, err := s.db.Exec(rawSQL, args...)
if err != nil {
return err
}
if rowAffected, _ := result.RowsAffected(); rowAffected != int64(len(ids)) {
return fmt.Errorf("expected %v but %v rows affected", len(ids), rowAffected)
}
return nil
}
/*
设置任务状态
*/
func (this *TaskDao) SetTasksStatus(tasks []types.CrawlTask, status TaskStatus) (int64, error) {
nTasks := len(tasks)
var affectedRows int64 = 0
var err = ErrNoTasks
if nTasks > 0 {
taskIds := []int32{}
var result sql.Result
var args []interface{}
var sqlStr string
var now = time.Now()
for _, task := range tasks {
taskIds = append(taskIds, task.Id)
}
if status == TASK_FINISH {
sqlStr = fmt.Sprintf("update %s set status=%d, last_crawl_time=%d, crawl_times=crawl_times+1, update_time='%s' where id in (?)", TaskTable, status, now.Unix(), now.Format("2006-01-02 15:04:05"))
} else {
sqlStr = fmt.Sprintf("update %s set status=%d, update_time='%s' where id in (?)", TaskTable, status, now.Format("2006-01-02 15:04:05"))
}
sqlStr, args, err = sqlx.In(sqlStr, taskIds)
if err != nil {
log.Errorln("build sql to set task status failed! sql is ", sqlStr)
} else {
result, err = this.db.Exec(sqlStr, args...)
if err != nil {
log.Errorln("update tasks status error: ", err)
}
}
affectedRows, _ = result.RowsAffected()
}
return affectedRows, err
}
func search(db *sqlx.DB, query string) ([]person, error) {
people := []person{}
tokens := strings.Split(query, " ")
cryptoTokens := []string{}
for _, token := range tokens {
token = strings.TrimSpace(strings.ToLower(token))
crypted := fmt.Sprintf("%x", sha256.Sum256([]byte(token)))
cryptoTokens = append(cryptoTokens, crypted)
}
q, args, err := sqlx.In(`SELECT DISTINCT p.id, p.name
FROM people p
JOIN hashes_people hp ON hp.person_id = p.id
JOIN hashes h ON h.id = hp.hash_id AND h.hash IN (?)
GROUP BY p.name`, cryptoTokens)
if err != nil {
return people, err
}
err = db.Select(&people, q, args...)
if err != nil {
return people, err
}
return people, err
}
// ExtractCities efficiently from a user's message.
func ExtractCities(db *sqlx.DB, in *dt.Msg) ([]dt.City, error) {
// Interface type is used to expand the args in db.Select below.
// Although we're only storing strings, []string{} doesn't work.
var args []interface{}
// Look for "at", "in", "on" prepositions to signal that locations
// follow, skipping everything before
var start int
for i := range in.Stems {
switch in.Stems[i] {
case "at", "in", "on":
start = i
break
}
}
// Prepare sentence for iteration
tmp := regexNonWords.ReplaceAllString(in.Sentence, "")
words := strings.Fields(strings.Title(tmp))
// Iterate through words and bigrams to assemble a DB query
for i := start; i < len(words); i++ {
args = append(args, words[i])
}
bgs := bigrams(words, start)
for i := 0; i < len(bgs); i++ {
args = append(args, bgs[i])
}
cities := []dt.City{}
q := `SELECT name, countrycode FROM cities
WHERE countrycode='US' AND name IN (?)
ORDER BY LENGTH(name) DESC`
query, arguments, err := sqlx.In(q, args)
query = db.Rebind(query)
rows, err := db.Query(query, arguments...)
if err != nil {
return nil, err
}
defer func() {
if err = rows.Close(); err != nil {
log.Info("failed to close db rows.", err)
}
}()
for rows.Next() {
city := dt.City{}
if err = rows.Scan(&city.Name, &city.CountryCode); err != nil {
return nil, err
}
cities = append(cities, city)
}
if err = rows.Err(); err != nil {
return nil, err
}
if len(cities) == 0 {
return nil, ErrNotFound
}
return cities, nil
}
func ParseQueryStruct(q string, d interface{}, db *sqlx.DB) (string, []interface{}, error) {
errors := make([]error, 2)
query, args, err := sqlx.Named(q, d)
errors = append(errors, err)
query, args, err = sqlx.In(query, args...)
errors = append(errors, err)
query = db.Rebind(query)
for _, e := range errors {
if e != nil {
return "", nil, e
}
}
return query, args, nil
}
// HavePermissions return true if the user have all the wanted permissions
func (u *User) HaveAtLeastOnePermission(tx *sqlx.Tx, permissions ...string) (ok bool, err error) {
if len(permissions) == 0 {
return true, nil
}
query, args, err := sqlx.In("SELECT count(distinct(p.id)) FROM permission p,user__group ug, group__permission gp WHERE ug.user_id = ? AND ug.group_id = gp.group_id AND gp.permission_id = p.id AND p.name in (?)", u.Id, permissions)
if err != nil {
return false, err
}
query = db.DB.Rebind(query)
var count int
err = tx.Get(&count, query, args...)
if count > 0 {
return true, err
}
return false, err
}
func (sq *sqlBuilder) compile() {
if sq.sql == "" {
if sq.Query.RawSQL.Fragment != "" {
sq.sql = sq.Query.RawSQL.Fragment
} else {
sq.sql = sq.buildSelectSQL()
}
re := regexp.MustCompile(`(?i)in\s*\(\s*\?\s*\)`)
if re.MatchString(sq.sql) {
s, _, err := sqlx.In(sq.sql, sq.Args())
if err == nil {
sq.sql = s
}
}
sq.sql = sq.Query.Connection.Dialect.TranslateSQL(sq.sql)
}
}
func (s *LocalUsersService) Authorize(userId, clientId int64, scope string, refresh bool) (string, error) {
if !refresh {
return "", nil
}
tx, err := s.client.db.Begin()
if err != nil {
log.Println("User.Authorize:", err)
return "", err
}
refreshToken, err := RandomToken()
if err != nil {
log.Println("User.Authorize: failed generating token:", err)
return "", err
}
var tokenId int
err = tx.QueryRow(createTokenSql, clientId, userId, "refresh_token", refreshToken, nil).Scan(&tokenId)
if err != nil {
tx.Rollback()
log.Println("User.Authorize: failed inserting token:", err)
return "", err
}
scopes := strings.Split(scope, " ")
query, args, err := sqlx.In(attachScopesSql, tokenId, scopes, clientId)
query = s.client.db.Rebind(query)
_, err = tx.Exec(query, args...)
if err != nil {
tx.Rollback()
log.Println("User.Authorize: failed attaching scopes:", err)
return "", err
}
err = tx.Commit()
if err != nil {
log.Println("User.Authorize: failed committing transaction:", err)
return "", err
}
return refreshToken, nil
}
// RemoveFeedsFromUser does the opposite of AddFeedsToUser
func (d *Handle) RemoveFeedsFromUser(u *User, feeds []*FeedInfo) error {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
feedIDs := make([]int64, len(feeds))
for i, f := range feeds {
feedIDs[i] = f.ID
}
q := `DELETE FROM user_feeds WHERE user_id = ? AND feed_info_id IN (?)`
q, args, err := sqlx.In(q, u.ID, feedIDs)
if err != nil {
return err
}
_, err = d.queryer.Exec(q, args...)
if err != nil {
return err
}
return nil
}
func FindExistingEventURLList(urlList []string) ([]string, error) {
var existingURLS []string
dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
constants.DB_USER, constants.DB_PASSWORD, constants.DB_NAME)
db, err := sqlx.Open("postgres", dbinfo)
if err != nil {
return existingURLS, err
}
defer db.Close()
query, args, err := sqlx.In("SELECT url FROM event_exhibition WHERE url IN (?);", urlList)
query = db.Rebind(query)
rows, err := db.Query(query, args...)
if err != nil {
return existingURLS, err
}
defer rows.Close()
for rows.Next() {
var eventURL string
err = rows.Scan(&eventURL)
if err == nil {
existingURLS = append(existingURLS, eventURL)
}
}
err = rows.Err()
if err != nil {
return existingURLS, err
}
return existingURLS, nil
}
/*
根据任务添加结果，修改rule的状态
*/
func (this *TaskDao) UpdateRules(rules []types.CrawlRule, taskAddedResults []sql.Result) (int64, error) {
if len(taskAddedResults) == 0 {
log.Errorln("no tasks added so no need to update rules!")
return 0, ErrNoTasks
}
if len(rules) != len(taskAddedResults) {
log.Errorln("rules number not equal to added tasks number!")
return 0, ErrNotEqual
}
ruleIds := []int32{}
var insertId int64
for i, result := range taskAddedResults {
insertId, _ = result.LastInsertId()
if insertId > 0 {
ruleIds = append(ruleIds, rules[i].Id)
}
}
sqlTmp := fmt.Sprintf("update %s set status=%d, update_time='%s' where id in (?)", RuleTable, RULE_ADDED, time.Now().Format("2006-01-02 15:04:05"))
sqlStr, args, err := sqlx.In(sqlTmp, ruleIds)
if err != nil {
log.Errorln("make in sql error: ", err)
return 0, err
}
var result sql.Result
var affectedRows int64
result, err = this.db.Exec(sqlStr, args...)
if err != nil {
log.Errorln("update rules error: ", err)
return 0, err
}
affectedRows, _ = result.RowsAffected()
return affectedRows, nil
}
func CreateLearningResource(lr *models.LearningResource) error {
var typeId int64
var languageIds []int64
tx := db.MustBegin()
// check for existence
count := 0
query := "select count(1) from gophergala_learning_resources where name=$1"
err := tx.Get(&count, query, lr.Name)
if err != nil {
return err
}
if count > 0 {
return errors.New("duplicate name for learning resource")
}
query = "select id from gophergala_types where name=$1;"
err = tx.Get(&typeId, query, lr.Type)
if err != nil {
return err
}
// add languages that don't exist
for _, l := range lr.Languages {
query = "select id from gophergala_languages where name=$1"
var languageId int64
err = tx.Get(&languageId, query, l)
if err == sql.ErrNoRows {
query = "insert into gophergala_languages (name) values($1)"
_, err = tx.Exec(query, l)
}
if err != nil {
log.Println(err)
return err
}
}
query = "select id from gophergala_languages where name in (?);"
query, args, err := sqlx.In(query, lr.Languages)
if err != nil {
return err
}
query = db.Rebind(query)
err = tx.Select(&languageIds, query, args...)
if err != nil {
return err
}
query = "insert into gophergala_learning_resources (name, url, type_id) values($1, $2, $3);"
_, err = tx.Exec(query, lr.Name, lr.URL, typeId)
if err != nil {
return err
}
// hacky ... sometimes causes weird dups if you put the same one in twice
query = "select id from gophergala_learning_resources where name=$1;"
var lastId int64
err = tx.Get(&lastId, query, lr.Name)
if err != nil {
return err
}
for _, languageId := range languageIds {
query = "insert into gophergala_learning_resource_languages (learning_resource_id, language_id) values($1, $2);"
_, err = tx.Exec(query, lastId, languageId)
if err != nil {
return err
}
}
err = tx.Commit()
return err
}
func (self *Reportd) getIndexData(c *gin.Context) gin.H {
db := self.db
data := gin.H{"PageTitle": "Report", "ReportTime": time.Now().Format("2006-01-02 15:04:05")}
var args []interface{} // sql query args
var params = make(map[string]interface{}) // url parameters
for k, v := range c.Request.URL.Query() {
params[k] = v
}
var results = []Result{}
for _, v := range conf.Reports {
sqlStr := v.Sql
var err error
if v.Params {
sqlStr, args, err = sqlx.Named(v.Sql, params)
if err != nil {
log.Println(err)
}
sqlStr, args, err = sqlx.In(sqlStr, args...)
if err != nil {
log.Println(err)
}
sqlStr = db.Rebind(sqlStr)
}
rows, err := db.Queryx(sqlStr, args...)
if err != nil {
log.Println(err)
}
defer rows.Close()
cols, _ := rows.Columns()
result := Result{Title: v.Title, Columns: cols}
for rows.Next() {
obj, err := rows.SliceScan()
for i, item := range obj {
if item == nil {
obj[i] = nil
} else {
t := reflect.TypeOf(item).Kind().String()
if t == "slice" {
obj[i] = string(item.([]byte))
}
}
}
if err == nil {
result.Rows = append(result.Rows, obj)
}
}
results = append(results, result)
}
data["Results"] = results
return data
}
// Save saves/replaces a given node.
func (ns NodeStore) Save(n entities.Node) error {
vars, err := n.Vars.Serialize()
if err != nil {
return err
}
// setup transaction
tx, err := ns.db.Beginx()
defer func() {
if err != nil {
tx.Rollback()
return
}
err = tx.Commit()
}()
// save node
node := &Node{
Name: n.Name,
Vars: string(vars),
}
result, err := tx.NamedExec(`INSERT OR REPLACE INTO
node(name, vars)
VALUES(:name, :vars);`, node)
// save relations to roles if required
if len(n.Roles) > 0 {
nodeID, err := result.LastInsertId()
if err != nil {
return err
}
data := struct {
NodeID int64 `db:"nid"`
Roles []string `db:"roles"`
}{
NodeID: nodeID,
Roles: n.Roles,
}
query, args, err := sqlx.Named(`INSERT OR IGNORE INTO
node_role(role_id, node_id, id)
SELECT r.id AS role_id,
:nid AS node_id,
:nid || '-' || r.ID AS id
FROM role r
WHERE r.name IN (:roles);`, data)
if err != nil {
return err
}
query, args, err = sqlx.In(query, args...)
if err != nil {
return err
}
query = tx.Rebind(query)
_, err = tx.Exec(query, args...)
}
return err
}
func (cq *ContactQuery) fillDependentData() (err error) {
db, err := DBConn()
if err != nil {
return
}
query, args, err := sqlx.In(PROFILES_QUERY, cq.collection.Ids())
if err != nil {
return
}
query = db.Rebind(query)
rows, err := db.Queryx(query, args...)
if err != nil {
return
}
defer rows.Close()
current := cq.collection.Next()
if current == nil {
return errors.New("Empty collection")
}
var (
profile *Profile
classUnit *ClassUnit
school *School
subject *Subject
)
for rows.Next() {
profile = NewProfile()
classUnit = NewClassUnit()
school = NewSchool()
subject = NewSubject()
rows.Scan(
&profile.Id,
&profile.Type,
&profile.UserId,
&school.Id,
&school.Name,
&school.Guid,
&classUnit.Id,
&classUnit.Name,
&classUnit.EnlistedOn,
&classUnit.LeftOn,
&subject.Id,
&subject.Name,
)
for *current.Id != *profile.UserId {
if next := cq.collection.Next(); next != nil {
current = next
} else {
break
}
}
if *current.Id != *profile.UserId {
continue
}
if classUnit.Id != nil {
profile.ClassUnit = classUnit
}
if school.Id != nil {
profile.School = school
}
if lastPr := current.LastProfile(); lastPr == nil {
current.Profiles = append(current.Profiles, profile)
} else if *lastPr.Id != *profile.Id {
current.Profiles = append(current.Profiles, profile)
}
if subject.Id != nil {
current.LastProfile().Subjects = append(
current.LastProfile().Subjects,
subject,
)
}
}
return
}
