DROP TABLE voters;
CREATE TABLE voters (
    username character varying(32) PRIMARY KEY,
    passwd character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    vote int NOT NULL DEFAULT 1,
    state character varying(32)
);

-- insert "johnpc" with password "foo".
INSERT INTO voters (username,passwd,name,vote)
VALUES ('johnpc','aaKNIEDOaueR6','Jan-Pieter Cornet',1);
-- insert "guest" with password "guest".
INSERT INTO voters (username,passwd,name,vote)
VALUES ('guest','zzVQM.JFZA8GY','Honorary guest voter',1);

DROP TABLE candidates;
CREATE TABLE candidates (
    id serial PRIMARY KEY,
    name character varying(255) NOT NULL UNIQUE
);

INSERT INTO candidates (name) VALUES ('Larry');
INSERT INTO candidates (name) VALUES ('Guido');
INSERT INTO candidates (name) VALUES ('John');
INSERT INTO candidates (name) VALUES ('Tim');
INSERT INTO candidates (name) VALUES ('Moe');
INSERT INTO candidates (name) VALUES ('Groucho');

-- we assume that apache runs cgi-bin scripts as user "nobody", which
-- should also be a valid PostgreSQL user.
GRANT SELECT ON candidates TO nobody;

DROP TABLE votenr;
CREATE TABLE votenr (
    id serial PRIMARY KEY,
    nr character varying(255) NOT NULL UNIQUE
);

GRANT SELECT, INSERT ON votenr TO nobody;
GRANT SELECT, UPDATE, INSERT, DELETE ON votenr_id_seq TO nobody;

DROP TABLE vote;
CREATE TABLE vote (
    vote int4 NOT NULL REFERENCES votenr(id),
    candidate int4 NOT NULL REFERENCES candidates(id),
    PRIMARY KEY (vote, candidate)
);

GRANT SELECT, INSERT ON vote TO nobody;
