CREATE TABLE OJB_SEQ (
    CLASSNAME    VARCHAR(175) NOT NULL,
    FIELDNAME    VARCHAR(70) NOT NULL,
    LAST_NUM     INT,
    PRIMARY KEY (CLASSNAME, FIELDNAME)
);

-- create the HIGH/LOW SequenceManager table
CREATE TABLE OJB_HL_SEQ (
    CLASSNAME    VARCHAR(175) NOT NULL,
    FIELDNAME    VARCHAR(70) NOT NULL,
    MAX_KEY      INT,
    GRAB_SIZE    INT,
    PRIMARY KEY (CLASSNAME, FIELDNAME)
);

create table PORTLET
(
    id integer primary key,
    name varchar(128) not null,
    hidden smallint default 0,
    classname varchar(256),
    type varchar(32) not null,
    application smallint default 0,
    parent integer,
    foreign key (parent) references PORTLET(id) on delete cascade,
    -- embedded : content URL
    url varchar(256),
    cachedOnURL smallint default 1,  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    -- parameters 
    -- media types
    -- categories 
    constraint uk_portlet_name unique(name)
);

create table CONTROL
(
    id integer primary key,
    name varchar(128) not null,
    hidden smallint default 0,
    classname varchar(256),                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    -- parameters 
    -- media types
    constraint uk_control_name unique(name)
);

create table CONTROLLER
(
    id integer primary key,
    name varchar(128) not null,
    hidden smallint default 0,
    classname varchar(256),                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    -- parameters 
    -- media types
    constraint uk_controller_name unique(name)
);

create table MEDIATYPE
(
    id integer primary key,
    name varchar(128) not null,
    hidden smallint default 0,
    mimeType varchar(128) not null,                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    constraint uk_mediatype_name unique(name)
);

create table SKIN
(
    id integer primary key,
    name varchar(128) not null,
    hidden smallint default 0,
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    -- parameters 
    constraint uk_skin_name unique(name)
);

create table PORTLET_PARAMETER
(
    id integer primary key,
    name varchar(128) not null,
    value varchar(256),
    type varchar(64),
    hidden smallint default 0,                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    cachedOnName smallint default 1,
    cachedOnValue smallint default 1,
    portlet_id integer,
    foreign key (portlet_id) references PORTLET(id) on delete cascade
);

create table CONTROL_PARAMETER
(
    id integer primary key,
    name varchar(128) not null,
    value varchar(256),
    type varchar(64),
    hidden smallint default 0,                                 
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    control_id integer,
    foreign key (control_id) references CONTROL(id) on delete cascade
);

create table CONTROLLER_PARAMETER
(
    id integer primary key,
    name varchar(128) not null,
    value varchar(256),
    type varchar(64),
    hidden smallint default 0,                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    controller_id integer,
    foreign key (controller_id) references CONTROLLER(id) on delete cascade
);

create table SKIN_PARAMETER
(
    id integer primary key,
    name varchar(128) not null,
    value varchar(256),
    type varchar(64),
    hidden smallint default 0,                                  
    -- embedded : security
    role varchar(99),
    -- embedded : metaInfo
    title varchar(128),
    description varchar(256),
    image varchar(128),
    skin_id integer,
    foreign key (skin_id) references SKIN(id) on delete cascade
);

create table CATEGORY
(
    id integer primary key,
    name varchar(128) not null,
    groupe varchar(99) not null,
    owner integer,
    foreign key (owner) references PORTLET(id) on delete cascade
);

CREATE INDEX IX_CATEGORY ON CATEGORY(groupe, name);

create table PORTLET_MEDIATYPE
(
    id integer not null,
    media_id integer not null
);

CREATE INDEX IX_PORTLET_MEDIATYPE ON PORTLET_MEDIATYPE(id,media_id);

create table CONTROL_MEDIATYPE
(
    control_id integer not null,
    media_id integer not null
);

CREATE INDEX IX_CONTROL_MEDIATYPE ON CONTROL_MEDIATYPE(control_id, media_id);

create table CONTROLLER_MEDIATYPE
(
    controller_id integer not null,
    media_id integer not null
);

CREATE INDEX IX_CONTROLLER_MEDIATYPE ON CONTROLLER_MEDIATYPE(controller_id, media_id);

