Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-package-create.sql,v
diff -u -r1.4 -r1.5
--- openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql	17 May 2004 15:14:39 -0000	1.4
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql	13 Jan 2005 13:54:39 -0000	1.5
@@ -1,4 +1,5 @@
 -- API for survey objects
+select define_function_args('survey__new','survey_id,name,description,description_html_p;f,single_response_p;f,editable_p;f,enabled_p;f,single_section_p;t,type;general,display_type,package_id,public_p,creation_user,context_id');
 
 create or replace function survey__new (integer,varchar,text,boolean,boolean,boolean,boolean,boolean,varchar,varchar,integer,boolean,integer,integer)
 returns integer as '
@@ -14,7 +15,7 @@
   new__type                     alias for $9;  -- default general
   new__display_type             alias for $10;
   new__package_id               alias for $11;
-  new__public_p			alias for $12;  -- default t
+  new__public_p            alias for $12;  -- default t
   new__creation_user            alias for $13; -- default null
   new__context_id               alias for $14; -- default null
   v_survey_id                   integer;
@@ -41,6 +42,8 @@
 
 end;' language 'plpgsql';
 
+select define_function_args('survey__remove','survey_id');
+
 create or replace function survey__remove (integer)
 returns integer as '
 declare
@@ -50,19 +53,19 @@
 begin
 
     for v_response_row in SELECT
-		response_id	
+               response_id     
                from survey_responses
                where survey_id=remove__survey_id
                and initial_response_id is NULL
     loop
-	PERFORM survey_response__remove(v_response_row.response_id);
+        PERFORM survey_response__remove(v_response_row.response_id);
     end loop;
 
     for v_section_row in select section_id
-		from survey_sections
-		where survey_id=remove__survey_id
+                from survey_sections
+                where survey_id=remove__survey_id
     loop
-	PERFORM survey_section__remove(v_section_row.section_id);
+        PERFORM survey_section__remove(v_section_row.section_id);
     end loop;
 
     delete from surveys
@@ -77,8 +80,8 @@
 create or replace function survey__name (integer)
 returns varchar as '
 declare
-  name__survey_id		alias for $1;
-  v_name			surveys.name%TYPE;
+  name__survey_id               alias for $1;
+  v_name                        surveys.name%TYPE;
 begin
     select into v_name name
     from surveys
@@ -89,22 +92,23 @@
 
 
 -- API for survey_section objects
-
-create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer)
+select define_function_args('survey_section__new','section_id,survey_id,name,description,description_html_p;f,sort_key;0,branch_p;f,branched_p;f,block_section_p;f,page_break_p;t,creation_user,context_id,pretty_id');
+create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer,integer)
 returns integer as '
 declare
   new__section_id               alias for $1;  -- default null
   new__survey_id                alias for $2;  -- default null
   new__name                     alias for $3;  -- default null
   new__description              alias for $4;  -- default null
   new__description_html_p       alias for $5;  -- default f
-  new__sort_key			alias for $6;  -- default 0
-  new__branch_p			alias for $7;  -- default f
-  new__branched_p		alias for $8;  -- default f
-  new__block_section_p		alias for $9;  -- default f
-  new__page_break_p		alias for $10; -- default t
+  new__sort_key                 alias for $6;  -- default 0
+  new__branch_p                 alias for $7;  -- default f
+  new__branched_p               alias for $8;  -- default f
+  new__block_section_p          alias for $9;  -- default f
+  new__page_break_p             alias for $10; -- default t
   new__creation_user            alias for $11;  -- default null
   new__context_id               alias for $12;  -- default null
+  new__pretty_id                alias for $13;
   v_section_id                  integer;
 begin
     v_section_id := acs_object__new (
@@ -117,9 +121,9 @@
     );
 
     insert into survey_sections
-    (section_id, survey_id, name, description, description_html_p, sort_key, branch_p, branched_p, page_break_p, block_section_p)
+    (section_id, survey_id, name, description, description_html_p, sort_key, branch_p, branched_p, page_break_p, block_section_p,pretty_id)
     values
