Spaces:

Bin29
/

ManimCat

Running

ManimCat / src /infrastructure /database /migrations /010_create_studio_scenes.sql

Sync from main: 5548f84 fix(workflow): preserve reference images across generation and recovery [skip ci]

b1df986 1 day ago

4.27 kB

-- Task 11A: ordered Scenes beneath a Studio session.
-- One migration unit: the table, its constraints, its indexes, and the two functions that make
-- the position invariant atomic (append) and impossible to observe half-applied (reorder).

create table if not exists studio_scenes (
  id text primary key,
  owner_id text not null,
  session_id text not null references studio_sessions(id) on delete cascade,
  position integer not null check (position >= 0),
  source_path text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint studio_scenes_session_position_key unique (session_id, position)
);

create index if not exists idx_studio_scenes_owner_session_position
  on studio_scenes(owner_id, session_id, position asc, id asc);

-- Atomic append: serialize per Session, then take the next free position in the same
-- transaction. The owner check keeps the ownership invariant inside the database.
create or replace function studio_scene_append(
  p_id text,
  p_owner_id text,
  p_session_id text,
  p_source_path text,
  p_created_at timestamptz,
  p_updated_at timestamptz
) returns setof studio_scenes
language plpgsql
as $$
declare
  v_position integer;
begin
  if not exists (
    select 1 from studio_sessions where id = p_session_id and owner_id = p_owner_id
  ) then
    raise exception 'studio_scene_append: session does not belong to owner'
      using errcode = 'P0002';
  end if;

  perform pg_advisory_xact_lock(hashtextextended(p_session_id, 0));

  select coalesce(max(position) + 1, 0) into v_position
    from studio_scenes
   where session_id = p_session_id;

  return query
    insert into studio_scenes (id, owner_id, session_id, position, source_path, created_at, updated_at)
    values (
      p_id,
      p_owner_id,
      p_session_id,
      v_position,
      p_source_path,
      coalesce(p_created_at, now()),
      coalesce(p_updated_at, now())
    )
    returning *;
end;
$$;

-- Atomic reorder: the submitted list must be exactly the persisted Scene set of that Session.
-- `unique (session_id, position)` is enforced per row, so a permutation would collide midway.
-- Every row therefore leaves the target range first, then 0..n-1 is assigned in one statement.
create or replace function studio_scene_replace_order(
  p_owner_id text,
  p_session_id text,
  p_scene_ids text[]
) returns setof studio_scenes
language plpgsql
as $$
declare
  v_persisted integer;
  v_matched integer;
  v_submitted integer;
  v_lift integer;
begin
  v_submitted := coalesce(array_length(p_scene_ids, 1), 0);
  if v_submitted = 0 then
    raise exception 'studio_scene_replace_order: the order must not be empty'
      using errcode = 'P0003';
  end if;

  perform pg_advisory_xact_lock(hashtextextended(p_session_id, 0));

  select count(*) into v_persisted
    from studio_scenes
   where owner_id = p_owner_id and session_id = p_session_id;

  select count(*) into v_matched
    from (
      select distinct ids.id
        from unnest(p_scene_ids) as ids(id)
        join studio_scenes s
          on s.id = ids.id and s.owner_id = p_owner_id and s.session_id = p_session_id
    ) as matched;

  if v_submitted <> v_persisted or v_matched <> v_persisted then
    raise exception 'studio_scene_replace_order: order does not match the persisted scene set'
      using errcode = 'P0004';
  end if;

  -- Lift every row strictly above the current maximum, so the source range and the temporary
  -- range cannot overlap even when legacy rows left gaps behind.
  select max(position) + 1 into v_lift
    from studio_scenes
   where owner_id = p_owner_id and session_id = p_session_id;

  update studio_scenes
     set position = position + v_lift,
         updated_at = now()
   where owner_id = p_owner_id and session_id = p_session_id;

  update studio_scenes as s
     set position = (ordered.ordinality - 1)::integer,
         updated_at = now()
    from unnest(p_scene_ids) with ordinality as ordered(id, ordinality)
   where s.id = ordered.id
     and s.owner_id = p_owner_id
     and s.session_id = p_session_id;

  return query
    select *
      from studio_scenes
     where owner_id = p_owner_id and session_id = p_session_id
     order by position asc, id asc;
