--
-- Copyright (C) 2001, 2002 MIT
--
-- 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.
--
--
-- create portals model
--
-- @author arjun@openforce.net
-- @author yon@openforce.net
-- @creation-date 2001-10-01
-- @version $Id: portal-core-create.sql,v 1.5 2004/06/06 02:29:10 donb Exp $
--
create sequence portal_seq;
create table portal_datasources (
datasource_id integer
constraint portal_datasources_pk
primary key,
name varchar(200)
constraint p_datasources_name_nn
not null,
pretty_name varchar(200)
constraint p_datasources_pretty_name_nn
not null,
application varchar(100)
constraint p_datasources_application_fk
references apm_package_types (package_key)
on delete cascade,
owner varchar(100)
constraint p_datasources_owner_fk
references apm_package_types (package_key)
on delete cascade
constraint p_datasources_owner_nn
not null,
template varchar(200)
constraint p_datasources_template_nn
not null,
admin_p char(1)
default 't'
constraint p_datasources_admin_p_nn
not null
constraint p_datasources_admin_p_ck
check (admin_p in ('t', 'f')),
shadeable_p char(1)
default 't'
constraint p_datasources_shadeable_p_nn
not null
constraint p_datasources_shadeable_p_ck
check (shadeable_p in ('t', 'f')),
hideable_p char(1)
default 't'
constraint p_datasources_hideable_p_nn
not null
constraint p_datasources_hideable_p_ck
check (hideable_p in ('t', 'f')),
description varchar(200)
);
-- indexes for referential integrity checking
create index p_datasources_application_idx on portal_datasources(application);
create index p_datasources_owner_idx on portal_datasources(owner);
comment on table portal_datasources is '
a portal datasource is the package of code that generates the content
of a portal element. the foo-portlet packages create datasources.
';
comment on column portal_datasources.name is '
The name of this portal datasource. We can''t arbitrarily use portlet_key because many
portlet packages will support at least two portlets, one user portlet and one admin
portlet.
';
comment on column portal_datasources.pretty_name is '
The default pretty name to use for an instance of this portlet. This should be a
message key for internationalized portlets.
';
comment on column portal_datasources.application is '
The package key of the application that this portlet works with. For instance the
forums portlet works with the forums package.
';
comment on column portal_datasources.owner is '
The package key of the package that implements this portlet.
';
comment on column portal_datasources.template is '
The name of template that displays the portlet content. Note this is not a full
path, portlet templates go in the standard package template library directory.
';
comment on column portal_datasources.admin_p is '
If true, this is a package admin datasource that should only be made available
to admins. This only determines how the portal package code chooses to handle
a portal datasource when it creates portal pages and creates portlets. An
admin portlet is responsible for ensuring that a user actually has the proper
privileges to admin its related application. And applications that create their
own portal page structure (.LRN, in particular) may or may not honor this flag.
';
comment on column portal_datasources.shadeable_p is '
If true, this datasource implements the shade operator. DRB: This is only
being kept around for .LRN, shading is something the portal page manager
should have absolute control over.
';
comment on column portal_datasources.hideable_p is '
If true, this datasource implements the hide operator. DRB: This is only
being kept around for .LRN, shading is something the portal page manager
should have absolute control over.
';
create table portal_datasource_parameters (
datasource_id constraint p_ds_params_datasource_id_fk
references portal_datasources (datasource_id)
on delete cascade
constraint p_ds_params_datasource_id_nn
not null,
key varchar(200)
constraint p_ds_params_key_nn
not null,
value varchar(200),
config_required_p char(1)
default 'f'
constraint p_ds_params_cfg_req_p_nn
not null
constraint p_ds_params_cfg_req_p_ck
check (config_required_p in ('t', 'f')),
configured_p char(1)
default 'f'
constraint p_ds_params_cfg_p_nn
not null
constraint p_ds_params_cfg_p_ck
check (configured_p in ('t', 'f')),
constraint portal_datasource_params_pk
primary key (datasource_id, key)
);
comment on table portal_datasource_parameters is '
maintains a set of default parameters for portal datsources. these
are copied to a portal element when it is created.
configuration semantics:
config_required_p | configured_p |
----------------- | ----------------- | ----------------------------------
t | t | static configuration is provided
| | for the portal elements. it can be
| | changed later.
----------------- | ----------------- | ----------------------------------
t | f | the portal element must be
| | configured when created.
----------------- | ----------------- | ----------------------------------
f | t | an optional default configuration
| | is provided.
----------------- | ----------------- | ----------------------------------
f | f | configuration is optional and no
| | default is provided.
';
comment on column portal_datasource_parameters.config_required_p is '
this parameter requires configuration. if configured_p is true then no
action is needed at portal element creation since the portal element
can simply use the default value provided. if configured_p is false then
the portal element must provide a value for this parameter at creation
time.
';
comment on column portal_datasource_parameters.configured_p is '
if true then a default value is provided for this parameter.
';
create table portal_layouts (
layout_id constraint p_layouts_layout_id_fk
references acs_objects (object_id)
constraint portal_layouts_pk
primary key,
name varchar(200)
constraint p_layouts_name_un
unique
constraint p_layouts_name_nn
not null,
description varchar(200),
filename varchar(200)
);
comment on table portal_layouts is '
a portal layout is a template that defines regions for portal
elements to "fill in". Examples of layouts are two column and
three column
';
comment on column portal_layouts.filename is '
the relative path to the layout template from the "portal/www/"
directory without the ending ".adp" extension. Example: "layouts/simple2"
';
comment on column portal_layouts.resource_dir is '
XXX remove me
';
create table portal_layout_regions (
layout_id constraint p_layout_regions_layout_id_fk
references portal_layouts (layout_id)
on delete cascade
constraint p_layout_regions_layout_id_nn
not null,
region varchar(20)
constraint p_layout_regions_region_nn
not null,
constraint portal_layout_regions_pk
primary key (layout_id, region)
);
comment on table portal_layout_regions is '
this table describes each of the regions in each of the layouts.
for example a two column layout would have two entries in this
table: region = 1 and region = 2.
';
comment on column portal_layout_regions.region is '
an ordering of regions in a layout starting from 1
';
create table portal_themes (
theme_id constraint p_themes_theme_id_fk
references acs_objects (object_id)
constraint portal_themes_pk
primary key,
name varchar(200)
constraint p_themes_name_un
unique
constraint p_themes_name_nn
not null,
description varchar(200),
filename varchar(200),
resource_dir varchar(200)
);
comment on table portal_themes is '
decoration templates for portal elements
';
comment on column portal_themes.filename is '
the relative path to the theme template from the "portal/www/"
directory without the ending ".adp" extension. Example: "themes/deco-theme"
';
comment on column portal_themes.resource_dir is '
the relative path to the theme''s resource directory from the "portal/www/"
directory. The resource directory typically contains graphics files.
Example: "themes/deco-theme"
';
create table portals (
portal_id constraint portal_portal_id_fk
references acs_objects (object_id)
constraint portals_pk
primary key,
owner_id integer
constraint p_owner_id_fk
references acs_objects (object_id),
package_id integer
constraint p_package_id_fk
references apm_packages(package_id)
constraint p_package_id_nn
not null,
name varchar(200)
default 'Untitled'
constraint portal_name_nn
not null,
template_id constraint template_id_fk
references portals (portal_id),
theme_id constraint portal_theme_id_fk
references portal_themes (theme_id)
constraint portal_theme_id_nn
not null
);
create index portals_owner_id_idx on portals(owner_id);
create index portals_package_id_idx on portals(package_id);
comment on table portals is '
portals are containers of one or more portal pages mapped to a party.
';
comment on column portals.template_id is '
a portal may have a template (another portal)
';
comment on column portals.theme_id is '
portals one theme for all its contained pages
';
create table portal_datasource_map (
package_id integer
constraint pdm_package_id_fk
references apm_packages (package_id)
constraint pdm_package_id_nn
not null,
datasource_id integer
constraint pdm_datasource_id_fk
references portal_datasources (datasource_id)
constraint pdm_datasource_id_nn
not null,
constraint pdm_pk
primary key (package_id, datasource_id)
);
create index pdm_datasource_idx on portal_datasource_map (datasource_id);
comment on table portal_datasource_map is '
Maps datasources to portal instances. This is used by the portal admin UI to track
which datasources have had their applications mounted under the instance''s mount
point. Independent applications like .LRN that use the portal package as a service
do not need to maintain this map.
';
comment on column portal_datasource_map.package_id is '
The package id of the portal instance the datasource is mapped to.
';
comment on column portal_datasource_map.datasource_id is '
The package id of the portal instance the datasource is mapped to.
';
create table portal_pages (
page_id constraint p_pages_page_id_fk
references acs_objects (object_id)
constraint portal_pages_pk
primary key,
name varchar(200)
default 'Untitled Page'
constraint p_pages_name_nn
not null,
portal_id constraint p_pages_portal_id_fk
references portals (portal_id)
constraint p_pages_portal_id_nn
not null,
layout_id constraint p_pages_layout_id_fk
references portal_layouts (layout_id)
constraint p_pages_layout_id_nn
not null,
sort_key integer
constraint p_pages_sort_key_nn
not null,
constraint p_pages_portal_id_sort_key_un
unique (portal_id, sort_key)
);
create index p_pages_prtl_page_idx on portal_pages (portal_id, page_id);
comment on table portal_pages is '
portal pages are containers for portal elements. can be thought of
as the "tabs" on a portal
';
comment on column portal_pages.sort_key is '
an ordering of the pages contained in the same portal starting from
0 for the first page and increasing in an gapless integer sequence
';
create table portal_elements (
element_id integer
constraint portal_elements_pk
primary key,
datasource_id constraint p_elements_datasource_id_fk
references portal_datasources (datasource_id)
on delete cascade
constraint p_elements_datasource_id_nn
not null,
name varchar(200)
constraint p_elements_name_nn
not null,
page_id constraint p_elements_page_id_fk
references portal_pages (page_id)
on delete cascade
not null,
region varchar(20)
constraint p_elements_region_nn
not null,
sort_key integer
constraint p_elements_sort_key_nn
not null,
state varchar(6)
default 'full'
constraint p_elements_state_ck
check (state in ('full', 'shaded', 'hidden', 'pinned')),
shadeable_p char(1)
default 't'
constraint p_elements_shadeable_p_nn
not null
constraint p_elements_shadeable_p_ck
check (shadeable_p in ('t', 'f')),
hideable_p char(1)
default 't'
constraint p_elements_hideable_p_nn
not null
constraint p_elements_hideable_p_ck
check (hideable_p in ('t', 'f')),
constraint p_elements_page_id_name_un
unique (page_id, name)
);
comment on table portal_elements is '
the user-visible "box" on a portal page that displays the content of a datasource
';
comment on column portal_elements.sort_key is '
an ordering of elements contained in the same region on a page starting from
0 for the first element and increasing in an gapless integer sequence
';
comment on column portal_elements.state is '
one of the set "full" (normal), "shaded" (title bar only), "hidden" (not shown),
or "pinned" (like full, but without state change links). portal themes impliment these
different behaviors based on a given elements state
';
create table portal_element_parameters (
parameter_id integer
constraint portal_element_parameters_pk
primary key,
element_id constraint p_element_params_element_id_fk
references portal_elements (element_id)
on delete cascade
constraint p_element_params_element_id_nn
not null,
key varchar(50)
constraint p_element_params_key_nn
not null,
value varchar(200),
config_required_p char(1)
default 'f'
constraint p_element_params_cfg_req_p_nn
not null
constraint p_element_params_cfg_req_p_ck
check (config_required_p in ('t', 'f')),
configured_p char(1)
default 'f'
constraint p_element_params_cfg_p_nn
not null
constraint p_element_params_cfg_p_ck
check(configured_p in ('t', 'f'))
);
create index p_element_params_elem_key_idx on portal_element_parameters (element_id, key);
comment on table portal_element_parameters is '
parameters on a per-element basis. the data structure is that of a multiset (aka bag)
where multiple entries with the same key (for the same element) are allowed ?? XXXX.
';
comment on column portal_element_parameters.config_required_p is '
';
comment on column portal_element_parameters.configured_p is '
';