func (d *Postgres) getLogTags(tx *sql.Tx, log *models.Log) error {
var (
tag string
tags []string
)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"log_tag"`).
Map(`"tag"`, &tag).
Where(`"log_id" = ?`, log.ID).
Build()
rows, err := tx.Query(query, args...)
if err != nil {
return err
}
for rows.Next() {
if err := rows.Scan(dest...); err != nil {
return err
}
tags = append(tags, tag)
}
if err := rows.Err(); err != nil {
return err
}
log.Tags = tags
return nil
}
func (d *Postgres) UserLogYears(user *models.User) ([]int, error) {
var (
years []int
year int
)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"log"`).
Map(`DISTINCT EXTRACT(YEAR FROM "start")`, &year).
Where(`"user_id" = ?`, user.ID).
Order(`EXTRACT(YEAR FROM "start") ASC`).
Build()
rows, err := d.db.Query(query, args...)
if err != nil {
return nil, err
}
for rows.Next() {
if err := rows.Scan(dest...); err != nil {
return nil, err
}
years = append(years, year)
}
if err := rows.Err(); err != nil {
return nil, err
}
return years, nil
}
func (d *Postgres) UserLogsByYear(user *models.User, year int) ([]*models.Log, error) {
tx, err := d.db.Begin()
if err != nil {
return nil, err
}
defer tx.Rollback() // read-only transaction
var (
log models.Log
logs []*models.Log
)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"log"`).
Map(`"id"`, &log.ID).
Map(`"name"`, &log.Name).
Map(`"start"`, &log.Start).
Map(`"end"`, &log.End).
Map(`"duration"`, &log.Duration).
Map(`"distance"`, &log.Distance).
Map(`"gpx"`, &log.GPX).
Where(`"user_id" = ?`, user.ID).
Where(`EXTRACT(YEAR FROM "start") = ?`, year).
Order(`"start" DESC`).
Build()
rows, err := tx.Query(query, args...)
if err != nil {
return nil, err
}
for rows.Next() {
if err := rows.Scan(dest...); err != nil {
rows.Close()
return nil, err
}
l := new(models.Log)
*l = log
logs = append(logs, l)
}
if err := rows.Err(); err != nil {
return nil, err
}
for _, log := range logs {
if err := d.getLogTags(tx, log); err != nil {
return nil, err
}
}
return logs, nil
}
func (d *Postgres) getLogTracks(tx *sql.Tx, log *models.Log) error {
var (
track models.Track
tracks []*models.Track
)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"track"`).
Map(`"id"`, &track.ID).
Map(`"log_id"`, &track.LogID).
Map(`COALESCE("name", '')`, &track.Name).
Map(`"start"`, &track.Start).
Map(`"end"`, &track.End).
Map(`"duration"`, &track.Duration).
Map(`"distance"`, &track.Distance).
Where(`"log_id" = ?`, log.ID).
Build()
rows, err := tx.Query(query, args...)
if err != nil {
return err
}
for rows.Next() {
if err := rows.Scan(dest...); err != nil {
return err
}
t := new(models.Track)
*t = track
tracks = append(tracks, t)
}
if err := rows.Err(); err != nil {
return err
}
for _, track := range tracks {
if err := d.getTrackPoints(tx, track); err != nil {
return err
}
}
log.Tracks = tracks
return nil
}
func (d *Postgres) UserLogByID(user *models.User, id int) (*models.Log, error) {
tx, err := d.db.Begin()
if err != nil {
return nil, err
}
defer tx.Rollback() // read-only transaction
log := new(models.Log)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"log"`).
Map(`"id"`, &log.ID).
Map(`"user_id"`, &log.UserID).
Map(`"name"`, &log.Name).
Map(`"start"`, &log.Start).
Map(`"end"`, &log.End).
Map(`"duration"`, &log.Duration).
Map(`"distance"`, &log.Distance).
Map(`"gpx"`, &log.GPX).
Where(`"id" = ?`, id).
Build()
err = tx.QueryRow(query, args...).Scan(dest...)
if err == sql.ErrNoRows {
return nil, nil
}
if err != nil {
return nil, err
}
if err := d.getLogTracks(tx, log); err != nil {
return nil, err
}
if err := d.getLogTags(tx, log); err != nil {
return nil, err
}
return log, nil
}
func (d *Postgres) UserByUsername(username string) (*models.User, error) {
user := new(models.User)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"user"`).
Map(`"id"`, &user.ID).
Map(`"username"`, &user.Username).
Map(`"password"`, &user.Password).
Map(`"password_version"`, &user.PasswordVersion).
Where(`"username" = ?`, username).
Build()
err := d.db.QueryRow(query, args...).Scan(dest...)
if err == sql.ErrNoRows {
return nil, nil
}
if err != nil {
return nil, err
}
return user, nil
}
func (d *Postgres) getTrackPoints(tx *sql.Tx, track *models.Track) error {
var (
point models.Point
points []*models.Point
)
query, args, dest := sqlbuilder.Select().
Dialect(sqlbuilder.Postgres).
From(`"trackpoint"`).
Map(`"id"`, &point.ID).
Map(`"track_id"`, &point.TrackID).
Map(`"point"[0]`, &point.Longitude).
Map(`"point"[1]`, &point.Latitude).
Map(`"time"`, &point.Time).
Map(`"elevation"`, &point.Elevation).
Map(`"heartrate"`, &point.Heartrate).
Where(`"track_id" = ?`, track.ID).
Build()
rows, err := tx.Query(query, args...)
if err != nil {
return err
}
for rows.Next() {
if err := rows.Scan(dest...); err != nil {
return err
}
p := new(models.Point)
*p = point
points = append(points, p)
}
if err := rows.Err(); err != nil {
return err
}
track.Points = points
return nil
}