Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-create.sql,v diff -u -N -r1.5 -r1.6 --- openacs-4/contrib/packages/survey/sql/postgresql/survey-create.sql 2 Nov 2005 02:58:49 -0000 1.5 +++ openacs-4/contrib/packages/survey/sql/postgresql/survey-create.sql 14 Nov 2005 04:34:53 -0000 1.6 @@ -97,13 +97,13 @@ create table survey_templates ( -- until we got a better solution - template_id integer - constraint survey_templates_pk - primary key, - template_file varchar(400) - constraint survey_templates_tpl_file_un - unique, - description varchar(4000) + template_id integer + constraint survey_templates_pk + primary key, + template_file varchar(400) + constraint survey_templates_tpl_file_un + unique, + description varchar(4000) ); insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'plain','Plain'); @@ -113,7 +113,7 @@ create table surveys ( survey_id integer - constraint surveys_survey_id_fk + constraint surveys_survey_id_fk references acs_objects (object_id) on delete cascade constraint surveys_pk @@ -129,106 +129,109 @@ -- limit to one response per user single_response_p boolean not null, editable_p boolean not null, - single_section_p boolean not null, + single_section_p boolean not null, type varchar(20), display_type varchar(20), package_id integer - constraint surveys_package_id_nn - not null + constraint surveys_package_id_nn + not null constraint surveys_package_id_fk references apm_packages (package_id) on delete cascade, - public_p boolean default TRUE, - status_bar_color varchar(100) default null - constraint surveys_status_bar_colors - check(status_bar_color in ('blue','red','green','yellow')), - template integer - constraint surveys_template_fk - references survey_templates(template_id) + public_p boolean default TRUE, + status_bar_color varchar(100) default null + constraint surveys_status_bar_colors + check(status_bar_color in ('blue','red','green','yellow')), + template integer + constraint surveys_template_fk + references survey_templates(template_id) ); - create table survey_sections ( - section_id integer constraint survey_sections_section_id_fk - references acs_objects (object_id) + section_id integer + constraint survey_sections_section_id_fk + references acs_objects (object_id) constraint survey_sections_pk - primary key, - survey_id integer - constraint survey_sections_survey_id_nn - not null - constraint survey_sections_survey_id_fk - references surveys, - name varchar(4000) - constraint survey_sections_name_nn - not null, - description text, + primary key, + survey_id integer + constraint survey_sections_survey_id_nn + not null + constraint survey_sections_survey_id_fk + references surveys, + name varchar(4000) + constraint survey_sections_name_nn + not null, + description text, description_html_p boolean, - pretty_id varchar(100) + pretty_id varchar(100), + + sort_key integer default 0 not null, + branch_p boolean default FALSE, + -- are there any branches dependant from this section? + branched_p boolean default FALSE, + block_section_p boolean default FALSE, + page_break_p boolean default TRUE, + constraint survey_sections_pretty_id_un - unique (pretty_id, section_id), - sort_key integer default 0 not null, - branch_p boolean default FALSE, - -- are there any branches dependant from this section? - branched_p boolean default FALSE, - block_section_p boolean default FALSE, - page_break_p boolean default TRUE + unique (pretty_id, section_id) + ); create index survey_sections_survey_id_fk on survey_sections(survey_id); create table survey_predefined_questions ( - predefined_question_id integer - constraint survey_pq_pquestion_id_fk + predefined_question_id integer + constraint survey_pq_pquestion_id_fk references acs_objects (object_id) constraint survey_pq_pquestion_id_pk primary key, - question_text text - constraint survey_pq_question_text_nn - not null, - question_html_p boolean default FALSE, + question_text text + constraint survey_pq_question_text_nn + not null, + question_html_p boolean default FALSE, 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_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 text, - table_name varchar(100), - column_name varchar(100), - key_name varchar(100) + tcl text, + table_name varchar(100), + column_name varchar(100), + key_name varchar(100) ); create table survey_predef_question_choices ( - choice_id integer + choice_id integer constraint survey_pqc_choice_id_pk primary key, question_id integer - constraint survey_pqc_question_id_nn + constraint survey_pqc_question_id_nn not null constraint survey_pqc_question_id_fk references survey_predefined_questions(predefined_question_id), -- human readable label varchar(500) - constraint survey_pqc_label_nn + constraint survey_pqc_label_nn not null, -- might be useful for averaging or whatever, generally null numeric_value numeric, - -- lower is earlier + -- lower is earlier sort_order integer, presentation_alignment varchar(15) default 'right' constraint survey_pqc_pres_align_ck @@ -240,13 +243,13 @@ create table survey_questions ( question_id integer - constraint survey_q_question_id_fk + constraint survey_q_question_id_fk references acs_objects (object_id) on delete cascade constraint survey_q_question_id_pk primary key, section_id integer - constraint survey_q_section_id_fk + constraint survey_q_section_id_fk references survey_sections on delete cascade, sort_order integer @@ -255,7 +258,7 @@ question_text text constraint survey_q_question_text_nn not null, - question_html_p boolean default FALSE, + question_html_p boolean default FALSE, abstract_data_type varchar(30) constraint survey_q_abs_data_type_ck check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice','date','blob')), @@ -265,49 +268,50 @@ constraint survey_q_pres_type_nn not null constraint survey_q_pres_type_ck - check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')), + check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file','select_text','radio_text','checkbox_text')), -- for text, "small", "medium", "large" sizes -- for textarea, "rows=X cols=X" presentation_options varchar(50), presentation_alignment varchar(15) constraint survey_q_pres_alignment_ck check(presentation_alignment in ('below','beside')), - summary_type varchar(20) - constraint survey_q_summary_type check - (summary_type in ('sum','avg','concat')), - -- describing the options of radio button set - answer_description varchar(100), - pretty_id varchar(100) + summary_type varchar(20) + constraint survey_q_summary_type check + (summary_type in ('sum','avg','concat')), + -- describing the options of radio button set + answer_description varchar(100), + pretty_id varchar(100), + predefined_question_id integer + constraint survey_q_pred_q_id_fk + references survey_predefined_questions, + -- unique table constraint constraint survey_q_pretty_id_un - unique (pretty_id, section_id), - predefined_question_id integer - constraint survey_q_pred_q_id_fk - references survey_predefined_questions + unique (pretty_id, section_id) ); create index survey_q_sort_order on survey_questions(sort_order); create index survey_q_active_p on survey_questions(active_p); create table survey_block_sections ( - block_section_id integer primary key, - section_id integer + block_section_id integer primary key, + section_id integer constraint survey_b_q_section_id_fk references survey_sections, - answer_description text + answer_description text ); create table survey_block_questions ( - block_section_id integer + block_section_id integer constraint survey_b_q_section_id_fk references survey_block_sections, - choice_id integer - constraint survey_b_q_choice_id_nn - not null, - label text, + choice_id integer + constraint survey_b_q_choice_id_nn + not null, + label text, sort_order integer constraint survey_b_q_sort_order_nn not null, - numeric_value numeric + numeric_value numeric ); create sequence survey_block_section_id_seq; @@ -319,33 +323,33 @@ create table survey_question_choices ( choice_id integer - constraint survey_qc_choice_id_nn + constraint survey_qc_choice_id_nn not null constraint survey_qc_choice_id_pk primary key, - predef_choice_id integer - constraint survey_qc_predef_choice_id_fk - references survey_predef_question_choices(choice_id), + predef_choice_id integer + constraint survey_qc_predef_choice_id_fk + references survey_predef_question_choices(choice_id), question_id integer - constraint survey_qc_question_id_nn + constraint survey_qc_question_id_nn not null constraint survey_qc_question_id_fk references survey_questions on delete cascade, -- human readable label text - constraint survey_qc_label_nn + constraint survey_qc_label_nn not null, -- might be useful for averaging or whatever, generally null numeric_value numeric, -- lower is earlier sort_order integer, - presentation_alignment varchar(15) default 'right' - constraint survey_qc_pres_align_ck - check (presentation_alignment in ('left','right')), - more_info_type varchar(30) default '' - constraint survey_qc_more_info_ck - check (more_info_type in ('','varchar','number')) + presentation_alignment varchar(15) default 'right' + constraint survey_qc_pres_align_ck + check (presentation_alignment in ('left','right')), + more_info_type varchar(30) default '' + constraint survey_qc_more_info_ck + check (more_info_type in ('','varchar','number')) ); @@ -357,38 +361,38 @@ create table survey_conditions ( condition_id integer - constraint survey_conditions_pk - primary key, + constraint survey_conditions_pk + primary key, question_id integer - constraint survey_conditions_question_id_nn - not null - constraint survey_conditions_question_id_fk - references survey_questions, + constraint survey_conditions_question_id_nn + not null + constraint survey_conditions_question_id_fk + references survey_questions, -- like question_responses - choice_id integer - constraint survey_conditions_choice_id_fk - references survey_question_choices, + choice_id integer + constraint survey_conditions_choice_id_fk + references survey_question_choices, boolean_answer char(1), constraint survey_c_boolean_answer_ck - check(boolean_answer in ('t','f')) + check(boolean_answer in ('t','f')) ); create table survey_branches ( branch_id integer - constraint survey_branches_pk - primary key, + constraint survey_branches_pk + primary key, section_id integer - constraint survey_branches_section_id_fk - references survey_sections, + constraint survey_branches_section_id_fk + references survey_sections, after integer - constraint survey_branches_after_nn - not null - constraint survey_branches_after_fk - references survey_sections, + constraint survey_branches_after_nn + not null + constraint survey_branches_after_fk + references survey_sections, -- null means always condition integer - constraint survey_branches_condition_fk - references survey_conditions + constraint survey_branches_condition_fk + references survey_conditions ); -- this records a response by one user to one survey @@ -397,21 +401,21 @@ -- email if someone comments on it, etc.) create table survey_responses ( response_id integer - constraint survey_resp_response_id_fk + constraint survey_resp_response_id_fk references acs_objects (object_id) on delete cascade constraint survey_resp_response_id_pk primary key, - initial_response_id integer - constraint survey_resp_initial_fk + initial_response_id integer + constraint survey_resp_initial_fk references survey_responses(response_id), - survey_id integer - constraint survey_resp_survey_id_fk + survey_id integer + constraint survey_resp_survey_id_fk references surveys on delete cascade, title varchar(100), notify_on_comment_p boolean, - finished_p boolean default FALSE + finished_p boolean default FALSE ); -- this table stores the answers to each question for a survey @@ -420,36 +424,36 @@ create table survey_question_responses ( response_id integer - constraint survey_qr_response_id_nn + constraint survey_qr_response_id_nn not null constraint survey_qr_response_id_fk references survey_responses on delete cascade, question_id integer - constraint survey_qr_question_id_nn + constraint survey_qr_question_id_nn not null constraint survey_qr_question_id_fk references survey_questions on delete cascade, -- if the user picked a canned response choice_id integer - constraint survey_qr_choice_id_fk + constraint survey_qr_choice_id_fk references survey_question_choices on delete cascade, boolean_answer boolean, clob_answer text, number_answer numeric, varchar_answer text, date_answer timestamptz, - attachment_answer integer - constraint survey_q_response_item_id_fk + attachment_answer integer + constraint survey_q_response_item_id_fk references cr_revisions(revision_id) on delete cascade, -- file name including extension but not path attachment_file_name varchar(500), attachment_file_type varchar(100), -- this is a MIME type (e.g., image/jpeg) attachment_file_extension varchar(50), -- e.g., "jpg" - constraint surv_quest_resp_uq unique(response_id,question_id,choice_id) + constraint surv_quest_resp_uq unique(response_id,question_id,choice_id) ); create index survey_q_r_choice_id on survey_question_responses(choice_id); @@ -460,29 +464,29 @@ (integer) returns text as ' declare - v_answers text; - first_p integer; - choice_cursor RECORD; - v_question_id alias for $1; + v_answers text; + first_p integer; + choice_cursor RECORD; + v_question_id alias for $1; begin - v_answers := ''''; - first_p := 0; + v_answers := ''''; + first_p := 0; - for choice_cursor in - SELECT cast (label as text) - from survey_question_choices - where question_id = v_question_id - order by sort_order - loop - if first_p = 0 then - v_answers := choice_cursor.label::text; - first_p := 1; - else - v_answers := v_answers || '','' || choice_cursor.label::text; - end if; - end loop; + for choice_cursor in + SELECT cast (label as text) + from survey_question_choices + where question_id = v_question_id + order by sort_order + loop + if first_p = 0 then + v_answers := choice_cursor.label::text; + first_p := 1; + else + v_answers := v_answers || '','' || choice_cursor.label::text; + end if; + end loop; - return v_answers; + return v_answers; end' language 'plpgsql';