Index: openacs-4/packages/acs-reference/sql/postgresql/acs-reference-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/acs-reference-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-reference/sql/postgresql/acs-reference-create.sql	17 Jul 2001 01:44:58 -0000	1.1
+++ openacs-4/packages/acs-reference/sql/postgresql/acs-reference-create.sql	17 Jul 2001 03:59:17 -0000	1.2
@@ -22,20 +22,17 @@
 show errors
 
 -- Create the basic object type used to represent a reference database
-
-begin
-    acs_object_type.create_type (
-        supertype     => 'acs_object',
-        object_type   => 'acs_reference_repository',
-        pretty_name   => 'ACS Reference Repository',
-        pretty_plural => 'ACS Reference Repositories', 
-        table_name    => 'acs_reference_repositories',
-        id_column     => 'repository_id',
-        name_method   => 'acs_object.default_name'
+select acs_object_type__create_type (
+        'acs_object',
+        'acs_reference_repository',
+        'ACS Reference Repository',
+        'ACS Reference Repositories', 
+        'acs_reference_repositories',
+        'repository_id',
+        'f',
+	null,
+	'acs_object.default_name'
 );
-end;
-/
-show errors
 
 -- A table to store metadata for each reference database
 -- add functions to do exports and imports to selected tables.
@@ -45,175 +42,150 @@
 			constraint arr_repository_id_fk references acs_objects (object_id)
 			constraint arr_repository_id_pk primary key,
     -- what is the table name we are monitoring
-    table_name		varchar2(100)  
+    table_name		varchar(100)  
 			constraint arr_table_name_nn not null
 			constraint arr_table_name_uq unique,
     -- is this external or internal data
     internal_data_p     char(1)       
 			constraint arr_internal_data_p_ck
         		check (internal_data_p in ('t','f')),
     -- Does this source include pl/sql package?
-    package_name	varchar2(100)
+    package_name	varchar(100)
 			constraint arr_package_name_uq unique,
     -- last updated
-    last_update		date,
+    last_update		timestamp,
     -- where is this data from
-    source		varchar2(1000),
-    source_url		varchar2(255),
+    source		varchar(1000),
+    source_url		varchar(255),
     -- should default to today
-    effective_date	date default sysdate,
-    expiry_date		date,
+    effective_date	timestamp -- default sysdate
+    expiry_date		timestamp,
     -- a text field to hold the maintainer
     maintainer_id	integer
 			constraint arr_maintainer_id_fk references persons(person_id),
     -- this could be ancillary docs, pdf's etc
+    -- needs to be fixed for PG
     notes blob
 );
 
 -- API
 
 create or replace package acs_reference
 as
-    function new (
-        repository_id   in acs_reference_repositories.repository_id%TYPE default null,
-        table_name      in acs_reference_repositories.table_name%TYPE,
-        internal_data_p in acs_reference_repositories.internal_data_p%TYPE default 'f',
-        package_name    in acs_reference_repositories.package_name%TYPE default null,
-        last_update     in acs_reference_repositories.last_update%TYPE default sysdate,
-        source          in acs_reference_repositories.source%TYPE default null,
-        source_url      in acs_reference_repositories.source_url%TYPE default null,
-        effective_date  in acs_reference_repositories.effective_date%TYPE default sysdate,
-        expiry_date     in acs_reference_repositories.expiry_date%TYPE default null,
-        notes           in acs_reference_repositories.notes%TYPE default empty_blob(), 
-        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,
-        object_type     in acs_objects.object_type%TYPE default 'acs_reference_repository',
-        first_names     in persons.first_names%TYPE default null,
-        last_name       in persons.last_name%TYPE default null
-    ) return acs_objects.object_id%TYPE;
+create function acs_reference__new (integer,varchar,char,varchar,timestamp,
+varchar,varchar,timestamp,timestamp,integer,blob,timestamp,
+integer,varchar,integer)
+returns integer as '
+declare
+    repository_id   alias for $1; -- default null
+    table_name      alias for $2; -- 
+    internal_data_p alias for $3; -- default 'f'
+    package_name    alias for $4; -- default null
+    last_update     alias for $5; -- default sysdate
+    source          alias for $6; -- default null
+    source_url      alias for $7; -- default null
+    effective_date  alias for $8; -- default sysdate
+    expiry_date     alias for $9; -- default null
+    maintainer_id   alias for $10; -- default null
+    notes           alias for $11; -- default empty_blob()
+-- I really see no need for these as parameters
+--    creation_date   alias for $12; -- default sysdate
+    first_names     alias for $12; -- default null
+    last_names      alias for $13; -- default null
+    creation_ip     alias for $14; -- default null
+    object_type     alias for $15; -- default 'acs_reference_repository'
+    creation_user   alias for $16; -- default null
+)
+    if object_type is null then
+        v_object_type := 'acs_reference_repository';
+    else
+        v_object_type := object_type;
+    end if;
 
