Index: openacs-4/contrib/packages/survey/sql/oracle/upgrade/survey-upgrade-0.1-0.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/upgrade/Attic/survey-upgrade-0.1-0.2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/survey/sql/oracle/upgrade/survey-upgrade-0.1-0.2.sql 27 Sep 2003 07:57:57 -0000 1.1 @@ -0,0 +1,225 @@ +begin + + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'survey_predefined_question', + pretty_name => 'Predefined Survey Question', + pretty_plural => 'Predefined Survey Questions', + table_name => 'SURVEY_PREDEFINED_QUESTIONS', + id_column => 'PREDEFINED_QUESTION_ID' + ); + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'survey_predefined_question', + pretty_name => 'Predefined Survey Question', + pretty_plural => 'Predefined Survey Questions', + table_name => 'SURVEY_PREDEFINED_QUESTIONS', + id_column => 'PREDEFINED_QUESTION_ID' + ); + +end; +/ +show errors + +create table survey_templates ( +-- until we got a better solution + template_id integer primary key, + template_file varchar(400) unique, + description varchar(4000) +); + +insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'plain','Plain'); +insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard','Standard Blue'); +insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard-lars','Silver'); +insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'two-column','Two Column'); + +alter table surveys add (public_p char(1) default 't' + constraint surveys_public_p_ck + check(public_p in ('t','f'))); + +alter table surveys add (status_bar_color varchar(100) default null + constraint surveys_status_bar_colors + check(status_bar_color in ('blue','red','green','yellow'))); + +alter table surveys add (template integer references survey_templates(template_id)); + +alter table survey_sections add (sort_key integer default 0 not null, + branch_p char(1) default 'f' + constraint survey_sections_branch_p_ck + check (branch_p in ('t','f'))); +alter table survey_sections add (branched_p char(1) default 'f' + constraint survey_sections_branched_p_ck + check (branched_p in ('t','f'))); +alter table survey_sections add (block_section_p char(1) default 'f' + constraint survey_sections_block_p_ck + check (block_section_p in ('t','f'))); +alter table survey_sections add (page_break_p char(1) default 't' + constraint survey_sections_break_p_ck + check (page_break_p in ('t','f'))); + +create table survey_predefined_questions ( + predefined_question_id constraint survey_pq_pquestion_id_fk + references acs_objects (object_id) + constraint survey_pq_pquestion_id_pk + primary key, + question_text clob + constraint survey_pq_question_text_nn + not null, + question_html_p char(1) default 'f' + constraint survey_pq_question_html_p_ck + check(question_html_p in ('t','f')), + abstract_data_type varchar(30) + constraint survey_pq_abs_data_type_ck + check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice', 'date','blob')), + presentation_type varchar(20) + constraint survey_pq_pres_type_nn + not null + constraint survey_pq_pres_type_ck + check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')), + -- for text, "small", "medium", "large" sizes + -- for textarea, "rows=X cols=X" + presentation_options varchar(50), + presentation_alignment varchar(15) + constraint survey_pq_pres_alignment_ck + check(presentation_alignment in ('below','beside')), + summary_type varchar(20) + constraint survey_pq_summary_type check + (summary_type in ('sum','avg','concat')), + action_type varchar(20) + constraint survey_pq_action_type_ck + check(action_type in ('tcl','db')), + tcl clob, + table_name varchar(100), + column_name varchar(100), + key_name varchar(100) + ); + + +create table survey_predef_question_choices ( + choice_id integer constraint survey_pqc_choice_id_nn + not null + constraint survey_pqc_choice_id_pk + primary key, + question_id constraint survey_pqc_question_id_nn + not null + constraint survey_pqc_question_id_fk + references survey_predefined_questions, + -- human readable + label varchar(500) constraint survey_pqc_label_nn + not null, + -- might be useful for averaging or whatever, generally null + numeric_value number, + -- lower is earlier + sort_order integer, + presentation_alignment varchar(15) default 'right' + constraint survey_pqc_pres_align_ck + check (presentation_alignment in ('left','right')), + more_info_type varchar(30) default '' + constraint survey_pqc_more_info_ck + check (more_info_type in ('','varchar','number')) +); + +alter table survey_questions add (question_html_p char(1) default 'f' + constraint survey_q_question_html_p_ck + check(question_html_p in ('t','f'))); +alter table survey_questions add (summary_type varchar(20) + constraint survey_q_summary_type check + (summary_type in ('sum','avg','concat'))); +alter table survey_questions add (answer_description varchar(100), + predefined_question_id constraint survey_q_pred_q_id_fk + references survey_predefined_questions); +create table survey_block_questions ( + block_section_id integer, + section_id integer + constraint survey_b_q_section_id_fk + references survey_sections, + choice_id integer + constraint survey_b_q_choice_id_nn + not null, + label varchar(200), + sort_order integer + constraint survey_b_q_sort_order_nn + not null +); +create sequence survey_block_section_id_seq; +create sequence survey_block_choice_id_seq; + + +alter table survey_question_choices add (predef_choice_id constraint survey_qc_predef_choice_id_fk + references survey_predef_question_choices); +alter table survey_question_choices add (presentation_alignment varchar(15) default 'right' + constraint survey_qc_pres_align_ck + check (presentation_alignment in ('left','right'))); +alter table survey_question_choices add (more_info_type varchar(30) default '' + constraint survey_qc_more_info_ck + check (more_info_type in ('','varchar','number'))); + +create sequence survey_branch_id_sequence start with 1; +create sequence survey_condition_id_sequence start with 1; + +create table survey_conditions ( + condition_id integer + constraint survey_cond_pk + primary key, + question_id constraint survey_cond_question_id_nn + not null + constraint survey_cond_question_id_fk + references survey_questions, + -- like question_responses + choice_id constraint survey_cond_choice_id_fk + references survey_question_choices, + boolean_answer char(1) + constraint survey_cond_boolean_answer_ck + check(boolean_answer in ('t','f')) +); + +create table survey_branches ( + branch_id integer + constraint survey_branches_pk + primary key, + section_id constraint survey_branches_section_id_fk + references survey_sections, + after constraint survey_branches_after_nn + not null + constraint survey_branches_after_fk + references survey_sections, + -- null means always + condition constraint survey_branches_condition_fk + references survey_conditions +); + +alter table survey_responses add (finished_p char(1) default 'f' + constraint survey_resp_finished_p_ck + check (finished_p in ('t','f'))); + +alter table survey_question_responses add (attachment_file_name varchar(500)); +alter table survey_question_responses add (attachment_file_type varchar(100)); +alter table survey_question_responses add (attachment_file_extension varchar(50), + constraint surv_quest_resp_uq unique(response_id,question_id,choice_id)); + +create or replace function survey_question_all_choices +(v_question_id in integer) return varchar +is + v_answers varchar(4000); + first_p integer; + cursor choice_cursor is + select label + from survey_question_choices + where question_id = v_question_id + order by sort_order; +begin + v_answers := ''; + first_p := 0; + for choice_rec in choice_cursor loop + if first_p = 0 then + v_answers := choice_rec.label; + first_p := 1; + else + v_answers := v_answers || ',' || choice_rec.label; + end if; + end loop; + + return v_answers; +end; +/ +show errors