Index: openacs-4/packages/simple-survey/sql/oracle/simple-survey-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/oracle/simple-survey-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/oracle/simple-survey-create.sql 19 Jul 2001 22:36:47 -0000 1.1 @@ -0,0 +1,548 @@ +-- 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:36:47 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" + +-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel +-- 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... +-- create or replace function logical_negation(true_or_false IN varchar) +-- return varchar +-- is +-- BEGIN +-- IF true_or_false is null THEN +-- return null; +-- ELSIF true_or_false = 'f' THEN +-- return 't'; +-- ELSE +-- return 'f'; +-- END IF; +-- END logical_negation; +-- / +-- show errors + +begin + + acs_privilege.create_privilege('survsimp_create_survey'); + acs_privilege.create_privilege('survsimp_modify_survey'); + acs_privilege.create_privilege('survsimp_delete_survey'); + acs_privilege.create_privilege('survsimp_create_question'); + acs_privilege.create_privilege('survsimp_modify_question'); + acs_privilege.create_privilege('survsimp_delete_question'); + acs_privilege.create_privilege('survsimp_take_survey'); + + acs_privilege.create_privilege('survsimp_admin_survey'); + + acs_privilege.add_child('survsimp_admin_survey','survsimp_create_survey'); + acs_privilege.add_child('survsimp_admin_survey','survsimp_modify_survey'); + acs_privilege.add_child('survsimp_admin_survey','survsimp_delete_survey'); + acs_privilege.add_child('survsimp_admin_survey','survsimp_create_question'); + acs_privilege.add_child('survsimp_admin_survey','survsimp_modify_question'); + acs_privilege.add_child('survsimp_admin_survey','survsimp_delete_question'); + + acs_privilege.add_child('read','survsimp_take_survey'); + acs_privilege.add_child('admin','survsimp_admin_survey'); + + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'survsimp_survey', + pretty_name => 'Simple Survey', + pretty_plural => 'Simple Surveys', + table_name => 'SURVSIMP_SURVEYS', + id_column => 'SURVEY_ID' + ); + + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'survsimp_question', + pretty_name => 'Simple Survey Question', + pretty_plural => 'Simple Survey Questions', + table_name => 'SURVSIMP_QUESTIONS', + id_column => 'QUESTION_ID' + ); + + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'survsimp_response', + pretty_name => 'Simple Survey Response', + pretty_plural => 'Simple Survey Responses', + table_name => 'SURVSIMP_RESPONSES', + id_column => 'RESPONSE_ID' + ); + + acs_rel_type.create_type ( + rel_type => 'user_blob_response_rel', + pretty_name => 'User Blob Response', + pretty_plural => 'User Blob Responses', + object_type_one => 'user', + role_one => 'user', + table_name => 'survsimp_question_responses', + id_column => 'user_id', + package_name => 'user_blob_response_rel', + min_n_rels_one => 1, + max_n_rels_one => 1, + object_type_two => 'content_item', + min_n_rels_two => 0, + max_n_rels_two => 1 + ); + +end; +/ +show errors + +create table survsimp_surveys ( + survey_id constraint survsimp_surveys_survey_id_fk + references acs_objects (object_id) + 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 varchar(4000) + constraint survsimp_surveys_desc_nn + not null, + description_html_p char(1) + constraint survsimp_surv_desc_html_p_ck + check(description_html_p in ('t','f')), + enabled_p char(1) + constraint survsimp_surveys_enabled_p_ck + check(enabled_p in ('t','f')), + -- limit to one response per user + single_response_p char(1) + constraint survsimp_sur_single_resp_p_ck + check(single_response_p in ('t','f')), + single_editable_p 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 constraint survsimp_q_question_id_fk + references acs_objects (object_id) + constraint survsimp_q_question_id_pk + primary key, + survey_id constraint survsimp_q_survey_id_fk + references survsimp_surveys, + sort_key integer + constraint survsimp_q_sort_key_nn + not null, + question_text clob + 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 char(1) + constraint survsimp_q_required_p_ck + check (required_p in ('t','f')), + active_p 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_sequence start with 1; + +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 constraint survsimp_qc_question_id_nn + not null + constraint survsimp_qc_question_id_fk + references survsimp_questions, + -- human readable + label varchar(500) constraint survsimp_qc_label_nn not null, + -- might be useful for averaging or whatever, generally null + numeric_value number, + -- 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 constraint survsimp_resp_response_id_fk + references acs_objects (object_id) + constraint srvsimp_resp_response_id_pk + primary key, + survey_id constraint survsimp_resp_survey_id_fk + references survsimp_surveys, + title varchar(100), + notify_on_comment_p 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 or replace 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 not null references survsimp_responses, + question_id not null references survsimp_questions, + -- if the user picked a canned response + choice_id references survsimp_question_choices, + boolean_answer char(1) check(boolean_answer in ('t','f')), + clob_answer clob, + number_answer number, + varchar_answer varchar(4000), + date_answer date, + -- 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, + -- 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 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; + + + +-- sequence for variable names +create sequence survsimp_variable_id_sequence start with 1; + +-- variable names for scored surveys +create table survsimp_variables ( + variable_id integer primary key, + variable_name varchar(100) not null +); + +-- map variable names to surveys +create table survsimp_variables_surveys_map ( + variable_id not null references survsimp_variables, + survey_id not null references survsimp_surveys +); + +-- scores for scored responses +create table survsimp_choice_scores ( + choice_id not null references survsimp_question_choices, + variable_id not null references survsimp_variables, + score integer not null +); + +-- logic for scored survey redirection +create table survsimp_logic ( + logic_id integer primary key, + logic clob +); + +create sequence survsimp_logic_id_sequence start with 1; + +-- map logic to surveys +create table survsimp_logic_surveys_map ( + logic_id not null references survsimp_logic, + survey_id not null references survsimp_surveys +); + + +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 survsimp_survey +as + function new ( + survey_id in survsimp_surveys.survey_id%TYPE default null, + name in survsimp_surveys.name%TYPE, + short_name in survsimp_surveys.short_name%TYPE, + description in survsimp_surveys.description%TYPE, + description_html_p in survsimp_surveys.description_html_p%TYPE default 'f', + single_response_p in survsimp_surveys.single_response_p%TYPE default 'f', + single_editable_p in survsimp_surveys.single_editable_p%TYPE default 't', + enabled_p in survsimp_surveys.enabled_p%TYPE default 'f', + type in survsimp_surveys.type%TYPE default 'general', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE; + + procedure delete ( + survey_id in survsimp_surveys.survey_id%TYPE + ); +end survsimp_survey; +/ +show errors + +create or replace package body survsimp_survey +as + function new ( + survey_id in survsimp_surveys.survey_id%TYPE default null, + name in survsimp_surveys.name%TYPE, + short_name in survsimp_surveys.short_name%TYPE, + description in survsimp_surveys.description%TYPE, + description_html_p in survsimp_surveys.description_html_p%TYPE default 'f', + single_response_p in survsimp_surveys.single_response_p%TYPE default 'f', + single_editable_p in survsimp_surveys.single_editable_p%TYPE default 't', + enabled_p in survsimp_surveys.enabled_p%TYPE default 'f', + type in survsimp_surveys.type%TYPE default 'general', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE + is + v_survey_id survsimp_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 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 new; + + procedure delete ( + survey_id survsimp_surveys.survey_id%TYPE + ) + is + begin + delete from survsimp_surveys + where survey_id = survsimp_survey.delete.survey_id; + acs_object.delete(survey_id); + end delete; +end survsimp_survey; +/ +show errors + +-- +-- constructor for a survsimp_question +-- +create or replace package survsimp_question +as + function new ( + question_id in survsimp_questions.question_id%TYPE default null, + survey_id in survsimp_questions.survey_id%TYPE default null, + sort_key in survsimp_questions.sort_key%TYPE default null, + question_text in survsimp_questions.question_text%TYPE default null, + abstract_data_type in survsimp_questions.abstract_data_type%TYPE default null, + required_p in survsimp_questions.required_p%TYPE default 't', + active_p in survsimp_questions.active_p%TYPE default 't', + presentation_type in survsimp_questions.presentation_type%TYPE default null, + presentation_options in survsimp_questions.presentation_options%TYPE default null, + presentation_alignment in survsimp_questions.presentation_alignment%TYPE default 'below', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE; + + procedure delete ( + question_id in survsimp_questions.question_id%TYPE + ); +end survsimp_question; +/ +show errors + +create or replace package body survsimp_question +as + function new ( + question_id in survsimp_questions.question_id%TYPE default null, + survey_id in survsimp_questions.survey_id%TYPE default null, + sort_key in survsimp_questions.sort_key%TYPE default null, + question_text in survsimp_questions.question_text%TYPE default null, + abstract_data_type in survsimp_questions.abstract_data_type%TYPE default null, + required_p in survsimp_questions.required_p%TYPE default 't', + active_p in survsimp_questions.active_p%TYPE default 't', + presentation_type in survsimp_questions.presentation_type%TYPE default null, + presentation_options in survsimp_questions.presentation_options%TYPE default null, + presentation_alignment in survsimp_questions.presentation_alignment%TYPE default 'below', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE + is + v_question_id survsimp_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 => survey_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 new; + + procedure delete ( + question_id in survsimp_questions.question_id%TYPE + ) + is + begin + delete from survsimp_questions + where question_id = survsimp_question.delete.question_id; + acs_object.delete(question_id); + end delete; +end survsimp_question; +/ +show errors + +-- +-- constructor for a survsimp_response +-- +create or replace package survsimp_response +as + function new ( + response_id in survsimp_responses.response_id %TYPE default null, + survey_id in survsimp_responses.survey_id%TYPE default null, + title in survsimp_responses.title%TYPE default null, + notify_on_comment_p in survsimp_responses.notify_on_comment_p%TYPE default 'f', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE; + + procedure delete ( + response_id in survsimp_responses.response_id%TYPE + ); +end survsimp_response; +/ +show errors + +create or replace package body survsimp_response +as + function new ( + response_id in survsimp_responses.response_id %TYPE default null, + survey_id in survsimp_responses.survey_id%TYPE default null, + title in survsimp_responses.title%TYPE default null, + notify_on_comment_p in survsimp_responses.notify_on_comment_p%TYPE default 'f', + object_type in acs_objects.object_type%TYPE default 'survsimp_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 + ) return acs_objects.object_id%TYPE + is + v_response_id survsimp_responses.response_id%TYPE; + begin + v_response_id := acs_object.new ( + object_id => response_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => 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 new; + + procedure delete ( + response_id in survsimp_responses.response_id%TYPE + ) + is + begin + delete from survsimp_responses + where response_id = survsimp_response.delete.response_id; + acs_object.delete(response_id); + end delete; +end survsimp_response; +/ +show errors + + + + + + Index: openacs-4/packages/simple-survey/sql/oracle/simple-survey-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/oracle/simple-survey-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/oracle/simple-survey-drop.sql 19 Jul 2001 22:36:47 -0000 1.1 @@ -0,0 +1,69 @@ +-- +-- 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:36:47 gilbertw Exp $ + +drop_package survsimp_response; +drop_package survsimp_question; +drop_package survsimp_survey; + +drop table survsimp_logic_surveys_map cascade constraints; + +drop sequence survsimp_logic_id_sequence; +drop table survsimp_logic; +drop table survsimp_choice_scores cascade constraints; +drop table survsimp_variables_surveys_map cascade constraints; +drop table survsimp_variables; + +drop sequence survsimp_variable_id_sequence; +drop view survsimp_question_responses_un; +drop table survsimp_question_responses cascade constraints; +drop view survsimnp_responses_unique; +drop table survsimp_responses cascade constraints; +drop table survsimp_question_choices cascade constraints; + +drop sequence survsimp_choice_id_sequence; +drop table survsimp_questions cascade constraints; +drop table survsimp_surveys cascade constraints; + +-- 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'; + +begin + acs_rel_type.drop_type('user_blob_response_rel'); + + acs_object_type.drop_type ('survsimp_response'); + acs_object_type.drop_type ('survsimp_question'); + acs_object_type.drop_type ('survsimp_survey'); + + acs_privilege.remove_child ('admin','survsimp_admin_survey'); + acs_privilege.remove_child ('read','survsimp_take_survey'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_delete_question'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_modify_question'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_create_question'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_delete_survey'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_modify_survey'); + acs_privilege.remove_child ('survsimp_admin_survey','survsimp_create_survey'); + + acs_privilege.drop_privilege('survsimp_admin_survey'); + acs_privilege.drop_privilege('survsimp_take_survey'); + acs_privilege.drop_privilege('survsimp_delete_question'); + acs_privilege.drop_privilege('survsimp_modify_question'); + acs_privilege.drop_privilege('survsimp_create_question'); + acs_privilege.drop_privilege('survsimp_delete_survey'); + acs_privilege.drop_privilege('survsimp_modify_survey'); + acs_privilege.drop_privilege('survsimp_create_survey'); + + +end; +/ +show errors + +-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel +-- drop function logical_negation; + Index: openacs-4/packages/simple-survey/sql/oracle/survsimp-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/oracle/Attic/survsimp-create.sql,v diff -u -N --- openacs-4/packages/simple-survey/sql/oracle/survsimp-create.sql 9 Jul 2001 19:24:29 -0000 1.3 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,548 +0,0 @@ --- 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: survsimp-create.sql,v 1.3 2001/07/09 19:24:29 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" - --- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel --- 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... --- create or replace function logical_negation(true_or_false IN varchar) --- return varchar --- is --- BEGIN --- IF true_or_false is null THEN --- return null; --- ELSIF true_or_false = 'f' THEN --- return 't'; --- ELSE --- return 'f'; --- END IF; --- END logical_negation; --- / --- show errors - -begin - - acs_privilege.create_privilege('survsimp_create_survey'); - acs_privilege.create_privilege('survsimp_modify_survey'); - acs_privilege.create_privilege('survsimp_delete_survey'); - acs_privilege.create_privilege('survsimp_create_question'); - acs_privilege.create_privilege('survsimp_modify_question'); - acs_privilege.create_privilege('survsimp_delete_question'); - acs_privilege.create_privilege('survsimp_take_survey'); - - acs_privilege.create_privilege('survsimp_admin_survey'); - - acs_privilege.add_child('survsimp_admin_survey','survsimp_create_survey'); - acs_privilege.add_child('survsimp_admin_survey','survsimp_modify_survey'); - acs_privilege.add_child('survsimp_admin_survey','survsimp_delete_survey'); - acs_privilege.add_child('survsimp_admin_survey','survsimp_create_question'); - acs_privilege.add_child('survsimp_admin_survey','survsimp_modify_question'); - acs_privilege.add_child('survsimp_admin_survey','survsimp_delete_question'); - - acs_privilege.add_child('read','survsimp_take_survey'); - acs_privilege.add_child('admin','survsimp_admin_survey'); - - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'survsimp_survey', - pretty_name => 'Simple Survey', - pretty_plural => 'Simple Surveys', - table_name => 'SURVSIMP_SURVEYS', - id_column => 'SURVEY_ID' - ); - - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'survsimp_question', - pretty_name => 'Simple Survey Question', - pretty_plural => 'Simple Survey Questions', - table_name => 'SURVSIMP_QUESTIONS', - id_column => 'QUESTION_ID' - ); - - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'survsimp_response', - pretty_name => 'Simple Survey Response', - pretty_plural => 'Simple Survey Responses', - table_name => 'SURVSIMP_RESPONSES', - id_column => 'RESPONSE_ID' - ); - - acs_rel_type.create_type ( - rel_type => 'user_blob_response_rel', - pretty_name => 'User Blob Response', - pretty_plural => 'User Blob Responses', - object_type_one => 'user', - role_one => 'user', - table_name => 'survsimp_question_responses', - id_column => 'user_id', - package_name => 'user_blob_response_rel', - min_n_rels_one => 1, - max_n_rels_one => 1, - object_type_two => 'content_item', - min_n_rels_two => 0, - max_n_rels_two => 1 - ); - -end; -/ -show errors - -create table survsimp_surveys ( - survey_id constraint survsimp_surveys_survey_id_fk - references acs_objects (object_id) - 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 varchar(4000) - constraint survsimp_surveys_desc_nn - not null, - description_html_p char(1) - constraint survsimp_surv_desc_html_p_ck - check(description_html_p in ('t','f')), - enabled_p char(1) - constraint survsimp_surveys_enabled_p_ck - check(enabled_p in ('t','f')), - -- limit to one response per user - single_response_p char(1) - constraint survsimp_sur_single_resp_p_ck - check(single_response_p in ('t','f')), - single_editable_p 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 constraint survsimp_q_question_id_fk - references acs_objects (object_id) - constraint survsimp_q_question_id_pk - primary key, - survey_id constraint survsimp_q_survey_id_fk - references survsimp_surveys, - sort_key integer - constraint survsimp_q_sort_key_nn - not null, - question_text clob - 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 char(1) - constraint survsimp_q_required_p_ck - check (required_p in ('t','f')), - active_p 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_sequence start with 1; - -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 constraint survsimp_qc_question_id_nn - not null - constraint survsimp_qc_question_id_fk - references survsimp_questions, - -- human readable - label varchar(500) constraint survsimp_qc_label_nn not null, - -- might be useful for averaging or whatever, generally null - numeric_value number, - -- 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 constraint survsimp_resp_response_id_fk - references acs_objects (object_id) - constraint srvsimp_resp_response_id_pk - primary key, - survey_id constraint survsimp_resp_survey_id_fk - references survsimp_surveys, - title varchar(100), - notify_on_comment_p 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 or replace 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 not null references survsimp_responses, - question_id not null references survsimp_questions, - -- if the user picked a canned response - choice_id references survsimp_question_choices, - boolean_answer char(1) check(boolean_answer in ('t','f')), - clob_answer clob, - number_answer number, - varchar_answer varchar(4000), - date_answer date, - -- 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, - -- 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 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; - - - --- sequence for variable names -create sequence survsimp_variable_id_sequence start with 1; - --- variable names for scored surveys -create table survsimp_variables ( - variable_id integer primary key, - variable_name varchar(100) not null -); - --- map variable names to surveys -create table survsimp_variables_surveys_map ( - variable_id not null references survsimp_variables, - survey_id not null references survsimp_surveys -); - --- scores for scored responses -create table survsimp_choice_scores ( - choice_id not null references survsimp_question_choices, - variable_id not null references survsimp_variables, - score integer not null -); - --- logic for scored survey redirection -create table survsimp_logic ( - logic_id integer primary key, - logic clob -); - -create sequence survsimp_logic_id_sequence start with 1; - --- map logic to surveys -create table survsimp_logic_surveys_map ( - logic_id not null references survsimp_logic, - survey_id not null references survsimp_surveys -); - - -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 survsimp_survey -as - function new ( - survey_id in survsimp_surveys.survey_id%TYPE default null, - name in survsimp_surveys.name%TYPE, - short_name in survsimp_surveys.short_name%TYPE, - description in survsimp_surveys.description%TYPE, - description_html_p in survsimp_surveys.description_html_p%TYPE default 'f', - single_response_p in survsimp_surveys.single_response_p%TYPE default 'f', - single_editable_p in survsimp_surveys.single_editable_p%TYPE default 't', - enabled_p in survsimp_surveys.enabled_p%TYPE default 'f', - type in survsimp_surveys.type%TYPE default 'general', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE; - - procedure delete ( - survey_id in survsimp_surveys.survey_id%TYPE - ); -end survsimp_survey; -/ -show errors - -create or replace package body survsimp_survey -as - function new ( - survey_id in survsimp_surveys.survey_id%TYPE default null, - name in survsimp_surveys.name%TYPE, - short_name in survsimp_surveys.short_name%TYPE, - description in survsimp_surveys.description%TYPE, - description_html_p in survsimp_surveys.description_html_p%TYPE default 'f', - single_response_p in survsimp_surveys.single_response_p%TYPE default 'f', - single_editable_p in survsimp_surveys.single_editable_p%TYPE default 't', - enabled_p in survsimp_surveys.enabled_p%TYPE default 'f', - type in survsimp_surveys.type%TYPE default 'general', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE - is - v_survey_id survsimp_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 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 new; - - procedure delete ( - survey_id survsimp_surveys.survey_id%TYPE - ) - is - begin - delete from survsimp_surveys - where survey_id = survsimp_survey.delete.survey_id; - acs_object.delete(survey_id); - end delete; -end survsimp_survey; -/ -show errors - --- --- constructor for a survsimp_question --- -create or replace package survsimp_question -as - function new ( - question_id in survsimp_questions.question_id%TYPE default null, - survey_id in survsimp_questions.survey_id%TYPE default null, - sort_key in survsimp_questions.sort_key%TYPE default null, - question_text in survsimp_questions.question_text%TYPE default null, - abstract_data_type in survsimp_questions.abstract_data_type%TYPE default null, - required_p in survsimp_questions.required_p%TYPE default 't', - active_p in survsimp_questions.active_p%TYPE default 't', - presentation_type in survsimp_questions.presentation_type%TYPE default null, - presentation_options in survsimp_questions.presentation_options%TYPE default null, - presentation_alignment in survsimp_questions.presentation_alignment%TYPE default 'below', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE; - - procedure delete ( - question_id in survsimp_questions.question_id%TYPE - ); -end survsimp_question; -/ -show errors - -create or replace package body survsimp_question -as - function new ( - question_id in survsimp_questions.question_id%TYPE default null, - survey_id in survsimp_questions.survey_id%TYPE default null, - sort_key in survsimp_questions.sort_key%TYPE default null, - question_text in survsimp_questions.question_text%TYPE default null, - abstract_data_type in survsimp_questions.abstract_data_type%TYPE default null, - required_p in survsimp_questions.required_p%TYPE default 't', - active_p in survsimp_questions.active_p%TYPE default 't', - presentation_type in survsimp_questions.presentation_type%TYPE default null, - presentation_options in survsimp_questions.presentation_options%TYPE default null, - presentation_alignment in survsimp_questions.presentation_alignment%TYPE default 'below', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE - is - v_question_id survsimp_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 => survey_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 new; - - procedure delete ( - question_id in survsimp_questions.question_id%TYPE - ) - is - begin - delete from survsimp_questions - where question_id = survsimp_question.delete.question_id; - acs_object.delete(question_id); - end delete; -end survsimp_question; -/ -show errors - --- --- constructor for a survsimp_response --- -create or replace package survsimp_response -as - function new ( - response_id in survsimp_responses.response_id %TYPE default null, - survey_id in survsimp_responses.survey_id%TYPE default null, - title in survsimp_responses.title%TYPE default null, - notify_on_comment_p in survsimp_responses.notify_on_comment_p%TYPE default 'f', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE; - - procedure delete ( - response_id in survsimp_responses.response_id%TYPE - ); -end survsimp_response; -/ -show errors - -create or replace package body survsimp_response -as - function new ( - response_id in survsimp_responses.response_id %TYPE default null, - survey_id in survsimp_responses.survey_id%TYPE default null, - title in survsimp_responses.title%TYPE default null, - notify_on_comment_p in survsimp_responses.notify_on_comment_p%TYPE default 'f', - object_type in acs_objects.object_type%TYPE default 'survsimp_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 - ) return acs_objects.object_id%TYPE - is - v_response_id survsimp_responses.response_id%TYPE; - begin - v_response_id := acs_object.new ( - object_id => response_id, - object_type => object_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => 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 new; - - procedure delete ( - response_id in survsimp_responses.response_id%TYPE - ) - is - begin - delete from survsimp_responses - where response_id = survsimp_response.delete.response_id; - acs_object.delete(response_id); - end delete; -end survsimp_response; -/ -show errors - - - - - - Index: openacs-4/packages/simple-survey/sql/oracle/survsimp-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/oracle/Attic/survsimp-drop.sql,v diff -u -N --- openacs-4/packages/simple-survey/sql/oracle/survsimp-drop.sql 9 Jul 2001 19:24:29 -0000 1.3 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,69 +0,0 @@ --- --- drop SQL for survsimp package --- --- by nstrug@arsdigita.com on 29th September 2000 --- --- $Id: survsimp-drop.sql,v 1.3 2001/07/09 19:24:29 gilbertw Exp $ - -drop_package survsimp_response; -drop_package survsimp_question; -drop_package survsimp_survey; - -drop table survsimp_logic_surveys_map cascade constraints; - -drop sequence survsimp_logic_id_sequence; -drop table survsimp_logic; -drop table survsimp_choice_scores cascade constraints; -drop table survsimp_variables_surveys_map cascade constraints; -drop table survsimp_variables; - -drop sequence survsimp_variable_id_sequence; -drop view survsimp_question_responses_un; -drop table survsimp_question_responses cascade constraints; -drop view survsimnp_responses_unique; -drop table survsimp_responses cascade constraints; -drop table survsimp_question_choices cascade constraints; - -drop sequence survsimp_choice_id_sequence; -drop table survsimp_questions cascade constraints; -drop table survsimp_surveys cascade constraints; - --- 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'; - -begin - acs_rel_type.drop_type('user_blob_response_rel'); - - acs_object_type.drop_type ('survsimp_response'); - acs_object_type.drop_type ('survsimp_question'); - acs_object_type.drop_type ('survsimp_survey'); - - acs_privilege.remove_child ('admin','survsimp_admin_survey'); - acs_privilege.remove_child ('read','survsimp_take_survey'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_delete_question'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_modify_question'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_create_question'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_delete_survey'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_modify_survey'); - acs_privilege.remove_child ('survsimp_admin_survey','survsimp_create_survey'); - - acs_privilege.drop_privilege('survsimp_admin_survey'); - acs_privilege.drop_privilege('survsimp_take_survey'); - acs_privilege.drop_privilege('survsimp_delete_question'); - acs_privilege.drop_privilege('survsimp_modify_question'); - acs_privilege.drop_privilege('survsimp_create_question'); - acs_privilege.drop_privilege('survsimp_delete_survey'); - acs_privilege.drop_privilege('survsimp_modify_survey'); - acs_privilege.drop_privilege('survsimp_create_survey'); - - -end; -/ -show errors - --- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel --- drop function logical_negation; -