Golang DB.Table方法代码示例

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
}