
-- before release, we'll have to copy and paste from the referenced sql
-- files into this one.  For now, we just reference some sql files.

------------------------------------------------------------------------------
-- packages/acs-subsite/sql/application_groups-create.sql
--
-- @author oumi@arsdigita.com
-- @creation-date 2000-02-02
-- @cvs-id $Id: upgrade-4.1.1-4.2.sql,v 1.2 2006/09/25 21:16:34 byronl Exp $
--

------------------------
-- APPLICATION GROUPS --
------------------------


begin
        acs_object_type.create_type (
           supertype => 'group',
           object_type => 'application_group',
           pretty_name => 'Application Group',
           pretty_plural => 'Application Groups',
           table_name => 'application_groups',
           id_column => 'group_id',
           package_name => 'application_group',
           type_extension_table => 'group_types',
           name_method => 'acs_group.name'
        );
end;
/
show errors

create table application_groups (
	group_id		constraint application_groups_group_id_fk
				references groups (group_id)
				constraint application_groups_group_id_pk
				primary key,
        package_id              constraint application_groups_package_id_fk
                                references apm_packages,
                                constraint application_groups_package_id_un
                                unique (package_id)
);


create or replace package application_group
is

 function new (
  group_id              in application_groups.group_id%TYPE default null,
  object_type           in acs_objects.object_type%TYPE
                           default 'application_group',
  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,
  email                 in parties.email%TYPE default null,
  url                   in parties.url%TYPE default null,
  group_name            in groups.group_name%TYPE,
  package_id            in application_groups.package_id%TYPE,
  context_id	in acs_objects.context_id%TYPE default null
 ) return application_groups.group_id%TYPE;

 procedure delete (
   group_id     in application_groups.group_id%TYPE
 );

 function group_id_from_package_id (
   package_id    in application_groups.group_id%TYPE,
   no_complain_p in char default 'f'
 ) return char;

end application_group;
/
show errors


create or replace package body application_group
is

 function new (
  group_id              in application_groups.group_id%TYPE default null,
  object_type           in acs_objects.object_type%TYPE
                           default 'application_group',
  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,
  email                 in parties.email%TYPE default null,
  url                   in parties.url%TYPE default null,
  group_name            in groups.group_name%TYPE,
  package_id            in application_groups.package_id%TYPE,
  context_id	in acs_objects.context_id%TYPE default null
 )
 return application_groups.group_id%TYPE
 is
  v_group_id application_groups.group_id%TYPE;
 begin
  v_group_id := acs_group.new (
               group_id => group_id,
               object_type => object_type,
               creation_date => creation_date,
               creation_user => creation_user,
               creation_ip => creation_ip,
               email => email,
               url => url,
               group_name => group_name,
               context_id => context_id
           );

  insert into application_groups (group_id, package_id) 
    values (v_group_id, package_id);

  return v_group_id;
 end new;


 procedure delete (
    group_id     in application_groups.group_id%TYPE
 )
 is
 begin

   acs_group.delete(group_id); 

 end delete;

 function group_id_from_package_id (
   package_id    in application_groups.group_id%TYPE,
   no_complain_p in char default 'f'
 ) return char
 is
   v_group_id application_groups.group_id%TYPE;
 begin

   select group_id 
   into v_group_id
   from application_groups 
   where package_id = group_id_from_package_id.package_id;

   return v_group_id;

 exception when no_data_found then

   if no_complain_p != 't' then
     raise_application_error(-20000, 'No group_id found for package ' ||
       package_id || ' (' || acs_object.name(package_id) || ').' );
   end if;

   return null;

 end group_id_from_package_id;

end application_group;
/
show errors

insert into group_type_rels
(group_rel_type_id, group_type, rel_type)
values
(acs_object_id_seq.nextval, 'application_group', 'composition_rel');

insert into group_type_rels
(group_rel_type_id, group_type, rel_type)
values
(acs_object_id_seq.nextval, 'application_group', 'membership_rel');

-----------
-- Views --
-----------

create or replace view application_group_element_map as
select g.package_id, g.group_id, 
       m.element_id, m.container_id, m.rel_id, m.rel_type, m.ancestor_rel_type
from application_groups g,
     group_element_map m
where g.group_id = m.group_id;

create or replace view app_group_distinct_element_map as
select distinct package_id, group_id, element_id
from application_group_element_map;

create or replace view app_group_distinct_rel_map as
select distinct package_id, group_id, rel_id, rel_type, ancestor_rel_type
from application_group_element_map;

create or replace view application_group_segments as
select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name
from application_groups g,
     group_element_map m,
     rel_segments s
where g.group_id = m.group_id
  and m.element_id = s.group_id
UNION ALL
select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name
from application_groups g,
     rel_segments s
where g.group_id = s.group_id;


