Index: openacs-4/packages/new-portal/sql/postgresql/portal-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/new-portal/sql/postgresql/portal-core-create.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/new-portal/sql/postgresql/portal-core-create.sql 24 May 2002 01:58:46 -0000 1.2 +++ openacs-4/packages/new-portal/sql/postgresql/portal-core-create.sql 1 Jul 2002 20:03:25 -0000 1.3 @@ -1,269 +1,293 @@ +-- +-- 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 foreign key more +-- details. +-- + +-- -- The New Portal Package --- copyright 2001, OpenForce, Inc. --- distributed under the GNU GPL v2 -- --- arjun@openforce.net --- $Id$ +-- @author arjun@openforce.net +-- @version $Id$ +-- --- **** PRIVILEGES **** +select acs_privilege__create_privilege('portal_create_portal'); +select acs_privilege__create_privilege('portal_delete_portal'); +select acs_privilege__create_privilege('portal_read_portal'); +select acs_privilege__create_privilege('portal_edit_portal'); +select acs_privilege__create_privilege('portal_admin_portal'); +select acs_privilege__add_child('read','portal_read_portal'); +select acs_privilege__add_child('portal_edit_portal','portal_read_portal'); +select acs_privilege__add_child('portal_admin_portal','portal_edit_portal'); +select acs_privilege__add_child('create','portal_create_portal'); +select acs_privilege__add_child('delete','portal_delete_portal'); +select acs_privilege__add_child('admin','portal_admin_portal'); - -- multi portal admin privs - select acs_privilege__create_privilege('portal_create_portal'); - select acs_privilege__create_privilege('portal_delete_portal'); - - -- privs on a single portal - select acs_privilege__create_privilege('portal_read_portal'); - - select acs_privilege__create_privilege('portal_edit_portal'); - - select acs_privilege__create_privilege('portal_admin_portal'); - - -- set up the portal perms hierarchy - -- and root privs to global priv names - select acs_privilege__add_child('read','portal_read_portal'); - select acs_privilege__add_child('portal_edit_portal','portal_read_portal'); - select acs_privilege__add_child('portal_admin_portal','portal_edit_portal'); - select acs_privilege__add_child('create','portal_create_portal'); - select acs_privilege__add_child('delete','portal_delete_portal'); - select acs_privilege__add_child('admin','portal_admin_portal'); - - - --- **** DATASOURCES **** - 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, - description varchar(200), - name varchar(200) not null, - pretty_name varchar(200) + datasource_id integer + 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) + constraint p_datasources_name_nn + 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 +-- 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 integer - 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) + parameter_id integer + constraint p_ds_def_prms_prm_id_pk + primary key, + datasource_id integer + constraint p_ds_def_prms_element_id_fk + references portal_datasources (datasource_id) + on delete cascade + constraint p_ds_def_prms_element_id_nn + not null, + config_required_p char(1) + default 'f' + constraint p_ds_def_prms_cfg_req_p_nn + not null + 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_nn + not null + 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 the template for the portal page. i.e. 2 cols, 3 cols, -- etc. They are globally available. No secret layouts! create table portal_layouts ( - layout_id integer - 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) + layout_id integer + 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 + constraint p_layouts_name_nn + not null, + description varchar(200), + filename varchar(200), + resource_dir varchar(200) ); create table portal_supported_regions ( - layout_id integer - 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) + layout_id integer + constraint p_spprtd_rgns_layout_id_fk + references portal_layouts (layout_id) + on delete cascade + constraint p_spprtd_rgns_layout_id_nn + not null, + region varchar(20) + constraint p_spprtd_rgns_immtble_p_nn + not null, + immutable_p char(1) + constraint p_spprtd_rgns_immtble_p_nn + not null + constraint p_spprtd_rgns_immtble_p_ck + check (immutable_p in ('t', 'f')), + constraint portal_supported_regions_pk + primary key (layout_id, region) ); - --- **** Portal Element Themes **** - -- Themes are 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 integer - 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) + theme_id integer + 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 + constraint p_e_themes_name_nn + 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? +-- Parties have, optionally have portals +-- Restrict to party check? +-- Roles and perms issues? create table portals ( - portal_id integer - 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 integer - constraint portal_theme_id_fk - references portal_element_themes - not null, - -- AKS this is out of date!! is this portal a portal template? - portal_template_p char(1) default 'f' - constraint p_portal_template_p_ck - check(portal_template_p in ('f', 't')), - -- Not null, if I have a template - template_id integer - constraint portal_template_id_fk - references portals + portal_id integer + constraint portal_portal_id_fk + references acs_objects (object_id) + constraint p_portal_id_pk + primary key, + name varchar(200) + default 'Untitled' + constraint portal_name_nn + not null, + theme_id integer + constraint portal_theme_id_fk + references portal_element_themes (theme_id) + constraint portal_theme_id_nn + not null, + template_id integer + constraint portal_template_id_fk + references portals (portal_id) ); --- **** (Portal) Pages **** -- Support for multi-page portals (think my.yahoo.com) create table portal_pages ( - page_id integer - 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 integer - constraint portal_pages_portal_id_fk - references portals - not null, - layout_id integer - 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) + page_id integer + 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' + constraint portal_pages_pretty_name_nn + not null, + portal_id integer + constraint portal_pages_portal_id_fk + references portals (portal_id) + constraint portal_pages_portal_id_nn + not null, + layout_id integer + constraint portal_pages_layout_id_fk + references portal_layouts (layout_id) + constraint portal_pages_layout_id_nn + not null, + sort_key integer + constraint portal_pages_sort_key_nn + not null, + constraint portal_pages_srt_key_un + unique (portal_id, sort_key) ); --- **** Portal Elements (PEs) **** +create index portal_pages_prtl_page_idx on portal_pages (portal_id, page_id); - -- 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. +-- 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; - 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 integer - constraint p_element_map_page_id_fk - references portal_pages - on delete cascade - not null, - datasource_id integer - 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 - 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) + element_id integer + constraint portal_element_map_pk + primary key, + name varchar(200) + constraint p_element_map_name_nn + not null, + pretty_name varchar(200) + constraint p_element_map_pretty_name_nn + not null, + page_id integer + constraint p_element_map_page_id_fk + references portal_pages + on delete cascade + constraint p_element_map_page_id_nn + not null, + datasource_id integer + constraint p_element_map_datasource_id_fk + references portal_datasources (datasource_id) + on delete cascade + constraint p_element_map_datasource_id_nn + not null, + region varchar(20) + constraint p_element_map_region_nn + not null, + sort_key integer + constraint p_element_map_sort_key_nn + not null, + state varchar(6) + default 'full' + constraint p_element_map_state_ck + check (state in ('full', 'shaded', 'hidden', 'pinned')), + 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 integer - 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) + parameter_id integer + constraint portal_element_parameters_pk + primary key, + element_id integer + constraint p_element_prms_element_id_fk + references portal_element_map (element_id) + on delete cascade + constraint p_element_prms_element_id_nn + not null, + config_required_p char(1) + default 'f' + constraint p_element_prms_cfg_req_p_nn + not null + 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_nn + not null + constraint p_element_prms_configured_p_ck + check (configured_p in ('t', 'f')), + key varchar(50) + constraint p_element_prms_key_nn + not null, + value varchar(200) ); +create index p_element_prms_element_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 integer - constraint p_ds_a_map_portal_id_fk - references portals on delete cascade - not null, - datasource_id integer - 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) + portal_datasource_id integer + constraint portal_datasource_avail_map_pk + primary key, + portal_id integer + constraint p_ds_a_map_portal_id_fk + references portals (portal_id) + on delete cascade + constraint p_ds_a_map_portal_id_nn + not null, + datasource_id integer + constraint p_ds_a_map_datasource_id_fk + references portal_datasources (datasource_id) + on delete cascade + constraint p_ds_a_map_datasource_id_nn + not null, + constraint p_ds_a_map_pid_ds_un unique (portal_id, datasource_id) ); - -