/*
*********************** READ THIS ***********************
BlogCFC v5.5 to v5.9.3 MySQL Migration Code

The SQL statements below are designed to migrate an existing MySQL BlogCFC 5.5
database to version 5.9.3 architecture. I have tested and executed the code against
local and production installations of MySQL 4.x and MySQL 5.x only. I had no problems
each time I tested this code, but your mileage may vary.

DATE:		4.10.2009
AUTHOR:		Aaron S. West
E-MAIL:		trajik210@gmail.com
WEB:		http://www.trajiklyhip.com/blog/
			http://www.aaronwest.net (soon!)

WARNING:
If something screws up using the code below you are on your own. I'm making it
available as a community service only. I make absolutely zero warranties or
claims as to its validity or correctness. Pretty please backup your production database before
you attempt to upgrade. Do the same with your blog code. If something blow up, you won't stress
out as much if you KNOW you have a current backup of your database and your code.
*/

TABLES UNAFFECTED BY THESE SCRIPTS:
The following tables had zero changes from v5.5 to v5.9.3 and do not need to be changed with
this upgrade:

tblblogpages
tblblogsearchstats
tblblogsubscribers
tblblogtextblocks
tblusers


HOW TO READ AND USE THE REST OF THIS TEXT FILE (PLUS INFO ON SOME HELPFUL TOOLS):
All text from here on out is mostly SQL code with some section delimiters to make things easier
for you (and me) to read. Each table that changed from BlogCFC 5.5 to 5.9.3 is listed below in it's
own section. I show the v5.5 create table script followed by the v5.9.3 create table script. I
then compared the two scripts in order to manually create alter table statements. The needed
alter table statements follow the two create table scripts. Each tables working code (old create
table script, current create table script, and alter table commands) are separated by a long line of
equals signs to make it easy for you to jump from table to table.

What I did to bring my blog architecture up to 5.9.3 was to execute the ALTER TABLE commands
individually for each table paying close to attention to any thrown errors. I never received any
errors. NOTE: This should go without saying but you will also need to compare the latest 5.9.3
ColdFusion code with your current codebase and make any needed changes. If you're on Windows
this can be done pretty easily using Scooter Software's Beyond Compare tool ($30) or
WinMerge (free). If you're on Mac OS X like me, I highly, highly recommend you purchase Changes.
It costs $50 but it is well, well worth the money as it's the best OS X directory and file
compare/merge software I've found. Here are current links to these tools:

Beyond Compare (Windows): http://www.scootersoftware.com/
WinMerge (Windows): http://winmerge.org/
Changes (Mac OS X): http://changesapp.com/

While I'm on the subject of tools, I'll also say the best MySQL tool I've found for managing
table architecture, indexes, and data, and to write queries is Sequel Pro (free, OS X only). If
you couple Sequel Pro with the free MySQL Administrator tool (from the MySQL team), you'll have
a decent set of DB maangement tools.

Sequel Pro (Mac OS X): http://www.sequelpro.com/

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogcategories` (
  `categoryid` varchar(35) character set latin1 NOT NULL default '',
  `categoryname` varchar(50) character set latin1 NOT NULL default '',
  `categoryalias` varchar(50) default NULL,
  `blog` varchar(50) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`categoryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogcategories` (
  `categoryid` varchar(35) character set latin1 NOT NULL default '',
  `categoryname` varchar(50) character set latin1 NOT NULL default '',
  `categoryalias` varchar(50) default NULL,
  `blog` varchar(50) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`categoryid`),
  KEY `blogCategories_blog` (`blog`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogcategories` ADD KEY `blogCategories_blog` (`blog`)

