Defined database tables for ranking feature.

4 files changed, 132 insertions, 0 deletions

diff --git a/database/scripts/README b/database/scripts/README
index 5d03bfa..7b193cc 100644
--- a/database/scripts/README
+++ b/database/scripts/README
@@ -24,4 +24,13 @@ host    bm          bmuser      172.24.90.79/32       trust # barbarella (jaspli
 --- END --------------------------------------------------------------
 
 
+
 STEP 2: Run the reset.py script to initialize an empty database.
+
+
+STEP 3: Run the reset-ranking.py script to initialize an empty 'ranking'
+        table.
+
+        NOTE: For now this is kept as a separate script in order not
+        to affect other tables. When the ranking feature has matured,
+        everything should be merged into the reset.py script.
diff --git a/database/scripts/privileges-ranking.sql b/database/scripts/privileges-ranking.sql
new file mode 100644
index 0000000..9b8b438
--- /dev/null
+++ b/database/scripts/privileges-ranking.sql
@@ -0,0 +1,7 @@
+-- Define privileges for the 'bmuser':
+
+GRANT SELECT ON rankingStat TO bmuser;
+
+GRANT SELECT ON ranking TO bmuser;
+GRANT INSERT ON ranking TO bmuser;
+GRANT UPDATE ON ranking_id_seq TO bmuser;
diff --git a/database/scripts/reset-ranking.py b/database/scripts/reset-ranking.py
new file mode 100755
index 0000000..5f7afde
--- /dev/null
+++ b/database/scripts/reset-ranking.py
@@ -0,0 +1,54 @@
+#!/usr/bin/env python
+
+import sys
+from subprocess import Popen, PIPE
+import string
+
+"""
+NOTE: This script should be run as the 'postgres' role.
+
+NOTE: Eventually, once the ranking feature is mature, this script should be removed
+      and the effect it has should be implemented in reset.py instead.
+"""
+
+# --- Global functions -------------------------------------------------
+
+def runCommand(cmd, exitOnFail = True):
+    print "running command >%s< ..." % string.join(cmd, " "),
+    p = Popen(cmd, stdout = PIPE, stderr = PIPE)
+    stdout, stderr = p.communicate()
+    if (p.returncode != 0):
+        print "FAIL:"
+        print "  return code:", p.returncode
+        print "  stdout: >%s<" % stdout.strip()
+        print "  stderr: >%s<" % stderr.strip()
+        if (exitOnFail):
+            print "exiting ..."
+            sys.exit(1)
+        else:
+            print "CONTINUING ..."
+    else:
+        print "PASS"
+
+
+# --- Main program -------------------------------------------------
+
+# Ensure the intention of running this script.
+reply = raw_input(
+    "WARNING: The 'ranking' and 'rankingStat' tables will be deleted!"
+    " Continue [y/N]?").strip().lower()
+if (reply != "y" and reply != "yes"):
+    sys.exit(1)
+
+database = "bm"
+user = "bmuser"
+owner = "postgres"
+tabledefs = "tabledefs-ranking.sql"
+privileges = "privileges-ranking.sql"
+
+# Load table definitions (deleting existing tables).
+runCommand(["psql", "-U", owner, "-d", database, "-f", tabledefs])
+
+# Define user privileges (if necessary).
+# (Note: remote client access is defined separately in pg_hba.conf)
+runCommand(["psql", "-U", owner, "-d", database, "-f", privileges])
diff --git a/database/scripts/tabledefs-ranking.sql b/database/scripts/tabledefs-ranking.sql
new file mode 100644
index 0000000..0fee11e
--- /dev/null
+++ b/database/scripts/tabledefs-ranking.sql
@@ -0,0 +1,62 @@
+-- Table to keep available ranking statistic types:
+DROP TABLE IF EXISTS rankingStat;
+CREATE TABLE rankingStat (
+    id BIGSERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
+ALTER TABLE rankingStat OWNER TO postgres;
+-- Pre-populate:
+INSERT INTO rankingStat (name) VALUES
+    ('LSD'), -- Last snapshot distance
+    ('NI'), -- Number of invalid observations
+    ('NZ'), -- Number of non-positive observations
+    ('NC'), -- Number of changes
+    ('MDRSE'), -- Median of relative standard errors
+    ('RSEMD'), -- Relative standard error of median observations
+    ('LCSSR'), -- Last chance stability score (last change regressing)
+    ('LCSSI'), -- Last change stability score (last change improving)
+    ('LCSS1R'), -- Last change stability score not considering history after
+                -- last change (last change regressing)
+    ('LCSS1I'); -- Last change stability score not considering history after
+                -- last change (last change improving)
+
+
+-- Table to store rankings:
+DROP TABLE IF EXISTS ranking;
+CREATE TABLE ranking
+(
+    id BIGSERIAL PRIMARY KEY,
+
+    -- Environment:
+    hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE,
+    platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE,
+
+    -- Revision:
+    branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE,
+    sha1Id BIGINT NOT NULL REFERENCES sha1 ON DELETE CASCADE,
+
+    -- Benchmark:
+    benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
+
+    -- Metric:
+    metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE,
+
+    -- Ranking statistic type:
+    statId BIGINT NOT NULL REFERENCES rankingStat ON DELETE CASCADE,
+
+    -- Ranking statistic value:
+    value REAL NOT NULL,
+
+    -- Ranking position:
+    pos INTEGER NOT NULL
+
+) WITH (OIDS=FALSE);
+ALTER TABLE ranking OWNER TO postgres;
+
+-- Indexes:
+CREATE INDEX ranking_host_idx ON ranking (hostId);
+CREATE INDEX ranking_platform_idx ON ranking (platformId);
+CREATE INDEX ranking_branch_idx ON ranking (branchId);
+CREATE INDEX ranking_sha1_idx ON ranking (sha1Id);
+CREATE INDEX ranking_benchmark_idx ON ranking (benchmarkId);
+CREATE INDEX ranking_metric_idx ON ranking (metricId);
+CREATE INDEX ranking_stat_idx ON ranking (statId);
+CREATE INDEX ranking_pos_idx ON ranking (pos);