-- sql/api-create.sql
--
-- Portal PL/SQL API.
--
-- Ian Baker <ibaker@arsdigita.com>
-- $Id: api-create.sql,v 1.1.1.2 2002/10/08 15:47:17 rmello Exp $
--
--
-- The Oracle Relational Database Management System was designed
-- by a horde of gnomes whose alignment is chaotic good, and
-- implemented by a second horde who were all lawful evil.
-- Their marketing staff, on the other hand...


-- create or replace package body portal
-- as

create function portal__new (integer, integer, varchar, integer, integer, integer, integer, integer)
returns integer as '
declare
	p_portal_id		alias for $1;		-- default null
	p_template_id		alias for $2;		-- default null
	p_name			alias for $3;		-- default null
	p_package_id		alias for $4;		-- default 0
	p_target_object_id	alias for $5;		-- default 0
	p_owner_id		alias for $6;		-- default 0
	p_parent_portal_id	alias for $7;		-- default null
	p_copy_portal_id	alias for $8;		-- default null
begin
	return portal__new(p_portal_id, p_template_id, p_name,
                           p_package_id, p_target_object_id, 
			   p_owner_id, p_parent_portal_id, p_copy_portal_id,
                           ''portal'', now(), null, null, null);
end;
' language 'plpgsql';

