scrobbler: add interface to the sqlite3 database

We want to persist the records in a database, so we can extract
statistics and an history.

The module for the database is straightforward: it opens the database if
it exists and return an handler to it. If the database does not exists,
we create it and we create the only table we need (records).

3 files changed, 57 insertions, 0 deletions

diff --git a/tools/mpd-stats/go.mod b/tools/mpd-stats/go.mod
index 6ffe974..cc9971c 100644
--- a/tools/mpd-stats/go.mod
+++ b/tools/mpd-stats/go.mod
@@ -5,4 +5,5 @@ go 1.17
 require (
 	github.com/fhs/gompd/v2 v2.2.0
 	github.com/google/uuid v1.3.0
+	github.com/mattn/go-sqlite3 v1.14.8
 )
diff --git a/tools/mpd-stats/go.sum b/tools/mpd-stats/go.sum
index 127090d..fab0f00 100644
--- a/tools/mpd-stats/go.sum
+++ b/tools/mpd-stats/go.sum
@@ -2,3 +2,5 @@ github.com/fhs/gompd/v2 v2.2.0 h1:zdSYAAOzQ5cCCgYa5CoXkL0Vr0Cqb/b5JmTobirLc90=
 github.com/fhs/gompd/v2 v2.2.0/go.mod h1:nNdZtcpD5VpmzZbRl5rV6RhxeMmAWTxEsSIMBkmMIy4=
 github.com/google/uuid v1.3.0 h1:t6JiXgmwXMjEs8VusXIJk2BXHsn+wx8BZdTaoZ5fu7I=
 github.com/google/uuid v1.3.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
+github.com/mattn/go-sqlite3 v1.14.8 h1:gDp86IdQsN/xWjIEmr9MF6o9mpksUgh0fu+9ByFxzIU=
+github.com/mattn/go-sqlite3 v1.14.8/go.mod h1:NyWgC/yNuGj7Q9rpYnZvas74GogHl5/Z4A/KQRfk6bU=
diff --git a/tools/mpd-stats/internal/scrobbler/db.go b/tools/mpd-stats/internal/scrobbler/db.go
new file mode 100644
index 0000000..a788b4c
--- /dev/null
+++ b/tools/mpd-stats/internal/scrobbler/db.go
@@ -0,0 +1,54 @@
+package scrobbler
+
+import (
+	"database/sql"
+	"fmt"
+	"os"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+func initdb(dbpath string) error {
+	if _, err := os.Stat(dbpath); err == nil {
+		return fmt.Errorf("%s already exists", dbpath)
+	}
+
+	db, err := sql.Open("sqlite3", dbpath)
+	if err != nil {
+		return err
+	}
+	defer db.Close()
+
+	sqlStmt := `create table records (id text primary key,
+				title text,
+				artist text,
+                album text,
+                duration int,
+                time timestamp
+			);`
+
+	_, err = db.Exec(sqlStmt)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func opendatabase(dbpath string) (*sql.DB, error) {
+	var err error
+	_, err = os.Stat(dbpath)
+
+	if err != nil {
+		if err := initdb(dbpath); err != nil {
+			return nil, err
+		}
+	}
+
+	db, err := sql.Open("sqlite3", dbpath)
+	if err != nil {
+		return nil, fmt.Errorf("unable to open database: %s", err)
+	}
+
+	return db, nil
+}