188 lines
14 KiB
PL/PgSQL
188 lines
14 KiB
PL/PgSQL
-- Per-map connection address; NULL explicitly disables the capture server check.
|
||
alter table public.datasets add column contribution_server_address text;
|
||
|
||
create function public.normalize_server_address(p_address text) returns text
|
||
language plpgsql immutable security invoker set search_path='' as $$
|
||
declare address text:=lower(btrim(p_address)); parts text[]; hostname text; label text; port integer;
|
||
begin
|
||
if address is null or address='' then return null; end if;
|
||
if length(address)>255 then raise exception 'Server address is too long'; end if;
|
||
parts:=regexp_match(address,'^(\[[0-9a-f:]+\]|[a-z0-9.-]+)(:([0-9]{1,5}))?$');
|
||
if parts is null then raise exception 'Use a Minecraft hostname or IP, with an optional port'; end if;
|
||
hostname:=parts[1]; port:=coalesce(parts[3]::integer,25565);
|
||
if left(hostname,1)='[' then
|
||
if position(':' in hostname)=0 then raise exception 'Invalid IPv6 address'; end if;
|
||
perform trim(both '[]' from hostname)::inet;
|
||
else
|
||
hostname:=regexp_replace(hostname,'\.$','');
|
||
if length(hostname)>253 then raise exception 'Invalid server hostname'; end if;
|
||
foreach label in array string_to_array(hostname,'.') loop
|
||
if label !~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?$' then raise exception 'Invalid server hostname'; end if;
|
||
end loop;
|
||
end if;
|
||
if port<1 or port>65535 then raise exception 'Server port must be between 1 and 65535'; end if;
|
||
return hostname||case when port=25565 then '' else ':'||port::text end;
|
||
end; $$;
|
||
|
||
create function public.assert_capture_server(p_expected text,p_actual text) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare expected text:=public.normalize_server_address(p_expected); actual text;
|
||
begin
|
||
if expected is null then return; end if;
|
||
actual:=public.normalize_server_address(p_actual);
|
||
if actual is distinct from expected then
|
||
raise exception 'Wrong capture server: this map requires %, but the export records %. Capture on the correct server, or ask the owner to correct or clear the server address in map settings.', expected,coalesce(actual,'no server address');
|
||
end if;
|
||
end; $$;
|
||
|
||
-- Use each map's own captured address, never another map sharing its server name.
|
||
do $$ declare d record; begin
|
||
for d in select dataset_id,manifest->>'serverAddress' as address from public.dataset_inputs where manifest ? 'serverAddress' loop
|
||
begin
|
||
update public.datasets set contribution_server_address=public.normalize_server_address(d.address) where id=d.dataset_id and parent_dataset_id is null;
|
||
exception when others then null; -- Legacy free-text addresses require owner correction.
|
||
end;
|
||
end loop;
|
||
end; $$;
|
||
|
||
create function public.set_contribution_server(p_dataset uuid,p_owner uuid,p_address text) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
begin
|
||
perform 1 from public.datasets where id=p_dataset and owner_id=p_owner and parent_dataset_id is null for update;
|
||
if not found then raise exception 'Map owner permission required'; end if;
|
||
update public.datasets set contribution_server_address=public.normalize_server_address(p_address),updated_at=now() where id=p_dataset;
|
||
end; $$;
|
||
|
||
-- Available during resumable retries without reserving more panorama slots.
|
||
create function public.contribution_server(p_dataset uuid,p_owner uuid) returns jsonb
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and parent_dataset_id is null;
|
||
if not found then raise exception 'Map not found'; end if;
|
||
if d.owner_id<>p_owner and not d.allow_contributions then raise exception 'The owner has disabled player contributions'; end if;
|
||
return jsonb_build_object('serverAddress',d.contribution_server_address);
|
||
end; $$;
|
||
|
||
-- Used by signing and finalizing retries as well as new uploads.
|
||
create function public.check_contribution_server(p_dataset uuid,p_owner uuid) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare target uuid; expected text; manifest jsonb;
|
||
begin
|
||
select parent_dataset_id into target from public.datasets where id=p_dataset and owner_id=p_owner;
|
||
if not found then raise exception 'Dataset not found'; end if;
|
||
if target is null then return; end if;
|
||
select contribution_server_address into expected from public.datasets where id=target for update;
|
||
select i.manifest into manifest from public.dataset_inputs i where dataset_id=p_dataset;
|
||
perform public.assert_capture_server(expected,manifest->>'serverAddress');
|
||
end; $$;
|
||
|
||
create or replace function public.contribution_capacity(p_dataset uuid,p_owner uuid) returns jsonb
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; m jsonb; reserved integer; used integer; pool integer;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset for update;
|
||
if not found or d.parent_dataset_id is not null or d.state not in ('draft','rejected','published') then raise exception 'Map does not accept contributions at this time'; end if;
|
||
if d.owner_id<>p_owner and not d.allow_contributions then raise exception 'The owner has disabled player contributions'; end if;
|
||
select manifest into m from public.dataset_inputs where dataset_id=p_dataset;
|
||
pool:=jsonb_array_length(coalesce(m->'availableViewpoints',m->'viewpoints'));
|
||
select coalesce(sum(contribution_count) filter(where contribution_status in ('pending','submitted') and state<>'failed'),0),
|
||
coalesce(sum(contribution_count) filter(where owner_id=p_owner and (contribution_status='accepted' or (contribution_status in ('pending','submitted') and state<>'failed'))),0)
|
||
into reserved,used from public.datasets where parent_dataset_id=p_dataset;
|
||
return jsonb_build_object('serverAddress',d.contribution_server_address,'id',d.id,'name',d.name,'bounds',d.bounds,'remaining',greatest(0,24-pool-reserved),
|
||
'playerRemaining',greatest(0,case when d.owner_id=p_owner then 24 else 12 end-used),'isOwner',d.owner_id=p_owner);
|
||
end; $$;
|
||
|
||
|
||
create or replace function public.create_upload(p_owner uuid,p_manifest jsonb) returns uuid
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare target uuid; capacity jsonb; server uuid; result uuid; count integer; b jsonb;
|
||
begin
|
||
target:=(p_manifest->>'contributionTo')::uuid;
|
||
count:=jsonb_array_length(p_manifest->'viewpoints');
|
||
if count<1 or count>24 or (target is null and count<5) then raise exception 'Maps require 5–24 panoramas'; end if;
|
||
if exists(select 1 from jsonb_array_elements(p_manifest->'viewpoints') p where p ?| array['assetRoot','assetId','contributorId','source']) or p_manifest ? 'availableViewpoints' then raise exception 'Upload contains server-only media provenance'; end if;
|
||
if target is not null then
|
||
capacity:=public.contribution_capacity(target,p_owner); b:=capacity->'bounds';
|
||
perform public.assert_capture_server(capacity->>'serverAddress',p_manifest->>'serverAddress');
|
||
if count>(capacity->>'remaining')::integer then raise exception 'Map limit is 24 panoramas, including pending uploads'; end if;
|
||
if count>(capacity->>'playerRemaining')::integer then raise exception 'Each player may contribute at most 12 panoramas per map'; end if;
|
||
if exists(select 1 from jsonb_array_elements(p_manifest->'viewpoints') p where
|
||
(p->>'x')::numeric<(b->>'minX')::numeric or (p->>'x')::numeric>(b->>'maxX')::numeric or
|
||
(p->>'z')::numeric<(b->>'minZ')::numeric or (p->>'z')::numeric>(b->>'maxZ')::numeric) then raise exception 'Contributed panoramas must be inside the original map bounds'; end if;
|
||
select server_id into server from public.datasets where id=target;
|
||
else
|
||
select id into server from public.catalog_servers where name=p_manifest->>'serverName' limit 1;
|
||
if server is null then insert into public.catalog_servers(name,address) values(p_manifest->>'serverName',p_manifest->>'serverAddress') returning id into server; end if;
|
||
end if;
|
||
insert into public.datasets(owner_id,server_id,name,description,source,bounds,parent_dataset_id,contribution_status,contribution_count,contribution_server_address)
|
||
values(p_owner,server,p_manifest->>'name',coalesce(p_manifest->>'description',''),p_manifest->>'source',p_manifest->'bounds',target,
|
||
case when target is not null then 'pending' end,case when target is not null then count else 0 end,case when target is null then public.normalize_server_address(p_manifest->>'serverAddress') end) returning id into result;
|
||
insert into public.dataset_inputs(dataset_id,manifest) values(result,p_manifest);
|
||
return result;
|
||
end; $$;
|
||
|
||
|
||
create or replace function public.review_contribution(p_dataset uuid,p_contribution uuid,p_owner uuid,p_decision text,p_reason text default null) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; c public.datasets; m jsonb; cm jsonb; added jsonb; pool jsonb; chosen jsonb;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner and parent_dataset_id is null 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' then raise exception 'Wait until the current map review finishes'; end if;
|
||
select * into c from public.datasets where id=p_contribution and parent_dataset_id=p_dataset for update;
|
||
if not found or c.contribution_status<>'submitted' or c.state<>'review' then raise exception 'Contribution is not awaiting owner approval'; end if;
|
||
if p_decision='reject' then
|
||
if coalesce(length(trim(p_reason)),0)=0 then raise exception 'Provide feedback for the contributor'; end if;
|
||
update public.datasets set state='rejected',contribution_status='rejected',rejection_reason=p_reason,updated_at=now() where id=c.id;
|
||
return;
|
||
end if;
|
||
if p_decision<>'accept' then raise exception 'Invalid contribution decision'; end if;
|
||
select manifest into cm from public.dataset_inputs where dataset_id=c.id and validated;
|
||
select manifest into m from public.dataset_inputs where dataset_id=d.id and validated;
|
||
if cm is null or m is null then raise exception 'Dataset not validated'; end if;
|
||
perform public.assert_capture_server(d.contribution_server_address,cm->>'serverAddress');
|
||
select jsonb_agg(p||jsonb_build_object('id','c'||replace(c.id::text,'-','')||'_'||n,'assetRoot',c.id,'assetId',p->>'id','contributorId',c.owner_id,'source',c.source) order by n)
|
||
into added from jsonb_array_elements(cm->'viewpoints') with ordinality as pts(p,n);
|
||
pool:=coalesce(m->'availableViewpoints',m->'viewpoints')||added;
|
||
if jsonb_array_length(pool)>24 then raise exception 'Map limit is 24 panoramas'; end if;
|
||
if c.owner_id<>d.owner_id and (select coalesce(sum(contribution_count),0) from public.datasets where parent_dataset_id=d.id and owner_id=c.owner_id and contribution_status='accepted')+jsonb_array_length(added)>12 then raise exception 'Each player may contribute at most 12 panoramas per map'; end if;
|
||
chosen:=(m->'viewpoints')||added;
|
||
update public.dataset_inputs set manifest=m||jsonb_build_object('availableViewpoints',pool,'viewpoints',chosen) where dataset_id=d.id;
|
||
update public.datasets set state=case when state='published' then state else 'draft' end,
|
||
update_state=case when state='published' then 'draft' end,
|
||
panorama_count=case when state='published' then panorama_count else jsonb_array_length(chosen) end,rejection_reason=null,updated_at=now() where id=d.id;
|
||
update public.datasets set contribution_status='accepted',contribution_count=jsonb_array_length(added),updated_at=now() where id=c.id;
|
||
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
|
||
perform public.check_contribution_server(p_dataset,p_owner);
|
||
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;
|
||
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.finalize_upload(p_dataset uuid,p_owner uuid) returns text language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; begin
|
||
perform public.check_contribution_server(p_dataset,p_owner);
|
||
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 in ('processing','draft','review','published') then return d.state; end if;
|
||
if d.state<>'uploading' then raise exception 'Dataset cannot be finalized'; end if;
|
||
insert into public.upload_jobs(dataset_id) values(p_dataset) on conflict(dataset_id) do nothing;
|
||
update public.datasets set state='processing',updated_at=now() where id=p_dataset;
|
||
return 'processing'; end; $$;
|
||
|
||
revoke execute on function public.normalize_server_address(text),public.assert_capture_server(text,text),public.set_contribution_server(uuid,uuid,text),public.contribution_server(uuid,uuid),public.check_contribution_server(uuid,uuid) from public,anon,authenticated;
|
||
grant execute on function public.normalize_server_address(text),public.assert_capture_server(text,text),public.set_contribution_server(uuid,uuid,text),public.contribution_server(uuid,uuid),public.check_contribution_server(uuid,uuid) to service_role;
|