LpYQ3XK7

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.first_name = common.trim(NEW.first_name);
56    NEW.last_name  = common.trim(NEW.last_name);
57
58    RETURN NEW;
59END;
60$$ LANGUAGE plpgsql;
61
62CREATE TRIGGER validate_user BEFORE INSERT OR UPDATE ON common.user
63    FOR EACH ROW EXECUTE PROCEDURE common.validate_user();
64
65CREATE TRIGGER update_modified_at BEFORE UPDATE ON common.user
66    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
67
68
69DROP SCHEMA IF EXISTS blog CASCADE;
70CREATE SCHEMA blog;
71
72CREATE TABLE blog.post
73(
74    id bigserial,
75    author_id bigint NOT NULL,
76    title varchar NOT NULL,
77    body text NOT NULL,
78    is_published bool NOT NULL DEFAULT false,
79    created_at timestamptz(0) NOT NULL DEFAULT now(),
80    modified_at timestamptz(0) NOT NULL DEFAULT now(),
81    published_at timestamptz(0) NOT NULL DEFAULT now(),
82
83    PRIMARY KEY (id),
84
85    FOREIGN KEY (author_id) REFERENCES common.user (id)
86);
87
88CREATE TABLE blog.category
89(
90    id bigserial,
91    name varchar NOT NULL,
92    created_at timestamptz(0) NOT NULL DEFAULT now(),
93    modified_at timestamptz(0) NOT NULL DEFAULT now(),
94
95    PRIMARY KEY (id),
96    UNIQUE (name)
97);
98
99CREATE TABLE blog.post_category
100(
101    post_id bigint,
102    category_id bigint,
103
104    PRIMARY KEY (post_id, category_id),
105
106    FOREIGN KEY (post_id) REFERENCES blog.post (id),
107    FOREIGN KEY (category_id) REFERENCES blog.category (id)
108);
109
110CREATE TABLE blog.comment
111(
112    id bigserial,
113    post_id bigint NOT NULL,
114    author_id bigint NOT NULL,
115    body text NOT NULL,
116    created_at timestamptz(0) NOT NULL DEFAULT now(),
117    modified_at timestamptz(0) NOT NULL DEFAULT now(),
118
119    PRIMARY KEY (id),
120
121    FOREIGN KEY (post_id) REFERENCES blog.post (id),
122    FOREIGN KEY (author_id) REFERENCES common.user (id)
123);
124
125CREATE FUNCTION blog.validate_post() RETURNS trigger AS $$
126BEGIN
127    NEW.title = common.trim(NEW.title);
128    NEW.body  = common.trim(NEW.body);
129
130    RETURN NEW;
131END;
132$$ LANGUAGE plpgsql;
133
134CREATE TRIGGER validate_post BEFORE INSERT OR UPDATE ON blog.post
135    FOR EACH ROW EXECUTE PROCEDURE blog.validate_post();
136
137CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.post
138    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
139
140CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.category
141    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();
142
143CREATE TRIGGER update_modified_at BEFORE UPDATE ON blog.comment
144    FOR EACH ROW EXECUTE PROCEDURE common.update_modified_at();