-- sql/data-model-create.sql
--
-- The data model for the ACS portal package.
--
-- Ian Baker <ibaker@arsdigita.com>
-- $Id: data-model-create.sql,v 1.1 2002/07/09 17:35:10 rmello Exp $
--

-- Note:  When naming constraints, I'm abbreviating the portals_
--        prefix as simply p_.  Also, where the names are too
--        long, I'll first follow the "removing vowels" strategy,
--        interspersed with other (hopefully) well-known
--        abbreviations like 'attribute' -> 'attr'

-- TODO:
-- add some indices.
-- especially, index all foreign key columns.
-- add "comment on table" / "comment on column" style comments.
-- add javadoc style comments in place of the above, etc.
-- check the API to make sure that nulls aren't permitted there and not here.


-- These tables define data- and content-types from datasources.  These will
-- not change often. If you're concerned about performance, BTW, don't
-- worry. We don't join against this table; these values get cached at
-- startup.  The table is handy, however, for producing select lists, and if
-- we ever want to produce small packages that extend portals to work with
-- other data- and content-types.
create table portal_mime_types (
	name		varchar(200)
			constraint p_mime_types_name_pk
			primary key,
	pretty_name	varchar(200),
	sort_key	integer
);

-- Usually, no joins are done against this table.  See above.
create table portal_data_types (
	name		varchar(200)
			constraint p_data_types_name_pk
			primary key,
	pretty_name	varchar(200) not null,
	secure_p	boolean default 'f',
	sort_key	integer
);

-- this table stores both the portal layout and element theme
-- templates.
create table portal_templates (
	template_id		integer
                                constraint p_templates_template_id_fk
				references acs_objects(object_id)
				constraint p_templates_template_id_pk
				primary key,
	name			varchar(200)
				constraint p_templates_name_un
				unique
				not null,
	description		text,
	-- These will likely never change, and so can be hard-coded
	-- like this.  If at some point this assumption turns out
	-- to be false, the scheme used for data- and content-types
	-- could be substituted without much pain.
	type			varchar(20)
				constraint p_templates_type_ck
				check(type in ('layout', 'theme')),
	filename		text not null,
	resource_dir		text
);

-- this should probably instead be a mapping table between
-- templates and regions, but that just seems like overkill.
create table portal_supported_regions (
	template_id		integer 
                                constraint p_supported_regions_tid_fk
				references portal_templates
				on delete cascade
				not null,
	region			varchar(20),
	immutable_p		boolean,
	constraint p_spprtd_rgns_tmpl_id_rgn_pk primary key (template_id,region)
);

-- define which types are available for each template.
create table portal_available_mime_type_map (
	template_id	integer 
                        constraint p_avail_mime_type_map_tid_fk
			references portal_templates(template_id)
			on delete cascade,
	mime_type	varchar(200)
                        constraint p_avail_mime_type_map_typ_fk
			references portal_mime_types(name)
			on delete cascade,
	constraint p_avail_mime_type_map_fk
	primary key(template_id, mime_type)
);

-- Which layouts are available to a particular portal?
-- These values will be inherited from a portal's parent, if it has one.
create table portal_available_template_map (
	template_id	integer 
                        constraint p_avail_template_map_tid_fk
			references portal_templates(template_id)
			on delete cascade,
	portal_id	integer
                        constraint p_avail_template_map_pid_fk
			references acs_objects(object_id)
			on delete cascade,
	constraint p_avail_template_map_pk
	primary key(template_id, portal_id)
);


-- the actual portal data goes here.
-- portals are acs_objects, with all the rights and privileges thereof.
create table portals (
	portal_id	 	integer
                                constraint p_portal_id_fk
				references acs_objects(object_id)
				constraint p_portal_id_pk
				primary key,
	name			varchar(200) default 'Untitled' not null,
        -- the layout template.
	template_id		integer
                                constraint p_template_id_fk
				references portal_templates
				not null,
	package_id		integer
                                constraint p_package_id_fk
				references apm_packages(package_id)
				on delete cascade,
	-- The object that this portal is assigned to.  For example,
	-- the Intranet package might assign a portal to an office,
	-- which is an office object.  The target object ID and type
	-- are available to elements.
	--
	-- In normal use (when portal is not being used by another package),
	-- this will be the same as the package_id.
	target_object_id	integer
                                constraint p_target_object_id_fk
				references acs_objects(object_id)
				on delete cascade,
	-- there's a special case here.  I hate to do it, but realistically,
	-- nobody owns a portal except for a person (parties create their own package
	-- instances).  I didn't like this about the old portals data model, but it
	-- does make sense.  If this is null then it's the "default portal" and is
        -- owned by nobody.  If this comment confuses you, email me. -ib
	owner_id		integer
                                constraint p_owner_id_fk
				references persons(person_id)
				on delete cascade,
	parent_portal_id 	integer
                                constraint p_parent_portal_id_fk
				references acs_objects(object_id)
				on delete cascade,
	-- it's only possible to own one personal version of any portal.  This constraint may
        -- someday be relaxed if it turns out that there's a need to.
	constraint p_owner_package_un unique(package_id,owner_id)
);

-- INDEX owner_id
-- INDEX package_id


