
-----------------------------------------------------------------------------
-- MEDIA_TYPE
-----------------------------------------------------------------------------

CREATE TABLE MEDIA_TYPE
(
    MEDIATYPE_ID INTEGER NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    CHARACTER_SET VARCHAR(40),
    TITLE VARCHAR(80),
    DESCRIPTION LONG VARCHAR
);

ALTER TABLE MEDIA_TYPE
    ADD PRIMARY KEY (MEDIATYPE_ID) ;



-----------------------------------------------------------------------------
-- CLIENT
-----------------------------------------------------------------------------

CREATE TABLE CLIENT
(
    CLIENT_ID INTEGER NOT NULL,
    EVAL_ORDER INTEGER NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    USER_AGENT_PATTERN VARCHAR(128),
    MANUFACTURER VARCHAR(80),
    MODEL VARCHAR(80),
    VERSION VARCHAR(40),
    PREFERRED_MIMETYPE_ID INTEGER NOT NULL
);

ALTER TABLE CLIENT
    ADD PRIMARY KEY (CLIENT_ID) ;



-----------------------------------------------------------------------------
-- MIMETYPE
-----------------------------------------------------------------------------

CREATE TABLE MIMETYPE
(
    MIMETYPE_ID INTEGER NOT NULL,
    NAME VARCHAR(80) NOT NULL
);

ALTER TABLE MIMETYPE
    ADD PRIMARY KEY (MIMETYPE_ID) ;



-----------------------------------------------------------------------------
-- CAPABILITY
-----------------------------------------------------------------------------

CREATE TABLE CAPABILITY
(
    CAPABILITY_ID INTEGER NOT NULL,
    CAPABILITY VARCHAR(80) NOT NULL
);

ALTER TABLE CAPABILITY
    ADD PRIMARY KEY (CAPABILITY_ID) ;



-----------------------------------------------------------------------------
-- CLIENT_TO_CAPABILITY
-----------------------------------------------------------------------------

CREATE TABLE CLIENT_TO_CAPABILITY
(
    CLIENT_ID INTEGER NOT NULL,
    CAPABILITY_ID INTEGER NOT NULL
);




-----------------------------------------------------------------------------
-- CLIENT_TO_MIMETYPE
-----------------------------------------------------------------------------

CREATE TABLE CLIENT_TO_MIMETYPE
(
    CLIENT_ID INTEGER NOT NULL,
    MIMETYPE_ID INTEGER NOT NULL
);




-----------------------------------------------------------------------------
-- MEDIATYPE_TO_CAPABILITY
-----------------------------------------------------------------------------

CREATE TABLE MEDIATYPE_TO_CAPABILITY
(
    MEDIATYPE_ID INTEGER NOT NULL,
    CAPABILITY_ID INTEGER NOT NULL
);




-----------------------------------------------------------------------------
-- MEDIATYPE_TO_MIMETYPE
-----------------------------------------------------------------------------

CREATE TABLE MEDIATYPE_TO_MIMETYPE
(
    MEDIATYPE_ID INTEGER NOT NULL,
    MIMETYPE_ID INTEGER NOT NULL
);




-----------------------------------------------------------------------------
-- PORTLET_STATISTICS
-----------------------------------------------------------------------------

CREATE TABLE PORTLET_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    PAGE VARCHAR(80),
    PORTLET VARCHAR(255),
    STATUS INTEGER,
    ELAPSED_TIME FIXED(38,0)
);




-----------------------------------------------------------------------------
-- PAGE_STATISTICS
-----------------------------------------------------------------------------

CREATE TABLE PAGE_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    PAGE VARCHAR(80),
    STATUS INTEGER,
    ELAPSED_TIME FIXED(38,0)
);




-----------------------------------------------------------------------------
-- USER_STATISTICS
-----------------------------------------------------------------------------

CREATE TABLE USER_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    STATUS INTEGER,
    ELAPSED_TIME FIXED(38,0)
);




-----------------------------------------------------------------------------
-- ADMIN_ACTIVITY
-----------------------------------------------------------------------------

CREATE TABLE ADMIN_ACTIVITY
(
    ACTIVITY VARCHAR(40),
    CATEGORY VARCHAR(40),
    ADMIN VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    IPADDRESS VARCHAR(80),
    ATTR_NAME VARCHAR(40),
    ATTR_VALUE_BEFORE VARCHAR(80),
    ATTR_VALUE_AFTER VARCHAR(80),
    DESCRIPTION VARCHAR(128)
);




-----------------------------------------------------------------------------
-- USER_ACTIVITY
-----------------------------------------------------------------------------

CREATE TABLE USER_ACTIVITY
(
    ACTIVITY VARCHAR(40),
    CATEGORY VARCHAR(40),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    IPADDRESS VARCHAR(80),
    ATTR_NAME VARCHAR(40),
    ATTR_VALUE_BEFORE VARCHAR(80),
    ATTR_VALUE_AFTER VARCHAR(80),
    DESCRIPTION VARCHAR(128)
);
















