Andy Jarrett: SQL for upgrading to Blog CFC v5

I am in the middle of very slowy upgrading my blog to Rays version 5, and as usual there has been a few DB changes. Below is the SQL (for MySQL, though easily adapted) update script I'm using for the updgrade, with Rays comments about the changes included. You have to be running v4+ of BlogCFC for this to work!

Before running the SQL please read through it! Along with the new table and fields there are two UPDATE statements which affects your data. Just make sure you understand what you are doing before you run it as this code comes as is, if you run the script and your computer turn into a Commodore 64 because of it, its not my fault!

view plain print about

1#tblUsers:
2#name(nvarchar/50) added - You should add your name here, or your code name. Or whatever you go by.
3ALTER TABLE `andyjarrett`.`tblusers`
4 ADD COLUMN `name` varchar(50) NULL DEFAULT NULL;
5
6UPDATE `tblusers` SET `name`=!!PUT YOU NAME/CODE NAME HERE!! WHERE `username`=!!USERNAME HERE!! AND `password`=!!PASSWORD HERE!!;
7
8
9#tblBlogEntriesRelated: (New table)
10#    entryid (nvarchar/35)
11#    relatedid (nvarchar/35)
12CREATE TABLE `tblblogentriesrelated` (
13 `entryid` varchar(35) NOT NULL DEFAULT '',
14 `relatedid` varchar(35) NULL DEFAULT NULL,
15 PRIMARY KEY (`entryid`)
16) ENGINE=MyISAM;
17
18
19#tblBlogEntries:
20#    views(int) added - You must set all old views to 0
21#    released(bit) added - You must set your old data to released=1 with a quick query
22#    mailed(bit) added - You can set the old ones to true, but you don't need to)
23
24ALTER TABLE `tblblogentries`
25 ADD COLUMN `views` int(11) NOT NULL DEFAULT 0;
26
27ALTER TABLE `tblblogentries`
28 ADD COLUMN `released` tinyint(3) NOT NULL DEFAULT 0;
29
30ALTER TABLE `tblblogentries`
31 ADD COLUMN `mailed` tinyint(3) NOT NULL DEFAULT 1;
32
33# :::::::::::::::::::::::::::::::::: START: DATA UPDATE STATMENT :::::::::::::::::::::::::::::::::::::::::#
34UPDATE `tblblogentries`
35SET `released`=1;
36# :::::::::::::::::::::::::::::::::: END: DATA UPDATE STATMENT :::::::::::::::::::::::::::::::::::::::::#
37
38
39#tblBlogCategories:
40#    categoryalias(nvarchar/50) added
41ALTER TABLE `tblblogcategories`
42 ADD COLUMN `categoryalias` varchar(50) NOT NULL;
43
44
45#DB CHANGE: New table, tblblogtextblocks. Columns:
46#    id, nvarchar 35, primary key
47#    label, nvarchar 255,
48#    body, ntext,
49#    blog, nvarchar 50
50
51CREATE TABLE `tblblogtextblocks` (
52 `id` varchar(35) NOT NULL DEFAULT '',
53 `label` varchar(255) NULL DEFAULT NULL,
54 `body` text NULL,
55 `blog` varchar(50) NULL DEFAULT NULL,
56 PRIMARY KEY (`id`)
57) ENGINE=MyISAM;
58
59
60
61#DB CHANGE: New table, tblblogpages. Columns:
62#    id, nvarchar 35, primary key
63#    title, nvarchar 255
64#    alias, nvarchar 100
65#    body, ntext,
66#    blog, nvarchar 50
67
68CREATE TABLE `tblblogpages` (
69 `id` varchar(35) NOT NULL DEFAULT '',
70 `title` varchar(255) NULL DEFAULT NULL,
71 `alias` varchar(100) NULL DEFAULT NULL,
72 `body` text NULL,
73 `blog` varchar(50) NULL DEFAULT NULL,
74 PRIMARY KEY (`id`)
75) ENGINE=MyISAM;
76
77#DB CHANGE: Add verified as a bit to the tblblogsubscribers table.
78#You should write a quick script to update your current subscribers with verified=1. If you do not, they will not get email.
79
80ALTER TABLE `tblblogsubscribers`
81 ADD COLUMN `verified` tinyint(3) NULL DEFAULT 0;
82
83# :::::::::::::::::::::::::::::::::: START: DATA UPDATE STATMENT :::::::::::::::::::::::::::::::::::::::::#
84UPDATE `tblblogsubscribers`
85SET `verified`=1
86# :::::::::::::::::::::::::::::::::: END: DATA UPDATE STATMENT :::::::::::::::::::::::::::::::::::::::::#

Related Blog Entries

Finally upgraded (August 3, 2006)

Posted By Sam Farmer @ 8/6/06 5:00 PM

Thanks Andy.

This helped a lot.

[Add Comment]