<?php
$res = array();
$res[] = tableCreate('role', "
roleid int(10) unsigned NOT NULL AUTO_INCREMENT,
rolename varchar(200) NOT NULL,
builtin bool NOT NULL DEFAULT '0',
roledescription TEXT,
PRIMARY KEY (roleid)
");
if (tableExists('user_x_role')) {
if (tableExists('role_x_user')) {
Database::exec('DROP TABLE user_x_role');
} else {
$res[] = tableRename('user_x_role', 'role_x_user');
}
}
$res[] = tableCreate('role_x_user', "
userid int(10) unsigned NOT NULL,
roleid int(10) unsigned NOT NULL,
PRIMARY KEY (userid, roleid)
");
$res[] = tableCreate('role_x_location', "
roleid int(10) unsigned NOT NULL,
locationid int(11),
CONSTRAINT role_loc UNIQUE (roleid, locationid)
");
$res[] = tableCreate('role_x_permission', "
roleid int(10) unsigned NOT NULL,
permissionid varchar(100) NOT NULL,
PRIMARY KEY (roleid, permissionid)
");
if (tableHasColumn('role_x_location', 'id')) {
$cnt = Database::exec('DELETE a FROM role_x_location a, role_x_location b
WHERE a.roleid = b.roleid AND (a.locationid = b.locationid OR (a.locationid IS NULL AND b.locationid IS NULL))
AND a.id > b.id');
$ret = Database::exec('ALTER TABLE role_x_location DROP COLUMN id,
ADD CONSTRAINT role_loc UNIQUE (roleid, locationid)');
if ($ret === false) {
$res[] = UPDATE_NOOP;
} else {
$res[] = UPDATE_DONE;
}
}
if (!tableExists('user') || !tableExists('location')) {
finalResponse(UPDATE_RETRY, 'Cannot add constraint yet. Please retry.');
} else {
$c = tableGetConstraints('role_x_user', 'userid', 'user', 'userid');
if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add userid constraint referencing user table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
$c = tableGetConstraints('role_x_user', 'roleid', 'role', 'roleid');
if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
$c = tableGetConstraints('role_x_location', 'roleid', 'role', 'roleid');
if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
$c = tableGetConstraints('role_x_location', 'locationid', 'location', 'locationid');
if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (locationid) REFERENCES location (locationid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add locationid constraint referencing location table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
$c = tableGetConstraints('role_x_permission', 'roleid', 'role', 'roleid');
if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_permission ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
}
// 2018-04-13 role description field; add a couple default roles
if (!tableHasColumn('role', 'roledescription')) {
$alter = Database::exec("ALTER TABLE role ADD roledescription TEXT");
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roledescription field to table role: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
// 2020-01-09 flag for builtin roles that can't be edited
if (!tableHasColumn('role', 'builtin')) {
$alter = Database::exec("ALTER TABLE role ADD builtin bool NOT NULL DEFAULT '0' AFTER rolename");
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add builtin field to table role: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
// 2022-07-06 permissionid too long for older mariadb versions
if (stripos(tableColumnType('role_x_permission', 'permissionid'), 'varchar(200)') !== false) {
$alter = Database::exec("ALTER TABLE role_x_permission MODIFY permissionid varchar(100) NOT NULL");
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot shorten permissionid to 100: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
if (Database::exec("INSERT INTO `role` (roleid, rolename, builtin, roledescription) VALUES
(1,'Super-Admin', 1, 'Hat keinerlei Zugriffsbeschränkungen'),
(2,'Admin', 1, 'Alles bis auf Rechte-/Nutzerverwaltung'),
(3,'Prüfungsadmin', 1, 'Kann E-Prüfungen verwalten, Prüfungsmodus einschalten, etc.'),
(4,'Lesezugriff', 1, 'Kann auf die meisten Seiten zugreifen, jedoch keine Änderungen vornehmen')
ON DUPLICATE KEY UPDATE rolename = VALUES(rolename), builtin = 1, roledescription = VALUES(roledescription)") !== false) {
// Assign roles to location (all)
Database::exec("DELETE FROM role_x_location WHERE roleid IN (1,2,3,4)");
Database::exec("INSERT INTO `role_x_location` VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL)");
// In case user fiddled around before
Database::exec("DELETE FROM role_x_permission WHERE roleid IN (1,2,3,4)");
// Assign permissions to roles
Database::exec("INSERT IGNORE INTO `role_x_permission` VALUES
-- Exams Admin
(3,'exams.exams.*'),
(3,'locations.location.view'),
(3,'rebootcontrol.action.*'),
(3,'statistics.hardware.projectors.view'),
(3,'statistics.hints'),
(3,'statistics.machine.note.*'),
(3,'statistics.machine.view-details'),
(3,'statistics.view.*'),
(3,'syslog.view'),
-- Super Admin
(1,'*'),
-- Read only
(4,'adduser.user.view-list'),
(4,'baseconfig.view'),
(4,'dnbd3.access-page'),
(4,'dnbd3.view.details'),
(4,'dozmod.actionlog.view'),
(4,'dozmod.users.view'),
(4,'eventlog.filter.rules.view'),
(4,'eventlog.view'),
(4,'exams.exams.view'),
(4,'locationinfo.backend.check'),
(4,'locationinfo.panel.list'),
(4,'locations.location.view'),
(4,'minilinux.view'),
(4,'news.access-page'),
(4,'passthrough.view'),
(4,'permissionmanager.locations.view'),
(4,'permissionmanager.roles.view'),
(4,'permissionmanager.users.view'),
(4,'remoteaccess.view'),
(4,'runmode.list-all'),
(4,'serversetup.access-page'),
(4,'serversetup.download'),
(4,'statistics.hardware.projectors.view'),
(4,'statistics.hints'),
(4,'statistics.machine.note.view'),
(4,'statistics.machine.view-details'),
(4,'statistics.view.*'),
(4,'statistics_reporting.reporting.download'),
(4,'statistics_reporting.table.export'),
(4,'statistics_reporting.table.view.*'),
(4,'sysconfig.config.view-list'),
(4,'sysconfig.module.download'),
(4,'sysconfig.module.view-list'),
(4,'syslog.view'),
(4,'systemstatus.show.overview.*'),
(4,'systemstatus.tab.*'),
(4,'webinterface.access-page'),
(4,'rebootcontrol.subnet.view'),
(4,'rebootcontrol.jumphost.view'),
-- Admin
(2,'adduser.user.view-list'),
(2,'backup.*'),
(2,'baseconfig.*'),
(2,'dnbd3.*'),
(2,'dozmod.*'),
(2,'eventlog.*'),
(2,'exams.exams.*'),
(2,'locationinfo.*'),
(2,'locations.*'),
(2,'minilinux.*'),
(2,'news.*'),
(4,'passthrough.*'),
(2,'permissionmanager.locations.view'),
(2,'permissionmanager.roles.view'),
(2,'permissionmanager.users.view'),
(2,'rebootcontrol.*'),
(2,'remoteaccess.*'),
(2,'roomplanner.edit'),
(2,'runmode.list-all'),
(2,'serversetup.*'),
(2,'statistics.*'),
(2,'statistics_reporting.*'),
(2,'sysconfig.*'),
(2,'syslog.*'),
(2,'systemstatus.*'),
(2,'vmstore.*'),
(2,'webinterface.*')");
Database::exec("OPTIMIZE TABLE role_x_permission");
// Assign the first user to the superadmin role (if one exists)
$num = Database::exec("INSERT IGNORE INTO `role_x_user` VALUES (1,1)");
if ($num > 0) {
$res[] = UPDATE_DONE;
}
}
//
//
if (in_array(UPDATE_DONE, $res)) {
finalResponse(UPDATE_DONE, 'Tables created successfully');
}
finalResponse(UPDATE_NOOP, 'Everything already up to date');
