VUfb4PkE

1begin;
2drop schema if exists blog cascade;
3create schema blog;
4create table blog.users (
5  user_id serial not null primary key,
6  username text not null check(username ~ '^(?=.{3,32}$)[a-zA-Z]\w+$'),
7  password text not null check(password ~ '^.{6,32}$')
8);
9create unique index username_idx on blog.users (lower(username));
10create index password_idx on blog.users (password);
11create table blog.posts (
12  post_id serial not null primary key,
13  user_id int not null,
14  subject text not null check(trim(subject) <> '' and length(subject) <= 128),
15  content text not null check(trim(content) <> '' and length(content) <= 2048),
16  constraint user_fk foreign key (user_id) references blog.users (user_id)
17);
18create table blog.comments (
19  comment_id serial not null primary key,
20  post_id int not null,
21  user_id int not null,
22  content text not null check(trim(content) <> '' and length(content) <= 1024),
23  constraint post_fk foreign key (post_id) references blog.posts (post_id),
24  constraint user_fk foreign key (user_id) references blog.users (user_id)
25);
26insert into blog.users (username, password) values ('admin', 'admin1'), ('test123', 'qweasd'), ('noob', '123456');
27insert into blog.posts (user_id, subject, content) values (2, 'Test', 'This is test message.');
28insert into blog.comments (post_id, user_id, content) values (1, 3, 'buy viagra *url*');
29commit;