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
#
# add null_data table
# Update mysql schema to version v2.0.19
NEW_VERSION="v2.0.19"
OLD_VERSION="v2.0.18"

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

. $UTILSPATH/patch_header.sh $*


function add_null_data_table {
	mysql_execute 'CREATE TABLE IF NOT EXISTS null_data (
		domain_id INT UNSIGNED,
		entity_id INT UNSIGNED,
		property_id INT UNSIGNED,
		status ENUM("OBLIGATORY","RECOMMENDED","SUGGESTED","FIX"),
		pidx INT UNSIGNED NOT NULL DEFAULT 0,
		INDEX `null_data_dom_ent_idx` (domain_id,entity_id),
		FOREIGN KEY `null_forkey_dom` (`domain_id`) REFERENCES `entities` (`id`),
		FOREIGN KEY `null_forkey_ent` (`entity_id`) REFERENCES `entities` (`id`),
		FOREIGN KEY `null_forkey_pro` (`property_id`) REFERENCES `entities` (`id`)
	) ENGINE=InnoDB;'
}

function move_null_data_from {
	mysql_execute "INSERT INTO null_data (domain_id, entity_id, property_id, status, pidx) SELECT domain_id, entity_id, property_id, status, pidx FROM $1 WHERE value IS NULL;"
	remove_null_data $1
	modify_value_column $1 "$2"
} 

function remove_null_data {
	mysql_execute "DELETE FROM $1 WHERE value IS NULL;"
}

function modify_value_column {
	stmt="SET FOREIGN_KEY_CHECKS=0; ALTER TABLE $1 MODIFY COLUMN value $2 NOT NULL; SET FOREIGN_KEY_CHECKS=1;"
	mysql_execute "$stmt" 
}

check_version $OLD_VERSION

add_null_data_table
move_null_data_from "text_data" "TEXT" 
move_null_data_from "date_data" "INT"
move_null_data_from "datetime_data" "BIGINT"
move_null_data_from "integer_data" "BIGINT"
move_null_data_from "enum_data" 'VARBINARY(255)'
move_null_data_from "double_data" "DOUBLE"
move_null_data_from "reference_data" "INT UNSIGNED"



update_version $NEW_VERSION

success