==================================================================================

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogcomments` (
  `id` varchar(35) character set latin1 NOT NULL default '',
  `entryidfk` varchar(35) character set latin1 default NULL,
  `name` varchar(50) character set latin1 default NULL,
  `email` varchar(50) character set latin1 default NULL,
  `comment` text character set latin1,
  `posted` datetime default NULL,
  `subscribe` tinyint(1) default NULL,
  `website` varchar(255) default NULL,
  `moderated` tinyint(4) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogcomments` (
  `id` varchar(35) character set latin1 NOT NULL default '',
  `entryidfk` varchar(35) character set latin1 default NULL,
  `name` varchar(50) character set latin1 default NULL,
  `email` varchar(50) character set latin1 default NULL,
  `comment` text character set latin1,
  `posted` datetime default NULL,
  `subscribe` tinyint(1) default NULL,
  `website` varchar(255) default NULL,
  `moderated` tinyint(1) default NULL,
  `killcomment` varchar(35) default NULL,
  `subscribeonly` tinyint(4) default NULL,
  PRIMARY KEY  (`id`),
  KEY `blogComments_entryid` (`entryidfk`),
  KEY `blogComments_posted` (`posted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogcomments` MODIFY COLUMN `moderated` tinyint(1) default NULL
ALTER TABLE `tblblogcomments` ADD COLUMN `killcomment` varchar(35) default NULL
ALTER TABLE `tblblogcomments` ADD COLUMN `subscribeonly` tinyint(4) default NULL
ALTER TABLE `tblblogcomments` ADD KEY `blogComments_entryid` (`entryidfk`)
ALTER TABLE `tblblogcomments` ADD KEY `blogComments_posted` (`posted`)

==================================================================================

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogentries` (
  `id` varchar(35) character set latin1 NOT NULL default '',
  `title` varchar(100) character set latin1 NOT NULL default '',
  `body` text character set latin1 NOT NULL,
  `posted` datetime NOT NULL default '0000-00-00 00:00:00',
  `morebody` text character set latin1,
  `alias` varchar(100) character set latin1 default NULL,
  `username` varchar(50) character set latin1 default NULL,
  `blog` varchar(50) character set latin1 NOT NULL default '',
  `allowcomments` tinyint(1) NOT NULL default '0',
  `enclosure` varchar(255) character set latin1 default NULL,
  `filesize` int(11) unsigned default '0',
  `mimetype` varchar(255) character set latin1 default NULL,
  `views` int(11) default NULL,
  `released` tinyint(1) default '0',
  `mailed` tinyint(1) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogentries` (
  `id` varchar(35) character set latin1 NOT NULL default '',
  `title` varchar(100) character set latin1 NOT NULL default '',
  `body` longtext character set latin1 NOT NULL,
  `posted` datetime NOT NULL default '0000-00-00 00:00:00',
  `morebody` longtext character set latin1,
  `alias` varchar(100) character set latin1 default NULL,
  `username` varchar(50) character set latin1 default NULL,
  `blog` varchar(50) character set latin1 NOT NULL default '',
  `allowcomments` tinyint(1) NOT NULL default '0',
  `enclosure` varchar(255) character set latin1 default NULL,
  `filesize` int(11) unsigned default '0',
  `mimetype` varchar(255) character set latin1 default NULL,
  `views` int(11) default NULL,
  `released` tinyint(1) default '0',
  `mailed` tinyint(1) default '0',
  `summary` varchar(255) default NULL,
  `subtitle` varchar(100) default NULL,
  `keywords` varchar(100) default NULL,
  `duration` varchar(10) default NULL,
  PRIMARY KEY  (`id`),
  KEY `blogEntries_blog` (`blog`),
  KEY `blogEntries_released` (`released`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogentries` MODIFY COLUMN `body` longtext character set latin1 NOT NULL
ALTER TABLE `tblblogentries` MODIFY COLUMN `morebody` longtext character set latin1
ALTER TABLE `tblblogentries` ADD COLUMN `summary` varchar(255) default NULL
ALTER TABLE `tblblogentries` ADD COLUMN `subtitle` varchar(100) default NULL
ALTER TABLE `tblblogentries` ADD COLUMN `keywords` varchar(100) default NULL
ALTER TABLE `tblblogentries` ADD COLUMN `duration` varchar(10) default NULL
ALTER TABLE `tblblogentries` ADD KEY `blogEntries_blog` (`blog`)
ALTER TABLE `tblblogentries` ADD KEY `blogEntries_released` (`released`)

==================================================================================

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogentriescategories` (
  `categoryidfk` varchar(35) character set latin1 default NULL,
  `entryidfk` varchar(35) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogentriescategories` (
  `categoryidfk` varchar(35) character set latin1 default NULL,
  `entryidfk` varchar(35) character set latin1 default NULL,
  KEY `blogEntriesCategories_entryid` (`entryidfk`,`categoryidfk`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogentriescategories` ADD KEY `blogEntriesCategories_entryid` (`entryidfk`,`categoryidfk`)

==================================================================================

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogentriesrelated` (
  `id` int(11) default NULL,
  `entryid` varchar(35) NOT NULL default '',
  `relatedid` varchar(35) default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8


*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogentriesrelated` (
  `id` int(11) default NULL,
  `entryid` varchar(35) NOT NULL default '',
  `relatedid` varchar(35) default '',
  KEY `blogEntriesRelated_entryid` (`entryid`,`relatedid`),
  KEY `blogEntriesRelated_relatedid` (`relatedid`,`entryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogentriesrelated` ADD KEY `blogEntriesRelated_entryid` (`entryid`,`relatedid`)
ALTER TABLE `tblblogentriesrelated` ADD KEY `blogEntriesRelated_relatedid` (`relatedid`,`entryid`)

==================================================================================

*********** old BlogCFC 5.5 database ***********

CREATE TABLE `tblblogtrackbacks` (
  `Id` varchar(35) character set latin1 NOT NULL default '',
  `title` varchar(255) character set latin1 NOT NULL default '',
  `blogname` varchar(255) character set latin1 NOT NULL default '',
  `posturl` varchar(255) character set latin1 NOT NULL default '',
  `excerpt` text character set latin1 NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `entryid` varchar(35) character set latin1 NOT NULL default '',
  `blog` varchar(50) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

*********** default BlogCFC 5.9.3 database ***********

CREATE TABLE `tblblogtrackbacks` (
  `Id` varchar(35) character set latin1 NOT NULL default '',
  `title` varchar(255) character set latin1 NOT NULL default '',
  `blogname` varchar(255) character set latin1 NOT NULL default '',
  `posturl` varchar(255) character set latin1 NOT NULL default '',
  `excerpt` text character set latin1 NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `entryid` varchar(35) character set latin1 NOT NULL default '',
  `blog` varchar(50) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`Id`),
  KEY `blogTrackBacks_entryid` (`entryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

++++++++++++ ALTER table commands ++++++++++++

ALTER TABLE `tblblogtrackbacks` ADD KEY `blogTrackBacks_entryid` (`entryid`)

==================================================================================