-- -- -- -- @author Dave Bauer (dave@thedesignexperience.org) -- @creation-date 2004-12-18 -- @arch-tag: 6f2a0457-27c9-4895-8612-326fa1295799 -- @cvs-id $Id: upgrade-5.1.4d1-5.1.4d2.sql,v 1.2 2005/01/13 13:54:39 jeffd Exp $ -- 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 surveys alter description drop not null; alter table survey_sections alter description drop not null; 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'))); 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); 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); 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); create table survey_block_sections ( block_section_id integer primary key, section_id integer constraint survey_b_s_section_id_fk references survey_sections, answer_description varchar(400) ); create table survey_block_questions ( block_section_id integer constraint survey_block_sec_fk foreign key (block_section_id) references survey_block_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 package survey as function new ( survey_id in surveys.survey_id%TYPE default null, name in surveys.name%TYPE, description in surveys.description%TYPE, description_html_p in surveys.description_html_p%TYPE default 'f', single_response_p in surveys.single_response_p%TYPE default 'f', editable_p in surveys.editable_p%TYPE default 't', enabled_p in surveys.enabled_p%TYPE default 'f', single_section_p in surveys.single_section_p%TYPE default 't', type in surveys.type%TYPE default 'general', display_type in surveys.display_type%TYPE default 'list', package_id in surveys.package_id%TYPE, object_type in acs_objects.object_type%TYPE default 'survey', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, public_p in surveys.public_p%TYPE default 't' ) return acs_objects.object_id%TYPE; procedure remove ( survey_id in surveys.survey_id%TYPE ); function name ( survey_id in surveys.survey_id%TYPE ) return varchar; end survey; / show errors -- survey_section create or replace package survey_section as function new ( section_id in survey_sections.section_id%TYPE default null, survey_id in survey_sections.survey_id%TYPE default null, name in survey_sections.name%TYPE default null, description in survey_sections.description%TYPE default null, description_html_p in survey_sections.description_html_p%TYPE default null, object_type in acs_objects.object_type%TYPE default 'survey_section', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, sort_key in survey_sections.sort_key%TYPE default 1, branch_p in survey_sections.branch_p%TYPE default 'f', branched_p in survey_sections.branched_p%TYPE default 'f', block_section_p in survey_sections.block_section_p%TYPE default 'f', page_break_p in survey_sections.page_break_p%TYPE default 't' ) return acs_objects.object_id%TYPE; procedure remove ( section_id in survey_sections.section_id%TYPE ); end survey_section; / show errors -- -- constructor for a predefined question -- create or replace package survey_predefined_question as function new ( question_id in survey_predefined_questions.question_id%TYPE default null, section_id in survey_predefined_questions.section_id%TYPE default null, sort_order in survey_predefined_questions.sort_order%TYPE default null, question_text in survey_predefined_questions.question_text%TYPE default null, abstract_data_type in survey_predefined_questions.abstract_data_type%TYPE default null, required_p in survey_predefined_questions.required_p%TYPE default 't', active_p in survey_predefined_questions.active_p%TYPE default 't', presentation_type in survey_predefined_questions.presentation_type%TYPE default null, presentation_options in survey_predefined_questions.presentation_options%TYPE default null, presentation_alignment in survey_predefined_questions.presentation_alignment%TYPE default 'below', object_type in acs_objects.object_type%TYPE default 'survey_predefined_question', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, question_html_p in survey_predefined_questions.%TYPE, action_type in survey_predefined_questions.action_type%TYPE null, tcl in survey_predefined_questions.tcl%TYPE default null, table_name in survey_predefined_questions.table_name%TYPE default null, column_name in survey_predefined_questions.column_name%TYPE default null, key_name in survey_predefined_questions.key_name%TYPE default null, summary_type in survey_predefined_questions.summary_type%TYPE default null ) return acs_objects.object_id%TYPE; end; / show errors -- -- constructor for a survey_question -- create or replace package survey_question as function new ( question_id in survey_questions.question_id%TYPE default null, section_id in survey_questions.section_id%TYPE default null, sort_order in survey_questions.sort_order%TYPE default null, question_text in survey_questions.question_text%TYPE default null, abstract_data_type in survey_questions.abstract_data_type%TYPE default null, required_p in survey_questions.required_p%TYPE default 't', active_p in survey_questions.active_p%TYPE default 't', presentation_type in survey_questions.presentation_type%TYPE default null, presentation_options in survey_questions.presentation_options%TYPE default null, presentation_alignment in survey_questions.presentation_alignment%TYPE default 'below', object_type in acs_objects.object_type%TYPE default 'survey_question', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, question_html_p in survey_questions.question_html_p%TYPE default 'f', summary_type in survey_questions.summary_type%TYPE default null, predefined_question_id in survey_questions.predefined_question_id%TYPE default null ) return acs_objects.object_id%TYPE; procedure remove ( question_id in survey_questions.question_id%TYPE ); end survey_question; / show errors -- -- constructor for a survey_response -- create or replace package survey_response as function new ( response_id in survey_responses.response_id %TYPE default null, survey_id in survey_responses.survey_id%TYPE default null, title in survey_responses.title%TYPE default null, notify_on_comment_p in survey_responses.notify_on_comment_p%TYPE default 'f', object_type in acs_objects.object_type%TYPE default 'survey_response', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, initial_response_id in survey_responses.initial_response_id%TYPE default null ) return acs_objects.object_id%TYPE; function initial_response_id ( response_id in survey_responses.response_id%TYPE ) return survey_responses.response_id%TYPE; function initial_user_id ( response_id in survey_responses.response_id%TYPE ) return acs_objects.creation_user%TYPE; procedure remove ( response_id in survey_responses.response_id%TYPE ); procedure del ( response_id in survey_responses.response_id%TYPE ); function boolean_answer ( answer varchar, question_id survey_questions.question_id%TYPE ) return varchar; end survey_response; / show errors -- next we define the package bodies create or replace package body survey as function new ( survey_id in surveys.survey_id%TYPE default null, name in surveys.name%TYPE, description in surveys.description%TYPE, description_html_p in surveys.description_html_p%TYPE default 'f', single_response_p in surveys.single_response_p%TYPE default 'f', editable_p in surveys.editable_p%TYPE default 't', enabled_p in surveys.enabled_p%TYPE default 'f', single_section_p in surveys.single_section_p%TYPE default 't', type in surveys.type%TYPE default 'general', display_type in surveys.display_type%TYPE default 'list', package_id in surveys.package_id%TYPE, object_type in acs_objects.object_type%TYPE default 'survey', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, public_p in surveys.public_p%TYPE default 't' ) return acs_objects.object_id%TYPE is v_survey_id surveys.survey_id%TYPE; begin v_survey_id := acs_object.new ( object_id => survey_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => 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 new; procedure remove ( survey_id surveys.survey_id%TYPE ) is 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 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 survey_section.remove(v_section_row.section_id); end loop; delete from surveys where survey_id=remove.survey_id; acs_object.del(survey_id); end remove; function name ( survey_id in surveys.survey_id%TYPE ) return varchar is v_name surveys.name%TYPE; begin select name into v_name from surveys where survey_id = name.survey_id; return v_name; end name; end survey; / show errors create or replace package body survey_section as function new ( section_id in survey_sections.section_id%TYPE default null, survey_id in survey_sections.survey_id%TYPE default null, name in survey_sections.name%TYPE default null, description in survey_sections.description%TYPE default null, description_html_p in survey_sections.description_html_p%TYPE default null, object_type in acs_objects.object_type%TYPE default 'survey_section', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, sort_key in survey_sections.sort_key%TYPE default 1, branch_p in survey_sections.branch_p%TYPE default 'f', branched_p in survey_sections.branched_p%TYPE default 'f', block_section_p in survey_sections.block_section_p%TYPE default 'f', page_break_p in survey_sections.page_break_p%TYPE default 't' ) return acs_objects.object_id%TYPE is v_section_id survey_sections.section_id%TYPE; begin v_section_id := acs_object.new ( object_id => section_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id ); insert into survey_sections (section_id, survey_id, name, description, description_html_p,sort_key,branch_p,branched_p,block_section_p,page_break_p) 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.block_section_p,new.page_break_p); return v_section_id; end new; procedure remove ( section_id in survey_sections.section_id%TYPE ) is v_question_row survey_questions%ROWTYPE; begin for v_question_row in (select question_id from survey_questions where section_id=remove.section_id) loop survey_question.remove(v_question_row.question_id); end loop; delete from survey_sections where section_id=remove.section_id; acs_object.del(remove.section_id); end remove; end survey_section; / show errors create or replace package body survey_predefined_question as function new ( predefined_question_id in survey_predefined_questions.predefined_question_id%TYPE default null, section_id in survey_predefined_questions.section_id%TYPE default null, sort_order in survey_predefined_questions.sort_order%TYPE default null, question_text in survey_predefined_questions.question_text%TYPE default null, abstract_data_type in survey_predefined_questions.abstract_data_type%TYPE default null, required_p in survey_predefined_questions.required_p%TYPE default 't', active_p in survey_predefined_questions.active_p%TYPE default 't', presentation_type in survey_predefined_questions.presentation_type%TYPE default null, presentation_options in survey_predefined_questions.presentation_options%TYPE default null, presentation_alignment in survey_predefined_questions.presentation_alignment%TYPE default 'below', object_type in acs_objects.object_type%TYPE default 'survey_predefined_question', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, question_html_p in survey_predefined_questions.question_html_p%TYPE default 'f', action_type in survey_predefined_questions.action_type%TYPE null, tcl in survey_predefined_questions.tcl%TYPE default null, table_name in survey_predefined_questions.table_name%TYPE default null, column_name in survey_predefined_questions.column_name%TYPE default null, key_name in survey_predefined_questions.key_name%TYPE default null, summary_type in survey_predefined_questions.summary_type%TYPE default null ) return acs_objects.object_id%TYPE is v_predefined_question_id survey_predefined_questions.predefined_question_id%TYPE; begin v_predefined_question_id := acs_object.new ( object_id => question_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => section_id ); insert into survey_predefined_questions (question_id, section_id, sort_order, question_text, abstract_data_type, required_p, active_p, presentation_type, presentation_options, presentation_alignment,question_html_p,action_type,tcl,table_name,column_name,key_name) 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.question_html_p,new.action_type,new.tcl,new.table_name,new.column_name,new.key_name); return v_question_id; end new; end survey_predefined_question; / show errors; create or replace package body survey_question as function new ( question_id in survey_questions.question_id%TYPE default null, section_id in survey_questions.section_id%TYPE default null, sort_order in survey_questions.sort_order%TYPE default null, question_text in survey_questions.question_text%TYPE default null, abstract_data_type in survey_questions.abstract_data_type%TYPE default null, required_p in survey_questions.required_p%TYPE default 't', active_p in survey_questions.active_p%TYPE default 't', presentation_type in survey_questions.presentation_type%TYPE default null, presentation_options in survey_questions.presentation_options%TYPE default null, presentation_alignment in survey_questions.presentation_alignment%TYPE default 'below', object_type in acs_objects.object_type%TYPE default 'survey_question', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null, question_html_p in survey_questions.question_html_p%TYPE default 'f', summary_type in survey_questions.summary_type%TYPE default null, predefined_question_id in survey_questions.predefined_question_id%TYPE default null ) return acs_objects.object_id%TYPE is v_question_id survey_questions.question_id%TYPE; begin v_question_id := acs_object.new ( object_id => question_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => section_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,question_html_p,summary_type,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.question_html_p,new.summary_type,new.predefined_question_id); return v_question_id; end new; procedure remove ( question_id in survey_questions.question_id%TYPE ) is 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; acs_object.del(remove.question_id); end remove; end survey_question; / show errors v_split_pos integer; begin if answer is NOT NULL then select presentation_options into v_presentation_options from survey_questions where question_id=boolean_answer.question_id; v_split_pos:= instr(v_presentation_options, '/'); if answer = 't' then v_answer:=substr(v_presentation_options, 1, v_split_pos -1 ); end if; if answer = 'f' then v_answer:=substr(v_presentation_options, v_split_pos + 1 ); end if; else v_answer := ''; end if; return v_answer; end boolean_answer; end survey_response; / show errors -- these views depend on functions in this file -DaveB -- this view contains only the most recently edited version -- of each survey response. create or replace view survey_responses_latest as select sr.*, o.creation_date, o.creation_user as initial_user_id from survey_responses sr, acs_objects o, (select max(response_id) as response_id from survey_responses group by nvl(initial_response_id, response_id)) latest where nvl(sr.initial_response_id,sr.response_id) = o.object_id and sr.response_id= latest.response_id; create or replace view survey_ques_responses_latest as select qr.* from survey_question_responses qr, survey_responses_latest r where qr.response_id=r.response_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