Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.5-4.5.1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.5-4.5.1.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.5-4.5.1.sql	17 Aug 2002 17:42:45 -0000	1.1
+++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.5-4.5.1.sql	30 Nov 2002 17:19:07 -0000	1.2
@@ -715,6 +715,448 @@
 show errors
 
 
+-- new function apm_package.parent_id
+
+create or replace package apm_package
+as
+
+function new (
+  package_id		in apm_packages.package_id%TYPE 
+			default null,
+  instance_name		in apm_packages.instance_name%TYPE
+			default null,
+  package_key		in apm_packages.package_key%TYPE,
+  object_type		in acs_objects.object_type%TYPE
+			default 'apm_package', 
+  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 apm_packages.package_id%TYPE;
+
+  procedure delete (
+   package_id		in apm_packages.package_id%TYPE
+  );
+
+  function initial_install_p (
+	package_key		in apm_packages.package_key%TYPE
+  ) return integer;
+
+  function singleton_p (
+	package_key		in apm_packages.package_key%TYPE
+  ) return integer;
+
+  function num_instances (
+	package_key		in apm_package_types.package_key%TYPE
+  ) return integer;
+
+  function name (
+    package_id		in apm_packages.package_id%TYPE
+  ) return varchar2;
+
+ -- Enable a package to be utilized by a subsite.
+  procedure enable (
+   package_id		in apm_packages.package_id%TYPE
+  );
+  
+  procedure disable (
+   package_id		in apm_packages.package_id%TYPE
+  );
+
+  function highest_version (
+   package_key		in apm_package_types.package_key%TYPE
+  ) return apm_package_versions.version_id%TYPE;
+  
+    function parent_id (
+        package_id in apm_packages.package_id%TYPE
+    ) return apm_packages.package_id%TYPE;
+
+end apm_package;
+/
+show errors
+
+create or replace package body apm_package
+as
+  procedure initialize_parameters (
+    package_id			in apm_packages.package_id%TYPE,
+    package_key		        in apm_package_types.package_key%TYPE
+  )
+  is
+   v_value_id apm_parameter_values.value_id%TYPE;
+   cursor cur is
+       select parameter_id, default_value
+       from apm_parameters
+       where package_key = initialize_parameters.package_key;
+  begin
+    -- need to initialize all params for this type
+    for cur_val in cur
+      loop
+        v_value_id := apm_parameter_value.new(
+          package_id => initialize_parameters.package_id,
+          parameter_id => cur_val.parameter_id,
+          attr_value => cur_val.default_value
+        ); 
+      end loop;   
+  end initialize_parameters;
+
+ function new (
+  package_id		in apm_packages.package_id%TYPE 
+			default null,
+  instance_name		in apm_packages.instance_name%TYPE
+			default null,
+  package_key		in apm_packages.package_key%TYPE,
+  object_type		in acs_objects.object_type%TYPE
+			default 'apm_package', 
+  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 apm_packages.package_id%TYPE
+  is 
+   v_singleton_p integer;
+   v_package_type apm_package_types.package_type%TYPE;
+   v_num_instances integer;
+   v_package_id apm_packages.package_id%TYPE;
+   v_instance_name apm_packages.instance_name%TYPE; 
+  begin
+   v_singleton_p := apm_package.singleton_p(
+			package_key => apm_package.new.package_key
+		    );
+   v_num_instances := apm_package.num_instances(
+			package_key => apm_package.new.package_key
+		    );
+  
+   if v_singleton_p = 1 and v_num_instances >= 1 then
+       select package_id into v_package_id 
+       from apm_packages
+       where package_key = apm_package.new.package_key;
+       return v_package_id;
+   else
+       v_package_id := acs_object.new(
+          object_id => package_id,
+          object_type => object_type,
+          creation_date => creation_date,
+          creation_user => creation_user,
+	  creation_ip => creation_ip,
+	  context_id => context_id
+	 );
+       if instance_name is null then 
+	 v_instance_name := package_key || ' ' || v_package_id;
+       else
+	 v_instance_name := instance_name;
+       end if;
+
+       select package_type into v_package_type
+       from apm_package_types
+       where package_key = apm_package.new.package_key;
+
+       insert into apm_packages
+       (package_id, package_key, instance_name)
+       values
+       (v_package_id, package_key, v_instance_name);
+
+       if v_package_type = 'apm_application' then
+	   insert into apm_applications
+	   (application_id)
+	   values
+	   (v_package_id);
+       else
+	   insert into apm_services
+	   (service_id)
+	   values
+	   (v_package_id);
+       end if;
+
+       initialize_parameters(
+	   package_id => v_package_id,
+	   package_key => apm_package.new.package_key
+       );
+       return v_package_id;
+
+  end if;
+end new;
+  
+  procedure delete (
+   package_id		in apm_packages.package_id%TYPE
+  )
+  is
+    cursor all_values is
+    	select value_id from apm_parameter_values
+	where package_id = apm_package.delete.package_id;
+    cursor all_site_nodes is
+    	select node_id from site_nodes
+	where object_id = apm_package.delete.package_id;
+  begin
+    -- Delete all parameters.
+    for cur_val in all_values loop
+    	apm_parameter_value.delete(value_id => cur_val.value_id);
+    end loop;    
+    delete from apm_applications where application_id = apm_package.delete.package_id;
+    delete from apm_services where service_id = apm_package.delete.package_id;
+    delete from apm_packages where package_id = apm_package.delete.package_id;
+    -- Delete the site nodes for the objects.
+    for cur_val in all_site_nodes loop
+    	site_node.delete(cur_val.node_id);
+    end loop;
+    -- Delete the object.
+    acs_object.delete (
+	object_id => package_id
+    );
+   end delete;
+
+    function initial_install_p (
+	package_key		in apm_packages.package_key%TYPE
+    ) return integer
+    is
+        v_initial_install_p integer;
+    begin
+        select 1 into v_initial_install_p
+	from apm_package_types
+	where package_key = initial_install_p.package_key
+        and initial_install_p = 't';
+	return v_initial_install_p;
+	
+	exception 
+	    when NO_DATA_FOUND
+            then
+                return 0;
+    end initial_install_p;
+
+    function singleton_p (
+	package_key		in apm_packages.package_key%TYPE
+    ) return integer
+    is
+        v_singleton_p integer;
+    begin
+        select 1 into v_singleton_p
+	from apm_package_types
+	where package_key = singleton_p.package_key
+        and singleton_p = 't';
+	return v_singleton_p;
+	
+	exception 
+	    when NO_DATA_FOUND
+            then
+                return 0;
+    end singleton_p;
+
+    function num_instances (
+	package_key		in apm_package_types.package_key%TYPE
+    ) return integer
+    is
+        v_num_instances integer;
+    begin
+        select count(*) into v_num_instances
+	from apm_packages
+	where package_key = num_instances.package_key;
+        return v_num_instances;
+	
+	exception
+	    when NO_DATA_FOUND
+	    then
+	        return 0;
+    end num_instances;
+
+  function name (
+    package_id		in apm_packages.package_id%TYPE
+  ) return varchar2
+  is
+    v_result apm_packages.instance_name%TYPE;
+  begin
+    select instance_name into v_result
+    from apm_packages
+    where package_id = name.package_id;
+
+    return v_result;
+  end name;
+
+    procedure enable (
+       package_id			in apm_packages.package_id%TYPE
+    )
+    is
+    begin
+      update apm_packages 
+      set enabled_p = 't'
+      where package_id = enable.package_id;	
+    end enable;
+    
+    procedure disable (
+       package_id			in apm_packages.package_id%TYPE
+    )
+    is
+    begin
+      update apm_packages 
+      set enabled_p = 'f'
+      where package_id = disable.package_id;	
+    end disable;
+	
+   function highest_version (
+     package_key		in apm_package_types.package_key%TYPE
+   ) return apm_package_versions.version_id%TYPE
+   is
+     v_version_id apm_package_versions.version_id%TYPE;
+   begin
+     select version_id into v_version_id
+	from apm_package_version_info i 
+	where apm_package_version.sortable_version_name(version_name) = 
+             (select max(apm_package_version.sortable_version_name(v.version_name))
+	             from apm_package_version_info v where v.package_key = highest_version.package_key)
+	and package_key = highest_version.package_key;
+     return v_version_id;
+     exception
+         when NO_DATA_FOUND
+         then
+         return 0;
+   end highest_version;
+
+    function parent_id (
+        package_id in apm_packages.package_id%TYPE
+    ) return apm_packages.package_id%TYPE
+    is
+        v_package_id apm_packages.package_id%TYPE;
+    begin
+        select sn1.object_id
+        into v_package_id
+        from site_nodes sn1
+        where sn1.node_id = (select sn2.parent_id
+                             from site_nodes sn2
+                             where sn2.object_id = apm_package.parent_id.package_id);
+
+        return v_package_id;
+
+        exception when NO_DATA_FOUND then
+            return -1;
+    end parent_id;
+
+end apm_package;
+/
+show errors
+
+
 -- acs-permissions-create.sql
 
+create or replace package body acs_permission
+as
+  procedure grant_permission (
+    object_id	 acs_permissions.object_id%TYPE,
+    grantee_id	 acs_permissions.grantee_id%TYPE,
+    privilege	 acs_permissions.privilege%TYPE
+  )
+  as
+  begin
+    insert into acs_permissions
+      (object_id, grantee_id, privilege)
+    values
+      (object_id, grantee_id, privilege);
+  exception
+    when dup_val_on_index then
+      return;
+  end grant_permission;
+  --
+  procedure revoke_permission (
+    object_id	 acs_permissions.object_id%TYPE,
+    grantee_id	 acs_permissions.grantee_id%TYPE,
+    privilege	 acs_permissions.privilege%TYPE
+  )
+  as
+  begin
+    delete from acs_permissions
+    where object_id = revoke_permission.object_id
+    and grantee_id = revoke_permission.grantee_id
+    and privilege = revoke_permission.privilege;
+  end revoke_permission;
 
+  function permission_p (
+    object_id	 acs_objects.object_id%TYPE,
+    party_id	 parties.party_id%TYPE,
+    privilege	 acs_privileges.privilege%TYPE
+  ) return char
+  as
+    exists_p char(1);
+  begin
+    --
+    -- direct permissions
+    select decode(count(*),0,'f','t') into exists_p
+    from dual where exists (
+        select 'x'
+          from acs_object_grantee_priv_map
+         where object_id = permission_p.object_id
+           and grantee_id = permission_p.party_id
+           and privilege = permission_p.privilege);
+    if exists_p = 't' then
+        return 't';
+    end if;
+    --
+    -- public-like permissions
+    select decode(count(*),0,'f','t') into exists_p
+    from dual where exists (
+        select 'x'
+          from acs_object_grantee_priv_map
+         where object_id = permission_p.object_id
+           and 0 = permission_p.party_id
+           and privilege = permission_p.privilege
+           and grantee_id = -1);
+    if exists_p = 't' then
+        return 't';
+    end if;
+    --
+    -- public permissions
+    select decode(count(*),0,'f','t') into exists_p
+    from dual where exists (
+        select 'x'
+          from acs_object_grantee_priv_map m, users u
+         where object_id = permission_p.object_id
+           and u.user_id = permission_p.party_id
+           and privilege = permission_p.privilege
+           and m.grantee_id = -1);
+    if exists_p = 't' then
+        return 't';
+    end if;
+    --
+    -- group permmissions
+    select decode(count(*),0,'f','t') into exists_p
+    from dual where exists (
+          select 'x'
+          from acs_object_grantee_priv_map ogpm,
+               group_approved_member_map gmm
+         where object_id = permission_p.object_id
+           and gmm.member_id = permission_p.party_id
+           and privilege = permission_p.privilege
+           and ogpm.grantee_id = gmm.group_id);
+    if exists_p = 't' then
+        return 't';
+    end if;
+    --
+    -- relational segment approved group
+    select decode(count(*),0,'f','t') into exists_p
+    from dual where exists (
+        select 'x'
+          from acs_object_grantee_priv_map ogpm,
+               rel_seg_approved_member_map rsmm
+         where object_id = permission_p.object_id
+           and rsmm.member_id = permission_p.party_id
+           and privilege = permission_p.privilege
+           and ogpm.grantee_id = rsmm.segment_id);
+    if exists_p = 't' then
+        return 't';
+    end if;
+    return exists_p;
+  end;
+  --
+end acs_permission;
+/
+show errors
+
+-- add indices (yon)
+
+create index users_email_verified_idx on users (email_verified_p);
+create index member_rels_member_state_idx on membership_rels (member_state);
+create index site_nodes_parent_id_idx on site_nodes(parent_id,object_id,node_id);
