Index: openacs-4/packages/curriculum-central/sql/postgresql/curriculum-central-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum-central/sql/postgresql/curriculum-central-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/curriculum-central/sql/postgresql/curriculum-central-create.sql 20 Nov 2005 23:51:10 -0000 1.1 +++ openacs-4/packages/curriculum-central/sql/postgresql/curriculum-central-create.sql 4 Dec 2005 07:30:45 -0000 1.2 @@ -12,3 +12,4 @@ \i department-create.sql \i stream-create.sql \i uos-create.sql +\i staff-create.sql Index: openacs-4/packages/curriculum-central/sql/postgresql/staff-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum-central/sql/postgresql/staff-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum-central/sql/postgresql/staff-create.sql 4 Dec 2005 07:30:45 -0000 1.1 @@ -0,0 +1,144 @@ +-- +-- packages/curriculum-central/sql/postgresql/staff-create.sql +-- +-- @author Nick Carroll (nick.c@rroll.net) +-- @creation-date 2005-11-08 +-- @cvs-id $Id: staff-create.sql,v 1.1 2005/12/04 07:30:45 ncarroll Exp $ +-- +-- + + +create function inline_0 () +returns integer as' +begin + PERFORM acs_object_type__create_type ( + ''cc_staff'', -- object_type + ''Staff'', -- pretty_name + ''Staff'', -- pretty_plural + ''acs_object'', -- supertype + ''cc_staff'', -- table_name + ''staff_id'', -- id_column + null, -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + ''cc_staff__name'' -- name_method + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +create table cc_staff ( + staff_id integer + constraint cc_staff_staff_id_fk + references users(user_id) + constraint cc_staff_staff_id_pk primary key, + title varchar(256) + constraint cc_staff_title_nn not null, + position varchar(256) + constraint cc_staff_position_nn not null, + department_id integer + constraint cc_staff_department_id_fk + references cc_department(department_id) +); + + +-- +-- Attributes for the Staff Object +-- +create function inline_1 () +returns integer as ' +begin + PERFORM acs_attribute__create_attribute ( + ''cc_staff'', -- object_type + ''title'', -- attribute_name + ''string'', -- datatype + ''Title'', -- pretty_name + ''Titles'', -- pretty_plural + null, -- table_name + null, -- column_name + null, -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''type_specific'', -- storage + ''f'' -- static_p + ); + + PERFORM acs_attribute__create_attribute ( + ''cc_staff'', -- object_type + ''position'', -- attribute_name + ''string'', -- datatype + ''Position'', -- pretty_name + ''Positions'', -- pretty_plural + null, -- table_name + null, -- column_name + null, -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''type_specific'', -- storage + ''f'' -- static_p + ); + + return 0; +end;' +language 'plpgsql'; +select inline_1 (); +drop function inline_1 (); + + +select define_function_args('cc_staff__new', 'staff_id,title,position,department_id'); + +create function cc_staff__new(integer, varchar, varchar, integer) +returns integer as' + +declare + + p_staff_id alias for $1; + p_title alias for $2; + p_position alias for $3; + p_department_id alias for $4; +begin + + -- The p_staff_id should already exist in acs_objects and users + -- tables. cc_staff is a "subtype" of the users table. + insert into cc_staff values(p_staff_id, p_title, p_position, p_department_id); + + return p_staff_id; + +end; +' language plpgsql; + + +select define_function_args('cc_staff__delete', 'staff_id'); + +create function cc_staff__delete (integer) +returns integer as ' +declare + p_staff_id alias for $1; +begin + delete from cc_staff + where staff_id = p_staff_id; + + raise NOTICE ''Deleting staff...''; + + return 0; + +end;' +language plpgsql; + + +select define_function_args('cc_staff__name', 'staff_id'); + +create function cc_staff__name (integer) +returns varchar as ' +declare + p_staff_id alias for $1; +begin + return person__name(p_staff_id); +end; +' language plpgsql; Index: openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql 20 Nov 2005 23:51:10 -0000 1.1 +++ openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql 4 Dec 2005 07:30:45 -0000 1.2 @@ -31,42 +31,127 @@ drop function inline_0 (); +-- content_item subtype create table cc_uos ( - uos_id integer - constraint cc_uos_uos_id_fk - references acs_objects(object_id) - constraint cc_uos_uos_id_pk primary key, - coordinator_id integer - constraint cc_uos_coordinator_id_fk - references users(user_id) - constraint cc_uos_coordinator_id_nn - not null, - uos_name varchar(256) - constraint cc_uos_uos_name_nn not null - constraint cc_uos_uos_name_un unique, - uos_code varchar(256), - department_id integer, - faculty_id integer + uos_id integer + constraint cc_uos_uos_id_fk + references cr_items(item_id) + on delete cascade + constraint cc_uos_uos_id_pk + primary key, + package_id integer, + -- denormalised from cr_items + parent_id integer, + live_revision_id integer, + -- denormalised from cc_uos_revisions + uos_code varchar(256) + constraint cc_uos_uos_code_nn not null + constraint cc_uos_uos_code_un unique, + uos_name varchar(256) + constraint cc_uos_uos_name_nn not null + constraint cc_uos_uos_name_un unique, + unit_coordinator_id integer + constraint cc_uos_coordinator_id_fk + references users(user_id) + constraint cc_uos_coordinator_id_nn + not null ); -select define_function_args('cc_uos__new', 'uos_id,owner_id,object_type,name,code,department,faculty,creation_user,creation_ip,context_id'); +-- Create the UoS content_revision +create table cc_uos_revisions ( + uos_revision_id integer + constraint cc_uos_rev_pk + primary key + constraint cc_uos_rev_uos_id_fk + references cr_revisions(revision_id) + on delete cascade, + uos_code varchar(256) + constraint cc_uos_rev_uos_code_nn not null + constraint cc_uos_rev_uos_code_un unique, + uos_name varchar(256) + constraint cc_uos_rev_uos_name_nn not null + constraint cc_uos_rev_uos_name_un unique, + credit_value integer, + semester varchar(32), + online_course_content varchar(256), + unit_coordinator_id integer + constraint cc_uos_rev_coordinator_id_fk + references users(user_id) + constraint cc_uos_rev_coordinator_id_nn + not null, + contact_hours varchar(256), + assessments varchar(512), + core_uos_for varchar(512), + recommended_uos_for varchar(512), + prerequisites varchar(256), + objectives text, + outcomes text, + syllabus text, + syllabus_format varchar(256) +); -create function cc_uos__new(integer, integer, varchar, varchar, varchar, varchar, varchar, integer, varchar, integer) -returns integer as' +-- Create the UoS revision content type. +select content_type__create_type ( + 'cc_uos_revision', + 'content_revision', + 'UoS Revision', + 'UoS Revisions', + 'cc_uos_revisions', + 'uos_revision_id', + 'content_revision.revision_name' +); + +-- TODO: variable assignment +select define_function_args('cc_uos__new', 'uos_id,owner_id,object_type,name,code,department,faculty,creation_user,creation_ip,context_id'); + +create function cc_uos__new( + integer, -- uos_id + varchar, -- uos_code + varchar, -- uos_name + integer, -- unit_coordinator_id + integer, -- credit_value + varchar, -- semester + varchar, -- online_course_content + varchar, -- contact_hours + varchar, -- assessments + varchar, -- core_uos_for + varchar, -- recommended_uos_for + varchar, -- prerequisites + text, -- objectives + text, -- outcomes + text, -- syllabus + varchar, -- syllabus_format + integer, -- creation_user + varchar, -- creation_ip + integer, -- context_id + varchar, -- item_subtype + varchar -- content_type +) returns integer as' declare - p_uos_id alias for $1; - p_owner_id alias for $2; - p_object_type alias for $3; - p_name alias for $4; - p_code alias for $5; - p_department alias for $6; - p_faculty alias for $7; - p_creation_user alias for $8; - p_creation_ip alias for $9; - p_context_id alias for $10; + p_uos_id alias for $1; + p_uos_code alias for $2; + p_uos_name alias for $3; + p_unit_coordinator_id alias for $4; + p_credit_value alias for $5; + p_semester alias for $6; + p_online_course_content alias for $7; + p_contact_hours alias for $8; + p_assessments alias for $9; + p_core_uos_for alias for $10; + p_recommended_uos_for alias for $11; + p_prerequisites alias for $12; + p_objectives alias for $13; + p_outcomes alias for $14; + p_syllabus alias for $15; + p_syllabus_format alias for $16; + p_creation_user alias for $17; + p_creation_ip alias for $18; + p_context_id alias for $19; + p_item_subtype alias for $20; + p_content_type alias for $21; v_uos_id cc_uos.uos_id%TYPE; begin @@ -84,7 +169,7 @@ PERFORM acs_permission__grant_permission( v_uos_id, - p_owner_id, + p_unit_coordinator_id, ''admin'' );