Index: openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 12 Jun 2002 15:24:02 -0000 1.4 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 24 Jun 2002 21:40:16 -0000 1.5 @@ -13,63 +13,61 @@ select define_function_args('forums_forum__new','forum_id,object_type;forums_forum,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); -select define_function_args('forums_forum__name','forum_id'); - -select define_function_args('forums_forum__delete','forum_id'); - --- implementation - create function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer) returns integer as ' -DECLARE - p_forum_id alias for $1; - p_object_type alias for $2; - p_name alias for $3; - p_charter alias for $4; - p_presentation_type alias for $5; - p_posting_policy alias for $6; - p_package_id alias for $7; - p_creation_date alias for $8; - p_creation_user alias for $9; - p_creation_ip alias for $10; - p_context_id alias for $11; - v_forum_id integer; -BEGIN - v_forum_id:= acs_object__new ( - p_forum_id, - p_object_type, - p_creation_date, - p_creation_user, - p_creation_ip, - p_context_id); +declare + p_forum_id alias for $1; + p_object_type alias for $2; + p_name alias for $3; + p_charter alias for $4; + p_presentation_type alias for $5; + p_posting_policy alias for $6; + p_package_id alias for $7; + p_creation_date alias for $8; + p_creation_user alias for $9; + p_creation_ip alias for $10; + p_context_id alias for $11; + v_forum_id integer; +begin + v_forum_id:= acs_object__new( + p_forum_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); - insert into forums_forums - (forum_id, name, charter, presentation_type, posting_policy, package_id) values - (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); + insert into forums_forums + (forum_id, name, charter, presentation_type, posting_policy, package_id) + values + (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); - perform acs_object__update_last_modified(p_context_id); + perform acs_object__update_last_modified(p_context_id); - return v_forum_id; -END; + return v_forum_id; +end; ' language 'plpgsql'; +select define_function_args('forums_forum__name','forum_id'); create function forums_forum__name(integer) returns varchar as ' -DECLARE - p_forum_id alias for $1; -BEGIN - return name from forums_forums where forum_id= p_forum_id; -END; +declare + p_forum_id alias for $1; +begin + return name from forums_forums where forum_id = p_forum_id; +end; ' language 'plpgsql'; +select define_function_args('forums_forum__delete','forum_id'); create function forums_forum__delete(integer) returns integer as ' -DECLARE - p_forum_id alias for $1; -BEGIN - perform acs_object__delete(p_forum_id); - return 0; -END; +declare + p_forum_id alias for $1; +begin + perform acs_object__delete(p_forum_id); + return 0; +end; ' language 'plpgsql'; Index: openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 12 Jun 2002 15:24:02 -0000 1.3 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 24 Jun 2002 21:40:16 -0000 1.4 @@ -41,6 +41,7 @@ constraint forums_mess_user_id_nn not null, posting_date timestamp + default now() constraint forum_mess_post_date_nn not null, state varchar(100) @@ -58,7 +59,7 @@ tree_sortkey varbit, max_child_sortkey varbit, constraint forums_mess_sk_forum_un - unique (tree_sortkey,forum_id) + unique (tree_sortkey, forum_id) ); create view forums_messages_approved Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 19 Jun 2002 15:11:46 -0000 1.5 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 24 Jun 2002 21:40:16 -0000 1.6 @@ -11,191 +11,208 @@ -- chunks lifted from Gilbert. Thanks Orchard Labs! -- -select define_function_args ('forums_message_new', 'message_id,object_type;forums_message,forum_id,subject,content,html_p,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); +select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,html_p,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); -select define_function_args ('forums_message__root_message_id', 'message_id'); - -select define_function_args ('forums_message__thread_open', 'message_id'); - -select define_function_args ('forums_message__thread_close', 'message_id'); - -select define_function_args ('forums_message__delete', 'message_id'); - -select define_function_args ('forums_message__delete_thread', 'message_id'); - -select define_function_args('forums_message__name','message_id'); - --- implementation - create function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamp,varchar,integer,timestamp,integer,varchar,integer) returns integer as ' -DECLARE - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_html_p alias for $6; - p_user_id alias for $7; - p_posting_date alias for $8; - p_state alias for $9; - p_parent_id alias for $10; - p_creation_date alias for $11; - p_creation_user alias for $12; - p_creation_ip alias for $13; - p_context_id alias for $14; - v_message_id integer; - v_forum_policy forums_forums.posting_policy%TYPE; - v_state forums_messages.state%TYPE; -BEGIN - v_message_id:= acs_object__new ( - p_message_id, - p_object_type, - p_creation_date, - p_creation_user, - p_creation_ip, - p_context_id); +declare + p_message_id alias for $1; + p_object_type alias for $2; + p_forum_id alias for $3; + p_subject alias for $4; + p_content alias for $5; + p_html_p alias for $6; + p_user_id alias for $7; + p_posting_date alias for $8; + p_state alias for $9; + p_parent_id alias for $10; + p_creation_date alias for $11; + p_creation_user alias for $12; + p_creation_ip alias for $13; + p_context_id alias for $14; + v_message_id integer; + v_forum_policy forums_forums.posting_policy%TYPE; + v_state forums_messages.state%TYPE; + v_posting_date forums_messages.posting_date%TYPE; +begin + v_message_id := acs_object__new( + p_message_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); - IF p_state is NULL - then - select posting_policy into v_forum_policy from forums_forums - where forum_id= p_forum_id; + if p_state is null then + select posting_policy + into v_forum_policy + from forums_forums + where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; - end if; - else - v_state := p_state; + if v_forum_policy = ''moderated'' + then v_state := ''pending''; + else v_state := ''approved''; end if; + else + v_state := p_state; + end if; - insert into forums_messages - (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state) - values - (v_message_id, p_forum_id, p_subject, p_content, p_html_p, p_user_id, p_posting_date, p_parent_id, v_state); + if p_posting_date is null then + v_posting_date = now(); + else + v_posting_date = p_posting_date; + end if; - perform acs_object__update_last_modified(p_forum_id); + insert into forums_messages + (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state) + values + (v_message_id, p_forum_id, p_subject, p_content, p_html_p, p_user_id, v_posting_date, p_parent_id, v_state); - return v_message_id; -END; + perform acs_object__update_last_modified(p_forum_id); + + return v_message_id; +end; ' language 'plpgsql'; +select define_function_args ('forums_message__root_message_id', 'message_id'); create function forums_message__root_message_id (integer) returns integer as ' -DECLARE - p_message_id alias for $1; - v_message_id forums_messages.message_id%TYPE; - v_forum_id forums_messages.forum_id%TYPE; - v_sortkey forums_messages.tree_sortkey%TYPE; -BEGIN - select forum_id, tree_sortkey into v_forum_id, v_sortkey - from forums_messages where message_id= p_message_id; +declare + p_message_id alias for $1; + v_message_id forums_messages.message_id%TYPE; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +begin + select forum_id, tree_sortkey + into v_forum_id, v_sortkey + from forums_messages + where message_id = p_message_id; - select message_id into v_message_id from forums_messages where forum_id= v_forum_id - and tree_sortkey= tree_ancestor_key(v_sortkey, 1); + select message_id + into v_message_id + from forums_messages + where forum_id = v_forum_id + and tree_sortkey = tree_ancestor_key(v_sortkey, 1); - return v_message_id; -END; + return v_message_id; +end; ' language 'plpgsql'; +select define_function_args ('forums_message__thread_open', 'message_id'); create function forums_message__thread_open (integer) returns integer as ' -DECLARE - p_message_id alias for $1; - v_forum_id forums_messages.forum_id%TYPE; - v_sortkey forums_messages.tree_sortkey%TYPE; -BEGIN - select forum_id, tree_sortkey into v_forum_id, v_sortkey - from forums_messages where message_id= p_message_id; +declare + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +begin + select forum_id, tree_sortkey + into v_forum_id, v_sortkey + from forums_messages + where message_id = p_message_id; - update forums_messages set open_p=''t'' - where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) - and forum_id = v_forum_id; + update forums_messages + set open_p = ''t'' + where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) + and forum_id = v_forum_id; - update forums_messages set open_p=''t'' - where message_id= p_message_id; + update forums_messages + set open_p = ''t'' + where message_id = p_message_id; - return 0; -END; + return 0; +end; ' language 'plpgsql'; +select define_function_args ('forums_message__thread_close', 'message_id'); create function forums_message__thread_close (integer) returns integer as ' -DECLARE - p_message_id alias for $1; - v_forum_id forums_messages.forum_id%TYPE; - v_sortkey forums_messages.tree_sortkey%TYPE; -BEGIN - select forum_id, tree_sortkey into v_forum_id, v_sortkey - from forums_messages where message_id= p_message_id; +declare + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +begin + select forum_id, tree_sortkey + into v_forum_id, v_sortkey + from forums_messages + where message_id = p_message_id; - update forums_messages set open_p=''f'' - where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) - and forum_id = v_forum_id; + update forums_messages + set open_p = ''f'' + where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) + and forum_id = v_forum_id; - update forums_messages set open_p=''f'' - where message_id= p_message_id; + update forums_messages + set open_p = ''f'' + where message_id = p_message_id; - return 0; -END; + return 0; +end; ' language 'plpgsql'; +select define_function_args ('forums_message__delete', 'message_id'); create function forums_message__delete (integer) returns integer as ' -DECLARE - p_message_id alias for $1; -BEGIN +declare + p_message_id alias for $1; +begin perform acs_object__delete(p_message_id); return 0; -END; +end; ' language 'plpgsql'; +select define_function_args ('forums_message__delete_thread', 'message_id'); create function forums_message__delete_thread (integer) returns integer as ' -DECLARE - p_message_id alias for $1; - v_forum_id forums_messages.forum_id%TYPE; - v_sortkey forums_messages.tree_sortkey%TYPE; - v_message RECORD; -BEGIN - select forum_id, tree_sortkey into v_forum_id, v_sortkey - from forums_messages where message_id= p_message_id; +declare + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; + v_message RECORD; +begin + select forum_id, tree_sortkey + into v_forum_id, v_sortkey + from forums_messages + where message_id = p_message_id; - -- if it is already deleted - if NOTFOUND - then return 0; - end if; + -- if it is already deleted + if NOTFOUND + then return 0; + end if; - -- delete all children - -- order by tree_sortkey desc to guarantee - -- that we never delete a parent before its child - -- sortkeys are beautiful - FOR v_message in - (select * from forums_messages - where forum_id = v_forum_id and - tree_sortkey between tree_left(v_sortkey) - and tree_right(v_sortkey) order by tree_sortkey desc) - LOOP - perform forums_message__delete(v_message.message_id); - END LOOP; + -- delete all children + -- order by tree_sortkey desc to guarantee + -- that we never delete a parent before its child + -- sortkeys are beautiful + for v_message in (select * + from forums_messages + where forum_id = v_forum_id + and tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) + order by tree_sortkey desc) + loop + perform forums_message__delete(v_message.message_id); + end loop; - -- delete the message itself - perform forums_message.delete(p_message_id); + -- delete the message itself + perform forums_message.delete(p_message_id); - return 0; -END; + return 0; +end; ' language 'plpgsql'; +select define_function_args('forums_message__name','message_id'); + create function forums_message__name (integer) returns varchar as ' -DECLARE - p_message_id alias for $1; -BEGIN +declare + p_message_id alias for $1; +begin return subject from forums_messages where message_id = p_message_id; -END; +end; ' language 'plpgsql'; Index: openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 12 Jun 2002 15:24:02 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 24 Jun 2002 21:40:16 -0000 1.3 @@ -16,7 +16,7 @@ create function forums_mess_insert_tr () returns opaque as ' declare - v_max_child_sortkey forums_messages.max_child_sortkey%TYPE; + v_max_child_sortkey forums_forums.max_child_sortkey%TYPE; v_parent_sortkey forums_messages.tree_sortkey%TYPE; begin if new.parent_id is null @@ -26,7 +26,7 @@ into v_max_child_sortkey from forums_forums where forum_id = new.forum_id - for update of max_child_sortkey; + for update; v_parent_sortkey = null; else @@ -36,11 +36,11 @@ into v_parent_sortkey, v_max_child_sortkey from forums_messages where message_id = new.parent_id - for update of max_child_sortkey; + for update; end if; -- increment the sortkey - v_max_child_sortkey := lpad(tree.increment_key(v_max_child_sortkey), 6, ''0''); + v_max_child_sortkey := tree_increment_key(v_max_child_sortkey); if new.parent_id is null then Index: openacs-4/packages/forums/tcl/forums-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/tcl/forums-procs.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/tcl/forums-procs.xql 22 Jun 2002 20:58:04 -0000 1.3 +++ openacs-4/packages/forums/tcl/forums-procs.xql 24 Jun 2002 21:40:16 -0000 1.4 @@ -26,9 +26,9 @@ - select * + select forums_forums.* from forums_forums - where forum_id = :forum_id + where forums_forums.forum_id = :forum_id Index: openacs-4/packages/forums/www/forum-view-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/Attic/forum-view-oracle.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/forums/www/forum-view-oracle.xql 6 Jun 2002 00:46:55 -0000 1.4 +++ openacs-4/packages/forums/www/forum-view-oracle.xql 24 Jun 2002 21:40:16 -0000 1.5 @@ -15,7 +15,7 @@ where fm1.forum_id = :forum_id and fm1.tree_sortkey between tree.left(fm.tree_sortkey) and tree.right(fm.tree_sortkey)) as n_messages, to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified, - case when last_modified > (sysdate - 1) then 't' else 'f' end as new_p + case when acs_objects.last_modified > (sysdate - 1) then 't' else 'f' end as new_p from forums_messages_approved fm, acs_objects where fm.forum_id = :forum_id @@ -38,7 +38,7 @@ where fm1.forum_id = :forum_id and fm1.tree_sortkey between tree.left(fm.tree_sortkey) and tree.right(fm.tree_sortkey)) as n_messages, to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified, - case when last_modified > (sysdate - 1) then 't' else 'f' end as new_p + case when acs_objects.last_modified > (sysdate - 1) then 't' else 'f' end as new_p from forums_messages fm, acs_objects where fm.forum_id = :forum_id Index: openacs-4/packages/forums/www/forum-view-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/Attic/forum-view-postgresql.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/www/forum-view-postgresql.xql 19 Jun 2002 15:11:46 -0000 1.3 +++ openacs-4/packages/forums/www/forum-view-postgresql.xql 24 Jun 2002 21:40:16 -0000 1.4 @@ -10,11 +10,12 @@ person__name(fm.user_id) as user_name, fm.posting_date, fm.state, - (select count(fm1.*) + (select count(*) from forums_messages_approved fm1 where fm1.forum_id = :forum_id and fm1.tree_sortkey between tree_left(fm.tree_sortkey) and tree_right(fm.tree_sortkey)) as n_messages, - to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified + to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified, + case when acs_objects.last_modified > (now() - 1) then 't' else 'f' end as new_p from forums_messages_approved fm, acs_objects where fm.forum_id = :forum_id @@ -32,11 +33,12 @@ person__name(fm.user_id) as user_name, fm.posting_date, fm.state, - (select count(fm1.*) + (select count(*) from forums_messages fm1 where fm1.forum_id = :forum_id and fm1.tree_sortkey between tree_left(fm.tree_sortkey) and tree_right(fm.tree_sortkey)) as n_messages, - to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified + to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified, + case when acs_objects.last_modified > (now() - 1) then 't' else 'f' end as new_p from forums_messages fm, acs_objects where fm.forum_id = :forum_id Index: openacs-4/packages/forums/www/forum-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/forum-view.adp,v diff -u -r1.6 -r1.7 --- openacs-4/packages/forums/www/forum-view.adp 6 Jun 2002 00:46:55 -0000 1.6 +++ openacs-4/packages/forums/www/forum-view.adp 24 Jun 2002 21:40:16 -0000 1.7 @@ -62,7 +62,7 @@ @messages.subject@ -(new!) + (new!) (@messages.state@) Index: openacs-4/packages/forums/www/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/Attic/index-postgresql.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/forums/www/index-postgresql.xql 22 Jun 2002 20:58:04 -0000 1.1 +++ openacs-4/packages/forums/www/index-postgresql.xql 24 Jun 2002 21:40:16 -0000 1.2 @@ -10,7 +10,8 @@ from forums_messages where forums_messages.forum_id = forums_forums_enabled.forum_id and 1 = tree_level(forums_messages.tree_sortkey)) as n_threads, - to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified + to_char(acs_objects.last_modified, 'Mon DD YYYY HH24:MI:SS') as last_modified, + case when last_modified > (now() - 1) then 't' else 'f' end as new_p from forums_forums_enabled, acs_objects where forums_forums_enabled.package_id = :package_id Index: openacs-4/packages/forums/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/index.adp,v diff -u -r1.6 -r1.7 --- openacs-4/packages/forums/www/index.adp 6 Jun 2002 00:46:55 -0000 1.6 +++ openacs-4/packages/forums/www/index.adp 24 Jun 2002 21:40:16 -0000 1.7 @@ -38,7 +38,7 @@ @forums.name@ -(new postings!) + (new postings)
@forums.charter@ @forums.n_threads@