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 --- schema.sql | 228 ++++++++++++++++++++++++++++++++++++++++++++++++------------- 1 file changed, 180 insertions(+), 48 deletions(-) (limited to 'schema.sql') 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); + -- cgit v1.2.3-70-g09d2