Index: openacs-4/packages/assessment/lib/item-show-mc-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/lib/item-show-mc-oracle.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/assessment/lib/item-show-mc-oracle.xql 7 Jan 2005 16:10:44 -0000 1.1 +++ openacs-4/packages/assessment/lib/item-show-mc-oracle.xql 30 Jan 2005 14:16:14 -0000 1.2 @@ -10,7 +10,7 @@ c.text_value, c.content_value, r2.title as content_filename, r2.title || ' (' || r2.content_length || ' bytes)' as content_name from cr_revisions r, as_item_choices c, cr_revisions r2 - where c.content_value(+) = r2.revision_id + where c.content_value = r2.revision_id(+) and r.revision_id = c.choice_id and c.mc_id = :as_item_type_id order by c.sort_order Index: openacs-4/packages/assessment/sql/oracle/assessment-checks-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-checks-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-checks-create.sql 28 Jan 2005 20:37:19 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-checks-create.sql 30 Jan 2005 14:16:14 -0000 1.2 @@ -22,63 +22,63 @@ begin attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'name', pretty_name => 'Name', pretty_plural => 'Names', datatype => 'string' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'action_p', pretty_name => 'Action_p', pretty_plural => 'Action_p', datatype => 'string' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'section_id_from', pretty_name => 'From', pretty_plural => 'from', datatype => 'integer' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'section_id_to', pretty_name => 'To', pretty_plural => 'to', datatype => 'integer' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'check_sql', pretty_name => 'check_sql', pretty_plural => 'check_sql', datatype => 'string' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'description', pretty_name => 'Description', pretty_plural => 'descriptions', datatype => 'string' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'postcheck_p', pretty_name => 'postcheck_p', pretty_plural => 'postcheck_p', datatype => 'string' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'item_id', pretty_name => 'item_id', pretty_plural => 'item_id', datatype => 'integer' ); attr_id := acs_attribute.create_attribute ( - object_type => 'inter_item_check', + object_type => 'as_inter_item_check', attribute_name => 'assessment_id', pretty_name => 'assessment_id', pretty_plural => 'assessment_id', Index: openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql 6 Dec 2004 12:06:56 -0000 1.2 +++ openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql 30 Jan 2005 14:16:14 -0000 1.3 @@ -42,6 +42,9 @@ check (percent_score <= 100) ); +create index as_sessions_ass_idx on as_sessions (assessment_id); +create index as_sessions_subj_idx on as_sessions (subject_id); + --Assessment Section Data: tracks the state of each Section in the Assessment. create table as_section_data ( section_data_id integer @@ -61,9 +64,18 @@ references persons(person_id), staff_id integer constraint as_section_data_staff_id_fk - references users(user_id) + references users(user_id), + points integer, + -- when the subject initiated the section + creation_datetime date, + -- when the final submission produced a complete section + completed_datetime date ); +create unique index as_section_data_pk2 on as_section_data (session_id, section_id); +create unique index as_section_data_pk3 on as_section_data (section_id, session_id); +create index as_section_data_subj_idx on as_section_data (subject_id); + -- Assessment Item Data: is the "long skinny table" where all the primary data go create table as_item_data ( item_data_id integer @@ -85,13 +97,12 @@ as_item_id integer constraint as_item_data_item_id references as_items(as_item_id), + section_id integer + constraint as_item_data_section_id + references as_sections(section_id), is_unknown_p char(1) default 'f' constraint as_item_data_unknown_p_ck check (is_unknown_p in ('t','f')), - -- references as_item_choices - choice_id_answer integer - constraint as_item_data_choice_id_fk - references as_item_choices(choice_id), boolean_answer char(1) constraint as_item_data_bool_ck check (boolean_answer in ('t','f')), @@ -106,9 +117,94 @@ constraint as_item_data_content_fk references cr_revisions, -- This field stores the signed entered data - signed_data varchar(500) - --percent_score integer - -- constraint as_item_data_percent_ck - --check (percent_score <=100) + signed_data varchar(500), + points integer -- to do: figure out how attachment answers should be supported; the Attachment package is still in need of considerable help. Can we rely on it here? ); + +create index as_item_data_pk2 on as_item_data (session_id, section_id, as_item_id); +create index as_item_data_pk3 on as_item_data (as_item_id, section_id, session_id); +create index as_item_data_subj_idx on as_item_data (subject_id); + +-- here the selected choices are stored +create table as_item_data_choices ( + item_data_id integer + constraint as_idata_cho_data_id_fk + references as_item_data, + -- references as_item_choices + choice_id integer + constraint as_idata_cho_choice_id_fk + references as_item_choices, + constraint as_idata_choices_pk + primary key (item_data_id, choice_id) +); + +create unique index as_idata_choices_pk2 on as_item_data_choices (choice_id, item_data_id); + +-- here the order of the displayed sections is stored per session +create table as_session_sections ( + session_id integer + constraint as_sess_sect_session_fk + references as_sessions, + section_id integer + constraint as_sess_sect_section_fk + references as_sections, + sort_order integer, + constraint as_sess_sections_pk + primary key (session_id, section_id) +); + +create unique index as_sess_sections_pk2 on as_session_sections (section_id, session_id); + +-- here the order of the displayed items is stored per session +create table as_session_items ( + session_id integer + constraint as_sess_items_session_fk + references as_sessions, + section_id integer + constraint as_sess_items_section_fk + references as_sections, + as_item_id integer + constraint as_sess_items_item_fk + references as_items, + sort_order integer, + constraint as_sess_items_pk + primary key (session_id, section_id, as_item_id) +); + +create unique index as_sess_items_pk2 on as_session_items (as_item_id, section_id, session_id); + +-- here the order of the displayed item choices is stored per session +create table as_session_choices ( + session_id integer + constraint as_sess_cho_session_fk + references as_sessions, + section_id integer + constraint as_sess_cho_section_fk + references as_sections, + as_item_id integer + constraint as_sess_cho_item_fk + references as_items, + choice_id integer + constraint as_sess_cho_choice_fk + references as_item_choices, + sort_order integer, + constraint as_sess_choices_pk + primary key (session_id, section_id, as_item_id, choice_id) +); + +create unique index as_sess_choices_pk2 on as_session_choices (choice_id, as_item_id, section_id, session_id); + +-- here all references to answers of a session are stored +create table as_session_item_map ( + session_id integer + constraint as_sess_imap_session_fk + references as_sessions, + item_data_id integer + constraint as_sess_imap_item_data_fk + references as_item_data, + constraint as_sess_imap_pk + primary key (session_id, item_data_id) +); + +create unique index as_sess_imap_pk2 on as_session_item_map (item_data_id, session_id); Index: openacs-4/packages/assessment/sql/oracle/assessment-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/assessment/sql/oracle/assessment-create.sql 28 Jan 2005 20:37:19 -0000 1.2 +++ openacs-4/packages/assessment/sql/oracle/assessment-create.sql 30 Jan 2005 14:16:14 -0000 1.3 @@ -9,7 +9,7 @@ @ assessment-item-create.sql @ assessment-section-create.sql @ assessment-collected-data-create.sql -@ assessment-files.sql +@ assessment-types-create.sql @ assessment-checks-create.sql @ assessment-actions-create.sql @ assessment-checks-package-create.sql \ No newline at end of file Index: openacs-4/packages/assessment/sql/oracle/assessment-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-drop.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/assessment/sql/oracle/assessment-drop.sql 28 Jan 2005 20:37:19 -0000 1.3 +++ openacs-4/packages/assessment/sql/oracle/assessment-drop.sql 30 Jan 2005 14:16:14 -0000 1.4 @@ -1,37 +1,51 @@ -drop table as_item_types_map; - -drop table as_item_choices; -drop table as_item_attributes; -drop table as_item_localized; - -drop table as_item_section_map; - -drop table as_items; -drop table as_item_display_types; -drop table as_item_type_attributes; -drop table as_item_types; - -drop table as_assessment_section_map; -drop table as_assessments; -drop table as_sections; -drop table as_section_display_types; +drop sequence action_log_id_seq; +drop table as_actions_log; drop table as_param_map; - drop table as_action_params; -drop table as_actions_log; drop table as_action_map; drop table as_actions; - drop package as_inter_item_check; - declare begin - acs_object_type.drop_type (object_type => 'as_inter_item_checks'); + acs_object_type.drop_type (object_type => 'as_inter_item_check'); end; / show errors; - drop table as_inter_item_checks; +drop table as_item_rels; +drop table as_item_types_map; + +drop table as_session_item_map; +drop table as_session_choices; +drop table as_session_items; +drop table as_session_sections; +drop table as_item_data_choices; +drop table as_item_data; +drop table as_section_data; +drop table as_sessions; + +drop table as_item_section_map; +drop table as_assessment_section_map; +drop table as_assessment_styles; +drop table as_assessments; +drop table as_sections; +drop table as_section_display_types; + +drop table as_item_help_map; +drop table as_messages; +drop table as_item_sa_answers; +drop table as_item_choices; +drop table as_items; + +drop table as_item_display_ta; +drop table as_item_display_tb; +drop table as_item_display_sb; +drop table as_item_display_sa; +drop table as_item_display_cb; +drop table as_item_display_rb; +drop table as_item_type_sa; +drop table as_item_type_oq; +drop table as_item_type_mc; Index: openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql 6 Dec 2004 12:06:56 -0000 1.2 +++ openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql 30 Jan 2005 14:16:14 -0000 1.3 @@ -16,8 +16,6 @@ subtext varchar(500), -- a short label for use in data output header rows, etc field_code varchar(500), - -- some descriptive text - definition clob, -- whether Item must be answered (default value, can be overriden) required_p char(1) default 'f' constraint as_items_required_p_ck @@ -26,12 +24,12 @@ data_type varchar(50), -- optional max number of seconds to perform Item max_time_to_complete integer, - -- a denormalization to cache the generated "widget" for the Item (NB: when any change is made to an as_item_choice related to an as_item, this will have to be updated!) - adp_chunk varchar(500), -- right feedback feedback_right clob, -- wrong feedback - feedback_wrong clob + feedback_wrong clob, + -- number of points for item; might be used for defining difficulty levels + points integer ); -- contains additional information for all multiple choices (radiobutton, checkbox) @@ -68,12 +66,16 @@ check (correct_answer_p in ('t','f')), -- the order this choice will appear with regards to the MC item. sort_order integer, + -- fixed position in display. 0 for default, negative values relative to end + fixed_position integer, -- this is where points are stored percent_score integer constraint as_item_choices_percent_ck check (percent_score <= 100) ); +create index as_i_choices_order_idx on as_item_choices (mc_id, sort_order); + -- Short Answer Answers create table as_item_sa_answers ( choice_id integer Index: openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql 16 Dec 2004 09:37:42 -0000 1.3 +++ openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql 30 Jan 2005 14:16:14 -0000 1.4 @@ -7,35 +7,29 @@ --Section Display Types: define types of display for an groups of Items. create table as_section_display_types ( - section_display_type_id integer + display_type_id integer constraint as_section_display_types_id_pk - primary key, - -- name - section_type_name varchar(25), - -- all-items; one-item-per-page; variable (get item groups from mapping table) - pagination_style varchar(25) - constraint as_section_disptyp_pagin_nn - not null, + primary key + constraint as_section_display_types_id_fk + references cr_revisions(revision_id), + -- number of items displayed per page + num_items integer, + -- adp template + adp_chunk clob, -- whether this Section defines a branch point or whether this Section simply transitions to the next Section branched_p char(1) default 'f' constraint as_section_disptyp_bra_p_ck check (branched_p in ('t','f')), - -- the pattern by which 2..n Items are laid out when displayed (horizontal, vertical, matrix_col-row, matrix_row-col) - item_orientation varchar(25) default 'horizontal' - constraint as_section_disptyp_orient_ck - check (item_orientation in ('horizontal','vertical','matrix_col-row','matrix_row-col')), - -- whether to display labels of the Items - item_labels_as_headers_p char(1) default 't' - constraint as_section_disptyp_labels_p_ck - check (item_labels_as_headers_p in ('t','f')), - -- May actually be superfluous - presentation_type varchar(25), - -- the orientation between the "section description part" of the Section and the group of Items (beside-left, beside-right, bellow, above) - item_aligment varchar(25) - constraint as_section_disptyp_align_ck - check (item_aligment in ('beside_left','beside_right','below','above')), - -- other stuff like the grid dimensions - display_options varchar(25) + -- whether the back button is not allowed to work + back_button_p char(1) default 't' + constraint as_section_disptyp_bbu_p_ck + check (back_button_p in ('t','f')), + -- whether each answer has to be submitted via a seperate button + submit_answer_p char(1) default 'f' + constraint as_section_disptyp_san_p_ck + check (submit_answer_p in ('t','f')), + -- order in which the items will appear (randomized, alphabetical, order_of_entry) + sort_order_type varchar(20) ); -- Sections: represents logically-grouped set of items @@ -45,21 +39,15 @@ primary key constraint as_sections_section_id_fk references cr_revisions(revision_id), - section_display_type_id integer - constraint as_sections_display_type_id_fk - references as_section_display_types (section_display_type_id), - -- text used for identification and selection in admin pages, not for end-user pages - definition clob, + display_type_id integer + constraint as_sections_display_type_id_fk + references as_section_display_types (display_type_id), -- text displayed on user pages instructions clob, - -- Maybe this isnt really useful - required_p char(1) default 't' - constraint as_sections_required_p_ck - check (required_p in ('t','f')), - -- References an item in the CR (for an image, audio file or video file) - content_value integer, - -- number of points for section - numeric_value integer, + -- number of items displayed + num_items integer, + -- number of points for section; might be used for defining difficulty levels + points integer, -- preset text to show user feedback_text clob, -- max number of seconds to perform Section @@ -115,6 +103,8 @@ wait_between_tries integer, -- how many minutes has the respondee to finish the assessment time_for_response integer, + -- ip mask for restricted access + ip_mask varchar(100), -- the feedback type which will be displayed to the respondee (all, none, correct, incorrect) show_feedback varchar(50) default 'all' constraint as_assessments_feedback_ck @@ -156,14 +146,18 @@ section_id integer constraint as_assessment_smap_s_id_fk references as_sections (section_id), - -- feedback - feedback_text clob, -- maximum time to complete a section max_time_to_complete integer, -- order in which a section will be displayed - sort_order integer + sort_order integer, + -- this is the relative weight of a section in an assessment + points integer, + constraint as_assessment_smap_pk primary key (assessment_id, section_id) ); +create unique index as_assessment_smap_pk2 on as_assessment_section_map (section_id, assessment_id); +create index as_assessment_smap_ord_idx on as_assessment_section_map (assessment_id, sort_order); + -- Item Section Map: defines the items of a section create table as_item_section_map ( as_item_id integer @@ -172,26 +166,20 @@ section_id integer constraint as_item_smap_s_id_fk references as_sections (section_id), - enabled_p char(1) default 't' - constraint as_item_smap_enabled_p_ck - check (enabled_p in ('t','f')), -- whether Item must be answered required_p char(1) default 'f' constraint as_item_smap_required_p_ck check (required_p in ('t','f')), - item_default integer, - -- references CR - content_value integer - constraint as_item_smap_content_fk - references cr_revisions, - -- points for the item - numeric_value integer, - -- feedback for the item - feedback_text clob, -- maximum time to answer the item max_time_to_complete integer, - -- display code - adp_chunk varchar(25), -- order in which items appear in a section - sort_order integer + sort_order integer, + -- fixed position in display. 0 for default, negative values relative to section end + fixed_position integer, + -- this is the relative weight of an item in a section + points integer, + constraint as_item_smap_pk primary key (section_id, as_item_id) ); + +create unique index as_item_smap_pk2 on as_item_section_map (as_item_id, section_id); +create index as_item_smap_ord_idx on as_item_section_map (section_id, sort_order); Index: openacs-4/packages/assessment/sql/oracle/assessment-types-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-types-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/assessment/sql/oracle/assessment-types-create.sql 8 Dec 2004 15:30:33 -0000 1.2 +++ openacs-4/packages/assessment/sql/oracle/assessment-types-create.sql 30 Jan 2005 14:16:14 -0000 1.3 @@ -9,7 +9,8 @@ create table as_item_types_map ( item_type varchar(50), - display_type varchar(50) + display_type varchar(50), + constraint as_item_types_map_pk primary key (item_type, display_type) ); insert into as_item_types_map (item_type, display_type) Index: openacs-4/packages/assessment/sql/postgresql/assessment-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/postgresql/assessment-drop.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/assessment/sql/postgresql/assessment-drop.sql 28 Jan 2005 20:36:56 -0000 1.5 +++ openacs-4/packages/assessment/sql/postgresql/assessment-drop.sql 30 Jan 2005 14:16:14 -0000 1.6 @@ -1,23 +1,7 @@ -drop table as_item_types_map; - -drop table as_item_choices; -drop table as_item_attributes; -drop table as_item_localized; - -drop table as_item_section_map; - -drop table as_items; -drop table as_item_display_types; -drop table as_item_type_attributes; -drop table as_item_types; - -drop table as_assessment_section_map; -drop table as_assessments; -drop table as_sections; -drop table as_section_display_types; +drop sequence as_actions_log_action_log_id; +drop table as_actions_log; drop table as_param_map; drop table as_action_params; -drop table as_actions_log; drop table as_action_map; drop table as_actions; @@ -55,3 +39,37 @@ drop table as_inter_item_checks; +drop table as_item_rels; +drop table as_item_types_map; + +drop table as_session_item_map; +drop table as_session_choices; +drop table as_session_items; +drop table as_session_sections; +drop table as_item_data_choices; +drop table as_item_data; +drop table as_section_data; +drop table as_sessions; + +drop table as_item_section_map; +drop table as_assessment_section_map; +drop table as_assessment_styles; +drop table as_assessments; +drop table as_sections; +drop table as_section_display_types; + +drop table as_item_help_map; +drop table as_messages; +drop table as_item_sa_answers; +drop table as_item_choices; +drop table as_items; + +drop table as_item_display_ta; +drop table as_item_display_tb; +drop table as_item_display_sb; +drop table as_item_display_sa; +drop table as_item_display_cb; +drop table as_item_display_rb; +drop table as_item_type_sa; +drop table as_item_type_oq; +drop table as_item_type_mc; Index: openacs-4/packages/assessment/tcl/as-item-type-mc-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/tcl/as-item-type-mc-procs-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/assessment/tcl/as-item-type-mc-procs-oracle.xql 18 Jan 2005 12:42:37 -0000 1.2 +++ openacs-4/packages/assessment/tcl/as-item-type-mc-procs-oracle.xql 30 Jan 2005 14:16:14 -0000 1.3 @@ -9,8 +9,8 @@ r2.title as content_filename, i.content_type from as_session_choices sc, cr_revisions r, as_item_choices c, cr_revisions r2, cr_items i - where r2.revision_id = c.content_value (+) - and i.item_id = r2.item_id (+) + where r2.revision_id(+) = c.content_value + and i.item_id(+) = r2.item_id and sc.session_id = :session_id and sc.section_id = :section_id and sc.as_item_id = :as_item_id @@ -29,8 +29,8 @@ i.content_type from as_session_choices sc, cr_revisions r, as_item_choices c, cr_revisions r2, cr_items i - where r2.revision_id = c.content_value (+) - and i.item_id = r2.item_id (+) + where r2.revision_id(+) = c.content_value + and i.item_id(+) = r2.item_id and sc.session_id = :session_id and sc.section_id = :section_id and sc.as_item_id = :as_item_id @@ -48,8 +48,8 @@ r2.revision_id as content_rev_id, r2.title as content_filename, i.content_type from cr_revisions r, as_item_choices c, cr_revisions r2, cr_items i - where r2.revision_id = c.content_value (+) - and i.item_id = r2.item_id (+) + where r2.revision_id(+) = c.content_value + and i.item_id(+) = r2.item_id and c.mc_id = :type_id and r.revision_id = c.choice_id order by c.sort_order Index: openacs-4/packages/assessment/www/admin/item-edit-mc-choices-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/www/admin/Attic/item-edit-mc-choices-oracle.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/assessment/www/admin/item-edit-mc-choices-oracle.xql 7 Jan 2005 16:10:45 -0000 1.1 +++ openacs-4/packages/assessment/www/admin/item-edit-mc-choices-oracle.xql 30 Jan 2005 14:16:14 -0000 1.2 @@ -10,7 +10,7 @@ c.content_value, r2.title as content_filename, r2.title || ' (' || r2.content_length || ' bytes)' as content_name from cr_revisions r, as_item_choices c, cr_revisions r2 - where c.content_value(+) = r2.revision_id + where c.content_value = r2.revision_id(+) and r.revision_id = c.choice_id and c.mc_id = :mc_id order by c.sort_order