-    (v_section_id, new__survey_id, new__name, new__description, new__description_html_p, new__sort_key, new__branch_p, new__branched_p, new__page_break_p, new__block_section_p);
+    (v_section_id, new__survey_id, new__name, new__description, new__description_html_p, new__sort_key, new__branch_p, new__branched_p, new__page_break_p, new__block_section_p, new__pretty_id);
 
     return v_section_id;
 
@@ -128,22 +132,22 @@
 create or replace function survey_section__remove (integer)
 returns integer as '
 declare
-  remove_section_id             alias for $1;
-  v_question_row         	 survey_questions%ROWTYPE;
+  remove__section_id             alias for $1;
+  v_question_row                 survey_questions%ROWTYPE;
 begin
-    delete from survey_branches where section_id=remove_section_id;
-    delete from survey_conditions where question_id in (select question_id from survey_questions where section_id=remove_section_id);
+    delete from survey_branches where section_id=remove__section_id;
+    delete from survey_conditions where question_id in (select question_id from survey_questions where section_id=remove__section_id);
     for v_question_row in select question_id
-		from survey_questions
-		where section_id=remove_section_id
+                from survey_questions
+                where section_id=remove__section_id
     loop
-	PERFORM survey_question__remove(v_question_row.question_id);
+        PERFORM survey_question__remove(v_question_row.question_id);
     end loop;
 
     delete from survey_sections
-    where section_id = remove_section_id;
+    where section_id = remove__section_id;
 
-    PERFORM acs_object__delete(remove_section_id);
+    PERFORM acs_object__delete(remove__section_id);
 
     return 0;
 
@@ -154,22 +158,22 @@
 create or replace function survey_predefined_question__new (integer,text,varchar,varchar,varchar,varchar,boolean,varchar,varchar,text,varchar,varchar,varchar,integer,integer)
 returns integer as '
 declare
-  new__predefined_question_id	alias for $1;	-- default null
-  new__question_text 		alias for $2;    -- default null
-  new__abstract_data_type	alias for $3;	-- default null
-  new__presentation_type	alias for $4;	-- default null
-  new__presentation_options	alias for $5;	-- default null
-  new__presentation_alignment	alias for $6;	-- default below
-  new__question_html_p 		alias for $7;	-- default f
-  new__summary_type		alias for $8;
-  new__action_type		alias for $9;	-- default null
-  new__tcl			alias for $10;	-- default null
-  new__table_name		alias for $11;	-- default null
-  new__column_name		alias for $12;	-- default null
-  new__key_name			alias for $13; 	-- default null
-  new__creation_user		alias for $14;	-- default null
-  new__context_id 		alias for $15;	-- default null
-  v_predefined_question_id	survey_predefined_questions.predefined_question_id%TYPE;
+  new__predefined_question_id   alias for $1;   -- default null
+  new__question_text            alias for $2;    -- default null
+  new__abstract_data_type       alias for $3;   -- default null
+  new__presentation_type        alias for $4;   -- default null
+  new__presentation_options     alias for $5;   -- default null
+  new__presentation_alignment   alias for $6;   -- default below
+  new__question_html_p          alias for $7;   -- default f
+  new__summary_type             alias for $8;
+  new__action_type              alias for $9;   -- default null
+  new__tcl                      alias for $10;  -- default null
+  new__table_name               alias for $11;  -- default null
+  new__column_name              alias for $12;  -- default null
+  new__key_name                 alias for $13;  -- default null
+  new__creation_user            alias for $14;  -- default null
+  new__context_id               alias for $15;  -- default null
+  v_predefined_question_id      survey_predefined_questions.predefined_question_id%TYPE;
 begin
     v_predefined_question_id := acs_object__new (
         new__predefined_question_id,
@@ -190,7 +194,6 @@
 end;' language 'plpgsql';
 
 -- API for Survey questions
-
 create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer)
 returns integer as '
 declare
