#** 
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 database tables in your database.

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

create table roller_user (
    id              varchar(48) not null primary key,
    username        varchar(255) not null,
    passphrase      varchar(255) not null,
    openid_url      varchar(255),
    screenname      varchar(255) not null,
    fullname        varchar(255) not null,
    emailaddress    varchar(255) not null,
    activationcode	varchar(48),
    datecreated     $db.TIMESTAMP_SQL_TYPE not null,
    locale          varchar(20),  
    timezone        varchar(50),    
    isenabled       $db.BOOLEAN_SQL_TYPE_TRUE not null
);
alter table roller_user add constraint ru_username_uq unique ( username$!db.INDEXSIZE );

create table userrole (
    id               varchar(48) not null primary key,
    rolename         varchar(255) not null,
    username         varchar(255) not null
);
create index ur_username_idx on userrole( username$!db.INDEXSIZE );

-- actions: comma separated list of actions permitted by permission
-- objectid: for now this will always store weblogid
-- objectType: for now this will always be 'Weblog'
create table roller_permission (
   id              varchar(48) not null primary key,
   username        varchar(255) not null,
   actions         varchar(255), 
   objectid        varchar(48),           
   objecttype      varchar(255), 
   pending         $db.BOOLEAN_SQL_TYPE_TRUE,         
   datecreated     $db.TIMESTAMP_SQL_TYPE not null
);

-- Audit log records time and comment about change
-- user_id: user that made change
-- object_id: id of associated object, if any
-- object_class: name of associated object class (e.g. WeblogEntryData)
-- comment: description of change
-- change_time: time that change was made
create table roller_audit_log (
    id              varchar(48) not null primary key,
    user_id         varchar(48) not null,  
    object_id       varchar(48),           
    object_class    varchar(255),          
    comment_text    varchar(255) not null, 
    change_time     $db.TIMESTAMP_SQL_TYPE              
);

create table weblog (
    id                varchar(48) not null primary key,
    name              varchar(255) not null,
    handle            varchar(255) not null,
    tagline           varchar(255),
    creator           varchar(255),
    enablebloggerapi  $db.BOOLEAN_SQL_TYPE_FALSE not null,
    editorpage        varchar(255),
    bloggercatid      varchar(48),
    allowcomments     $db.BOOLEAN_SQL_TYPE_TRUE not null,
    emailcomments     $db.BOOLEAN_SQL_TYPE_FALSE not null,
    emailaddress      varchar(255) not null,
    editortheme       varchar(255),
    locale            varchar(20),
    timezone          varchar(50),
    defaultplugins    varchar(255),
    visible           $db.BOOLEAN_SQL_TYPE_TRUE not null,
    isactive          $db.BOOLEAN_SQL_TYPE_TRUE not null,
    datecreated          $db.TIMESTAMP_SQL_TYPE not null,
    blacklist            $db.TEXT_SQL_TYPE,
    defaultallowcomments $db.BOOLEAN_SQL_TYPE_TRUE not null,
    defaultcommentdays   integer default 7 not null,
    commentmod           $db.BOOLEAN_SQL_TYPE_FALSE not null,
    displaycnt           integer default 15 not null,
    lastmodified         $db.TIMESTAMP_SQL_TYPE,
    enablemultilang   $db.BOOLEAN_SQL_TYPE_FALSE not null,
    showalllangs      $db.BOOLEAN_SQL_TYPE_TRUE not null,
    about             varchar(255),
    icon              varchar(255),
    analyticscode      $db.TEXT_SQL_TYPE
);
create index ws_visible_idx on weblog(visible);
alter table weblog add constraint ws_handle_uq unique (handle$!db.INDEXSIZE);

create table weblog_custom_template (
    id              varchar(48)  not null primary key,
    name            varchar(255)  not null,
    description     varchar(255),
    link            varchar(255),
    websiteid       varchar(48) not null,
    updatetime      $db.TIMESTAMP_SQL_TYPE not null,
    hidden          $db.BOOLEAN_SQL_TYPE_FALSE not null,
    navbar          $db.BOOLEAN_SQL_TYPE_FALSE not null,
    outputtype      varchar(48) default null,
    #columnNotNullWithDefault('action' 'varchar(16)' 'custom')
);
create index wp_name_idx on weblog_custom_template(name$!db.INDEXSIZE);
create index wp_link_idx on weblog_custom_template(link$!db.INDEXSIZE);
create index wp_id_idx on weblog_custom_template(websiteid);