end;
$$;

	-- Task 11A: ordered Scenes beneath a Studio session.
	-- One migration unit: the table, its constraints, its indexes, and the two functions that make
	-- the position invariant atomic (append) and impossible to observe half-applied (reorder).

	create table if not exists studio_scenes (
	id text primary key,
	owner_id text not null,
	session_id text not null references studio_sessions(id) on delete cascade,
	position integer not null check (position >= 0),
	source_path text not null,
	created_at timestamptz not null default now(),
	updated_at timestamptz not null default now(),
	constraint studio_scenes_session_position_key unique (session_id, position)
	);

	create index if not exists idx_studio_scenes_owner_session_position
	on studio_scenes(owner_id, session_id, position asc, id asc);

	-- Atomic append: serialize per Session, then take the next free position in the same
	-- transaction. The owner check keeps the ownership invariant inside the database.
	create or replace function studio_scene_append(
	p_id text,
	p_owner_id text,
	p_session_id text,
	p_source_path text,
	p_created_at timestamptz,
	p_updated_at timestamptz
	) returns setof studio_scenes
	language plpgsql
	as $$
	declare
	v_position integer;
	begin
	if not exists (
	select 1 from studio_sessions where id = p_session_id and owner_id = p_owner_id
	) then
	raise exception 'studio_scene_append: session does not belong to owner'
	using errcode = 'P0002';
	end if;

	perform pg_advisory_xact_lock(hashtextextended(p_session_id, 0));

	select coalesce(max(position) + 1, 0) into v_position
	from studio_scenes
	where session_id = p_session_id;

	return query
	insert into studio_scenes (id, owner_id, session_id, position, source_path, created_at, updated_at)
	values (
	p_id,
	p_owner_id,
	p_session_id,
	v_position,
	p_source_path,
	coalesce(p_created_at, now()),
	coalesce(p_updated_at, now())
	)
	returning *;
	end;
	$$;

	-- Atomic reorder: the submitted list must be exactly the persisted Scene set of that Session.
	-- `unique (session_id, position)` is enforced per row, so a permutation would collide midway.
	-- Every row therefore leaves the target range first, then 0..n-1 is assigned in one statement.
	create or replace function studio_scene_replace_order(
	p_owner_id text,
	p_session_id text,
	p_scene_ids text[]
	) returns setof studio_scenes
	language plpgsql
	as $$
	declare
	v_persisted integer;
	v_matched integer;
	v_submitted integer;
	v_lift integer;
	begin
	v_submitted := coalesce(array_length(p_scene_ids, 1), 0);
	if v_submitted = 0 then
	raise exception 'studio_scene_replace_order: the order must not be empty'
	using errcode = 'P0003';
	end if;

	perform pg_advisory_xact_lock(hashtextextended(p_session_id, 0));

	select count(*) into v_persisted
	from studio_scenes
	where owner_id = p_owner_id and session_id = p_session_id;

	select count(*) into v_matched
	from (
	select distinct ids.id
	from unnest(p_scene_ids) as ids(id)
	join studio_scenes s
	on s.id = ids.id and s.owner_id = p_owner_id and s.session_id = p_session_id
	) as matched;

	if v_submitted <> v_persisted or v_matched <> v_persisted then
	raise exception 'studio_scene_replace_order: order does not match the persisted scene set'
	using errcode = 'P0004';
	end if;

	-- Lift every row strictly above the current maximum, so the source range and the temporary
	-- range cannot overlap even when legacy rows left gaps behind.
	select max(position) + 1 into v_lift
	from studio_scenes
	where owner_id = p_owner_id and session_id = p_session_id;

	update studio_scenes
	set position = position + v_lift,
	updated_at = now()
	where owner_id = p_owner_id and session_id = p_session_id;

	update studio_scenes as s
	set position = (ordered.ordinality - 1)::integer,
	updated_at = now()
	from unnest(p_scene_ids) with ordinality as ordered(id, ordinality)
	where s.id = ordered.id
	and s.owner_id = p_owner_id
	and s.session_id = p_session_id;

	return query
	select *
	from studio_scenes
	where owner_id = p_owner_id and session_id = p_session_id
	order by position asc, id asc;
	end;
	$$;