Index: openacs-4/packages/curriculum/sql/oracle/curriculum-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,45 @@ +-- packages/curriculum/sql/oracle/curriculum-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +@ curriculum-curriculum-create.sql +@ curriculum-element-create.sql +@ curriculum-element-package-create.sql +@ curriculum-curriculum-package-create.sql + + +-- Keep track of which elements a particular user has seen. +create table cu_user_element_map ( + user_id integer + constraint cu_user_element_map_user_id_nn + not null + constraint cu_user_element_map_user_id_fk + references users + on delete cascade, + element_id integer + constraint cu_user_element_map_elem_id_nn + not null + constraint cu_user_element_map_elem_id_fk + references cu_elements + on delete cascade, + curriculum_id integer + constraint cu_user_element_map_curr_id_nn + not null + constraint cu_user_element_map_curr_id_fk + references cu_curriculums + on delete cascade, + package_id integer + constraint cu_user_element_map_pack_id_nn + not null + constraint cu_user_element_map_pack_id_fk + references apm_packages + on delete cascade, + completion_date date + default sysdate + constraint cu_user_element_map_comp_da_nn + not null, + constraint cu_user_element_map_pk + primary key (user_id, element_id) +); Index: openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,60 @@ +-- packages/curriculum/sql/oracle/curriculum-curriculum-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-curriculum-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +create table cu_curriculums ( + curriculum_id integer + constraint cu_curriculums_curricul_id_fk + references acs_objects (object_id) + on delete cascade + constraint cu_curriculums_curricul_id_pk + primary key, + name varchar(200) + constraint cu_curriculums_name_nn + not null, + description clob, + desc_format varchar(200), + owner_id integer + constraint cu_curriculums_owner_id_nn + not null + -- owner can be any party, e.g., a group + constraint cu_curriculums_owner_id_fk + references parties (party_id), + package_id integer + constraint cu_curriculums_package_id_nn + not null + constraint cu_curriculums_package_id_fk + references apm_packages (package_id) + on delete cascade, + sort_key integer + constraint cu_curriculums_sort_key_nn + not null +); + +comment on table cu_curriculums is ' +A package instance of Curriculum may contain any number of curriculums. However, only one package instance may be mounted per subsite (This limitation is less of a problem in dotLRN where every class, club, department, etc., is a subsite). +'; + +comment on column cu_curriculums.desc_format is ' +Stores the format of the contents in the description column. The possible formats are defined in the richtext datatype in the form builder and may grow in number over time or change, which is why we do not bother to add a check constraint ... +'; + +create index cu_curriculums_package_id_idx on cu_curriculums(package_id); + +declare +begin + acs_object_type.create_type( + supertype => 'acs_object', + object_type => 'cu_curriculum', + pretty_name => 'Curriculum', + pretty_plural => 'Curriculums', + table_name => 'cu_curriculums', + id_column => 'curriculum_id', + package_name => 'cu_curriculum', + name_method => 'cu_curriculum.name' + ); +end; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,20 @@ +-- packages/curriculum/sql/oracle/curriculum-curriculum-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-curriculum-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +drop table cu_curriculums; + +-- Had to add this in order to cleanly drop the package when there +-- were curriculums created in the db. +delete from acs_objects where object_type = 'cu_curriculum'; + +declare +begin + acs_object_type.drop_type ( + object_type => 'cu_curriculum' + ); +end; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,126 @@ +-- packages/curriculum/sql/oracle/curriculum-curriculum-package-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-curriculum-package-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +create or replace package cu_curriculum +as + + function new ( + curriculum_id in cu_curriculums.curriculum_id%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cu_curriculum', + name in cu_curriculums.name%TYPE, +-- The description column is of type clob rather than varchar +-- in order to allow a maximum of 32K as opposed to varchar's 4K. +-- However, clob doesn't support the %TYPE syntax and clob isn't +-- a valid type in PLSQL. But since a varchar in PLSQL can be up +-- to 32K that is the explicit type we use here. + description in varchar default null, + desc_format in cu_curriculums.desc_format%TYPE, + owner_id in cu_curriculums.owner_id%TYPE, + package_id in cu_curriculums.package_id%TYPE, + sort_key in cu_curriculums.sort_key%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return cu_curriculums.curriculum_id%TYPE; + + function name ( + curriculum_id in cu_curriculums.curriculum_id%TYPE + ) return cu_curriculums.name%TYPE; + + procedure del ( + curriculum_id in cu_curriculums.curriculum_id%TYPE + ); + +end cu_curriculum; +/ +show errors + + +create or replace package body cu_curriculum +as + + function new ( + curriculum_id in cu_curriculums.curriculum_id%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cu_curriculum', + name in cu_curriculums.name%TYPE, + description in varchar default null, + desc_format in cu_curriculums.desc_format%TYPE, + owner_id in cu_curriculums.owner_id%TYPE, + package_id in cu_curriculums.package_id%TYPE, + sort_key in cu_curriculums.sort_key%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return cu_curriculums.curriculum_id%TYPE + is + v_curriculum_id cu_curriculums.curriculum_id%TYPE; + v_sort_key cu_curriculums.sort_key%TYPE; + begin + v_curriculum_id := acs_object.new( + object_id => curriculum_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => nvl(context_id, package_id) + ); + + if new.sort_key is null then + select nvl(max(sort_key)+1, 1) + into v_sort_key + from cu_curriculums + where package_id = new.package_id; + else + v_sort_key := new.sort_key; + end if; + + insert into cu_curriculums + (curriculum_id, name, description, desc_format, owner_id, package_id, sort_key) + values + (v_curriculum_id, name, description, desc_format, owner_id, package_id, v_sort_key); + + return v_curriculum_id; + end new; + + function name ( + curriculum_id in cu_curriculums.curriculum_id%TYPE + ) return cu_curriculums.name%TYPE + is + v_name cu_curriculums.name%TYPE; + begin + select name + into v_name + from cu_curriculums + where curriculum_id = cu_curriculum.name.curriculum_id; + + return v_name; + end name; + + procedure del ( + curriculum_id in cu_curriculums.curriculum_id%TYPE + ) + is + cursor c_element_cur is + select element_id + from cu_elements + where curriculum_id = cu_curriculum.del.curriculum_id; + begin + delete from acs_permissions + where object_id = cu_curriculum.del.curriculum_id; + + -- Delete all elements in the curriculum. + for v_element_val in c_element_cur loop + cu_element.del(v_element_val.element_id); + end loop; + + acs_object.delete(cu_curriculum.del.curriculum_id); + end del; + +end cu_curriculum; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-curriculum-package-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,7 @@ +-- packages/curriculum/sql/oracle/curriculum-curriculum-package-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-curriculum-package-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +drop package cu_curriculum; Index: openacs-4/packages/curriculum/sql/oracle/curriculum-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,16 @@ +-- packages/curriculum/sql/oracle/curriculum-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +-- Drop the mapping table. +drop table cu_user_element_map; + +-- Drop the cu_element object. +@ curriculum-element-drop.sql +@ curriculum-element-package-drop.sql + +-- Drop the cu_curriculum object. +@ curriculum-curriculum-drop.sql +@ curriculum-curriculum-package-drop.sql Index: openacs-4/packages/curriculum/sql/oracle/curriculum-element-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-element-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-element-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,69 @@ +-- packages/curriculum/sql/oracle/curriculum-element-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-element-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +create table cu_elements ( + element_id integer + constraint cu_elements_element_id_fk + references acs_objects (object_id) + on delete cascade + constraint cu_elements_element_id_pk + primary key, + curriculum_id integer + constraint cu_elements_curriculum_id_fk + references cu_curriculums (curriculum_id) + on delete cascade, + name varchar(200) + constraint cu_elements_name_nn + not null, + description clob, + desc_format varchar(200), + url varchar(400) + constraint cu_elements_url_nn + not null, + enabled_p char(1) + default 't' + constraint cu_elements_enabled_p_nn + not null + constraint cu_elements_enabled_p_ck + check (enabled_p in ('t','f')), + sort_key integer + constraint cu_elements_sort_key_nn + not null +); + +comment on table cu_elements is ' +An element is a pointer to a piece of content, local or external, that is part of a certain curriculum. +'; + +comment on column cu_elements.sort_key is ' +The relative position the element has within the curriculum. +'; + +create index cu_elements_curriculum_id_idx on cu_elements(curriculum_id); + +create view cu_elements_enabled +as + select ce.*, cc.package_id + from cu_elements ce, + cu_curriculums cc + where ce.curriculum_id = cc.curriculum_id + and ce.enabled_p = 't'; + +declare +begin + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'cu_element', + pretty_name => 'Curriculum Element', + pretty_plural => 'Curriculum Elements', + table_name => 'cu_elements', + id_column => 'element_id', + package_name => 'cu_element', + name_method => 'cu_element.name' + ); +end; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-element-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-element-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-element-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,21 @@ +-- packages/curriculum/sql/oracle/curriculum-element-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-element-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +drop view cu_elements_enabled; +drop table cu_elements; + +-- Had to add this in order to cleanly drop the package when +-- there were elements created in the db. +delete from acs_objects where object_type = 'cu_element'; + +declare +begin + acs_object_type.drop_type ( + object_type => 'cu_element' + ); +end; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,126 @@ +-- packages/curriculum/sql/oracle/curriculum-element-package-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-element-package-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +create or replace package cu_element +as + + function new ( + element_id in cu_elements.element_id%TYPE default null, + curriculum_id in cu_elements.curriculum_id%TYPE, + name in cu_elements.name%TYPE, +-- The description column is of type clob rather than varchar +-- in order to allow a maximum of 32K as opposed to varchar's 4K. +-- However, clob doesn't support the %TYPE syntax and clob isn't +-- a valid type in PLSQL. But since a varchar in PLSQL can be up +-- to 32K that is the explicit type we use here. + description in varchar default null, + desc_format in cu_elements.desc_format%TYPE, + url in cu_elements.url%TYPE, + enabled_p in cu_elements.enabled_p%TYPE default 't', + sort_key in cu_elements.sort_key%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cu_element', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return cu_elements.element_id%TYPE; + + function name ( + element_id in cu_elements.element_id%TYPE + ) return cu_elements.name%TYPE; + + procedure del ( + element_id in cu_elements.element_id%TYPE + ); + +end cu_element; +/ +show errors + + +create or replace package body cu_element +as + + function new ( + element_id in cu_elements.element_id%TYPE default null, + curriculum_id in cu_elements.curriculum_id%TYPE, + name in cu_elements.name%TYPE, +-- The 'description' column is of type clob rather than varchar +-- in order to allow a maximum of 32K as opposed to varchar's 4K. +-- However, clob doesn't support the %TYPE syntax and clob isn't +-- a valid type in PLSQL. But since a varchar in PLSQL can be up +-- to 32K that is the explicit type we use here. + description in varchar default null, + desc_format in cu_elements.desc_format%TYPE, + url in cu_elements.url%TYPE, + enabled_p in cu_elements.enabled_p%TYPE default 't', + sort_key in cu_elements.sort_key%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cu_element', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return cu_elements.element_id%TYPE + is + v_element_id cu_elements.element_id%TYPE; + v_sort_key cu_elements.sort_key%TYPE; + begin + v_element_id := acs_object.new ( + object_id => element_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => nvl(context_id, curriculum_id) + ); + + if new.sort_key is null then + select nvl(max(sort_key)+1, 1) + into v_sort_key + from cu_elements + where curriculum_id = new.curriculum_id; + else + v_sort_key := new.sort_key; + end if; + + insert into cu_elements + (element_id, curriculum_id, name, description, desc_format, url, enabled_p, sort_key) + values + (v_element_id, curriculum_id, name, description, desc_format, url, enabled_p, v_sort_key); + + return v_element_id; + end new; + + function name ( + element_id in cu_elements.element_id%TYPE + ) return cu_elements.name%TYPE + is + v_name cu_elements.name%TYPE; + begin + select name into v_name + from cu_elements + where element_id = name.element_id; + + return v_name; + end name; + + procedure del ( + element_id in cu_elements.element_id%TYPE + ) + is + begin + delete from acs_permissions + where object_id = cu_element.del.element_id; + + delete from cu_elements + where element_id = cu_element.del.element_id; + + acs_object.delete(element_id); + end del; + +end cu_element; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,7 @@ +-- packages/curriculum/sql/oracle/curriculum-element-package-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-element-package-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +drop package cu_element; Index: openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-create.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,25 @@ +-- packages/curriculum/sql/oracle/curriculum-privileges-create.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-privileges-create.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +declare +begin + acs_privilege.create_privilege('curriculum_create',null,null); + acs_privilege.create_privilege('curriculum_write',null,null); + acs_privilege.create_privilege('curriculum_delete',null,null); + acs_privilege.create_privilege('curriculum_read',null,null); + acs_privilege.create_privilege('curriculum_admin',null,null); + + -- add children + acs_privilege.add_child('create','curriculum_create'); + acs_privilege.add_child('write','curriculum_write'); + acs_privilege.add_child('delete','curriculum_delete'); + acs_privilege.add_child('read','curriculum_read'); + acs_privilege.add_child('admin','curriculum_admin'); + acs_privilege.add_child('curriculum_admin','curriculum_read'); + acs_privilege.add_child('curriculum_write','curriculum_read'); +end; +/ +show errors Index: openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/oracle/curriculum-privileges-drop.sql 26 May 2003 17:40:02 -0000 1.1 @@ -0,0 +1,25 @@ +-- packages/curriculum/sql/oracle/curriculum-privileges-drop.sql +-- +-- @author Ola Hansson (ola@polyxena.net) +-- @creation-date 2003-05-23 +-- @cvs-id $Id: curriculum-privileges-drop.sql,v 1.1 2003/05/26 17:40:02 olah Exp $ + +declare +begin + -- remove children + acs_privilege.remove_child('read','curriculum_read'); + acs_privilege.remove_child('create','curriculum_create'); + acs_privilege.remove_child('write','curriculum_write'); + acs_privilege.remove_child('delete','curriculum_delete'); + acs_privilege.remove_child('admin','curriculum_admin'); + acs_privilege.remove_child('curriculum_admin','curriculum_read'); + acs_privilege.remove_child('curriculum_write','curriculum_read'); + + acs_privilege.drop_privilege('curriculum_admin'); + acs_privilege.drop_privilege('curriculum_read'); + acs_privilege.drop_privilege('curriculum_create'); + acs_privilege.drop_privilege('curriculum_write'); + acs_privilege.drop_privilege('curriculum_delete'); +end; +/ +show errors