[Roller-JIRA] Resolved: (ROL-1563) Errors in postgres migration script from 3.1 to 4.0

["David Johnson (JIRA)" <no...@atlassian.com>]

     [ http://opensource.atlassian.com/projects/roller/browse/ROL-1563?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Johnson resolved ROL-1563.
--------------------------------

    Resolution: Fixed

See commit: http://svn.apache.org/viewvc?rev=574968&view=rev

1) Postgres doesn't like drop index on syntax
- Added Postgres to special cases in #dropIndex() macro

2) Postgres doesn't allow you to use alias name the set column of update
- Added special case for Postgres so alias is not used in that update

3) Boolean values were not properly quoted
- Use $db.BOOLEAN_FALSE and $db.BOOLEAN_TRUE to ensure that Posgtres gets true and false instead of 1 and 0.

> Errors in postgres migration script from 3.1 to 4.0
> ---------------------------------------------------
>
>                 Key: ROL-1563
>                 URL: http://opensource.atlassian.com/projects/roller/browse/ROL-1563
>             Project: Roller
>          Issue Type: Bug
>            Reporter: David Johnson
>            Assignee: David Johnson
>             Fix For: 4.0
>
>
> (From Adib Saikali)
> 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;

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/roller/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira