Set up test database automatically when testing

diff --git a/db.sql b/db.sql
index 46ddcdb7569e6fa7a84c15f32889d3adb9557af3..a9f03c694aa185c6055b7ab03db1d6c25e64233c 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -1,4 +1,4 @@
-CREATE TABLE users (
+CREATE TABLE IF NOT EXISTS users (

        id         TEXT    PRIMARY KEY,
        passphrase TEXT,   -- NOT NULL,
        admin      BOOLEAN NOT NULL DEFAULT FALSE,
        id         TEXT    PRIMARY KEY,
        passphrase TEXT,   -- NOT NULL,
        admin      BOOLEAN NOT NULL DEFAULT FALSE,


@@ -13,7 +13,7 @@ CREATE TABLE users (
        since      BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
 );
 
        since      BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
 );
 

-CREATE TABLE contests (
+CREATE TABLE IF NOT EXISTS contests (

        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        editorial   TEXT,
        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        editorial   TEXT,


@@ -24,7 +24,7 @@ CREATE TABLE contests (
        CONSTRAINT  positive_duration CHECK (stop > start)
 );
 
        CONSTRAINT  positive_duration CHECK (stop > start)
 );
 

-CREATE TABLE contest_status (
+CREATE TABLE IF NOT EXISTS contest_status (

        contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
        owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
        score   INT  NOT NULL,
        contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
        owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
        score   INT  NOT NULL,


@@ -33,7 +33,7 @@ CREATE TABLE contest_status (
        PRIMARY KEY (owner, contest)
 );
 
        PRIMARY KEY (owner, contest)
 );
 

-CREATE TABLE problems (
+CREATE TABLE IF NOT EXISTS problems (

        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,
        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,


@@ -58,13 +58,13 @@ CREATE TABLE problems (
        versource TEXT
 );
 
        versource TEXT
 );
 

-CREATE TABLE contest_problems (
+CREATE TABLE IF NOT EXISTS contest_problems (

        contest TEXT REFERENCES contests ON DELETE CASCADE,
        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        PRIMARY KEY (contest, problem)
 );
 
        contest TEXT REFERENCES contests ON DELETE CASCADE,
        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        PRIMARY KEY (contest, problem)
 );
 

-CREATE TABLE jobs (
+CREATE TABLE IF NOT EXISTS jobs (

        id          SERIAL  PRIMARY KEY,
        contest     TEXT    REFERENCES contests ON DELETE CASCADE,
        daemon      TEXT,
        id          SERIAL  PRIMARY KEY,
        contest     TEXT    REFERENCES contests ON DELETE CASCADE,
        daemon      TEXT,


@@ -82,7 +82,7 @@ CREATE TABLE jobs (
        owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
 );
 
        owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
 );
 

-CREATE TABLE problem_status (
+CREATE TABLE IF NOT EXISTS problem_status (

        problem TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
        owner   TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
        job     SERIAL  NOT NULL REFERENCES jobs ON DELETE CASCADE,
        problem TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
        owner   TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
        job     SERIAL  NOT NULL REFERENCES jobs ON DELETE CASCADE,


@@ -91,7 +91,7 @@ CREATE TABLE problem_status (
        PRIMARY KEY (owner, problem)
 );
 
        PRIMARY KEY (owner, problem)
 );
 

-CREATE TABLE opens (
+CREATE TABLE IF NOT EXISTS opens (

        contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
        problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,
        owner   TEXT   NOT NULL REFERENCES users ON DELETE CASCADE,
        contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
        problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,
        owner   TEXT   NOT NULL REFERENCES users ON DELETE CASCADE,


@@ -99,7 +99,7 @@ CREATE TABLE opens (
        PRIMARY KEY (contest, problem, owner)
 );
 
        PRIMARY KEY (contest, problem, owner)
 );
 

-CREATE TABLE limits (
+CREATE TABLE IF NOT EXISTS limits (

        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        format  TEXT NOT NULL,
        timeout REAL NOT NULL,
        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        format  TEXT NOT NULL,
        timeout REAL NOT NULL,

diff --git a/t/Gruntmaster-Data.t b/t/Gruntmaster-Data.t
index d7e2d7faedbd54b4fc3db3267b31d7f6bdfec040..54861af544b218237a321ccfdfd9ed3bfb5bd289 100644 (file)
--- a/t/Gruntmaster-Data.t
+++ b/t/Gruntmaster-Data.t
@@ -9,10 +9,15 @@ use Test::More;
 BEGIN {
        eval {
                Gruntmaster::Data::init 'dbi:Pg:dbname=gmtest'; 1;
 BEGIN {
        eval {
                Gruntmaster::Data::init 'dbi:Pg:dbname=gmtest'; 1;

-       } or plan skip_all => 'Cannot connect to test database. Create it by running make_test_db.sh before running this test. '. "Error: $@";
+       } or plan skip_all => 'Cannot connect to test database. Create it by running createdb gmtest before running this test. '. "Error: $@";

        plan tests => 37;
 }
 
        plan tests => 37;
 }
 

+note 'Setting up test database';
+$ENV{PGOPTIONS} = '-c client_min_messages=WARNING';
+system 'psql', 'gmtest', '-qf', 'db.sql';
+system 'psql', 'gmtest', '-qf', 'testdata.sql';
+

 note 'Running update_status';
 update_status;
 
 note 'Running update_status';
 update_status;
 

diff --git a/testdata.sql b/testdata.sql
index 5cf0565094409307ff8550391a01deae565c7edb..bc00077b68fbd1a1682ad5dfa99daae3c6b8dbc1 100644 (file)
--- a/testdata.sql
+++ b/testdata.sql
@@ -1,7 +1,9 @@
-CREATE FUNCTION tm() RETURNS BIGINT IMMUTABLE LANGUAGE SQL AS $$
+CREATE OR REPLACE FUNCTION tm() RETURNS BIGINT IMMUTABLE LANGUAGE SQL AS $$

        SELECT EXTRACT(epoch FROM NOW())::bigint
 $$;
 
        SELECT EXTRACT(epoch FROM NOW())::bigint
 $$;
 

+TRUNCATE users, contests, problems, jobs RESTART IDENTITY CASCADE;
+

 -- USERS
 
 INSERT INTO users (id, admin) VALUES ('MGV', TRUE);
 -- USERS
 
 INSERT INTO users (id, admin) VALUES ('MGV', TRUE);