------------------------------------------------------------------------------
-- packages/acs-subsite/sql/user-profiles-create.sql
--
-- @author oumi@arsdigita.com
-- @creation-date 2000-02-02
-- @cvs-id $Id: upgrade-4.1.1-4.2.sql,v 1.2 2006/09/25 21:16:34 byronl Exp $
--

---------------------------
-- UPGRADE EXISTING DATA --
---------------------------

-- ACS's current system:
--
--  - Magic object -2 is the 'Registered Users' party.
--
--  - developers use the views registered_users and cc_registered_users.
--    These views join the users table with the members of group -2.
-- 
-- ACS Subsite 4.1.2 now adds a concept of users (or any party, for that 
-- matter) "belonging" to a subsite.  The upgrade to 4.1.2 needs to 
-- add all registered users to the main site.
--
-- In future versions of ACS, the registration stuff should get RIPPED OUT
-- of the kernel (Rafi agrees).  Right now, we take the path of least change.
--
-- The new and improved system:
--
--  - a group type called 'application_group' is created.  Application groups
--    have a package_id.  The application group serves as a container for
--    all parties that belong to the package_id application instance.
--    (see application-groups-create.sql)
--
--  - An application group called 'Main Site Parties' is created.  Its 
--    package_id points to the main site.
--


-- Assume that application-groups-create has already been run.

set serveroutput on;

declare
    v_package_id           integer;
    v_group_name           varchar(100);
    v_group_id             integer;
    v_rel_id               integer;
    v_segment_id           integer;
    v_segment_name         varchar(100);
begin

    dbms_output.put_line('selecting main site instance name and package_id');

    select package_id, 
           substr(instance_name, 1, 90) || ' Parties',
           substr(instance_name, 1, 60) || ' Registered Users'
    into v_package_id, v_group_name, v_segment_name
    from apm_packages, site_nodes
    where site_nodes.object_id = apm_packages.package_id
      and site_nodes.parent_id is null;
        
    dbms_output.put_line('creating main site application_group');

    v_group_id := application_group.new(
	group_name => v_group_name, 
	package_id => v_package_id
    );

    dbms_output.put_line('adding system users to main site');

    for r in (select user_id, mr.member_state
              from users, membership_rels mr, acs_rels r 
              where user_id = r.object_id_two and object_id_one = -2
                and r.rel_id = mr.rel_id ) loop

	v_rel_id := membership_rel.new (
	    object_id_one => v_group_id,
            object_id_two => r.user_id,
            member_state => r.member_state
	);	

    end loop;

    -- add all the groups in the system to the Main Site Parties group
    -- (except for 'Registered Users' and 'Main Site Parties' itself)
    for r in (select group_id
              from groups
              where not exists(select 1 from group_component_map 
                               where group_id = groups.group_id)
                and group_id not in (-2, v_group_id)) loop

	v_rel_id := composition_rel.new (
	    object_id_one => v_group_id,
            object_id_two => r.group_id
	);	

    end loop;

    -- add the 'Main Site Registered Members' segment:
    v_segment_id := rel_segment.new(
        segment_name=> v_segment_name,
        group_id => v_group_id,
        rel_type => 'membership_rel'
    );

end;
/
show errors

--------------------------------------------------------------
-- acs-subsite-create.sql
-- oumi@arsdigita.com
-- 2/20/2001
--
-- CHANGES
--
-- Added party_names view.
--------------------------------------------------------------

-- This view lets us avoid using acs_object.name to get party_names.
-- 
create or replace view party_names
as
select p.party_id,
       decode(groups.group_id,
              null, decode(persons.person_id, 
                           null, p.email,
                           persons.first_names || ' ' || persons.last_name),
              groups.group_name) as party_name
from parties p,
     groups,
     persons
where p.party_id = groups.group_id(+)
  and p.party_id = persons.person_id(+);




--------------------------------------------------------------
-- subsite-callbacks-create.sql
-- mbryzek@arsdigita.com
-- 2/20/2001
--------------------------------------------------------------

-- /packages/acs-subsite/sql/subsite-group-callbacks-create.sql

-- Defines a simple callback system to allow other applications to
-- register callbacks when groups of a given type are created. 

-- Copyright (C) 2001 ArsDigita Corporation
-- @author Michael Bryzek (mbryzek@arsdigita.com)
-- @creation-date 2001-02-20

-- $Id: upgrade-4.1.1-4.2.sql,v 1.2 2006/09/25 21:16:34 byronl Exp $

-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html


-- What about instead of? 
   -- insead_of viewing the group, go to the portal
   -- instead of inserting the group with package_instantiate_object, go here 

