Index: openacs-4/packages/new-portal/sql/oracle/portal-core-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/new-portal/sql/oracle/portal-core-create.sql,v
diff -u -r1.42 -r1.43
--- openacs-4/packages/new-portal/sql/oracle/portal-core-create.sql	25 Jun 2002 21:47:15 -0000	1.42
+++ openacs-4/packages/new-portal/sql/oracle/portal-core-create.sql	1 Jul 2002 20:03:49 -0000	1.43
@@ -14,239 +14,247 @@
 --  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 ****
 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)
+    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)
+                                    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		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                   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)
+                                    constraint p_ds_def_prms_key_nn
+                                    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)
+    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
+                                    constraint p_layouts_name_nn
+                                    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)
+    layout_id                       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_region_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 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)
+    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
+                                    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	 	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_id                       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                        constraint portal_theme_id_fk
+                                    references portal_element_themes (theme_id)
+                                    portal_theme_id_nn
+                                    not null,
+    template_id                     constraint template_id_fk
+                                    references portals (portal_id)
 );
 
--- **** (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)
+    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'
+                                    constraint portal_pages_pretty_name_nn
+                                    not null,
+    portal_id                       constraint portal_pages_portal_id_fk
+                                    references portals (portal_id)
+                                    constraint portal_pages_portal_id_nn
+                                    not null,
+    layout_id                       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,
+     -- Two pages on one portal canot have the same sort key
+    constraint portal_pages_sort_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. 
+-- 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)
+    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                         constraint p_element_map_page_id_fk
+                                    references portal_pages (page_id)
+                                    on delete cascade
+                                    not null,
+    datasource_id                   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		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                      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_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
@@ -255,21 +263,19 @@
 -- 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)
+    portal_datasource_id            integer
+                                    constraint portal_datasource_avail_map_pk
+                                    primary key,
+    portal_id                       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                   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)
 );
-
-
