4.0 RC1 Errors in postgres migration script from 3.1 to 4.0 corrected script attached

[Adib Saikali <ad...@programmingmastery.com>]

Hi,

I downloaded the roller 4.0 rc1 and discovered that the upgrade scripts from
roller 3.1 to 4.0 were not working, they had a few syntax errors. 

I encountred three tyes of errors. 

1) various drop index statements were of the form "drop index xyz on table"
those had to be changed to 
"drop index xyz" the on table part of the syntox did not work with postgres
8.2.4 

2) a udpate statement on weblog entry was of the form update weblogentry e
set e.coulmname ... Etc postgres does not allow you to use the alias name on
the set column so I had to drop the e. from the update statement 

3) various update statements where clauses did not work because boolean
values were not properly quoted, changes were of the form column=1 to
column='1' 

I made the chages to the script to get to execute with psql without erros,
hopefuly there is nothing missing from the script.


Adib's version of 310-to-400-migration.sql for postgres 
---



create table rag_properties (
    name     varchar(255) not null primary key,
    value    text
);

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
update weblogentry as e set 
   pubtime=pubtime, 
   updatetime=updatetime, 
   locale=(select locale from website where website.id=e.websiteid) 
   where e.locale is null or length(e.locale)=0;

-- add new planet_id column to planet group table
    alter table rag_group add column planet_id varchar(48) default null;

-- 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','zzz_default_planet_zzz');
update rag_group set planet_id='zzz_default_planet_zzz';

-- remove old id column of group subscription table
alter table rag_group_subscription drop column id;

-- drop old planet config table
drop table if exists rag_config;


-- upgrade the way hierarchical objects are modeled

-- add new parentid column to weblogcategory table
    alter table weblogcategory add column parentid varchar(48) default null;
create index ws_parentid_idx on weblogcategory( parentid );

-- add new path column to weblogcategory table
    alter table weblogcategory add column path varchar(255) default null;
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
    alter table folder add column path varchar(255) default null;
create index fo_path_idx on folder( path );


-- update comment handling

-- add new fields to comment table to support CommentValidators
    alter table roller_comment add column referrer varchar(255) default
null;
    alter table roller_comment add column useragent varchar(255) default
null;

-- add new field to support comment plugins and content-type
    alter table roller_comment add column plugins varchar(255) default null;
    alter table roller_comment add column contenttype varchar(128);
    alter table roller_comment alter contenttype set default 'text/plain';
    update roller_comment set contenttype='text/plain';
    alter table roller_comment alter contenttype set not null;

-- add new status field to comment table to simplify queries
    alter table roller_comment add column status varchar(20);
    alter table roller_comment alter status set default 'APPROVED';
    update roller_comment set status='APPROVED';
    alter table roller_comment alter status set not null;

-- 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='1';
update roller_comment set status = 'PENDING', posttime=posttime where
pending='1';
update roller_comment set status = 'SPAM', posttime=posttime where spam='1';
update roller_comment set status = 'DISAPPROVED', posttime=posttime where
approved='0' and spam='0' and pending='0';


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

-- add new client column to roller_tasklock table
    alter table roller_tasklock add column client varchar(255) default null;

-- new column to support account activation by email
    alter table rolleruser add column activationcode varchar(48) default
null;

-- new column to support screen name and populate with user names
    alter table rolleruser add column screenname varchar(255);
    alter table rolleruser alter screenname set default 'unspecified';
    update rolleruser set screenname='unspecified';
    alter table rolleruser alter screenname set not null;
update rolleruser set screenname = username;

-- new column to allow setting of path to icon for website
    alter table website add column icon varchar(255) default null;

-- new column to allow setting of short website about text
    alter table website add column about varchar(255) default null;

-- new column to allow setting of page template content-type
    alter table webpage add column outputtype varchar(48) default null;

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

-- add new custom stylesheet column to website table
    alter table website add column customstylesheet varchar(128) default
null;

-- fix blogs which have unchecked showalllangs but did not check
enablemultilang
update website set enablemultilang = '1' where showalllangs = '0';


-- some missing foreign key constraints
alter table roller_user_permissions add constraint up_userid_fk
    foreign key ( user_id ) references rolleruser( id )  ;

alter table roller_user_permissions add constraint up_websiteid_fk
    foreign key ( website_id ) references website( id )  ;


-- 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
drop index we_pubtime_idx;
drop index we_pubentry_idx;
-- drop index co_pending_idx on roller_comment;
-- drop index co_approved_idx on roller_comment;

-- fix wacky indexs which ended up with a size constraint
drop index rage_sid_idx;
create index rage_sid_idx on rag_entry(subscription_id);
drop index raggs_gid_idx;
create index raggs_gid_idx on rag_group_subscription(group_id);
drop index raggs_sid_idx;
create index raggs_sid_idx on rag_group_subscription(subscription_id);


-- remove old usercookie table which has been unused since 0.x?
drop table if exists usercookie;

-- remove old assoc tables which were EOLed in 3.2
drop table if exists folderassoc;
drop table if exists weblogcategoryassoc;

-- remove old rollerconfig table which has been deprecated since 1.2
-- NOTE: since this breaks the pre-1.2 -> 4.0+ direct upgrade path then
--       maybe we want to attempt to fix that by doing that upgrade via sql?
drop table if exists rollerconfig;

-- remove old approved, spam, pending columns from comment table
alter table roller_comment drop column approved;
alter table roller_comment drop column spam;
alter table roller_comment drop column pending;

-- remove bastard columns from various tables
-- NOTE: these are only here as options, we don't *have* to do them
--alter table website drop column userid;
--alter table website drop column weblogdayid;
--alter table weblogentry drop column publishentry;
--alter table weblogentry drop column link;

Re: 4.0 RC1 Errors in postgres migration script from 3.1 to 4.0 corrected script attached

[Dave <sn...@gmail.com>]

On 8/16/07, Adib Saikali <ad...@programmingmastery.com> wrote:
> I downloaded the roller 4.0 rc1 and discovered that the upgrade scripts from
> roller 3.1 to 4.0 were not working, they had a few syntax errors.

Adib,

Thank you *very* much for your helpful feedback on Roller 4.0 RC1. I
will take a closer look at the issues you raised before I create RC2.

- Dave