use in-memory sqlite database for local backendv0.0.6-alphaweb-overhaul

3 files changed, 83 insertions, 23 deletions

diff --git a/go.mod b/go.mod
index a4c1cf7..d8b6dc3 100644
--- a/go.mod
+++ b/go.mod
@@ -13,6 +13,7 @@ require (
 	github.com/badfortrains/mdns v0.0.0-20160325001438-447166384f51 // indirect
 	github.com/golang/protobuf v1.5.2 // indirect
 	github.com/klauspost/compress v1.15.13 // indirect
+	github.com/mattn/go-sqlite3 v1.14.16 // indirect
 	github.com/miekg/dns v1.1.50 // indirect
 	github.com/stretchr/testify v1.5.1 // indirect
 	golang.org/x/crypto v0.4.0 // indirect
diff --git a/go.sum b/go.sum
index 2d42bac..2a4aae5 100644
--- a/go.sum
+++ b/go.sum
@@ -46,6 +46,8 @@ github.com/librespot-org/librespot-golang v0.0.0-20220325184705-31669e5a889f h1:
 github.com/librespot-org/librespot-golang v0.0.0-20220325184705-31669e5a889f/go.mod h1:LeHPXRci2Bg6RBmw8BDEFH5nfb8oGU6mll+qTH8UIzw=
 github.com/mattn/go-isatty v0.0.12 h1:wuysRhFDzyxgEmMf5xjvJ2M9dZoWAXNNr5LSBS7uHXY=
 github.com/mattn/go-isatty v0.0.12/go.mod h1:cbi8OIDigv2wuxKPP5vlRcQ1OAZbq2CE4Kysco4FUpU=
+github.com/mattn/go-sqlite3 v1.14.16 h1:yOQRA0RpS5PFz/oikGwBEqvAWhWg5ufRz4ETLjwpU1Y=
+github.com/mattn/go-sqlite3 v1.14.16/go.mod h1:2eHXhiwb8IkHr+BDWZGa96P6+rkvnG63S2DGjv9HUNg=
 github.com/miekg/dns v1.1.8/go.mod h1:W1PPwlIAgtquWBMBEV9nkV9Cazfe8ScdGz/Lj7v3Nrg=
 github.com/miekg/dns v1.1.50 h1:DQUfb9uc6smULcREF09Uc+/Gd46YWqJd5DbpPE9xkcA=
 github.com/miekg/dns v1.1.50/go.mod h1:e3IlAVfNqAllflbibAZEWOXOQ+Ynzk/dDozDxY7XnME=
diff --git a/localBackend.go b/localBackend.go
index 2dbd4fd..1e50817 100644
--- a/localBackend.go
+++ b/localBackend.go
@@ -1,44 +1,80 @@
 package main
 
 import (
+	"database/sql"
+	"fmt"
 	"os"
 	"path"
 	"path/filepath"
 	"strings"
-	"sync"
 
 	"github.com/dhowden/tag"
+	_ "github.com/mattn/go-sqlite3"
 	"muhq.space/go/wrms/llog"
 )
 
+const DB_URL = "file:songs?mode=memory&cache=shared"
+
 type LocalBackend struct {
 	musicDir string
-	lock     sync.RWMutex
-	songs    map[string]*Song
+	db       *sql.DB
 }
 
 func NewLocalBackend(musicDir string) *LocalBackend {
-	b := LocalBackend{musicDir, sync.RWMutex{}, map[string]*Song{}}
+	b := LocalBackend{musicDir: musicDir}
+
+	var err error
+	b.db, err = sql.Open("sqlite3", DB_URL)
+	if err != nil {
+		llog.Fatal("Opening in-memory db failed: %q", err)
+	}
+
+	sqlStmt := `
+	CREATE TABLE songs (
+		Uri text NOT NULL PRIMARY KEY,
+		Title text,
+		Artist text
+	);
+	`
+	_, err = b.db.Exec(sqlStmt)
+	if err != nil {
+		llog.Fatal("%q: %s", err, sqlStmt)
+	}
 
 	go b.findSongs()
 	return &b
 }
 
-func (b *LocalBackend) insert(song *Song) {
-	b.lock.Lock()
-	defer b.lock.Unlock()
-	b.songs[song.Uri] = song
+func (b *LocalBackend) insert(songs []*Song) {
+	llog.Debug("Inserting %d songs into local DB", len(songs))
+	tx, err := b.db.Begin()
+	if err != nil {
+		llog.Fatal("Starting insert transaction failed: %q", err)
+	}
+
+	stmt, err := tx.Prepare("INSERT INTO songs(Uri, Title, Artist) VALUES(?, ?, ?)")
+	if err != nil {
+		llog.Fatal("Preparing inser statement failed: %q", err)
+	}
+	defer stmt.Close()
+
+	for _, song := range songs {
+		_, err = stmt.Exec(song.Uri, song.Title, song.Artist)
+		if err != nil {
+			llog.Fatal("Executing insert statement failed: %q", err)
+		}
+	}
+
+	err = tx.Commit()
+	if err != nil {
+		llog.Fatal("Commiting insert transaction failed: %q", err)
+	}
 }
 
 func (b *LocalBackend) findSongs() {
 	llog.Debug("Starting song search under: %s", b.musicDir)
 
-	// Insert some Songs
-	song := NewSong("No!", "Bukahara", "local", "Bukahara/Phantasma/01-No!.ogg")
-
-	for _, s := range []*Song{&song} {
-		b.insert(s)
-	}
+	var songs []*Song
 
 	err := filepath.Walk(b.musicDir, func(p string, finfo os.FileInfo, err error) error {
 		if err != nil {
@@ -72,32 +108,53 @@ func (b *LocalBackend) findSongs() {
 		}
 
 		s := NewSong(m.Title(), m.Artist(), "local", p)
-		b.insert(&s)
+		songs = append(songs, &s)
 		return nil
 	})
 
 	if err != nil {
 		llog.Error("error walking the path %q: %v", b.musicDir, err)
 	}
+
+	b.insert(songs)
 }
 
 func (b *LocalBackend) Play(song *Song, player *Player) {
 	player.PlayUri("file://" + song.Uri)
 }
 
-func (b *LocalBackend) Search(keyword string) []Song {
+func (b *LocalBackend) Search(keyword string) (results []Song) {
 	pattern := strings.ToLower(keyword)
-	results := []Song{}
 
-	b.lock.RLock()
-	defer b.lock.RUnlock()
+	query := fmt.Sprintf("SELECT * FROM songs WHERE Title LIKE '%%%s%%' OR Artist LIKE '%%%s%%'",
+		pattern, pattern)
+	llog.Debug("Searching in local DB using: %q", query)
+	rows, err := b.db.Query(query)
+	if err != nil {
+		llog.Error("Building search query %q failed: %q", query, err)
+		return
+	}
+	defer rows.Close()
 
-	for _, s := range b.songs {
-		if strings.Contains(strings.ToLower(s.Title), pattern) ||
-			strings.Contains(strings.ToLower(s.Artist), pattern) {
-			results = append(results, *s)
+	for rows.Next() {
+		var uri string
+		var title string
+		var artist string
+
+		err = rows.Scan(&uri, &title, &artist)
+		if err != nil {
+			llog.Warning("Scanning query result failed: %q", err)
 		}
+
+		results = append(results, NewSong(title, artist, "local", uri))
 	}
 
+	err = rows.Err()
+	if err != nil {
+		llog.Error("Iterator returned error: %q", err)
+	}
+
+	llog.Debug("Local search returned %d results", len(results))
+
 	return results
 }