-- bookmarks.sql
--
-- created June 1999 and modified July 1999
-- by aure@arsdigita.com and dh@arsdigita.com 

-- ported to openacs by sokolof@rpi.edu


create sequence bm_url_id_seq;
-- since many people will be bookmarking the same sites, we keep urls in a separate table

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 timestamp,
	-- the last time the site returned a "live" status
	last_live_date timestamp
);

create sequence bm_bookmark_id_seq;
-- this table contains both bookmarks and folders


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 timestamp not null,
	modification_date timestamp,
	-- 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')) 
);

-- a hack to supply Oracle chr()functionality in pg7.0.2
-- will need to be changed because postgres programmers
-- intend to have chr in future releases

create function chr(int4)
returns char
as '
begin
   return ichar($1);
end;'
language 'plpgsql';


-- Increments char from 0-9 A-Z.
-- acs_classic incremented from a-z after A-Z
-- but this doesn't seem to sort properly in
-- postgres

CREATE function inc_char_for_sort_key (char)
RETURNS CHAR
AS 'DECLARE
   old_code INTEGER;
   new_code INTEGER;
BEGIN
   old_code := ascii($1);
   IF old_code = 57 THEN
      -- skip from 9 to A
      new_code := 65;
   ELSE
     IF old_code = 90 THEN
       -- wrap around
       new_code := 48;
     ELSE
       new_code := old_code + 1;
     END IF;
  END IF;
  RETURN chr(new_code);
END;'
LANGUAGE 'plpgsql';


-- Sets carry_p to true if incrementing
-- from Z to 0.

CREATE function get_carry_for_sort_key (char)
RETURNS CHAR
AS 'DECLARE
   old_code INTEGER;
--   carry_p INTEGER;
BEGIN
  old_code := ascii($1);
  IF old_code = 90 THEN
   -- wrap around
      RETURN ''t'';
  END IF;
    RETURN ''f'';
END;'
LANGUAGE 'plpgsql';


-- Takes a local sort key and increments it by one.

CREATE FUNCTION new_sort_key (varchar) 
RETURNS VARCHAR
AS 'DECLARE
   v_chr_1 char;
   v_chr_2 char;
   v_chr_3 char;
   v_carry char;
   foo char;
BEGIN

   IF $1 IS null THEN
      RETURN ''000'';
   END IF;
   
   v_chr_1 := substr($1, 1, 1);
   v_chr_2 := substr($1, 2, 1);
   v_chr_3 := substr($1, 3, 1);

   SELECT get_carry_for_sort_key(v_chr_3) INTO v_carry;
   SELECT inc_char_for_sort_key(v_chr_3) INTO v_chr_3;
   IF v_carry = ''t'' THEN
      SELECT get_carry_for_sort_key(v_chr_2) INTO v_carry;
      SELECT inc_char_for_sort_key(v_chr_2) INTO v_chr_2;
      IF v_carry = ''t'' THEN
        SELECT inc_char_for_sort_key(v_chr_1) INTO v_chr_1;
      END IF;
   END IF;
   RETURN v_chr_1 || v_chr_2 || v_chr_3;
END;'
LANGUAGE 'plpgsql';

-- calculates a new sort key for inserts

CREATE function bm_list_sort_key_i() returns opaque as '
DECLARE
  v_last_sort_key bm_list.local_sort_key%TYPE;
  v_parent_sort_key bm_list.parent_sort_key%TYPE;
  parent_rec record;
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;

      -- postgres cannot concatenate a null value to a non null value
      SELECT INTO parent_rec * FROM bm_list WHERE bookmark_id = NEW.parent_id;
      IF parent_rec.parent_sort_key IS NULL THEN
         v_parent_sort_key := parent_rec.local_sort_key;
      ELSE
        v_parent_sort_key := parent_rec.parent_sort_key || cast(parent_rec.local_sort_key as varchar(3));
      END IF;
   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_insert_tr
before INSERT ON bm_list
for each row
execute procedure bm_list_sort_key_i(); 


-- Fixes up parent_sort_key and local_sort_key for a bookmark.
-- If the bookmark was a folder, recursively updates its children.

CREATE FUNCTION bm_fixup_sort_key(INTEGER) 
RETURNS INTEGER AS 'DECLARE
   v_last_sort_key bm_list.local_sort_key%TYPE;
   v_parent_sort_key bm_list.parent_sort_key%TYPE;
   v_new_sort_key bm_list.local_sort_key%TYPE;
   bm_list_rec record;
   bm_list_parent_rec record;
   child_rec record;
   placeholder integer;
BEGIN
   SELECT INTO bm_list_rec * FROM bm_list WHERE bookmark_id = $1;
   IF bm_list_rec.parent_id IS NULL THEN
      -- Handle top-level changes
      SELECT max(local_sort_key) INTO v_last_sort_key
	FROM bm_list
	WHERE parent_id IS NULL;
      UPDATE bm_list SET parent_sort_key = NULL, local_sort_key = new_sort_key(v_last_sort_key) WHERE bookmark_id = $1;	 
   ELSE
      -- we are in a subfolder

      SELECT max(local_sort_key) INTO v_last_sort_key
	FROM bm_list
	WHERE parent_id = bm_list_rec.parent_id;
      SELECT into bm_list_parent_rec * FROM bm_list WHERE bookmark_id = bm_list_rec.parent_id;
      -- postgres does not concatenate nulls to strings
      -- so we have to test first
         IF bm_list_parent_rec.parent_sort_key is null THEN
   	    v_parent_sort_key := bm_list_parent_rec.local_sort_key;
	 ELSE
   	    v_parent_sort_key := bm_list_parent_rec.parent_sort_key || cast(bm_list_parent_rec.local_sort_key as varchar(3));
	 END IF;
      UPDATE bm_list SET parent_sort_key = v_parent_sort_key, local_sort_key = new_sort_key(v_last_sort_key) WHERE bookmark_id = $1;
   END IF;
   
   -- Recursively run on children if this is a folder.
    IF bm_list_rec.folder_p = ''t'' THEN
      FOR child_rec in SELECT * FROM bm_list WHERE parent_id = $1 LOOP
	 placeholder := bm_fixup_sort_key(child_rec.bookmark_id);
      END LOOP;
   END IF;
   RETURN 1;
END;'
language 'plpgsql';


-- finds out if bookmark is in a folder; used for connect statements
-- tcl/bookmarks.defs

create function bm_node_is_child(integer,integer) returns char as '
declare
        start   alias for $1;
        id      alias for $2;
        pid     integer;
begin
        if id = start then return ''t'';
        end if;

        select into pid parent_id from bm_list where bookmark_id = id;

        if pid is null then
           return ''f'';
        else if id = start then 
           return ''t'';
        else 
           return bm_node_is_child(start,pid);
        end if;
        end if;
end;
' language 'plpgsql';