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

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

-- roller_permission constant updated to be more clear
update roller_properties set name = 'user.account.email.activation' where name = 'user.account.activation.enabled';

-- website table was renamed to weblog and several unused columns dropped
#renameTable('website' 'weblog')
#dropColumn('weblog' 'emailfromaddress')
#dropColumn('weblog' 'pagemodels')
#dropColumn('weblog' 'defaultcatid')
#dropColumn('weblog' 'ignorewords')
#dropColumn('weblog' 'defaultpageid')
#dropColumn('weblog' 'weblogdayid')

-- two weblog columns renamed
#addColumnNotNull('weblog' 'visible' $db.BOOLEAN_SQL_TYPE $db.BOOLEAN_TRUE)
#addColumnNull('weblog' 'tagline'  "varchar(255)")
update weblog set visible = isenabled;
update weblog set tagline = description;
#dropColumn('weblog' 'isenabled')
#dropColumn('weblog' 'description')

-- different value for our Xinha editor
update weblog set editorpage = 'editor-xinha.jsp' where editorpage = 'editor-rte.jsp';

-- some tables renamed
#renameTable('folder' 'bookmark_folder')
#renameTable('rolleruser' 'roller_user')
#renameTable('webpage' 'weblog_custom_template')

-- openID value moved from deprecated roller_userattribute table to new roller_user.openid_url column
#addColumnNull('roller_user' 'openid_url'  "varchar(255)")

update roller_user ru
set openid_url = (select attrvalue from roller_userattribute rua where attrname = 'openid.url' and ru.username = rua.username)
where username in (select username from roller_userattribute rua where attrname = 'openid.url');

-- roller_userattribute table no longer referenced by Roller application, can be
-- manually dropped if your project is not using it for anything else

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

-- following may not run on MySQL, recommended to try manually
-- alter table custom_template_rendition add constraint ctr_templateid_fk
--    foreign key ( templateid ) references weblog_custom_template( id ) $db.ADDL_FK_PARAMS ;

-- capitalizing column constants as these are now stored as enums in Java.
update weblog_custom_template set templatelang = upper(templatelang);
update weblog_custom_template wct set action = upper(action);

-- The main stylesheet for a theme has a new action, STYLESHEET.
update weblog_custom_template wct set action='STYLESHEET' where link is not null and link = (select customstylesheet from weblog w where w.id = wct.websiteid);

-- With above statement, weblog.customstylesheet no longer needed.
#dropColumn('weblog' 'customstylesheet')

-- template renditions now stored in custom_template_rendition table; 5.0.x has only standard (non-mobile) type
-- important: Don't run below statement if upgrading from a 5.1 snapshot to 5.1 release version as
-- custom_template_rendition already has your custom templates, it would end up overwriting with default ones
insert into custom_template_rendition(id, templateid, template, templatelang, type)
select id, id, template, templatelang, 'STANDARD' from weblog_custom_template;

-- With above statement, below columns no longer needed
#dropColumn('weblog_custom_template' 'template')
#dropColumn('weblog_custom_template' 'templatelang')
#dropColumn('weblog_custom_template' 'decorator')

-- HTML header search description now available for each blog entry
#addColumnNull("weblogentry" "search_description"  "varchar(255)")

-- Removal of subcategories means no more path and parentid columns
delete from weblogcategory where name = 'root' and parentid is null;
#dropColumn("weblogcategory" 'parentid')
#dropColumn("weblogcategory" 'path')

-- Allow users to order their weblog categories (zero-based)
#addColumnNotNull("weblogcategory" "position" "integer" "0")

-- Removal of custom ping targets
delete from pingtarget where websiteid is not null;
#dropForeignKey("pingtarget" "pt_websiteid_fk")
#dropColumn("pingtarget" "websiteid")

-- If you run this script manually (i.e. you are doing installation.type=manual)
-- them you may need to comment out this next statement, this index does not 
-- exist in all Roller systems:
#dropIndex("bookmark_folder" "folder_namefolderid_uq")

-- Removal of bookmark subfolders and renaming of former root folder to 'default'
-- If a bookmark folder with name 'default' already exists, rename it by adding its id to it.
update bookmark_folder set name = #concat("name" "id") where name = 'default';
update bookmark_folder set name = 'default' where name = 'root' and parentid is null;
#dropColumn("bookmark_folder" 'parentid')
#dropColumn("bookmark_folder" 'path')
#dropColumn("bookmark_folder" "description")
#dropColumn("bookmark" "weight")

-- Removal of media file subfolders and renaming of former root folder to 'default'
-- If a folder with name 'default' already exists, rename it by adding its id to it.
update roller_mediafiledir set name = #concat("name" "id") where name = 'default';
update roller_mediafiledir set name = 'default' where name = 'root' and parentid is null;
#dropColumn("roller_mediafiledir" "path")
#dropForeignKey("roller_mediafiledir" "mf_parentid_fk")
#dropColumn("roller_mediafiledir" "parentid")

-- Adding blog-specific web analytics (e.g. Google Analytics) tracking code
#addColumnNull("weblog" "analyticscode" $db.TEXT_SQL_TYPE)

-- Referer table no longer populated, retaining for older Roller instances in case
-- legacy data is desired to keep; but removing its FK relationships to other tables
#dropForeignKey("referer" "ref_entryid_fk")
#dropForeignKey("referer" "ref_websiteid_fk")