Multiple bugfixes to authorization system & sql tweaks

This commit is tested to work.

2 files changed, 13 insertions, 3 deletions

diff --git a/util/dump.sql b/util/dump.sql
index 22e80099..e69947f6 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -161,6 +161,14 @@ CREATE TABLE screenshots (
   height smallint NOT NULL DEFAULT 0
 );
 
+-- sessions
+CREATE TABLE sessions (
+    uid integer NOT NULL,
+    token character(40) NOT NULL,
+    expiration bigint DEFAULT 0 NOT NULL,
+    PRIMARY KEY (uid, token)
+);
+
 -- stats_cache
 CREATE TABLE stats_cache (
   section varchar(25) NOT NULL PRIMARY KEY,
@@ -256,7 +264,8 @@ CREATE TABLE users (
   skin varchar(128) NOT NULL DEFAULT '',
   customcss text NOT NULL DEFAULT '',
   ip inet NOT NULL DEFAULT '0.0.0.0',
-  c_tags integer NOT NULL DEFAULT 0
+  c_tags integer NOT NULL DEFAULT 0,
+  salt character(9) NOT NULL DEFAULT ''
 );
 
 -- vn
@@ -376,6 +385,7 @@ ALTER TABLE releases_vn        ADD FOREIGN KEY (rid)       REFERENCES releases_r
 ALTER TABLE releases_vn        ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE sessions           ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags               ADD FOREIGN KEY (addedby)   REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags_aliases       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags_parents       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
index 444d81c9..e631039e 100644
--- a/util/updates/update_2.6.sql
+++ b/util/updates/update_2.6.sql
@@ -2,7 +2,7 @@
 -- Create table for session data storage
 
 CREATE TABLE sessions (
-    uid integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    uid integer NOT NULL REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED;
     token character(40) NOT NULL,
     expiration bigint DEFAULT 0 NOT NULL,
     PRIMARY KEY (uid, token)
@@ -10,5 +10,5 @@ CREATE TABLE sessions (
 
 -- Add column to users for salt storage
 
-ALTER TABLE users ADD COLUMN salt character(9) NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN salt character(9) NOT NULL DEFAULT '';