#** 
400-to-500-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 #concat("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 #concat("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 #concat("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);

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$!db.INDEXSIZE );
create index ua_attrname_idx  on roller_userattribute( attrname$!db.INDEXSIZE );
create index ua_attrvalue_idx on roller_userattribute( attrvalue$!db.INDEXSIZE );

create table media_file (
    id              varchar(48) not null primary key,
    name            varchar(255) not null,
    description     varchar(255),
    content_type    varchar(50)  not null,
    copyright_text  varchar(1023),
    directory_id    varchar(48), 
    size_in_bytes   integer,
    date_uploaded   $db.TIMESTAMP_SQL_TYPE not null,
    last_updated    $db.TIMESTAMP_SQL_TYPE,
    anchor          varchar(255),
    creator         varchar(255),
    is_public       $db.BOOLEAN_SQL_TYPE_FALSE not null
);

create table media_file_tag (
    id              varchar(48) not null primary key,
    media_file_id   varchar(48) not null,
    name            varchar(30) not null
);

create table media_file_directory (
    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 media_file add constraint media_file_directory_id_fk
    foreign key (directory_id) references media_file_directory(id) $!db.ADDL_FK_PARAMS ;

alter table media_file_tag add constraint media_file_id_tag_fk
    foreign key (media_file_id) references media_file(id) $!db.ADDL_FK_PARAMS ;

alter table media_file_directory add constraint mf_websiteid_fk
    foreign key ( websiteid ) references website( id ) $!db.ADDL_FK_PARAMS ;

alter table media_file_directory add constraint mf_parentid_fk
    foreign key ( parentid ) references media_file_directory( id )  $!db.ADDL_FK_PARAMS ;