@@ -204,10 +207,10 @@
   new__presentation_type        alias for $8; -- default null
   new__presentation_options     alias for $9; -- default null
   new__presentation_alignment   alias for $10; -- default below
-  new__question_html_p		alias for $11; -- default f
-  new__summary_type		alias for $12;
-  new__answer_description	alias for $13;
-  new__predefined_question_id	alias for $14; -- default null
+  new__question_html_p          alias for $11; -- default f
+  new__summary_type             alias for $12;
+  new__answer_description       alias for $13;
+  new__predefined_question_id   alias for $14; -- default null
   new__creation_user            alias for $15; -- default null
   new__context_id               alias for $16; -- default null
   v_question_id                 integer;
@@ -236,19 +239,67 @@
 
 end;' language 'plpgsql';
 
+
+select define_function_args('survey_question__new','question_id,section_id,sort_order,question_text,abstract_data_type,required_p;t,active_p,presentation_type,presentation_options,presentation_alignment;below,question_html_p;f,summary_type,answer_description,predefined_qustion_id,creation_user,context_id,pretty_id');
+create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer,integer)
+returns integer as '
+declare
+  new__question_id              alias for $1; -- default null
+  new__section_id               alias for $2; -- default null
+  new__sort_order               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__question_html_p          alias for $11; -- default f
+  new__summary_type             alias for $12;
+  new__answer_description       alias for $13;
+  new__predefined_question_id   alias for $14; -- default null
+  new__creation_user            alias for $15; -- default null
+  new__context_id               alias for $16; -- default null
+  new__pretty_id                alias for $17; 
+  v_question_id                 integer;
+begin
+    v_question_id := acs_object__new (
+        new__question_id,
+        ''survey_question'',
+        now(),
+        new__creation_user,
+        null,
+        new__context_id
+    );
+
+    insert into survey_questions
+    (question_id, section_id, sort_order, question_text, 
+    abstract_data_type, required_p, active_p, 
+    presentation_type, presentation_options,
+    presentation_alignment, predefined_question_id,pretty_id)
+    values
+    (v_question_id, new__section_id, new__sort_order, new__question_text, 
+    new__abstract_data_type, new__required_p, new__active_p, 
+    new__presentation_type, new__presentation_options,
+    new__presentation_alignment, new__predefined_question_id,new__pretty_id);
+
+    return v_question_id;
+
+end;' language 'plpgsql';
+
 create or replace function survey_question__remove (integer)
 returns integer as '
 declare
   remove__question_id           alias for $1;
 begin
 
-	delete from survey_question_responses
-	    where question_id=remove__question_id;
+        delete from survey_question_responses
+            where question_id=remove__question_id;
 
-	delete from survey_question_choices
-	    where question_id=remove__question_id;
+        delete from survey_question_choices
+            where question_id=remove__question_id;
 
-	delete from survey_questions
+        delete from survey_questions
             where question_id = remove__question_id;
 
     PERFORM acs_object__delete(remove__question_id);
@@ -296,19 +347,30 @@
 create or replace function survey_response__initial_response_id(integer)
 returns integer as '
 declare
-  p_response_id		alias for $1;
-  v_initial_response_id		integer;
+  p_response_id         alias for $1;
+  v_initial_response_id         integer;
 begin
   select into v_initial_response_id initial_response_id
   from survey_responses
   where response_id = p_response_id;
   
   if v_initial_response_id is NULL then
-	v_initial_response_id := p_response_id;
+        v_initial_response_id := p_response_id;
   end if;
   return v_initial_response_id;
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
+create or replace function survey_response__initial_user_id (integer)
+returns integer as '
+declare
+p_response_id   alias for $1;
+v_user_id       integer;
+begin
+    select into v_user_id creation_user
+    from acs_objects where
+    object_id = survey_response__initial_response_id(p_response_id);
+return v_user_id;
+end;' language 'plpgsql' stable;
 
 -- procedure delete 
 create or replace function survey_response__remove(integer)
