Get additional song metadata from last.fm

4 files changed, 143 insertions, 44 deletions

diff --git a/apiv1.go b/apiv1.go
index 592e7de..a23780b 100644
--- a/apiv1.go
+++ b/apiv1.go
@@ -158,10 +158,10 @@ func parseScrobbles(values url.Values, session *Session) ([]Scrobble, int) {
 		if part, ok := parts[i]; ok {
 			c++
 			if scrobble, err := parsePart(part); err != nil {
-				fmt.Printf("%v\n", err)
+				log.Println(err)
 				ignored++
 			} else {
-				scrobble.Session = session.Key
+				scrobble.SessionKey = session.Key
 				scrobble.UserId = session.UserId
 				scrobbles = append(scrobbles, scrobble)
 			}
@@ -180,15 +180,8 @@ func (app *App) scrobbleHandler(w http.ResponseWriter, r *http.Request) {
 	scrobbles, _ := parseScrobbles(r.Form, session)
 
 	for i, s := range scrobbles {
-		t := app.TrackInfo(s.TrackName.Name, s.Artist.Name)
-		s.MbidComp = t.Mbid
+		app.GetSong(&s)
 		scrobbles[i] = s
-		_, err = app.DB.Exec("INSERT INTO songs VALUES ($1, $2) "+
-			"ON CONFLICT (mbid) DO UPDATE SET mbid=$1, image=$2",
-			t.Mbid, t.GetImage("medium"))
-		if err != nil {
-			log.Println(err)
-		}
 	}
 
 	err = app.PutScrobbles(scrobbles)
diff --git a/data.go b/data.go
index 373dddb..9e5f5d5 100644
--- a/data.go
+++ b/data.go
@@ -19,8 +19,8 @@ type Scrobble struct {
 	Time        time.Time   `xml:"timestamp" json:"timestamp,string"`
 	Chosen      bool        `xml:"-" json:"-"`
 	Mbid        string      `xml:"-" json:"-"`
-	MbidComp    string
-	Session     string `xml:"-" json:"-"`
+	SongId      int
+	SessionKey  string `xml:"-" json:"-"`
 	UserId      int
 	Image       string
 }
@@ -43,6 +43,8 @@ type DataStore interface {
 	GetUser(lfmName string) (int, string, error)
 	PutScrobbles([]Scrobble) error
 	RecentScrobbles(userId int) []*Scrobble
+	GetSongId(artist, album, name string) (int, error)
+	InsertSong(s *Scrobble) (int, error)
 	Api
 }
 
@@ -85,8 +87,8 @@ func (store *SqlStore) PutScrobbles(scrobbles []Scrobble) error {
 	}
 	query := `
 	INSERT INTO scrobbles
-	(artist, albumartist, trackname, album, tracknumber, duration, time,
-	chosen, mbid, mbid_computed, session_key, user_id)
+	(artist, album_artist, track_name, album, track_number, duration, time,
+	chosen, mbid, song_id, session_key, user_id)
 	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`
 	st, err := tx.Prepare(query)
 	if err != nil {
@@ -95,8 +97,8 @@ func (store *SqlStore) PutScrobbles(scrobbles []Scrobble) error {
 
 	for _, s := range scrobbles {
 		_, err = st.Exec(s.Artist, s.AlbumArtist, s.TrackName, s.Album,
-			s.TrackNumber, s.Duration, s.Time, s.Chosen, s.Mbid, s.MbidComp,
-			s.Session, s.UserId)
+			s.TrackNumber, s.Duration, s.Time, s.Chosen, s.Mbid, s.SongId,
+			s.SessionKey, s.UserId)
 		if err != nil {
 			tx.Rollback()
 			return err
@@ -108,13 +110,14 @@ func (store *SqlStore) PutScrobbles(scrobbles []Scrobble) error {
 func (store *SqlStore) RecentScrobbles(userId int) []*Scrobble {
 	scrobbles := make([]*Scrobble, 0, 10)
 	query := `
-	SELECT artist, album, trackname, time, image
-	FROM scrobbles 
-	LEFT JOIN songs ON COALESCE(scrobbles.mbid, scrobbles.mbid_computed) = songs.mbid 
+	SELECT s.artist, s.album, s.track_name, s.time, songs.image
+	FROM scrobbles s
+	LEFT JOIN songs ON songs.song_id = s.song_id
 	WHERE user_id=$1 ORDER BY time DESC LIMIT 10`
 	rows, err := store.Query(query, userId)
 	if err != nil {
 		log.Println(err)
+		return scrobbles
 	}
 	defer rows.Close()
 
@@ -126,3 +129,27 @@ func (store *SqlStore) RecentScrobbles(userId int) []*Scrobble {
 	}
 	return scrobbles
 }
+
+func (store *SqlStore) GetSongId(artist, album, song string) (int, error) {
+	query := `
+	SELECT song_id FROM songs
+	WHERE artist=$1 AND album=$2 AND name=$3
+	`
+	var id int
+	row := store.QueryRow(query, artist, album, song)
+	err := row.Scan(&id)
+	return id, err
+
+}
+
+func (store *SqlStore) InsertSong(s *Scrobble) (int, error) {
+	query := `
+	INSERT INTO songs (artist, album, name, track_number, duration, mbid, image)
+	VALUES ($1, $2, $3, $4, $5, $6, $7)
+	RETURNING song_id`
+	var id int
+	row := store.QueryRow(query, s.Artist, s.Album, s.TrackName, s.TrackNumber,
+		s.Duration*1000, s.Mbid, s.Image)
+	err := row.Scan(&id)
+	return id, err
+}
diff --git a/lfmclient.go b/lfmclient.go
index 244ab8c..9c500b7 100644
--- a/lfmclient.go
+++ b/lfmclient.go
@@ -3,7 +3,9 @@ package main
 import (
 	"encoding/json"
 	"io/ioutil"
+	"log"
 	"net/http"
+	"strconv"
 )
 
 type AlbumImage struct {
@@ -11,12 +13,8 @@ type AlbumImage struct {
 	Href string `json:"#text"`
 }
 
-type TrackAlbum struct {
-	Images []AlbumImage `json:"image"`
-}
-
-func (t TrackGetInfo) GetImage(size string) string {
-	images := t.Album.Images
+func (a AlbumInfo) GetImage(size string) string {
+	images := a.Images
 	for _, image := range images {
 		if image.Size == size {
 			return image.Href
@@ -25,23 +23,97 @@ func (t TrackGetInfo) GetImage(size string) string {
 	return "https://lastfm-img2.akamaized.net/i/u/64s/4128a6eb29f94943c9d206c08e625904.png"
 }
 
-type TrackGetInfo struct {
-	Mbid  string     `json:"mbid"`
-	Album TrackAlbum `jon:"album"`
+type AlbumInfo struct {
+	Images []AlbumImage `json:"image"`
+	Mbid   string       `json:"mbid"`
+	Name   string       `json:"title"`
+}
+
+type ArtistInfo struct {
+	Name string `json:"name"`
 }
 
-func (app *App) TrackInfo(track string, artist string) TrackGetInfo {
+type Position struct {
+	TrackNumber string `json:"position"`
+}
+
+type TrackInfo struct {
+	Name     string     `json:"name"`
+	Mbid     string     `json:"mbid"`
+	Duration string     `json:"duration"`
+	Artist   ArtistInfo `json:"artist"`
+	Album    AlbumInfo  `json:"album"`
+	Position `json:"@attr"`
+}
+
+func (app *App) LfmQuery(payload map[string]string) []byte {
 	r, _ := http.NewRequest("GET", "http://ws.audioscrobbler.com/2.0/", nil)
 	values := r.URL.Query()
-	values.Add("method", "track.getInfo")
 	values.Add("api_key", app.Config.Lfm.ApiKey)
-	values.Add("artist", artist)
-	values.Add("track", track)
 	values.Add("format", "json")
+	for key, value := range payload {
+		values.Add(key, value)
+	}
 	r.URL.RawQuery = values.Encode()
 	resp, _ := http.DefaultClient.Do(r)
 	body, _ := ioutil.ReadAll(resp.Body)
-	var dst map[string]TrackGetInfo
-	json.Unmarshal(body, &dst)
-	return dst["track"]
+	return body
+}
+
+func (app *App) AlbumImage(artist, album string) string {
+	r := app.LfmQuery(map[string]string{
+		"method": "album.getInfo",
+		"artist": artist,
+		"album":  album,
+	})
+	var dst map[string]AlbumInfo
+	json.Unmarshal(r, &dst)
+	return dst["album"].GetImage("medium")
+}
+
+func (app *App) TrackInfo(artist, name string) *Scrobble {
+	r := app.LfmQuery(map[string]string{
+		"method":      "track.getInfo",
+		"artist":      artist,
+		"track":       name,
+		"autocorrect": "1",
+	})
+	var dst map[string]TrackInfo
+	json.Unmarshal(r, &dst)
+	track := dst["track"]
+	duration, _ := strconv.Atoi(track.Duration)
+	position, _ := strconv.Atoi(track.TrackNumber)
+	s := &Scrobble{
+		Artist:      NewCorrectable(track.Artist.Name),
+		Album:       NewCorrectable(track.Album.Name),
+		TrackName:   NewCorrectable(track.Name),
+		Duration:    duration * 1000,
+		TrackNumber: position,
+		Mbid:        track.Mbid,
+		Image:       track.Album.GetImage("medium"),
+	}
+	return s
+
+}
+
+func (app *App) GetSong(s *Scrobble) {
+	var err error
+	if s.Album.Name != "" {
+		id, _ := app.GetSongId(s.Artist.Name, s.Album.Name, s.TrackName.Name)
+		if id == 0 {
+			s.Image = app.AlbumImage(s.Artist.Name, s.Album.Name)
+			id, err = app.InsertSong(s)
+			if err != nil {
+				log.Println(err)
+			}
+		}
+		s.SongId = id
+	} else {
+		s2 := app.TrackInfo(s.Artist.Name, s.TrackName.Name)
+		id, err := app.InsertSong(s2)
+		if err != nil {
+			log.Println(err)
+		}
+		s.SongId = id
+	}
 }
diff --git a/schema.sql b/schema.sql
index f875137..b65ae6b 100644
--- a/schema.sql
+++ b/schema.sql
@@ -17,17 +17,29 @@ CREATE TABLE IF NOT EXISTS scrobbling_sessions (
     protocol text,
 	created timestamptz DEFAULT current_timestamp
 );
+
+CREATE TABLE IF NOT EXISTS songs (
+	song_id SERIAL PRIMARY KEY,
+	artist text,
+	album text,
+	name text,
+	track_number text,
+	duration text,
+	mbid text,
+	image text
+);
+
 CREATE TABLE IF NOT EXISTS scrobbles (
 	artist text,
-	albumartist text,
-	trackname text,
+	album_artist text,
+	track_name text,
 	album text,
-	tracknumber int,
+	track_number int,
 	duration int,
 	time timestamptz,
 	chosen bool,
 	mbid text,
-	mbid_computed text,
+	song_id int REFERENCES songs,
 	session_key text REFERENCES scrobbling_sessions,
 	user_id int REFERENCES users
 );
@@ -37,8 +49,3 @@ CREATE TABLE IF NOT EXISTS user_sessions (
 	user_id integer REFERENCES users,
 	created timestamptz DEFAULT current_timestamp
 );
-
-CREATE TABLE IF NOT EXISTS songs (
-	mbid text PRIMARY KEY,
-	image text
-);