Raven RSS Reader Schema Definition

CREATE SCHEMA RAVEN AUTHORIZATION RAVEN

    CREATE SEQUENCE SITES
    CREATE SEQUENCE GROUPS
    CREATE SEQUENCE FEEDS
    CREATE SEQUENCE ARTICLES

    CREATE TABLE SITES (
        SITE_ID INTEGER DEFAULT NEXT_SEQUENCE(SITES) NOT NULL PRIMARY KEY,
        SITE_NAME VARCHAR(100) NOT NULL UNIQUE,
        PORT INTEGER,
        STYLE VARCHAR(100),
        SUMMARY_SIZE INTEGER,
        ARTICLE_LIVE_DAYS INTEGER,
        ARTICLE_PURGE_DAYS INTEGER
    )

    CREATE TABLE GROUPS (
        GROUP_ID INTEGER DEFAULT NEXT_SEQUENCE(GROUPS) NOT NULL PRIMARY KEY,
        GROUP_NAME VARCHAR(100) NOT NULL,
        SITE_ID INTEGER NOT NULL REFERENCES SITES ON DELETE CASCADE
    )

    CREATE TABLE FEEDS (
        FEED_ID INTEGER DEFAULT NEXT_SEQUENCE(FEEDS) NOT NULL PRIMARY KEY,
        FEED_NAME VARCHAR(100) NOT NULL,
        FEED_URL VARCHAR(200),
        FEED_SUMMARY_SIZE INTEGER,
        FEED_MODIFIED TIMESTAMP(0),
        FEED_ETAG VARCHAR(100),
        GROUP_ID INTEGER NOT NULL REFERENCES GROUPS ON DELETE CASCADE
    )

    CREATE TABLE ARTICLES (
        ARTICLE_ID INTEGER DEFAULT NEXT_SEQUENCE(ARTICLES) NOT NULL PRIMARY KEY,
        ARTICLE_LINK VARCHAR(200) NOT NULL,
        ARTICLE_TITLE VARCHAR(200),
        ARTICLE_DESCRIPTION CHARACTER LARGE OBJECT(1M),
        ARTICLE_DATE TIMESTAMP(0),
        ARTICLE_STATUS CHAR(1),
        FEED_ID INTEGER NOT NULL REFERENCES FEEDS ON DELETE CASCADE
    )
;

COMMIT;