-- /packages/news/sql/news-create.sql -- -- @author stefan@arsdigita.com -- @created 2000-12-13 -- @cvs-id $Id: news-create.sql,v 1.19 2014/10/27 16:41:46 victorg 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); -- 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'); select acs_privilege__add_child('news_admin', 'news_delete'); end; -- assign permission to defined contexts within ACS by default -- -- -- procedure inline_0/0 -- CREATE OR REPLACE 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, -- article abstract lead varchar(4000), -- 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 timestamptz, -- support for approval approval_user integer constraint cr_news_approval_user_fk references users, approval_date timestamptz, 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 'content_revision', -- supertype 'News Article', -- pretty_name 'News Articles', -- pretty_plural 'cr_news', -- table_name 'news_id', -- id_column 'news__name' -- name_method ); end; begin; -- create attributes for widget generation -- lead SELECT content_type__create_attribute ( 'news', -- content type 'lead', -- attr name 'text', -- datatype 'Lead', -- pretty name 'Leads', -- pretty plural null, -- sort order null, -- default value 'varchar(4000)' -- column spec ); -- 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 -- -- procedure inline_0/0 -- CREATE OR REPLACE 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 (); -- Create views after package since they need news__status \i news-package-create.sql \i news-views-create.sql