1: procedure wp_revert_to_checkpoint
2: (v_presentation_id IN wp_presentations.presentation_id%TYPE,
3: v_checkpoint IN wp_checkpoints.checkpoint%TYPE)
4: is
5: duplicate_sort_keys integer;
6: begin
7: -- Fix old versions of slides. If min_checkpoint <= v_checkpoint < max_checkpoint,
8: -- the slide is now the most recent.
9: update wp_slides
10: set max_checkpoint = null
11: where presentation_id = v_presentation_id
12: and wp_between_checkpoints_p(v_checkpoint, min_checkpoint, max_checkpoint) = 't';
13: -- Restore sort_keys from wp_historical sort.
14: update wp_slides s
15: set sort_key = (select sort_key
16: from wp_historical_sort h
17: where h.slide_id = s.slide_id
18: and h.checkpoint = v_checkpoint)
19: where presentation_id = v_presentation_id
20: and max_checkpoint is null
21: and min_checkpoint <= v_checkpoint;
22: -- Delete wp_historical_sort info for the current checkpoint.
23: delete from wp_historical_sort
24: where presentation_id = v_presentation_id
25: and checkpoint = v_checkpoint;
26: -- Delete hosed slides.
27: delete from wp_slides
28: where presentation_id = v_presentation_id
29: and min_checkpoint > v_checkpoint;
30: -- Delete recent checkpoints. "on delete cascade" causes appropriate rows
31: -- in wp_historical_sort to be hosed. Gotta love cascading deletes!
32: delete from wp_checkpoints
33: where presentation_id = v_presentation_id
34: and checkpoint > v_checkpoint;
35: -- A little sanity checking: make sure sort_keys are unique in the most recent
36: -- version now. Use a self-join.
37: select count(*) into duplicate_sort_keys
38: from wp_slides s1, wp_slides s2
39: where s1.presentation_id = v_presentation_id
40: and s2.presentation_id = v_presentation_id
41: and s1.max_checkpoint is null
42: and s2.max_checkpoint is null
43: and s1.sort_key = s2.sort_key
44: and s1.slide_id <> s2.slide_id;
45: if duplicate_sort_keys <> 0 then
46: raise_application_error(-20000, 'Duplicate sort_keys');
47: end if;
48: end;
|