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
}