Index: openacs-4/packages/news/news.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/news.info,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/news.info 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/news.info 15 Oct 2001 13:41:03 -0000 1.2 @@ -1,9 +1,10 @@ - + News News + f f @@ -15,7 +16,7 @@ News application 2001-03-07 ArsDigita Corporation - News publication tool for corporate and website news in HTML and plain text format. + News publication tool for corporate and website news in HTML and plain text format. Beta release. @@ -24,57 +25,96 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - + + + + + Index: openacs-4/packages/news/sql/postgresql/news-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-create.sql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,887 @@ +-- /packages/news/sql/news-create.sql +-- +-- @author stefan@arsdigita.com +-- @created 2000-12-13 +-- @cvs-id $Id: news-create.sql,v 1.1 2001/10/15 13:41:03 robertl Exp $ +-- +-- OpenACS Port: Robert Locke (rlocke@infiniteinfo.com) +-- + +-- *** PERMISSION MODEL *** +-- + +begin; + -- the read privilege is by default granted to 'the_public' + -- the site-wide administrator has to change this in /permissions/ + -- if he wants to restrict an instance to a specific party + + -- the news_admin has all privileges: read, create, delete, approve + -- news_admin is a child of 'admin'. + -- 'admin' is therefore the top-administrator, news_admin is the news administrator + -- in the context of an instance + + select acs_privilege__create_privilege('news_read', null, null); + select acs_privilege__create_privilege('news_create', null, null); + select acs_privilege__create_privilege('news_delete', null, null); + select acs_privilege__create_privilege('news_admin', 'News Administrator', null); + + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + + -- bind privileges to global names + select acs_privilege__add_child('read', 'news_read'); + select acs_privilege__add_child('create', 'news_create'); + select acs_privilege__add_child('delete', 'news_delete'); + + -- add this to the news_admin privilege + -- news administrator binds to global 'admin', plus inherits news_* permissions + select acs_privilege__add_child('admin', 'news_admin'); + select acs_privilege__add_child('news_admin', 'news_read'); + select acs_privilege__add_child('news_admin', 'news_create'); + + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + + select acs_privilege__add_child('news_admin', 'news_delete'); + +end; + + +-- assign permission to defined contexts within ACS by default +-- +create function inline_0 () +returns integer as ' +declare + default_context acs_objects.object_id%TYPE; + registered_users acs_objects.object_id%TYPE; + the_public acs_objects.object_id%TYPE; +begin + default_context := acs__magic_object_id(''default_context''); + registered_users := acs__magic_object_id(''registered_users''); + the_public := acs__magic_object_id(''the_public''); + + + -- give the public permission to read by default + PERFORM acs_permission__grant_permission ( + default_context, -- object_id + the_public, -- grantee_id + ''news_read'' -- privilege + ); + + -- give registered users permission to upload items by default + -- However, they must await approval by users with news_admin privilege + PERFORM acs_permission__grant_permission ( + default_context, -- object_id + registered_users, -- grantee_id + ''news_create'' -- privilege + ); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +-- *** DATAMODEL *** +-- we use the content repository (see http://cvs.arsdigita.com/acs/packages/acs-content-repository) plus this +create table cr_news ( + news_id integer + constraint cr_news_id_fk + references cr_revisions + constraint cr_news_pk + primary key, + -- include package_id to provide support for multiple instances + package_id integer + constraint cr_news_package_id_nn not null, + -- regarding news item + -- *** support for dates when items are displayed or archived *** + -- unarchived news items have archive_date null + archive_date timestamp, + -- support for approval + approval_user integer + constraint cr_news_approval_user_fk + references users, + approval_date timestamp, + approval_ip varchar(50) +); + + +-- index to avoid lock situation through parent table +create index cr_news_appuser_id_fk on cr_news(approval_user); + + +-- *** NEWS item defitition *** --- +begin; + select content_type__create_type ( + 'news', -- content_type + null, -- supertype + 'News Item', -- pretty_name + 'News Items', -- pretty_plural + 'cr_news', -- table_name + 'news_id', -- id_column + 'news__name' -- name_method +); +end; + + +begin; +-- create attributes for widget generation + +-- website archive date of news release + select content_type__create_attribute ( + 'news', -- content_type + 'archive_date', -- attribute_name + 'timestamp', -- datatype + 'Archive Date', -- pretty_name + 'Archive Dates', -- pretty_plural + null, -- sort_order + null, -- default_value + 'timestamp' -- column_spec + ); +-- authorized user for approval + select content_type__create_attribute ( + 'news', -- content_type + 'approval_user', -- attribute_name + 'integer', -- datatype + 'Approval User', -- pretty_name + 'Approval Users', -- pretty_plural + null, -- sort_order + null, -- default_value + 'integer' -- column_spec + ); +-- approval date + select content_type__create_attribute ( + 'news', -- content_type + 'approval_date', -- attribute_name + 'timestamp', -- datatype + 'Approval Date', -- pretty_name + 'Approval Dates', -- pretty_plural + null, -- sort_order + current_date::varchar, -- default_value + 'timestamp' -- column_spec + ); +-- approval IP address + select content_type__create_attribute ( + 'news', -- content_type + 'approval_ip', -- attribute_name + 'text', -- datatype + 'Approval IP', -- pretty_name + 'Approval IPs', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(50)' -- column_spec + ); +end; + + +-- *** CREATE THE NEWS FOLDER as our CONTAINER *** + +-- create 1 news folder; different instances are filtered by package_id +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; +begin + v_folder_id := content_folder__new( + ''news'', -- name + ''news'', -- label + ''News Item Root Folder, all news items go in here'', -- description + null -- parent_id + ); +-- associate content types with news folder + PERFORM content_folder__register_content_type ( + v_folder_id, -- folder_id + ''news'', -- content_type + ''t'' -- include_subtypes + ); + PERFORM content_folder__register_content_type ( + v_folder_id, -- folder_id + ''content_revision'', -- content_type + ''t'' -- include_subtypes + ); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +-- *** PACKAGE NEWS, plsql to create content_item *** +create function news__new (integer,varchar,timestamp,text,varchar,varchar, + varchar,integer,timestamp,integer,timestamp,varchar,varchar, + varchar,integer,boolean) +returns integer as ' +declare + p_item_id alias for $1; -- default null + -- + p_locale alias for $2; -- default null, + -- + p_publish_date alias for $3; -- default null + p_text alias for $4; -- default null + p_nls_language alias for $5; -- default null + p_title alias for $6; -- default null + p_mime_type alias for $7; -- default ''text/plain'' + -- + p_package_id alias for $8; -- default null, + p_archive_date alias for $9; -- default null + p_approval_user alias for $10; -- default null + p_approval_date alias for $11; -- default null + p_approval_ip alias for $12; -- default null, + -- + p_relation_tag alias for $13; -- default null + -- + -- REMOVED: p_item_subtype alias for $14; -- default ''content_item'' + -- REMOVED: p_content_type alias for $15; -- default ''news'' + -- REMOVED: p_creation_date alias for $16; -- default current_timestamp + p_creation_ip alias for $14; -- default null + p_creation_user alias for $15; -- default null + -- + p_is_live_p alias for $16; -- default ''f'' + + v_news_id integer; + v_item_id integer; + v_id integer; + v_revision_id integer; + v_parent_id integer; + v_name varchar; + v_log_string varchar; +begin + select content_item__get_id(''news'',null,''f'') + into v_parent_id + from dual; + -- + -- this will be used for 2xClick protection + if p_item_id is null then + select acs_object_id_seq.nextval + into v_id + from dual; + else + v_id := p_item_id; + end if; + -- + select ''news'' || to_char(current_timestamp,''YYYYMMDD'') || v_id + into v_name + from dual; + -- + v_log_string := ''initial submission''; + -- + v_item_id := content_item__new( + v_name, -- name + v_parent_id, -- parent_id + v_id, -- item_id + p_locale, -- locale + current_timestamp,-- creation_date + p_creation_user, -- creation_user + null, -- context_id + p_creation_ip, -- creation_ip + ''content_item'', -- item_subtype + ''news'', -- content_type + null, -- title + null, -- description + p_mime_type, -- mime_type + p_nls_language, -- nls_language + null -- data + -- relation tag is not used by any callers or any + -- implementations of content_item__new + ); + v_revision_id := content_revision__new( + p_title, -- title + v_log_string, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + p_nls_language, -- nls_language + p_text, -- data + v_item_id, -- item_id + null, -- revision_id + current_timestamp, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + insert into cr_news + (news_id, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make this revision live when immediately approved + if p_is_live_p = ''t'' then + update + cr_items + set + live_revision = v_revision_id, + publish_status = ''ready'' + where + item_id = v_item_id; + end if; + v_news_id := v_revision_id; + return v_news_id; +end; +' language 'plpgsql'; + + +-- deletes a news item along with all its revisions and possible attachements +create function news__delete (integer) +returns integer as ' +declare + p_item_id alias for $1; + v_item_id cr_items.item_id%TYPE; + v_cm RECORD; +begin + v_item_id := p_item_id; + -- dbms_output.put_line(''Deleting associated comments...''); + -- delete acs_messages, images, comments to news item + + FOR v_cm IN + select message_id from acs_messages am, acs_objects ao + where am.message_id = ao.object_id + and ao.context_id = v_item_id + LOOP + -- images + delete from images + where image_id in (select latest_revision + from cr_items + where parent_id = v_cm.message_id); + PERFORM acs_message__delete(v_cm.message_id); + delete from general_comments + where comment_id = v_cm.message_id; + END LOOP; + delete from cr_news + where news_id in (select revision_id + from cr_revisions + where item_id = v_item_id); + PERFORM content_item__delete(v_item_id); + return 0; +end; +' language 'plpgsql'; + + +-- (re)-publish a news item out of the archive by nulling the archive_date +-- this only applies to the currently active revision +create function news__make_permanent (integer) +returns integer as ' +declare + p_item_id alias for $1; +begin + update cr_news + set archive_date = null + where news_id = content_item__get_live_revision(p_item_id); + + return 0; +end; +' language 'plpgsql'; + + +-- archive a news item +-- this only applies to the currently active revision +create function news__archive (integer,timestamp) +returns integer as ' +declare + p_item_id alias for $1; + p_archive_date alias for $2; -- default current_timestamp +begin + update cr_news + set archive_date = p_archive_date + where news_id = content_item__get_live_revision(p_item_id); + + return 0; +end; +' language 'plpgsql'; + +-- RAL: an overloaded version using current_timestamp for archive_date +create function news__archive (integer) +returns integer as ' +declare + p_item_id alias for $1; + -- p_archive_date alias for $2; -- default current_timestamp +begin + return news__archive (p_item_id, current_timestamp); +end; +' language 'plpgsql'; + + +-- approve/unapprove a specific revision +-- approving a revision makes it also the active revision +create function news__set_approve(integer,varchar,timestamp, + timestamp,integer,timestamp,varchar,boolean) +returns integer as ' +declare + p_revision_id alias for $1; + p_approve_p alias for $2; -- default ''t'' + p_publish_date alias for $3; -- default null + p_archive_date alias for $4; -- default null + p_approval_user alias for $5; -- default null + p_approval_date alias for $6; -- default current_timestamp + p_approval_ip alias for $7; -- default null + p_live_revision_p alias for $8; -- default ''t'' + v_item_id cr_items.item_id%TYPE; +begin + select item_id into v_item_id + from cr_revisions + where revision_id = p_revision_id; + -- unapprove an revision (does not mean to knock out active revision) + if p_approve_p = ''f'' then + update cr_news + set approval_date = null, + approval_user = null, + approval_ip = null, + archive_date = null + where news_id = p_revision_id; + -- + update cr_revisions + set publish_date = null + where revision_id = p_revision_id; + else + -- approve a revision + update cr_revisions + set publish_date = p_publish_date + where revision_id = p_revision_id; + -- + update cr_news + set archive_date = p_archive_date, + approval_date = p_approval_date, + approval_user = p_approval_user, + approval_ip = p_approval_ip + where news_id = p_revision_id; + -- + -- cannot use content_item.set_live_revision because it sets publish_date to sysdate + if p_live_revision_p = ''t'' then + update cr_items + set live_revision = p_revision_id, + publish_status = ''ready'' + where item_id = v_item_id; + end if; + -- + end if; + + return 0; +end; +' language 'plpgsql'; + + +-- the status function returns information on the puplish or archive status +-- it does not make any checks on the order of publish_date and archive_date +create function news__status (integer) +returns varchar as ' +declare + p_news_id alias for $1; + v_archive_date timestamp; + v_publish_date timestamp; +begin + -- populate variables + select archive_date into v_archive_date + from cr_news + where news_id = p_news_id; + -- + select publish_date into v_publish_date + from cr_revisions + where revision_id = p_news_id; + + -- if publish_date is not null the item is approved, otherwise it is not + if v_publish_date is not null then + if v_publish_date > current_timestamp then + -- to be published (2 cases) + -- archive date could be null if it has not been decided when to archive + -- RAL: the nasty ''extract'' code below was the only way I could figure + -- to get the same result as Oracle (eg, 2.4 days) + if v_archive_date is null then + return ''going live in '' + || text(round(extract(days from interval (v_publish_date - current_timestamp)) + + extract(hours from interval (v_publish_date - current_timestamp))/24,1)) + || '' days''; + else + return ''going live in '' + || text(round(extract(days from interval (v_publish_date - current_timestamp)) + + extract(hours from interval (v_publish_date - current_timestamp))/24,1)) + || '' days'' || '', archived in '' + || text(round(extract(days from interval (v_archive_date - current_timestamp)) + + extract(hours from interval (v_archive_date - current_timestamp))/24,1)) + || '' days''; + end if; + else + -- already released or even archived (3 cases) + if v_archive_date is null then + return ''published, not scheduled for archive''; + else + if v_archive_date - current_timestamp > 0 then + return ''published, archived in '' + || text(round(extract(days from interval (v_archive_date - current_timestamp)) + + extract(hours from interval (v_archive_date - current_timestamp))/24,1)) + || '' days''; + else + return ''archived''; + end if; + end if; + end if; + else + return ''unapproved''; + end if; +end; +' language 'plpgsql'; + + +create function news__name (integer) +returns varchar as ' +declare + p_news_id alias for $1; + v_news_title cr_revisions.title%TYPE; +begin + select title + into v_news_title + from cr_revisions + where revision_id = p_news_id; + + return v_news_title; +end; +' language 'plpgsql'; + + +-- +-- API for Revision management +-- +create function news__revision_new (integer,timestamp,text,varchar,text, + varchar,integer,timestamp,integer,timestamp,varchar,timestamp,varchar, + integer,boolean) +returns integer as ' +declare + p_item_id alias for $1; + -- + p_publish_date alias for $2; -- default null + p_text alias for $3; -- default null + p_title alias for $4; + -- + -- here goes the revision log + p_description alias for $5; + -- + p_mime_type alias for $6; -- default ''text/plain'' + p_package_id alias for $7; -- default null + p_archive_date alias for $8; -- default null + p_approval_user alias for $9; -- default null + p_approval_date alias for $10; -- default null + p_approval_ip alias for $11; -- default null + -- + p_creation_date alias for $12; -- default current_timestamp + p_creation_ip alias for $13; -- default null + p_creation_user alias for $14; -- default null + -- + p_make_active_revision_p alias for $15; -- default ''f'' + + v_revision_id integer; +begin + -- create revision + v_revision_id := content_revision__new( + p_title, -- title + p_description, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_text, -- text + p_item_id, -- item_id + null, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + -- create new news entry with new revision + insert into cr_news + (news_id, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make active revision if indicated + if p_make_active_revision_p = ''t'' then + PERFORM news__revision_set_active(v_revision_id); + end if; + return v_revision_id; +end; +' language 'plpgsql'; + + +create function news__revision_set_active (integer) +returns integer as ' +declare + p_revision_id alias for $1; + v_news_item_p boolean; + -- could be used to check if really a ''news'' item +begin + update + cr_items + set + live_revision = p_revision_id, + publish_status = ''ready'' + where + item_id = (select + item_id + from + cr_revisions + where + revision_id = p_revision_id); + + return 0; +end; +' language 'plpgsql'; + + +-- currently not used, because we want to audit revisions +create function news__revision_delete (integer) +returns integer as ' +declare + p_revision_id alias for $1; +begin + -- delete from cr_news table + delete from cr_news + where news_id = p_revision_id; + + -- delete revision + PERFORM content_revision__delete( + p_revision_id -- revision_id + ); + + return 0; +end; +' language 'plpgsql'; + + +-- +-- views on 'news' application that pick from cr_news, cr_items, cr_revisions +-- Re-arrange 'joins' for performance tuning +-- RAL: Casted all _date columns to ::date for consistency with Oracle views. +-- + +-- Views on multiple items + +-- View on all released news items in its active revision +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +-- RAL: Dropped 'content' column from this view which is redundant and not +-- used anywhere. +-- +create view news_items_approved +as +select + ci.item_id as item_id, + cn.package_id, + cr.title as publish_title, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, + cr.publish_date::date as publish_date, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + cn.archive_date::date as archive_date +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and ci.live_revision = cr.revision_id +and cr.revision_id = cn.news_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of all news items in the system +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create view news_items_live_or_submitted +as +select + ci.item_id as item_id, + cn.news_id, + cn.package_id, + to_char(cr.publish_date,'MM-DD-yyyy') as publish_date, + to_char(cn.archive_date,'MM-DD-yyyy') as archive_date, + cr.title as publish_title, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_date::date as creation_date, + ci.live_revision, + news__status(cn.news_id) as status +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id) +or (ci.live_revision is null + and ci.item_id = cr.item_id + and cr.revision_id = content_item__get_latest_revision(ci.item_id) + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id); + + +-- View of unapproved items +create view news_items_unapproved +as +select + ci.item_id as item_id, + cr.title as publish_title, + cn.package_id as package_id, + ao.creation_date::date as creation_date, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id +and cr.revision_id = content_item__get_live_revision(ci.item_id) +and cr.revision_id = cn.news_id +and cr.item_id = ci.item_id +and cr.publish_date is null; + + +-- One News Item Views +-- +-- View of all revisions of a news item +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create view news_item_revisions +as +select + cr.item_id as item_id, + cr.revision_id, + ci.live_revision, + cr.title as publish_title, + cr.content as publish_body, + cr.publish_date::date as publish_date, + cn.archive_date::date as archive_date, + cr.description as log_entry, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.mime_type as mime_type, + cn.package_id, + ao.creation_date::date as creation_date, + news__status(news_id) as status, + case when exists (select 1 from cr_news where news_id = revision_id + and news__status(news_id) = 'unapproved') then 1 else 0 end + as + approval_needed_p, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_user, + ao.creation_ip, + ci.name as item_name +from + cr_revisions cr, + cr_news cn, + cr_items ci, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and cr.revision_id = cn.news_id +and ci.item_id = cr.item_id +and ao.creation_user = ps.person_id; + + +-- View of a submitted news item or active revision in unapproved state +create view news_item_unapproved +as +select + cr.revision_id, + ci.name as item_name, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_ip as item_creation_ip, + ao.creation_date::date as creation_date +from + cr_revisions cr, + cr_items ci, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of a news item as of its active revision +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create view news_item_full_active +as +select + ci.item_id as item_id, + cn.package_id as package_id, + revision_id, + title as publish_title, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.publish_date::date as publish_date, + cn.archive_date::date as archive_date, + news__status(cr.revision_id) as status, + ci.name as item_name, + ps.person_id as creator_id, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.item_id = ci.item_id +and (cr.revision_id = ci.live_revision + or (ci.live_revision is null + and cr.revision_id = content_item__get_latest_revision(ci.item_id))) +and cr.revision_id = cn.news_id +and ci.item_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- plsql to create keywords for news items +-- no additional code necessary for news items right now. + +-- plsql for searches: will be covered by site-wide search +-- no additional code necessary for news items right now. Index: openacs-4/packages/news/sql/postgresql/news-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-drop.sql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,179 @@ +-- /packages/news/sql/news-drop.sql +-- +-- @author stefan@arsdigita.com +-- @created 2000-12-20 +-- $Id: news-drop.sql,v 1.1 2001/10/15 13:41:03 robertl Exp $ + + +-- unregister content_types from folder +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_id cr_items.item_id%TYPE; + -- RAL: commented out, not used. GC should be probably dealt with in + -- news__delete anyways. + -- v_gc_id general_comments.comment_id%TYPE; + -- v_gc_msg_id acs_messages.message_id%TYPE; + v_item_cursor RECORD; + +begin + select content_item__get_id(''news'', null, ''f'') into v_folder_id from dual; + + -- delete all contents of news folder + FOR v_item_cursor IN + select item_id + from cr_items + where parent_id = v_folder_id + LOOP + -- all attached types/item are deleted in news.delete - modify there + PERFORM news__delete(v_item_cursor.item_id); + END LOOP; + + -- unregister_content_types + PERFORM content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''content_revision'', -- content_type + ''t'' -- include_subtypes + ); + PERFORM content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''news'', -- content_type + ''t'' -- include_subtypes + ); + + -- this table must not hold reference to ''news'' type + delete from cr_folder_type_map where content_type = ''news''; + + -- delete news folder + PERFORM content_folder__delete(v_folder_id); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +-- drop package news +drop function news__new (integer,varchar,timestamp,text,varchar,varchar, + varchar,integer,timestamp,integer,timestamp,varchar,varchar, + varchar,integer,boolean); +drop function news__delete (integer); +drop function news__make_permanent (integer); +drop function news__archive (integer,timestamp); +drop function news__archive (integer); +drop function news__set_approve(integer,varchar,timestamp, + timestamp,integer,timestamp,varchar,boolean); +drop function news__status (integer); +drop function news__name (integer); +drop function news__revision_new (integer,timestamp,text,varchar,text, + varchar,integer,timestamp,integer,timestamp,varchar,timestamp,varchar, + integer,boolean); +drop function news__revision_set_active (integer); +drop function news__revision_delete (integer); + + +-- delete news views + +drop view news_items_approved; +drop view news_items_live_or_submitted; +drop view news_items_unapproved; +drop view news_item_revisions; +drop view news_item_unapproved; +drop view news_item_full_active; + + +-- drop attributes +begin; + +-- website archive date of news release +select content_type__drop_attribute ( + 'news', -- content_type + 'archive_date', -- attribute_name + 'f' -- drop_column +); +-- assignement to an authorized user for approval +select content_type__drop_attribute ( + 'news', -- content_type + 'approval_user', -- attribute_name + 'f' -- drop_column +); +-- approval date +select content_type__drop_attribute ( + 'news', -- content_type + 'approval_date', -- attribute_name + 'f' -- drop_column +); +-- approval IP address +select content_type__drop_attribute ( + 'news', -- content_type + 'approval_ip', -- attribute_name + 'f' -- drop_column +); +-- delete content_type 'news' +select acs_object_type__drop_type ( + 'news', -- object_type + 't' -- cascade_p +); + +end; + + +-- drop indices to avoid lock situation through parent table + +drop index cr_news_appuser_id_fk; + +-- delete pertinent info from cr_news + +drop table cr_news; + + +-- delete privileges; +create function inline_0 () +returns integer as ' +declare + default_context acs_objects.object_id%TYPE; + registered_users acs_objects.object_id%TYPE; + the_public acs_objects.object_id%TYPE; +begin + PERFORM acs_privilege__remove_child(''news_admin'',''news_approve''); + PERFORM acs_privilege__remove_child(''news_admin'',''news_create''); + PERFORM acs_privilege__remove_child(''news_admin'',''news_delete''); + PERFORM acs_privilege__remove_child(''news_admin'',''news_read''); + + PERFORM acs_privilege__remove_child(''read'',''news_read''); + PERFORM acs_privilege__remove_child(''create'',''news_create''); + PERFORM acs_privilege__remove_child(''delete'',''news_delete''); + PERFORM acs_privilege__remove_child(''admin'',''news_approve''); + + PERFORM acs_privilege__remove_child(''admin'',''news_admin''); + + default_context := acs__magic_object_id(''default_context''); + registered_users := acs__magic_object_id(''registered_users''); + the_public := acs__magic_object_id(''the_public''); + + PERFORM acs_permission__revoke_permission ( + default_context, -- object_id + registered_users, -- grantee_id + ''news_create'' -- privilege + ); + PERFORM acs_permission__revoke_permission ( + default_context, -- object_id + the_public, -- grantee_id + ''news_read'' -- privilege + ); + + PERFORM acs_privilege__drop_privilege(''news_approve''); + PERFORM acs_privilege__drop_privilege(''news_create''); + PERFORM acs_privilege__drop_privilege(''news_delete''); + PERFORM acs_privilege__drop_privilege(''news_read''); + PERFORM acs_privilege__drop_privilege(''news_admin''); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); Index: openacs-4/packages/news/tcl/news-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/news-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/tcl/news-procs-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,41 @@ + + + + oracle8.1.6 + + + + + begin + news.archive( + item_id => :id, + archive_date => :when); + end; + + + + + + + + + begin + news.make_permanent(:id); + end; + + + + + + + + + begin + news.delete(:id); + end; + + + + + + Index: openacs-4/packages/news/tcl/news-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/news-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/tcl/news-procs-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,36 @@ + + + + postgresql7.1 + + + + + select news__archive( + :id, -- item_id + :when -- archive_date + ); + + + + + + + + + select news__make_permanent(:id); + + + + + + + + + select news__delete(:id); + + + + + + Index: openacs-4/packages/news/www/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/index-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,48 @@ + + + + oracle8.1.6 + + + + + select case when count(*) = 0 then 0 else 1 end + from news_items_approved + where publish_date < sysdate + and archive_date < sysdate + and package_id = :package_id + + + + + + + + select case when count(*) = 0 then 0 else 1 end + from news_items_approved + where publish_date < sysdate + and (archive_date is null + or archive_date > sysdate) + and package_id = :package_id + + + + + + + + publish_date < sysdate + and (archive_date is null or archive_date > sysdate) + + + + + + + + publish_date < sysdate + and archive_date < sysdate + + + + Index: openacs-4/packages/news/www/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/index-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,48 @@ + + + + postgresql7.1 + + + + + select case when count(*) = 0 then 0 else 1 end + from news_items_approved + where publish_date < current_timestamp + and archive_date < current_timestamp + and package_id = :package_id + + + + + + + + select case when count(*) = 0 then 0 else 1 end + from news_items_approved + where publish_date < current_timestamp + and (archive_date is null + or archive_date > current_timestamp) + and package_id = :package_id + + + + + + + + publish_date < current_timestamp + and (archive_date is null or archive_date > current_timestamp) + + + + + + + + publish_date < current_timestamp + and archive_date < current_timestamp + + + + Index: openacs-4/packages/news/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.adp,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/index.adp 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/index.adp 15 Oct 2001 13:41:03 -0000 1.2 @@ -24,7 +24,7 @@ Search -
search> +
Index: openacs-4/packages/news/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/index.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/index.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -48,9 +48,7 @@ if { [string equal "live" $view] } { set title "News" - set view_clause " - publish_date < sysdate - and (archive_date is null or archive_date > sysdate)" + set view_clause [db_map view_clause_live] if { [db_string archived_p " select decode(count(*),0,0,1) @@ -66,9 +64,7 @@ } else { set title "News Archive" - set view_clause " - publish_date < sysdate - and archive_date < sysdate" + set view_clause [db_map view_clause_archived] if { [db_string live_p " select decode(count(*),0,0,1) Index: openacs-4/packages/news/www/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/index.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + +select item_id, + package_id, + publish_title, + publish_date +from news_items_approved +where $view_clause +and package_id = :package_id +order by publish_date desc, item_id desc + + + + + Index: openacs-4/packages/news/www/item-create-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-create-3-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,39 @@ + + + + oracle8.1.6 + + + + +begin +:1 := news.new( +title => :publish_title, +publish_date => :publish_date_ansi, +archive_date => :archive_date_ansi, +mime_type => :mime_type, +package_id => :package_id, +approval_user => :approval_user, +approval_date => :approval_date, +approval_ip => :approval_ip, +creation_ip => :creation_ip, +creation_user => :user_id, +is_live_p => :live_revision_p +); +end; + + + + + + + +update cr_revisions +set content = empty_blob() +where revision_id = :news_id +returning content into :1 + + + + + Index: openacs-4/packages/news/www/item-create-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-create-3-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,31 @@ + + + + postgresql7.1 + + + + + select news__new( + null, -- p_item_id + null, -- p_locale + :publish_date_ansi, -- p_publish_date + :publish_body, -- p_text + null, -- p_nls_language + :publish_title, -- p_title + :mime_type, -- p_mime_type + :package_id, -- p_package_id + :archive_date_ansi, -- p_archive_date + :approval_user, -- p_approval_user + :approval_date, -- p_approval_date + :approval_ip, -- p_approval_ip + null, -- p_relation_tag + :creation_ip, -- p_creation_ip + :user_id, -- p_creation_user + :live_revision_p -- p_is_live_p + ); + + + + + Index: openacs-4/packages/news/www/item-create-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/item-create-3.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/item-create-3.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -50,6 +50,12 @@ } +# RAL: This was missing and allows the user to "never expire" a news +# item. +if {[string equal $permanent_p "t"] } { + set archive_date_ansi [db_null] +} + # get creation_foo set creation_date [dt_sysdate] set creation_ip [ad_conn "peeraddr"] @@ -83,13 +89,18 @@ ); end;"] -db_dml content_add " -update cr_revisions -set content = empty_blob() -where revision_id = :news_id -returning content into :1" -blobs [list $publish_body] +# +# RAL: For postgres, we need NOT store the data in a blob. The +# news item body is stored in the prior news__new call. +# +if {![string match [db_type] "postgresql"]} { + db_dml content_add " + update cr_revisions + set content = empty_blob() + where revision_id = :news_id + returning content into :1" -blobs [list $publish_body] +} - if { !$news_admin_p } { if { ![string equal "open" [ad_parameter ApprovalPolicy "news" "wait"]] } { Index: openacs-4/packages/news/www/item-create-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-create-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-create-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + select sysdate + [ad_parameter ActiveDays "news" 14] from dual + + + + + Index: openacs-4/packages/news/www/item-create-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-create-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-create-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select current_timestamp + [ad_parameter ActiveDays "news" 14] + + + + + Index: openacs-4/packages/news/www/item-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,20 @@ + + + oracle8.1.6 + + + + +select item_id, + live_revision, + publish_title, + html_p, + publish_date, + '' || item_creator || '' as creator_link +from news_items_live_or_submitted +where item_id = :item_id + + + + + Index: openacs-4/packages/news/www/item-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,20 @@ + + + postgresql7.1 + + + + +select item_id, + live_revision, + publish_title, + html_p, + publish_date, + publish_body, + '' || item_creator || '' as creator_link +from news_items_live_or_submitted +where item_id = :item_id + + + + Index: openacs-4/packages/news/www/item.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/item.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/item.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -45,10 +45,13 @@ # workaround to get blobs with >4000 chars into a var, content.blob_to_string fails! # when this'll work, you get publish_body by selecting 'publish_body' directly from above view # - set publish_body [db_string get_content "select content - from cr_revisions - where revision_id = :live_revision"] - + # RAL: publish_body is already snagged in the 1st query above for postgres. + # + if {![string match [db_type] "postgresql"]} { + set publish_body [db_string get_content "select content + from cr_revisions + where revision_id = :live_revision"] + } # text-only body if {[info exists html_p] && [string equal $html_p "f"]} { Index: openacs-4/packages/news/www/item.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/item.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + + select content + from cr_revisions + where revision_id = :live_revision + + + + + Index: openacs-4/packages/news/www/preview.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/preview.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/preview.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + +select first_names || ' ' || last_name +from cc_users +where user_id = :user_id + + + + + Index: openacs-4/packages/news/www/admin/approve-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/approve-2-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,26 @@ + + + + oracle8.1.6 + + + + + begin + news.set_approve( + approve_p => 't', + revision_id => :id, + publish_date => :publish_date_ansi, + archive_date => :archive_date_ansi, + approval_user => :approval_user, + approval_date => :approval_date, + approval_ip => :approval_ip, + live_revision_p => :live_revision_p + ); + end; + + + + + + Index: openacs-4/packages/news/www/admin/approve-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/approve-2-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select news__set_approve( + :id, -- p_revision_id + 't', -- p_approve_p + :publish_date_ansi, -- p_publish_date + :archive_date_ansi, -- p_archive_date + :approval_user, -- p_approval_user + :approval_date, -- p_approval_date + :approval_ip, -- p_approval_ip + :live_revision_p -- p_live_revision_p + ); + + + + + + Index: openacs-4/packages/news/www/admin/approve-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/approve-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + select sysdate + [ad_parameter ActiveDays "news" 14] from dual + + + + + + + + content_item.get_best_revision(item_id) as revision_id, + + + + + Index: openacs-4/packages/news/www/admin/approve-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/approve-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,21 @@ + + + + postgresql7.1 + + + + select current_timestamp + [ad_parameter ActiveDays "news" 14] + + + + + + + + content_item__get_best_revision(item_id) as revision_id, + + + + + Index: openacs-4/packages/news/www/admin/approve.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/approve.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/admin/approve.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -42,7 +42,7 @@ # get most likely revision_id if not supplied if {[empty_string_p $revision_id]} { - set revision_select "content_item.get_best_revision(item_id) as revision_id," + set revision_select [db_map revision_select] } else { set revision_select "'$revision_id' as revision_id," } Index: openacs-4/packages/news/www/admin/approve.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/approve.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + + select + item_id, + $revision_select + publish_title, + creation_date, + item_creator + from + news_items_live_or_submitted + where + item_id in ([join $bind_id_list ","]) + + + + + Index: openacs-4/packages/news/www/admin/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/index-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,31 @@ + + + + oracle8.1.6 + + + + +select + item_id, + content_item.get_best_revision(item_id) as revision_id, + content_revision.get_number(news_id) as revision_no, + publish_title, + html_p, + publish_date, + archive_date, + creation_user, + item_creator, + package_id, + status +from + news_items_live_or_submitted +where + package_id = :package_id + $view_option +order by item_id desc + + + + + Index: openacs-4/packages/news/www/admin/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/index-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,31 @@ + + + + postgresql7.1 + + + + +select + item_id, + content_item__get_best_revision(item_id) as revision_id, + content_revision__get_number(news_id) as revision_no, + publish_title, + html_p, + publish_date, + archive_date, + creation_user, + item_creator, + package_id, + status +from + news_items_live_or_submitted +where + package_id = :package_id + $view_option +order by item_id desc + + + + + Index: openacs-4/packages/news/www/admin/item.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/item.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/item.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + + +select + item_id, + revision_id, + live_revision as item_live_revision_id, + publish_title, + log_entry, + package_id, + approval_needed_p, + creation_user, + item_creator, + status +from + news_item_revisions +where + item_id = :item_id +order by revision_id desc + + + + + + Index: openacs-4/packages/news/www/admin/process-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/process-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/process-2-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + select sysdate from dual + + + + + + + select next_day(sysdate,'Monday') from dual + + + + + + + select last_day(sysdate)+1 from dual + + + + + Index: openacs-4/packages/news/www/admin/process-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/process-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/process-2-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + select current_timestamp + + + + + + + select next_day(current_timestamp,'Monday')::date + + + + + + + select (last_day(current_timestamp)+1)::date + + + + + Index: openacs-4/packages/news/www/admin/process-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/process-2.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/process-2.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/admin/process-2.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -29,17 +29,17 @@ } "archive now" { - set when [db_string publish_now "select sysdate from dual"] + set when [db_string archive_now "select sysdate from dual"] news_items_archive $n_items $when } "archive next week" { - set when [db_string publish_now "select next_day(sysdate,'Monday') from dual"] + set when [db_string archive_next_week "select next_day(sysdate,'Monday') from dual"] news_items_archive $n_items $when } "archive next month" { - set when [db_string publish_now "select last_day(sysdate)+1 from dual"] + set when [db_string archive_next_month "select last_day(sysdate)+1 from dual"] news_items_archive $n_items $when } Index: openacs-4/packages/news/www/admin/process-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/process-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/process-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,24 @@ + + + + oracle8.1.6 + + + + + select + item_id, + content_item.get_best_revision(item_id) as revision_id, + package_id, + publish_title, + creation_date, + item_creator + from + news_items_live_or_submitted + where + item_id in ([join $bind_id_list ","]) + + + + + Index: openacs-4/packages/news/www/admin/process-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/process-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/process-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select + item_id, + content_item__get_best_revision(item_id) as revision_id, + package_id, + publish_title, + creation_date, + item_creator + from + news_items_live_or_submitted + where + item_id in ([join $bind_id_list ","]) + + + + + Index: openacs-4/packages/news/www/admin/process.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/process.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/process.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + select + item_id, + publish_title, + creation_date, + item_creator + from + news_items_unapproved + where + item_id in ([join $bind_id_list ","]) + + + + + Index: openacs-4/packages/news/www/admin/revision-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-add-3-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,40 @@ + + + + oracle8.1.6 + + + + + begin + :1 := news.revision_new( + item_id => :item_id, + publish_date => :publish_date_ansi, + title => :publish_title, + description => :revision_log, + mime_type => :mime_type, + package_id => [ad_conn package_id], + archive_date => :archive_date_ansi, + approval_user => :approval_user, + approval_date => :approval_date, + approval_ip => :approval_ip, + creation_ip => :creation_ip, + creation_user => :creation_user, + make_active_revision_p => :active_revision_p); + end; + + + + + + + + update cr_revisions + set content = empty_blob() + where revision_id = :revision_id + returning content into :1 + + + + + Index: openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,30 @@ + + + + postgresql7.1 + + + + + select news__revision_new( + :item_id, -- p_item_id + :publish_date_ansi, -- p_publish_date + :publish_body, -- p_text + :publish_title, -- p_title + :revision_log, -- p_description + :mime_type, -- p_mime_type + [ad_conn package_id], -- p_package_id + :archive_date_ansi, -- p_archive_date + :approval_user, -- p_approval_user + :approval_date, -- p_approval_date + :approval_ip, -- p_approval_ip + current_timestamp, -- p_creation_date + :creation_ip, -- p_creation_ip + :creation_user, -- p_creation_user + :active_revision_p -- p_make_active_revision_p + ); + + + + + Index: openacs-4/packages/news/www/admin/revision-add-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/revision-add-3.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/admin/revision-add-3.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -64,22 +64,24 @@ creation_user => :creation_user, make_active_revision_p => :active_revision_p); end;"] - - db_dml content_add " - update cr_revisions - set content = empty_blob() - where revision_id = :revision_id - returning content into :1" -blobs [list $publish_body] + + if {![string match [db_type] "postgresql"]} { + db_dml content_add " + update cr_revisions + set content = empty_blob() + where revision_id = :revision_id + returning content into :1" -blobs [list $publish_body] + } - } errmsg ] { +} errmsg ] { - set complaint " The database did not accept your input. - See details for the error message below\n\n\t

$errmsg" - ad_return_error "Database Error" "$complaint" - return -code return + set complaint " The database did not accept your input. + See details for the error message below\n\n\t

$errmsg" + ad_return_error "Database Error" "$complaint" + return -code return - } else { +} else { - ad_returnredirect "item?item_id=$item_id" + ad_returnredirect "item?item_id=$item_id" - } +} Index: openacs-4/packages/news/www/admin/revision-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/revision-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-add-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,26 @@ + + + + oracle8.1.6 + + + + +select + item_id, + package_id, + revision_id, + publish_title, + html_p, + publish_date, + NVL(archive_date, sysdate+[ad_parameter ActiveDays "news" 14]) as archive_date, + status +from + news_item_full_active +where + item_id = :item_id + + + + + Index: openacs-4/packages/news/www/admin/revision-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/revision-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-add-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + +select + item_id, + package_id, + revision_id, + publish_title, + html_p, + publish_date, + publish_body, + coalesce(archive_date, current_timestamp+[ad_parameter ActiveDays "news" 14]) as archive_date, + status +from + news_item_full_active +where + item_id = :item_id + + + + + Index: openacs-4/packages/news/www/admin/revision-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/revision-add.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/admin/revision-add.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -60,9 +60,11 @@ # workaround to get blobs with >4000 chars into a var, content.blob_to_string fails! # when this'll work, you get publish_body by selecting 'publish_body' directly from above view # -set publish_body [db_string get_content "select content -from cr_revisions -where revision_id = :revision_id"] +if {![string match [db_type] "postgresql"]} { + set publish_body [db_string get_content "select content + from cr_revisions + where revision_id = :revision_id"] +} set never_checkbox " + + + + + + select + item_name, + creator_id, + item_creator + from + news_item_full_active + where item_id = :item_id + + + + + + + + select content +from cr_revisions +where revision_id = :revision_id + + + + + Index: openacs-4/packages/news/www/admin/revision-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,26 @@ + + + + oracle8.1.6 + + + + + select item_id, + revision_id, + content_revision.get_number(:revision_id) as revision_no, + publish_title, + html_p, + publish_date, + archive_date, + creation_ip, + creation_date, + '' || item_creator || '' as creator_link + from news_item_revisions + where item_id = :item_id + and revision_id = :revision_id + + + + + Index: openacs-4/packages/news/www/admin/revision-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + + select item_id, + revision_id, + content_revision__get_number(:revision_id) as revision_no, + publish_title, + html_p, + publish_date, + archive_date, + creation_ip, + creation_date, + publish_body, + '' || item_creator || '' as creator_link + from news_item_revisions + where item_id = :item_id + and revision_id = :revision_id + + + + + Index: openacs-4/packages/news/www/admin/revision-set-active-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-set-active-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-set-active-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + begin + news.revision_set_active (:new_rev_id); + end; + + + + + + Index: openacs-4/packages/news/www/admin/revision-set-active-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-set-active-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision-set-active-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,15 @@ + + + + postgresql7.1 + + + + + select news__revision_set_active (:new_rev_id); + + + + + + Index: openacs-4/packages/news/www/admin/revision.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/revision.tcl 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/news/www/admin/revision.tcl 15 Oct 2001 13:41:03 -0000 1.2 @@ -49,9 +49,11 @@ # workaround to get blobs with >4000 chars into a var, content.blob_to_string fails! # when this'll work, you get publish_body by selecting 'publish_body' directly from above view # - set publish_body [db_string get_content "select content - from cr_revisions - where revision_id = :revision_id"] + if {![string match [db_type] "postgresql"]} { + set publish_body [db_string get_content "select content + from cr_revisions + where revision_id = :revision_id"] + } # text-only body if {[info exists html_p] && ![string equal $html_p "t"]} { Index: openacs-4/packages/news/www/admin/revision.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/revision.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revision.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + + select content + from cr_revisions + where revision_id = :revision_id + + + + + Index: openacs-4/packages/news/www/admin/revoke-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revoke-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revoke-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + begin + news.set_approve( + approve_p => 'f', + revision_id => :revision_id + ); + end; + + + + + + Index: openacs-4/packages/news/www/admin/revoke-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revoke-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/admin/revoke-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 @@ -0,0 +1,24 @@ + + + + postgresql7.1 + + + + + select news__set_approve( + :revision_id, -- p_revision_id + 'f', -- p_approve_p + null, -- p_publish_date + null, -- p_archive_date + null, -- p_approval_user + current_timestamp, -- p_approval_date + null, -- p_approval_ip + 't' -- p_live_revision_p + ); + + + + + +