Forum OpenACS Q&A: Response to Breakthrough on bookmarks module
Posted by
Bob Fuller
on 01/09/01 09:18 PM
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)!
