app/src/main/resources/sql/310-to-400-migration.vm

#** 
310-to-400-migration.vm: Velocity template that generates vendor-specific database scripts 

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

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

-- ensure that every weblog entry has a valid locale

#if ($db.DBTYPE != "POSTGRESQL")
update weblogentry e set 
   e.pubtime=pubtime, 
   e.updatetime=updatetime, 
   e.locale=(select locale from website where website.id=e.websiteid) 
   where e.locale is null or length(e.locale)=0;
#else
update weblogentry set 
   pubtime=pubtime, 
   updatetime=updatetime, 
   locale=(select locale from website where website.id=websiteid) 
   where locale is null or length(locale)=0;
#end

-- add new planet_id column to planet group table
#addColumnNull("rag_group" "planet_id" "varchar(48)")

-- upgrade old planet users to work with the new Roller Planet code
-- all groups must have a planet now, so provide a default planet and
-- put all existing groups in the new default planet
insert into rag_planet (id,title,handle) values ('zzz_default_planet_zzz','Default Planet','default');
update rag_group set planet_id='zzz_default_planet_zzz';


-- upgrade the way hierarchical objects are modeled

-- add new parentid column to weblogcategory table
#addColumnNull("weblogcategory" "parentid" "varchar(48)")
create index ws_parentid_idx on weblogcategory( parentid );

-- add new path column to weblogcategory table
#addColumnNull("weblogcategory" "path" "varchar(255)")
create index ws_path_idx on weblogcategory( path );

-- need to add this index for existing folder.parentid
create index fo_parentid_idx on folder( parentid );

-- add new path column to folder table
#addColumnNull("folder" "path" "varchar(255)")
create index fo_path_idx on folder( path );


-- update comment handling

-- add new fields to comment table to support CommentValidators
#addColumnNull("roller_comment" "referrer" "varchar(255)")
#addColumnNull("roller_comment" "useragent" "varchar(255)")

-- add new field to support comment plugins and content-type
#addColumnNull("roller_comment" "plugins" "varchar(255)")
#addColumnNotNull("roller_comment" "contenttype" "varchar(128)" "'text/plain'")

-- add new status field to comment table to simplify queries
#addColumnNotNull("roller_comment" "status" "varchar(20)" "'APPROVED'")

-- new status column needs an index
create index co_status_idx on roller_comment(status);

-- update existing data to use new status column
update roller_comment set status = 'APPROVED', posttime=posttime where approved=$db.BOOLEAN_TRUE;
update roller_comment set status = 'PENDING', posttime=posttime where pending=$db.BOOLEAN_TRUE;
update roller_comment set status = 'SPAM', posttime=posttime where spam=$db.BOOLEAN_TRUE;

update roller_comment set status = 'DISAPPROVED', posttime=posttime 
   where approved=$db.BOOLEAN_FALSE and spam=$db.BOOLEAN_FALSE and pending=$db.BOOLEAN_FALSE;


-- better support for doing scheduled entries

-- add new status option 'SCHEDULED' for future published entries
update weblogentry set status = 'SCHEDULED', pubtime=pubtime, updatetime=updatetime where pubtime > current_timestamp;

-- add new client column to roller_tasklock table
#addColumnNull("roller_tasklock" "client" "varchar(255)")

-- new column to support account activation by email
#addColumnNull("rolleruser" "activationcode" "varchar(48)")

-- new column to support screen name and populate with user names
#addColumnNotNull("rolleruser" "screenname" "varchar(255)" "'unspecified'")
update rolleruser set screenname = username;

-- new column to allow setting of path to icon for website
#addColumnNull("website" "icon" "varchar(255)")

-- new column to allow setting of short website about text
#addColumnNull("website" "about" "varchar(255)")

-- new column to allow setting of page template content-type
#addColumnNull("webpage" "outputtype" "varchar(48)")

-- add new action column to webpage table, default value is custom
#addColumnNotNull("webpage" "action" "varchar(16)" "'custom'")
update webpage set action = 'weblog' where name = 'Weblog';

-- add new custom stylesheet column to website table
#addColumnNull("website" "customstylesheet" "varchar(128)")

-- fix blogs which have unchecked showalllangs but did not check enablemultilang
update website set enablemultilang=$db.BOOLEAN_TRUE, datecreated=datecreated where showalllangs=$db.BOOLEAN_FALSE;


-- some missing foreign key constraints
alter table roller_user_permissions add constraint up_userid_fk
    foreign key ( user_id ) references rolleruser( id ) $!db.ADDL_FK_PARAMS ;

alter table roller_user_permissions add constraint up_websiteid_fk
    foreign key ( website_id ) references website( id ) $!db.ADDL_FK_PARAMS ;


-- some various indexes to improve performance
create index rhc_dailyhits_idx on roller_hitcounts( dailyhits );
create index we_combo1_idx on weblogentry(status, pubtime, websiteid);
create index we_combo2_idx on weblogentry(websiteid, pubtime, status);
create index co_combo1_idx on roller_comment(status, posttime);


-- remove old indexes that are no longer of value
#dropIndex('weblogentry' 'we_pubentry_idx')


-- fix wacky indexs which ended up with a size constraint
#dropIndex('rag_entry' 'rage_sid_idx')
create index rage_sid_idx on rag_entry(subscription_id);

#dropIndex('rag_group_subscription' 'raggs_gid_idx')
create index raggs_gid_idx on rag_group_subscription(group_id);

#dropIndex('rag_group_subscription' 'raggs_sid_idx')
create index raggs_sid_idx on rag_group_subscription(subscription_id);


-- remove no-longer-used needed tables

-- remove old rollerconfig table which has been deprecated since 1.2
#dropTableIfExists('rollerconfig')

-- remove old id column of group subscription table
-- #dropColumn('rag_group_subscription' 'id')

-- remove old approved, spam, pending columns from comment table
#dropColumn('roller_comment' 'approved')
#dropColumn('roller_comment' 'spam')
#dropColumn('roller_comment' 'pending')

-- remove bastard columns and indexes (optional)
-- #dropIndex('weblogentry' 'index_we_pubtime_idx')
-- #dropIndex('roller_comment' 'co_pending_idx')
-- #dropIndex('roller_comment' 'co_approved_idx')
-- #dropColumn('website' 'userid')
-- #dropColumn('website' 'weblogdayid')
-- #dropColumn('weblogentry' 'publishentry')
-- #dropColumn('weblogentry' 'link')
-- #dropTableIfExists('usercookie')
-- #dropTableIfExists('rag_config')
-- #dropTableIfExists('folderassoc')
-- #dropTableIfExists('weblogcategoryassoc')