patch.sh

#!/bin/bash
#
# ** header v3.0
# This file is a part of the CaosDB Project.
#
# Copyright (C) 2018 Research Group Biomedical Physics,
# Max-Planck-Institute for Dynamics and Self-Organization Göttingen
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
#
# ** end header
#
# tables for new user accounts and roles
# Update mysql schema to version v2.0.15
NEW_VERSION="v2.0.15"
OLD_VERSION="v2.0.14"

if [ -z "$UTILSPATH" ]; then
 UTILSPATH="../utils"
fi

. $UTILSPATH/patch_header.sh $*

CREATE_SUBJECTS_TABLE="CREATE TABLE subjects ( name VARBINARY(255) NOT NULL PRIMARY KEY, email VARBINARY(255) DEFAULT NULL, status ENUM('ACTIVE','REGISTER', 'INACTIVE', 'RESET') NOT NULL DEFAULT 'INACTIVE') ENGINE=InnoDB;"
CREATE_ROLES_TABLE="CREATE TABLE roles ( name VARBINARY(255) NOT NULL PRIMARY KEY, description MEDIUMTEXT NULL) ENGINE=InnoDB;"
CREATE_PERMISSIONS_TABLE='CREATE TABLE permissions ( role VARBINARY(255) NOT NULL PRIMARY KEY, permissions MEDIUMTEXT NOT NULL, CONSTRAINT `perm_name_roles` FOREIGN KEY (`role`) REFERENCES `roles` (`name`)) ENGINE=InnoDB;'
CREATE_MEMBERSHIP_TABLE='CREATE TABLE membership ( subject VARBINARY(255) NOT NULL, role VARBINARY(255), UNIQUE KEY(subject, role), CONSTRAINT `mem_name_roles` FOREIGN KEY (`role`) REFERENCES `roles` (`name`)) ENGINE=InnoDB;'
CREATE_PASSWORDS_TABLE='CREATE TABLE passwd ( principal VARBINARY(255) NOT NULL PRIMARY KEY, hash VARBINARY(255) NOT NULL, alg VARCHAR(255) DEFAULT "SHA-512", it INT UNSIGNED DEFAULT 5000, salt VARBINARY(255) NOT NULL ) ENGINE=InnoDB;'

ADD_TRANSACTOR_COLUMN='ALTER TABLE transaction_log ADD COLUMN transactor VARBINARY(255) NOT NULL'
MODIFY_USERID_COLUMN='ALTER TABLE transaction_log MODIFY COLUMN user_id INT UNSIGNED NULL'
UPDATE_TRANSACTION_LOG_TABLE='UPDATE transaction_log SET transactor=(SELECT name FROM entities WHERE id=user_id)'
UPDATE_SUBJECTS_TABLE='INSERT INTO subjects (name, status) SELECT name as name, "ACTIVE" as status FROM entities WHERE role="USER" and name!="USER";'
UPDATE_PASSWD_TABLE='INSERT INTO passwd (principal, hash, it, salt) SELECT e.name as principal, p.password as hash, 0 as it, "" as salt FROM passwords AS p JOIN entities as e ON (p.entity_id = e.id);'

UPDATE_ROLES_TABLE='INSERT INTO roles (name, description) SELECT name as name, description as description FROM entities WHERE role="GROUP" and name!="GROUP"'
UPDATE_MEMBERSHIP_TABLE='INSERT INTO membership SELECT (SELECT name FROM entities WHERE id=user_id) as user, (SELECT name FROM entities WHERE id=group_id) as ugroup FROM groups'
UPDATE_PERM_TABLE='INSERT INTO permissions (role,permissions) VALUES ("administration","*");'

DROP_TABLE_PASSWORDS='DROP TABLE passwords;'
DROP_TABLE_GROUPS='DROP TABLE groups;'

DELETE_OLD_USERS='DELETE FROM entities WHERE role="USER" or role="GROUP";'

check_version $OLD_VERSION

mysql_execute "$CREATE_SUBJECTS_TABLE"
mysql_execute "$CREATE_ROLES_TABLE"
mysql_execute "$CREATE_PERMISSIONS_TABLE"
mysql_execute "$CREATE_MEMBERSHIP_TABLE"
mysql_execute "$CREATE_PASSWORDS_TABLE"

mysql_execute "$ADD_TRANSACTOR_COLUMN"
mysql_execute "$MODIFY_USERID_COLUMN"
mysql_execute "$UPDATE_TRANSACTION_LOG_TABLE"

mysql_execute "$UPDATE_SUBJECTS_TABLE"
mysql_execute "$UPDATE_ROLES_TABLE"
mysql_execute "$UPDATE_MEMBERSHIP_TABLE"
mysql_execute "$UPDATE_PASSWD_TABLE"
mysql_execute "$UPDATE_PERM_TABLE"

mysql_execute "$DROP_TABLE_PASSWORDS"
mysql_execute "$DROP_TABLE_GROUPS"
mysql_execute "$DELETE_OLD_USERS"

update_version $NEW_VERSION

success