mirror of
https://github.com/AppFlowy-IO/AppFlowy-Cloud.git
synced 2025-04-19 03:24:42 -04:00
70 lines
2.5 KiB
PL/PgSQL
70 lines
2.5 KiB
PL/PgSQL
-- collab update table.
|
|
CREATE TABLE IF NOT EXISTS af_collab (
|
|
oid TEXT NOT NULL,
|
|
blob BYTEA NOT NULL,
|
|
len INTEGER,
|
|
partition_key INTEGER NOT NULL,
|
|
encrypt INTEGER DEFAULT 0,
|
|
owner_uid BIGINT NOT NULL,
|
|
deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
workspace_id UUID NOT NULL REFERENCES af_workspace(workspace_id) ON DELETE CASCADE,
|
|
PRIMARY KEY (oid, partition_key)
|
|
) PARTITION BY LIST (partition_key);
|
|
CREATE TABLE IF NOT EXISTS af_collab_document PARTITION OF af_collab FOR
|
|
VALUES IN (0);
|
|
CREATE TABLE IF NOT EXISTS af_collab_database PARTITION OF af_collab FOR
|
|
VALUES IN (1);
|
|
CREATE TABLE IF NOT EXISTS af_collab_w_database PARTITION OF af_collab FOR
|
|
VALUES IN (2);
|
|
CREATE TABLE IF NOT EXISTS af_collab_folder PARTITION OF af_collab FOR
|
|
VALUES IN (3);
|
|
CREATE TABLE IF NOT EXISTS af_collab_database_row PARTITION OF af_collab FOR
|
|
VALUES IN (4);
|
|
CREATE TABLE IF NOT EXISTS af_collab_user_awareness PARTITION OF af_collab FOR
|
|
VALUES IN (5);
|
|
|
|
CREATE TABLE IF NOT EXISTS af_collab_member (
|
|
uid BIGINT REFERENCES af_user(uid) ON DELETE CASCADE,
|
|
oid TEXT NOT NULL,
|
|
permission_id INTEGER REFERENCES af_permissions(id) NOT NULL,
|
|
PRIMARY KEY(uid, oid)
|
|
);
|
|
|
|
-- Listener
|
|
CREATE OR REPLACE FUNCTION notify_af_collab_member_change() RETURNS trigger AS $$
|
|
DECLARE
|
|
payload TEXT;
|
|
BEGIN
|
|
payload := json_build_object(
|
|
'old', row_to_json(OLD),
|
|
'new', row_to_json(NEW),
|
|
'action_type', TG_OP
|
|
)::text;
|
|
|
|
PERFORM pg_notify('af_collab_member_channel', payload);
|
|
-- Return the new row state for INSERT/UPDATE, and the old state for DELETE.
|
|
IF TG_OP = 'DELETE' THEN
|
|
RETURN OLD;
|
|
ELSE
|
|
RETURN NEW;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE TRIGGER af_collab_member_change_trigger
|
|
AFTER INSERT OR UPDATE OR DELETE ON af_collab_member
|
|
FOR EACH ROW EXECUTE FUNCTION notify_af_collab_member_change();
|
|
|
|
-- collab snapshot. It will be used to store the snapshots of the collab.
|
|
CREATE TABLE IF NOT EXISTS af_collab_snapshot (
|
|
sid BIGSERIAL PRIMARY KEY,-- snapshot id
|
|
oid TEXT NOT NULL,
|
|
blob BYTEA NOT NULL,
|
|
len INTEGER NOT NULL,
|
|
encrypt INTEGER DEFAULT 0,
|
|
deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
|
|
workspace_id UUID NOT NULL REFERENCES af_workspace(workspace_id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_af_collab_snapshot_oid ON af_collab_snapshot(oid);
|