func AllDistinctDates(db gorm.DB) []Date { var results []Date scope := db.Table("media").Select("date_part('day', taken_at) as day, date_part('month', taken_at) as month, date_part('year', taken_at) as year") scope = scope.Group("day, month, year").Order("year, month, day") scope.Scan(&results) return results }
func isAuthorized(db *gorm.DB, nameOfTable, header string) (*authPair, bool) { // blank login and password with ":" separator return, in base64, a size of 10 char if len(header) <= 10 { return nil, false } var i int var u authPair var cred string // get credentials from header if value, err := base64.StdEncoding.DecodeString(header[6:]); err != nil { return nil, false } else { cred = string(value) } // check ":" separator i = strings.Index(cred, ":") // check present in database db.Table(nameOfTable).Select( "identifiant, mot_de_passe").Where( "identifiant=? and mot_de_passe=?", cred[:i], cred[i+1:]).Find(&u) if reflect.DeepEqual(u, authPair{}) { return nil, false } // success return &u, true }
func getPlayerWeapons(db *gorm.DB, player uint) []WeaponStat { // have some initial capacity ws := make([]WeaponStat, 0, 10) rows, err := db.Table("weapon_stats"). Select("type, sum(weapon_stats.shots) as shots, sum(weapon_stats.hits) as hits, sum(weapon_stats.kills) as kills"). Joins("join player_match_stats on weapon_stats.player_match_stat_id = player_match_stats.id"). Joins("join aliases on player_match_stats.alias_id = aliases.id"). Where("aliases.player_id = ?", player). Group("weapon_stats.type"). Rows() if err != nil { log.Printf("query failed: %s", err) return ws } for rows.Next() { var t string // type var k, s, h uint // kills, shots, hits if err := rows.Scan(&t, &s, &h, &k); err != nil { log.Printf("failed to scan row: %s", err) } log.Printf("stats: %s %v %v %v", t, s, h, k) ws = append(ws, WeaponStat{ Shots: s, Kills: k, Hits: h, Type: t, }) } return ws }
func makeIndexes(db *gorm.DB) { log.Println("makeing indexes START") db.Table("articles").AddIndex("articlesYear", "year") db.Table("articles").AddIndex("articlesJournalId", "journal_id") log.Println("makeing indexes END") }
func CreateClient(db gorm.DB, options *Options) (id int64) { var c Client db.Where( &Client{ Hostname: options.Hostname, Interface: options.Interface, MacAddr: options.InterfaceData.HardwareAddr.String(), Ip: options.Ip, }, ).First(&c) if c.Id != 0 { db.Model(&c).Update( &Client{ LastSeen: time.Now().Unix(), }, ) } else { c.LastSeen = time.Now().Unix() c.Hostname = options.Hostname c.MacAddr = options.InterfaceData.HardwareAddr.String() c.Interface = options.Interface c.Ip = options.Ip db.Table("clients").Create(&c) } options.Client = &c return c.Id }
func isAuthorized(db *gorm.DB, nameOfTable, header string) (*authPair, bool) { var i int var u authPair var cred string // get credentials from header if value, err := base64.StdEncoding.DecodeString(header); err != nil { return nil, false } else { cred = string(value) } // check format if i = strings.Index(cred, ":"); len(cred) < 8 || i < 8 { return nil, false } // check present in database db.Table(nameOfTable).Select( "identifiant, mot_de_passe").Where( "identifiant=? and mot_de_passe=?", cred[6:i], cred[i:]).Find(&u) if reflect.DeepEqual(u, authPair{}) { return nil, false } // success return &u, true }
func queryTags(db *gorm.DB, postID int64) []*Tag { var results []*Tag join := "inner join tagmap on tagmap.tag_id = tag.id" tables := db.Table("tag").Select("tag.tag").Joins(join) tables.Where("tagmap.post_id = ?", postID).Scan(&results) logger.LogIff(db.Error, "error querying tags for post %d", postID) return results }
func Update(id string, data *UserJSON, DB *gorm.DB) *gorm.DB { __id, _ := utils.ParseId(id) result := DB.Table("user").Where(gorm.Model{ID: __id}).UpdateColumn(&data) if result.Error == nil { return FetchUserById(id, DB) } return result }
func fetchTrackerQueries(db *gorm.DB) []trackerSchedule { tsList := []trackerSchedule{} err := db.Table("tracker_queries").Select("trackers.id as tracker_id, trackers.url, trackers.type as tracker_type, tracker_queries.query, tracker_queries.schedule").Joins("left join trackers on tracker_queries.tracker_id = trackers.id").Where("trackers.deleted_at is NULL AND tracker_queries.deleted_at is NULL").Scan(&tsList).Error if err != nil { log.Error(nil, map[string]interface{}{ "err": err, }, "fetch operation failed for tracker queries") } return tsList }
// GetWhitelistedTorrents allows us to retrieve all of the white listed // torrents. Mostly used for populating the Redis KV storage with all of our // whitelisted torrents. func GetWhitelistedTorrents(dbConn *gorm.DB) (x *sql.Rows, err error) { dbConn = assertOpenConnection(dbConn) x, err = dbConn.Table("white_torrents").Rows() if err != nil { return } return }
func queryComments(db *gorm.DB, postID int64) []*Comment { var comments []*Comment join := "inner join commenter on comment.commenter_id = commenter.id" order := "timestamp asc" tables := db.Table("comment").Select("*").Joins(join) rows := tables.Where("post_id = ?", postID).Order(order) err := rows.Scan(&comments).Error logger.LogIff(err, "error querying comments") for _, c := range comments { c.EmailHash = md5Hash(c.Email) c.Time = time.Unix(c.Timestamp, 0).Format("2006-01-02 15:04") c.Body = sanitizeHTML(mdToHTML(c.RawBody)) } return comments }
func GetMessages(db gorm.DB) { var messages []Message partner := os.Args[2] if !HasContact(db, partner) { fmt.Printf("You don't have a contact with username %q", partner) return } db.Table("messages").Where("dialog_partner = ?", partner).Or("author = ?", partner).Where("chatmsg_type = ?", 3).Order("timestamp desc").Limit(30).Find(&messages) if len(messages) <= 0 { fmt.Println("No records found!") } else { for _, message := range messages { fmt.Println(message.From, message.Content, time.Unix(message.Timestamp, 0)) } } }
func (user *User) FetchSyncs(db *gorm.DB) error { query := db.Table("syncs AS Sync").Select("Sync.id, Sync.weekday, Sync.created_at"). Joins("LEFT JOIN sync_users AS SyncUser ON (SyncUser.sync_id = Sync.id)"). Where("SyncUser.user_id = ?", user.Id). Find(&user.Syncs) if query.Error != nil { return query.Error } if len(user.Syncs) == 0 { return nil } ids := make([]int64, len(user.Syncs)) syncsMap := map[int64]*Sync{} for idx, sync := range user.Syncs { ids[idx] = sync.Id syncsMap[sync.Id] = &user.Syncs[idx] } syncUsers := []SyncUser{} query = db.Model(&SyncUser{}).Where("sync_id IN (?)", ids).Find(&syncUsers) if query.Error != nil { return query.Error } for _, syncUser := range syncUsers { if err := db.Model(&syncUser).Select("id, username, email, email_verified, first_name, last_name, ride").Related(&syncUser.User).Error; err != nil { return err } if err := db.Model(&syncUser.User).Related(&syncUser.User.Schedules).Error; err != nil { return err } syncsMap[syncUser.SyncId].SyncUsers = append(syncsMap[syncUser.SyncId].SyncUsers, syncUser) } return nil }
// map json from player to logicserver func mapPlayerToGlobal(db gorm.DB) *gabs.Container { // TODO: IMPORTANT THIS RELIES ON GAME TICK LOGIC var player_actions []PlayerAction // fetch all active entries to memory // ordering by game tick so only recent ones are taken db.Where("Stale = ?", false).Order("GameTick").Find(&player_actions) // update all the entries as stale in a batch // TODO: ideally the above two should be in a transaction db.Table("player_action").Where("Stale = ?", false).Updates(PlayerAction{Stale: true}) jsonObj := gabs.New() // ref: https://github.com/Jeffail/gabs#generating-json for _, player_state := range player_actions { jsonObj.Set(player_state.Actions, player_state.Username, "Actions") jsonObj.Set(player_state.GameTick, player_state.Username, "GameTick") } return jsonObj }
func getPlayerItems(db *gorm.DB, player uint) []ItemStat { // have some initial capacity is := make([]ItemStat, 0, 10) rows, err := db.Table("item_stats"). Select("type, sum(item_stats.pickups) as pickups, sum(item_stats.time) as time"). Joins("join player_match_stats on item_stats.player_match_stat_id = player_match_stats.id"). Joins("join aliases on player_match_stats.alias_id = aliases.id"). Where("aliases.player_id = ?", player). Group("item_stats.type"). Rows() if err != nil { log.Printf("query failed: %s", err) return is } for rows.Next() { var t string // type var p uint // pickups var d uint64 // duration if err := rows.Scan(&t, &p, &d); err != nil { log.Printf("failed to scan row: %s", err) } dur := time.Duration(d) log.Printf("stats: %s %v %v", t, p, dur) is = append(is, ItemStat{ Pickups: p, Time: dur, Type: t, }) } return is }
func HasContact(db gorm.DB, username string) bool { count := 0 db.Table("contacts").Where("skypename = ?", username).Count(&count) return (count > 0) }
func (pa *PersonAddress) JoinWith(handler gorm.JoinTableHandlerInterface, db *gorm.DB, source interface{}) *gorm.DB { table := pa.Table(db) return db.Table(table).Joins("INNER JOIN person_addresses ON person_addresses.address_id = addresses.id").Where(fmt.Sprintf("%v.deleted_at IS NULL OR %v.deleted_at <= '0001-01-02'", table, table)) }
func (question *Question) ToDatabase(db gorm.DB, options *Options) (err error) { var q Question var id int64 = 0 db.Where( &Question{ SrcIp: question.SrcIp, DstIp: question.DstIp, Question: question.Question, }, ).First(&q) if q.Id != 0 { db.Model(&q).Update( &Question{ UpdatedAt: time.Now().Unix(), SeenCount: q.SeenCount + 1, }, ) id = q.Id } else { options.Client.QuestionCount = options.Client.QuestionCount + 1 db.Exec("UPDATE clients SET question_count=? WHERE id=?", options.Client.QuestionCount, options.Client.Id) var time int64 = time.Now().Unix() question.SeenCount = 1 question.CreatedAt = time question.UpdatedAt = time db.Table("questions").Create(question) id = question.Id } go func() { for _, a := range question.Answers { var aa Answer db.Table("answers").Where( &Answer{ Name: a.Name, Data: a.Data, Record: a.Record, Class: a.Class, QuestionId: id, ClientId: options.Client.Id, }, ).First(&aa) if aa.Id != 0 { db.Model(&aa).Update( &Answer{ SeenCount: aa.SeenCount + 1, Active: true, }, ) } else { a.ClientId = options.Client.Id a.QuestionId = id a.SeenCount = 1 a.Active = true db.Table("answers").Create(a) } } var olda []Answer db.Where(&Answer{ QuestionId: id, ClientId: options.Client.Id, }).Find(&olda) for _, a := range olda { index := contains(a, question.Answers) if index == -1 { a.Active = false a.UpdatedAt = time.Now().Unix() db.Save(&a) } } }() return nil }