--
--  Copyright (C) 2001, 2002 OpenForce, Inc.
--
--  This file is part of dotLRN.
--
--  dotLRN is free software; you can redistribute it and/or modify it under the
--  terms of the GNU General Public License as published by the Free Software
--  Foundation; either version 2 of the License, or (at your option) any later
--  version.
--
--  dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
--  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
--  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
--  details.
--

-- The New Portal Package
-- copyright 2001, OpenForce, Inc.
-- distributed under the GNU GPL v2
--
-- arjun@openforce.net
-- $Id: portal-core-create.sql,v 1.42 2002/06/25 21:47:15 arjun Exp $

-- **** PRIVILEGES ****
begin
    -- multi portal admin privs
    acs_privilege.create_privilege('portal_create_portal');
    acs_privilege.create_privilege('portal_delete_portal');

    -- privs on a single portal
    acs_privilege.create_privilege('portal_read_portal');

    acs_privilege.create_privilege('portal_edit_portal');

    acs_privilege.create_privilege('portal_admin_portal');

    -- set up the portal perms hierarchy
    -- and root privs to global priv names
    acs_privilege.add_child('read','portal_read_portal');
    acs_privilege.add_child('portal_edit_portal','portal_read_portal');
    acs_privilege.add_child('portal_admin_portal','portal_edit_portal');
    acs_privilege.add_child('create','portal_create_portal');
    acs_privilege.add_child('delete','portal_delete_portal');
    acs_privilege.add_child('admin','portal_admin_portal');



end;
/
show errors

-- **** DATASOURCES ****

create table portal_datasources (
	datasource_id		constraint p_datasources_datasource_id_fk
				references acs_objects(object_id)
				constraint p_datasources_datasource_id_pk
				primary key,
	description		varchar(200),
	name			varchar(200) not null,
	pretty_name		varchar(200)
);


-- A default configuration for a ds will be stored here, to be copied
-- to the portal_element_parameters table at PE creation (DS binding) time
-- 
-- Config semantics:
-- true: cfg_req, cfg_p - A static config is given for all PEs, can
--	 be changed later
-- true: cfg_req false: cfg_p - PE must be configured before use
-- false: cfg_req true: cfg_p - An optional default cfg given
-- both false: Configuration optional w. no default suggested
create table portal_datasource_def_params (
	parameter_id		integer
				constraint p_ds_def_prms_prm_id_pk
				primary key,
	datasource_id		constraint p_ds_def_prms_element_id_fk
				references portal_datasources on delete cascade
				not null,
	config_required_p	char(1) default 'f'
				constraint p_ds_def_prms_cfg_req_p_ck
				check(config_required_p in ('t', 'f')),
	configured_p		char(1) default 'f'
				constraint p_ds_def_prms_configured_p_ck
				check(configured_p in ('t', 'f')),
	key			varchar(200) not null,
	value			varchar(200)
);


-- **** Portal Layouts ****

-- Layouts are ADP templates for the portal page. i.e. 2 cols, 3 cols,
-- etc. They are globally available. No secret layouts!
create table portal_layouts (
	layout_id		constraint p_layouts_layout_id_fk
				references acs_objects(object_id)
				constraint p_layouts_layout_id_pk
				primary key,
	name			varchar(200)
				constraint p_layouts_name_un
				unique
				not null,
	description		varchar(200),
	filename		varchar(200),
	resource_dir		varchar(200)
);

create table portal_supported_regions (
	  layout_id		constraint p_spprtd_rgns_layout_id_fk
				references portal_layouts
				on delete cascade
				not null,
	  region		varchar(20) not null,
	  immutable_p		char(1) not null
				constraint p_spprtd_rgns_immtble_p_ck
				check(immutable_p in ('t', 'f')),
	  constraint		p_spprtd_rgns_tmpl_id_rgn_pk 
				primary key (layout_id,region)
);


-- **** Portal Element Themes ****

-- Themes are ADP templates with decoration for PEs, nothing more.
-- At this point they will just be bits of ADPs  in the filesystem
create table portal_element_themes (
	theme_id		constraint p_e_themes_theme_id_fk
				references acs_objects(object_id)
				constraint p_e_themes_theme_id_pk
				primary key,
	name			varchar(200)
				constraint p_e_themes_name_un
				unique
				not null,
	description		varchar(200),
	filename		varchar(200),
	resource_dir		varchar(200)
);


-- **** Portals ****

