

CREATE TABLE game
  (g_id                            INTEGER     NOT NULL,
   g_title                         VARCHAR(50) NOT NULL,
   g_max_nr_of_levels              INTEGER     NOT NULL,
   g_max_nr_of_enduserscore_entries INTEGER     NOT NULL,

   CONSTRAINT pk_g_title
     PRIMARY KEY (g_id),

   CONSTRAINT unique_g_title
     UNIQUE (g_title)
  );
                
CREATE TABLE score
  (e_id          INTEGER                  NOT NULL,
   g_id          INTEGER                  NOT NULL,
   s_time        TIMESTAMP WITH TIME ZONE NOT NULL,
   s_level       INTEGER                  NOT NULL DEFAULT 1,
   s_score       INTEGER                  NOT NULL,

   CONSTRAINT pk_s_score
     PRIMARY KEY (e_id, g_id, s_time),

   CONSTRAINT fk_s_score_enduser
     FOREIGN KEY (e_id) REFERENCES enduser (e_id),

   CONSTRAINT fk_s_score_game
     FOREIGN KEY (g_id) REFERENCES game (g_id)
  );

INSERT INTO game(g_id, g_title, g_max_nr_of_levels, g_max_nr_of_enduserscore_entries)
VALUES (1, 'WKSnake 1', 1, 20);
INSERT INTO game(g_id, g_title, g_max_nr_of_levels, g_max_nr_of_enduserscore_entries)
VALUES (2, 'SKTetris', 100, 20);

INSERT INTO score(e_id, g_id, s_time, s_score, s_level)
VALUES (1, 1, current_timestamp - interval '1 day', 1234, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (1, 1, current_timestamp, 879, 1);

INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '5 days', 1111, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '4 days', 20, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '3 days', 1579, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 2, current_timestamp - interval '2 days', 142, 3);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 2, current_timestamp, 87, 2);

CREATE VIEW highscore (e_id, e_name, g_id, g_title, s_score, s_level, s_time) AS
SELECT   e_id, e_name, g_id, g_title, s_score, s_level, s_time
FROM     enduserscore
WHERE    e_verified = TRUE
;

/***********************************************************************
 * Dummy-Tabelle. Diese Tabelle kann für Abfragen eingesetzt 
 * werden, für deren Beantwortung eigentlich gar keine Tabelle
 * benötigt wird. Laut SQL-Standard muss immer eine Tabelle
 * in der FROM-Klausel einer SELECT-Anweisung angegeben werden.
 *
 * In PostgreSQL ist es auch möglich, SELECT-Anweisungen ohne 
 * zugehörige FROM-Klausel anzugeben. Dies ist allerdings nicht
 * standard-konform.
 ***********************************************************************/

CREATE TABLE dummy
(
 id INTEGER NOT NULL,
 CONSTRAINT e_dummy
   PRIMARY KEY (id)
);

INSERT INTO dummy
VALUES (1);

/***********************************************************************
 * Rights
 ***********************************************************************/

REVOKE ALL ON game FROM PUBLIC;
GRANT SELECT ON game TO GROUP iam;

REVOKE ALL ON enduser FROM PUBLIC;
GRANT SELECT ON enduser TO GROUP iam;

REVOKE ALL ON score FROM PUBLIC;
GRANT SELECT ON score TO GROUP iam;

REVOKE ALL ON dummy FROM PUBLIC;
GRANT SELECT ON dummy TO GROUP iam;

ALTER DATABASE web_highscore OWNER TO highscore;

ALTER TABLE game   OWNER TO highscore;
ALTER TABLE score  OWNER TO highscore;

ALTER TABLE dummy  OWNER TO highscore;

ALTER TABLE enduserscore OWNER TO highscore;
ALTER TABLE highscore   OWNER TO highscore;
