-- -- -- -- @author Dave Bauer (dave@thedesignexperience.org) -- @creation-date 2004-12-16 -- @arch-tag: 1ac83d6e-2e0d-46f5-b02c-9d24873fdfff -- @cvs-id $Id: upgrade-5.1.4d1-5.1.4d2,v 1.2 2005/01/13 13:54:39 jeffd Exp $ -- -- upgrade to "complex" survey -- add predefined question type 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 ); -- add survey templates? 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'); -- update surveys table alter table surveys add public_p boolean; alter table surveys alter public_p set default true; alter table surveys add status_bar_color varchar(100) constraint surveys_status_bar_colors check(status_bar_color in ('blue','red','green','yellow')); alter table surveys alter status_bar_color set default null; alter table surveys add template integer constraint surveys_template_fk references survey_templates(template_id); alter table surveys alter description drop not null; alter table survey_sections alter description drop not null; -- update survey_sections table alter table survey_sections add pretty_id varchar(100); alter table survey_sections add constraint survey_sections_pretty_id_un unique (pretty_id, section_id); alter table survey_sections add sort_key integer; -- set sort_keys; alter table survey_sections alter sort_key set not null; alter table survey_sections alter sort_key set default 0; alter table survey_sections add branch_p boolean; alter table survey_sections alter branch_p set default FALSE; alter table survey_sections add branched_p boolean; alter table survey_sections alter branched_p set default FALSE; alter table survey_sections add block_section_p boolean; alter table survey_sections alter block_section_p set default FALSE; alter table survey_sections add page_break_p boolean; alter table survey_sections alter page_break_p set default TRUE; update survey_sections set sort_key=0, branch_p='f', branched_p='f', block_section_p='f', page_break_p='f'; create unique index survey_sections_sort_key_un on survey_sections (survey_id,sort_key); -- add predefined questions 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', 'select_text', 'radio_text', 'checkbox_text', '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')) ); -- update survey_questions table 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); alter table survey_questions add predefined_question_id integer constraint survey_q_pred_q_id_fk references survey_predefined_questions; alter table survey_questions add question_html_p boolean; alter table survey_questions alter question_html_p set default false; update survey_questions set question_html_p='f'; alter table survey_questions add pretty_id varchar(100); alter table survey_questions add constraint survey_q_pretty_id_un unique (pretty_id, section_id); -- add block questions create table survey_block_sections ( block_section_id integer primary key, section_id integer constraint survey_b_q_section_id_fk references survey_sections on delete cascade, answer_description text ); create table survey_block_questions ( block_section_id integer constraint survey_b_q_section_id_fk references survey_block_sections on delete cascade, 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 ); create sequence survey_block_section_id_seq; create sequence survey_block_choice_id_seq; -- update survey_questions alter table survey_question_choices add predef_choice_id integer constraint survey_qc_predef_choice_id_fk references survey_predef_question_choices(choice_id); alter table survey_question_choices add presentation_alignment varchar(15) constraint survey_qc_pres_align_ck check (presentation_alignment in ('left','right')); alter table survey_question_choices alter presentation_alignment set default 'right'; alter table survey_question_choices add more_info_type varchar(30) constraint survey_qc_more_info_ck check (more_info_type in ('varchar','number')); update survey_question_choices set presentation_alignment='right'; -- add branching 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 ); alter table survey_responses add finished_p boolean; alter table survey_responses alter finished_p set default FALSE; update survey_responses set finished_p='f'; -- update survey_question_responses table -- file name including extension but not path alter table survey_question_responses add attachment_file_name varchar(500); alter table survey_question_responses add attachment_file_type varchar(100); -- this is a MIME type (e.g., image/jpeg) alter table survey_question_responses add attachment_file_extension varchar(50); -- e.g., "jpg" -- plpgsql updates 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'; -- API for survey objects select define_function_args('survey__new','survey_id,name,description,description_html_p;f,single_response_p;f,editable_p;f,enabled_p;f,single_section_p;t,type;general,display_type,package_id,public_p,creation_user,context_id'); create or replace function survey__new (integer,varchar,text,boolean,boolean,boolean,boolean,boolean,varchar,varchar,integer,boolean,integer,integer) returns integer as ' declare new__survey_id alias for $1; -- default null new__name alias for $2; new__description alias for $3; new__description_html_p alias for $4; -- default f new__single_response_p alias for $5; -- default f new__editable_p alias for $6; -- default t new__enabled_p alias for $7; -- default f new__single_section_p alias for $8; -- default t new__type alias for $9; -- default general new__display_type alias for $10; new__package_id alias for $11; new__public_p alias for $12; -- default t new__creation_user alias for $13; -- default null new__context_id alias for $14; -- default null v_survey_id integer; begin v_survey_id := acs_object__new ( new__survey_id, ''survey'', now(), new__creation_user, null, new__context_id ); insert into surveys (survey_id, name, description, description_html_p, single_response_p, editable_p, enabled_p, single_section_p, type, display_type, package_id, public_p) values (v_survey_id, new__name, new__description, new__description_html_p, new__single_response_p, new__editable_p, new__enabled_p, new__single_section_p, new__type, new__display_type, new__package_id, new__public_p); return v_survey_id; end;' language 'plpgsql'; select define_function_args('survey__remove','survey_id'); create or replace function survey__remove (integer) returns integer as ' declare remove__survey_id alias for $1; v_response_row survey_responses%ROWTYPE; v_section_row survey_sections%ROWTYPE; begin for v_response_row in SELECT response_id from survey_responses where survey_id=remove__survey_id and initial_response_id is NULL loop PERFORM survey_response__remove(v_response_row.response_id); end loop; for v_section_row in select section_id from survey_sections where survey_id=remove__survey_id loop PERFORM survey_section__remove(v_section_row.section_id); end loop; delete from surveys where survey_id = remove__survey_id; PERFORM acs_object__delete(remove__survey_id); return 0; end;' language 'plpgsql'; create or replace function survey__name (integer) returns varchar as ' declare name__survey_id alias for $1; v_name surveys.name%TYPE; begin select into v_name name from surveys where survey_id = name__survey_id; return v_name; end;' language 'plpgsql'; -- API for survey_section objects select define_function_args('survey_section__new','section_id,survey_id,name,description,description_html_p;f,sort_key;0,branch_p;f,branched_p;f,block_section_p;f,page_break_p;t,creation_user,context_id,pretty_id'); create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer,integer) returns integer as ' declare new__section_id alias for $1; -- default null new__survey_id alias for $2; -- default null new__name alias for $3; -- default null new__description alias for $4; -- default null new__description_html_p alias for $5; -- default f new__sort_key alias for $6; -- default 0 new__branch_p alias for $7; -- default f new__branched_p alias for $8; -- default f new__block_section_p alias for $9; -- default f new__page_break_p alias for $10; -- default t new__creation_user alias for $11; -- default null new__context_id alias for $12; -- default null new__pretty_id alias for $13; v_section_id integer; begin v_section_id := acs_object__new ( new__section_id, ''survey_section'', now(), new__creation_user, null, new__context_id ); insert into survey_sections (section_id, survey_id, name, description, description_html_p, sort_key, branch_p, branched_p, page_break_p, block_section_p, pretty_id) values (v_section_id, new__survey_id, new__name, new__description, new__description_html_p, new__sort_key, new__branch_p, new__branched_p, new__page_break_p, new__block_section_p, new__pretty_id); return v_section_id; end;' language 'plpgsql'; create or replace function survey_section__remove (integer) returns integer as ' declare remove__section_id alias for $1; v_question_row survey_questions%ROWTYPE; begin delete from survey_branches where section_id=remove__section_id; delete from survey_conditions where question_id in (select question_id from survey_questions where section_id=remove__section_id); for v_question_row in select question_id from survey_questions where section_id=remove__section_id loop PERFORM survey_question__remove(v_question_row.question_id); end loop; delete from survey_sections where section_id = remove__section_id; PERFORM acs_object__delete(remove__section_id); return 0; end;' language 'plpgsql'; -- API for predefined questions create or replace function survey_predefined_question__new (integer,text,varchar,varchar,varchar,varchar,boolean,varchar,varchar,text,varchar,varchar,varchar,integer,integer) returns integer as ' declare new__predefined_question_id alias for $1; -- default null new__question_text alias for $2; -- default null new__abstract_data_type alias for $3; -- default null new__presentation_type alias for $4; -- default null new__presentation_options alias for $5; -- default null new__presentation_alignment alias for $6; -- default below new__question_html_p alias for $7; -- default f new__summary_type alias for $8; new__action_type alias for $9; -- default null new__tcl alias for $10; -- default null new__table_name alias for $11; -- default null new__column_name alias for $12; -- default null new__key_name alias for $13; -- default null new__creation_user alias for $14; -- default null new__context_id alias for $15; -- default null v_predefined_question_id survey_predefined_questions.predefined_question_id%TYPE; begin v_predefined_question_id := acs_object__new ( new__predefined_question_id, ''survey_predefined_question'', now(), new__creation_user, null, new__context_id ); insert into survey_predefined_questions (predefined_question_id,question_text,abstract_data_type,presentation_type,presentation_options,presentation_alignment,question_html_p,summary_type,action_type,tcl,table_name,column_name,key_name) values (v_predefined_question_id,new__question_text,new__abstract_data_type,new__presentation_type,new__presentation_options,new__presentation_alignment,new__question_html_p,new__summary_type,new__action_type,new__tcl,new__table_name,new__column_name,new__key_name); return v_predefined_question_id; end;' language 'plpgsql'; -- API for Survey questions create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer) returns integer as ' declare new__question_id alias for $1; -- default null new__section_id alias for $2; -- default null new__sort_order alias for $3; -- default null new__question_text alias for $4; -- default null new__abstract_data_type alias for $5; -- default null new__required_p alias for $6; -- default t new__active_p alias for $7; -- default new__presentation_type alias for $8; -- default null new__presentation_options alias for $9; -- default null new__presentation_alignment alias for $10; -- default below new__question_html_p alias for $11; -- default f new__summary_type alias for $12; new__answer_description alias for $13; new__predefined_question_id alias for $14; -- default null new__creation_user alias for $15; -- default null new__context_id alias for $16; -- default null v_question_id integer; begin v_question_id := acs_object__new ( new__question_id, ''survey_question'', now(), new__creation_user, null, new__context_id ); insert into survey_questions (question_id, section_id, sort_order, question_text, abstract_data_type, required_p, active_p, presentation_type, presentation_options, presentation_alignment, predefined_question_id) values (v_question_id, new__section_id, new__sort_order, new__question_text, new__abstract_data_type, new__required_p, new__active_p, new__presentation_type, new__presentation_options, new__presentation_alignment, new__predefined_question_id); return v_question_id; end;' language 'plpgsql'; select define_function_args('survey_question__new','question_id,section_id,sort_order,question_text,abstract_data_type,required_p;t,active_p,presentation_type,presentation_options,presentation_alignment;below,question_html_p;f,summary_type,answer_description,predefined_qustion_id,creation_user,context_id,pretty_id'); create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer,integer) returns integer as ' declare new__question_id alias for $1; -- default null new__section_id alias for $2; -- default null new__sort_order alias for $3; -- default null new__question_text alias for $4; -- default null new__abstract_data_type alias for $5; -- default null new__required_p alias for $6; -- default t new__active_p alias for $7; -- default new__presentation_type alias for $8; -- default null new__presentation_options alias for $9; -- default null new__presentation_alignment alias for $10; -- default below new__question_html_p alias for $11; -- default f new__summary_type alias for $12; new__answer_description alias for $13; new__predefined_question_id alias for $14; -- default null new__creation_user alias for $15; -- default null new__context_id alias for $16; -- default null new__pretty_id alias for $17; v_question_id integer; begin v_question_id := acs_object__new ( new__question_id, ''survey_question'', now(), new__creation_user, null, new__context_id ); insert into survey_questions (question_id, section_id, sort_order, question_text, abstract_data_type, required_p, active_p, presentation_type, presentation_options, presentation_alignment, predefined_question_id, pretty_id) values (v_question_id, new__section_id, new__sort_order, new__question_text, new__abstract_data_type, new__required_p, new__active_p, new__presentation_type, new__presentation_options, new__presentation_alignment, new__predefined_question_id, new__pretty_id); return v_question_id; end;' language 'plpgsql'; create or replace function survey_question__remove (integer) returns integer as ' declare remove__question_id alias for $1; begin delete from survey_question_responses where question_id=remove__question_id; delete from survey_question_choices where question_id=remove__question_id; delete from survey_questions where question_id = remove__question_id; PERFORM acs_object__delete(remove__question_id); return 0; end;' language 'plpgsql'; -- API for survey responses create or replace function survey_response__new(integer,integer,varchar,boolean,integer,varchar,integer,integer) returns integer as ' declare new__response_id alias for $1; -- default null new__survey_id alias for $2; -- default null new__title alias for $3; -- default null new__notify_on_comment_p alias for $4; -- default f new__creation_user alias for $5; -- default null new__creation_ip alias for $6; -- default null new__context_id alias for $7; -- default null new__initial_response_id alias for $8; -- default null v_response_id integer; begin v_response_id := acs_object__new ( new__response_id, ''survey_response'', now(), new__creation_user, new__creation_ip, new__context_id ); insert into survey_responses (response_id, survey_id, title, notify_on_comment_p, initial_response_id) values (v_response_id, new__survey_id, new__title, new__notify_on_comment_p, new__initial_response_id); return v_response_id; end;' language 'plpgsql'; --function initial_response_id create or replace function survey_response__initial_response_id(integer) returns integer as ' declare p_response_id alias for $1; v_initial_response_id integer; begin select into v_initial_response_id initial_response_id from survey_responses where response_id = p_response_id; if v_initial_response_id is NULL then v_initial_response_id := p_response_id; end if; return v_initial_response_id; end;' language 'plpgsql' stable; create or replace function survey_response__initial_user_id (integer) returns integer as ' declare p_response_id alias for $1; v_user_id integer; begin select into v_user_id creation_user from acs_objects where object_id = survey_response__initial_response_id(p_response_id); return v_user_id; end;' language 'plpgsql' stable; -- procedure delete create or replace function survey_response__remove(integer) returns integer as ' declare remove__response_id alias for $1; v_response_row survey_responses%ROWTYPE; begin -- delete from survey_branches where section_id in (select section_id from survey_sections where survey_id=remove.survey_id); -- delete from survey_conditions where question_id in (select question_id from survey_questions q, survey_sections s where s.section_id=q.section_id and s.survey_id=remove.survey_id); for v_response_row in select response_id from survey_responses where initial_response_id=remove__response_id loop PERFORM survey_response__del(v_response_row.response_id); end loop; PERFORM survey_response__del(remove__response_id); return 0; end;' language 'plpgsql'; create or replace function survey_response__del (integer) returns integer as ' declare del__response_id alias for $1; v_question_response_row survey_question_responses%ROWTYPE; begin for v_question_response_row in select item_id from survey_question_responses, cr_revisions where response_id=del__response_id and attachment_answer=revision_id loop PERFORM content_item__delete(item_id); end loop; delete from survey_question_responses where response_id=del__response_id; delete from survey_responses where response_id=del__response_id; PERFORM acs_object__delete(del__response_id); return 0; end;' language 'plpgsql'; create or replace function survey_response__boolean_answer (varchar,integer) returns varchar as ' declare boolean_answer__answer alias for $1; boolean_answer__question_id alias for $2; v_answer varchar(100); v_presentation_options survey_questions.presentation_options%TYPE; v_split_pos integer; begin if boolean_answer__answer is NOT NULL then select into v_presentation_options presentation_options from survey_questions where question_id=boolean_answer__question_id; v_split_pos:= instr(v_presentation_options, ''/''); if boolean_answer__answer = ''t'' then v_answer:=substr(v_presentation_options, 1, v_split_pos -1 ); end if; if boolean_answer__answer = ''f'' then v_answer:=substr(v_presentation_options, v_split_pos + 1 ); end if; else v_answer := ''; end if; return v_answer; end;' language 'plpgsql'; drop view survey_ques_responses_latest; drop view survey_responses_latest; create view survey_responses_latest as select sr.*, o.creation_date, o.creation_user, o2.creation_user as initial_user_id from survey_responses sr join acs_objects o on (sr.response_id = o.object_id) join acs_objects o2 on (coalesce(sr.initial_response_id, sr.response_id) = o2.object_id) join (select max(response_id) as response_id from survey_responses group by survey_response__initial_response_id(response_id)) latest on (sr.response_id = latest.response_id); create view survey_ques_responses_latest as select qr.* from survey_question_responses qr, survey_responses_latest r where qr.response_id=r.response_id;