Comments on Articles

• Comments need to refer to the article they are about and the user who posted it:

create table comments_on_articles_raw (
        comment_id              integer primary key,
        -- on what article is this a comment?
        refers_to               not null references articles,
        creation_user           not null references users,
        creation_date           not null date,
        language                char(2) references language_codes,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

create view comments_on_articles_approved 
        as
        select * 
        from comments_on_articles_raw
        where editorial_status = 'approved';

• Hmmm, that looks just like article. Lets merge them.

create table content_raw (
        content_id              integer primary key,
        -- if not NULL, this row represents a comment
        refers_to               references content_raw,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        -- what language is this in?
        -- visit http://www.w3.org/International/O-charset-lang
        -- to see the allowable 2-character codes (en is English, ja is Japanese)
        language                char(2) references language_codes,
        -- could be text/html or text/plain or some sort of XML document
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        -- the entire article; 4 GB limit
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

-- if we want to be able to write some scripts without having to think
-- about the fact that different content types are merged

create view articles_approved 
        as
        select * 
        from content_raw
        where refers_to is null
        and editorial_status = 'approved';

create view comments_on_articles_approved 
        as
        select * 
        from content_raw
        where refers_to is not null
        and editorial_status = 'approved';

-- let's build a single full-text index on both articles and comments
-- using Oracle Intermedia Text (formerly known as "Context")

create index content_ctx on content_raw (body) indextype is ctxsys.context;