Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b3-5.0.0b4.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b3-5.0.0b4.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b3-5.0.0b4.sql	14 Jan 2004 17:35:48 -0000	1.3
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b3-5.0.0b4.sql	26 Jan 2004 15:39:45 -0000	1.4
@@ -531,25 +531,26 @@
 declare
   drop_type__rel_type               alias for $1;  
   drop_type__cascade_p              alias for $2;  -- default ''f''  
+  v_cascade                         boolean;
 begin
     -- XXX do cascade_p.
     -- JCD: cascade_p seems to be ignored in acs_o_type__drop_type anyway...
-
     if drop_type__cascade_p is null then 
-	drop_type__cascade_p := ''f'';
+	v_cascade_p := ''f'';
+    else 
+	v_cascade_p := drop_type__cascade_p;
     end if;
 
     delete from acs_rel_types
 	  where rel_type = drop_type__rel_type;
 
     PERFORM acs_object_type__drop_type(drop_type__rel_type, 
-                                       drop_type__cascade_p);
+                                       v_cascade_p);
 
     return 0; 
 end;' language 'plpgsql';
 
 
-
 create or replace function apm__unregister_package (varchar,boolean)
 returns integer as '
 declare
@@ -771,7 +772,8 @@
 
 ---- DRB: fixes bug 1144
 
-create or replace view registered_users
+drop view registered_users;
+create view registered_users
 as
   select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state
   from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
@@ -786,7 +788,9 @@
   and mr.member_state = 'approved'
   and u.email_verified_p = 't';
 
-create or replace view cc_users
+
+drop view cc_users;
+create view cc_users
 as
 select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
 from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
@@ -800,3 +804,176 @@
   and m.container_id = m.group_id
   and m.rel_type = 'membership_rel';
 
+drop function acs__add_user(int4,varchar,timestamptz,int4,varchar,varchar,varchar,varchar,varchar,bpchar,bpchar,varchar,varchar,varchar,bool,varchar);
+
+create or replace function acs__add_user (
+    integer,      -- user_id
+    varchar,      -- object_type
+    timestamptz,  -- creation_date
+    integer,      -- creation_user
+    varchar,      -- cretion_ip
+    integer,      -- authority_id; default 'local'
+    varchar,      -- username
+    varchar,      -- email
+    varchar,      -- url
+    varchar,      -- first_names
+    varchar,      -- last_name
+    char,         -- password
+    char,         -- salt
+    varchar,      -- screen_name
+    boolean,      -- email_verified_p
+    varchar       -- member_state
+)
+returns integer as '
+declare
+    p_user_id              alias for $1;  -- default null    
+    p_object_type          alias for $2;  -- default ''user''
+    p_creation_date        alias for $3;  -- default now()
+    p_creation_user        alias for $4;  -- default null
+    p_creation_ip          alias for $5;  -- default null
+    p_authority_id         alias for $6;  -- defaults to local authority
+    p_username             alias for $7;  --
+    p_email                alias for $8;  
+    p_url                  alias for $9;  -- default null
+    p_first_names          alias for $10;  
+    p_last_name            alias for $11;  
+    p_password             alias for $12; 
+    p_salt                 alias for $13; 
+    p_screen_name          alias for $14; -- default null
+    p_email_verified_p     alias for $15; -- default ''t''
+    p_member_state         alias for $16; -- default ''approved''
+    v_user_id              users.user_id%TYPE;
+    v_rel_id               membership_rels.rel_id%TYPE;
+begin
+    v_user_id := acs_user__new (
+        p_user_id, 
+        p_object_type, 
+        p_creation_date,
+        p_creation_user, 
+        p_creation_ip, 
+        p_authority_id,
+        p_username,
+        p_email,
+        p_url, 
+        p_first_names, 
+        p_last_name, 
+        p_password,
+	p_salt, 
+        p_screen_name, 
+        p_email_verified_p,
+        null                  -- context_id
+    );
+   
+    v_rel_id := membership_rel__new (
+      null,
+      ''membership_rel'',
+      acs__magic_object_id(''registered_users''),      
+      v_user_id,
+      p_member_state,
+      null,
+      null);
+
+    PERFORM acs_permission__grant_permission (
+      v_user_id,
+      v_user_id,
+      ''read''
+      );
+
+    PERFORM acs_permission__grant_permission (
+      v_user_id,
+      v_user_id,
+      ''write''
+      );
+
+    return v_user_id;
+   
+end;' language 'plpgsql';
+
+
+drop function acs_user__new(int4,varchar,timestamptz,int4,varchar,varchar,varchar,varchar,varchar,bpchar,bpchar,varchar,varchar,varchar,bool,int4);
+
+create or replace function acs_user__new (
+    integer,      -- user_id
+    varchar,      -- object_type
+    timestamptz,  -- creation_date
+    integer,      -- creation_user
+    varchar,      -- creation_ip
+    integer,      -- authority_id; default 'local'
+    varchar,      -- username
+    varchar,      -- email
+    varchar,      -- url
+    varchar,      -- first_names
+    varchar,      -- last_name
+    char,         -- password
+    char,         -- salt
+    varchar,      -- screen_name
+    boolean,      -- email_verified_p
+    integer       -- context_id
+)
+returns integer as '
+declare
+    p_user_id                  alias for $1;  -- default null  
+    p_object_type              alias for $2;  -- default ''user''
+    p_creation_date            alias for $3;  -- default now()
+    p_creation_user            alias for $4;  -- default null
+    p_creation_ip              alias for $5;  -- default null
+    p_authority_id             alias for $6;  -- defaults to local authority
+    p_username                 alias for $7;  --
+    p_email                    alias for $8;  
+    p_url                      alias for $9;  -- default null
+    p_first_names              alias for $10;  
+    p_last_name                alias for $11;  
+    p_password                 alias for $12; 
+    p_salt                     alias for $13; 
+    p_screen_name              alias for $14; -- default null
+    p_email_verified_p         alias for $15; -- default ''t''
+    p_context_id               alias for $16; -- default null
+    v_user_id                  users.user_id%TYPE;
+    v_authority_id             auth_authorities.authority_id%TYPE;
+    v_person_exists            varchar;			
+begin
+    v_user_id := p_user_id;
+
+    select case when count(*) = 0 then ''f'' else ''t'' end into v_person_exists
+    from persons where person_id = v_user_id;
+
+    if v_person_exists = ''f'' then
+        v_user_id := person__new(
+            v_user_id, 
+            p_object_type,
+            p_creation_date, 
+            p_creation_user, 
+            p_creation_ip,
+            p_email, 
+            p_url, 
+            p_first_names, 
+            p_last_name, 
+            p_context_id
+        );
+    else
+     update acs_objects set object_type = ''user'' where object_id = v_user_id;
+    end if;
+
+    -- default to local authority
+    if p_authority_id is null then
+        select authority_id
+        into   v_authority_id
+        from   auth_authorities
+        where  short_name = ''local'';
+    else
+        v_authority_id := p_authority_id;
+    end if;
+
+    insert into users
+       (user_id, authority_id, username, password, salt, screen_name, email_verified_p)
+    values
+       (v_user_id, v_authority_id, p_username, p_password, p_salt, p_screen_name, p_email_verified_p);
+
+    insert into user_preferences
+      (user_id)
+      values
+      (v_user_id);
+
+    return v_user_id;
+  
+end;' language 'plpgsql';
