Index: openacs-4/packages/simple-survey/sql/postgresql/simple-survey-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/simple-survey-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/postgresql/simple-survey-create.sql 19 Jul 2001 22:40:55 -0000 1.1 @@ -0,0 +1,584 @@ +-- 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 +-- +-- $Id: simple-survey-create.sql,v 1.1 2001/07/19 22:40:55 gilbertw Exp $ + +-- we expect this to be replaced with a more powerful survey +-- module, to be developed by buddy@ucla.edu, so we prefix +-- all of our Oracle structures with "survsimp" + +-- this is a PL/SQL function that used to be in the standard ACS 3.x core - not in the +-- current ACS 4.0 core however... +-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel +-- create function logical_negation(boolean) +-- returns boolean as ' +-- declare +-- true_or_false alias for $1; +-- begin +-- if true_or_false is null then +-- return null; +-- else +-- if true_or_false = ''f'' then +-- return ''t''; +-- else +-- return ''f''; +-- end if; +-- end if; +-- end;' language 'plpgsql'; + +create function inline_0 () +returns integer as ' +begin + PERFORM acs_privilege__create_privilege(''survsimp_create_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_modify_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_delete_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_create_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_modify_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_delete_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_take_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_admin_survey'', null, null); + + return 0; + +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +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; + + select acs_privilege__add_child('survsimp_admin_survey','survsimp_create_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_modify_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_delete_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_create_question'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_modify_question'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_delete_question'); + + select acs_privilege__add_child('read','survsimp_take_survey'); + + -- 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 (); + + select acs_privilege__add_child('admin','survsimp_admin_survey'); + +end; + + + +create function inline_1 () +returns integer as ' +begin + + PERFORM acs_object_type__create_type ( + ''survsimp_survey'', + ''Simple Survey'', + ''Simple Surveys'', + ''acs_object'', + ''survsimp_surveys'', + ''survey_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_object_type__create_type ( + ''survsimp_question'', + ''Simple Survey Question'', + ''Simple Survey Questions'', + ''acs_object'', + ''survsimp_questions'', + ''question_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_object_type__create_type ( + ''survsimp_response'', + ''Simple Survey Response'', + ''Simple Survey Responses'', + ''acs_object'', + ''survsimp_responses'', + ''response_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_rel_type__create_type ( + ''user_blob_response_rel'', + ''User Blob Response'', + ''User Blob Responses'', + ''relationship'', + ''survsimp_question_responses'', + ''user_id'', + ''user_blob_response_rel'', + ''user'', + ''user'', + 1, + 1, + ''content_item'', + null, + 0, + 1 + ); + + + return 0; + +end;' language 'plpgsql'; + +select inline_1 (); +drop function inline_1 (); + +create table survsimp_surveys ( + survey_id integer constraint survsimp_surveys_survey_id_fk + references acs_objects (object_id) + on delete cascade + constraint survsimp_surveys_pk + primary key, + name varchar(100) + constraint survsimp_surveys_name_nn + not null, + -- short, non-editable name we can identify this survey by + short_name varchar(20) + constraint survsimp_surveys_short_name_u + unique + constraint survsimp_surveys_short_name_nn + not null, + description text -- was varchar(4000) + constraint survsimp_surveys_desc_nn + not null, + description_html_p boolean, -- was char(1) + --constraint survsimp_surv_desc_html_p_ck + --check(description_html_p in ('t','f')), + enabled_p boolean, -- was char(1) + -- constraint survsimp_surveys_enabled_p_ck + -- check(enabled_p in ('t','f')), + -- limit to one response per user + single_response_p boolean, -- was char(1) + -- constraint survsimp_sur_single_resp_p_ck + -- check(single_response_p in ('t','f')), + single_editable_p boolean, -- was char(1) + -- constraint survsimp_surv_single_edit_p_ck + -- check(single_editable_p in ('t','f')), + type varchar(20) +); + +-- each question can be + +create table survsimp_questions ( + question_id integer constraint survsimp_q_question_id_fk + references acs_objects (object_id) + on delete cascade + constraint survsimp_q_question_id_pk + primary key, + survey_id integer constraint survsimp_q_survey_id_fk + references survsimp_surveys + on delete cascade, + sort_key integer + constraint survsimp_q_sort_key_nn + not null, + question_text text + constraint survsimp_q_question_text_nn + not null, + abstract_data_type varchar(30) + constraint survsimp_q_abs_data_type_ck + check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice','date')), + required_p boolean, -- was char(1) + -- constraint survsimp_q_required_p_ck + -- check (required_p in ('t','f')), + active_p boolean, -- was char(1) + -- constraint survsimp_q_qctive_p_ck + -- check (active_p in ('t','f')), + presentation_type varchar(20) + constraint survsimp_q_pres_type_nn + not null + constraint survsimp_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 survsimp_q_pres_alignment_ck + check(presentation_alignment in ('below','beside')) +); + + +-- for when a question has a fixed set of responses + +create sequence survsimp_choice_id_seq; +create view survsimp_choice_id_sequence as select nextval('survsimp_choice_id_seq') as nextval; + +create table survsimp_question_choices ( + choice_id integer constraint survsimp_qc_choice_id_nn + not null + constraint survsimp_qc_choice_id_pk + primary key, + question_id integer constraint survsimp_qc_question_id_nn + not null + constraint survsimp_qc_question_id_fk + references survsimp_questions + on delete cascade, + -- human readable + label varchar(500) constraint survsimp_qc_label_nn + not null, + -- might be useful for averaging or whatever, generally null + numeric_value numeric, + -- lower is earlier + sort_order integer +); + +-- 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 survsimp_responses ( + response_id integer constraint survsimp_resp_response_id_fk + references acs_objects (object_id) + on delete cascade + constraint srvsimp_resp_response_id_pk + primary key, + survey_id integer constraint survsimp_resp_survey_id_fk + references survsimp_surveys + on delete cascade, + title varchar(100), + notify_on_comment_p boolean -- was char(1) + -- constraint survsimp_resp_noton_com_p_ck + -- check(notify_on_comment_p in ('t','f')) +); + + +-- mbryzek: 3/27/2000 +-- Sometimes you release a survey, and then later decide that +-- you only want to include one response per user. The following +-- view includes only the latest response from all users +-- create or replace view survsimp_responses_unique as +-- select r1.* from survsimp_responses r1 +-- where r1.response_id=(select max(r2.response_id) +-- from survsimp_responses r2 +-- where r1.survey_id=r2.survey_id +-- and r1.user_id=r2.user_id); + +create view survsimp_responses_unique as +select r1.* from survsimp_responses r1, acs_objects a1 +where r1.response_id = (select max(r2.response_id) + from survsimp_responses r2, acs_objects a2 + where r1.survey_id = r2.survey_id + and a1.object_id = r1.response_id + and a2.object_id = r2.response_id + and a1.creation_user = a2.creation_user); + +-- 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 survsimp_question_responses ( + response_id integer constraint survsimp_qr_response_id_nn + not null + constraint survsimp_qr_response_id_fk + references survsimp_responses + on delete cascade, + question_id integer constraint survsimp_qr_question_id_nn + not null + constraint survsimp_qr_question_id_fk + references survsimp_questions + on delete cascade, + -- if the user picked a canned response + choice_id integer constraint survsimp_qr_choice_id_fk + references survsimp_question_choices + on delete cascade, + boolean_answer boolean, + -- was char(1) + -- check(boolean_answer in ('t','f')), + clob_answer text, + number_answer numeric, + varchar_answer text, + date_answer timestamp, + -- columns useful for attachments, column names + -- lifted from file-storage.sql and bboard.sql + -- this is where the actual content is stored + -- attachment_answer blob, + item_id integer + constraint survsimp_q_response_item_id_fk + references cr_items(item_id) + on delete cascade, + content_length integer, + -- 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" +); + + +-- We create a view that selects out only the last response from each +-- user to give us at most 1 response from all users. +create view survsimp_question_responses_un as +select qr.* + from survsimp_question_responses qr, survsimp_responses_unique r + where qr.response_id=r.response_id; + + + +-- sequence for variable names +create sequence survsimp_variable_id_seq; +create view survsimp_variable_id_sequence as select nextval('survsimp_variable_id_seq') as nextval; + + +-- variable names for scored surveys +create table survsimp_variables ( + variable_id integer + constraint survsimp_variable_id_pk + primary key, + variable_name varchar(100) + constraint survsimp_variable_name_nn not null +); + +-- map variable names to surveys +create table survsimp_variables_surveys_map ( + variable_id integer + constraint survsimp_vs_map_var_id_nn not null + constraint survsimp_vs_map_var_id_fk + references survsimp_variables(variable_id) + on delete cascade, + survey_id integer + constraint survsimp_vs_map_sur_id_nn not null + constraint survsimp_vs_map_sur_id_fk + references survsimp_surveys(survey_id) + on delete cascade +); + +-- scores for scored responses +create table survsimp_choice_scores ( + choice_id integer + constraint survsimp_choi_sc_ch_id_nn not null + constraint survsimp_choi_sc_ch_id_fk + references survsimp_question_choices(choice_id) + on delete cascade, + variable_id integer + constraint survsimp_choi_sc_var_id_nn not null + constraint survsimp_choi_sc_var_id_fk + references survsimp_variables(variable_id) + on delete cascade, + score integer + constraint survsimp_choi_sc_sc_nn not null +); + +-- logic for scored survey redirection +create table survsimp_logic ( + logic_id integer primary key, + logic text +); + +create sequence survsimp_logic_id_seq; +create view survsimp_logic_id_sequence as select nextval('survsimp_logic_id_seq') as nextval; + + +-- map logic to surveys +create table survsimp_logic_surveys_map ( + logic_id integer + constraint survsimp_l_s_map_logic_id_nn not null + constraint survsimp_l_s_map_logic_id_fk + references survsimp_logic(logic_id) + on delete cascade, + survey_id integer + constraint survsimp_l_s_map_sur_id_nn not null + constraint survsimp_l_s_map_sur_id_fk + references survsimp_surveys(survey_id) + on delete cascade +); + + +create index survsimp_response_index on survsimp_question_responses (response_id, question_id); + +-- We create a view that selects out only the last response from each +-- user to give us at most 1 response from all users. +-- create or replace view survsimp_question_responses_un as +-- select qr.* +-- from survsimp_question_responses qr, survsimp_responses_unique r +-- where qr.response_id=r.response_id; + +-- +-- constructor function for a survsimp_survey +-- + +-- create or replace package body survsimp_survey +-- procedure new +create function survsimp_survey__new (integer,varchar,varchar,text,boolean,boolean,boolean,boolean,varchar,integer,integer) +returns integer as ' +declare + new__survey_id alias for $1; -- default null + new__name alias for $2; + new__short_name alias for $3; + new__description alias for $4; + new__description_html_p alias for $5; -- default f + new__single_response_p alias for $6; -- default f + new__single_editable_p alias for $7; -- default t + new__enabled_p alias for $8; -- default f + new__type alias for $9; -- default general + new__creation_user alias for $10; -- default null + new__context_id alias for $11; -- default null + v_survey_id integer; +begin + v_survey_id := acs_object__new ( + new__survey_id, + ''survsimp_survey'', + now(), + new__creation_user, + null, + new__context_id + ); + + insert into survsimp_surveys + (survey_id, name, short_name, description, + description_html_p, single_response_p, single_editable_p, + enabled_p, type) + values + (v_survey_id, new__name, new__short_name, new__description, + new__description_html_p, new__single_response_p, new__single_editable_p, + new__enabled_p, new__type); + + return v_survey_id; + +end;' language 'plpgsql'; + +-- procedure delete +create function survsimp_survey__delete (integer) +returns integer as ' +declare + delete__survey_id alias for $1; +begin + delete from survsimp_surveys + where survey_id = delete__survey_id; + + PERFORM acs_object__delete(delete__survey_id); + + return 0; + +end;' language 'plpgsql'; + + +-- create or replace package body survsimp_question +-- procedure new +create function survsimp_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,integer,integer) +returns integer as ' +declare + new__question_id alias for $1; -- default null + new__survey_id alias for $2; -- default null + new__sort_key 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__creation_user alias for $11; -- default null + new__context_id alias for $12; -- default null + v_question_id integer; +begin + v_question_id := acs_object__new ( + new__question_id, + ''survsimp_question'', + now(), + new__creation_user, + null, + new__context_id + ); + + insert into survsimp_questions + (question_id, survey_id, sort_key, question_text, + abstract_data_type, required_p, active_p, + presentation_type, presentation_options, + presentation_alignment) + values + (v_question_id, new__survey_id, new__sort_key, new__question_text, + new__abstract_data_type, new__required_p, new__active_p, + new__presentation_type, new__presentation_options, + new__presentation_alignment); + + return v_question_id; + +end;' language 'plpgsql'; + +-- procedure delete +create function survsimp_question__delete (integer) +returns integer as ' +declare + delete__question_id alias for $1; +begin + delete from survsimp_questions + where question_id = delete__question_id; + + PERFORM acs_object__delete(delete__question_id); + + return 0; + +end;' language 'plpgsql'; + + +-- create or replace package body survsimp_response +-- procedure new +create function survsimp_response__new(integer,integer,varchar,boolean,integer,varchar,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 + v_response_id integer; +begin + v_response_id := acs_object__new ( + new__response_id, + ''survsimp_response'', + now(), + new__creation_user, + new__creation_ip, + new__context_id + ); + + insert into survsimp_responses + (response_id, survey_id, title, notify_on_comment_p) + values + (v_response_id, new__survey_id, new__title, new__notify_on_comment_p); + + return v_response_id; + +end;' language 'plpgsql'; + + +-- procedure delete +create function survsimp_response__delete(integer) +returns integer as ' +declare + delete__response_id alias for $1; +begin + delete from survsimp_responses + where response_id = delete__response_id; + + PERFORM acs_object__delete(delete__response_id); + + return 0; + +end;' language 'plpgsql'; + Index: openacs-4/packages/simple-survey/sql/postgresql/simple-survey-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/simple-survey-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/postgresql/simple-survey-drop.sql 19 Jul 2001 22:40:55 -0000 1.1 @@ -0,0 +1,73 @@ +-- +-- drop SQL for survsimp package +-- +-- by nstrug@arsdigita.com on 29th September 2000 +-- +-- $Id: simple-survey-drop.sql,v 1.1 2001/07/19 22:40:55 gilbertw Exp $ + +select drop_package('survsimp_response'); +select drop_package('survsimp_question'); +select drop_package('survsimp_survey'); + +drop table survsimp_logic_surveys_map; +drop view survsimp_logic_id_sequence; +drop sequence survsimp_logic_id_seq; +drop table survsimp_logic; +drop table survsimp_choice_scores; +drop table survsimp_variables_surveys_map; +drop table survsimp_variables; +drop view survsimp_variable_id_sequence; +drop sequence survsimp_variable_id_seq; +drop view survsimp_question_responses_un; +drop table survsimp_question_responses; +drop view survsimp_responses_unique; +drop table survsimp_responses; +drop table survsimp_question_choices; +drop view survsimp_choice_id_sequence; +drop sequence survsimp_choice_id_seq; +drop table survsimp_questions; +drop table survsimp_surveys; + +-- nuke all created objects +-- need to do this before nuking the types +delete from acs_objects where object_type = 'survsimp_response'; +delete from acs_objects where object_type = 'survsimp_question'; +delete from acs_objects where object_type = 'survsimp_survey'; + +create function inline_0 () +returns integer as ' +begin + PERFORM acs_rel_type__drop_type (''user_blob_response_rel'',''f''); + + PERFORM acs_object_type__drop_type (''survsimp_response'',''f''); + PERFORM acs_object_type__drop_type (''survsimp_question'',''f''); + PERFORM acs_object_type__drop_type (''survsimp_survey'',''f''); + + PERFORM acs_privilege__remove_child (''admin'',''survsimp_admin_survey''); + PERFORM acs_privilege__remove_child (''read'',''survsimp_take_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_delete_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_modify_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_create_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_delete_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_modify_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_create_survey''); + + PERFORM acs_privilege__drop_privilege(''survsimp_admin_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_take_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_delete_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_modify_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_create_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_delete_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_modify_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_create_survey''); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel +-- drop function logical_negation(boolean); + + Fisheye: Tag 1.4 refers to a dead (removed) revision in file `openacs-4/packages/simple-survey/sql/postgresql/survsimp-create.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 1.3 refers to a dead (removed) revision in file `openacs-4/packages/simple-survey/sql/postgresql/survsimp-drop.sql'. Fisheye: No comparison available. Pass `N' to diff?