129 lines
4.6 KiB
PL/PgSQL
129 lines
4.6 KiB
PL/PgSQL
-- Canonical document schema_version 2 (ANATOMICAL_SHEET_MODEL.md §2, Step 4c):
|
|
-- shared `references` registry cited by `reference_ids`, `review_status`
|
|
-- on findings and annotations, optional `count_range` on vertebral regions.
|
|
-- No version 1 document was ever published (the only row is the official
|
|
-- equine draft, regenerated by its seed), so version 1 is no longer stored.
|
|
|
|
alter table public.anatomical_sheet_versions
|
|
drop constraint anatomical_sheet_versions_schema_known;
|
|
|
|
alter table public.anatomical_sheet_versions
|
|
add constraint anatomical_sheet_versions_schema_known
|
|
check (document_schema_version = 2);
|
|
|
|
-- Shape: the references registry is one more id-bearing array.
|
|
create or replace function private.sheet_document_has_shape(p_doc jsonb)
|
|
returns boolean
|
|
language sql
|
|
immutable
|
|
set search_path = ''
|
|
as $$
|
|
select jsonb_typeof(p_doc) = 'object'
|
|
and jsonb_typeof(coalesce(p_doc -> 'views', '[]'::jsonb)) = 'array'
|
|
and jsonb_typeof(coalesce(p_doc -> 'annotations', '[]'::jsonb)) = 'array'
|
|
and jsonb_typeof(coalesce(p_doc -> 'categories', '[]'::jsonb)) = 'array'
|
|
and jsonb_typeof(coalesce(p_doc -> 'finding_zones', '[]'::jsonb)) = 'array'
|
|
and jsonb_typeof(coalesce(p_doc -> 'references', '[]'::jsonb)) = 'array'
|
|
and jsonb_typeof(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) = 'array';
|
|
$$;
|
|
|
|
-- Uniqueness of the id-bearing collections, references included.
|
|
create or replace function private.sheet_document_duplicate_issues(p_doc jsonb)
|
|
returns text[]
|
|
language plpgsql
|
|
immutable
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_issues text[] := '{}';
|
|
r record;
|
|
begin
|
|
for r in
|
|
select * from (values
|
|
('views', 'id', 'view.id.duplicate'),
|
|
('annotations', 'id', 'annotation.id.duplicate'),
|
|
('categories', 'id', 'category.id.duplicate'),
|
|
('finding_zones', 'id', 'finding_zone.id.duplicate'),
|
|
('references', 'id', 'reference.id.duplicate')
|
|
) as t(collection, key, code)
|
|
loop
|
|
if exists (
|
|
select 1 from jsonb_array_elements(coalesce(p_doc -> r.collection, '[]'::jsonb)) e
|
|
group by e ->> r.key having count(*) > 1
|
|
) then
|
|
v_issues := private.append_issue(v_issues, r.code);
|
|
end if;
|
|
end loop;
|
|
if exists (
|
|
select 1 from jsonb_array_elements(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) g
|
|
group by g ->> 'code' having count(*) > 1
|
|
) then
|
|
v_issues := private.append_issue(v_issues, 'region.code.duplicate');
|
|
end if;
|
|
return v_issues;
|
|
end;
|
|
$$;
|
|
|
|
-- Citations point at declared references (every depth of the finding tree,
|
|
-- categories included); the usual vertebra count sits inside its range.
|
|
create or replace function private.sheet_document_reference_issues(p_doc jsonb)
|
|
returns text[]
|
|
language plpgsql
|
|
immutable
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_issues text[] := '{}';
|
|
begin
|
|
if exists (
|
|
select 1
|
|
from jsonb_path_query(coalesce(p_doc -> 'finding_zones', '[]'::jsonb), '$[*].categories[*].**.reference_ids[*]') cited
|
|
where not exists (
|
|
select 1 from jsonb_array_elements(coalesce(p_doc -> 'references', '[]'::jsonb)) ref
|
|
where ref -> 'id' = cited
|
|
)
|
|
) then
|
|
v_issues := private.append_issue(v_issues, 'finding.reference.missing');
|
|
end if;
|
|
if exists (
|
|
select 1
|
|
from jsonb_array_elements(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) g
|
|
where jsonb_typeof(g -> 'count_range') = 'object'
|
|
and not (
|
|
(g -> 'count_range' ->> 'min')::numeric <= (g ->> 'count')::numeric
|
|
and (g ->> 'count')::numeric <= (g -> 'count_range' ->> 'max')::numeric
|
|
)
|
|
) then
|
|
v_issues := private.append_issue(v_issues, 'region.count_range.invalid');
|
|
end if;
|
|
-- Zod refuses non-http(s) reference urls at parse; a document sent straight
|
|
-- to the RPC must meet the same bar before anything renders it as a link.
|
|
if exists (
|
|
select 1
|
|
from jsonb_array_elements(coalesce(p_doc -> 'references', '[]'::jsonb)) ref
|
|
where jsonb_typeof(ref -> 'url') = 'string'
|
|
and (ref ->> 'url') !~ '^https?://'
|
|
) then
|
|
v_issues := private.append_issue(v_issues, 'reference.url.scheme');
|
|
end if;
|
|
return v_issues;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function private.sheet_document_reference_issues(jsonb) from public;
|
|
|
|
create or replace function private.sheet_document_issues(p_doc jsonb)
|
|
returns text[]
|
|
language sql
|
|
immutable
|
|
set search_path = ''
|
|
as $$
|
|
select case
|
|
when not private.sheet_document_has_shape(p_doc) then array['document.shape']
|
|
else private.sheet_document_view_issues(p_doc)
|
|
|| private.sheet_document_duplicate_issues(p_doc)
|
|
|| private.sheet_document_annotation_issues(p_doc)
|
|
|| private.sheet_document_reference_issues(p_doc)
|
|
end;
|
|
$$;
|