app/src/main/resources/sql/createdb.vm

#**
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  The ASF licenses this file to You
  under the Apache License, Version 2.0 (the "License"); you may not
  use this file except in compliance with the License.
  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.  For additional information regarding
  copyright in this work, please see the NOTICE file in the top level
  directory of this distribution.
*#
#** 
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,
    bannedwordslist            $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 );