#** 
400-to-410-migration.vm: Velocity template that generates vendor-specific database scripts 

DON'T RUN THIS, IT'S NOT A DATABASE CREATION SCRIPT!!!
**#

-- 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(48) not null primary key,
   username        varchar(255) not null,
   actions         varchar(255), 
   objectid        varchar(48),           
   objecttype      varchar(255),          
   pending         $db.BOOLEAN_SQL_TYPE_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', 0, 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', 0, 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', 0, 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

#dropNotNullFromColumn("userrole" "userid" "varchar(48)")

#dropNotNullFromColumn("website" "userid" "varchar(48)")

#addColumnNull("website" "creator" "varchar(255)")
update website as w set 
    lastmodified = lastmodified,
    datecreated = datecreated,
    creator = (select u.username from rolleruser as u where u.id = w.userid);

#dropNotNullFromColumn("weblogentry" "userid" "varchar(48)")
#addColumnNull("weblogentry" "creator" "varchar(255)")
update weblogentry as w set
    pubtime = pubtime,
    updatetime = updatetime, 
    creator = (select u.username from rolleruser as u where u.id = w.userid);

#dropNotNullFromColumn("roller_weblogentrytag" "userid" "varchar(48)")
#addColumnNull("roller_weblogentrytag" "creator" "varchar(255)")
update roller_weblogentrytag as w set 
    time = time,
    creator = (select u.username from rolleruser as u where u.id = w.userid);