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.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql 26 Nov 2004 20:44:54 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql 6 Dec 2004 12:06:56 -0000 1.2 @@ -45,19 +45,19 @@ --Assessment Section Data: tracks the state of each Section in the Assessment. create table as_section_data ( section_data_id integer - constraint as_section_data_section_data_id_pk + constraint as_section_data_id_pk primary key - constraint as_section_data_section_data_id_fk + constraint as_section_data_id_fk references cr_revisions(revision_id), session_id integer - constraint as_section_data_session_id_fk + constraint as_section_data_sess_id_fk references as_sessions(session_id), section_id integer - constraint as_section_data_section_id_fk + constraint as_section_data_sect_id_fk references as_sections(section_id), -- if subjects can't be "persons" then Assessment will have to define an as_subjects table for its own use. subject_id integer - constraint as_section_data_subject_id_fk + constraint as_section_data_subj_id_fk references persons(person_id), staff_id integer constraint as_section_data_staff_id_fk @@ -67,16 +67,16 @@ -- Assessment Item Data: is the "long skinny table" where all the primary data go create table as_item_data ( item_data_id integer - constraint as_item_data_item_data_id_pk + constraint as_item_data_id_pk primary key - constraint as_item_data_item_data_id_fk + constraint as_item_data_id_fk references cr_revisions(revision_id), session_id integer - constraint as_item_data_session_id_fk + constraint as_item_data_sess_id_fk references as_sessions(session_id), -- if subjects can't be "persons" then Assessment will have to define an as_subjects table for its own use subject_id integer - constraint as_item_data_subject_id_fk + constraint as_item_data_subj_id_fk references persons(person_id), -- missing foreign key staff_id integer @@ -86,29 +86,29 @@ constraint as_item_data_item_id references as_items(as_item_id), is_unknown_p char(1) default 'f' - constraint as_item_data_is_unknown_p_ck + 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_answer_fk + constraint as_item_data_choice_id_fk references as_item_choices(choice_id), boolean_answer char(1) - constraint as_item_data_boolean_ck + constraint as_item_data_bool_ck check (boolean_answer in ('t','f')), clob_answer clob, - numeric_answer number, + numeric_answer numeric, integer_answer integer, -- presumably can store both varchar and text datatypes text_answer varchar(500), timestamp_answer date, -- references cr_revisions content_answer integer - constraint as_item_data_content_answer_fk + 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_score_ck + -- constraint as_item_data_percent_ck --check (percent_score <=100) -- 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? ); Index: openacs-4/packages/assessment/sql/oracle/assessment-files.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/Attic/assessment-files.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-files.sql 26 Nov 2004 20:44:54 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-files.sql 6 Dec 2004 12:06:56 -0000 1.2 @@ -12,39 +12,3 @@ constraint as_files_file_id_fk references cr_revisions(revision_id) ); - -declare -begin - content_type.create_type ( - content_type => 'as_files', - super_type => 'content_revision', - pretty_name => 'Assessment File', - pretty_plural => 'Assessment Files', - table_name => 'as_files', - id_column => 'file_id', - name_method => null - ); -end; -/ - -declare - v_template_id integer; -begin - -- Create the (default) content type template - - v_template_id := content_template.new ( - name => 'as_files_default', - text => '@text;noquote@', - is_live => 't' - ); - - -- Register the template for the content type - - content_type__register_template( - content_type => 'as_files', - template_id => v_template_id, - use_context => 'public', - is_default => 't' - ); -end; -/ 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.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql 26 Nov 2004 20:44:54 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-item-create.sql 6 Dec 2004 12:06:56 -0000 1.2 @@ -47,14 +47,14 @@ -- which of the value columns has the information this Choice conveys data_type varchar(20), -- we can stuff both integers and real numbers here - numeric_value number, + numeric_value numeric, text_value varchar(500), boolean_value char(1) constraint as_item_choices_boolean_ck check (boolean_value in ('t','f')), -- references an item in the CR -- for an image, audio file, or video file content_value integer - constraint as_item_choices_content_value_fk + constraint as_item_choices_content_fk references cr_revisions, -- where optionally some preset feedback can be specified by the author feedback_text varchar(500), @@ -64,35 +64,35 @@ check (selected_p in ('t','f')), -- this choice is the correct answer correct_answer_p char(1) default 'f' - constraint as_item_choices_correct_answer_ck + constraint as_item_choices_canswer_ck check (correct_answer_p in ('t','f')), -- the order this choice will appear with regards to the MC item. sort_order integer, -- this is where points are stored percent_score integer - constraint as_item_choices_percent_score_ck + constraint as_item_choices_percent_ck check (percent_score <= 100) ); -- Short Answer Answers create table as_item_sa_answers ( choice_id integer - constraint as_item_sa_answers_id_pk + constraint as_item_sa_answ_id_pk primary key - constraint as_item_sa_answers_id_fk + constraint as_item_sa_answ_id_fk references cr_revisions(revision_id), answer_id integer - constraint as_item_sa_answers_parent_id_fk + constraint as_item_sa_answ_parent_id_fk references as_item_type_sa(as_item_type_id), -- integer vs. real number vs. text data_type varchar(20), -- shall the match be case sensitive case_sensitive_p char(1) default 't' - constraint as_item_sa_answers_case_sensitive_p_ck + constraint as_item_sa_answ_case_p_ck check (case_sensitive_p in ('t','f')), -- this is where points are stored percent_score integer - constraint as_item_sa_answers_percent_score_ck + constraint as_item_sa_answ_percent_ck check (percent_score <= 100), -- how is the comparison done (equal, contains, regexp) compare_by varchar(20), Index: openacs-4/packages/assessment/sql/oracle/assessment-item-type-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-item-type-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-item-type-create.sql 26 Nov 2004 20:44:54 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-item-type-create.sql 6 Dec 2004 12:06:56 -0000 1.2 @@ -8,17 +8,17 @@ -- Multiple Choice Item create table as_item_type_mc ( as_item_type_id integer - constraint as_item_type_mc_as_item_type_id_pk + constraint as_item_type_mc_type_id_pk primary key - constraint as_item_type_mc_as_item_type_id_fk + constraint as_item_type_mc_type_id_fk references cr_revisions(revision_id), -- (number of correct matches / number of total matches) *100% points. All or nothing will either give 100%, if all correct answers are given, or 0% else. increasing_p char(1) default 'f' - constraint as_item_type_mc_increasing_p_ck + constraint as_item_type_mc_incr_p_ck check (increasing_p in ('t','f')), -- if a negative percentage is allowed allow_negative_p char(1) default 'f' - constraint as_item_type_mc_allow_negative_p_ck + constraint as_item_type_mc_neg_p_ck check (allow_negative_p in ('t','f')), -- number of correct options num_correct_answers integer, @@ -29,9 +29,9 @@ -- Open Question Item create table as_item_type_oq ( as_item_type_id integer - constraint as_item_type_oq_as_item_type_id_pk + constraint as_item_type_oq_type_id_pk primary key - constraint as_item_type_oq_as_item_type_id_fk + constraint as_item_type_oq_type_id_fk references cr_revisions(revision_id), -- the content of this field will be prefilled in the response of the user taking the survey default_value varchar(500), @@ -42,9 +42,9 @@ -- Radiobutton display type create table as_item_display_rb ( as_item_display_id integer - constraint as_item_display_rb_as_item_display_id_pk + constraint as_item_display_rb_displ_id_pk primary key - constraint as_item_display_rb_as_item_display_id_fk + constraint as_item_display_rb_displ_id_fk references cr_revisions(revision_id), -- field to specify other stuff like textarea dimensions html_display_options varchar(50), @@ -61,9 +61,9 @@ -- Checkbox Display Type create table as_item_display_cb ( as_item_display_id integer - constraint as_item_display_cb_as_item_display_id_pk + constraint as_item_display_cb_displ_id_pk primary key - constraint as_item_display_cb_as_item_display_id_fk + constraint as_item_display_cb_displ_id_fk references cr_revisions(revision_id), -- field to specify other stuff like textarea dimensions html_display_options varchar(50), @@ -73,7 +73,7 @@ choice_label_orientation varchar(20), -- if it is allowed to select multiple values allow_multiple_p char(1) default 'f' - constraint as_item_type_cb_allow_multiple_p_ck + constraint as_item_type_cb_multiple_p_ck check (allow_multiple_p in ('t','f')), -- order in which the choices will appear (numerical, alphabetic, randomized or by order of entry) sort_order_type varchar(20), @@ -84,9 +84,9 @@ -- Textbox Display Type create table as_item_display_tb ( as_item_display_id integer - constraint as_item_display_tb_as_item_display_id_pk + constraint as_item_display_tb_displ_id_pk primary key - constraint as_item_display_tb_as_item_display_id_fk + constraint as_item_display_tb_displ_id_fk references cr_revisions(revision_id), -- field to specify other stuff like textarea dimensions html_display_options varchar(50), @@ -99,9 +99,9 @@ -- ShortAnswer Display Type: multiple textboxes in one item. create table as_item_display_sa ( as_item_display_id integer - constraint as_item_display_sa_as_item_display_id_pk + constraint as_item_display_sa_displ_id_pk primary key - constraint as_item_display_sa_as_item_display_id_fk + constraint as_item_display_sa_displ_id_fk references cr_revisions(revision_id), -- field to specify other stuff like textarea dimensions html_display_options varchar(50), @@ -114,26 +114,26 @@ -- Short Answer Item create table as_item_type_sa ( as_item_type_id integer - constraint as_item_type_sa_as_item_type_id_pk + constraint as_item_type_sa_type_id_pk primary key - constraint as_item_type_sa_as_item_type_id_fk + constraint as_item_type_sa_type_id_fk references cr_revisions(revision_id), -- (number of correct matches / number of total matches) *100% points. All or nothing will either give 100%, if all correct answers are given, or 0% else. increasing_p char(1) default 'f' - constraint as_item_type_sa_increasing_p_ck + constraint as_item_type_sa_incr_p_ck check (increasing_p in ('t','f')), -- if a negative percentage is allowed allow_negative_p char(1) default 'f' - constraint as_item_type_sa_allow_negative_p_ck + constraint as_item_type_sa_neg_p_ck check (allow_negative_p in ('t','f')) ); -- Textarea Display Type: multiple-line typed entry create table as_item_display_ta ( as_item_display_id integer - constraint as_item_display_ta_as_item_display_id_pk + constraint as_item_display_ta_displ_id_pk primary key - constraint as_item_display_ta_as_item_display_id_fk + constraint as_item_display_ta_displ_id_fk references cr_revisions(revision_id), -- field to specify other stuff like textarea dimensions html_display_options varchar(50), 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.1 -r1.2 --- openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql 26 Nov 2004 20:44:54 -0000 1.1 +++ openacs-4/packages/assessment/sql/oracle/assessment-section-create.sql 6 Dec 2004 12:06:56 -0000 1.2 @@ -14,25 +14,25 @@ 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_display_types_pagination_style_nn + constraint as_section_disptyp_pagin_nn not null, -- 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_display_types_branched_p_ck + 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_display_types_item_orientation_ck + 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_display_types_item_labels_as_headers_p_ck + 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_display_types_item_alignment_ck + 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) @@ -46,7 +46,7 @@ constraint as_sections_section_id_fk references cr_revisions(revision_id), section_display_type_id integer - constraint as_sections_section_display_type_id_fk + 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, @@ -72,31 +72,31 @@ create table as_assessments ( -- A revision_id inherited from cr_revisions assessment_id integer - constraint as_assessments_assessment_id_pk + constraint as_assessments_id_pk primary key - constraint as_assessments_assessment_id_fk + constraint as_assessments_id_fk references cr_revisions(revision_id), -- who is the "main" author and creator of this assessment creator_id integer, -- text that explains any specific steps the subject needs to follow instructions clob, -- whether this is a standalone assessment (like current surveys), or if it provides an "assessment service" to another OpenACS app, or a "web service" via SOAP, etc - mode varchar(25), + run_mode varchar(25), -- whether the creator of the assessment will have the possibility to see the personal details of the respondee or not anonymous_p char(1) default 'f' - constraint as_assessments_anonymous_p_ck + constraint as_assessments_anonym_p_ck check (anonymous_p in ('t','f')), -- whether the assessment can only be taken if a secure connection (https) is used secure_access_p char(1) default 'f' - constraint as_assessments_secure_access_p_ck + constraint as_assessments_secure_p_ck check (secure_access_p in ('t','f')), -- whether an assessment can reuse responses reuse_responses_p char(1) default 'f' - constraint as_assessments_reuse_responses_p_ck + constraint as_assessments_reuse_p_ck check (reuse_responses_p in ('t','f')), -- whether the name of the item is shown show_item_name_p char(1) default 'f' - constraint as_assessments_show_item_name_p_ck + constraint as_assessments_name_p_ck check (show_item_name_p in ('t','f')), -- the customizable entry page that will be displayed before the first response. entry_page varchar(50), @@ -117,11 +117,11 @@ time_for_response integer, -- the feedback type which will be displayed to the respondee (all, none, correct, incorrect) show_feedback varchar(50) default 'none' - constraint as_assessments_show_feedback_ck + constraint as_assessments_feedback_ck check (show_feedback in ('none', 'all', 'incorrect', 'correct')), -- how shall the navigation happen (default path, randomized, rule-based branching) section_navigation varchar(50) default 'default path' - constraint as_assessments_section_navigation_ck + constraint as_assessments_navigation_ck check (section_navigation in ('default path', 'randomized', 'rule-based branching')), -- differenciate between an assessment and a survey survey_p char(1) default 'f' @@ -140,21 +140,21 @@ form_template varchar(500), -- what kind of progress bar shall be displayed to the respondee while taking the assessment progress_bar varchar(20) default 'no' - constraint as_assessment_styles_progress_bar_ck + constraint as_assessment_styl_progress_ck check (progress_bar in ('no','blue','red','green', 'yellow')), -- select a presentation style (all question at once, one question per page, sectioned) presentation_style varchar(20) default 'all' - constraint as_assessment_styles_presentation_style_ck + constraint as_assessment_styl_present_ck check (presentation_style in ('all', 'one', 'sectioned')) ); -- Section Assessment Map: defines the sections of an assessment create table as_assessment_section_map ( assessment_id integer - constraint as_assessment_section_map_assessment_id_fk + constraint as_assessment_smap_a_id_fk references as_assessments (assessment_id), section_id integer - constraint as_assessment_section_map_section_id_fk + constraint as_assessment_smap_s_id_fk references as_sections (section_id), -- feedback feedback_text clob, @@ -167,22 +167,22 @@ -- Item Section Map: defines the items of a section create table as_item_section_map ( as_item_id integer - constraint as_item_section_map_item_id_fk + constraint as_item_smap_i_id_fk references as_items (as_item_id), section_id integer - constraint as_item_section_map_section_id_fk + constraint as_item_smap_s_id_fk references as_sections (section_id), enabled_p char(1) default 't' - constraint as_item_section_map_enabled_p_ck + 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_section_map_required_p_ck + constraint as_item_smap_required_p_ck check (required_p in ('t','f')), item_default integer, -- references CR content_value integer - constraint as_item_section_map_content_value_fk + constraint as_item_smap_content_fk references cr_revisions, -- points for the item numeric_value integer,