Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,45 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +\i curriculum-curriculum-create.sql +\i curriculum-element-create.sql +\i curriculum-element-package-create.sql +\i 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_element_id_nn + not null + constraint cu_user_element_map_elemen_id_fk + references cu_elements + on delete cascade, + curriculum_id integer + constraint cu_user_element_map_curriculum_id_nn + not null + constraint cu_user_element_map_curriculum_id_fk + references cu_curriculums + on delete cascade, + package_id integer + constraint cu_user_element_map_package_id_nn + not null + constraint cu_user_element_map_package_id_fk + references apm_packages + on delete cascade, + completion_date timestamptz + default current_timestamp + constraint cu_user_element_map_completion_date_nn + not null, + constraint cu_user_element_map_pk + primary key (user_id, element_id) +); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,58 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + + +create table cu_curriculums ( + curriculum_id integer + constraint cu_curriculums_curriculum_id_fk + references acs_objects (object_id) + on delete cascade + constraint cu_curriculums_curriculum_id_pk + primary key, + name varchar(200) + constraint cu_curriculums_name_nn + not null, + description text, + 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); + +select acs_object_type__create_type ( + 'cu_curriculum', -- object_type + 'Curriculum', -- pretty_name + 'Curriculums', -- pretty_plural + 'acs_object', -- supertype + 'cu_curriculums', -- table_name + 'curriculum_id', -- id_column + 'cu_curriculum', -- package_name + 'f', -- abstract_p + null, -- type_extension_table + 'cu_curriculum__name' -- name_method +); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,15 @@ +-- packages/curriculum/sql/postgresql/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:42:18 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'; + +select acs_object_type__drop_type ( + 'cu_curriculum', 'f' +); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,109 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +select define_function_args('cu_curriculum__new','curriculum_id,name,description,desc_format,owner_id,package_id,sort_key,object_type;cu_curriculum,creation_date,creation_user,creation_ip,context_id'); + +create or replace function cu_curriculum__new ( + integer, -- curriculum_id + varchar, -- name + text, -- description + varchar, -- desc_format + integer, -- owner_id + integer, -- package_id + integer, -- sort_key + varchar, -- object_type + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- context_id +) returns integer as ' +declare + p_curriculum_id alias for $1; -- default null + p_name alias for $2; + p_description alias for $3; -- default null + p_desc_format alias for $4; + p_owner_id alias for $5; + p_package_id alias for $6; + p_sort_key alias for $7; -- default null + p_object_type alias for $8; -- default ''cu_curriculum'' + p_creation_date alias for $9; -- default current_timestamp + p_creation_user alias for $10; -- default null + p_creation_ip alias for $11; -- default null + p_context_id alias for $12; -- default null + v_curriculum_id cu_curriculums.curriculum_id%TYPE; + v_sort_key cu_curriculums.sort_key%TYPE; +begin + v_curriculum_id := acs_object__new ( + p_curriculum_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + if p_sort_key is null then + select coalesce(max(sort_key)+1, 1) + into v_sort_key + from cu_curriculums + where package_id = p_package_id; + else + v_sort_key := p_sort_key; + end if; + + insert into cu_curriculums + (curriculum_id, name, description, desc_format, owner_id, package_id, sort_key) + values + (v_curriculum_id, p_name, p_description, p_desc_format, p_owner_id, p_package_id, v_sort_key); + + raise NOTICE ''Adding curriculum - %'',p_name; + + return v_curriculum_id; + +end;' language 'plpgsql'; + + +select define_function_args('cu_curriculum__del','curriculum_id'); + +create or replace function cu_curriculum__del ( + integer -- curriculum_id +) returns integer as ' +declare + p_curriculum_id alias for $1; + v_cur RECORD; + v_return integer := 0; +begin + delete from acs_permissions + where object_id = p_curriculum_id; + + for v_cur in select element_id + from cu_elements + where curriculum_id = p_curriculum_id + + loop + perform cu_element__del(v_cur.element_id); + end loop; + + raise NOTICE ''Deleting curriculum - %'',p_curriculum_id; + + perform acs_object__delete(p_curriculum_id); + + return v_return; + +end;' language 'plpgsql'; + + +select define_function_args('cu_curriculum__name','curriculum_id'); + +create or replace function cu_curriculum__name ( + integer -- curriculum_id +) returns varchar as ' +declare + p_curriculum_id alias for $1; +begin + return name from cu_curriculums where curriculum_id = p_curriculum_id; +end; +' language 'plpgsql'; Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-curriculum-package-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,7 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +select drop_package('cu_curriculum'); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,16 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +-- Drop the mapping table. +drop table cu_user_element_map; + +-- Drop the cu_element object. +\i curriculum-element-drop.sql +\i curriculum-element-package-drop.sql + +-- Drop the cu_curriculum object. +\i curriculum-curriculum-drop.sql +\i curriculum-curriculum-package-drop.sql Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-element-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-element-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-element-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,67 @@ +-- packages/curriculum/sql/postgresql/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:42:18 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 text, + desc_format varchar(200), + url varchar(400) + constraint cu_elements_url_nn + not null, + enabled_p character(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'; + +select acs_object_type__create_type ( + 'cu_element', -- object_type + 'Curriculum Element', -- pretty_name + 'Curriculum Elements', -- pretty_plural + 'acs_object', -- supertype + 'cu_elements', -- table_name + 'element_id', -- id_column + 'cu_element', -- package_name + 'f', -- abstract_p + null, -- type_extension_table + 'cu_element__name' -- name_method +); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-element-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-element-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-element-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,16 @@ +-- packages/curriculum/sql/postgresql/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:42:18 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'; + +select acs_object_type__drop_type ( + 'cu_element', 'f' +); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,105 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +select define_function_args('cu_element__new','element_id,curriculum_id,name,description,desc_format,url,enabled_p,sort_key,object_type;cu_element,creation_date,creation_user,creation_ip,context_id'); + +create or replace function cu_element__new ( + integer, -- element_id + integer, -- curriculum_id + varchar, -- name + text, -- description + varchar, -- desc_format + varchar, -- url + char, -- enabled_p + integer, -- sort_key + varchar, -- object_type + timestamptz, -- creation_date + integer, -- creation_user + varchar, -- creation_ip + integer -- context_id +) returns integer as ' +declare + p_element_id alias for $1; -- default null + p_curriculum_id alias for $2; + p_name alias for $3; + p_description alias for $4; + p_desc_format alias for $5; + p_url alias for $6; + p_enabled_p alias for $7; -- default ''t'' + p_sort_key alias for $8; -- default null + p_object_type alias for $9; -- default ''cu_element'' + p_creation_date alias for $10; -- default current_timestamp + p_creation_user alias for $11; -- default null + p_creation_ip alias for $12; -- default null + p_context_id alias for $13; -- default null + v_element_id cu_elements.element_id%TYPE; + v_sort_key cu_elements.sort_key%TYPE; +begin + v_element_id := acs_object__new ( + p_element_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id + ); + + if p_sort_key is null then + select coalesce(max(sort_key)+1, 1) + into v_sort_key + from cu_elements + where curriculum_id = p_curriculum_id; + else + v_sort_key := p_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, p_curriculum_id, p_name, p_description, p_desc_format, p_url, p_enabled_p, v_sort_key); + + raise NOTICE ''Adding element - %'',p_name; + + return v_element_id; + +end;' language 'plpgsql'; + + +select define_function_args('cu_element__del','element_id'); + +create or replace function cu_element__del ( + integer -- element_id +) returns integer as ' +declare + p_element_id alias for $1; + v_return integer := 0; +begin + delete from acs_permissions + where object_id = p_element_id; + + delete from cu_elements + where element_id = p_element_id; + + raise NOTICE ''Deleting element - %'',p_element_id; + + perform acs_object__delete(p_element_id); + + return v_return; + +end;' language 'plpgsql'; + + +select define_function_args('cu_element__name','element_id'); + +create or replace function cu_element__name ( + integer -- element_id +) returns varchar as ' +declare + p_element_id alias for $1; +begin + return name from cu_elements where element_id = p_element_id; +end; +' language 'plpgsql'; Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-element-package-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,7 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +select drop_package('cu_element'); Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-create.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,22 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +begin; + select acs_privilege__create_privilege('curriculum_create',null,null); + select acs_privilege__create_privilege('curriculum_write',null,null); + select acs_privilege__create_privilege('curriculum_delete',null,null); + select acs_privilege__create_privilege('curriculum_read',null,null); + select acs_privilege__create_privilege('curriculum_admin',null,null); + + -- add children + select acs_privilege__add_child('create','curriculum_create'); + select acs_privilege__add_child('write','curriculum_write'); + select acs_privilege__add_child('delete','curriculum_delete'); + select acs_privilege__add_child('read','curriculum_read'); + select acs_privilege__add_child('admin','curriculum_admin'); + select acs_privilege__add_child('curriculum_admin','curriculum_read'); + select acs_privilege__add_child('curriculum_write','curriculum_read'); +end; Index: openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/curriculum/sql/postgresql/curriculum-privileges-drop.sql 26 May 2003 17:42:18 -0000 1.1 @@ -0,0 +1,20 @@ +-- packages/curriculum/sql/postgresql/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:42:18 olah Exp $ + +-- remove children +select acs_privilege__remove_child(''read'',''curriculum_read''); +select acs_privilege__remove_child(''create'',''curriculum_create''); +select acs_privilege__remove_child(''write'',''curriculum_write''); +select acs_privilege__remove_child(''delete'',''curriculum_delete''); +select acs_privilege__remove_child(''admin'',''curriculum_admin''); +select acs_privilege__remove_child(''curriculum_admin'',''curriculum_read''); +select acs_privilege__remove_child(''curriculum_write'',''curriculum_read''); + +select acs_privilege__drop_privilege(''curriculum_admin''); +select acs_privilege__drop_privilege(''curriculum_read''); +select acs_privilege__drop_privilege(''curriculum_create''); +select acs_privilege__drop_privilege(''curriculum_write''); +select acs_privilege__drop_privilege(''curriculum_delete'');