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

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

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

        

[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