1 CREATE TABLE category (
3 name varchar(100) NOT NULL UNIQUE
6 INSERT INTO category (name)
23 ('Gender/Identity/Sexuality'),
24 ('Race/Colonisation'),
26 ('Science/Technology'),
27 ('Programming/Software'),
28 ('Health/Sports/Fitness'),
34 CREATE TABLE community (
35 id serial PRIMARY KEY,
36 name varchar(20) NOT NULL UNIQUE,
37 title varchar(100) NOT NULL,
39 category_id int REFERENCES category ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
40 creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
41 removed boolean DEFAULT FALSE NOT NULL,
42 published timestamp NOT NULL DEFAULT now(),
46 CREATE TABLE community_moderator (
47 id serial PRIMARY KEY,
48 community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
49 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
50 published timestamp NOT NULL DEFAULT now(),
51 UNIQUE (community_id, user_id)
54 CREATE TABLE community_follower (
55 id serial PRIMARY KEY,
56 community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
57 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
58 published timestamp NOT NULL DEFAULT now(),
59 UNIQUE (community_id, user_id)
62 CREATE TABLE community_user_ban (
63 id serial PRIMARY KEY,
64 community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
65 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
66 published timestamp NOT NULL DEFAULT now(),
67 UNIQUE (community_id, user_id)
70 INSERT INTO community (name, title, category_id, creator_id)
71 VALUES ('main', 'The Default Community', 1, 1);
74 id serial PRIMARY KEY,
75 name varchar(20) NOT NULL UNIQUE,
77 creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
78 published timestamp NOT NULL DEFAULT now(),