82 lines
7.6 KiB
PL/PgSQL
82 lines
7.6 KiB
PL/PgSQL
alter table public.upload_jobs
|
||
add column kind text not null default 'upload' check (kind in ('upload','map')),
|
||
add column source_dataset_id uuid references public.datasets(id);
|
||
|
||
create function public.refresh_map(p_dataset uuid,p_owner uuid,p_source uuid) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; s public.datasets;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner for update;
|
||
if not found then raise exception 'Map owner permission required'; end if;
|
||
if d.state not in ('draft','rejected','published') or d.update_state='review' or d.contribution_status in ('submitted','accepted','rejected') then raise exception 'Wait until the current review finishes'; end if;
|
||
if exists(select 1 from public.upload_jobs where dataset_id=d.id and state in ('queued','running')) then raise exception 'A render is already in progress'; end if;
|
||
select * into s from public.datasets where id=p_source;
|
||
if not found or not (s.owner_id=p_owner or (s.parent_dataset_id=d.id and s.contribution_status='accepted')) then raise exception 'Capture permission required'; end if;
|
||
if s.bounds<>d.bounds then raise exception 'Capture boundaries must match the map exactly'; end if;
|
||
if s.map_path is null or s.state not in ('draft','rejected','review','published') or not exists(select 1 from public.dataset_inputs where dataset_id=s.id and validated) then raise exception 'Wait for the capture to finish processing'; end if;
|
||
perform public.assert_capture_server(d.contribution_server_address, (select manifest->>'serverAddress' from public.dataset_inputs where dataset_id=s.id));
|
||
insert into public.upload_jobs(dataset_id,kind,source_dataset_id) values(d.id,'map',s.id)
|
||
on conflict(dataset_id) do update set id=gen_random_uuid(),kind='map',source_dataset_id=s.id,state='queued',attempts=0,progress=0,error=null,lease_owner=null,lease_until=null,created_at=now(),updated_at=now();
|
||
end; $$;
|
||
revoke execute on function public.refresh_map(uuid,uuid,uuid) from public,anon,authenticated;
|
||
grant execute on function public.refresh_map(uuid,uuid,uuid) to service_role;
|
||
|
||
create or replace function public.complete_job(p_job uuid,p_worker text,p_count integer,p_cover text) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare j public.upload_jobs; d public.datasets;
|
||
begin
|
||
select * into j from public.upload_jobs where id=p_job and lease_owner=p_worker and state='running' and lease_until>now() for update;
|
||
if not found then raise exception 'Worker lease lost'; end if;
|
||
select * into d from public.datasets where id=j.dataset_id for update;
|
||
if j.kind='map' then
|
||
if d.state not in ('draft','rejected','published') or d.update_state='review' or d.contribution_status in ('submitted','accepted','rejected') then raise exception 'Map cannot change during review'; end if;
|
||
update public.dataset_inputs set manifest=manifest||jsonb_build_object('mapPath',d.id::text||'/maps/'||j.id::text||'.webp') where dataset_id=d.id and validated;
|
||
if not found then raise exception 'Dataset not validated'; end if;
|
||
update public.datasets set update_state=case when state='published' then 'draft' else null end,state=case when state='published' then state else 'draft' end,rejection_reason=null,updated_at=now() where id=d.id;
|
||
update public.upload_jobs set state='complete',progress=100,lease_until=null,error=null,updated_at=now() where id=j.id;
|
||
return;
|
||
end if;
|
||
if p_count<(case when d.parent_dataset_id is null then 5 else 1 end) or p_count>24 then raise exception 'Invalid panorama count'; end if;
|
||
update public.datasets set state='draft',panorama_count=p_count,map_path=j.dataset_id::text||'/map.webp',cover_path=j.dataset_id::text||'/'||p_cover||'/preview.webp',updated_at=now() where id=j.dataset_id and state='processing';
|
||
if not found then raise exception 'Dataset processing cancelled'; end if;
|
||
update public.dataset_inputs set validated=true where dataset_id=j.dataset_id;
|
||
update public.upload_jobs set state='complete',progress=100,lease_until=null,error=null,updated_at=now() where id=p_job;
|
||
end; $$;
|
||
create or replace function public.submit_draft(p_dataset uuid,p_owner uuid) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; count integer;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner for update;
|
||
if not found then raise exception 'Dataset not found'; end if;
|
||
if d.state not in ('draft','rejected','published') or (d.state='published' and coalesce(d.update_state,'') not in ('draft','rejected')) or d.contribution_status in ('submitted','accepted','rejected') then raise exception 'Draft is not ready for submission'; end if;
|
||
if exists(select 1 from public.upload_jobs where dataset_id=d.id and state in ('queued','running')) then raise exception 'Wait for map rendering to finish before submitting'; end if;
|
||
select jsonb_array_length(manifest->'viewpoints') into count from public.dataset_inputs where dataset_id=p_dataset and validated;
|
||
if count is null or count<(case when d.parent_dataset_id is null then 5 else 1 end) or count>24 then raise exception 'Invalid panorama count'; end if;
|
||
update public.datasets set state=case when state='published' then state else 'review' end,
|
||
update_state=case when state='published' then 'review' end,
|
||
contribution_status=case when parent_dataset_id is not null then 'submitted' end,updated_at=now() where id=p_dataset;
|
||
end; $$;
|
||
create or replace function public.publish_dataset(p_dataset uuid,p_reviewer uuid,p_decision text,p_reason text default null) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; m jsonb; rev uuid; first jsonb; count integer;
|
||
begin
|
||
if not exists(select 1 from public.profiles where id=p_reviewer and role='moderator') then raise exception 'Moderator permission required'; end if;
|
||
select * into d from public.datasets where id=p_dataset and parent_dataset_id is null for update;
|
||
if not found then raise exception 'Map not found'; end if;
|
||
if p_decision='approve' then
|
||
if not(d.state='review' or (d.state='published' and coalesce(d.update_state,'')='review')) then raise exception 'Map is not ready for publication'; end if;
|
||
select manifest into m from public.dataset_inputs where dataset_id=p_dataset and validated;
|
||
count:=jsonb_array_length(m->'viewpoints'); first:=m->'viewpoints'->0;
|
||
if m is null or count<5 or count>24 then raise exception 'Map requires 5–24 validated panoramas'; end if;
|
||
insert into public.revisions(dataset_id,manifest,asset_root) values(p_dataset,m,p_dataset::text) returning id into rev;
|
||
update public.datasets set state='published',revision_id=rev,update_state=null,name=m->>'name',description=m->>'description',panorama_count=count,
|
||
map_path=coalesce(m->>'mapPath',d.map_path),
|
||
cover_path=coalesce(first->>'assetRoot',id::text)||'/'||coalesce(first->>'assetId',first->>'id')||'/preview.webp',rejection_reason=null,updated_at=now() where id=p_dataset;
|
||
elsif p_decision='reject' and (d.state='review' or (d.state='published' and d.update_state='review')) then
|
||
if coalesce(length(trim(p_reason)),0)=0 then raise exception 'Provide a rejection reason'; end if;
|
||
update public.datasets set state=case when state='published' then state else 'rejected' end,update_state=case when state='published' then 'rejected' end,rejection_reason=p_reason,updated_at=now() where id=p_dataset;
|
||
elsif p_decision='unpublish' and d.state='published' then
|
||
update public.datasets set state='unpublished',update_state=null,updated_at=now() where id=p_dataset;
|
||
else raise exception 'Invalid review transition'; end if;
|
||
insert into public.review_decisions(dataset_id,reviewer_id,decision,reason) values(p_dataset,p_reviewer,p_decision,p_reason);
|
||
end; $$; |