create table subsite_callbacks (
       callback_id         integer 
			   constraint sgc_callback_id_pk primary key,
       event_type          varchar(100) not null
			   constraint sgc_event_type_ck check(event_type in ('insert','update','delete')),
       object_type         varchar(100) not null
			   constraint sgc_object_type_fk references acs_object_types
                           on delete cascade,
       callback		   varchar(300) not null,
       callback_type       varchar(100) not null
			   constraint sgc_callback_type_ck check(callback_type in ('tcl')),
       sort_order          integer default(1) not null
			   constraint sgc_sort_order_ck check(sort_order >= 1),
       -- allow only one callback of a given type for given 
       constraint subsite_callbacks_un unique (object_type, event_type, callback_type, callback)
);

comment on table subsite_callbacks is '
	Applications can register callbacks that are triggered
	whenever a group of a specified type is created. The callback
	must expect the following arguments: 
	  * object_id: The object that just got created
	  * node_id: The node_id where the object got created
	  * package_id: The package_id from where the object got created
	These are passed in the following way:
	  * tcl procedure: Using named parameters (e.g. -object_id $object_id)
	All callbacks must accept all of these parameters.
';

comment on column subsite_callbacks.event_type is '
	The type of event we are monitoring. The keywords here are used
	by the applications to determine which callbacks to trigger.
';      

comment on column subsite_callbacks.object_type is '
	The object type to monitor. Whenever an object of this type is
	created, the subsite package will check for a registered
	callbacks.
';

comment on column subsite_callbacks.callback_type is ' 
	The type of the callback. This determines how the callback is
	executed. Currenlty only a tcl type is supported but other
	types may be added in the future. 
';


comment on column subsite_callbacks.callback is '
	The actual callback. This can be the name of a plsql function
	or procedure, a url stub relative to the node at which package
	id is mounted, or the name of a tcl function.
';

comment on column subsite_callbacks.sort_order is '
	The order in which the callbacks should fire. This is
	important when you need to ensure that one event fires before
	another (e.g. you must mount a portals application before the
	bboard application)
';      


create or replace package subsite_callback as

  function new (
  --/** Registers a new callback. If the same callback exists as
  --    defined in the unique constraint on the table, does 
  --    nothing but returns the existing callback_id.
  -- 
  --    @author Michael Bryzek (mbryzek@arsdigita.com)
  --    @creation-date 2001-02-20
  -- 
  --*/
       callback_id         IN subsite_callbacks.callback_id%TYPE default null,
       event_type          IN subsite_callbacks.event_type%TYPE,
       object_type         IN subsite_callbacks.object_type%TYPE,
       callback		   IN subsite_callbacks.callback%TYPE,
       callback_type       IN subsite_callbacks.callback_type%TYPE,
       sort_order          IN subsite_callbacks.sort_order%TYPE default null
  ) return subsite_callbacks.callback_id%TYPE;

  procedure delete (
  --/** Deletes the specified callback
  -- 
  --    @author Michael Bryzek (mbryzek@arsdigita.com)
  --    @creation-date 2001-02-20
  -- 
  --*/
  
       callback_id         IN subsite_callbacks.callback_id%TYPE
  );

end subsite_callback;
/
show errors;



create or replace package body subsite_callback as

  function new (
       callback_id         IN subsite_callbacks.callback_id%TYPE default null,
       event_type          IN subsite_callbacks.event_type%TYPE,
       object_type         IN subsite_callbacks.object_type%TYPE,
       callback		   IN subsite_callbacks.callback%TYPE,
       callback_type       IN subsite_callbacks.callback_type%TYPE,
       sort_order          IN subsite_callbacks.sort_order%TYPE default null
  ) return subsite_callbacks.callback_id%TYPE
  IS
    v_callback_id  subsite_callbacks.callback_id%TYPE;
    v_sort_order   subsite_callbacks.sort_order%TYPE;
  BEGIN

    if new.callback_id is null then
       select acs_object_id_seq.nextval into v_callback_id from dual;
    else
       v_callback_id := new.callback_id;
    end if;
   
    if new.sort_order is null then
       -- Make this the next event for this object_type/event_type combination
       select nvl(max(sort_order),0) + 1 into v_sort_order
         from subsite_callbacks
        where object_type = new.object_type
          and event_type = new.event_type;
    else
       v_sort_order := new.sort_order;
    end if;

    begin 
      insert into subsite_callbacks
      (callback_id, event_type, object_type, callback, callback_type, sort_order)
      values
      (v_callback_id, new.event_type, new.object_type, new.callback, new.callback_type, v_sort_order);
     exception when dup_val_on_index then
      select callback_id into v_callback_id
        from subsite_callbacks
       where event_type = new.event_type
         and object_type = new.object_type
         and callback_type = new.callback_type
         and callback = new.callback;
    end;
    return v_callback_id;

  END new;


  procedure delete (
       callback_id         IN subsite_callbacks.callback_id%TYPE
  )
  is
  begin
     delete from subsite_callbacks where callback_id=subsite_callback.delete.callback_id;
  end delete;

end subsite_callback;
/
show errors;


