Migrator Stops Migrating at "Adds Default Permissions"

[geirman]

Discussion started here: https://groups.google.com/forum/#!topic/cfwheels/r1i1P5qsGmo

For some reason, the Migrate to Latest fails, and fails silently. While troubleshooting, I narrowed it down to the "Adds Default Permissions" step. After reviewing the migration file, I noticed the following...

BUG: a counter is set at the top. Permissions are added, dependent on the auto-increment id. Then, the counter (not the id) is assumed to match the id when setting the matching rolepermissions.

Problem Example

c=1;
addRecord(table='permissions', name='admin', description='Global Administrative Access');
addRecord(table='rolepermissions', roleid=1, permissionid=c);
c++;

This works fine on a fresh install since the first auto-assigned id will match the value of c, but there's no guarantee that the first id assigned is 1. For instance, if you roll up/down a few times, the next first id would definitely not be 1.

To fix this problem, I propose we manually assign the id of each permission. This way it's a known value and we're not relying an anything circumstantial.

Solution Example

c=0;
addRecord(table='permissions', id=++c, name='admin', description='Global Administrative Access');
addRecord(table='rolepermissions', roleid=1, permissionid=c)

Please note that I still wasn't able to get the migration file to pass, but I'm sure that this fix is necessary.

Helper Function

With the updated code plus the following function, I was able to generate the MySQL insert statements below to manually update the database.

function addRecord(
    required string table
) {
    var columnNames = '';
    var columnValues = '';
    for(key in arguments) {
        if(key != 'table') {
            columnNames = ListAppend(columnNames,key);
            columnValues = ListAppend(columnValues,"'#ReplaceNoCase(arguments[key],"'","''","all")#'");
        }
    }
    writeOutput("INSERT INTO #table# (#columnNames#) VALUES (#columnValues#);");
}

SQL Code Generated from Helper Function

INSERT INTO permissions (id,name,description) VALUES ('1','admin','Global Administrative Access');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('1','1');
INSERT INTO permissions (id,name,description) VALUES ('2','admin.auditlogs','Allow Global Administrative Access to Logs');
INSERT INTO permissions (id,name,description) VALUES ('3','admin.auditlogs.index','View Logs');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','3');
INSERT INTO permissions (id,name,description) VALUES ('4','admin.auditlogs.show','Show Log Extended Data');
INSERT INTO permissions (id,name,description) VALUES ('5','admin.permissions','Allow Global Administrative Access to Permissions');
INSERT INTO permissions (id,name,description) VALUES ('6','admin.permissions.index','List Permissions');
INSERT INTO permissions (id,name,description) VALUES ('7','admin.permissions.edit','Edit Permission');
INSERT INTO permissions (id,name,description) VALUES ('8','admin.permissions.update','Update Permission');
INSERT INTO permissions (id,name,description) VALUES ('9','admin.settings','Allow Global Administrative Access to Settings');
INSERT INTO permissions (id,name,description) VALUES ('10','admin.settings.index','List Settings');
INSERT INTO permissions (id,name,description) VALUES ('11','admin.settings.edit','Edit Setting');
INSERT INTO permissions (id,name,description) VALUES ('12','admin.settings.update','Update Setting');
INSERT INTO permissions (id,name,description) VALUES ('13','admin.users','Allow Global Administrative Access to Users');
INSERT INTO permissions (id,name,description) VALUES ('14','admin.users.index','List Users');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','14');
INSERT INTO permissions (id,name,description) VALUES ('15','admin.users.new','New User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','15');
INSERT INTO permissions (id,name,description) VALUES ('16','admin.users.create','Create User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','16');
INSERT INTO permissions (id,name,description) VALUES ('17','admin.users.edit','Edit User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','17');
INSERT INTO permissions (id,name,description) VALUES ('18','admin.users.update','Update User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','18');
INSERT INTO permissions (id,name,description) VALUES ('19','admin.users.delete','Delete User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','19');
INSERT INTO permissions (id,name,description) VALUES ('20','admin.users.reset','Reset Users Password');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','20');
INSERT INTO permissions (id,name,description) VALUES ('21','admin.users.recover','Recover User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','21');
INSERT INTO permissions (id,name,description) VALUES ('22','admin.users.show','View User');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','22');
INSERT INTO permissions (id,name,description) VALUES ('23','admin.users.assume','Assume Users (Grant only to Admins)');
INSERT INTO permissions (id,name,description) VALUES ('24','admin.users.destroy','Destroy Users (Grant only to Admins)');
INSERT INTO permissions (id,name,description) VALUES ('25','admin.roles','Allow Global Administrative Access to Roles');
INSERT INTO permissions (id,name,description) VALUES ('26','admin.roles.index','List Roles');
INSERT INTO permissions (id,name,description) VALUES ('27','admin.roles.new','New Role');
INSERT INTO permissions (id,name,description) VALUES ('28','admin.roles.create','Create Role');
INSERT INTO permissions (id,name,description) VALUES ('29','admin.roles.edit','Edit Role');
INSERT INTO permissions (id,name,description) VALUES ('30','admin.roles.update','Update Role');
INSERT INTO permissions (id,name,description) VALUES ('31','admin.roles.delete','Delete Role');
INSERT INTO permissions (id,name,description) VALUES ('32','accounts','Allow Global Access to Own Profile');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('1','32');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('2','32');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('3','32');
INSERT INTO permissions (id,name,description) VALUES ('33','accounts.show','View My Account');
INSERT INTO permissions (id,name,description) VALUES ('34','accounts.edit','Edit Own Account');
INSERT INTO permissions (id,name,description) VALUES ('35','accounts.update','Update Own Account');
INSERT INTO permissions (id,name,type,description) VALUES ('36','canViewAdminNotes','named','Allow user to view admin notes');
INSERT INTO permissions (id,name,type,description) VALUES ('37','canViewLogData','named','Allow user to view extended log data');
INSERT INTO rolepermissions (roleid,permissionid) VALUES ('1','37');

[neokoenig]

Merged your PR - cheers!