ACS3 Data Model

     1: PROCEDURE bm_fixup_sort_key(v_bookmark_id IN INTEGER)
     2: IS
     3:    v_row 		bm_list%ROWTYPE;
     4:    v_last_sort_key 	bm_list.local_sort_key%TYPE;
     5:    v_parent_sort_key 	bm_list.parent_sort_key%TYPE;
     6:    cursor child_cursor(v_parent_id integer) IS
     7:      SELECT bookmark_id FROM bm_list WHERE parent_id = v_parent_id;
     8: BEGIN
     9:    SELECT * INTO v_row FROM bm_list WHERE bookmark_id = v_bookmark_id;
    10:    IF v_row.parent_id IS NULL THEN
    11:       -- Handle top-level changes
    12:       SELECT max(local_sort_key) INTO v_last_sort_key
    13: 	FROM bm_list
    14: 	WHERE parent_id IS NULL;
    15:       UPDATE bm_list SET parent_sort_key = NULL, local_sort_key = new_sort_key(v_last_sort_key) WHERE bookmark_id = v_bookmark_id;
    16:    ELSE
    17:       -- we're in a subfolder
    18:       SELECT max(local_sort_key) INTO v_last_sort_key
    19: 	FROM bm_list
    20: 	WHERE parent_id = v_row.parent_id;
    21:       SELECT parent_sort_key || local_sort_key INTO v_parent_sort_key FROM bm_list WHERE bookmark_id = v_row.parent_id;
    22:       UPDATE bm_list SET parent_sort_key = v_parent_sort_key, local_sort_key = new_sort_key(v_last_sort_key) WHERE bookmark_id = v_bookmark_id;
    23:    END IF;
    24: 
    25:    -- Recursively run on children if this is a folder.
    26:    IF v_row.folder_p = 't' THEN
    27:       FOR child_row IN child_cursor(v_bookmark_id) LOOP
    28: 	 bm_fixup_sort_key(child_row.bookmark_id);
    29:       END LOOP;
    30:    END IF;
    31: END bm_fixup_sort_key;