51 lines
1.6 KiB
SQL
51 lines
1.6 KiB
SQL
CREATE TABLE locaties(
|
|
locatieid int PRIMARY KEY,
|
|
naam varchar(64) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE moderators(
|
|
moderatorid serial PRIMARY KEY,
|
|
naam varchar(64) NOT NULL,
|
|
hash varchar(255),
|
|
maylogin boolean NOT NULL,
|
|
lastseen timestamp
|
|
|
|
);
|
|
|
|
CREATE TABLE twitterberichten(
|
|
twitterid bigint PRIMARY KEY,
|
|
datumpublished timestamp NOT NULL
|
|
);
|
|
|
|
CREATE TABLE statuses(
|
|
statusid int PRIMARY KEY,
|
|
status varchar(32)
|
|
);
|
|
|
|
CREATE TABLE berichten(
|
|
berichtid serial PRIMARY KEY,
|
|
bericht varchar(140) NOT NULL,
|
|
datumontvangen timestamp NOT NULL,
|
|
naamposter varchar(32),
|
|
moderatorid int,
|
|
datumgekeurd timestamp,
|
|
statusid int NOT NULL,
|
|
locatieid int NOT NULL,
|
|
twitterid bigint,
|
|
FOREIGN KEY (twitterid) REFERENCES twitterberichten(twitterid),
|
|
FOREIGN KEY (locatieid) REFERENCES locaties(locatieid),
|
|
FOREIGN KEY (moderatorid) REFERENCES moderators(moderatorid),
|
|
FOREIGN KEY (statusid) REFERENCES statuses(statusid)
|
|
);
|
|
|
|
-- administrator:password
|
|
INSERT INTO moderators(naam, hash, maylogin) VALUES ('administrator', 'a3eec2461d6637164d836db2c9f19795357c80c1420ccc13386839ae1104b53a', true);
|
|
INSERT INTO locaties(locatieid, naam) VALUES ('1', 'Utrecht Centraal');
|
|
INSERT INTO locaties(locatieid, naam) VALUES ('2', 'Amsterdam Centraal');
|
|
INSERT INTO locaties(locatieid, naam) VALUES ('3', 'Rotterdam Centraal');
|
|
INSERT INTO statuses(statusid, status) VALUES ('1', 'pending moderation');
|
|
INSERT INTO statuses(statusid, status) VALUES ('2', 'moderation lock');
|
|
INSERT INTO statuses(statusid, status) VALUES ('3', 'moderation rejected');
|
|
INSERT INTO statuses(statusid, status) VALUES ('4', 'moderation accepted');
|
|
INSERT INTO statuses(statusid, status) VALUES ('5', 'published');
|