create table custom_template_rendition (
    id           varchar(48)  not null primary key,
    templateid   varchar(48) not null,
    template     $db.TEXT_SQL_TYPE not null,
    templatelang varchar(48),
    #columnNotNullWithDefault('type' 'varchar(16)' 'STANDARD')
);

create table bookmark_folder (
    id               varchar(48) not null primary key,
    name             varchar(255) not null,
    websiteid        varchar(48) not null
);
create index fo_weblogid_idx on bookmark_folder( websiteid );

create table bookmark (
    id               varchar(48) not null primary key,
    folderid         varchar(48) not null,
    name             varchar(255) not null,
    description      varchar(255),
    url              varchar(255) not null,
    priority         integer default 100 not null,
    image            varchar(255),
    feedurl          varchar(255)
);
create index bm_folderid_idx on bookmark( folderid );

create table weblogcategory (
    id               varchar(48) not null primary key,
    name             varchar(255) not null,
    description      varchar(255),
    websiteid        varchar(48) not null,
    image            varchar(255),
    position         integer default 0 not null
);
create index wc_weblogid_idx on weblogcategory( websiteid );

create table weblogentry (
    id              varchar(48)  not null primary key,
    anchor          varchar(255)  not null,
    creator         varchar(255)  not null,
    title           varchar(255)  not null,
    text            $db.TEXT_SQL_TYPE not null,
    pubtime         $db.TIMESTAMP_SQL_TYPE_NULL,
    updatetime      $db.TIMESTAMP_SQL_TYPE     not null,
    websiteid       varchar(48)  not null,
    categoryid      varchar(48)  not null,
    publishentry    $db.BOOLEAN_SQL_TYPE_TRUE not null,
    link            varchar(255),
    plugins         varchar(255),
    allowcomments   $db.BOOLEAN_SQL_TYPE_FALSE not null, 
    commentdays     integer default 7 not null,
    rightToLeft     $db.BOOLEAN_SQL_TYPE_FALSE not null,
    pinnedtomain    $db.BOOLEAN_SQL_TYPE_FALSE not null,
    locale          varchar(20),
    status          varchar(20) not null,
    summary         $db.TEXT_SQL_TYPE default null, 
    content_type    varchar(48) default null, 
    content_src     varchar(255) default null,
    search_description varchar(255) default null
);
create index we_weblogid_idx on weblogentry( websiteid );
create index we_categoryid_idx on weblogentry( categoryid );
create index we_pinnedtom_idx on weblogentry(pinnedtomain);
create index we_creator_idx on weblogentry(creator);
create index we_status_idx on weblogentry(status);
create index we_locale_idx on weblogentry(locale);
create index we_combo1_idx on weblogentry(status, pubtime, websiteid);
create index we_combo2_idx on weblogentry(websiteid, pubtime, status);

create table roller_weblogentrytag (
    id              varchar(48)   not null primary key,
    entryid         varchar(48)   not null,
    websiteid       varchar(48)   not null,    
    creator	        varchar(255)   not null,
    name            varchar(255)  not null,
    time            $db.TIMESTAMP_SQL_TYPE 	not null
);

create index wet_entryid_idx on roller_weblogentrytag( entryid );
create index wet_weblogid_idx on roller_weblogentrytag( websiteid );
create index wet_creator_idx on roller_weblogentrytag( creator );
create index wet_name_idx on roller_weblogentrytag( name );

create table roller_weblogentrytagagg (
    id              varchar(48)   not null primary key,
    websiteid       varchar(48) ,    
    name            varchar(255)  not null,
    total           integer		  not null,
    lastused        $db.TIMESTAMP_SQL_TYPE 	not null
);

create index weta_weblogid_idx on roller_weblogentrytagagg( websiteid );
create index weta_name_idx on roller_weblogentrytagagg( name );
create index weta_lastused_idx on roller_weblogentrytagagg( lastused );
alter table roller_weblogentrytagagg add constraint weta_weblog_tag_uq unique ( websiteid, name );

