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 -N
--- /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 -N
--- /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);
+
+
Index: openacs-4/packages/simple-survey/sql/postgresql/survsimp-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/Attic/survsimp-create.sql,v
diff -u -N
--- openacs-4/packages/simple-survey/sql/postgresql/survsimp-create.sql	9 Jul 2001 19:22:37 -0000	1.3
+++ /dev/null	1 Jan 1970 00:00:00 -0000
@@ -1,584 +0,0 @@
--- 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: survsimp-create.sql,v 1.3 2001/07/09 19:22:37 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/survsimp-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/Attic/survsimp-drop.sql,v
diff -u -N
--- openacs-4/packages/simple-survey/sql/postgresql/survsimp-drop.sql	9 Jul 2001 19:22:37 -0000	1.2
+++ /dev/null	1 Jan 1970 00:00:00 -0000
@@ -1,73 +0,0 @@
---
--- drop SQL for survsimp package
---
--- by nstrug@arsdigita.com on 29th September 2000
---
--- $Id: survsimp-drop.sql,v 1.2 2001/07/09 19:22:37 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);
-
-