Diff for "Pluto/EmbeddingDerby"

-- DDL for Derby persistence of portlet preferences.

-- build-container.sql
-- Builds database structures affiliated with portlet container
-- We should also have a portal schema for portal data

set schema container;

drop schema container;
create schema container;

set schema container;

--drop in reverse order to avoid constraint issues
drop view portlet_app_view;
drop table preference_value;
drop table preference;  
drop table portlet;
drop table portlet_app;

create table portlet_app (
        portlet_app_id integer not null generated always as identity 
        (start with 1, increment by 1),
        app_context varchar(250) not null,
        mod_date timestamp default current_timestamp,
        constraint portlet_app_pk primary key (portlet_app_id),
        constraint app_context_unique unique (app_context)      
        );

create table portlet (
        portlet_id integer not null generated always as identity 
        (start with 1, increment by 1),
        portlet_app_id integer not null, 
        portlet_name varchar(75) not null,
        mod_date timestamp default current_timestamp,
        constraint portlet_pk primary key (portlet_id),
        constraint portlet_fk foreign key (portlet_app_id) 
        references portlet_app(portlet_app_id)
        );

create table preference (
        preference_id integer not null generated always as identity 
        (start with 1, increment by 1),
        portlet_id integer not null, 
        preference_name varchar(75) not null,
        description varchar(250), --used in resource bundle (see PLT.14.3.1)
        read_only char(1) default 'N',
        auth_user varchar(75),
        mod_date timestamp default current_timestamp,
        constraint preference_pk primary key (preference_id),
        constraint preference_fk foreign key (portlet_id) 
                references portlet(portlet_id)
        );

drop index preference_auth_user_ndx;
create index preference_auth_user_ndx on preference(auth_user);

create table preference_value (
        preference_id integer not null,
        preference_value varchar(250),
        mod_date timestamp default current_timestamp,
        constraint preference_value_pk primary key 
                (preference_id, preference_value),      
        constraint preference_value_fk foreign key (preference_id) 
                references preference(preference_id)
        );

--View that holds all the portlet app data.
--Use this with a where clause to get data for a 
--particular portlet app or portlet.
 create view portlet_app_view 
        (portlet_app_id, app_context,
                portlet_id, portlet_name,
                preference_name, preference_value, 
                read_only) as
        select
                pa.portlet_app_id,      
                pa.app_context,         
                p.portlet_id, 
                p.portlet_name, 
                pr.preference_name, 
                pv.preference_value,
                pr.read_only
        from preference pr, preference_value pv, 
        portlet p, portlet_app pa
        where pr.preference_id=pv.preference_id
        and pr.portlet_id=p.portlet_id
        and p.portlet_app_id=pa.portlet_app_id;