Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql 14 Jun 2001 05:03:39 -0000 1.4 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-create.sql 5 Jul 2001 21:11:37 -0000 1.5 @@ -60,9 +60,11 @@ constraint acs_messages_rfc822_id_nn not null constraint acs_messages_rfc822_id_un - unique + unique, + tree_sortkey varchar(4000) ); +create index acs_messages_tree_skey_idx on acs_messages (tree_sortkey); create index acs_messages_reply_to_idx on acs_messages (reply_to); create index acs_messages_sender_idx on acs_messages (sender); @@ -88,6 +90,95 @@ The RFC822 message-id of this message, for sending email. '; + +-- support for tree queries on acs_messages + +create function acs_message_insert_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; +begin + if new.reply_to is null then + select max(tree_sortkey) into max_key + from acs_messages + where reply_to is null; + + v_parent_sk := ''''; + else + select max(tree_sortkey) into max_key + from acs_messages + where reply_to = new.reply_to; + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from acs_messages + where message_id = new.reply_to; + end if; + + new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key); + + return new; + +end;' language 'plpgsql'; + +create trigger acs_message_insert_tr before insert +on acs_messages +for each row +execute procedure acs_message_insert_tr (); + +create function acs_message_update_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; + v_rec record; + clr_keys_p boolean default ''t''; +begin + if new.message_id = old.message_id and + ((new.reply_to = old.reply_to) or + (new.reply_to is null and old.reply_to is null)) then + + return new; + + end if; + + for v_rec in select message_id + from acs_messages + where tree_sortkey like new.tree_sortkey || ''%'' + order by tree_sortkey + LOOP + if clr_keys_p then + update acs_messages set tree_sortkey = null + where tree_sortkey like new.tree_sortkey || ''%''; + clr_keys_p := ''f''; + end if; + + select max(tree_sortkey) into max_key + from acs_messages + where reply_to = (select reply_to + from acs_messages + where message_id = v_rec.message_id); + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from acs_messages + where message_id = (select reply_to + from acs_messages + where message_id = v_rec.message_id); + + update acs_messages + set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key) + where message_id = v_rec.message_id; + + end LOOP; + + return new; + +end;' language 'plpgsql'; + +create trigger acs_message_update_tr after update +on acs_messages +for each row +execute procedure acs_message_update_tr (); + + create table acs_messages_outgoing ( message_id integer constraint amo_message_id_fk Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-drop.sql 14 Jun 2001 05:03:39 -0000 1.2 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-drop.sql 5 Jul 2001 21:11:37 -0000 1.3 @@ -7,11 +7,16 @@ -- -- drop functions +drop trigger acs_message_insert_tr on acs_messages; +drop trigger acs_message_update_tr on acs_messages; +drop function acs_message_insert_tr(); +drop function acs_message_update_tr(); + drop function acs_message__edit (integer,varchar,varchar,varchar, - text,timestamp,integer,varchar,boolean); + text,integer,timestamp,integer,varchar,boolean); drop function acs_message__new (integer,integer,timestamp,integer, varchar,varchar,varchar,varchar,varchar,text, - integer,integer,integer,varchar,integer,boolean); + integer,integer,integer,varchar,varchar,boolean); drop function acs_message__delete (integer); drop function acs_message__message_p (integer); drop function acs_message__send (integer,varchar,integer,timestamp); Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 14 Jun 2001 05:03:39 -0000 1.3 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 5 Jul 2001 21:11:37 -0000 1.4 @@ -230,20 +230,24 @@ end;' language 'plpgsql'; --- This needs work as there is no connect by +-- Ported to take advantage of tree_sortkey column by DLP create function acs_message__first_ancestor (integer) returns integer as ' declare p_message_id alias for $1; v_message_id acs_messages.message_id%TYPE; + v_tree_sk varchar; begin + select tree_sortkey into v_tree_sk + from acs_messages + where message_id = p_message_id; + + v_tree_sk := substring(v_tree_sk from 0 for 4); + select message_id into v_message_id - from (select message_id, reply_to - from acs_messages - connect by message_id = prior reply_to - start with message_id = p_message_id) ancestors - where reply_to is null; + from acs_messages + where tree_sortkey = v_tree_sk; return v_message_id; end;' language 'plpgsql'; @@ -258,6 +262,7 @@ create function acs_message__new_file (integer,integer,varchar,varchar, text,varchar,text,timestamp,integer,varchar,boolean) returns integer as ' +declare p_message_id alias for $1; p_file_id alias for $2; -- default null p_file_name alias for $3; @@ -282,13 +287,13 @@ null, -- context_id p_creation_ip, -- creation_ip ''content_item'', -- item_subtype - ''content_revision, -- content_type + ''content_revision'', -- content_type null, -- title null, -- description ''text/plain'', -- mime_type null, -- nls_language null, -- text - ''file'', -- storage_type + ''file'' -- storage_type ); -- create an initial revision for the new attachment Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-views.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-views.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-views.sql 23 May 2001 16:38:19 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-views.sql 5 Jul 2001 21:11:37 -0000 1.2 @@ -11,13 +11,13 @@ create view acs_messages_all as select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id, - r.revision_id, r.title, r.mime_type, r.content + m.tree_sortkey, r.revision_id, r.title, r.mime_type, r.content from cr_items i, cr_revisions r, acs_messages m where i.item_id = m.message_id and r.revision_id = i.live_revision; create view acs_messages_latest as select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id, - r.revision_id, r.title, r.mime_type, r.content + m.tree_sortkey, r.revision_id, r.title, r.mime_type, r.content from cr_items i, cr_revisions r, acs_messages m where i.item_id = m.message_id and r.revision_id = content_item__get_latest_revision(i.item_id);