QTijTz7s

1DROP SCHEMA IF EXISTS public CASCADE;
2
3
4DROP SCHEMA IF EXISTS common CASCADE;
5CREATE SCHEMA common;
6
7CREATE TABLE common.user
8(
9    id bigserial,
10    email varchar NOT NULL,
11    password varchar NOT NULL,
12    first_name varchar NOT NULL,
13    last_name varchar NOT NULL,
14    is_admin bool NOT NULL DEFAULT false,
15    registered_at timestamptz(0) NOT NULL DEFAULT now(),
16    activated_at timestamptz(0) NULL,
17    modified_at timestamptz(0) NOT NULL DEFAULT now(),
18
19    PRIMARY KEY (id),
20    UNIQUE (email)
21);
22
23CREATE FUNCTION common.pwd(pwd varchar) RETURNS char(32) AS $$
24BEGIN
25    RETURN md5(pwd);
26END;
27$$ LANGUAGE plpgsql;
28
29CREATE FUNCTION common.trim(str varchar) RETURNS varchar AS $$
30BEGIN
31    IF str IS NULL THEN
32        RETURN NULL;
33    END IF;
34
35    str = trim(str);
36
37    IF str = '' THEN
38        RETURN NULL;
39    END IF;
40
41    RETURN str;
42END;
43$$ LANGUAGE plpgsql;
44
45CREATE FUNCTION common.update_modified_at() RETURNS trigger AS $$
46BEGIN
47    NEW.modified_at = now();
48    RETURN NEW;
49END;
50$$ LANGUAGE plpgsql;
51
52CREATE FUNCTION common.validate_user() RETURNS trigger AS $$
53BEGIN
54    NEW.email      = common.trim(NEW.email);
55    NEW.email      = lower(NEW.email);
56    NEW.first_name = common.trim(NEW.first_name);
57    NEW.last_name  = common.trim(NEW.last_name);
58
59    RETURN NEW;
60END;
61$$ LANGUAGE plpgsql;
62
63CREATE TRIGGER validate_user BEFORE INSERT OR UPDATE ON common.user
64    FOR EACH ROW EXECUTE PROCEDURE common.validate_user();
65
66CREATE TRIGGER update_modified_at BEFORE UPDATE ON common.user
67    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
68
69
70DROP SCHEMA IF EXISTS blog CASCADE;
71CREATE SCHEMA blog;
72
73CREATE TABLE blog.post
74(
75    id bigserial,
76    author_id bigint NOT NULL,
77    title varchar NOT NULL,
78    body text NOT NULL,
79    is_published bool NOT NULL DEFAULT false,
80    created_at timestamptz(0) NOT NULL DEFAULT now(),
81    modified_at timestamptz(0) NOT NULL DEFAULT now(),
82    published_at timestamptz(0) NOT NULL DEFAULT now(),
83
84    PRIMARY KEY (id),
85
86    FOREIGN KEY (author_id) REFERENCES common.user (id)
87);
88
89CREATE UNIQUE INDEX post_title_key ON blog.post (lower(title));
90
91CREATE TABLE blog.category
92(
93    id bigserial,
94    name varchar NOT NULL,
95    created_at timestamptz(0) NOT NULL DEFAULT now(),
96    modified_at timestamptz(0) NOT NULL DEFAULT now(),
97
98    PRIMARY KEY (id)
99);
100
101CREATE UNIQUE INDEX category_name_key ON blog.category (lower(name));
102
103CREATE TABLE blog.post_category
104(
105    post_id bigint,
106    category_id bigint,
107
108    PRIMARY KEY (post_id, category_id),
109
110    FOREIGN KEY (post_id) REFERENCES blog.post (id) ON DELETE CASCADE,
111    FOREIGN KEY (category_id) REFERENCES blog.category (id) ON DELETE CASCADE
112);
113
114CREATE TABLE blog.comment
115(
116    id bigserial,
117    post_id bigint NOT NULL,
118    author_id bigint NOT NULL,
119    body text NOT NULL,
120    created_at timestamptz(0) NOT NULL DEFAULT now(),
121    modified_at timestamptz(0) NOT NULL DEFAULT now(),
122
123    PRIMARY KEY (id),
124
125    FOREIGN KEY (post_id) REFERENCES blog.post (id),
126    FOREIGN KEY (author_id) REFERENCES common.user (id)
127);
128
129CREATE FUNCTION blog.validate_post() RETURNS trigger AS $$
130BEGIN
131    NEW.title = common.trim(NEW.title);
132    NEW.body  = common.trim(NEW.body);
133
134    RETURN NEW;
135END;
136$$ LANGUAGE plpgsql;
137
138CREATE TRIGGER validate_post BEFORE INSERT OR UPDATE ON blog.post
139    FOR EACH ROW EXECUTE PROCEDURE blog.validate_post();
140
141CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.post
142    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
143
144CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.category
145    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
146
147CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.comment
148    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();