diff options
Diffstat (limited to '')
| -rw-r--r-- | src/db/migrations/0000.sql (renamed from db/migrations/0000.sql) | 69 |
1 files changed, 53 insertions, 16 deletions
diff --git a/db/migrations/0000.sql b/src/db/migrations/0000.sql index f3577d4..b60c55b 100644 --- a/db/migrations/0000.sql +++ b/src/db/migrations/0000.sql @@ -50,11 +50,12 @@ CREATE TABLE admin.user ( middle_name TEXT DEFAULT ''::text NOT NULL, email TEXT DEFAULT ''::text NOT NULL, gender TEXT DEFAULT ''::text NOT NULL, - join_date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, birth_date TIMESTAMP WITH TIME ZONE NOT NULL, - profile_avatar BYTEA, - profile_banner BYTEA, - profile_bio TEXT DEFAULT ''::text NOT NULL + profile_bio TEXT DEFAULT ''::text NOT NULL, + created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + seen TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + deleted BOOLEAN DEFAULT FALSE NOT NULL ); ALTER TABLE admin.user OWNER TO xssbook; @@ -75,10 +76,12 @@ CREATE SEQUENCE IF NOT EXISTS sys.post_id_seq ALTER TABLE sys.post_id_seq OWNER TO xssbook; CREATE TABLE admin.post ( - id INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL, - user_id INTEGER NOT NULL, - content TEXT DEFAULT ''::text NOT NULL, - date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL + id INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL, + user_id INTEGER NOT NULL, + content TEXT DEFAULT ''::text NOT NULL, + created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + deleted BOOLEAN DEFAULT FALSE NOT NULL ); ALTER TABLE admin.post OWNER TO xssbook; @@ -97,11 +100,13 @@ CREATE SEQUENCE IF NOT EXISTS sys.comment_id_seq CACHE 1; CREATE TABLE admin.comment ( - id INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL, - user_id INTEGER NOT NULL, - post_id INTEGER NOT NULL, - content TEXT DEFAULT ''::text NOT NULL, - date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL + id INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL, + user_id INTEGER NOT NULL, + post_id INTEGER NOT NULL, + content TEXT DEFAULT ''::text NOT NULL, + created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + deleted BOOLEAN DEFAULT FALSE NOT NULL ); ALTER TABLE admin.comment OWNER TO xssbook; @@ -115,16 +120,29 @@ ALTER TABLE ONLY admin.comment ALTER TABLE ONLY admin.comment ADD CONSTRAINT comment_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE; +CREATE SEQUENCE IF NOT EXISTS sys.like_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + CREATE TABLE admin.like ( + id INTEGER DEFAULT nextval('sys.like_id_seq'::regclass) NOT NULL, user_id INTEGER NOT NULL, post_id INTEGER, comment_id INTEGER, - date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL + value BOOLEAN NOT NULL DEFAULT TRUE, + created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL ); ALTER TABLE admin.like OWNER TO xssbook; ALTER TABLE ONLY admin.like + ADD CONSTRAINT like_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY admin.like ADD CONSTRAINT like_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE; ALTER TABLE ONLY admin.like @@ -133,16 +151,32 @@ ALTER TABLE ONLY admin.like ALTER TABLE ONLY admin.like ADD CONSTRAINT like_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES admin.comment (id) ON DELETE CASCADE; +ALTER TABLE ONLY admin.like + ADD CONSTRAINT like_post_id_unique UNIQUE (user_id, post_id); + +ALTER TABLE ONLY admin.like + ADD CONSTRAINT like_comment_id_unique UNIQUE (user_id, comment_id); + +CREATE SEQUENCE IF NOT EXISTS sys.follow_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + CREATE TABLE admin.follow ( + id INTEGER DEFAULT nextval('sys.follow_id_seq'::regclass) NOT NULL, follower_id INTEGER NOT NULL, followee_id INTEGER NOT NULL, - date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL + value BOOLEAN NOT NULL DEFAULT TRUE, + created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL, + modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL ); ALTER TABLE admin.follow OWNER TO xssbook; ALTER TABLE ONLY admin.follow - ADD CONSTRAINT follow_pkey PRIMARY KEY (follower_id, followee_id); + ADD CONSTRAINT follow_pkey PRIMARY KEY (id); ALTER TABLE ONLY admin.follow ADD CONSTRAINT follow_follower_id FOREIGN KEY (follower_id) REFERENCES admin.user (id) ON DELETE CASCADE; @@ -150,6 +184,9 @@ ALTER TABLE ONLY admin.follow ALTER TABLE ONLY admin.follow ADD CONSTRAINT follow_followee_id FOREIGN KEY (followee_id) REFERENCES admin.user (id) ON DELETE CASCADE; +ALTER TABLE ONLY admin.follow + ADD CONSTRAINT follow_follower_unique UNIQUE (follower_id, followee_id); + CREATE TABLE admin.media ( name TEXT NOT NULL, content BYTEA NOT NULL, |