2017-09-29 04:14:47 +00:00
package main
import (
"database/sql"
"encoding/json"
2020-01-28 23:30:52 +00:00
"errors"
2017-09-29 04:14:47 +00:00
"fmt"
"io/ioutil"
"math/rand"
"net/http"
2019-04-26 11:44:43 +00:00
"strconv"
"strings"
2017-09-29 04:14:47 +00:00
"time"
_ "github.com/go-sql-driver/mysql"
2019-04-26 11:44:43 +00:00
_ "github.com/mattn/go-sqlite3"
2017-09-29 04:14:47 +00:00
)
2019-04-26 11:44:43 +00:00
// Note: SQLite may not be compiled with support for UPDATE/DELETE LIMIT
2019-12-17 01:28:17 +00:00
const (
2020-10-20 20:08:00 +00:00
databaseVersion = 1
accountKeyLength = 32 // Was using MD5 hashes
messageMaxLength = 4096
googleOAuthURL = "https://www.googleapis.com/oauth2/v3/userinfo?alt=json&access_token="
2019-12-17 01:28:17 +00:00
)
2019-04-26 11:44:43 +00:00
// TODO: Add indexes
2020-10-20 20:08:00 +00:00
var databaseTables = map [ string ] [ ] string {
2019-04-26 11:44:43 +00:00
"accounts" : {
"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT" ,
"`key` VARCHAR(145) NOT NULL DEFAULT ''" ,
"`google_id` VARCHAR(200) NOT NULL DEFAULT ''" ,
"`facebook_id` VARCHAR(200) NOT NULL DEFAULT ''" ,
"`twitter_id` VARCHAR(200) NOT NULL DEFAULT ''" ,
"`openid_id` VARCHAR(200) NOT NULL DEFAULT ''" ,
"`email` VARCHAR(254) NOT NULL DEFAULT ''" ,
"`name` VARCHAR(50) NOT NULL DEFAULT ''" ,
"`registered` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`lastactive` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`streak` SMALLINT UNSIGNED NOT NULL DEFAULT 0" ,
"`topstreak` SMALLINT UNSIGNED NOT NULL DEFAULT 0" ,
"`streakend` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`streakbuffer` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0" ,
"`announcement` SMALLINT UNSIGNED NOT NULL DEFAULT 0" ,
"`sessionspublic` TINYINT UNSIGNED NOT NULL DEFAULT 0" ,
"`allowcontact` TINYINT UNSIGNED NOT NULL DEFAULT 0" } ,
"announcements" : {
"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT" ,
"`posted` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`text` TEXT NOT NULL DEFAULT ''" ,
"`active` TINYINT UNSIGNED NOT NULL DEFAULT 0" } ,
"sessions" : {
"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT" ,
"`account` INTEGER NOT NULL DEFAULT 0" ,
"`api` VARCHAR(145) NOT NULL DEFAULT ''" ,
"`ip` VARCHAR(145) NOT NULL DEFAULT ''" ,
"`market` VARCHAR(145) NOT NULL DEFAULT ''" ,
"`app` VARCHAR(145) NOT NULL DEFAULT ''" ,
"`posted` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`started` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`length` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0" ,
"`completed` INTEGER UNSIGNED NOT NULL DEFAULT 0" ,
"`message` TEXT NOT NULL DEFAULT ''" ,
"`streakday` SMALLINT UNSIGNED NOT NULL DEFAULT 0" ,
"`modified` INTEGER UNSIGNED NOT NULL DEFAULT 0" } ,
"meta" : {
2019-04-26 12:03:08 +00:00
"`key` VARCHAR(50) NOT NULL PRIMARY KEY" ,
2019-04-26 11:44:43 +00:00
"`value` TEXT NOT NULL DEFAULT ''" } }
2017-09-29 04:14:47 +00:00
2020-10-20 20:08:00 +00:00
type database struct {
2019-04-26 11:44:43 +00:00
db * sql . DB
FuncGreatest string
2017-09-29 04:14:47 +00:00
}
var letters = [ ] rune ( "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" )
2020-10-20 20:08:00 +00:00
type account struct {
2018-08-10 01:01:50 +00:00
ID int
Key string
StreakBuffer int
2017-09-29 04:14:47 +00:00
}
2020-10-20 20:08:00 +00:00
type session struct {
2017-09-29 04:14:47 +00:00
ID int ` json:"id" `
Posted int ` json:"posted" `
Started int ` json:"started" `
StreakDay int ` json:"streakday" `
Length int ` json:"length" `
Completed int ` json:"completed" `
Message string ` json:"message" `
2019-04-26 11:44:43 +00:00
Modified int ` json:"modified" `
2017-09-29 04:14:47 +00:00
}
2020-10-20 20:08:00 +00:00
type recentSession struct {
session
2020-10-20 20:03:53 +00:00
AccountID int
AccountName string
AccountEmail string
}
2017-09-29 04:14:47 +00:00
func generateKey ( ) string {
2020-10-20 20:08:00 +00:00
b := make ( [ ] rune , accountKeyLength )
2017-09-29 04:14:47 +00:00
for i := range b {
b [ i ] = letters [ rand . Intn ( len ( letters ) ) ]
}
return string ( b )
}
2020-10-20 20:08:00 +00:00
func connect ( driver string , dataSource string ) ( * database , error ) {
2017-09-29 04:14:47 +00:00
var err error
2020-10-20 20:08:00 +00:00
d := new ( database )
2017-09-29 04:14:47 +00:00
2019-04-26 11:44:43 +00:00
d . db , err = sql . Open ( driver , dataSource )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to connect to database: %s" , err )
2017-09-29 04:14:47 +00:00
}
2019-04-26 11:44:43 +00:00
d . FuncGreatest = "GREATEST"
if config . DBDriver == "sqlite3" {
d . FuncGreatest = "MAX"
_ , err = d . db . Exec ( ` PRAGMA encoding="UTF-8" ` )
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to send PRAGMA: %s" , err )
2019-04-26 11:44:43 +00:00
}
}
err = d . CreateTables ( )
if err != nil {
_ = d . db . Close ( )
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to create tables: %s" , err )
2019-04-26 11:44:43 +00:00
}
err = d . Migrate ( )
if err != nil {
_ = d . db . Close ( )
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to migrate database: %s" , err )
2019-04-26 11:44:43 +00:00
}
2017-09-29 04:14:47 +00:00
return d , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) CreateTables ( ) error {
2019-04-26 12:03:08 +00:00
var (
tcolumns string
err error
)
2019-04-26 11:44:43 +00:00
createQueryExtra := ""
if config . DBDriver == "mysql" {
createQueryExtra = " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci"
}
2020-10-20 20:08:00 +00:00
for tname , tcols := range databaseTables {
2019-04-26 12:03:08 +00:00
tcolumns = strings . Join ( tcols , "," )
if config . DBDriver == "mysql" {
tcolumns = strings . Replace ( tcolumns , "AUTOINCREMENT" , "AUTO_INCREMENT" , - 1 )
}
_ , err = d . db . Exec ( fmt . Sprintf ( "CREATE TABLE IF NOT EXISTS `%s` (%s)" , tname , tcolumns ) + createQueryExtra )
2019-04-26 11:44:43 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to create table %s: %s" , tname , err )
2019-04-26 11:44:43 +00:00
}
}
return nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) Migrate ( ) error {
2019-04-26 11:44:43 +00:00
rows , err := d . db . Query ( "SELECT `value` FROM meta WHERE `key`=?" , "version" )
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to fetch database version: %s" , err )
2019-04-26 11:44:43 +00:00
}
version := 0
for rows . Next ( ) {
v := ""
err = rows . Scan ( & v )
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to scan database meta: %s" , err )
2019-04-26 11:44:43 +00:00
}
version , err = strconv . Atoi ( v )
if err != nil {
version = - 1
}
}
if version == - 1 {
panic ( "Unable to migrate database: database version unknown" )
} else if version == 0 {
2020-10-20 20:08:00 +00:00
_ , err := d . db . Exec ( "UPDATE meta SET `value`=? WHERE `key`=?" , strconv . Itoa ( databaseVersion ) , "version" )
2019-04-26 11:44:43 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to save database version: %s" , err )
2019-04-26 11:44:43 +00:00
}
}
migrated := false
2020-10-20 20:08:00 +00:00
for version < databaseVersion {
2019-04-26 11:44:43 +00:00
switch version {
case 1 :
2020-10-20 20:08:00 +00:00
// databaseVersion 2 migration queries will go here
2019-04-26 11:44:43 +00:00
}
version ++
migrated = true
}
if migrated {
2020-10-20 20:08:00 +00:00
_ , err := d . db . Exec ( "UPDATE meta SET `value`=? WHERE `key`=?" , strconv . Itoa ( databaseVersion ) , "version" )
2019-04-26 11:44:43 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to save updated database version: %s" , err )
2019-04-26 11:44:43 +00:00
}
}
return nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) authenticate ( token string ) ( * account , error ) {
2017-09-29 04:14:47 +00:00
key := ""
2020-10-20 20:08:00 +00:00
resp , err := http . Get ( googleOAuthURL + token )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to get userinfo from Google: %s" , err )
2017-09-29 04:14:47 +00:00
}
defer resp . Body . Close ( )
data , err := ioutil . ReadAll ( resp . Body )
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to read userinfo response from Google: %s" , err )
2017-09-29 04:14:47 +00:00
}
var userinfo map [ string ] interface { }
err = json . Unmarshal ( data , & userinfo )
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to unmarshal userinfo response from Google: %s" , err )
2017-09-29 04:14:47 +00:00
}
googleid := ""
email := ""
name := ""
if v , ok := userinfo [ "sub" ] ; ok {
googleid = v . ( string )
}
if googleid == "" || googleid == "0" {
2018-06-08 01:13:23 +00:00
logDebugf ( "Userinfo: %+v" , userinfo )
logDebugf ( "Access token: %+v" , googleid )
2020-01-28 23:30:52 +00:00
return nil , errors . New ( "invalid access token" )
2017-09-29 04:14:47 +00:00
}
if v , ok := userinfo [ "email" ] ; ok {
email = v . ( string )
2017-12-15 02:01:48 +00:00
if len ( email ) > 75 {
email = ""
}
2017-09-29 04:14:47 +00:00
}
if v , ok := userinfo [ "name" ] ; ok {
name = v . ( string )
2017-12-15 02:01:48 +00:00
if len ( name ) > 50 {
name = name [ 0 : 50 ]
}
2017-09-29 04:14:47 +00:00
}
err = d . db . QueryRow ( "SELECT `key` FROM accounts WHERE google_id=?" , googleid ) . Scan ( & key )
if err == sql . ErrNoRows {
key = generateKey ( )
_ , err = d . db . Exec ( "INSERT INTO accounts (`key`, `google_id`, `email`, `name`, `registered`) VALUES(?, ?, ?, ?, ?)" , key , googleid , email , name , time . Now ( ) . Unix ( ) )
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to insert account: %s" , err )
2017-09-29 04:14:47 +00:00
}
2018-06-08 02:30:37 +00:00
stats . AccountsCreated ++
2017-09-29 04:14:47 +00:00
} else if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to fetch account key: %s" , err )
2017-09-29 04:14:47 +00:00
}
2018-06-08 02:30:37 +00:00
account , err := d . getAccount ( key )
failOnError ( err )
return account , nil
2017-09-29 04:14:47 +00:00
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getAccount ( key string ) ( * account , error ) {
a := new ( account )
2018-08-10 01:01:50 +00:00
err := d . db . QueryRow ( "SELECT `id`, `key`, `streakbuffer` FROM accounts WHERE `key`=?" , key ) . Scan ( & a . ID , & a . Key , & a . StreakBuffer )
2017-09-29 04:14:47 +00:00
if err == sql . ErrNoRows {
return nil , nil
} else if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "getAccount error: %s" , err )
2017-09-29 04:14:47 +00:00
}
return a , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getStreak ( accountID int ) ( int64 , int64 , int64 , error ) {
2019-01-21 14:02:20 +00:00
streakDay := int64 ( 0 )
streakEnd := int64 ( 0 )
topStreak := int64 ( 0 )
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
err := d . db . QueryRow ( "SELECT `streak`, `streakend`, `topstreak` FROM accounts WHERE `id`=?" , accountID ) . Scan ( & streakDay , & streakEnd , & topStreak )
2017-09-29 04:14:47 +00:00
if err == sql . ErrNoRows {
2020-01-28 23:30:52 +00:00
return 0 , 0 , 0 , errors . New ( "invalid account ID" )
2017-09-29 04:14:47 +00:00
} else if err != nil {
2020-01-28 23:30:52 +00:00
return 0 , 0 , 0 , fmt . Errorf ( "getStreak error: %s" , err )
2017-09-29 04:14:47 +00:00
}
// Expire streak
2019-01-21 14:02:20 +00:00
if streakEnd <= time . Now ( ) . Unix ( ) {
streakDay = 0
streakEnd = 0
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
_ , err := d . db . Exec ( "UPDATE accounts SET `streak`=?, `streakend`=? WHERE `id`=?" , streakDay , streakEnd , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return 0 , 0 , 0 , fmt . Errorf ( "failed to expire streak: %s" , err )
2017-09-29 04:14:47 +00:00
}
}
2019-01-21 14:02:20 +00:00
return streakDay , streakEnd , topStreak , nil
2017-09-29 04:14:47 +00:00
}
2020-10-20 20:08:00 +00:00
func ( d * database ) updateLastActive ( accountID int ) error {
2019-01-21 14:02:20 +00:00
_ , err := d . db . Exec ( "UPDATE accounts SET `lastactive`=? WHERE `id`=?" , time . Now ( ) . Unix ( ) , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
err = fmt . Errorf ( "failed to update last active: %s" , err )
2017-09-29 04:14:47 +00:00
}
return err
}
2020-10-20 20:08:00 +00:00
func ( d * database ) updateTopStreak ( accountID int ) error {
2019-04-26 11:44:43 +00:00
_ , err := d . db . Exec ( "UPDATE accounts SET `topstreak`=" + d . FuncGreatest + "(`streak`, `topstreak`) WHERE `id`=?" , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
err = fmt . Errorf ( "failed to update top streak: %s" , err )
2017-09-29 04:14:47 +00:00
}
return err
}
2020-10-20 20:08:00 +00:00
func ( d * database ) updateStreakBuffer ( accountID int , streakBuffer int ) error {
2019-01-21 14:02:20 +00:00
_ , err := d . db . Exec ( "UPDATE accounts SET `streakbuffer`=? WHERE `id`=?" , streakBuffer , accountID )
2018-08-10 01:01:50 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
err = fmt . Errorf ( "failed to update streak buffer: %s" , err )
2018-08-10 01:01:50 +00:00
}
return err
}
2020-10-20 20:08:00 +00:00
func ( d * database ) calculateStreak ( accountID int , streakBuffer int , tz * time . Location ) ( int , error ) {
2019-01-21 14:02:20 +00:00
streak := 0
2017-09-29 04:14:47 +00:00
t := time . Now ( ) . In ( tz )
2019-01-07 15:34:52 +00:00
logDebugf ( "calculate start %v" , t )
2019-01-21 14:02:20 +00:00
if beforeWindowStart ( t , streakBuffer ) {
2017-09-29 04:14:47 +00:00
t = t . AddDate ( 0 , 0 , - 1 )
2019-01-07 15:34:52 +00:00
logDebugf ( "calculate added %v" , t )
2017-09-29 04:14:47 +00:00
}
for {
2019-01-21 14:02:20 +00:00
exists , err := d . sessionExistsByDate ( t , accountID , streakBuffer )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return 0 , fmt . Errorf ( "failed to check if session exists for date: %s" , err )
2017-09-29 04:14:47 +00:00
} else if exists {
streak ++
t = t . AddDate ( 0 , 0 , - 1 )
} else {
break
}
}
2019-01-07 15:34:52 +00:00
logDebugf ( "calculated streak as %d" , streak )
2017-09-29 04:14:47 +00:00
return streak , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) setStreak ( streakDay int , accountID int , streakBuffer int , tz * time . Location ) error {
2017-09-29 04:14:47 +00:00
t := time . Now ( ) . In ( tz )
2019-01-21 14:02:20 +00:00
if beforeWindowStart ( t , streakBuffer ) {
t = t . AddDate ( 0 , 0 , 1 )
} else {
t = t . AddDate ( 0 , 0 , 2 )
}
t = atWindowStart ( t , streakBuffer )
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
logDebugf ( "SETTING STREAK Account %d, Day %d, TZ %s, Streak end: %d" , accountID , streakDay , tz . String ( ) , t . Unix ( ) )
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
_ , err := d . db . Exec ( "UPDATE accounts SET `streak`=?, `streakend`=? WHERE `id`=?" , streakDay , t . Unix ( ) , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to update streak: %s" , err )
2017-09-29 04:14:47 +00:00
}
2019-01-21 14:02:20 +00:00
err = d . updateTopStreak ( accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to update top streak: %s" , err )
2017-09-29 04:14:47 +00:00
}
return nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) setSessionStreakDay ( started int , streakDay int , accountID int ) error {
2019-04-26 11:44:43 +00:00
_ , err := d . db . Exec ( "UPDATE sessions SET `streakday`=? WHERE `account`=? AND `started`=?" , streakDay , accountID , started )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return fmt . Errorf ( "failed to set session streak day: %s" , err )
2017-09-29 04:14:47 +00:00
}
return nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) scanSession ( rows * sql . Rows ) ( * session , error ) {
s := new ( session )
2019-04-26 11:44:43 +00:00
err := rows . Scan ( & s . ID , & s . Posted , & s . Started , & s . StreakDay , & s . Length , & s . Completed , & s . Message , & s . Modified )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to scan session: %s" , err )
2017-09-29 04:14:47 +00:00
}
return s , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) scanRecentSession ( rows * sql . Rows ) ( * recentSession , error ) {
s := new ( recentSession )
2020-10-20 20:03:53 +00:00
err := rows . Scan ( & s . ID , & s . Posted , & s . Started , & s . StreakDay , & s . Length , & s . Completed , & s . Message , & s . Modified , & s . AccountID , & s . AccountName , & s . AccountEmail )
if err != nil {
return nil , fmt . Errorf ( "failed to scan session: %s" , err )
}
return s , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) addSession ( s session , updateSessionStarted int , accountID int , appVer string , appMarket string ) ( bool , error ) {
2019-04-26 11:44:43 +00:00
var (
2020-10-20 20:08:00 +00:00
existingSession * session
updateSession * session
keepSession * session
2017-09-29 04:14:47 +00:00
2019-04-26 11:44:43 +00:00
err error
)
2017-09-29 04:14:47 +00:00
2019-04-26 11:44:43 +00:00
existingSession , err = d . getSessionByStarted ( s . Started , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to fetch session: %s" , err )
2017-09-29 04:14:47 +00:00
}
2019-04-26 11:44:43 +00:00
if updateSessionStarted > 0 && updateSessionStarted != s . Started {
updateSession , err = d . getSessionByStarted ( updateSessionStarted , accountID )
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to fetch session: %s" , err )
2019-04-26 11:44:43 +00:00
}
}
2017-09-29 04:14:47 +00:00
2019-04-26 11:44:43 +00:00
if ( existingSession != nil && existingSession . Modified >= s . Modified ) || ( updateSession != nil && updateSession . Modified >= s . Modified ) {
return false , nil
2019-01-21 14:02:20 +00:00
}
2020-10-20 20:08:00 +00:00
if len ( s . Message ) > messageMaxLength {
s . Message = s . Message [ : messageMaxLength ]
2019-12-17 01:28:17 +00:00
}
2019-01-21 14:02:20 +00:00
// Fix zero completed from older versions of the app
if s . Completed == 0 {
s . Completed = s . Started + s . Length
}
2019-04-26 11:44:43 +00:00
if existingSession == nil && updateSession == nil {
_ , err = d . db . Exec ( "INSERT INTO sessions (`account`, `market`, `app`, `posted`, `started`, `streakday`, `length`, `completed`, `message`, `modified`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" , accountID , appMarket , appVer , time . Now ( ) . Unix ( ) , s . Started , s . StreakDay , s . Length , s . Completed , s . Message , s . Modified )
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to add session: %s" , err )
2019-04-26 11:44:43 +00:00
}
} else {
keepSession = updateSession
if keepSession == nil {
keepSession = existingSession
} else if existingSession != nil {
_ , err = db . deleteSession ( existingSession . Started , accountID )
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to delete existing session: %s" , err )
2019-04-26 11:44:43 +00:00
}
}
2019-01-21 14:02:20 +00:00
2019-04-26 11:44:43 +00:00
_ , err = d . db . Exec ( "UPDATE sessions SET `started`=?, `length`=?, `completed`=?, `message`=?, `modified`=? WHERE `account`=? AND `started`=?" , s . Started , s . Length , s . Completed , s . Message , s . Modified , accountID , keepSession . Started )
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to update session: %s" , err )
2019-04-26 11:44:43 +00:00
}
2019-01-21 14:02:20 +00:00
}
return true , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getSessionByID ( sessionID int , accountID int ) ( * session , error ) {
2019-04-26 11:44:43 +00:00
rows , err := d . db . Query ( "SELECT `id`, `posted`, `started`, `streakday`, `length`, `completed`, `message`, `modified` FROM sessions WHERE `account`=? AND `id`=?" , accountID , sessionID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to fetch session: %s" , err )
2017-09-29 04:14:47 +00:00
}
defer rows . Close ( )
for rows . Next ( ) {
return d . scanSession ( rows )
}
return nil , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getSessionByStarted ( started int , accountID int ) ( * session , error ) {
2019-04-26 11:44:43 +00:00
rows , err := d . db . Query ( "SELECT `id`, `posted`, `started`, `streakday`, `length`, `completed`, `message`, `modified` FROM sessions WHERE `account`=? AND `started`=?" , accountID , started )
2019-01-21 14:02:20 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to fetch session: %s" , err )
2019-01-21 14:02:20 +00:00
}
defer rows . Close ( )
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
for rows . Next ( ) {
return d . scanSession ( rows )
}
return nil , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) sessionExistsByDate ( date time . Time , accountID int , streakBuffer int ) ( bool , error ) {
2019-01-21 14:02:20 +00:00
windowStart := atWindowStart ( date , streakBuffer )
windowEnd := atWindowStart ( windowStart . AddDate ( 0 , 0 , 1 ) , streakBuffer )
logDebugf ( "SESSION EXISTS %v - START %v END %v" , date , windowStart . Unix ( ) , windowEnd . Unix ( ) )
sessionid := 0
2017-09-29 04:14:47 +00:00
2019-01-21 14:02:20 +00:00
err := d . db . QueryRow ( "SELECT `id` FROM sessions WHERE `account`=? AND `started`>=? AND `started`<? LIMIT 1" , accountID , windowStart . Unix ( ) , windowEnd . Unix ( ) ) . Scan ( & sessionid )
2017-09-29 04:14:47 +00:00
if err != nil && err != sql . ErrNoRows {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "sessionExistsByDate failed: %s" , err )
2017-09-29 04:14:47 +00:00
}
return sessionid > 0 , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getAllSessions ( accountID int ) ( [ ] * session , error ) {
var sessions [ ] * session
2017-09-29 04:14:47 +00:00
2019-04-26 11:44:43 +00:00
rows , err := d . db . Query ( "SELECT `id`, `posted`, `started`, `streakday`, `length`, `completed`, `message`, `modified` FROM sessions WHERE `account`=?" , accountID )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to fetch sessions: %s" , err )
2017-09-29 04:14:47 +00:00
}
defer rows . Close ( )
for rows . Next ( ) {
s , err := d . scanSession ( rows )
if err != nil {
2020-01-28 23:30:52 +00:00
return nil , fmt . Errorf ( "failed to scan session: %s" , err )
2017-09-29 04:14:47 +00:00
}
2020-10-20 20:03:53 +00:00
sessions = append ( sessions , s )
}
return sessions , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) getRecentSessions ( ) ( [ ] * recentSession , error ) {
var sessions [ ] * recentSession
2020-10-20 20:03:53 +00:00
rows , err := d . db . Query ( "SELECT `sessions`.`id`, `sessions`.`posted`, `sessions`.`started`, `sessions`.`streakday`, `sessions`.`length`, `sessions`.`completed`, `sessions`.`message`, `sessions`.`modified`, `accounts`.`id` AS `accountid`, `accounts`.`name`, `accounts`.`email` FROM `sessions` LEFT OUTER JOIN `accounts` ON `sessions`.`account` = `accounts`.`id` WHERE `accounts`.`sessionspublic` = 1 AND `sessions`.`length` > 110 ORDER BY `sessions`.`completed` DESC LIMIT 50" )
if err != nil {
return nil , fmt . Errorf ( "failed to fetch recent sessions: %s" , err )
}
defer rows . Close ( )
for rows . Next ( ) {
rs , err := d . scanRecentSession ( rows )
if err != nil {
return nil , fmt . Errorf ( "failed to scan recent session: %s" , err )
}
sessions = append ( sessions , rs )
2017-09-29 04:14:47 +00:00
}
return sessions , nil
}
2020-10-20 20:08:00 +00:00
func ( d * database ) deleteSession ( started int , accountID int ) ( bool , error ) {
2019-04-26 11:44:43 +00:00
r , err := d . db . Exec ( "DELETE FROM sessions WHERE `account`=? AND `started`=?" , accountID , started )
2017-09-29 04:14:47 +00:00
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to delete session: %s" , err )
2017-09-29 04:14:47 +00:00
}
affected , err := r . RowsAffected ( )
if err != nil {
2020-01-28 23:30:52 +00:00
return false , fmt . Errorf ( "failed to fetch number of deleted sessions: %s" , err )
2017-09-29 04:14:47 +00:00
}
return affected > 0 , nil
}