#!/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/helpers.sh . $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