scrobbler: record how long a song was played

Add a column `playtime` to the records table to keep track of how long a
song was played.

With this information, in the future, we will be able to sum up how long
we listen to music, but also which songs were skipped.

2 files changed, 16 insertions, 1 deletions

diff --git a/tools/mpd-stats/internal/scrobbler/db.go b/tools/mpd-stats/internal/scrobbler/db.go
index a788b4c..5f80aa4 100644
--- a/tools/mpd-stats/internal/scrobbler/db.go
+++ b/tools/mpd-stats/internal/scrobbler/db.go
@@ -24,6 +24,7 @@ func initdb(dbpath string) error {
 				artist text,
                 album text,
                 duration int,
+                playtime int,
                 time timestamp
 			);`
 
diff --git a/tools/mpd-stats/internal/scrobbler/scrobbler.go b/tools/mpd-stats/internal/scrobbler/scrobbler.go
index e16458c..df8e46a 100644
--- a/tools/mpd-stats/internal/scrobbler/scrobbler.go
+++ b/tools/mpd-stats/internal/scrobbler/scrobbler.go
@@ -3,6 +3,7 @@ package scrobbler
 import (
 	"database/sql"
 	"log"
+	"time"
 
 	"golang.fcuny.net/mpd-stats/internal/mpd"
 )
@@ -67,6 +68,9 @@ func (s *Scrobbler) Run() error {
 			}
 
 			if currentRecord.Id != previousRecord.Id {
+				if err := s.update(previousRecord); err != nil {
+					log.Printf("failed to update record %s: %s", previousRecord.Id, err)
+				}
 				previousRecord = currentRecord
 				s.save(currentRecord)
 			}
@@ -75,7 +79,7 @@ func (s *Scrobbler) Run() error {
 }
 
 func (s *Scrobbler) save(record *Record) error {
-	_, err := s.db.Exec("insert into records(id, title, artist, album, duration, time) values(?, ?, ?, ?, ?, ?)",
+	_, err := s.db.Exec("insert into records(id, title, artist, album, duration, playtime, time) values(?, ?, ?, ?, ?, 0, ?)",
 		record.Id,
 		record.Title,
 		record.Artist,
@@ -85,3 +89,13 @@ func (s *Scrobbler) save(record *Record) error {
 	)
 	return err
 }
+
+func (s *Scrobbler) update(record *Record) error {
+	tnow := time.Now()
+	playtime := tnow.Sub(record.Timestamp).Seconds()
+	_, err := s.db.Exec("update records set playtime = ? where id = ?",
+		int(playtime),
+		record.Id,
+	)
+	return err
+}