44 lines
1.4 KiB
SQL
44 lines
1.4 KiB
SQL
create table if not exists "user" (
|
|
"id" serial primary key,
|
|
"username" varchar(32) not null unique,
|
|
"password_hash" varchar(72) not null,
|
|
"status" varchar(200) not null default '',
|
|
"picture" varchar(300) not null default '',
|
|
"bio" varchar(200) not null default ''
|
|
);
|
|
|
|
create table if not exists "role" (
|
|
"id" serial primary key,
|
|
"name" varchar(32) not null unique
|
|
);
|
|
|
|
create table if not exists "role_binding" (
|
|
"user_id" integer not null references "user"("id") on delete cascade,
|
|
"role_id" integer not null references "role"("id") on delete cascade,
|
|
primary key ("user_id", "role_id"),
|
|
unique ("user_id", "role_id")
|
|
);
|
|
|
|
create table if not exists "channel" (
|
|
"id" serial primary key,
|
|
"name" varchar(32) not null unique,
|
|
"description" varchar(200) default null
|
|
);
|
|
|
|
create type "right_t" as enum ('R', 'W', 'RW', 'A');
|
|
|
|
create table if not exists "right" (
|
|
"role_id" integer not null references "role"("id"),
|
|
"channel_id" integer not null references "channel"("id"),
|
|
"rights" right_t not null,
|
|
primary key ("role_id", "channel_id"),
|
|
unique ("role_id", "channel_id")
|
|
);
|
|
|
|
create table if not exists "message" (
|
|
"id" serial primary key,
|
|
"sender_id" integer not null references "user"("id") on delete cascade,
|
|
"channel_id" integer not null references "channel"("id") on delete cascade,
|
|
"time" timestamp without time zone default (now() at time zone 'utc'),
|
|
"content" varchar(2000) not null
|
|
); |