Re: migration script for postgresql

Thanks for letting us know, I entered a JIRA ticket for them: 
https://issues.apache.org/jira/browse/ROL-1969.  For future matters 
similar to these it would be good if you could enter the JIRA ticket 
directly.

Regards,
Glen

On 08/14/2013 03:16 AM, omid p wrote:
> the migration script for postgresql has some bugs e.g boolean in insert
> query should be false not 0. I've fixed it
>
>
>
>
> -- NEW PERMISSIONS SYSTEM
>
> -- We are replacing roller_user_permissions with this new table
> -- actions:    comma separated list of actions permitted by permission
> -- objectid:   for now this will always store weblogid
> -- objectType: for now this will always be 'Weblog'
> create table roller_permission (
>     id              varchar(120) not null primary key,
>     username        varchar(255) not null,
>     actions         varchar(255),
>     objectid        varchar(120),
>     objecttype      varchar(255),
>     pending         boolean default true,
>     datecreated     timestamp not null
> );
>
> insert into roller_permission
> (id,username,actions,objectid,objecttype,pending,datecreated)
>     select   w.id || u.username  , u.username, 'edit_draft', w.handle,
> 'Weblog', false, current_timestamp
>        from rolleruser as u, website as w, roller_user_permissions as p
>        where p.user_id = u.id and p.website_id = w.id and permission_mask =
> 1;
>
> insert into roller_permission
> (id,username,actions,objectid,objecttype,pending,datecreated)
>     select   w.id || u.username  , u.username, 'author', w.handle, 'Weblog',
> false, current_timestamp
>        from rolleruser as u, website as w, roller_user_permissions as p
>        where p.user_id = u.id and p.website_id = w.id and permission_mask =
> 2;
>
> insert into roller_permission
> (id,username,actions,objectid,objecttype,pending,datecreated)
>     select   w.id || u.username  , u.username, 'admin', w.handle, 'Weblog',
> false, current_timestamp
>        from rolleruser as u, website as w, roller_user_permissions as p
>        where p.user_id = u.id and p.website_id = w.id and permission_mask =
> 3;
>
> -- User management can now be exernalized, so no more relations with user
> table
>
>      alter table userrole  alter column userid drop not null;
>
>      alter table website  alter column userid drop not null;
>
>      alter table website add column creator varchar(255) default null;
> update website as w set
>      lastmodified = lastmodified,
>      datecreated = datecreated,
>      creator = (select u.username from rolleruser as u where u.id =
> w.userid);
>
>      alter table weblogentry  alter column userid drop not null;
>      alter table weblogentry add column creator varchar(255) default null;
> update weblogentry as w set
>      pubtime = pubtime,
>      updatetime = updatetime,
>      creator = (select u.username from rolleruser as u where u.id =
> w.userid);
>
>      alter table roller_weblogentrytag  alter column userid drop not null;
>      alter table roller_weblogentrytag add column creator varchar(255)
> default null;
> update roller_weblogentrytag as w set
>      time = time,
>      creator = (select u.username from rolleruser as u where u.id =
> w.userid);
>
>
> -- USER ATTRIBUTE
>
> create table  roller_userattribute(
>      id        varchar(48) not null primary key,
>      username  varchar(255) not null,
>      attrname  varchar(255) not null,
>      attrvalue varchar(255) not null
> );
> create index ua_username_idx  on roller_userattribute( username );
> create index ua_attrname_idx  on roller_userattribute( attrname );
> create index ua_attrvalue_idx on roller_userattribute( attrvalue );
>
>
> -- OAUTH SUPPORT
>
> -- each record is an OAuth consumer key and secret, can be tied to just one
> user
> create table roller_oauthconsumer (
>      consumerkey    varchar(48) not null primary key,
>      consumersecret varchar(48) not null,
>      username       varchar(48)
> );
> create index oc_username_idx  on roller_oauthconsumer( username );
> create index oc_consumerkey_idx  on roller_oauthconsumer( consumerkey );
>
> -- each record is an OAuth accessor, always tied to just one user
> create table roller_oauthaccessor (
>      consumerkey  varchar(48) not null primary key,
>      requesttoken varchar(48),
>      accesstoken  varchar(48),
>      tokensecret  varchar(48),
>      created      timestamp(2) with time zone not null,
>      updated      timestamp(2) with time zone not null,
>      username     varchar(48),
>      authorized   boolean default false
> );
>
>
> -- MEDIA BLOGGING
>
> create table roller_mediafile (
>      id              varchar(48) not null primary key,
>      name            varchar(255) not null,
>      description     varchar(255),
>      origpath        varchar(255),
>      content_type    varchar(50)  not null,
>      copyright_text  varchar(1023),
>      directoryid     varchar(48) not null,
>      weblogid        varchar(48) not null,
>      size_in_bytes   integer,
>      width           integer,
>      height          integer,
>      date_uploaded   timestamp(2) with time zone not null,
>      last_updated    timestamp(2) with time zone,
>      anchor          varchar(255),
>      creator         varchar(255),
>      is_public       boolean default false not null
> );
>
> create table roller_mediafiletag (
>      id              varchar(48) not null primary key,
>      mediafile_id    varchar(48) not null,
>      name            varchar(30) not null
> );
>
> create table roller_mediafiledir (
>      id               varchar(48) not null primary key,
>      name             varchar(255) not null,
>      description      varchar(255),
>      websiteid        varchar(48) not null,
>      parentid         varchar(48),
>      path             varchar(255)
> );
>
> -- media files
> alter table roller_mediafile add constraint roller_mediafiledir_id_fk
>      foreign key (directoryid) references roller_mediafiledir(id)  ;
>
> alter table roller_mediafiletag add constraint roller_mediafile_id_tag_fk
>      foreign key (mediafile_id) references roller_mediafile(id)  ;
>
> alter table roller_mediafiledir add constraint mf_websiteid_fk
>      foreign key ( websiteid ) references website( id )  ;
>
> alter table roller_mediafiledir add constraint mf_parentid_fk
>      foreign key ( parentid ) references roller_mediafiledir( id )   ;
>
>
> -- Fix for https://issues.apache.org/jira/browse/ROL-1760
>
> alter table roller_tasklock alter timeacquired type timestamp(2) with time
> zone;
> alter table roller_tasklock alter lastrun type timestamp(2) with time zone;
>