-- 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.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'
-- to do:
-- 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 char(1) default 'f'
constraint p_data_types_secure_p_ck
check(secure_p in ('t', 'f')),
sort_key integer
);
-- this table stores both the portal layout and element theme
-- templates.
create table portal_templates (
template_id 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 varchar(4000),
-- 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 varchar(4000) not null,
resource_dir varchar(4000)
);
-- 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 constraint p_supported_regions_tid_fk
references portal_templates
on delete cascade
not null,
region varchar(20),
immutable_p char(1)
constraint p_supported_rgns_immtble_p_ck
check(immutable_p in ('t', 'f')),
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 constraint p_avail_mime_type_map_tid_fk
references portal_templates(template_id)
on delete cascade,
mime_type 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 constraint p_avail_template_map_tid_fk
references portal_templates(template_id)
on delete cascade,
portal_id 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 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 constraint p_template_id_fk
references portal_templates
not null,
package_id 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 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 constraint p_owner_id_fk
references persons(person_id)
on delete cascade,
parent_portal_id 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 referrential
-- 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 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 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 constraint p_element_prmss_cnfg_id_fk
references portal_element_configs on delete cascade,
key varchar(50),
value varchar(4000)
);
-- here, config_id stores the configuration that should be used
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,
data_type constraint p_datasources_data_type_fk
references portal_data_types(name)
not null,
mime_type constraint p_datasources_mimetype_fk
references portal_mime_types(name)
not null,
default_config_id constraint p_datasources_dflt_cfg_id_fk
references portal_element_configs(config_id),
configurable_p char(1) default 'f'
constraint p_elements_configurable_p_ck
check(configurable_p in ('t', 'f')),
name varchar(200),
description varchar(4000),
content clob,
package_key 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 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 varchar(4000),
exportable_p char(1) default 'f'
constraint p_elements_exportable_p_ck
check(exportable_p in ('t', '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 constraint p_elements_portal_id_fk
references portals(portal_id)
not null,
datasource_id constraint p_elements_datasource_id_fk
references portal_datasources
on delete cascade
not null,
-- the theme for this element.
template_id 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 constraint p_elements_config_id_fk
references portal_element_configs on delete set null,
default_config_id constraint p_elements_default_config_fk
references portal_element_configs(config_id)
);
-- attach elements to portals.
create table portal_element_map (
portal_id constraint p_element_map_portal_id_fk
references portals on delete cascade
not null,
element_id 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 constraint p_avail_elmnt_map_ptl_id_fk
references acs_objects(object_id)
on delete cascade,
element_id 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 or replace view portal_elem_tmpl
as
select
e.element_id,
e.name,
e.datasource_id,
e.template_id,
e.description,
e.exportable_p,
nvl(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;