#** 
createdb.vm: Velocity template that generates vendor-specific database scripts 

DON'T RUN THIS, IT'S NOT A DATABASE CREATION SCRIPT!!!
**#

-- Run this script to create the Roller Planet tables in your database.

-- *****************************************************
-- Create the tables and indices

create table rag_properties (
    name     varchar(255) not null primary key,
    value    $db.TEXT_SQL_TYPE
);


create table rag_planet (
    id              varchar(48) not null primary key,
    handle          varchar(32) not null,
    title           varchar(255) not null,
    description     varchar(255)
);
alter table rag_planet add constraint ragp_handle_uq unique ( handle );


create table rag_group (
    id               varchar(48) not null primary key,
    planet_id        varchar(48) not null,
    handle           varchar(32) not null,
    title            varchar(255) not null,
    description      varchar(255),
    max_page_entries integer default 30,
    max_feed_entries integer default 30,
    cat_restriction  $db.TEXT_SQL_TYPE,
    group_page       varchar(255)
);
alter table rag_group add constraint ragg_handle_uq unique ( planet_id, handle );


create table rag_subscription (
    id               varchar(48) not null primary key,
    title            varchar(255) not null,
    feed_url         varchar(255) not null,
    site_url         varchar(255),
    author           varchar(255),
    last_updated     $db.TIMESTAMP_SQL_TYPE,
    inbound_links    integer default -1,
    inbound_blogs    integer default -1
);
alter table rag_subscription add constraint rags_feed_url_uq unique ( feed_url$!db.INDEXSIZE_LARGE );


create table rag_group_subscription (
    group_id         varchar(48) not null,
    subscription_id  varchar(48) not null
);
create index raggs_gid_idx on rag_group_subscription(group_id); 
create index raggs_sid_idx on rag_group_subscription(subscription_id); 


create table rag_entry (
    id               varchar(48) not null primary key,
    subscription_id  varchar(48) not null,
    handle           varchar(255),
    title            varchar(255),
    guid             varchar(255),
    permalink        $db.TEXT_SQL_TYPE not null,
    author           varchar(255),
    content          $db.TEXT_SQL_TYPE,
    categories       $db.TEXT_SQL_TYPE,
    published        $db.TIMESTAMP_SQL_TYPE not null,
    updated          $db.TIMESTAMP_SQL_TYPE    
);
create index rage_sid_idx on rag_entry(subscription_id); 


-- *****************************************************
-- Now add the foreign key relationships