From ff81576e21f5b89cbf47856c520df3e5e0c9adbe Mon Sep 17 00:00:00 2001 From: Thibaut Horel Date: Sun, 18 Jun 2017 18:18:36 -0400 Subject: Import listens from lastfm --- apiv1.go | 1 + data.go | 100 ++++++++++++++- lfmclient.go | 120 +++++++++++++++--- migrations/20170617130303_import.sql | 19 +++ schema.sql | 228 +++++++++++++++++++++++++++-------- static/style.css | 4 + templates/settings.tmpl | 8 +- web.go | 15 ++- 8 files changed, 420 insertions(+), 75 deletions(-) create mode 100644 migrations/20170617130303_import.sql diff --git a/apiv1.go b/apiv1.go index 4cf8941..c209f53 100644 --- a/apiv1.go +++ b/apiv1.go @@ -219,6 +219,7 @@ func (app *App) GetScrobbleSong(s *Scrobble) { TrackNumber: s.TrackNumber, Duration: s.Duration * 1000, Mbid: s.Mbid, + Image: s.Image, } app.GetSong(&song) s.SongId = song.Id diff --git a/data.go b/data.go index 5f1f0fb..3b4a783 100644 --- a/data.go +++ b/data.go @@ -46,6 +46,14 @@ type Song struct { Image string } +type Import struct { + LfmName string + From time.Time + To time.Time + LastFetch time.Time + Done bool +} + type DataStore interface { PutSession(*Session) error GetSession(key string) (*Session, error) @@ -62,6 +70,12 @@ type DataStore interface { InsertUser(u *User) error SaveUser(u *User) error + GetImport(i *Import) error + SaveImport(i *Import) error + InsertImport(i *Import) error + NewImport(name string) *Import + ImportStats(user int) (time.Time, int, error) + InsertUserSession(s *UserSession) error Api } @@ -179,12 +193,18 @@ func (store *SqlStore) NowPlaying(userId int) *Scrobble { } func (store *SqlStore) GetSongId(s *Song) error { - query := ` - SELECT song_id FROM songs - WHERE artist=$1 AND album=$2 AND name=$3 - ` - row := store.QueryRow(query, s.Artist, s.Album, s.Name) - return row.Scan(&s.Id) + var query string + if s.Mbid != "" { + query = `SELECT song_id FROM songs WHERE mbid=$1` + row := store.QueryRow(query, s.Mbid) + return row.Scan(&s.Id) + } else { + query = ` + SELECT song_id FROM songs + WHERE artist=$1 AND album=$2 AND name=$3` + row := store.QueryRow(query, s.Artist, s.Album, s.Name) + return row.Scan(&s.Id) + } } func (store *SqlStore) InsertSong(s *Song) error { @@ -245,3 +265,71 @@ func (store *SqlStore) InsertUserSession(s *UserSession) error { _, err := store.Exec(query, &s.Id, &s.UserId) return err } + +func (store *SqlStore) GetImport(i *Import) error { + query := ` + SELECT last_fetch + FROM scrobble_import WHERE lfm_name = $1 and "from"=$2 and "to"=$3` + row := store.QueryRow(query, i.LfmName, i.From, i.To) + return row.Scan(&i.LastFetch) +} + +func (store *SqlStore) SaveImport(i *Import) error { + query := `UPDATE scrobble_import SET last_fetch=$1, done=$5 + WHERE "from"=$2 and "to"=$3 and lfm_name=$4` + _, err := store.Exec(query, i.LastFetch, i.From, i.To, i.LfmName, i.Done) + return err +} + +func (store *SqlStore) InsertImport(i *Import) error { + query := ` + INSERT into scrobble_import (lfm_name, "from", "to", last_fetch, done) + VALUES ($1, $2, $3, $4, $5)` + _, err := store.Exec(query, i.LfmName, i.From, i.To, i.LastFetch, i.Done) + return err +} + +func (store *SqlStore) NewImport(name string) *Import { + i := &Import{ + LfmName: name, + To: time.Now(), + } + query := `SELECT max("to") FROM scrobble_import WHERE lfm_name=$1` + row := store.QueryRow(query, i.LfmName) + if row.Scan(&i.From) == nil { + i.From = i.From.Add(time.Second) + } + if err := store.InsertImport(i); err != nil { + log.Println(err) + } + return i +} + +func (store *SqlStore) ImportStats(user int) (time.Time, int, error) { + query := ` + SELECT "last", ct + FROM ( + SELECT max("to") "last", lfm_name + FROM scrobble_import + GROUP BY lfm_name + ) i + JOIN ( + SELECT count(*) ct, max(lfm_name) n + FROM scrobbles + JOIN scrobbling_sessions + ON scrobbles.session_key = scrobbling_sessions.session_key + JOIN users + ON scrobbles.user_id = users.user_id + WHERE users.user_id = $1 + AND client = 'import' + ) s + ON lfm_name = n` + var t time.Time + var count int + row := store.QueryRow(query, user) + if err := row.Scan(&t, &count); err != nil { + return t, count, err + } else { + return t, count, nil + } +} diff --git a/lfmclient.go b/lfmclient.go index 41ecd37..1b39ce9 100644 --- a/lfmclient.go +++ b/lfmclient.go @@ -2,10 +2,11 @@ package main import ( "encoding/json" - "fmt" "io/ioutil" "log" "net/http" + "strconv" + "time" ) type Image struct { @@ -19,6 +20,7 @@ type AlbumInfo struct { Name string `json:"title"` Url string Position `json:"@attr"` + Artist string } type Date struct { @@ -44,11 +46,29 @@ type Position struct { } type TrackInfo struct { - Name string - Mbid string - Duration string - Artist ArtistInfo - Album AlbumInfo + Name string + Mbid string + Duration string + Artist ArtistInfo + Album AlbumInfo + PlayCount string + Listeners string +} + +type RecentTrack struct { + Name string + Mbid string + Url string + Artist struct { + Name string `json:"#text"` + Mbid string + } + Album struct { + Name string `json:"#text"` + Mbid string + } + Images []Image `json:"image"` + Date Date } func (app *App) LfmQuery(payload map[string]string) []byte { @@ -66,8 +86,7 @@ func (app *App) LfmQuery(payload map[string]string) []byte { return body } -func (a AlbumInfo) GetImage(size string) string { - images := a.Images +func GetImage(images []Image, size string) string { for _, image := range images { if image.Size == size { return image.Href @@ -99,13 +118,75 @@ func (app *App) TrackInfo(artist, name string) TrackInfo { return dst["track"] } -func (app *App) RecentTracks(user string) { - r := app.LfmQuery(map[string]string{ +func (app *App) ImportRecentTracks(user *User) { + s := &Session{ + UserId: user.Id, + Client: "import", + ClientVersion: "", + Key: randomToken(16), + Protocol: "1.2.1", + } + app.PutSession(s) + i := app.NewImport(user.LfmName) + payload := map[string]string{ "method": "user.getRecentTracks", - "limit": "10", - "user": user, - }) - fmt.Println(string(r)) + "limit": "200", + "user": user.LfmName, + "to": strconv.Itoa(int(i.To.Unix())), + } + if i.From.IsZero() { + payload["from"] = "0" + } else { + payload["from"] = strconv.Itoa(int(i.From.Unix())) + } + var dst map[string]struct { + Attrs PageAttrs `json:"@attr"` + Tracks []RecentTrack `json:"track"` + } + scrobbles := make([]Scrobble, 0, 200) + var st time.Time + for { + scrobbles = scrobbles[:0] + if !i.LastFetch.IsZero() { + payload["to"] = strconv.Itoa(int(i.LastFetch.Unix())) + } + r := app.LfmQuery(payload) + json.Unmarshal(r, &dst) + tracks := dst["recenttracks"].Tracks + if len(tracks) == 0 { + i.Done = true + break + } + for _, t := range tracks { + ts, _ := strconv.Atoi(t.Date.Uts) + if ts == 0 { + continue + } + st = time.Unix(int64(ts), 0) + scrobble := Scrobble{ + Artist: NewCorrectable(t.Artist.Name), + Album: NewCorrectable(t.Album.Name), + TrackName: NewCorrectable(t.Name), + Mbid: t.Mbid, + Time: st, + UserId: user.Id, + SessionKey: s.Key, + Image: GetImage(t.Images, "medium"), + } + app.GetScrobbleSong(&scrobble) + scrobbles = append(scrobbles, scrobble) + } + if err := app.PutScrobbles(scrobbles); err != nil { + log.Println(err) + } + i.LastFetch = st + if err := app.SaveImport(i); err != nil { + log.Println(err) + } + } + if err := app.SaveImport(i); err != nil { + log.Println(err) + } } func (app *App) LovedTracks(user string) []TrackInfo { @@ -125,10 +206,11 @@ func (app *App) LovedTracks(user string) []TrackInfo { } func (app *App) GetSong(s *Song) { - if s.Album != "" { + if s.Album != "" || s.Mbid != "" { if app.GetSongId(s) != nil { if s.Image == "" { - s.Image = app.AlbumInfo(s.Artist, s.Album).GetImage("medium") + s.Image = GetImage(app.AlbumInfo(s.Artist, s.Album).Images, + "medium") } if err := app.InsertSong(s); err != nil { log.Println(err) @@ -138,7 +220,9 @@ func (app *App) GetSong(s *Song) { t := app.TrackInfo(s.Artist, s.Name) s.Album = t.Album.Name s.Mbid = t.Mbid - s.Image = t.Album.GetImage("medium") - app.InsertSong(s) + s.Image = GetImage(t.Album.Images, "medium") + if app.GetSongId(s) != nil { + app.InsertSong(s) + } } } diff --git a/migrations/20170617130303_import.sql b/migrations/20170617130303_import.sql new file mode 100644 index 0000000..d897366 --- /dev/null +++ b/migrations/20170617130303_import.sql @@ -0,0 +1,19 @@ + +-- +goose Up +-- SQL in section 'Up' is executed when this migration is applied + +CREATE TABLE IF NOT EXISTS scrobble_import ( + lfm_name text, + "from" timestamptz, + "to" timestamptz, + last_fetch timestamptz, + done bool, + PRIMARY KEY (lfm_name, "from", "to") +); + +CREATE INDEX scrobbles_time ON scrobbles(time DESC); + +-- +goose Down +-- SQL section 'Down' is executed when this migration is rolled back +DROP TABLE scrobble_import; +DROP INDEX scrobbles_time; diff --git a/schema.sql b/schema.sql index b65ae6b..24775db 100644 --- a/schema.sql +++ b/schema.sql @@ -1,51 +1,183 @@ -CREATE TABLE IF NOT EXISTS users ( - user_id SERIAL PRIMARY KEY, - type text, - op_id text, - name text, - email text, - lfm_name text UNIQUE, - lfm_password text, - created timestamptz DEFAULT current_timestamp -); - -CREATE TABLE IF NOT EXISTS scrobbling_sessions ( - session_key text PRIMARY KEY, - user_id int REFERENCES users, + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; +SET row_security = off; + +SET search_path = public, pg_catalog; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +CREATE TABLE goose_db_version ( + id integer NOT NULL, + version_id bigint NOT NULL, + is_applied boolean NOT NULL, + tstamp timestamp without time zone DEFAULT now() +); + +CREATE SEQUENCE goose_db_version_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE goose_db_version_id_seq OWNED BY goose_db_version.id; + +CREATE TABLE now_playing ( + artist text, + album_artist text, + track_name text, + album text, + track_number integer, + duration integer, + received timestamp with time zone DEFAULT now(), + mbid text, + song_id integer, + session_key text, + user_id integer +); + +CREATE TABLE scrobble_import ( + lfm_name text NOT NULL, + "from" timestamp with time zone NOT NULL, + "to" timestamp with time zone NOT NULL, + last_fetch timestamp with time zone, + done boolean +); + +CREATE TABLE scrobbles ( + artist text, + album_artist text, + track_name text, + album text, + track_number integer, + duration integer, + "time" timestamp with time zone, + chosen boolean, + mbid text, + song_id integer, + session_key text, + user_id integer +); + +CREATE TABLE scrobbling_sessions ( + session_key text NOT NULL, + user_id integer, client text, - client_version text, + client_version text, 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, - album_artist text, - track_name text, - album text, - track_number int, - duration int, - time timestamptz, - chosen bool, - mbid text, - song_id int REFERENCES songs, - session_key text REFERENCES scrobbling_sessions, - user_id int REFERENCES users -); - -CREATE TABLE IF NOT EXISTS user_sessions ( - id text PRIMARY KEY, - user_id integer REFERENCES users, - created timestamptz DEFAULT current_timestamp + created timestamp with time zone DEFAULT now() +); + +CREATE TABLE songs ( + song_id integer NOT NULL, + artist text, + album text, + name text, + track_number text, + duration text, + mbid text, + image text +); + +CREATE SEQUENCE songs_song_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE songs_song_id_seq OWNED BY songs.song_id; + +CREATE TABLE user_sessions ( + id text NOT NULL, + user_id integer, + created timestamp with time zone DEFAULT now() ); + +CREATE TABLE users ( + user_id integer NOT NULL, + type text, + op_id text, + name text, + email text, + lfm_name text, + lfm_password text, + created timestamp with time zone DEFAULT now() +); + +CREATE SEQUENCE users_user_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id; + +ALTER TABLE ONLY goose_db_version ALTER COLUMN id SET DEFAULT nextval('goose_db_version_id_seq'::regclass); + +ALTER TABLE ONLY songs ALTER COLUMN song_id SET DEFAULT nextval('songs_song_id_seq'::regclass); + +ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass); + +ALTER TABLE ONLY goose_db_version + ADD CONSTRAINT goose_db_version_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY now_playing + ADD CONSTRAINT now_playing_user_id_key UNIQUE (user_id); + +ALTER TABLE ONLY users + ADD CONSTRAINT op UNIQUE (type, op_id); + +ALTER TABLE ONLY scrobble_import + ADD CONSTRAINT scrobble_import_pkey PRIMARY KEY (lfm_name, "from", "to"); + +ALTER TABLE ONLY scrobbling_sessions + ADD CONSTRAINT scrobbling_sessions_pkey PRIMARY KEY (session_key); + +ALTER TABLE ONLY songs + ADD CONSTRAINT songs_pkey PRIMARY KEY (song_id); + +ALTER TABLE ONLY user_sessions + ADD CONSTRAINT user_sessions_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY users + ADD CONSTRAINT users_lfm_name_key UNIQUE (lfm_name); + +ALTER TABLE ONLY users + ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); + +CREATE INDEX scrobbles_time ON scrobbles USING btree ("time" DESC); + +ALTER TABLE ONLY now_playing + ADD CONSTRAINT now_playing_session_key_fkey FOREIGN KEY (session_key) REFERENCES scrobbling_sessions(session_key); + +ALTER TABLE ONLY now_playing + ADD CONSTRAINT now_playing_song_id_fkey FOREIGN KEY (song_id) REFERENCES songs(song_id); + +ALTER TABLE ONLY now_playing + ADD CONSTRAINT now_playing_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id); + +ALTER TABLE ONLY scrobbles + ADD CONSTRAINT scrobbles_session_key_fkey FOREIGN KEY (session_key) REFERENCES scrobbling_sessions(session_key); + +ALTER TABLE ONLY scrobbles + ADD CONSTRAINT scrobbles_song_id_fkey FOREIGN KEY (song_id) REFERENCES songs(song_id); + +ALTER TABLE ONLY scrobbles + ADD CONSTRAINT scrobbles_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id); + +ALTER TABLE ONLY scrobbling_sessions + ADD CONSTRAINT scrobbling_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id); + +ALTER TABLE ONLY user_sessions + ADD CONSTRAINT user_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id); + diff --git a/static/style.css b/static/style.css index 4072988..39fc0d5 100644 --- a/static/style.css +++ b/static/style.css @@ -175,6 +175,10 @@ input:focus+label { padding: 0 16px; } +form p { + padding: 0 16px; +} + .form-element { display: flex; flex-direction: column-reverse; diff --git a/templates/settings.tmpl b/templates/settings.tmpl index 9c68b16..da3dabb 100644 --- a/templates/settings.tmpl +++ b/templates/settings.tmpl @@ -29,9 +29,15 @@ +

+ {{if not .LastImport.IsZero}} + {{.ImportCount}} listens imported from Last.fm (last import on {{.LastImport.Format "Mon, Jan 2 15:04"}}). + {{end}} + +

- +
{{template "footer"}} diff --git a/web.go b/web.go index 371ae9f..73871b3 100644 --- a/web.go +++ b/web.go @@ -111,7 +111,7 @@ func (app *App) settings(w http.ResponseWriter, r *http.Request) { return } - if r.Method == "POST" { + if r.Method == "POST" && r.FormValue("save") != "" { u := &User{ Id: se.UserId, Name: r.FormValue("name"), @@ -128,14 +128,25 @@ func (app *App) settings(w http.ResponseWriter, r *http.Request) { app.SetCookie(w, "session", se, 86400*30) } + if r.Method == "POST" && r.FormValue("import") != "" { + u := &User{ + Id: se.UserId, + } + app.GetUser(u) + go app.ImportRecentTracks(u) + } + user := &User{Id: se.UserId} if err := app.GetUser(user); err != nil { log.Println(err) } + li, ct, err := app.ImportStats(user.Id) err = app.Template.ExecuteTemplate(w, "settings.tmpl", struct { Session *UserSession *User - }{Session: se, User: user}) + LastImport time.Time + ImportCount int + }{Session: se, User: user, LastImport: li, ImportCount: ct}) if err != nil { log.Println(err) } -- cgit v1.2.3-70-g09d2