Files

45 lines
1.4 KiB
SQL

-- +goose Up
-- Politique et visibilité effectives, calculées de haut en bas.
CREATE VIEW v_document_effective AS
WITH RECURSIVE t(id, eff_visibility, eff_policy_id) AS (
SELECT id, visibility, policy_id FROM document
WHERE parent_id IS NULL AND deleted_at IS NULL
UNION ALL
SELECT d.id, COALESCE(d.visibility, t.eff_visibility), COALESCE(d.policy_id, t.eff_policy_id)
FROM document d JOIN t ON d.parent_id = t.id
WHERE d.deleted_at IS NULL
)
SELECT id, eff_visibility, eff_policy_id FROM t;
-- Blobs encore référencés par une version non purgée ou par un lien d'export.
CREATE VIEW v_blob_refs AS
SELECT root, SUM(refs) AS refs
FROM (
SELECT content_root AS root, COUNT(*) AS refs
FROM document_version
WHERE content_purged_at IS NULL
GROUP BY content_root
UNION ALL
SELECT blob_root AS root, COUNT(*) AS refs
FROM export_link
WHERE state <> 'stale'
GROUP BY blob_root
)
GROUP BY root;
-- Ce que l'administrateur peut voir des bibliothèques des autres : un volume, jamais des noms.
CREATE VIEW v_usage_by_owner AS
SELECT d.owner_id,
COUNT(*) AS documents,
COALESCE(SUM(v.size), 0) AS bytes
FROM document d
LEFT JOIN document_version v ON v.id = d.head_version_id
WHERE d.deleted_at IS NULL
GROUP BY d.owner_id;
-- +goose Down
DROP VIEW IF EXISTS v_usage_by_owner;
DROP VIEW IF EXISTS v_blob_refs;
DROP VIEW IF EXISTS v_document_effective;