SHARE
TWEET

Untitled

a guest Jun 25th, 2019 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace function recalculate_duration_for(timeline_item_id integer, group_hash varchar,
  2.                                                     new_duration     int4range,
  3.                                                     old_duration     int4range default null)
  4.   returns jsonb as $$
  5. declare timeline_item          record;
  6.         shifted_timeline_items jsonb default '[]';
  7.         timeline_item_length   integer;
  8.         project                projects;
  9.         overlapped_data        record;
  10.         updated_model          timeline_items;
  11.         initital_duration      int4range;
  12.         recalculated_duration  int4range;
  13.         new_lower_bound        integer;
  14.         new_upper_bound        integer;
  15.   overlapped_data_middle     integer;
  16.   overlapped_rec_lower_bound integer;
  17.   undo_redo_data             record;
  18.   updated_durations          jsonb default '{}';
  19.   split_zone                 int4range;
  20.   split_diff                 integer;
  21. begin
  22.   if old_duration notnull and new_duration = old_duration
  23.   then
  24.     select _can_undo, _can_redo
  25.         into undo_redo_data
  26.     from get_data_for_redo_or_undo(project.id, project.locked_by_user_id);
  27.  
  28.     return jsonb_build_object('shifted_timeline_items', shifted_timeline_items,
  29.                               'can_undo', undo_redo_data._can_undo, 'can_redo', undo_redo_data._can_redo);
  30.   end if;
  31.  
  32.   select timeline_items.*,
  33.          timelines.parent_id,
  34.          timelines.initiate_event_id,
  35.          external_id,
  36.          type,
  37.          coalesce((events.metadata -> 'editMode' ->> 'split') :: boolean, false) as is_split,
  38.          events.metadata ->> 'type' = 'end_split'                                as is_end_split
  39.       into timeline_item
  40.   from timeline_items
  41.          inner join timelines on timelines.id = timeline_items.timeline_id
  42.          left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  43.   where timeline_items.id = timeline_item_id;
  44.  
  45.   if timeline_item.id isnull
  46.   then
  47.     raise exception 'Timeline item not found';
  48.   end if;
  49.  
  50.   updated_durations := updated_durations ||
  51.                        jsonb_build_object(case when timeline_item.type in ('image', 'video')
  52.                          then 'media_'
  53.                                           else 'event_'
  54.                                           end || timeline_item.timeline_id, new_duration);
  55.  
  56.   if timeline_item.is_split
  57.   then
  58.     updated_durations := updated_durations ||
  59.                          jsonb_build_object('split_' || timeline_item.external_id, new_duration);
  60.     split_diff := lower(new_duration) - lower(old_duration);
  61.   elseif timeline_item.is_end_split
  62.     then
  63.       updated_durations := updated_durations ||
  64.                            jsonb_build_object('end_split_' || timeline_item.timeline_id, new_duration) ||
  65.                            jsonb_build_object('media_' || timeline_item.parent_id, new_duration) ||
  66.                            jsonb_build_object('event_' || timeline_item.parent_id, new_duration);
  67.   end if;
  68.  
  69.   initital_duration := case when timeline_item.is_end_split then old_duration
  70.                        else new_duration end;
  71.   new_lower_bound := lower(new_duration);
  72.   new_upper_bound := upper(new_duration);
  73.   timeline_item_length := new_upper_bound - new_lower_bound;
  74.   split_zone := get_closer_split_zone(new_duration);
  75.  
  76.   select *
  77.       into project
  78.   from projects
  79.          inner join timelines on projects.id = timelines.project_id
  80.          inner join timeline_items on timelines.id = timeline_items.timeline_id
  81.                                         and timeline_items.id = timeline_item_id;
  82.  
  83.   select timeline_items.id,
  84.          type,
  85.          external_id,
  86.          parent_id,
  87.          initiate_event_id,
  88.          timeline_id,
  89.          duration,
  90.          lower(duration) as lower_bound,
  91.          upper(duration) as upper_bound
  92.       into overlapped_data
  93.   from timeline_items
  94.          inner join timelines on timeline_items.timeline_id = timelines.id
  95.   where new_duration && timeline_items.duration
  96.     and timeline_items.id != timeline_item.id
  97.     and (timeline_item.timeline_id = timeline_items.timeline_id or
  98.          (timeline_item.parent_id isnull and timelines.parent_id notnull))
  99.     and not timeline_items.deleted
  100.     and timelines.project_id = project.id
  101.     and (timeline_items.type = timeline_item.type
  102.            or case
  103.                 when timeline_item.type in ('image', 'video')
  104.                         then timeline_items.type in ('image', 'video') end)
  105.   order by duration;
  106.  
  107.   -- start set first element
  108.   if (overlapped_data.id notnull)
  109.   then
  110.     overlapped_data_middle := (overlapped_data.lower_bound + overlapped_data.upper_bound) / 2;
  111.  
  112.     if overlapped_data.timeline_id = timeline_item.timeline_id
  113.     then
  114.       if overlapped_data.duration @> (new_lower_bound + new_upper_bound) / 2
  115.       then
  116.         recalculated_duration := case
  117.                                  when new_lower_bound < overlapped_data_middle
  118.                                    then int4range(overlapped_data.lower_bound,
  119.                                                   overlapped_data.lower_bound + timeline_item_length)
  120.                                  else int4range(overlapped_data.upper_bound,
  121.                                                 overlapped_data.upper_bound + timeline_item_length)
  122.                                  end;
  123.       elseif overlapped_data.lower_bound < new_lower_bound
  124.         then
  125.           recalculated_duration := int4range(overlapped_data.upper_bound,
  126.                                              overlapped_data.upper_bound + timeline_item_length);
  127.       elseif overlapped_data.lower_bound > new_lower_bound
  128.         then
  129.           overlapped_rec_lower_bound := overlapped_data.lower_bound - timeline_item_length;
  130.           overlapped_rec_lower_bound := case
  131.                                         when overlapped_rec_lower_bound < 0
  132.                                           then 0
  133.                                         else overlapped_rec_lower_bound
  134.                                         end;
  135.           recalculated_duration := int4range(overlapped_rec_lower_bound,
  136.                                              overlapped_rec_lower_bound + timeline_item_length);
  137.       end if;
  138.     end if;
  139.   end if;
  140.  
  141.   if timeline_item.parent_id isnull and new_duration && split_zone
  142.   then
  143.     overlapped_data_middle := (lower(split_zone) + upper(split_zone)) / 2;
  144.     recalculated_duration := case
  145.                              when (new_lower_bound + new_upper_bound) / 2 < overlapped_data_middle
  146.                                then int4range(lower(split_zone) - timeline_item_length,
  147.                                               lower(split_zone))
  148.                              else int4range(upper(split_zone),
  149.                                             upper(split_zone) + timeline_item_length)
  150.                              end;
  151.   end if;
  152.  
  153.   -- process split
  154.   if timeline_item.parent_id notnull
  155.   then
  156.     with split_lower_bound as (select upper(duration)
  157.                                from timeline_items
  158.                                       inner join events on events.id = timeline_items.external_id
  159.                                where timeline_items.type = 'event'
  160.                                  and events.id = timeline_item.initiate_event_id),
  161.          split_upper_bound as (select max(upper(duration))
  162.                                from timelines
  163.                                       inner join timeline_items
  164.                                         on timelines.id = timeline_items.timeline_id and timelines.deleted is false
  165.                                       inner join events
  166.                                         on events.id = timeline_items.external_id and timeline_items.type = 'event'
  167.                                where timelines.deleted is false
  168.                                  and (timeline_items.duration > timeline_item.duration or
  169.                                       timeline_items.duration && timeline_item.duration)
  170.                                  and events.metadata ->> 'type' = 'end_split')
  171.     select int4range(split_lower_bound.upper, split_upper_bound.max)
  172.         into split_zone
  173.     from split_lower_bound,
  174.          split_upper_bound;
  175.  
  176.     if split_zone notnull and not split_zone @> new_lower_bound
  177.     then
  178.       recalculated_duration := int4range(lower(split_zone), lower(split_zone) + timeline_item_length);
  179.     end if;
  180.   end if;
  181.   -- end process split
  182.  
  183.   if recalculated_duration notnull
  184.   then
  185.     update timeline_items
  186.     set duration = recalculated_duration
  187.     where id = timeline_item.id
  188.         returning *
  189.           into updated_model;
  190.  
  191.     insert into project_histories (table_name,
  192.                                    action,
  193.                                    user_id,
  194.                                    project_id,
  195.                                    new_state,
  196.                                    old_state,
  197.                                    group_hash,
  198.                                    created_at,
  199.                                    updated_at)
  200.     values ('timeline_items',
  201.             'UPDATE',
  202.             project.locked_by_user_id,
  203.             project.id,
  204.             row_to_json(updated_model) :: jsonb,
  205.             jsonb_build_object('id', timeline_item.id, 'timeline_id', timeline_item.timeline_id,
  206.                                'type', timeline_item.type, 'external_id', timeline_item.external_id,
  207.                                'duration', timeline_item.duration, 'created_at', timeline_item.created_at,
  208.                                'updated_at', timeline_item.updated_at, 'deleted', timeline_item.deleted),
  209.             group_hash,
  210.             current_timestamp + (1 * interval '1 second'),
  211.             current_timestamp + (1 * interval '1 second'));
  212.  
  213.     updated_durations := updated_durations ||
  214.                          jsonb_build_object(case when timeline_item.type in ('image', 'video')
  215.                            then 'media_'
  216.                                             else 'event_'
  217.                                             end || timeline_item.timeline_id,
  218.                                             updated_model.duration);
  219.  
  220.     if timeline_item.is_split
  221.     then
  222.       updated_durations := updated_durations ||
  223.                            jsonb_build_object('split_' || timeline_item.external_id, updated_model.duration);
  224.       split_diff := lower(updated_model.duration) - lower(old_duration);
  225.     elseif timeline_item.is_end_split
  226.       then
  227.         updated_durations := updated_durations ||
  228.                              jsonb_build_object('end_split_' || timeline_item.timeline_id, updated_model.duration) ||
  229.                              jsonb_build_object('media_' || timeline_item.parent_id, updated_model.duration) ||
  230.                              jsonb_build_object('event_' || timeline_item.parent_id, updated_model.duration);
  231.     end if;
  232.  
  233.     new_duration := updated_model.duration;
  234.   end if;
  235.   -- end set first element
  236.  
  237.   select timeline_items.*,
  238.          convert_duration(timeline_items.duration)                                     as duration,
  239.          coalesce(row_to_json(images.*), row_to_json(videos.*), row_to_json(events.*)) as item
  240.       into timeline_item
  241.   from timeline_items
  242.          left join images on timeline_items.external_id = images.id and timeline_items.type = 'image'
  243.          left join videos on timeline_items.external_id = videos.id and timeline_items.type = 'video'
  244.          left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  245.   where timeline_items.id = timeline_item.id;
  246.  
  247.   shifted_timeline_items := shifted_timeline_items || row_to_json(timeline_item) :: jsonb;
  248.  
  249.   raise warning 'ID: %, DURATION: %, DURATIONS: %', timeline_item.id, timeline_item.duration, updated_durations;
  250.  
  251.   for timeline_item in
  252.   select timeline_items.*,
  253.          timelines.parent_id,
  254.          timelines.initiate_event_id,
  255.          external_id,
  256.          type,
  257.          (events.metadata -> 'editMode' ->> 'split') :: boolean as is_split,
  258.          events.metadata ->> 'type' = 'end_split'               as is_end_split
  259.   from timeline_items
  260.          inner join timelines on timeline_items.timeline_id = timelines.id
  261.          left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  262.   where (timeline_items.duration && (updated_durations ->>
  263.                                      (case when timelines.initiate_event_id notnull
  264.                                             then 'split_' || timelines.initiate_event_id
  265.                                            when timeline_items.type in ('video', 'image')
  266.                                             then 'media_' || timelines.id
  267.                                            else 'event_' || timelines.id end
  268.                                          ) :: varchar) :: int4range
  269.            or timeline_items.duration > initital_duration
  270.            or timeline_items.duration && new_duration
  271.            or (timeline_item.type = 'event' and timeline_item.external_id = timelines.initiate_event_id))
  272.     and timeline_items.id != timeline_item_id
  273.     and not timeline_items.deleted
  274.     and timelines.project_id = project.id
  275.   order by duration
  276.   loop
  277.  
  278.     if timeline_item.id isnull
  279.     then
  280.       exit;
  281.     end if;
  282.  
  283.     timeline_item_length := upper(timeline_item.duration) - lower(timeline_item.duration);
  284.     new_duration := (updated_durations ->> (case when timeline_item.type in ('video', 'image')
  285.       then 'media_'
  286.                                             else 'event_'
  287.                                             end || timeline_item.timeline_id) :: varchar) :: int4range;
  288.  
  289.     if timeline_item.parent_id notnull and new_duration isnull
  290.     then
  291.       new_duration := (updated_durations ->>
  292.                        ('split_' || timeline_item.initiate_event_id) :: varchar) :: int4range;
  293.     end if;
  294.     split_zone := get_closer_split_zone(new_duration);
  295.     if (split_diff < 0 and split_zone notnull) then
  296.       split_zone := int4range(lower(split_zone), upper(split_zone) + split_diff);
  297.     end if;
  298.  
  299.     raise warning '%, %', new_duration, split_zone;
  300.  
  301.     if new_duration notnull
  302.     then
  303.       update timeline_items
  304.       set duration = case
  305.                        when timeline_item.parent_id notnull and split_diff notnull
  306.                                then int4range(
  307.                                       lower(timeline_item.duration) + split_diff,
  308.                                       upper(timeline_item.duration) + split_diff)
  309.                        when upper(new_duration) + timeline_item_length <@ split_zone
  310.                             and timeline_item.parent_id isnull
  311.                             and lower(timeline_item.duration) - upper(new_duration) < 0
  312.                                then int4range(
  313.                                   upper(split_zone),
  314.                                   upper(split_zone) + timeline_item_length)
  315.                        when lower(timeline_item.duration) - upper(new_duration) < 0
  316.                                then int4range(
  317.                                       upper(new_duration),
  318.                                       upper(new_duration) + timeline_item_length)
  319.                        else timeline_item.duration
  320.           end
  321.       where id = timeline_item.id
  322.           returning *
  323.             into updated_model;
  324.  
  325.       updated_durations := updated_durations ||
  326.                            jsonb_build_object(case when timeline_item.type in ('image', 'video')
  327.                              then 'media_'
  328.                                               else 'event_'
  329.                                               end || timeline_item.timeline_id,
  330.                                               updated_model.duration);
  331.  
  332.       if timeline_item.is_split
  333.       then
  334.         updated_durations := updated_durations ||
  335.                              jsonb_build_object('split_' || timeline_item.external_id, updated_model.duration);
  336.         split_diff := lower(updated_model.duration) - lower(timeline_item.duration);
  337.       elseif timeline_item.is_end_split
  338.         then
  339.           updated_durations := updated_durations ||
  340.                                jsonb_build_object('end_split_' || timeline_item.timeline_id, updated_model.duration) ||
  341.                                jsonb_build_object('media_' || timeline_item.parent_id, updated_model.duration) ||
  342.                                jsonb_build_object('event_' || timeline_item.parent_id, updated_model.duration);
  343.       end if;
  344.  
  345.       insert into project_histories (table_name,
  346.                                      action,
  347.                                      user_id,
  348.                                      project_id,
  349.                                      new_state,
  350.                                      old_state,
  351.                                      group_hash,
  352.                                      created_at,
  353.                                      updated_at)
  354.       values ('timeline_items',
  355.               'UPDATE',
  356.               project.locked_by_user_id,
  357.               project.id,
  358.               row_to_json(updated_model) :: jsonb,
  359.               jsonb_build_object('id', timeline_item.id, 'timeline_id', timeline_item.timeline_id,
  360.                                  'type', timeline_item.type, 'external_id', timeline_item.external_id,
  361.                                  'duration', timeline_item.duration, 'created_at', timeline_item.created_at,
  362.                                  'updated_at', timeline_item.updated_at, 'deleted', timeline_item.deleted),
  363.               group_hash,
  364.               current_timestamp + (1 * interval '1 second'),
  365.               current_timestamp + (1 * interval '1 second'));
  366.     end if;
  367.  
  368.     select timeline_items.*,
  369.            convert_duration(timeline_items.duration)                                     as duration,
  370.            coalesce(row_to_json(images.*), row_to_json(videos.*), row_to_json(events.*)) as item
  371.         into timeline_item
  372.     from timeline_items
  373.            left join images on timeline_items.external_id = images.id and timeline_items.type = 'image'
  374.            left join videos on timeline_items.external_id = videos.id and timeline_items.type = 'video'
  375.            left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  376.     where timeline_items.id = timeline_item.id;
  377.  
  378.     shifted_timeline_items := shifted_timeline_items || row_to_json(timeline_item) :: jsonb;
  379.  
  380.     raise warning 'ID: %, DURATION: %, DURATIONS: %', timeline_item.id, timeline_item.duration, updated_durations;
  381.   end loop;
  382.  
  383. --   raise exception '1';
  384.  
  385.   select _can_undo, _can_redo
  386.       into undo_redo_data
  387.   from get_data_for_redo_or_undo(project.id, project.locked_by_user_id);
  388.  
  389.   return jsonb_build_object('shifted_timeline_items', shifted_timeline_items,
  390.                             'can_undo', undo_redo_data._can_undo, 'can_redo', undo_redo_data._can_redo);
  391. end;
  392. $$
  393. language plpgsql;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top