From 81522cbae16c6aadb1b789f3f875dc50f10cc005 Mon Sep 17 00:00:00 2001
From: Thibaut Horel <thibaut.horel@gmail.com>
Date: Sun, 4 Jun 2017 19:30:25 -0400
Subject: Clean up

---
 schema.sql | 48 ++++++++++++++++++++++++++----------------------
 1 file changed, 26 insertions(+), 22 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index fb13dc7..475c46d 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,37 +1,41 @@
+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 (
-	lfm_name text,
-    key text PRIMARY KEY,
+    session_key text PRIMARY KEY,
+	lfm_name text REFERENCES users(lfm_name),
     client text,
+	client_version text,
     protocol text,
 	created timestamptz DEFAULT current_timestamp
 );
-
 CREATE TABLE IF NOT EXISTS scrobbles (
-	artist      text,
+	artist text,
 	albumartist text,
-	trackname   text,
-	album       text,
+	trackname text,
+	album text,
 	tracknumber int,
-	duration    int,
-	time        timestamptz,
-	chosen      bool,
-	mbid        text,
-	session     text
-);
-
-CREATE TABLE IF NOT EXISTS users (
-	user_id SERIAL PRIMARY KEY,
-	type text,
-	op_id text,
-	name text,
-	email text,
-	lfm_name text,
-	lfm_password text
+	duration int,
+	time timestamptz,
+	chosen bool,
+	mbid text,
+	mbid_computed text,
+	session_key text REFERENCES scrobbling_sessions,
+	lfm_name text REFERENCES users(lfm_name)
 );
 
 CREATE TABLE IF NOT EXISTS user_sessions (
 	id text PRIMARY KEY,
-	user_id integer REFERENCES users
+	user_id integer REFERENCES users,
+	created timestamptz DEFAULT current_timestamp
 );
 
 CREATE TABLE IF NOT EXISTS songs (
-- 
cgit v1.2.3-70-g09d2