-    procedure delete (
-        repository_id in acs_reference_repositories.repository_id%TYPE
+    v_repository_id := acs_object__new (
+         object_id,    
+         sysdate(),
+         creation_user,
+         creation_ip,
+         v_object_type  
     );
 
-    function is_expired_p (
-	repository_id integer
-    ) return char;
+    -- This logic isn't correct as the maintainer could already exist
+    -- The way around this is a little clunky as you can search persons
+    -- then pick an existing person or add a new one, to many screens!
+    -- I really doubt the need for person anyway.
+    --
+    -- It probably needs to just be a UI function and pass
+    -- in the value for maintainer.
+    --
+    -- IN OTHER WORDS
+    -- Guaranteed to probably break in the future if you depend on
+    -- first_names and last_name to still exist as a param
+    -- This needs to be updated in the Oracle version also
+    -- NEEDS TO BE FIXED - jag
 
- end acs_reference;
-/
-show errors
-
-
-create or replace package body acs_reference
-as
-    function new (
-        repository_id   in acs_reference_repositories.repository_id%TYPE default null,
-        table_name      in acs_reference_repositories.table_name%TYPE,
-        internal_data_p in acs_reference_repositories.internal_data_p%TYPE default 'f',
-        package_name    in acs_reference_repositories.package_name%TYPE default null,
-        last_update     in acs_reference_repositories.last_update%TYPE default sysdate,
-        source          in acs_reference_repositories.source%TYPE default null,
-        source_url      in acs_reference_repositories.source_url%TYPE default null,
-        effective_date  in acs_reference_repositories.effective_date%TYPE default sysdate,
-        expiry_date     in acs_reference_repositories.expiry_date%TYPE default null,
-        notes           in acs_reference_repositories.notes%TYPE default empty_blob(), 
-        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,
-        object_type     in acs_objects.object_type%TYPE default 'acs_reference_repository',
-        first_names     in persons.first_names%TYPE default null,
-        last_name       in persons.last_name%TYPE default null
-    ) return acs_objects.object_id%TYPE
-    is
-        v_repository_id acs_reference_repositories.repository_id%TYPE;
-        v_maintainer_id persons.person_id%TYPE;
-    begin
-        v_repository_id := acs_object.new (
-             object_id     => repository_id,
-             creation_date => creation_date,
-             creation_user => creation_user,
-             creation_ip   => creation_ip,
-             object_type   => object_type
-        );
-
-	if first_names is not null and last_name is not null then
-            v_maintainer_id := person.new (
-	         first_names   => first_names,
-	         last_name     => last_name,
-		 email         => null
+    if first_names is not null and last_name is not null and maintainer_id is null then
+        v_maintainer_id := person__new (
+	    first_names,
+            last_name,  
+            null  -- email       
             );
-	else
+	else if maintainer_id is not null
+           v_maintainer_id := maintainer_id;
+        else 
 	    v_maintainer_id := null;
 	end if;
 
         insert into acs_reference_repositories
-            (repository_id, 
-             table_name,
-             internal_data_p,
-             last_update,
-             package_name,
-             source, 
-             source_url,
-             effective_date,
-             expiry_date,
-             maintainer_id,
-             notes)
+            (repository_id,table_name,internal_data_p,
+             last_update,package_name,source, 
+             source_url,effective_date,expiry_date,
+             maintainer_id,notes)
         values 
-            (v_repository_id, 
-             table_name,
-             internal_data_p,
-             last_update,
-             package_name,
-             source, 
-             source_url,
-             effective_date,
-             expiry_date,
-             v_maintainer_id,
-             notes);
+            (v_repository_id,table_name,internal_data_p,
+             last_update,package_name,source,source_url,
+             effective_date,expiry_date,v_maintainer_id,notes);
         return v_repository_id;    
-    end new;
+end;
+' language 'plpgsql';
 
-    procedure delete (
-        repository_id in acs_reference_repositories.repository_id%TYPE
-    )
-    is
-	v_maintainer_id integer;
-    begin
-	select maintainer_id into v_maintainer_id
-	from   acs_reference_repositories
-	where  repository_id = acs_reference.delete.repository_id;
 
-        delete from acs_reference_repositories
-        where  repository_id = acs_reference.delete.repository_id;
+create function acs_reference__delete (integer)
+returns integer as '
+declare
+    repository_id alias for $1;
+begin
+    select maintainer_id into v_maintainer_id
+    from   acs_reference_repositories
+    where  repository_id = acs_reference__delete.repository_id;
 
-        acs_object.delete(repository_id);
-	person.delete(v_maintainer_id);
+    delete from acs_reference_repositories
+    where repository_id = acs_reference__delete.repository_id;
 
-    end delete;
+    acs_object__delete(repository_id);
+    -- Who added this it is ridiculous
+    -- a person could exist from something else
+--  person__delete(v_maintainer_id);
+end;
+' language 'plpgsql';
 
-    function is_expired_p (
-	repository_id integer
-    ) return char
-    is
-	v_expiry_date date;
-    begin
-	select expiry_date into v_expiry_date
-	from   acs_reference_repositories
-	where  repository_id = is_expired_p.repository_id;
+create function acs_reference__is_expired_p (integer)
+returns char as '
+declare
+    repository_id alias for $1;
+begin
+    select expiry_date into v_expiry_date
+    from   acs_reference_repositories
+    where  repository_id = is_expired_p.repository_id;
 
-	if nvl(v_expiry_date,sysdate+1) < sysdate then
-	    return 't';
-	else
-	    return 'f';
-	end if;
-    end;	    
+    if nvl(v_expiry_date,sysdate()+1) < sysdate() then
+        return 't';
+    else
+        return 'f';
+    end if;
+end;
+' language 'plpgsql';
 
-end acs_reference;
-/
-show errors
-
 -- now load the reference data packages
 
-@@acs-reference-data
+/@acs-reference-data