@@ -317,12 +379,12 @@
   remove__response_id           alias for $1;
   v_response_row                survey_responses%ROWTYPE;
 begin
-    delete from survey_branches where section_id in (select section_id from survey_sections where survey_id=remove.survey_id);
-    delete from survey_conditions where question_id in (select question_id from survey_questions q, survey_sections s where s.section_id=q.section_id and s.survey_id=remove.survey_id);
+--    delete from survey_branches where section_id in (select section_id from survey_sections where survey_id=remove.survey_id);
+--    delete from survey_conditions where question_id in (select question_id from survey_questions q, survey_sections s where s.section_id=q.section_id and s.survey_id=remove.survey_id);
     for v_response_row in select response_id from survey_responses
-	where initial_response_id=remove__response_id
+        where initial_response_id=remove__response_id
     loop
-	PERFORM survey_response__del(v_response_row.response_id);
+        PERFORM survey_response__del(v_response_row.response_id);
     end loop;
 
     PERFORM survey_response__del(remove__response_id);
@@ -334,50 +396,49 @@
 create or replace function survey_response__del (integer)
 returns integer as '
 declare
-	del__response_id        alias for $1;
-	v_question_response_row survey_question_responses%ROWTYPE;
+    del__response_id        alias for $1;
+    v_question_response_row survey_question_responses%ROWTYPE;
 begin
 
-	for v_question_response_row in select item_id
-		from survey_question_responses, cr_revisions
-		where response_id=del__response_id
-		and attachment_answer=revision_id
-	loop
-		PERFORM content_item__delete(item_id);
-	end loop;
-	
-	delete from survey_question_responses
-		where response_id=del__response_id;
-	delete from survey_responses
-		where response_id=del__response_id;
-	PERFORM acs_object__delete(del__response_id);
+    for v_question_response_row in select item_id
+        from survey_question_responses, cr_revisions
+        where response_id=del__response_id
+        and attachment_answer=revision_id
+    loop
+        PERFORM content_item__delete(item_id);
+    end loop;
+    
+    delete from survey_question_responses
+        where response_id=del__response_id;
+    delete from survey_responses
+        where response_id=del__response_id;
+    PERFORM acs_object__delete(del__response_id);
     return 0;
 
 end;' language 'plpgsql';
 
 create or replace function survey_response__boolean_answer (varchar,integer)
 returns varchar as '
 declare
-	boolean_answer__answer	      alias for $1;
-	boolean_answer__question_id   alias for $2;
-
-	v_answer		      varchar(100);
-	v_presentation_options	      survey_questions.presentation_options%TYPE;
-	v_split_pos		      integer;
+    boolean_answer__answer          alias for $1;
+    boolean_answer__question_id   alias for $2;
+    v_answer              varchar(100);
+    v_presentation_options          survey_questions.presentation_options%TYPE;
+    v_split_pos              integer;
 begin
     if boolean_answer__answer is NOT NULL then
         select into v_presentation_options presentation_options 
-	from survey_questions where question_id=boolean_answer__question_id;
-	
-	v_split_pos:= instr(v_presentation_options, ''/'');
-	
-	if boolean_answer__answer = ''t'' then
-	    v_answer:=substr(v_presentation_options, 1, v_split_pos -1 );
-	end if;   
-	if boolean_answer__answer = ''f'' then
-	    v_answer:=substr(v_presentation_options, v_split_pos + 1 );
-	end if;
-	
+        from survey_questions where question_id=boolean_answer__question_id;
+        
+        v_split_pos:= instr(v_presentation_options, ''/'');
+        
+        if boolean_answer__answer = ''t'' then
+            v_answer:=substr(v_presentation_options, 1, v_split_pos -1 );
+        end if;   
+        if boolean_answer__answer = ''f'' then
+            v_answer:=substr(v_presentation_options, v_split_pos + 1 );
+        end if;
+        
     else
         v_answer := '';
     end if;
