Pluto EmbeddingDerby

-- 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;