Index: openacs-4/packages/spam/spam.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/spam.info,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/spam.info 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/spam.info 4 Sep 2001 21:14:59 -0000 1.2 @@ -1,21 +1,23 @@ - + Spam System Spam Systems + f t oracle postgresql - Bill Schneider - Mark Dalrymple + Tilmann Singer + Bill Schneider + Mark Dalrymple Port of the spam system to 4.1 2001-02-06 - ArsDigita Corporation + OpenACS Port of the spam system to 4.1. Provides a set of user-interface pages for sending e-mail to groups of users, selected based on some criteria in the database. Applications may link to the spam system with their own set of criteria (SQL queries). @@ -29,45 +31,80 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: openacs-4/packages/spam/sql/oracle/spam-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/oracle/spam-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/sql/oracle/spam-create.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/sql/oracle/spam-create.sql 4 Sep 2001 21:14:59 -0000 1.2 @@ -2,7 +2,8 @@ -- extends acs_mail_bodies spam_id integer not null constraint spam_messages_spam_id_fk - references acs_mail_links(mail_link_id), + references acs_mail_links(mail_link_id) + on delete cascade, -- date to send the spam out -- spam will be put in mail queue at that time send_date date not null, Index: openacs-4/packages/spam/sql/oracle/spam-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/oracle/spam-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/sql/oracle/spam-drop.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/sql/oracle/spam-drop.sql 4 Sep 2001 21:14:59 -0000 1.2 @@ -1,3 +1,21 @@ +delete from acs_mail_links where mail_link_id in ( + select spam_id from spam_messages +); + drop table spam_messages; drop view spam_messages_all; + +delete from acs_objects where object_type = 'spam_message'; + +begin + acs_object_type.drop_type ( + 'spam_message', + 'f' + ); +end; +/ +show errors + + + drop package spam; Index: openacs-4/packages/spam/sql/oracle/spam-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/oracle/spam-packages.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/sql/oracle/spam-packages.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/sql/oracle/spam-packages.sql 4 Sep 2001 21:14:59 -0000 1.2 @@ -22,6 +22,7 @@ send_date in date ) return acs_objects.object_id%TYPE; + procedure edit ( spam_id in spam_messages.spam_id%TYPE, title in acs_mail_bodies.header_subject%TYPE default null, @@ -31,16 +32,23 @@ send_date in date ); + function new_content ( context_id in acs_objects.context_id%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, object_type in acs_objects.object_type%TYPE default 'acs_content', - mime_type in acs_contents.mime_type%TYPE default 'text/plain', - text in varchar2 default null - ) return acs_objects.object_id%TYPE; + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + p_text in varchar2 default null, + -- TilmannS: the body_id of the spam message that this content + -- item will be associated with. Added to provide unique name + -- for the cr + p_body_id in acs_mail_bodies.body_id%TYPE + + ) return acs_objects.object_id%TYPE; + procedure approve ( spam_id in spam_messages.spam_id%TYPE ); @@ -72,10 +80,10 @@ IS v_link_id acs_objects.object_id%TYPE; v_body_id acs_objects.object_id%TYPE; - v_content_obj acs_mail_bodies.content_object_id%TYPE; - tmp_blob BLOB; - v_html_id acs_mail_bodies.content_object_id%TYPE; - v_text_id acs_mail_bodies.content_object_id%TYPE; + v_content_obj acs_mail_bodies.content_item_id%TYPE; + v_html_id acs_mail_bodies.content_item_id%TYPE; + v_text_id acs_mail_bodies.content_item_id%TYPE; + v_dummy integer; begin v_body_id := acs_mail_body.new ( body_id => null, @@ -87,7 +95,7 @@ creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, - object_type => object_type + object_type => 'acs_mail_body' ); if plain_text is not null then @@ -98,7 +106,8 @@ creation_ip => creation_ip, object_type => object_type, mime_type => 'text/plain', - text => plain_text + p_text => plain_text, + p_body_id => v_body_id ); end if; @@ -110,7 +119,8 @@ creation_ip => creation_ip, object_type => object_type, mime_type => 'text/html', - text => html_text + p_text => html_text, + p_body_id => v_body_id ); end if; @@ -129,8 +139,8 @@ creation_ip => creation_ip, object_type => object_type ); - acs_mail_multipart.add_content(v_content_obj, v_text_id); - acs_mail_multipart.add_content(v_content_obj, v_html_id); + v_dummy := acs_mail_multipart.add_content(v_content_obj, v_text_id); + v_dummy := acs_mail_multipart.add_content(v_content_obj, v_html_id); elsif plain_text is not null then v_content_obj := v_text_id; elsif html_text is not null then @@ -168,10 +178,13 @@ v_creation_ip acs_objects.creation_ip%TYPE; v_creation_date acs_objects.creation_date%TYPE; v_body_id acs_objects.object_id%TYPE; - v_content_obj acs_mail_bodies.content_object_id%TYPE; - tmp_blob BLOB; - v_html_id acs_mail_bodies.content_object_id%TYPE; - v_text_id acs_mail_bodies.content_object_id%TYPE; + v_content_obj acs_mail_bodies.content_item_id%TYPE; + v_html_id acs_mail_bodies.content_item_id%TYPE; + v_text_id acs_mail_bodies.content_item_id%TYPE; + v_dummy integer; + + v_parts_row acs_mail_multipart_parts%ROWTYPE; + begin select body_id, context_id, creation_user, creation_date, creation_ip @@ -185,34 +198,37 @@ and spam_id = edit.spam_id and object_id = edit.spam_id; + select content_item_id into v_content_obj + from acs_mail_bodies + where body_id = v_body_id; + update acs_mail_bodies - set content_object_id = null, + set content_item_id = null, header_subject = edit.title where body_id = v_body_id; -- now we have the body id of the spam message. -- nuke any content associated with it. - select content_object_id into v_content_obj - from acs_mail_bodies - where body_id = v_body_id; + if acs_mail_multipart.multipart_p(v_content_obj) = 't' then -- we have a multipart -- and thus have to nuke the components. - select content_id into v_text_id - from acs_contents, acs_mail_multipart_parts - where multipart_id = v_content_obj - and content_id = content_object_id - and mime_type = 'text/plain'; - select content_id into v_html_id - from acs_contents, acs_mail_multipart_parts - where multipart_id = v_content_obj - and content_id = content_object_id - and mime_type = 'text/html'; - acs_content.delete(v_text_id); - acs_content.delete(v_html_id); - acs_mail_multipart.delete(v_content_obj); + + for v_parts_row in (select content_item_id from acs_mail_multipart_parts where multipart_id=v_content_obj) loop + + -- cannot delete content_item as long as + -- acs_mail_multipart_parts references it, so + -- we set it null here. There's propably a more + -- elegant way to do this though. (TilmannS) + update acs_mail_multipart_parts set content_item_id=null where multipart_id=v_content_obj; + + content_item.delete(v_parts_row.content_item_id); + end loop; + + acs_mail_multipart.delete(v_content_obj); + else - acs_content.delete(v_content_obj); + content_item.delete(v_content_obj); end if; if plain_text is not null then @@ -222,7 +238,8 @@ creation_user => v_creation_user, creation_ip => v_creation_ip, mime_type => 'text/plain', - text => plain_text + p_text => plain_text, + p_body_id => v_body_id ); end if; @@ -233,7 +250,8 @@ creation_user => v_creation_user, creation_ip => v_creation_ip, mime_type => 'text/html', - text => html_text + p_text => html_text, + p_body_id => v_body_id ); end if; @@ -251,17 +269,15 @@ creation_user => v_creation_user, creation_ip => v_creation_ip ); - acs_mail_multipart.add_content(v_content_obj, v_text_id); - acs_mail_multipart.add_content(v_content_obj, v_html_id); + v_dummy := acs_mail_multipart.add_content(v_content_obj, v_text_id); + v_dummy := acs_mail_multipart.add_content(v_content_obj, v_html_id); elsif plain_text is not null then v_content_obj := v_text_id; elsif html_text is not null then v_content_obj := v_html_id; end if; acs_mail_body.set_content_object(v_body_id, v_content_obj); - - update spam_messages set sql_query = edit.sql_query, send_date = edit.send_date @@ -275,27 +291,31 @@ creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, object_type in acs_objects.object_type%TYPE default null, - mime_type in acs_contents.mime_type%TYPE default 'text/plain', - text in varchar2 default null + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + p_text in varchar2 default null, + p_body_id in acs_mail_bodies.body_id%TYPE + ) return acs_objects.object_id%TYPE is - tmp_blob BLOB; - v_id integer; + v_id integer; + v_rev_id integer; begin - v_id := acs_mail_gc_object.new ( - context_id => context_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip + v_id := content_item.new ( + name => ('spam_message body_id-' || p_body_id || ' ' || mime_type), + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + mime_type => mime_type, + text => p_text, + storage_type => 'text' ); - v_id := acs_content.new ( - content_id => v_id, - mime_type => mime_type - ); - select content into tmp_blob from acs_contents - where content_id = v_id; - string_to_blob(text, tmp_blob); + + -- set the new revision live + v_rev_id := content_item.get_latest_revision(v_id); + content_item.set_live_revision(v_rev_id); + return v_id; + end new_content; procedure approve(spam_id IN spam_messages.spam_id%TYPE) Index: openacs-4/packages/spam/sql/postgresql/spam-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/postgresql/spam-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/sql/postgresql/spam-create.sql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,35 @@ +create table spam_messages ( + -- extends acs_mail_bodies + spam_id integer not null + constraint spam_messages_spam_id_fk + references acs_mail_links(mail_link_id) + on delete cascade, + -- date to send the spam out + -- spam will be put in mail queue at that time + send_date timestamp not null, + -- the sql query for selecting users. + -- must be of the form "select party_id from ... where ..." + sql_query varchar(4000) not null, + -- has it been approved yet? spam won't go in queue until + -- approved. + approved_p boolean, + -- has it been approved yet? + sent_p boolean default FALSE +); + +-- create a new object type +select acs_object_type__create_type ( + 'spam_message', -- object_type + 'Spam Message', -- pretty_name + 'Spam Messages', -- pretty_plural + 'acs_mail_body', -- supertype + 'spam_messages', -- table_name + 'spam_id', -- id_column + 'spam', -- package_name + 'f', -- abstract_p (default) + null, -- type_extension_table(default) + 'acs_object.default_name' -- name_method +); + +\i spam-views.sql +\i spam-packages.sql Index: openacs-4/packages/spam/sql/postgresql/spam-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/postgresql/spam-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/sql/postgresql/spam-drop.sql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,21 @@ +delete from acs_mail_links where mail_link_id in (select spam_id from +spam_messages); + +drop table spam_messages; +drop view spam_messages_all; + +delete from acs_objects where object_type = 'spam_message'; + +select acs_object_type__drop_type( + 'spam_message', -- object_type + FALSE -- cascade_p +); + + +drop function spam__new (integer,varchar,timestamp,text,varchar,text,varchar,varchar,integer,timestamp,integer,varchar,varchar,boolean,varchar,timestamp); + +drop function spam__edit (integer,text,varchar,varchar,varchar,timestamp); + +drop function spam__new_content (integer,timestamp,integer,varchar,varchar,varchar,varchar,integer); + +drop function spam__approve (integer); Index: openacs-4/packages/spam/sql/postgresql/spam-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/postgresql/spam-packages.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/sql/postgresql/spam-packages.sql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,318 @@ +-- bodies of spam package +-- + + +create function spam__new (integer,varchar,timestamp,text,varchar,text,varchar,varchar,integer,timestamp,integer,varchar,varchar,boolean,varchar,timestamp) +returns integer as ' +declare + p_spam_id alias for $1; -- default null + reply_to alias for $2; -- default null + sent_date alias for $3; -- default sysdate + sender alias for $4; -- default null + rfc822_id alias for $5; -- default null + title alias for $6; -- default null + html_text alias for $7; -- default null + plain_text alias for $8; -- default null + context_id alias for $9; + creation_date alias for $10; -- default sysdate + creation_user alias for $11; -- default null + creation_ip alias for $12; -- default null + object_type alias for $13; -- default acs_mail_body + p_approved_p alias for $14; -- default f + p_sql_query alias for $15; + p_send_date alias for $16; + + v_link_id acs_objects.object_id%TYPE; + v_body_id acs_objects.object_id%TYPE; + v_content_obj acs_mail_bodies.content_item_id%TYPE; + v_html_id acs_mail_bodies.content_item_id%TYPE; + v_text_id acs_mail_bodies.content_item_id%TYPE; +begin + select acs_mail_body__new ( + null, -- p_body_id + null, -- p_body_reply_to + null, -- p_body_from + sent_date, -- p_body_date + rfc822_id, -- p_header_message_id + reply_to, -- p_header_reply_to + title, -- p_header_subject + null, -- p_header_from + null, -- p_header_to + null, -- p_content_item_id + ''acs_mail_body'', -- p_object_type + creation_date, -- p_creation_date + creation_user, -- p_creation_user + creation_ip, -- p_creation_ip + context_id -- p_context_id + ) into v_body_id; + + if plain_text is not null then + select spam__new_content( + context_id, -- context_id + creation_date, -- creation_date + creation_user, -- creation_user + creation_ip, -- creation_ip + null, -- object_type + ''text/plain'', -- mime_type + plain_text, -- text + v_body_id -- body_id + ) into v_text_id; + end if; + + if html_text is not null then + select spam__new_content( + context_id, -- context_id + creation_date, -- creation_date + creation_user, -- creation_user + creation_ip, -- creation_ip + null, -- object_type + ''text/html'', -- mime_type + html_text, -- text + v_body_id -- body_id + ) into v_html_id; + end if; + + + -- now we have the message header set up. now see what type + -- of content we create. We create a straight text/* content + -- object if we only have either html_text or plain_text set, + -- but we create a multipart/alternative if we have both. + + if html_text IS NOT NULL and plain_text IS NOT NULL then + -- we have both html and plain + select acs_mail_multipart__new( + null, -- p_multipart_id + ''alternative'', -- p_multipart_kind + ''acs_mail_multipart'', -- p_object_type + creation_date, -- p_creation_date + creation_user, -- p_creation_user + creation_ip, -- p_creation_ip + context_id -- p_context_id + + ) into v_content_obj; + perform acs_mail_multipart__add_content(v_content_obj, v_text_id); + perform acs_mail_multipart__add_content(v_content_obj, v_html_id); + else if plain_text is not null then + v_content_obj := v_text_id; + else if html_text is not null then + v_content_obj := v_html_id; + end if; + end if; + end if; + + perform acs_mail_body__set_content_object(v_body_id, v_content_obj); + + select acs_mail_link__new( + p_spam_id, -- p_mail_link_id + v_body_id, -- p_body_id + context_id, -- p_context_id + creation_date, -- p_creation_date + creation_user, -- p_creation_user + creation_ip, -- p_creation_ip + ''acs_mail_link'' -- p_object_type (default) + ) into v_link_id; + + + insert into spam_messages (spam_id, sql_query, approved_p, send_date) values (v_link_id, p_sql_query, p_approved_p, p_send_date); + + return v_link_id; +end; +' language 'plpgsql'; + +-- end spam__new + + + +-- procedure edit +create function spam__edit (integer,text,varchar,varchar,varchar,timestamp) returns integer as ' +declare + p_spam_id alias for $1; -- spam_messages.spam_id%TYPE, + p_title alias for $2; -- acs_mail_bodies.header_subject%TYPE default null + p_html_text alias for $3; -- default null + p_plain_text alias for $4; -- default null + p_sql_query alias for $5; + p_send_date alias for $6; + + v_context_id acs_objects.context_id%TYPE; + v_creation_user acs_objects.creation_user%TYPE; + v_creation_ip acs_objects.creation_ip%TYPE; + v_creation_date acs_objects.creation_date%TYPE; + v_body_id acs_objects.object_id%TYPE; + v_content_obj acs_mail_bodies.content_item_id%TYPE; + v_rec record; + v_html_id acs_mail_bodies.content_item_id%TYPE; + v_text_id acs_mail_bodies.content_item_id%TYPE; + +begin + select + body_id, context_id, creation_user, creation_date, creation_ip + into + v_body_id, v_context_id, v_creation_user, v_creation_date, + v_creation_ip + from + acs_mail_links, spam_messages, acs_objects + where + spam_id = mail_link_id + and spam_id = p_spam_id + and object_id = p_spam_id; + + select content_item_id into v_content_obj + from acs_mail_bodies + where body_id = v_body_id; + + update acs_mail_bodies + set content_item_id = null, + header_subject = p_title + where body_id = v_body_id; + + + -- now we have the body id of the spam message. + -- nuke any content associated with it. + + if (select acs_mail_multipart__multipart_p(v_content_obj)) then + -- we have a multipart + -- and thus have to nuke the components. + + for v_rec in + select content_item_id from acs_mail_multipart_parts + where multipart_id=v_content_obj + loop + perform content_item__delete(v_rec.content_item_id); + end loop; + perform acs_mail_multipart__delete(v_content_obj); + + else + perform content_item__delete(v_content_obj); + end if; + + if p_plain_text is not null then + select spam__new_content( + v_context_id, -- context_id + v_creation_date, -- creation_date + v_creation_user, -- creation_user + v_creation_ip, -- creation_ip + null, -- object_type + ''text/plain'', -- mime_type + p_plain_text, -- text + v_body_id -- body_id + ) into v_text_id; + end if; + + if p_html_text is not null then + select spam__new_content( + v_context_id, -- context_id + v_creation_date, -- creation_date + v_creation_user, -- creation_user + v_creation_ip, -- creation_ip + null, -- object_type + ''text/html'', -- mime_type + p_html_text, -- text + v_body_id -- body_id + ) into v_html_id; + end if; + + + -- now we have the message header set up. now see what type + -- of content we create. We create a straight text/* content + -- object if we only have either html_text or plain_text set, + -- but we create a multipart/alternative if we have both. + + if p_html_text IS NOT NULL and p_plain_text IS NOT NULL then + -- we have both html and plain + select acs_mail_multipart__new( + null, -- p_multipart_id + ''alternative'', -- p_multipart_kind + ''acs_mail_multipart'', -- p_object_type + v_creation_date, -- p_creation_date + v_creation_user, -- p_creation_user + v_creation_ip, -- p_creation_ip + v_context_id -- p_context_id + + ) into v_content_obj; + perform acs_mail_multipart__add_content(v_content_obj, v_text_id); + perform acs_mail_multipart__add_content(v_content_obj, v_html_id); + else if p_plain_text is not null then + v_content_obj := v_text_id; + else if p_html_text is not null then + v_content_obj := v_html_id; + end if; + end if; + end if; + perform acs_mail_body__set_content_object(v_body_id, v_content_obj); + + return 0; + +end; +' language 'plpgsql'; + +-- end spam__edit + + + +create function spam__new_content (integer,timestamp,integer,varchar,varchar,varchar,varchar,integer) +returns integer as ' +declare + context_id alias for $1; -- acs_objects.context_id%TYPE + creation_date alias for $2; -- acs_objects.creation_date%TYPE default sysdate + creation_user alias for $3; -- acs_objects.creation_user%TYPE default null + creation_ip alias for $4; -- acs_objects.creation_ip%TYPE default null + object_type alias for $5; -- acs_objects.object_type%TYPE default content_item + mime_type alias for $6; -- acs_contents.mime_type%TYPE default text/plain + + p_text alias for $7; -- default null + + -- TilmannS: the body_id of the spam message that this content + -- item will be associated with. Added to provide unique name + -- for the cr + p_body_id alias for $8; + + v_id integer; + v_rev_id integer; + +begin + + select content_item__new ( + (''spam_message body_id-'' || p_body_id || '' '' || mime_type)::varchar, -- name + null, -- item_id + null, -- parent_id + null, -- new_locale + creation_date, -- creation_date, + creation_user, -- creation_user, + null, -- context_id + creation_ip, -- creation_ip + ''content_item'', -- new__item_subtype default ''content_item'' + ''content_revision'', -- new__content_type default ''content_revision'' + null, -- new__title default null + null, -- new__description default null + mime_type, -- mime_type + null, -- nls_language + p_text, -- text + ''text''::varchar -- storage_type + ) into v_id; + + -- set the new revision live + select content_item__get_latest_revision(v_id) into v_rev_id; + perform content_item__set_live_revision(v_rev_id); + + return v_id; +end; +' language 'plpgsql'; + +-- end spam__new_content + + + +-- procedure approve ( +create function spam__approve (integer) +returns integer as ' +declare + p_spam_id alias for $1 -- spam_messages.spam_id%TYPE) +begin + update spam_messages + set approved_p = ''t'' + where spam_id = p_spam_id; + + return 0; +end; +' language 'plpgsql'; + Index: openacs-4/packages/spam/sql/postgresql/spam-views.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/postgresql/spam-views.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/sql/postgresql/spam-views.sql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,5 @@ +create view spam_messages_all as + select spam_messages.*, acs_mail_bodies.* + from spam_messages, acs_mail_bodies, acs_mail_links + where spam_id = acs_mail_links.mail_link_id + and acs_mail_links.body_id = acs_mail_bodies.body_id; Index: openacs-4/packages/spam/tcl/spam-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/tcl/spam-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/tcl/spam-procs-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,72 @@ + + + + oracle8.1.6 + + + + + select + site_node.url(node_id) + from + site_nodes, apm_packages + where + object_id=package_id and package_key='[spam_package_key]' + + + + + + + + begin + :1 := spam.new ( + spam_id => :spam_id, + send_date => to_date(:send_date, 'yyyy-mm-dd hh:mi:ss AM'), + title => :subject, + sql_query => :sql, + html_text => :html, + plain_text => :plain, + creation_user => [ad_get_user_id], + creation_ip => '[ad_conn peeraddr]', + context_id => :context_id, + approved_p => :approved_p + ); + end; + + + + + + + + + begin + :1 := acs_mail_queue_message.new ( + body_id => :body_id, + context_id => :context_id, + creation_date => :creation_date, + creation_user => :creation_user, + creation_ip => :creation_ip + ); + end; + + + + + + + + + select spam_id + from spam_messages + where + sysdate >= send_date + and approved_p = 't' + and sent_p = 'f' + + + + + + Index: openacs-4/packages/spam/tcl/spam-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/tcl/spam-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/tcl/spam-procs-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,89 @@ + + + + postgresql7.1 + + + + + select + site_node__url(node_id) + from + site_nodes, apm_packages + where + object_id=package_id and package_key='[spam_package_key]' + + + + + + + + select spam__new ( + :spam_id, -- spam_id + null, -- reply_to + null, -- sent_date + null, -- sender + null, -- rfc822_id + :subject, -- title + :html, -- html_text + :plain, -- plain_text + :context_id, -- context_id + now(), -- creation_date + :user_id, -- creation_user + :peeraddr, -- creation_ip + 'spam_message', -- object_type + :approved_p, -- approved_p + :sql, -- sql_query + to_timestamp(:send_date, 'yyyy-mm-dd hh:mi:ss AM') -- send_date + ); + + + + + + + + select spam__edit ( + :spam_id, -- spam_id + :subject, -- title + :html, -- html_text + :plain, -- plain_text + :sql, -- sql_query + to_timestamp(:send_date, 'yyyy-mm-dd hh:mi:ss AM') -- send_date + ); + + + + + + + + select acs_mail_queue_message__new ( + null, -- p_mail_link_id + :body_id, -- p_body_id + :context_id, -- p_context_id + :creation_date, -- p_creation_date + :creation_user, -- p_creation_user + :creation_ip, -- p_creation_ip + 'acs_mail_link' -- p_object_type (default) + ); + + + + + + + + select spam_id + from spam_messages + where + current_timestamp >= send_date + and approved_p = 't' + and sent_p = 'f' + + + + + + Index: openacs-4/packages/spam/tcl/spam-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/tcl/spam-procs.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/tcl/spam-procs.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/tcl/spam-procs.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -36,6 +36,7 @@ "}] } + ad_proc spam_new_message { {-context_id [db_null]} {-send_date [db_null]} @@ -51,6 +52,17 @@ Requires that send_date be a string in the format "YYYY-MM-DD HH:MI:SS AM"; nearly ANSI but 12-hour time with AM/PM } { + + # TilmannS: add a leading zero to the time, otherwise postgresql's + # to_timestamp chokes. The default (produced by + # spam_timeentrywidget, which uses ns_dbformvalueput) brings us a + # string like this: '2001-08-31 7:45:00 PM' but we need something + # like that: '2001-08-31 07:45:00 PM'. Not the most elegant + # solution and not meant to be final - in my opinion the + # time_widget needs some overall improvement here (is there + # something general in ACS for this kind of stuff?). + regsub { (\d):} $send_date { 0\1:} send_date + set sql_proc " begin :1 := spam.new ( @@ -67,6 +79,9 @@ ); end;" + set user_id [ad_get_user_id] + set peeraddr [ad_conn peeraddr] + return [db_exec_plsql spam_insert_message $sql_proc] } @@ -94,6 +109,16 @@ plain_text => :plain ); end;" + + # TilmannS: add a leading zero to the time, otherwise postgresql's + # to_timestamp chokes. The default (produced by + # spam_timeentrywidget, which uses ns_dbformvalueput) brings us a + # string like this: '2001-08-31 7:45:00 PM' but we need something + # like that: '2001-08-31 07:45:00 PM'. Not the most elegant + # solution and not meant to be final - in my opinion the + # time_widget needs some overall improvement here (is there + # something general in ACS for this kind of stuff?). + regsub { (\d):} $send_date { 0\1:} send_date return [db_exec_plsql spam_update_message $sql_proc] } @@ -134,18 +159,20 @@ where p2.party_id = parties.party_id "] db_transaction { - set id [db_exec_plsql spam_insert_into_outgoing { - begin - :1 := acs_mail_queue_message.new ( + + foreach email $recipients { + set id [db_exec_plsql spam_insert_into_outgoing { + begin + :1 := acs_mail_queue_message.new ( body_id => :body_id, context_id => :context_id, creation_date => :creation_date, creation_user => :creation_user, creation_ip => :creation_ip ); - end; - }] - foreach email $recipients { + end; + }] + db_dml spam_set_outgoing_addresses { insert into acs_mail_queue_outgoing (message_id, envelope_from, envelope_to) Index: openacs-4/packages/spam/tcl/spam-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/tcl/spam-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/tcl/spam-procs.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,80 @@ + + + + + + + select min(package_id) from apm_packages + where package_key = '[spam_package_key]' + + + + + + + + + spam_put_in_outgoing_queue $msg_id + acs_mail_process_queue + + + + + + + + + select body_id, send_date, sql_query, context_id, + creation_date, creation_user, creation_ip + from spam_messages, acs_objects, acs_mail_links + where + object_id = spam_id + and mail_link_id = spam_id + and spam_id = :spam_id + and approved_p = 't' + + + + + + + + + select email from parties, ($sql_query) p2 + where p2.party_id = parties.party_id + + + + + + + + + insert into acs_mail_queue_outgoing + (message_id, envelope_from, envelope_to) + values + (:id, :spam_sender, :email) + + + + + + + + + update spam_messages + set sent_p = 't' + where spam_id = :spam_id + + + + + + + + select count(spam_id) from spam_messages where spam_id = :spam_id + + + + + Index: openacs-4/packages/spam/www/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/index-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,55 @@ + + + + oracle8.1.6 + + + + + + select count(1) from ($__thisrow(sql_query)) + + + + + + + + + + select sm.spam_id, + to_char(sm.send_date, 'Mon DD, YYYY HH:MI:SS PM') wait_until, + decode (sm.approved_p, 't', 'Approved', + 'f', 'Not Approved', 'Error') pretty_approved, + approved_p, + sm.sql_query, + sm.header_subject as title, + acs_permission.permission_p(sm.spam_id, :user_id, 'admin') + as admin_p + from spam_messages_all sm + where sent_p = 'f' + and acs_permission.permission_p(sm.spam_id, :user_id, 'write') = 't' + + order by sm.send_date + + + + + + + + + select sm.spam_id, + sm.header_subject as title, + sm.send_date + from spam_messages_all sm + where + sent_p = 't' + and acs_permission.permission_p(sm.spam_id, :user_id, 'read') = 't' + order by sm.send_date + + + + + + Index: openacs-4/packages/spam/www/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/index-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,62 @@ + + + + postgresql7.1 + + + + + + select count(1) from ($__thisrow(sql_query)) as c + + + + + + + + + select sm.spam_id, + to_char(sm.send_date, 'Mon DD, YYYY HH:MI:SS PM') as wait_until, + case when sm.approved_p=TRUE then + 'Approved' + else + case when sm.approved_p=FALSE then + 'Not Approved' + else + 'Error' + end + end + as pretty_approved, + + approved_p, + sm.sql_query, + sm.header_subject as title, + acs_permission__permission_p(sm.spam_id, 2924, 'admin') + as admin_p + from spam_messages_all sm + where sent_p = 'f' + and acs_permission__permission_p(sm.spam_id, 2924, 'write') = 't' + + order by sm.send_date + + + + + + + + select sm.spam_id, + sm.header_subject as title, + sm.send_date + from spam_messages_all sm + where + sent_p = 't' + and acs_permission__permission_p(sm.spam_id, :user_id, 'read') = 't' + order by sm.send_date + + + + + + Index: openacs-4/packages/spam/www/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/index.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,4 @@ + + + + Index: openacs-4/packages/spam/www/spam-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-add.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select count(*) from ($sql_query) + + + + + + Index: openacs-4/packages/spam/www/spam-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-edit-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,70 @@ + + + + oracle8.1.6 + + + + + + select count(1) from ($sql_query) + + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/plain' + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/html' + + + + + + + + + select header_subject as title, + to_char(send_date, 'yyyy-mm-dd') as send_date, + to_char(send_date, 'hh24:mi:ss') as send_time, + sql_query, sent_p, content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item.get_live_revision(:content_item_id) + + + + + + + Index: openacs-4/packages/spam/www/spam-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-edit-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,41 @@ + + + + postgresql7.1 + + + + + + select count(1) from ($sql_query) as c + + + + + + + + + select header_subject as title, + to_char(send_date, 'yyyy-mm-dd') as send_date, + to_char(send_date, 'hh24:mi:ss') as send_time, + sql_query, sent_p, content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item__get_live_revision(:content_item_id) + + + + + + Index: openacs-4/packages/spam/www/spam-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-edit.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/spam-edit.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/spam-edit.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -39,24 +39,23 @@ # XXX: cut-and-paste programming, should be a proc -if [acs_mail_multipart_p $content_object_id] { - foreach type {plain html} { - db_1row spam_get_multipart_${type}_text " - select content - from acs_mail_multipart_parts, acs_contents - where multipart_id = :content_object_id - and content_id = content_object_id - and mime_type = 'text/$type' - " - set ${type}_text $content - } +if [acs_mail_multipart_p $content_item_id] { + + db_1row spam_get_multipart_plain_text "select content as plain_text from acs_mail_multipart_parts, acs_contents + where multipart_id = :content_item_id + and content_id = content_item_id and mime_type = 'text/plain'" + + db_1row spam_get_multipart_html_text "select content as html_text from acs_mail_multipart_parts, acs_contents + where multipart_id = :content_item_id + and content_id = content_item_id and mime_type = 'text/html'" + } else { set plain_text "" set html_text "" db_1row spam_get_text { select content, mime_type from acs_contents - where content_id = :content_object_id + where content_id = :content_item_id } if {$mime_type == "text/plain"} { set plain_text $content Index: openacs-4/packages/spam/www/spam-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-edit.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + + + + + + Index: openacs-4/packages/spam/www/spam-send.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-send.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-send.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,12 @@ + + + + + + + select count(1) from spam_messages where spam_id=:spam_id + + + + + Index: openacs-4/packages/spam/www/spam-show-users-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-show-users-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-show-users-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select email, first_names || ' ' || last_name as name + from parties, ($sql_query) p2, persons + where parties.party_id = person_id(+) + and p2.party_id = parties.party_id + + + + + + Index: openacs-4/packages/spam/www/spam-show-users.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-show-users.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-show-users.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select email, first_names || ' ' || last_name as name + from parties + left join persons on parties.party_id = person_id + join ($sql_query) p2 on p2.party_id = parties.party_id + + + + + + Index: openacs-4/packages/spam/www/spam-view-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-view-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-view-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,60 @@ + + + + oracle8.1.6 + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item.get_live_revision(:content_item_id) + + + + + + + + + select header_subject as title, + to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, + content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/plain' + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/html' + + + + + + + Index: openacs-4/packages/spam/www/spam-view-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-view-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-view-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,31 @@ + + + + postgresql7.1 + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item__get_live_revision(:content_item_id) + + + + + + + + + select header_subject as title, + to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, + content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/spam-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-view.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/spam-view.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/spam-view.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -9,25 +9,32 @@ db_1row spam_get_message { select header_subject as title, to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, - content_object_id + content_item_id from spam_messages_all where spam_id = :spam_id } set html_text "" set plain_text "" -if [acs_mail_multipart_p $content_object_id] { - foreach type {plain html} { - db_1row spam_get_multipart_${type}_text " +if [acs_mail_multipart_p $content_item_id] { + + db_1row spam_get_multipart_plain_text " select content from acs_mail_multipart_parts, acs_contents where multipart_id = :content_object_id and content_id = content_object_id - and mime_type = 'text/$type' + and mime_type = 'text/plain' " - set ${type}_text $content - } + db_1row spam_get_multipart_html_text " + select content + from acs_mail_multipart_parts, acs_contents + where multipart_id = :content_object_id + and content_id = content_object_id + and mime_type = 'text/plain' + " + + } else { db_1row spam_get_text { select content, mime_type Index: openacs-4/packages/spam/www/spam-view.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/spam-view.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/spam-view.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + + + + + + Index: openacs-4/packages/spam/www/toggle-approval-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/toggle-approval-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/toggle-approval-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,29 @@ + + + + oracle8.1.6 + + + + + update spam_messages + set approved_p = util.logical_negation(approved_p) + where spam_id = :spam_id + + + + + + + + + select to_char(send_date, 'yyyy-mm-dd hh24:mi:ss') as sql_send_time, + sql_query, approved_p + from spam_messages + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/toggle-approval-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/toggle-approval-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/toggle-approval-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,29 @@ + + + + postgresql7.1 + + + + + update spam_messages + set approved_p = util__logical_negation(approved_p) + where spam_id = :spam_id + + + + + + + + + select to_char(send_date, 'yyyy-mm-dd hh24:mi:ss') as sql_send_time, + sql_query, approved_p + from spam_messages + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/admin/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/index-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,38 @@ + + + + oracle8.1.6 + + + + + select count(1) from ($__thisrow(sql_query)) + + + + + + + + + + select sm.spam_id, + to_char(sm.send_date, 'Mon DD, YYYY HH:MI:SS PM') wait_until, + decode (sm.approved_p, 't', 'Approved', + 'f', 'Not Approved', 'Error') pretty_approved, + approved_p, + sm.sql_query, + sm.header_subject as title, + acs_permission.permission_p(sm.spam_id, :user_id, 'admin') + as admin_p + from spam_messages_all sm + where sent_p = 'f' + and acs_permission.permission_p(sm.spam_id, :user_id, 'write') = 't' + + order by sm.send_date + + + + + + Index: openacs-4/packages/spam/www/admin/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/index-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,45 @@ + + + + postgresql7.1 + + + + + select count(1) from ($__thisrow(sql_query)) as c + + + + + + + + + select sm.spam_id, + to_char(sm.send_date, 'Mon DD, YYYY HH:MI:SS PM') as wait_until, + case when sm.approved_p=TRUE then + 'Approved' + else + case when sm.approved_p=FALSE then + 'Not Approved' + else + 'Error' + end + end + as pretty_approved, + approved_p, + sm.sql_query, + sm.header_subject as title, + acs_permission__permission_p(sm.spam_id, :user_id, 'admin') + as admin_p + from spam_messages_all sm + where sent_p = 'f' + and acs_permission__permission_p(sm.spam_id, :user_id, 'write') = 't' + + order by sm.send_date + + + + + + Index: openacs-4/packages/spam/www/admin/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/index.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + + select sm.spam_id, + sm.header_subject as title, + sm.send_date + from spam_messages_all sm + where + sent_p = 't' + order by sm.send_date + + + + + + Index: openacs-4/packages/spam/www/admin/spam-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-edit-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,70 @@ + + + + oracle8.1.6 + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/plain' + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp, cr_items ci, cr_revisions cr + where + mpp.content_item_id=ci.item_id and + ci.live_revision=cr.revision_id and + multipart_id=:content_item_id and cr.mime_type='text/html' + + + + + + + + + select count(1) from ($sql_query) + + + + + + + + + select header_subject as title, + to_char(send_date, 'yyyy-mm-dd') as send_date, + to_char(send_date, 'hh24:mi:ss') as send_time, + sql_query, sent_p, content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item.get_live_revision(:content_item_id) + + + + + + Index: openacs-4/packages/spam/www/admin/spam-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-edit-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,47 @@ + + + + postgresql7.1 + + + + + select header_subject as title, + to_char(send_date, 'yyyy-mm-dd') as send_date, + to_char(send_date, 'hh24:mi:ss') as send_time, + sql_query, sent_p, content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + + + + + + + Index: openacs-4/packages/spam/www/admin/spam-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-edit.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/admin/spam-edit.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/admin/spam-edit.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -18,7 +18,7 @@ select header_subject as title, to_char(send_date, 'yyyy-mm-dd') as send_date, to_char(send_date, 'hh24:mi:ss') as send_time, - sql_query, sent_p, content_object_id + sql_query, sent_p, content_item_id from spam_messages_all where spam_id = :spam_id } @@ -40,17 +40,23 @@ set html_text "" -if [acs_mail_multipart_p $content_object_id] { - foreach type {plain html} { - db_1row spam_get_multipart_${type}_text " - select content - from acs_mail_multipart_parts, acs_contents - where multipart_id = :content_object_id - and content_id = content_object_id - and mime_type = 'text/$type' +if [acs_mail_multipart_p $content_item_id] { + + db_1row spam_get_multipart_plain_text " + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; " - set ${type}_text $content - } + db_1row spam_get_multipart_html_text " + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + " + } else { db_1row spam_get_text { select content, mime_type Index: openacs-4/packages/spam/www/admin/spam-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-edit.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,41 @@ + + + + + + + select count(1) from ($sql_query) as c + + + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + + + + + + + Index: openacs-4/packages/spam/www/admin/spam-show-users-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-show-users-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-show-users-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select email, first_names || ' ' || last_name as name + from parties, ($sql_query) p2, persons + where parties.party_id = person_id(+) + and p2.party_id = parties.party_id + + + + + + Index: openacs-4/packages/spam/www/admin/spam-show-users.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-show-users.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/admin/spam-show-users.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/admin/spam-show-users.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -14,4 +14,9 @@ set root [nsv_get acs_properties root_directory] source "$root/packages/spam/www/spam-show-users.tcl" -return \ No newline at end of file +return + + +# TilmannS: since I don't know how to tell the QD how to deal with +# sourced pages I simply copied ../spam-show-users.xql and +# ../spam-show-users-oracle.xql into admin/ Index: openacs-4/packages/spam/www/admin/spam-show-users.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-show-users.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-show-users.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select email, first_names || ' ' || last_name as name + from parties + left join persons on parties.party_id = person_id + join ($sql_query) p2 on p2.party_id = parties.party_id + + + + + + Index: openacs-4/packages/spam/www/admin/spam-view-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-view-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-view-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,32 @@ + + + + oracle8.1.6 + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item.get_live_revision(:content_item_id) + + + + + + + + + + select header_subject as title, + to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, + content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/admin/spam-view-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-view-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-view-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,32 @@ + + + + postgresql7.1 + + + + + + select content, mime_type + from cr_revisions + where revision_id = content_item__get_live_revision(:content_item_id) + + + + + + + + + + select header_subject as title, + to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, + content_item_id + from spam_messages_all + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/admin/spam-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-view.adp,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/admin/spam-view.adp 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/admin/spam-view.adp 4 Sep 2001 21:14:59 -0000 1.2 @@ -21,7 +21,7 @@ HTML message body - @content@ + @html_text@ Index: openacs-4/packages/spam/www/admin/spam-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-view.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/spam/www/admin/spam-view.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/packages/spam/www/admin/spam-view.tcl 4 Sep 2001 21:14:59 -0000 1.2 @@ -7,30 +7,37 @@ db_1row spam_get_message { select header_subject as title, to_char(send_date, 'Month DD, YYYY HH:MI:SS') as send_date, - content_object_id + content_item_id from spam_messages_all where spam_id = :spam_id } set html_text "" set plain_text "" -if [acs_mail_multipart_p $content_object_id] { - foreach type {plain html} { - db_1row spam_get_multipart_${type}_text " - select content - from acs_mail_multipart_parts, acs_contents - where multipart_id = :content_object_id - and content_id = content_object_id - and mime_type = 'text/$type' +if [acs_mail_multipart_p $content_item_id] { + + db_1row spam_get_multipart_plain_text " + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; " - set ${type}_text $content - } + db_1row spam_get_multipart_html_text " + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + " + + } else { db_1row spam_get_text { select content, mime_type - from acs_contents - where content_id = :content_object_id + from cr_revisions + where revision_id = content_item__get_live_revision(:content_item_id) } if {$mime_type == "text/plain"} { set plain_text $content Index: openacs-4/packages/spam/www/admin/spam-view.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/spam-view.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/spam-view.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + + select cr.content as plain_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/plain'; + + + + + + + + + select cr.content as html_text + from acs_mail_multipart_parts mpp + join cr_items ci on mpp.content_item_id=ci.item_id + join cr_revisions cr on ci.live_revision=cr.revision_id + where multipart_id=:content_item_id and cr.mime_type='text/html'; + + + + + + Index: openacs-4/packages/spam/www/admin/toggle-approval-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/toggle-approval-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/toggle-approval-oracle.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,29 @@ + + + + oracle8.1.6 + + + + + update spam_messages + set approved_p = util.logical_negation(approved_p) + where spam_id = :spam_id + + + + + + + + + select to_char(send_date, 'yyyy-mm-dd hh24:mi:ss') as sql_send_time, + sql_query, approved_p + from spam_messages + where spam_id = :spam_id + + + + + + Index: openacs-4/packages/spam/www/admin/toggle-approval-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/spam/www/admin/toggle-approval-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/spam/www/admin/toggle-approval-postgresql.xql 4 Sep 2001 21:14:59 -0000 1.1 @@ -0,0 +1,29 @@ + + + + postgresql7.1 + + + + + update spam_messages + set approved_p = util__logical_negation(approved_p) + where spam_id = :spam_id + + + + + + + + + select to_char(send_date, 'yyyy-mm-dd hh24:mi:ss') as sql_send_time, + sql_query, approved_p + from spam_messages + where spam_id = :spam_id + + + + + +