Forum OpenACS Q&A: Response to Breakthrough on bookmarks module

Collapse
Posted by Bob Fuller on

The revised data model, bookmarks.sql

To the best of my knowledge, then, here's what's required in the file for the data model (bookmarks.sql):
-- bookmarks.sql

create sequence bm_url_id_seq;

create table bm_urls (
    url_id                  integer primary key,
    -- url title may be null in the case of bookmarks
    -- that are merely icons ie. AIM
    url_title               varchar(500),
    -- host url is separated from complete_url for counting purposes
    host_url                varchar(100) not null,
    complete_url            varchar(500) not null,
    -- meta tags that could be looked up regularly
    meta_keywords           varchar(4000),
    meta_description        varchar(4000),
    last_checked_date       datetime,
    -- the last time the site returned a "live" status
    last_live_date          datetime
);

-- this insertion is required for the change in create-folder-2.tcl
-- (and possibly elsewhere):
insert into bm_urls (url_id, host_url, complete_url)
  values (0, ' ', ' ');
-- (folders in bm_list are joined to a "dummy" entry in bm_urls, as above)

create sequence bm_bookmark_id_seq;
create table bm_list (
    bookmark_id             integer primary key,
    -- sort keys contains 3 characters per level of depth, from
    -- 0-9, then A-Z, a-z. You can get the depth as
    -- length(parent_sort_key) / 3.
    -- the full sort key for any bookmark is
    -- parent_sort_key || local_sort_key
    parent_sort_key         varchar(99), -- parent's sort key
    local_sort_key          char(3) not null,
    owner_id                integer not null references users(user_id),
    creation_date           datetime not null,
    modification_date       datetime,
    -- url_id may be null if the bookmark is a folder
    url_id                  integer references bm_urls,
    -- a person may rename any of his bookmarks so we keep a local title
    local_title                     varchar(500),
    private_p               char(1) default 'f' check (private_p in ('t','f')),
    -- needed in addition to private_p for the case where a public bookmark
    -- is under a hidden folder
    hidden_p                char(1) default 'f' check (hidden_p in ('t','f')),
    -- this is 't' if the bookmark is a folder
    folder_p                char(1) default 'f' check (folder_p in ('t','f')),
    -- null parent_id indicates this is a top level folder/bookmark
    parent_id               integer references bm_list(bookmark_id),
    -- refers to whether a folder is open or closed
    closed_p                char(1) default 't' check (closed_p in ('t','f')),
    -- whether the bookmark is within a closed folder and therefore not shown
    in_closed_p             char(1) default 'f' check (in_closed_p in ('t','f'))
);

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 || cast(local_sort_key as varchar(3))
        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();

create table bm_list_pkg (v_updated_ids integer, v_num_entries integer);
insert into bm_list_pkg (v_num_entries) values (0);

create function trig_bm_list_sort_key_row_u_tr() returns opaque
as '
DECLARE
  old_parent_id integer;
  new_parent_id integer;
  f_bookmark_id integer;
  f_num_entries integer;
BEGIN
  old_parent_id := OLD.parent_id;
  new_parent_id := NEW.parent_id;
  IF old_parent_id is null THEN
    old_parent_id := 0;
  END IF;
  IF new_parent_id is null THEN
    new_parent_id := 0;
  END IF;
  IF old_parent_id != new_parent_id THEN
    f_bookmark_id := NEW.bookmark_id;
    select into f_num_entries max(v_num_entries)+1 from bm_list_pkg;
    insert into bm_list_pkg (v_updated_ids, v_num_entries)
      values (f_bookmark_id, f_num_entries);
  END IF;
  return NEW;
END;
' language 'plpgsql';

CREATE trigger bm_list_sort_key_row_u_tr
  before UPDATE ON bm_list
  FOR each row
execute procedure trig_bm_list_sort_key_row_u_tr();

-- the rest of the data model (functions and triggers) has moved,
-- for the time being, primarily, to edit-bookmark-2.tcl!

-- need two indices to support CONNECT BY

create index bm_list_idx1 on bm_list(bookmark_id, parent_id);
create index bm_list_idx2 on bm_list(parent_id, bookmark_id);

-- and the jury's still out on these two indexes, since our data
-- model has changed substantially (and does not use CONNECT BY)!