-- ported to OpenACS 4 by Gilbert Wong (gwong@orchardlabs.com) on 2001-05-20 -- -- based on student work from 6.916 in Fall 1999 -- which was in turn based on problem set 4 -- in http://photo.net/teaching/one-term-web.html -- -- by philg@mit.edu and raj@alum.mit.edu on February 9, 2000 -- converted to ACS 4.0 by nstrug@arsdigita.com on 29th September 2000 -- modified for dotLRN/OpenACS4.5 and renamed from "simple-survey" to "survey" -- by dave@thedesignexperience.org on 13 July 2002 -- -- $Id: survey-create.sql,v 1.4 2005/01/13 13:54:39 jeffd Exp $ begin; -- temporarily drop this trigger to avoid a data-change violation -- on acs_privilege_hierarchy_index while updating the child privileges. -- drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; -- -- re-enable the trigger before the last insert to force the -- -- acs_privilege_hierarchy_index table to be updated. -- create trigger acs_priv_hier_ins_del_tr after insert or delete -- on acs_privilege_hierarchy for each row -- execute procedure acs_priv_hier_ins_del_tr (); commit; select acs_object_type__create_type ( 'survey', 'Survey', 'Surveys', 'acs_object', 'surveys', 'survey_id', null, 'f', null, 'survey__name' ); select acs_object_type__create_type ( 'survey_section', 'Survey Section', 'Survey Sections', 'acs_object', 'survey_sections', 'section_id', null, 'f', null, null ); select acs_object_type__create_type ( 'survey_predefined_question', 'Predefined Survey Question', 'Predefined Survey Questions', 'acs_object', 'survey_predefined_questions', 'predefined_question_id', null, 'f', null, null ); select acs_object_type__create_type ( 'survey_question', 'Survey Question', 'Survey Questions', 'acs_object', 'survey_questions', 'question_id', null, 'f', null, null ); select acs_object_type__create_type ( 'survey_response', 'Survey Response', 'Survey Responses', 'acs_object', 'survey_responses', 'response_id', null, 'f', null, null ); 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) ); 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'); create table surveys ( survey_id integer constraint surveys_survey_id_fk references acs_objects (object_id) on delete cascade constraint surveys_pk primary key, name varchar(4000) constraint surveys_name_nn not null, description text, description_html_p boolean not null, enabled_p boolean not null, -- limit to one response per user single_response_p boolean not null, editable_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_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) ); create table survey_sections ( 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, description_html_p boolean, 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 ); 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 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, 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 text, 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_pk primary key, question_id integer 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 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_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')) ); create table survey_questions ( question_id integer 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 references survey_sections on delete cascade, sort_order integer constraint survey_q_sort_order_nn not null, question_text text constraint survey_q_question_text_nn not null, 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')), required_p boolean, active_p boolean, presentation_type varchar(20) 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')), -- 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), predefined_question_id integer constraint survey_q_pred_q_id_fk references survey_predefined_questions ); 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 constraint survey_b_q_section_id_fk references survey_sections, answer_description text ); create table survey_block_questions ( 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, 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; -- for when a question has a fixed set of responses create sequence survey_choice_id_sequence; create table survey_question_choices ( choice_id integer 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), question_id integer 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 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')) ); create index survey_q_c_question_id on survey_question_choices(question_id); create index survey_q_c_sort_order on survey_question_choices(sort_order); create sequence survey_branch_id_sequence start 1; create sequence survey_condition_id_sequence start 1; create table survey_conditions ( condition_id integer 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, -- like question_responses 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')) ); create table survey_branches ( branch_id integer constraint survey_branches_pk primary key, section_id integer 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, -- null means always condition integer constraint survey_branches_condition_fk references survey_conditions ); -- this records a response by one user to one survey -- (could also be a proposal in which case we'll do funny -- things like let the user give it a title, send him or her -- email if someone comments on it, etc.) create table survey_responses ( response_id integer 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 references survey_responses(response_id), 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 ); -- this table stores the answers to each question for a survey -- we want to be able to hold different data types in one long skinny table -- but we also may want to do averages, etc., so we can't just use CLOBs create table survey_question_responses ( response_id integer 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 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 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 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) ); create index survey_q_r_choice_id on survey_question_responses(choice_id); create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer); create index survey_response_index on survey_question_responses (response_id, question_id); create or replace function survey_question_all_choices (integer) returns text as ' declare v_answers text; first_p integer; choice_cursor RECORD; v_question_id alias for $1; begin 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; return v_answers; end' language 'plpgsql'; \i survey-package-create.sql \i survey-notifications-init.sql