create table newsfeed (
    id              varchar(48) not null primary key,
    name            varchar(255) not null,
    description     varchar(255) not null,
    link            varchar(255) not null,
    websiteid       varchar(48) not null
);
create index nf_weblogid_idx on newsfeed( websiteid );


create table roller_comment (
    id         varchar(48) not null primary key,
    entryid    varchar(48) not null,
    name       varchar(255),
    email      varchar(255),
    url        varchar(255),
    content    $db.TEXT_SQL_TYPE,
    posttime   $db.TIMESTAMP_SQL_TYPE   not null,
    notify     $db.BOOLEAN_SQL_TYPE_FALSE not null,
    remotehost varchar(128),
    referrer   varchar(255),
    useragent  varchar(255),
    status     varchar(20) not null,
    plugins    varchar(255),
    contenttype varchar(128) default 'text/plain' not null
);
create index co_entryid_idx on roller_comment( entryid );
create index co_status_idx on roller_comment( status );

-- Ping Feature Tables
-- name: short descriptive name of the ping target
-- pingurl: URL to receive the ping
-- conditioncode:
-- lastsuccess:
create table pingtarget (
    id           varchar(48) not null primary key,
    name         varchar(255) not null,
    pingurl      varchar(255) not null,
    conditioncode    integer default 0 not null,
    lastsuccess  $db.TIMESTAMP_SQL_TYPE,
    autoenabled  $db.BOOLEAN_SQL_TYPE_FALSE not null
);

-- auto ping configurations
-- websiteid:  fk reference to weblog for which this auto ping configuration applies
-- pingtargetid: fk reference to the ping target to be pinged when the weblog changes
create table autoping (
    id            varchar(48) not null primary key,
    websiteid     varchar(48) not null,
    pingtargetid  varchar(48) not null 
);
create index ap_websiteid_idx on autoping( websiteid );
create index ap_pingtid_idx on autoping( pingtargetid );

-- entrytime: timestamp of original entry onto the ping queue
-- pingtargetid: weak fk reference to ping target (not constrained)
-- websiteid: weak fk reference to weblog originating the ping (not constrained)
-- attempts:  number of ping attempts that have been made for this entry
create table pingqueueentry (
    id             varchar(48) not null primary key,
    entrytime      $db.TIMESTAMP_SQL_TYPE not null, 
    pingtargetid   varchar(48) not null,  
    websiteid      varchar(48) not null,  
    attempts       integer not null
);
create index pqe_entrytime_idx on pingqueueentry( entrytime );
create index pqe_pingtid_idx on pingqueueentry( pingtargetid );
create index pqe_websiteid_idx on pingqueueentry( websiteid );

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

create table roller_tasklock (
    id              varchar(48)   not null primary key,
    name            varchar(255)  not null,
    islocked        $db.BOOLEAN_SQL_TYPE_FALSE,
    timeacquired    $db.TIMESTAMP_SQL_TYPE_NULL,
    timeleased	    integer,
    lastrun         $db.TIMESTAMP_SQL_TYPE_NULL,
    client          varchar(255)
);
alter table roller_tasklock add constraint rtl_name_uq unique ( name$!db.INDEXSIZE );
#if($db.DBTYPE != "ORACLE")
create index rtl_taskname_idx on roller_tasklock( name );
#end

create table roller_hitcounts (
    id              varchar(48) not null primary key,
    websiteid       varchar(48) not null,
    dailyhits	    integer
);
create index rhc_websiteid_idx on roller_hitcounts( websiteid );
create index rhc_dailyhits_idx on roller_hitcounts( dailyhits );

-- Entry attribute: metadata for weblog entries
create table entryattribute (
    id       varchar(48) not null primary key,
    entryid  varchar(48) not null,
    name     varchar(255) not null,
    value    $db.TEXT_SQL_TYPE not null
);
create index ea_entryid_idx on entryattribute( entryid );
alter table entryattribute add constraint ea_name_uq unique ( entryid, name$!db.INDEXSIZE );


-- OAUTH SUPPORT

-- each record is an OAuth consumer key and secret, can be tied to just one user
create table roller_oauthconsumer (
    consumerkey    varchar(48) not null primary key,
    consumersecret varchar(48) not null,
    username       varchar(48)
);

