Files

119 lines
5.4 KiB
SQL

-- +goose Up
-- Groupes plats : un groupe contient des utilisateurs ou des invités, jamais d'autres groupes.
-- +goose StatementBegin
CREATE TRIGGER membership_check_ins BEFORE INSERT ON membership
WHEN COALESCE((SELECT kind FROM principal WHERE id = NEW.group_id), '') <> 'group'
OR COALESCE((SELECT kind FROM principal WHERE id = NEW.member_id), '') NOT IN ('user','guest')
BEGIN
SELECT RAISE(ABORT, 'membership : groupe plat, membres de type user ou guest uniquement');
END;
-- +goose StatementEnd
-- +goose StatementBegin
CREATE TRIGGER membership_check_upd BEFORE UPDATE OF group_id, member_id ON membership
WHEN COALESCE((SELECT kind FROM principal WHERE id = NEW.group_id), '') <> 'group'
OR COALESCE((SELECT kind FROM principal WHERE id = NEW.member_id), '') NOT IN ('user','guest')
BEGIN
SELECT RAISE(ABORT, 'membership : groupe plat, membres de type user ou guest uniquement');
END;
-- +goose StatementEnd
-- Le chemin d'un nouveau nœud doit être celui de son parent + son identifiant,
-- et sa profondeur doit être celle du parent + 1.
-- +goose StatementBegin
CREATE TRIGGER document_path_ins BEFORE INSERT ON document
WHEN NEW.parent_id IS NOT NULL
AND (
NEW.path <> (SELECT path FROM document WHERE id = NEW.parent_id) || lower(hex(NEW.id)) || '/'
OR NEW.depth <> (SELECT depth + 1 FROM document WHERE id = NEW.parent_id)
)
BEGIN
SELECT RAISE(ABORT, 'document : path ou profondeur incohérents avec le parent');
END;
-- +goose StatementEnd
-- Les déplacements doivent mettre à jour path/depth de tout le sous-arbre.
-- Le cycle est refusé si le nouveau parent se trouve déjà dans le sous-arbre.
-- +goose StatementBegin
CREATE TRIGGER document_path_upd BEFORE UPDATE OF parent_id, path, depth ON document
WHEN NEW.parent_id IS NOT NULL
AND (
NEW.path <> (SELECT path FROM document WHERE id = NEW.parent_id) || lower(hex(NEW.id)) || '/'
OR NEW.depth <> (SELECT depth + 1 FROM document WHERE id = NEW.parent_id)
OR (SELECT path FROM document WHERE id = NEW.parent_id) LIKE OLD.path || '%'
)
BEGIN
SELECT RAISE(ABORT, 'document : déplacement invalide (path, profondeur ou cycle)');
END;
-- +goose StatementEnd
-- +goose StatementBegin
CREATE TRIGGER document_root_upd BEFORE UPDATE OF parent_id, path, depth, kind ON document
WHEN NEW.parent_id IS NULL
AND (NEW.depth <> 0 OR NEW.path <> lower(hex(NEW.id)) || '/' OR NEW.kind <> 'library')
BEGIN
SELECT RAISE(ABORT, 'document : une racine doit être une bibliothèque (depth 0, path propre)');
END;
-- +goose StatementEnd
-- head_version_id doit désigner une version du même document.
-- +goose StatementBegin
CREATE TRIGGER document_head_version_ins BEFORE INSERT ON document
WHEN NEW.head_version_id IS NOT NULL
AND (SELECT document_id FROM document_version WHERE id = NEW.head_version_id) IS NOT NULL
AND (SELECT document_id FROM document_version WHERE id = NEW.head_version_id) <> NEW.id
BEGIN
SELECT RAISE(ABORT, 'document : head_version_id doit appartenir au document');
END;
-- +goose StatementEnd
-- +goose StatementBegin
CREATE TRIGGER document_head_version_upd BEFORE UPDATE OF head_version_id ON document
WHEN NEW.head_version_id IS NOT NULL
AND (SELECT document_id FROM document_version WHERE id = NEW.head_version_id) IS NOT NULL
AND (SELECT document_id FROM document_version WHERE id = NEW.head_version_id) <> NEW.id
BEGIN
SELECT RAISE(ABORT, 'document : head_version_id doit appartenir au document');
END;
-- +goose StatementEnd
-- Jellyfin ignore les ACL : on n'exporte qu'une bibliothèque racine du cluster,
-- non PRIVATE, ou avec le consentement tracé de son propriétaire.
-- +goose StatementBegin
CREATE TRIGGER export_target_check_ins BEFORE INSERT ON export_target
WHEN COALESCE((SELECT kind FROM document WHERE id = NEW.library_id), '') <> 'library'
OR (SELECT parent_id FROM document WHERE id = NEW.library_id) IS NOT NULL
OR ( COALESCE((SELECT p.kind FROM document d JOIN principal p ON p.id = d.owner_id
WHERE d.id = NEW.library_id), '') <> 'cluster'
AND NEW.consent_by IS NULL )
OR COALESCE((SELECT visibility FROM document WHERE id = NEW.library_id), 'PRIVATE') = 'PRIVATE'
BEGIN
SELECT RAISE(ABORT, 'export_target : bibliothèque racine du cluster non PRIVATE, ou consentement du propriétaire requis');
END;
-- +goose StatementEnd
-- +goose StatementBegin
CREATE TRIGGER export_target_check_upd BEFORE UPDATE OF library_id, consent_by ON export_target
WHEN COALESCE((SELECT kind FROM document WHERE id = NEW.library_id), '') <> 'library'
OR (SELECT parent_id FROM document WHERE id = NEW.library_id) IS NOT NULL
OR ( COALESCE((SELECT p.kind FROM document d JOIN principal p ON p.id = d.owner_id
WHERE d.id = NEW.library_id), '') <> 'cluster'
AND NEW.consent_by IS NULL )
OR COALESCE((SELECT visibility FROM document WHERE id = NEW.library_id), 'PRIVATE') = 'PRIVATE'
BEGIN
SELECT RAISE(ABORT, 'export_target : bibliothèque racine du cluster non PRIVATE, ou consentement du propriétaire requis');
END;
-- +goose StatementEnd
-- +goose Down
DROP TRIGGER IF EXISTS export_target_check_upd;
DROP TRIGGER IF EXISTS export_target_check_ins;
DROP TRIGGER IF EXISTS document_head_version_upd;
DROP TRIGGER IF EXISTS document_head_version_ins;
DROP TRIGGER IF EXISTS document_root_upd;
DROP TRIGGER IF EXISTS document_path_upd;
DROP TRIGGER IF EXISTS document_path_ins;
DROP TRIGGER IF EXISTS membership_check_upd;
DROP TRIGGER IF EXISTS membership_check_ins;