148 lines
3.9 KiB
PL/PgSQL
148 lines
3.9 KiB
PL/PgSQL
create type "public"."email" as ("email" text);
|
|
|
|
create type "public"."hashed_text" as ("hashed" text);
|
|
|
|
create type "public"."usr_username" as ("username" text);
|
|
|
|
create type "public"."audit_kind" as enum ('modify', 'delete', 'create');
|
|
|
|
create type "public"."thread_kind" as enum ('post', 'short', 'message');
|
|
|
|
create table "public"."audit" (
|
|
"id" integer generated always as identity not null,
|
|
"kind" audit_kind not null
|
|
);
|
|
|
|
|
|
create table "public"."thread" (
|
|
"id" integer generated always as identity not null,
|
|
"uid" uuid not null default gen_random_uuid(),
|
|
"kind" thread_kind not null
|
|
);
|
|
|
|
|
|
create table "public"."usr" (
|
|
"id" integer generated always as identity not null,
|
|
"uid" uuid not null default gen_random_uuid(),
|
|
"deleted" boolean not null default false,
|
|
"username" usr_username not null,
|
|
"password" hashed_text not null,
|
|
"email" email not null
|
|
);
|
|
|
|
|
|
create table "public"."usr_audit" (
|
|
"usr" integer not null,
|
|
"kind" audit_kind not null,
|
|
"actor" integer not null,
|
|
"prev_username" usr_username,
|
|
"prev_password" hashed_text,
|
|
"prev_email" email
|
|
);
|
|
|
|
|
|
CREATE UNIQUE INDEX audit_pkey ON public.audit USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX thread_pkey ON public.thread USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX usr_email_key ON public.usr USING btree (email);
|
|
|
|
CREATE UNIQUE INDEX usr_pkey ON public.usr USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX usr_username_key ON public.usr USING btree (username);
|
|
|
|
alter table "public"."audit" add constraint "audit_pkey" PRIMARY KEY using index "audit_pkey";
|
|
|
|
alter table "public"."thread" add constraint "thread_pkey" PRIMARY KEY using index "thread_pkey";
|
|
|
|
alter table "public"."usr" add constraint "usr_pkey" PRIMARY KEY using index "usr_pkey";
|
|
|
|
alter table "public"."usr" add constraint "usr_email_key" UNIQUE using index "usr_email_key";
|
|
|
|
alter table "public"."usr" add constraint "usr_username_key" UNIQUE using index "usr_username_key";
|
|
|
|
alter table "public"."usr_audit" add constraint "fk_usr_audit_actor" FOREIGN KEY (actor) REFERENCES usr(id) not valid;
|
|
|
|
alter table "public"."usr_audit" validate constraint "fk_usr_audit_actor";
|
|
|
|
alter table "public"."usr_audit" add constraint "fk_usr_audit_usr" FOREIGN KEY (usr) REFERENCES usr(id) not valid;
|
|
|
|
alter table "public"."usr_audit" validate constraint "fk_usr_audit_usr";
|
|
|
|
set check_function_bodies = off;
|
|
|
|
CREATE OR REPLACE FUNCTION public.do_usr_audit()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
declare
|
|
audit_kind public.audit_kind;
|
|
usr_id int;
|
|
prev_username public.usr_username := null;
|
|
prev_password public.hashed_text := null;
|
|
prev_email public.email := null;
|
|
begin
|
|
if (TG_OP = 'UPDATE') then
|
|
usr_id := OLD.id;
|
|
audit_kind := 'modify';
|
|
prev_username := OLD.username;
|
|
prev_password := OLD.password;
|
|
prev_email := OLD.email;
|
|
elsif (TG_OP = 'INSERT') then
|
|
usr_id := NEW.id;
|
|
audit_kind := 'create';
|
|
end if;
|
|
|
|
insert into public.usr_audit
|
|
( kind
|
|
, usr
|
|
, actor
|
|
, prev_username
|
|
, prev_email
|
|
, prev_password
|
|
)
|
|
values
|
|
( audit_kind
|
|
, usr_id
|
|
, usr_id -- TODO
|
|
, prev_username
|
|
, prev_email
|
|
, prev_password
|
|
);
|
|
|
|
return NEW;
|
|
end;
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION public.do_usr_soft_delete()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
declare
|
|
audit_id int;
|
|
begin
|
|
insert into public.usr_audit
|
|
( kind
|
|
, usr
|
|
, actor
|
|
)
|
|
values
|
|
( 'delete' :: public.audit_kind
|
|
, OLD.id
|
|
, OLD.id -- TODO
|
|
);
|
|
|
|
update public.usr
|
|
set deleted = true
|
|
where id = OLD.id;
|
|
|
|
return null;
|
|
end;
|
|
$function$
|
|
;
|
|
|
|
CREATE TRIGGER trigger_usr_audit AFTER INSERT OR UPDATE ON public.usr FOR EACH ROW EXECUTE FUNCTION do_usr_audit();
|
|
|
|
CREATE TRIGGER trigger_usr_soft_delete BEFORE DELETE ON public.usr FOR EACH ROW EXECUTE FUNCTION do_usr_soft_delete();
|