At times like this you need to let everything go and be at peace with yourself, it will come back to you. -Ransford Allen Gilliland <[EMAIL PROTECTED]> wrote:okay, folks ... i don't want to let this one slip off the radar, but unfortunately i don't have the time, resources, and db proficiency to know if the sql statements i listed below are okay in all dbs.
i was not kidding when i said it took 20 hrs to do the application level upgrade logic (instead of the sql below) on the first deployment of 2.0 against a db with 1600 blogs. if we can't get the sql below added to the upgrade script then that means the upgrade process for sites with more than a handful of blogs will probably get pretty ugly :/ -- Allen On Wed, 2005-09-21 at 13:18, Allen Gilliland wrote: > So, I've been doing some testing of the db upgrade procedure in preparation > for the upcoming deployment of 2.0 to blogs.sun.com and I found that the > UpgradeDatabase.upgradeTo200() application level upgrade process took me ~20 > hrs to run with 1650 blogs :( > > I'm not sure why it was so slow to be honest, but I decided to try doing the > same steps using pure sql and came up with this ... > > -- update website handles using existing usernames > UPDATE website,rolleruser SET website.handle = rolleruser.username WHERE > website.userid = rolleruser.id AND website.handle = ''; > > -- update PUBLISHED weblog entries with userids and status > UPDATE website,rolleruser,weblogentry SET weblogentry.userid=rolleruser.id, > weblogentry.status='PUBLISHED' WHERE website.userid=rolleruser.id AND > website.id=weblogentry.websiteid AND weblogentry.userid = '' AND > weblogentry.publishentry = 1; > > -- update DRAFT weblog entries with userids and status > UPDATE website,rolleruser,weblogentry SET weblogentry.userid=rolleruser.id, > weblogentry.status='DRAFT' WHERE website.userid=rolleruser.id AND > website.id=weblogentry.websiteid AND weblogentry.userid = '' AND > weblogentry.publishentry = 0; > > -- populate new permissions table > INSERT into roller_user_permissions(id, website_id, user_id, permission_mask, > pending) SELECT concat(website.id, 'p'),website.id,rolleruser.id,3,0 FROM > website,rolleruser WHERE website.userid=rolleruser.id; > > -- update db version property > UPDATE roller_properties SET value = '200' WHERE name = > 'roller.database.version'; > > I can commit these changes back to the upgrade script, but I wasn't sure if > this sql would work on all databases. I've only tested on mysql 4.1.10 so far. > > I'm guessing most dbs would allow for the multi-table update statements, but > the Insert ... Select statement may not be supported. Anyone else want to > give this a stab on any other dbs they use and see if we should make any of > these part of the standard 2.0 upgrade script? > > -- Allen > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
