Index: openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql,v diff -u -r1.1.2.1 -r1.1.2.2 --- openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql 7 Apr 2007 22:50:28 -0000 1.1.2.1 +++ openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql 8 Apr 2007 17:16:03 -0000 1.1.2.2 @@ -3,22 +3,22 @@ alter table as_item_rels add constraint as_item_rels_item_fk foreign key (item_rev_id) references acs_objects(object_id) on delete cascade; alter table as_item_rels add constraint as_item_rels_target_fk foreign key (target_rev_id) references acs_objects(object_id) on delete cascade; -alter table as_item_section_map drop constraint as_item_section_map_item_id_fk; -alter table as_item_section_map drop constraint as_item_section_map_section_id_fk; -alter table as_item_section_map add constraint as_item_section_map_item_id_fk foreign key (as_item_id) references as_items(as_item_id) on delete cascade; -alter table as_item_section_map add constraint as_item_section_map_section_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade; +alter table as_item_section_map drop constraint as_item_smap_i_id_fk; +alter table as_item_section_map drop constraint as_item_smap_s_id_fk; +alter table as_item_section_map add constraint as_item_smap_i_id_fk foreign key (as_item_id) references as_items(as_item_id) on delete cascade; +alter table as_item_section_map add constraint as_item_smap_s_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade; -alter table as_item_choices drop constraint as_item_choices_content_value_fk; +alter table as_item_choices drop constraint as_item_choices_content_fk; alter table as_item_choices drop constraint as_item_choices_id_fk; alter table as_item_choices drop constraint as_item_choices_parent_id_fk; -alter table as_item_choices add constraint as_item_choices_content_value_fk foreign key (content_value) references cr_revisions(revision_id) on delete cascade; +alter table as_item_choices add constraint as_item_choices_content_fk foreign key (content_value) references cr_revisions(revision_id) on delete cascade; alter table as_item_choices add constraint as_item_choices_id_fk foreign key (choice_id) references cr_revisions(revision_id) on delete cascade; alter table as_item_choices add constraint as_item_choices_parent_id_fk foreign key (mc_id) references as_item_type_mc(as_item_type_id) on delete cascade; -alter table as_item_sa_answers drop constraint as_item_sa_answers_id_fk; -alter table as_item_sa_answers drop constraint as_item_sa_answers_parent_id_fk; -alter table as_item_sa_answers add constraint as_item_sa_answers_id_fk foreign key (choice_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_sa_answers add constraint as_item_sa_answers_parent_id_fk foreign key (answer_id) references as_item_type_sa(as_item_type_id) on delete cascade; +alter table as_item_sa_answers drop constraint as_item_sa_answ_id_fk; +alter table as_item_sa_answers drop constraint as_item_sa_answ_parent_id_fk; +alter table as_item_sa_answers add constraint as_item_sa_answ_id_fk foreign key (choice_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_sa_answers add constraint as_item_sa_answ_parent_id_fk foreign key (answer_id) references as_item_type_sa(as_item_type_id) on delete cascade; alter table as_item_help_map drop constraint as_item_help_map_as_item_id_fk; alter table as_item_help_map drop constraint as_item_help_map_message_id_fk; @@ -39,58 +39,261 @@ alter table as_sessions add constraint as_sessions_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade; alter table as_sessions add constraint as_sessions_subject_id_fk foreign key (subject_id) references persons(person_id) on delete cascade; -alter table as_section_data drop constraint as_section_data_section_data_id_fk; -alter table as_section_data add constraint as_section_data_section_data_id_fk foreign key (section_data_id) references cr_revisions(revision_id) on delete cascade; -alter table as_section_data drop constraint as_section_data_section_id_fk; -alter table as_section_data add constraint as_section_data_section_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade; -alter table as_section_data drop constraint as_section_data_session_id_fk; -alter table as_section_data add constraint as_section_data_session_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade; +alter table as_section_data drop constraint as_section_data_id_fk; +alter table as_section_data add constraint as_section_data_id_fk foreign key (section_data_id) references cr_revisions(revision_id) on delete cascade; +alter table as_section_data drop constraint as_section_data_sect_id_fk; +alter table as_section_data add constraint as_section_data_sect_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade; +alter table as_section_data drop constraint as_section_data_sess_id_fk; +alter table as_section_data add constraint as_section_data_sess_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade; alter table as_section_data drop constraint as_section_data_staff_id_fk; alter table as_section_data add constraint as_section_data_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade; -alter table as_section_data drop constraint as_section_data_subject_id_fk; -alter table as_section_data add constraint as_section_data_subject_id_fk foreign key (subject_id) references persons(person_id) on delete cascade; +alter table as_section_data drop constraint as_section_data_subj_id_fk; +alter table as_section_data add constraint as_section_data_subj_id_fk foreign key (subject_id) references persons(person_id) on delete cascade; -alter table as_item_type_mc drop constraint as_item_type_mc_as_item_type_id_fk; -alter table as_item_type_mc add constraint as_item_type_mc_as_item_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_type_oq drop constraint as_item_type_oq_as_item_type_id_fk; -alter table as_item_type_oq add constraint as_item_type_oq_as_item_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_rb drop constraint as_item_display_rb_as_item_display_id_fk; -alter table as_item_display_rb add constraint as_item_display_rb_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_cb drop constraint as_item_display_cb_as_item_display_id_fk; -alter table as_item_display_cb add constraint as_item_display_cb_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_sb drop constraint as_item_display_sb_as_item_display_id_fk; -alter table as_item_display_sb add constraint as_item_display_sb_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_tb drop constraint as_item_display_tb_as_item_display_id_fk; -alter table as_item_display_tb add constraint as_item_display_tb_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_sa drop constraint as_item_display_sa_as_item_display_id_fk; -alter table as_item_display_sa add constraint as_item_display_sa_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_type_sa drop constraint as_item_type_sa_as_item_type_id_fk; -alter table as_item_type_sa add constraint as_item_type_sa_as_item_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_ta drop constraint as_item_display_ta_as_item_display_id_fk; -alter table as_item_display_ta add constraint as_item_display_ta_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_type_fu drop constraint as_item_type_fu_as_item_type_id_fk; -alter table as_item_type_fu add constraint as_item_type_fu_as_item_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_display_f drop constraint as_item_display_f_as_item_display_id_fk; -alter table as_item_display_f add constraint as_item_display_f_as_item_display_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_type_mc drop constraint as_item_type_mc_type_id_fk; +alter table as_item_type_mc add constraint as_item_type_mc_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_type_oq drop constraint as_item_type_oq_type_id_fk; +alter table as_item_type_oq add constraint as_item_type_oq_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_rb drop constraint as_item_display_rb_displ_id_fk; +alter table as_item_display_rb add constraint as_item_display_rb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_cb drop constraint as_item_display_cb_displ_id_fk; +alter table as_item_display_cb add constraint as_item_display_cb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_sb drop constraint as_item_display_sb_displ_id_fk; +alter table as_item_display_sb add constraint as_item_display_sb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_tb drop constraint as_item_display_tb_displ_id_fk; +alter table as_item_display_tb add constraint as_item_display_tb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_sa drop constraint as_item_display_sa_displ_id_fk; +alter table as_item_display_sa add constraint as_item_display_sa_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_type_sa drop constraint as_item_type_sa_type_id_fk; +alter table as_item_type_sa add constraint as_item_type_sa_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_display_ta drop constraint as_item_display_ta_displ_id_fk; +alter table as_item_display_ta add constraint as_item_display_ta_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade; alter table as_items drop constraint as_items_item_id_fk; alter table as_items add constraint as_items_item_id_fk foreign key (as_item_id) references cr_revisions(revision_id) on delete cascade; alter table as_section_display_types drop constraint as_section_display_types_id_fk; alter table as_section_display_types add constraint as_section_display_types_id_fk foreign key (display_type_id) references cr_revisions(revision_id) on delete cascade; -alter table as_assessments drop constraint as_assessments_assessment_id_fk; -alter table as_assessments add constraint as_assessments_assessment_id_fk foreign key (assessment_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_data drop constraint as_item_data_content_answer_fk; -alter table as_item_data add constraint as_item_data_content_answer_fk foreign key (content_answer) references cr_revisions(revision_id) on delete cascade; +alter table as_assessments drop constraint as_assessments_id_fk; +alter table as_assessments add constraint as_assessments_id_fk foreign key (assessment_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_data drop constraint as_item_data_content_fk; +alter table as_item_data add constraint as_item_data_content_fk foreign key (content_answer) references cr_revisions(revision_id) on delete cascade; alter table as_item_data drop constraint as_item_data_file_id_fk; alter table as_item_data add constraint as_item_data_file_id_fk foreign key (file_id) references cr_revisions(revision_id) on delete cascade; -alter table as_item_data drop constraint as_item_data_item_data_id_fk; -alter table as_item_data add constraint as_item_data_item_data_id_fk foreign key (item_data_id) references cr_revisions(revision_id) on delete cascade; +alter table as_item_data drop constraint as_item_data_id_fk; +alter table as_item_data add constraint as_item_data_id_fk foreign key (item_data_id) references cr_revisions(revision_id) on delete cascade; alter table as_item_data drop constraint as_item_data_item_id; alter table as_item_data add constraint as_item_data_item_id foreign key (as_item_id) references as_items(as_item_id) on delete cascade; alter table as_item_data drop constraint as_item_data_section_id; alter table as_item_data add constraint as_item_data_section_id foreign key (section_id) references as_sections(section_id) on delete cascade; -alter table as_item_data drop constraint as_item_data_session_id_fk; -alter table as_item_data add constraint as_item_data_session_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade; +alter table as_item_data drop constraint as_item_data_sess_id_fk; +alter table as_item_data add constraint as_item_data_sess_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade; alter table as_item_data drop constraint as_item_data_staff_id_fk; alter table as_item_data add constraint as_item_data_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade; -alter table as_item_data drop constraint as_item_data_subject_id_fk; -alter table as_item_data add constraint as_item_data_subject_id_fk foreign key (subject_id) references persons(person_id) on delete cascade; +alter table as_item_data drop constraint as_item_data_subj_id_fk; +alter table as_item_data add constraint as_item_data_subj_id_fk foreign key (subject_id) references persons(person_id) on delete cascade; + +-- -- File Upload Item +-- create table as_item_type_fu ( +-- as_item_type_id integer +-- constraint as_item_type_fu_type_id_pk +-- primary key +-- constraint as_item_type_fu_type_id_fk +-- references cr_revisions(revision_id) +-- on delete cascade +-- ); + +-- -- File Upload Display Type +-- create table as_item_display_f ( +-- as_item_display_id integer +-- constraint as_item_display_f_displ_id_pk +-- primary key +-- constraint as_item_display_f_displ_id_fk +-- references cr_revisions(revision_id) +-- on delete cascade, +-- -- field to specify other stuff like textarea dimensions +-- html_display_options varchar(50), +-- -- an abstraction of the real size value in "small","medium","large" +-- abs_size varchar(10), +-- -- the orientation between the "question part" of the Item (the title/subtext) and the "answer part" (beside-left, beside-right, bellow, above) +-- item_answer_alignment varchar(20) +-- ); + +-- alter table as_item_data add file_id integer +-- constraint as_item_data_file_id_fk +-- references cr_revisions(revision_id) +-- on delete cascade; + +-- insert into as_item_types_map (item_type, display_type) +-- values ('fu', 'f'); + + +create or replace package as_action +as + function new ( + action_id in acs_objects.object_id%TYPE default null, + name in as_actions.name%TYPE, + description in as_actions.description%TYPE, + tcl_code in as_actions.tcl_code%TYPE, + context_id in acs_objects.context_id%TYPE, + creation_user in acs_objects.creation_user%TYPE + ) return as_actions.action_id%TYPE; + procedure del ( + action_id in as_actions.action_id%TYPE + ); + procedure default_actions ( + context_id in acs_objects.context_id%TYPE, + creation_user in acs_objects.creation_user%TYPE + ); +end as_action; +/ +show errors; + +create or replace package body as_action +as + function new ( + action_id in acs_objects.object_id%TYPE default null, + name in as_actions.name%TYPE, + description in as_actions.description%TYPE, + tcl_code in as_actions.tcl_code%TYPE, + context_id in acs_objects.context_id%TYPE, + creation_user in acs_objects.creation_user%TYPE + ) return as_actions.action_id%TYPE + is + v_action_id as_actions.action_id%TYPE; + + begin + + v_action_id := acs_object.new ( + object_id => action_id, + object_type => 'as_action', + creation_user => creation_user, + creation_ip => null, + context_id => context_id + ); + + insert into as_actions (action_id,name,description,tcl_code) + values (v_action_id,name,description,tcl_code); + + return v_action_id; + end new; + + + + procedure del ( + action_id as_actions.action_id%TYPE + ) is + begin + + delete from as_action_params where action_id=as_action.del.action_id; + delete from as_actions where action_id = as_action.del.action_id; + acs_object.del(as_action.del.action_id); + + end del; + + + procedure default_actions ( + context_id in acs_objects.context_id%TYPE, + creation_user in acs_objects.creation_user%TYPE + + ) is + v_action_id as_actions.action_id%TYPE; + + begin + + v_action_id := new ( + action_id => null, + name => 'Register User', + description => 'Register new users', + tcl_code => 'set password [ad_generate_random_string] +db_transaction { +array set user_new_info [auth::create_user -username $user_name -email $email -first_names $first_names -last_name $last_name -password $password] +} + +set admin_user_id [as::actions::get_admin_user_id] +set administration_name [db_string admin_name "select first_names || '' '' || last_name from persons where +person_id = :admin_user_id"] + +set system_name [ad_system_name] +set system_url [ad_parameter -package_id [ad_acs_kernel_id] SystemURL ""]. +set admin_email [db_string unused "select email from parties where party_id = :admin_user_id"] +set message "$first_names $last_name, +You have been added as a user to $system_name +at $system_url +Login information: +Email: $email +Password: $password +(you may change your password after you log in) +Thank you, +$administration_name" +ns_sendmail "$email" "$admin_email" "You have been added as a user to [ad_system_name] at [ad_url]" "$message"', + context_id => context_id, + creation_user => creation_user + ); + + +insert into as_action_params (parameter_id, action_id,type, varname, description) +values (parameter_id_seq.nextval,v_action_id,'n','first_names','First Names of the User'); + +insert into as_action_params (parameter_id, action_id,type, varname, description) +values (parameter_id_seq.nextval,v_action_id,'n','last_name','Last Name of the User'); + +insert into as_action_params (parameter_id, action_id,type, varname, description) +values (parameter_id_seq.nextval,v_action_id,'n','email','Email of the User'); + +insert into as_action_params (parameter_id, action_id,type, varname, description) +values (parameter_id_seq.nextval,v_action_id,'n','user_name','User name of the User'); + +v_action_id:= new ( + action_id => null, + name => 'Event Registration', + description => 'Register user to event', + tcl_code => 'set user_id [ad_conn user_id] +events::registration::new -event_id $event_id -user_id $user_id', + context_id => context_id, + creation_user => creation_user + ); + + +insert into as_action_params (parameter_id, action_id,type, varname, description,query) +values (parameter_id_seq.nextval,v_action_id,'q','event_id','Event to add the user', 'select event_id,event_id from acs_events'); + +v_action_id:= new ( + action_id => null, + name => 'Add to Community', + description => 'Add user to a community', + tcl_code => 'set user_id [ad_conn user_id] +if { [exists_and_not_null subject_id] } { + set user_id $subject_id +} +dotlrn_privacy::set_user_guest_p -user_id $user_id -value "t" +dotlrn::user_add -can_browse -user_id $user_id +dotlrn_community::add_user_to_community -community_id $community_id -user_id $user_id +set community_name [db_string get_community_name { select pretty_name from dotlrn_communities where community_id = :community_id}] + +set subject "Your $community_name membership has been approved" +set message "Your $community_name membership has been approved. Please return to [ad_url] to log into [ad_system_name]." + +set email_from [ad_parameter -package_id [ad_acs_kernel_id] SystemOwner] + +db_1row select_user_info { select email, first_names, last_name from registered_users where user_id = :user_id} + +if [catch {ns_sendmail $email $email_from $subject $message} errmsg] { + ad_return_error \ + "Error sending mail" \ + "There was an error sending email to $email." +}', + context_id => context_id, + creation_user => creation_user + ); + + +insert into as_action_params (parameter_id, action_id,type, varname, description,query) +values (parameter_id_seq.nextval,v_action_id,'q','community_id','Community to add the user', +'select pretty_name,community_id from dotlrn_communities'); + + + end default_actions; + +end as_action; +/ +show errors; +