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
#
# implements new datetime data model
# Update mysql schema to version v2.0.16
NEW_VERSION="v2.0.16"
OLD_VERSION="v2.0.15"

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

. $UTILSPATH/patch_header.sh $*

ADD_SECONDS_COLUMN='ALTER TABLE transaction_log ADD COLUMN seconds BIGINT UNSIGNED NOT NULL DEFAULT 0;'
ADD_NANOS_COLUMN='ALTER TABLE transaction_log ADD COLUMN nanos INT UNSIGNED NOT NULL DEFAULT 0;'
DELETE_OLD_COLUMNS='ALTER TABLE transaction_log DROP COLUMN date; ALTER TABLE transaction_log DROP COLUMN time; ALTER TABLE transaction_log DROP COLUMN ns;'
DELETE_USER_ID_COLUMN='ALTER TABLE transaction_log DROP COLUMN user_id;'

function convert_datetime_to_secs {
    leap_secs=('1972-06-30' '1972-12-31' '1973-12-31' '1974-12-31' '1975-12-31' '1976-12-31' '1977-12-31' '1978-12-31' '1979-12-31' '1981-06-30' '1982-06-30' '1983-06-30' '1985-06-30' '1987-12-31' '1989-12-31' '1990-12-31' '1992-06-30' '1993-06-30' '1994-06-30' '1995-12-31' '1997-06-30' '1998-12-31' '2005-12-31' '2008-12-31' '2012-06-30' '2015-06-30', '2016-12-31')
	
	# update seconds table
	mysql_execute 'UPDATE transaction_log set seconds = unix_timestamp(CONCAT(date(date), " ",  time(time-1-100-10000)));'

	# remove extra nanosecond
	mysql_execute 'UPDATE transaction_log set nanos = ns-1 WHERE ns > 0;'

    # add leap second correction
    for l in "${leap_secs[@]}"; do
        mysql_execute "UPDATE transaction_log SET seconds=seconds+1 WHERE seconds>unix_timestamp('$l 23:59:59');"
    done
}


check_version $OLD_VERSION

dump_table 'transaction_log'

mysql_execute "$ADD_SECONDS_COLUMN"
mysql_execute "$ADD_NANOS_COLUMN"

convert_datetime_to_secs

# clean up
mysql_execute "$DELETE_OLD_COLUMNS"
TRY=$(mysql_execute "$DELETE_USER_ID_COLUMN" 2>&1)

update_version $NEW_VERSION

success