-- This is something of a strange relation.  Essentially, it's a
-- denormalization of the data model that still preserves referential
-- integrity.  Instead of relating configuration parameters and elements
-- using a mapping table between element_id and parameter_id, a config_id
-- is created in both, referencing this table.  It's a little messier, but
-- it's also not ever necessary to join against this table.  The motivation
-- here is performance.
--
-- Also, some considerations:  it's possible to support
-- default element configurations without a separate mapping table,
-- and groups of parameters can be made into acs_objects, instead of
-- individual parameters (which would suck - there's lots of them).
create table portal_element_configs (
	config_id	integer
                        constraint p_element_configs_cfg_id_fk
			references acs_objects(object_id)
			constraint p_element_configs_cfg_id_pk
			primary key,
	-- this is here mostly for the 'on delete cascade'.
	portal_id	integer
                        constraint p_element_configs_ptl_id_fk
			references acs_objects(object_id)
			on delete cascade
);


-- maintain configuration information for elements.
create table portal_element_parameters (
	parameter_id	integer
			constraint p_element_prms_prm_id_pk
			primary key,
	-- see the comment on the previous table about the existence
	-- of this row.  'on delete cascade' is set so that only
	-- a single operation is necessary to delete unused configurations.
	-- (but note that the configuration does in fact need to be unused
	--  for the delete to succeed).
	config_id	integer
                        constraint p_element_prmss_cnfg_id_fk
			references portal_element_configs on delete cascade,
	key		varchar(50),
	value		text
);


-- here, config_id stores the configuration that should be used
-- TODO: Should we be using the content repository?

create table portal_datasources (
	datasource_id		integer
                                constraint p_datasources_datasource_id_fk
				references acs_objects(object_id)
				constraint p_datasources_datasource_id_pk
				primary key,
	data_type		varchar(200)
                                constraint p_datasources_data_type_fk
				references portal_data_types(name)
				not null,
	mime_type		varchar(200)
                                constraint p_datasources_mimetype_fk
				references portal_mime_types(name)
				not null,
	default_config_id	integer
                                constraint p_datasources_dflt_cfg_id_fk
				references portal_element_configs(config_id),
	configurable_p		boolean default 'f',
	name			varchar(200),
	description		text,
	content			text,
	package_key		varchar(100)
                                constraint p_datasources_package_key_fk
				references apm_package_types(package_key) on delete cascade,
	constraint p_name_package_key_un unique(package_key,name)
);


create table portal_elements (
	element_id		integer
                                constraint p_elements_element_id_fk
				references acs_objects(object_id)
				constraint p_elements_element_id_pk
				primary key,
	name			varchar(200) not null,
	description		text,
	exportable_p		boolean default 'f',
	-- The presence of both this and the portal_element_map table might
	-- seem redundant.  Actually, though, it's not.  This relationship
	-- describes which instance owns the element (and is many-to-one).  The
	-- map table describes which elements actually appear in which portals.
	-- (and is many-to-many).  A third map table described which elements actually
	-- exist in which portals.  An element can exist without being in any
	-- portals...  it's just available.
	controlling_portal_id	integer
                                constraint p_elements_portal_id_fk
				references portals(portal_id)
				not null,
	datasource_id		integer
                                constraint p_elements_datasource_id_fk
				references portal_datasources
				on delete cascade
				not null,
	-- the theme for this element.
	template_id		integer
                                constraint p_elements_template_id_fk
				references portal_templates,
	-- default_config_id gives the user a place to which to revert if
	-- they decide to scrap their configuration.  When config_id is
	-- null, default_config_id is used instead.
	-- Note that the config_id column is likely to refer to a number of
	-- parameters.  Textbook SQL says that this should be done with
	-- another table, mapping parameter_ids to element_ids.  In
	-- practice, it's slower that way and provides few real world
	-- advantages.  In lieu of the Right Way(tm), we have this comment,
	-- which uses relatively few server resources.  :)
	config_id		integer
                                constraint p_elements_config_id_fk
				references portal_element_configs on delete set null,
	default_config_id	integer
                                constraint p_elements_default_config_fk
				references portal_element_configs(config_id)
);


-- attach elements to portals.
create table portal_element_map (
	portal_id	integer
                        constraint p_element_map_portal_id_fk
			references portals on delete cascade
			not null,
	element_id	integer
                        constraint p_element_map_element_id_fk
			references portal_elements on delete cascade
			not null,
	region varchar(20) not null,
	sort_key integer not null,
	-- Two elements may not exist in the same place on a portal.
	-- It would probably work, but it would just be stupid.
	constraint p_element_map_pid_rgn_srt_un unique(portal_id,region,sort_key)
);

-- The presence of both this and the portal_element_map table might
-- seem redundant.  Actually, though, it's not.  This relationship
-- describes which instance owns the element (and is many-to-one).  The
-- map table describes which elements actually appear in which portals.
-- (and is many-to-many).  An element can exist without being in any
-- portals...  it's just available.

-- make elements available to portals.
-- this is inherited from the parent portal, if available.
create table portal_available_element_map (
	portal_id	integer
                        constraint p_avail_elmnt_map_ptl_id_fk
			references acs_objects(object_id)
			on delete cascade,
	element_id	integer
                        constraint p_avail_elmnt_map_elmnt_id_fk
			references portal_elements
			on delete cascade,
	constraint p_avail_element_map_pk primary key (portal_id,element_id)
);

-- A handy view for rendering elements.
create view portal_elem_tmpl
as
select
	e.element_id,
	e.name,
	e.datasource_id,
	e.template_id,
	e.description,
	e.exportable_p,
        coalesce(e.config_id, e.default_config_id) as config_id,
	t.filename
from portal_elements e, portal_templates t
where
	e.template_id = t.template_id;