-- Portals are essentially "containers" for PEs that bind to DSs.
-- Parties have, optionally have portals 
-- Restrict to party check? 
-- Roles and perms issues? 
create table portals (
	portal_id	 	constraint portal_portal_id_fk
				references acs_objects(object_id)
				constraint p_portal_id_pk
				primary key,
	name			varchar(200) default 'Untitled' not null,
	theme_id		constraint portal_theme_id_fk
				references portal_element_themes
				not null,
	-- Not null, if I have a portal I'm using as a template
	template_id		constraint template_id_fk
				references portals
);

-- **** (Portal) Pages ****
-- Support for multi-page portals (think my.yahoo.com)
create table portal_pages (
	page_id                 constraint portal_pages_page_id_fk
				references acs_objects(object_id)
				constraint portal_pages_page_id_pk
				primary key,
	pretty_name		varchar(200) default 'Untitled Page' not null,
	portal_id		constraint portal_pages_portal_id_fk
				references portals
                                not null,
	layout_id		constraint portal_pages_layout_id_fk
				references portal_layouts
				not null,
	sort_key		integer	not null,
 	-- Two pages on one portal canot have the same sort key
	constraint portal_pages_srt_key_un 
	unique(portal_id,sort_key)
);

create index portal_pages_prtl_page_idx on portal_pages (portal_id, page_id);

-- **** Portal Elements (PEs) ****


-- PE are fully owned by one and only one portal. They are not
-- "objects" that live on after their portal is gone. One way to think
-- of them is a map b/w a portal and a DS, with satellite data of a
-- theme, a config, a region, etc. 
--
-- No securtiy checks are done here. If you can view and bind to a DS you have
-- a PE for it.

-- this sequence is only used for swapping sort keys so that we
-- don't abuse the acs_object_id_seq

create sequence portal_element_map_sk_seq start with 1000;


create table portal_element_map (
	element_id		integer
				constraint p_element_map_element_id_pk
				primary key,
	name			varchar(200) not null,
	pretty_name		varchar(200) not null,
	page_id                 constraint p_element_map_page_id_fk
				references portal_pages
                                on delete cascade
				not null,
	datasource_id		constraint p_element_map_datasource_id_fk
				references portal_datasources
				on delete cascade
				not null,
	region			varchar(20) not null,
	sort_key		integer	not null,
	state			varchar(6) default 'full'
				constraint p_element_map_state
				check(state in ('full', 'shaded', 'hidden', 
					        'pinned')),
 	-- Two elements may not exist in the same place on a portal.
        -- constraint p_element_map_pid_rgn_srt_un 
        -- FIXME: we want a constraint _like_ this, but it makes 
        -- reverting very complicated. XXX
        --	unique(page_id,region,sort_key),
 	-- Two elements may not have the same pretty name on a portal.
	constraint p_element_map_pid_name_un 
	unique(page_id,pretty_name)
);

create table portal_element_parameters (
	parameter_id		integer
				constraint p_element_prms_prm_id_pk
				primary key,
	element_id		constraint p_element_prms_element_id_fk
				references portal_element_map on delete cascade
				not null,
	config_required_p	char(1) default 'f'
				constraint p_element_prms_cfg_req_p_ck
				check(config_required_p in ('t', 'f')),
	configured_p		char(1) default 'f'
				constraint p_element_prms_configured_p_ck
				check(configured_p in ('t', 'f')),
	key			varchar(50) not null,
	value			varchar(200) 
);

create index p_element_prms_elem_id_idx on portal_element_parameters (element_id);
create index p_element_prms_elem_key_idx on portal_element_parameters (element_id, key);

-- This table maps the datasources that are available for portals to
-- bind to (i.e. creating a PE). This table is required since some DSs
-- will not make sense for every portal. A "current time" DS will make
-- sense for every portal, but a bboard DS may not, and we don't want
-- to confuse everyone with DSs that don't make sense for the given
-- portal

create table portal_datasource_avail_map (
	portal_datasource_id	integer
				constraint p_ds_a_map_p_ds_id_pk
				primary key,
	portal_id		constraint p_ds_a_map_portal_id_fk
				references portals on delete cascade
				not null,
	datasource_id		constraint p_ds_a_map_datasource_id_fk
				references portal_datasources
				on delete cascade
				not null,
 	-- DSs are unique per-portal
	constraint p_ds_a_map_pid_ds_un 
	unique(portal_id,datasource_id)
);