Forum OpenACS Q&A: Bookmarks Module

CREATE function inc_char_for_sort_key (char)
  returns char(2)
AS '
DECLARE
   old_char char(2);
   carry_p  integer;
   old_code INTEGER;
   new_code INTEGER;
BEGIN
   old_char := $1;
   old_code := ascii(old_char);
   IF old_code = 57 THEN
      -- skip from 9 to A
      new_code := 65;
      carry_p := 0;
   ELSE IF old_code = 90 THEN
      -- skip from Z to a
      new_code := 97;
      carry_p := 0;
   ELSE IF old_code = 122 THEN
      -- wrap around
      new_code := 48;
      carry_p := 1;
   ELSE
      new_code := old_code + 1;
      carry_p := 0;
   END IF;
   END IF;
   END IF;
   --old_char := chr(new_code);
   old_char := ichar(new_code) || cast(carry_p as char(1));
   return old_char;
END;
--END inc_char_for_sort_key;
' language 'plpgsql';


CREATE FUNCTION new_sort_key (CHAR(3)) RETURNS CHAR(3)
AS '
DECLARE
   v_old_sort_key char(3);
   v_chr_1 char(2);
   v_chr_2 char(2);
   v_chr_3 char(2);
   v_carry INTEGER;
BEGIN
   v_old_sort_key := $1;
   IF v_old_sort_key IS null or v_old_sort_key = '''' THEN
      RETURN ''000'';
   END IF;

   v_chr_1 := substr(v_old_sort_key, 1, 1);
   v_chr_2 := substr(v_old_sort_key, 2, 1);
   v_chr_3 := substr(v_old_sort_key, 3, 1);

   v_chr_3 := inc_char_for_sort_key(v_chr_3);
   v_carry := cast(substr(v_chr_3, 2, 1) as integer);
   v_chr_3 := substr(v_chr_3, 1, 1);
   IF v_carry = 1 THEN
      v_chr_2 := inc_char_for_sort_key(v_chr_2);
      v_carry := cast(substr(v_chr_2, 2, 1) as integer);
      v_chr_2 := substr(v_chr_2, 1, 1);
      IF v_carry = 1 THEN
         v_chr_1 := inc_char_for_sort_key(v_chr_1);
         v_carry := cast(substr(v_chr_1, 2, 1) as integer);
         v_chr_1 := substr(v_chr_1, 1, 1);
      END IF;
   END IF;

   RETURN trim(v_chr_1) || trim(v_chr_2) || trim(v_chr_3);
END;
--END new_sort_key;
' language 'plpgsql';
 

create function trig_bm_list_sort_key_i_tr() returns opaque
as '
DECLARE
  v_last_sort_key bm_list.local_sort_key%TYPE;
  v_parent_sort_key bm_list.parent_sort_key%TYPE;
BEGIN
   IF NEW.parent_id IS NULL THEN
      SELECT max(local_sort_key) INTO v_last_sort_key
        FROM bm_list
        WHERE parent_id IS NULL;
      v_parent_sort_key := null;
   ELSE
      SELECT max(local_sort_key) INTO v_last_sort_key
        FROM bm_list
        WHERE parent_id = NEW.parent_id;
      SELECT parent_sort_key || local_sort_key INTO v_parent_sort_key
        FROM bm_list WHERE bookmark_id = NEW.parent_id;
   END IF;

   NEW.local_sort_key := new_sort_key(v_last_sort_key);
   NEW.parent_sort_key := v_parent_sort_key;
    RETURN NEW;
END;
' language 'plpgsql';
 
CREATE trigger bm_list_sort_key_i_tr
before INSERT ON bm_list
FOR each row
execute procedure trig_bm_list_sort_key_i_tr();

I've also started on the rest of the (SQL) code, but last night ran into a problem because of the lack of cursors in pgplsql. Interestingly enough, I actually found a cursor in one of the other modules, but I'll bet you it doesn't work...

Moving forward in the SQL part of the module, I think bm_list_pkg has to be done as a table, and I've figured out how that should interface with the update functions and triggers that are used to update parent_row_key and local_row_key when the parent_id of a bookmark is changed. However, without cursors available, the implementation of the remaining items is a bit of a challenge, since the existing Oracle stuff is recursive.

I also did a preliminary check on the Tcl side of the equation, and there are definitely at least a few OUTER JOIN / UNION type things that need to be fixed.

I'll send a further update once I get past the hurdle of not having cursors...

-- Bob