-- each record is an OAuth accessor, always tied to just one user
create table roller_oauthaccessor (
    consumerkey  varchar(48) not null primary key,
    requesttoken varchar(48),
    accesstoken  varchar(48),
    tokensecret  varchar(48),
    created      $db.TIMESTAMP_SQL_TYPE not null,
    updated      $db.TIMESTAMP_SQL_TYPE not null,
    username     varchar(48),
    authorized   $db.BOOLEAN_SQL_TYPE_FALSE
);

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


-- PLANET FEED AGGREGATOR

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);

-- create a default planet and group
insert into rag_planet (id, handle, title) values ('zzz_default_planet_zzz', 'default', 'Default Planet');
insert into rag_group (id, planet_id, handle, title) values ('zzz_all_group_zzz', 'zzz_default_planet_zzz', 'all', 'Default Group');


-- MEDIA BLOGGING

create table roller_mediafile (
    id              varchar(48) not null primary key,
    name            varchar(255) not null,
    description     varchar(255),
    origpath        varchar(255),
    content_type    varchar(50)  not null,
    copyright_text  varchar(1023),
    directoryid     varchar(48) not null,
    weblogid        varchar(48) not null,
    width           integer,
    height          integer,
    size_in_bytes   integer,
    date_uploaded   $db.TIMESTAMP_SQL_TYPE not null,
    last_updated    $db.TIMESTAMP_SQL_TYPE,
    anchor          varchar(255),
    creator         varchar(255),
    is_public       $db.BOOLEAN_SQL_TYPE_FALSE not null
);

create table roller_mediafiletag (
    id              varchar(48) not null primary key,
    mediafile_id    varchar(48) not null,
    name            varchar(30) not null
);

create table roller_mediafiledir (
    id               varchar(48) not null primary key,
    name             varchar(255) not null,
    description      varchar(255),
    websiteid        varchar(48) not null
);


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

-- user, role, weblog, and permissions

-- page, entry, category, comment

alter table weblog_custom_template add constraint wct_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

alter table custom_template_rendition add constraint ctr_templateid_fk
    foreign key ( templateid ) references weblog_custom_template( id ) $!db.ADDL_FK_PARAMS ;

alter table weblogentry add constraint we_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

alter table weblogentry add constraint we_categoryid_fk
    foreign key ( categoryid ) references weblogcategory( id ) $!db.ADDL_FK_PARAMS ;

alter table roller_weblogentrytag add constraint rwtg_entryid_fk
    foreign key ( entryid ) references weblogentry( id ) $!db.ADDL_FK_PARAMS ;

alter table weblogcategory add constraint wc_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

alter table roller_comment add constraint co_entryid_fk
    foreign key ( entryid ) references weblogentry( id ) $!db.ADDL_FK_PARAMS ;

alter table entryattribute add constraint att_entryid_fk
    foreign key ( entryid ) references weblogentry( id ) $!db.ADDL_FK_PARAMS ;

-- bookmark_folder and bookmark

alter table bookmark_folder add constraint fo_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

alter table bookmark add constraint bm_folderid_fk
    foreign key ( folderid ) references bookmark_folder( id ) $!db.ADDL_FK_PARAMS ;

-- media file foreign key constraints

alter table roller_mediafile add constraint roller_mediafiledir_id_fk
    foreign key (directoryid) references roller_mediafiledir(id) $!db.ADDL_FK_PARAMS ;

alter table roller_mediafiletag add constraint roller_mediafile_id_tag_fk
    foreign key (mediafile_id) references roller_mediafile(id) $!db.ADDL_FK_PARAMS ;

alter table roller_mediafiledir add constraint mf_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

-- newsfeed

alter table newsfeed add constraint nf_weblogid_fk
    foreign key ( websiteid ) references weblog( id ) $!db.ADDL_FK_PARAMS ;

-- autoping

alter table autoping add constraint ap_weblogid_fk
    foreign key (websiteid) references weblog(id) $!db.ADDL_FK_PARAMS ;

alter table autoping add constraint ap_pingtargetid_fk
    foreign key (pingtargetid) references pingtarget(id) $!db.ADDL_FK_PARAMS ;

-- oauth indexes

create index oc_username_idx  on roller_oauthconsumer( username$!db.INDEXSIZE );
create index oc_consumerkey_idx  on roller_oauthconsumer( consumerkey$!db.INDEXSIZE );