create function portal__new (integer, integer, varchar, integer, integer, integer, 
                      integer, integer, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
	p_portal_id		alias for $1;		-- default null
	p_template_id		alias for $2;		-- default null
	p_name			alias for $3;		-- default null
	p_package_id		alias for $4;		-- default 0
	p_target_object_id	alias for $5;		-- default 0
	p_owner_id		alias for $6;		-- default 0
	p_parent_portal_id	alias for $7;		-- default null
	p_copy_portal_id	alias for $8;		-- default null
	p_object_type		alias for $9;		-- default ''portal''
	p_creation_date		alias for $10;		-- default now()
	p_creation_user		alias for $11;		-- default null
	p_creation_ip		alias for $12;		-- default null 
	p_context_id		alias for $13;		-- default null 
	v_portal_id integer;
	v_child_p boolean;
	v_target_object integer;
begin
	v_portal_id := acs_object__new (
		p_portal_id,
		p_object_type,
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);

	if p_target_object_id = 0
	then
		v_target_object := p_package_id;
	else
		v_target_object := p_target_object_id;
	end if;

	if p_parent_portal_id is not null
	then
		-- create the child portal
		-- the passed-in values take precedence.
		--
		-- package_id and owner_id default to zero, since null is a legal
		-- value for them.
		insert into portals (
			portal_id, name, template_id, package_id, target_object_id, owner_id, parent_portal_id
		)
		select
			v_portal_id,
			coalesce(p_name, p.name),
			coalesce(p_template_id, p.template_id),
			case when p_package_id = 0 then p.package_id else p_package_id end,
			case when v_target_object = 0 then p.package_id else v_target_object end,
			case when p_owner_id = 0 then p.owner_id else p_owner_id end,
			p_parent_portal_id
		from portals p
		where portal_id = p_parent_portal_id;

		-- copy the portal_element_map entries
		insert into portal_element_map
			(portal_id, element_id, region, sort_key)
		select v_portal_id, m.element_id, m.region, m.sort_key
		from portal_element_map m
		where portal_id = p_parent_portal_id;

	elsif p_copy_portal_id is not null
	then
		-- copy the portal, using the source''s parent_portal_id.
		insert into portals (
			portal_id, name, template_id, package_id, target_object_id, owner_id, parent_portal_id
		)
		select
			v_portal_id,
			coalesce(p_name, p.name),
			coalesce(p_template_id, p.template_id),
			case when p_package_id = 0 then p.package_id else p_package_id end,
			case when v_target_object = 0 then p.package_id else v_target_object end,
			case when p_owner_id = 0 then p.owner_id else p_owner_id end,
			p.parent_portal_id
		from portals p
		where portal_id = p_copy_portal_id;


		-- copy the portal_element_map entries
		insert into portal_element_map
			(portal_id, element_id, region, sort_key)
		select v_portal_id, m.element_id, m.region, m.sort_key
		from portal_element_map m
		where portal_id = p_copy_portal_id;

		-- copy the available template and available element map entries, if necessary.
		select case when p.parent_portal_id = NULL then ''f'' else ''t'' end
		into v_child_p
		from portals p
		where portal_id = v_portal_id;

		if v_child_p = ''f''
		then
			insert into portal_available_element_map
				(portal_id, element_id)
			select v_portal_id, m.element_id
			from portal_available_element_map m
			where portal_id = p_copy_portal_id;

			insert into portal_available_template_map
				(portal_id, template_id)
			select v_portal_id, m.template_id
			from portal_available_template_map m
			where portal_id = p_copy_portal_id;
		end if;

	else
		-- it''s all new.  just insert the thing.
		insert into portals (
			portal_id, template_id, name, package_id, target_object_id, owner_id, parent_portal_id
		) values (
			v_portal_id,
			p_template_id,
			p_name,
			case when p_package_id = 0 then NULL else p_package_id end,
			v_target_object,
			case when p_owner_id = 0 then NULL else p_owner_id end,
			p_parent_portal_id
		);
	end if;

	return v_portal_id;
end;
' language 'plpgsql';

create function portal__delete (integer)
returns integer as '
declare
	p_portal_id	alias for $1
begin
	perform acs_object__delete(p_portal_id);
	return 0;
end;
' language 'plpgsql';

-- determine what the appropriate portal to present is within an
-- instance of portal for a particular user.
create function portal__id_for_user (integer, integer)
returns integer as '
declare
	p_package_id	alias for $1;
	p_user_id	alias for $2;
	v_portal_id integer;
begin
	-- I have a way to do this in a single query, but for some
	-- reason, PL/SQL''s parser wouldn''t accept it.  <shrug>
	select portal_id
	into v_portal_id
	from portals
	where
	  package_id = p_package_id and
	  owner_id = p_user_id;

	if NOT FOUND then
		select portal_id
		into v_portal_id
		from portals
		where
		  package_id = p_package_id and
		  owner_id is null;
	end if;
	return v_portal_id;
end;
' language 'plpgsql';


-- find a portal's ultimate parent.
-- only iterate a few times, since circular refs are a posibility.
create function portal__parent (integer)
returns integer as '
declare
	p_portal_id	alias for $1;
	i integer;
	v_parent_portal_id integer;
	v_current_portal_id integer;
begin
	v_current_portal_id := p_portal_id;

	for i in 1..5 loop
		select p.parent_portal_id
		into v_parent_portal_id
		from portals p
		where portal_id = v_current_portal_id;

		if v_parent_portal_id is null
		then
			return v_current_portal_id;
		end if;

		v_current_portal_id := v_parent_portal_id;
	end loop;

	raise exception ''Found more than 5 parent portal references (circular ref?)'';
end;
' language 'plpgsql';

create function portal__root_p (integer)
returns boolean as '
declare
	p_portal_id	alias for $1;
	v_is_root_p	boolean;
begin
	select case when p.parent_portal_id is NULL then ''t'' else ''f'' end
	into v_is_root_p
	from portals p
	where p.portal_id = p_portal_id;

	return v_is_root_p;
end;
' language 'plpgsql';

create function portal__url (integer)
returns varchar as '
declare
	p_portal_id	alias for $1;
	v_url varchar(350);
	v_row RECORD;
begin
	select site_node__url(n.node_id) 
          into v_url
	  from site_nodes n, portals, apm_packages p
	 where n.object_id = p.package_id
	   and p.package_id = portals.package_id
	   and portals.portal_id = p_portal_id 
         limit 1;

	-- if not found just return null
	return v_url;
end;
' language 'plpgsql';


-- create or replace package body portal_template
-- as

create function portal_template__new (integer, varchar, text, varchar, text, text)
returns integer as '
declare
	p_template_id		alias for $1;	-- default null
	p_name			alias for $2;
	p_description		alias for $3;	-- default null
	p_type			alias for $4;	-- default null
	p_filename		alias for $5;
	p_resource_dir		alias for $6;
begin
	return portal_template__new(p_template_id, p_name, p_description,
	       p_type, p_filename, p_resource_dir,
               ''portal_template'', now(), null, null, null);
end;
' language 'plpgsql';

create function portal_template__new (integer, varchar, text, varchar, text, text, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
	p_template_id		alias for $1;	-- default null
	p_name			alias for $2;
	p_description		alias for $3;	-- default null
	p_type			alias for $4;	-- default null
	p_filename		alias for $5;
	p_resource_dir		alias for $6;
	p_object_type		alias for $7;	-- default ''portal_template''
	p_creation_date		alias for $8;	-- default now()
	p_creation_user		alias for $9;	-- default null
	p_creation_ip		alias for $10;	-- default null 
	p_context_id		alias for $11;	-- default null 
	v_template_id integer;
begin
	v_template_id := acs_object__new (
		p_template_id,
		p_object_type,
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);

	insert into portal_templates
		(template_id, name, description, type, filename, resource_dir)
	values
		(v_template_id, p_name, p_description, p_type, p_filename, p_resource_dir);

	return v_template_id;
end;
' language 'plpgsql';


create function portal_template__delete (integer)
returns integer as '
declare
	template_id	alias for $1;
begin
	perform acs_object__delete(template_id);
	return 0;
end;
' language 'plpgsql';


create function portal_template__add_type (integer, varchar)
returns integer as '
declare
	p_template_id	alias for $1;
	p_mime_type	alias for $2;
begin
	insert into portal_available_mime_type_map (template_id, mime_type) 
	values (p_template_id, p_mime_type);
	return 0;
end;
' language 'plpgsql';

create function portal_template__add_region (integer, varchar, boolean)
returns integer as '
declare
	p_template_id	alias for $1;
	p_region	alias for $2;
	p_immutable_p	alias for $3;  -- default ''f''
begin
	insert into portal_supported_regions (template_id, region, immutable_p)
	values (p_template_id, p_region, p_immutable_p);
	return 0;
end;
' language 'plpgsql';


-- create or replace package body portal_element_config
-- as

create function portal_element_config__new (integer, integer)
returns integer as '
declare
	p_config_id		alias for $1;	-- default null
	p_portal_id		alias for $2;	-- default null
begin
	return portal_element_config__new(p_config_id, p_portal_id,
               ''portal_element_config'', now(), null, null, null);
end;
' language 'plpgsql';

create function portal_element_config__new (integer, integer, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
	p_config_id		alias for $1;	-- default null
	p_portal_id		alias for $2;	-- default null
	p_object_type		alias for $3;	-- default ''portal_element_config''
	p_creation_date		alias for $4;	-- default now()
	p_creation_user		alias for $5;	-- default null
	p_creation_ip		alias for $6;	-- default null 
	p_context_id		alias for $7;	-- default null 
	v_config_id integer;
begin
	v_config_id := acs_object__new (
		p_config_id,
		p_object_type,
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);

	insert into portal_element_configs
		(config_id, portal_id)
	values
		(v_config_id, p_portal_id);

	return v_config_id;
end;
' language 'plpgsql';

create function portal_element_config__delete (integer)
returns integer as '
declare
	config_id	alias for $1;
begin
	perform acs_object__delete(config_id);
	return 0;
end;
' language 'plpgsql';

create function portal_element_config__assign (integer, integer, integer, integer, integer)
returns integer as '
declare
	p_config_id	  alias for $1;	-- default null
	p_to_element	  alias for $2;	-- default null
	p_from_element	  alias for $3;	-- default null
	p_to_datasource	  alias for $4;	-- default null
	p_from_datasource alias for $5;	-- default null
	v_config_id integer;
begin
	if p_config_id is not null
	then
		v_config_id := p_config_id;
	elsif p_from_element is not null
	then
		select config_id
		into v_config_id
		from portal_elements
		where element_id = p_from_element;
	elsif p_from_datasource is not null
	then
		select config_id
		into v_config_id
		from portal_datasources
		where datasource_id = p_from_datasource;
	else
		v_config_id := null;
	end if;

	if p_to_element is not null
	then
		update portal_elements 
		set config_id = v_config_id 
		where element_id = p_to_element;
	end if;

	if p_to_datasource is not null
	then
		update portal_datasources
		set default_config_id = v_config_id
		where datasource_id = p_to_datasource;
	end if;
	return 0;
end;
' language 'plpgsql';

-- datasources
-- create or replace package body portal_datasource
-- as

create function portal_datasource__new (integer, varchar, varchar, integer, varchar, text, boolean, text, varchar)
returns integer as '
declare
	p_datasource_id		alias for $1;	-- default null
	p_data_type		alias for $2;	-- default null
	p_mime_type		alias for $3;	-- default null
	p_default_config_id	alias for $4;	-- default null
	p_name			alias for $5;	-- default null
	p_description		alias for $6;	-- default null
	p_configurable_p	alias for $7;	-- default null
	p_content		alias for $8;	-- default null
	p_package_key		alias for $9;	-- default null
begin
	return portal_datasource__new(p_datasource_id, p_data_type,
	       p_mime_type, p_default_config_id, p_name, p_description,
	       p_configurable_p, p_content, p_package_key,
               ''portal_datasource'', now(), null, null, null);
end;
' language 'plpgsql';


create function portal_datasource__new (integer, varchar, varchar, integer, varchar, text, boolean, text, varchar, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
	p_datasource_id		alias for $1;	-- default null
	p_data_type		alias for $2;	-- default null
	p_mime_type		alias for $3;	-- default null
	p_default_config_id	alias for $4;	-- default null
	p_name			alias for $5;	-- default null
	p_description		alias for $6;	-- default null
	p_configurable_p	alias for $7;	-- default null
	p_content		alias for $8;	-- default null
	p_package_key		alias for $9;	-- default null
	p_object_type		alias for $10;	-- default ''portal_datasource''
	p_creation_date		alias for $11;	-- default now()
	p_creation_user		alias for $12;	-- default null
	p_creation_ip		alias for $13;	-- default null 
	p_context_id		alias for $14;	-- default null 
	v_datasource_id integer;
begin
	v_datasource_id := acs_object__new (
		p_datasource_id,
		p_object_type,
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);

	insert into portal_datasources
		(datasource_id, data_type,
		mime_type, default_config_id,
		name, description, configurable_p, 
		content, package_key)
	values
		(v_datasource_id, p_data_type,
		p_mime_type, p_default_config_id,
		p_name, p_description, p_configurable_p, 
		p_content, p_package_key); 

	return v_datasource_id;
end;
' language 'plpgsql';


create function portal_datasource__delete (integer)
returns integer as '
declare
	datasource_id	alias for $1;
begin
	perform acs_object__delete(datasource_id);
	return 0;
end;
' language 'plpgsql';


-- create or replace package body portal_element
-- as

create function portal_element__new (integer, varchar, text, boolean, integer, integer, integer, integer, integer)
returns integer as '
declare
	p_element_id		alias for $1;	-- default null
	p_name			alias for $2;
	p_description		alias for $3;	-- default null
	p_exportable_p		alias for $4;	-- default null
	p_datasource_id		alias for $5;
	p_controlling_portal_id	alias for $6;
	p_template_id		alias for $7;	-- default null
	p_config_id		alias for $8;	-- default null
	p_default_config_id	alias for $9;	-- default null
begin
	return portal_element__new(p_element_id, p_name, p_description,
	       p_exportable_p, p_datasource_id, p_controlling_portal_id,
	       p_template_id, p_config_id, p_default_config_id,
               ''portal_element'', now(), null, null, null);
end;
' language 'plpgsql';


create function portal_element__new (integer, varchar, text, boolean, integer, integer, integer, integer, integer, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
	p_element_id		alias for $1;	-- default null
	p_name			alias for $2;
	p_description		alias for $3;	-- default null
	p_exportable_p		alias for $4;	-- default null
	p_datasource_id		alias for $5;
	p_controlling_portal_id	alias for $6;
	p_template_id		alias for $7;	-- default null
	p_config_id		alias for $8;	-- default null
	p_default_config_id	alias for $9;	-- default null
	p_object_type		alias for $10;	-- default ''portal_element''
	p_creation_date		alias for $11;	-- default now()
	p_creation_user		alias for $12;	-- default null
	p_creation_ip		alias for $13;	-- default null 
	p_context_id		alias for $14;	-- default null 
	v_element_id integer;
	v_config_id integer;
begin
	v_element_id := acs_object__new (
		p_element_id,
		p_object_type,
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);

	-- use the default configuration if none was supplied.
	-- PORTING TODO: is it a bug that we never use p_default_config_id?

	if p_config_id is null
	then
		select default_config_id
		into v_config_id
		from portal_datasources
		where datasource_id = p_datasource_id;
	else
		v_config_id := p_config_id;
	end if;

	insert into portal_elements
		(element_id, name, description, datasource_id,
		template_id, config_id, default_config_id, exportable_p,
		controlling_portal_id)
	values
		(v_element_id, p_name, p_description, p_datasource_id,
		p_template_id, v_config_id, p_default_config_id, p_exportable_p,
		p_controlling_portal_id);

	return v_element_id;
end;
' language 'plpgsql';

create function portal_element__delete (integer)
returns integer as '
declare
	element_id	alias for $1;
begin
	perform acs_object__delete(element_id);
	return 0;
end;
' language 'plpgsql';

create function portal_element__make_available (integer, integer)
returns integer as '
declare
	p_element_id 	alias for $1;
	p_portal_id	alias for $2;
	v_exists_p boolean;
begin
	select ''t'' into v_exists_p
	  from portal_available_element_map m
	 where m.element_id = p_element_id 
           and m.portal_id = p_portal_id;

	if NOT FOUND then
          insert into portal_available_element_map
            (element_id, portal_id)
          values
            (p_element_id, p_portal_id);
	end if;
	
	return 0;
end;
' language 'plpgsql';


-- this swaps an element in one position with the adjacent element, either 
-- up or down.  This is so strange because the sort_key column is constrained to
-- be unique, the keys are not guaranteed to be contiguous, and a single element can
-- occur more than once in a region.

create function portal_element__move (integer, integer, varchar, varchar)
returns integer as '
declare
	p_portal_id	alias for $1;
	p_sort_key	alias for $2;
	p_region	alias for $3;
	p_direction	alias for $4;
	v_other_key	integer;
	v_element_id	integer;
begin
	if direction = ''up''
	then
		-- get the previous sort key.
		select sort_key into v_other_key
 		  from portal_element_map
		 where portal_id = p_portal_id 
                   and region = p_region 
                   and sort_key < p_sort_key 
                 order by sort_key desc
		 limit 1;

	elsif direction = ''down''
	then
		-- get the next sort key.
		select sort_key into v_other_key
		  from portal_element_map
		 where portal_id = p_portal_id 
		   and region = p_region 
                   and sort_key > p_sort_key 
                 order by sort_key
                 limit 1;
	else
		raise exception ''Invalid direction passed to portal_element__move'';
	end if;

	-- get the element_id in that position.
	select element_id into v_element_id
	  from portal_element_map
	 where portal_id = p_portal_id 
           and region = p_region 
           and sort_key = p_sort_key;

	-- remove the one we know everything about.
	delete from portal_element_map
	 where portal_id = p_portal_id 
           and region = p_region
	   and sort_key = p_sort_key;

	-- move the other one into its position.
	update portal_element_map
	   set sort_key = p_sort_key
	 where portal_id = p_portal_id 
	   and region = p_region 
	   and sort_key = v_other_key;

	-- reinsert the original entry.
	insert into portal_element_map (
	  portal_id, element_id, region, sort_key
	) values (
	  p_portal_id, v_element_id, p_region, v_other_key
	);

	return 0;
end;
' language 'plpgsql';