Import listens from lastfm

1 files changed, 172 insertions, 40 deletions

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
+
+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 IF NOT EXISTS scrobbling_sessions (
-    session_key text PRIMARY KEY,
-	user_id int REFERENCES users,
+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
+    created timestamp with time zone DEFAULT now()
 );
 
-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 songs (
+    song_id integer NOT NULL,
+    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 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 IF NOT EXISTS user_sessions (
-	id text PRIMARY KEY,
-	user_id integer REFERENCES users,
-	created timestamptz DEFAULT current_timestamp
+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);
+