#** 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 #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, 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 ;