6 "maunium.net/go/mautrix/id"
14 NotActive State = iota
27 CREATE TABLE IF NOT EXISTS room_users (
28 room_id STRING NOT NULL,
29 user_id STRING NOT NULL,
30 state_time TIMESTAMP NOT NULL,
32 idle_nsec INT64 NOT NULL,
33 active_nsec INT64 NOT NULL,
34 PRIMARY KEY(room_id, user_id)
38 const fetchStateQuery = `
39 SELECT state_time, state, idle_nsec, active_nsec
45 const insertStateQuery = `
46 INSERT INTO room_users(room_id, user_id, state_time, state, idle_nsec, active_nsec)
47 VALUES(?, ?, ?, ?, 0.0, 0.0)
50 const updateStateQuery = `
52 SET state_time = ?, state = ?, idle_nsec = ?, active_nsec = ?
57 const fetchUserScoresQuery = `
58 SELECT user_id, state_time, state, idle_nsec, active_nsec
65 func InitDatabase() error {
67 db, err = sql.Open("sqlite", "users.sqlite")
69 return fmt.Errorf("could not open SQLite database: %v", err)
71 _, err = db.Exec(dbSchema)
73 return fmt.Errorf("could not set SQLite database schema: %v", err)
78 func CloseDatabase() error {
82 func queryUserScores(room id.RoomID, now time.Time) (map[id.UserID]*Score, error) {
83 var users map[id.UserID]*Score
84 err := retryPolicy(func() error {
85 rows, err := db.Query(fetchUserScoresQuery, room)
87 return fmt.Errorf("could not query users: %v", err)
89 users = map[id.UserID]*Score{}
93 if err := rows.Scan(&user, &score.LastEvent, &score.CurrentState, &score.Idle, &score.Active); err != nil {
94 return fmt.Errorf("could not scan users query result: %v", err)
96 newScore := advanceScore(score, now)
97 users[user] = &newScore
99 if err := rows.Err(); err != nil {
100 return fmt.Errorf("could not read users: %v", err)
107 func advanceScore(score Score, now time.Time) Score {
108 if !now.After(score.LastEvent) {
111 dt := now.Sub(score.LastEvent)
112 switch score.CurrentState {
121 func retryPolicy(f func() error) error {
123 for attempt := 0; attempt < 12; attempt++ {
128 time.Sleep(time.Millisecond * time.Duration(1<<attempt))
133 func inTx(db *sql.DB, f func(tx *sql.Tx) error) error {
134 tx, err := db.Begin()
136 return fmt.Errorf("failed to create transaction: %v", err)
146 func writeUserStateAt(room id.RoomID, user id.UserID, now time.Time, maxPrevState, state State) error {
147 return retryPolicy(func() error {
148 return inTx(db, func(tx *sql.Tx) error {
149 row := tx.QueryRow(fetchStateQuery, room, user)
151 err := row.Scan(&score.LastEvent, &score.CurrentState, &score.Idle, &score.Active)
152 if err == sql.ErrNoRows {
153 _, err = tx.Exec(insertStateQuery, room, user, now, state)
155 return fmt.Errorf("failed to set state for new user: %v", err)
160 return fmt.Errorf("failed to fetch state for user: %v", err)
162 if now.After(score.LastEvent) {
163 if score.CurrentState > maxPrevState {
164 score.CurrentState = maxPrevState
166 score = advanceScore(score, now)
167 _, err = tx.Exec(updateStateQuery, now, state, score.Idle, score.Active, room, user)
169 return fmt.Errorf("failed to update state for new user: %v", err)
173 _, err = tx.Exec(updateStateQuery, score.LastEvent, state, score.Idle, score.Active, room, user)
175 return fmt.Errorf("failed to update state for new user: %v", err)