Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-package-create.sql,v diff -u -r1.2.2.4 -r1.2.2.5 --- openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql 21 Oct 2004 07:46:12 -0000 1.2.2.4 +++ openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql 1 Jan 2005 17:39:00 -0000 1.2.2.5 @@ -1,4 +1,5 @@ -- 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 ' @@ -14,7 +15,7 @@ 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__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; @@ -41,6 +42,8 @@ end;' language 'plpgsql'; +select define_function_args('survey__remove','survey_id'); + create or replace function survey__remove (integer) returns integer as ' declare @@ -50,19 +53,19 @@ begin for v_response_row in SELECT - response_id + 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); + 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 + from survey_sections + where survey_id=remove__survey_id loop - PERFORM survey_section__remove(v_section_row.section_id); + PERFORM survey_section__remove(v_section_row.section_id); end loop; delete from surveys @@ -77,8 +80,8 @@ create or replace function survey__name (integer) returns varchar as ' declare - name__survey_id alias for $1; - v_name surveys.name%TYPE; + name__survey_id alias for $1; + v_name surveys.name%TYPE; begin select into v_name name from surveys @@ -87,30 +90,25 @@ return v_name; end;' language 'plpgsql'; --- API for survey_section objects ---Create a new survey with a pretty_id - -- API for survey_section objects - ---Create a new survey with a pretty_id - -create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer,text) +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__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; -- default section_id + new__pretty_id alias for $13; v_section_id integer; begin v_section_id := acs_object__new ( @@ -123,54 +121,80 @@ ); 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) + (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'; ---Have the old API default to using the section_id +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; -create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer) + 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__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 - v_section_id integer; + 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_section_id := acs_object__new ( - new__section_id, - ''survey_section'', + 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_sections - (section_id, survey_id, name, description, description_html_p, sort_key, branch_p, branched_p, page_break_p, block_section_p, pretty_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_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, v_section_id); + (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; - return v_section_id; - end;' language 'plpgsql'; ---API for a question with a pretty_id passed in. - -create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer,text) +-- 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 @@ -183,13 +207,12 @@ 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__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 ( @@ -205,20 +228,20 @@ (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) + 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, new__pretty_id); + new__presentation_alignment, new__predefined_question_id); return v_question_id; end;' language 'plpgsql'; ---Default the old api to use question_id -create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer) +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 @@ -227,16 +250,17 @@ 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 f + 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__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 ( @@ -252,97 +276,30 @@ (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) + 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, v_question_id); + new__presentation_alignment, new__predefined_question_id,new__pretty_id); return v_question_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__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_responses + where question_id=remove__question_id; - delete from survey_question_choices - where question_id=remove__question_id; + delete from survey_question_choices + where question_id=remove__question_id; - delete from survey_questions + delete from survey_questions where question_id = remove__question_id; PERFORM acs_object__delete(remove__question_id); @@ -351,6 +308,8 @@ end;' language 'plpgsql'; + + -- API for survey responses create or replace function survey_response__new(integer,integer,varchar,boolean,integer,varchar,integer,integer) @@ -388,19 +347,30 @@ 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; + 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; + v_initial_response_id := p_response_id; end if; return v_initial_response_id; -end;' language 'plpgsql'; +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) @@ -409,12 +379,12 @@ 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); +-- 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 + where initial_response_id=remove__response_id loop - PERFORM survey_response__del(v_response_row.response_id); + PERFORM survey_response__del(v_response_row.response_id); end loop; PERFORM survey_response__del(remove__response_id); @@ -426,50 +396,49 @@ 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; + 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); + 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; + 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; - + 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;