78 lines
2.3 KiB
PL/PgSQL
78 lines
2.3 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS "user"
|
|
(
|
|
"id" UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v7(),
|
|
"avatar_id" UUID REFERENCES "file" ("id") ON DELETE SET NULL,
|
|
"username" VARCHAR NOT NULL UNIQUE,
|
|
"display_name" VARCHAR,
|
|
"email" VARCHAR NOT NULL,
|
|
"password_hash" VARCHAR NOT NULL,
|
|
"bot" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
"system" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
"settings" JSONB NOT NULL DEFAULT '{}'::JSONB
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user_relation"
|
|
(
|
|
"user_id" UUID NOT NULL CHECK (user_id <> other_id) REFERENCES "user" ("id") ON DELETE CASCADE,
|
|
"other_id" UUID NOT NULL CHECK (user_id <> other_id) REFERENCES "user" ("id") ON DELETE CASCADE,
|
|
"type" INT2 NOT NULL,
|
|
"created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
"updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("user_id", "other_id")
|
|
);
|
|
|
|
-- create system account
|
|
INSERT INTO "user" ("username", "display_name", "email", "password_hash", "bot", "system")
|
|
VALUES ('system', 'System', 'system@lionarius.ru', '', TRUE, TRUE);
|
|
|
|
CREATE OR REPLACE FUNCTION check_avatar_is_image()
|
|
RETURNS TRIGGER AS
|
|
$$
|
|
DECLARE
|
|
file_content_type VARCHAR;
|
|
BEGIN
|
|
-- Skip check if icon_id is null
|
|
IF NEW.avatar_id IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- Retrieve content_type from file table
|
|
SELECT content_type
|
|
INTO file_content_type
|
|
FROM file
|
|
WHERE id = NEW.avatar_id;
|
|
|
|
-- Raise exception if content_type does not start with 'image/'
|
|
IF file_content_type IS NULL OR file_content_type NOT LIKE 'image/%' THEN
|
|
RAISE EXCEPTION 'avatar_id must reference a file with content_type starting with image/';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trigger_check_icon_is_image
|
|
BEFORE INSERT OR UPDATE
|
|
ON "user"
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION check_avatar_is_image();
|
|
|
|
CREATE OR REPLACE FUNCTION fn_on_user_relation_update()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS
|
|
$$
|
|
BEGIN
|
|
IF (TG_OP = 'UPDATE') THEN
|
|
NEW.updated_at := now();
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER trg_user_relation_update
|
|
BEFORE UPDATE
|
|
ON "user_relation"
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION fn_on_user_relation_update(); |