From 00cc48cbc17c8e422eacd5fa8a3fedc39a729221 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Sun, 12 Nov 2023 14:46:34 -0600 Subject: [PATCH] Convert data into the tidy schema As of now, this means cleaning up the data types of the "raw" schema and checking that this does not discard any data. --- conversion/Makefile | 36 ++++- conversion/copy_schema.sh | 60 +++++++ conversion/createschemas.sql | 12 ++ conversion/libconv/checkdate.sql | 44 ++++++ conversion/libconv/checktime.sql | 44 ++++++ conversion/make_tidy.sh | 39 +++++ conversion/tidy.sql | 263 +++++++++++++++++++++++++++++++ 7 files changed, 497 insertions(+), 1 deletion(-) create mode 100755 conversion/copy_schema.sh create mode 100644 conversion/libconv/checkdate.sql create mode 100644 conversion/libconv/checktime.sql create mode 100755 conversion/make_tidy.sh create mode 100644 conversion/tidy.sql diff --git a/conversion/Makefile b/conversion/Makefile index 5e40611..1f09838 100644 --- a/conversion/Makefile +++ b/conversion/Makefile @@ -72,8 +72,15 @@ PSQL_SETUP: $(GENERIC_DEPENDS) # CONVERSION_SCHEMA := raw +LIBCONV_SCHEMA = libconv +TIDY_SCHEMA := tidy PSQL_RAW_SETUP := $(PSQL_SETUP) \ printf 'SET search_path TO $(CONVERSION_SCHEMA);\n' ; +PSQL_LIBCONV_SETUP := $(PSQL_SETUP) \ + printf 'SET search_path TO $(LIBCONV_SCHEMA);\n' ; +PSQL_TIDY_SETUP := $(PSQL_SETUP) \ + printf 'SET search_path TO %s,%s;\n' \ + $(TIDY_SCHEMA) $(LIBCONV_SCHEMA) ; ## ## The available targets for make (make TARGET) are: @@ -83,7 +90,7 @@ PSQL_RAW_SETUP := $(PSQL_SETUP) \ ## CONVERSION_SCHEMA and convert the data, storing ## the converted data in the TARGET_DB. .PHONY: convert -convert: create-schema restore grant_permissions +convert: create-schema restore grant_permissions copy_tidy make_tidy ## clean Delete all user-generated files .PHONY: clean @@ -120,3 +127,30 @@ grant_permissions: $(PSQL_DEPENDS) grant_permissions.sql ( $(PSQL_RAW_SETUP) \ cat grant_permissions.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y + +## libconv Load the libconv schema with utility code +.PHONY: libconv +libconv: $(PSQL_DEPENDS) + ( $(PSQL_LIBCONV_SETUP) \ + printf 'DROP SCHEMA IF EXISTS libconv CASCADE;\n' ; \ + printf 'CREATE SCHEMA libconv;\n' ; \ + cat libconv/*.sql ; ) \ + | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y + +## copy_tidy Copy the raw schema into the tidy schema +.PHONY: copy_tidy +copy_tidy: $(PSQL_DEPENDS) + # Copy the raw schema to the tidy schema + PSQL_SETUP="$(PSQL_SETUP)" \ + PSQL_ARGS="$(PSQL_ARGS)" \ + PSQL_ARGS_MINIMAL="$(PSQL_ARGS_MINIMAL)" \ + PSQL_SINGLE_TRANS="$(PSQL_SINGLE_TRANS)" \ + ./copy_schema.sh raw tidy + +## make_tidy Clean up the tidy schema +.PHONY: make_tidy +make_tidy: $(PSQL_DEPENDS) libconv + PSQL_TIDY_SETUP="$(PSQL_TIDY_SETUP)" \ + PSQL_ARGS="$(PSQL_ARGS)" \ + PSQL_SINGLE_TRANS="$(PSQL_SINGLE_TRANS)" \ + ./make_tidy.sh diff --git a/conversion/copy_schema.sh b/conversion/copy_schema.sh new file mode 100755 index 0000000..8078231 --- /dev/null +++ b/conversion/copy_schema.sh @@ -0,0 +1,60 @@ +#!/bin/sh +# Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# 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 . +# +# Copy a schema. +# +# Syntax: copy_schema.sh OLD NEW +# +# OLD The name of the schema to copy +# NEW The name of the schema to copy into +# +# Relies on the following environment variables: +# PSQL_SETUP +# PSQL_ARGS +# PSQL_ARGS_MINIMAL +# PSQL_SINGLE_TRANS +# +# Karl O. Pinc +# + +OLD=$1 +NEW=$2 + +export dump_file=$(mktemp) + +cleanup () { + rm -rf ${dump_file} +} + +trap cleanup EXIT + +# Rename the OLD to the NEW +{ ${PSQL_SETUP} ; + printf 'drop schema if exists %s cascade;\n' "${NEW}" ; + printf 'alter schema %s rename to %s;\n' "${OLD}" "${NEW}" ; +} | psql ${PSQL_ARGS} ${PSQL_SINGLE_TRANS} --set=ON_ERROR_STOP=y + +# Dump the NEW +pg_dump ${PSQL_ARGS_MINIMAL} --blobs --schema=tidy -Fc --no-owner \ + > ${dump_file} + +# Rename the NEW to the OLD, restoring it +{ ${PSQL_SETUP} ; + printf 'alter schema %s rename to %s;\n' "${NEW}" "${OLD}" ; + } | psql ${PSQL_ARGS} ${PSQL_SINGLE_TRANS} --set=ON_ERROR_STOP=y + +# Restore the NEW +pg_restore ${PSQL_ARGS_MINIMAL} < ${dump_file} diff --git a/conversion/createschemas.sql b/conversion/createschemas.sql index af44f3e..fb50e8f 100644 --- a/conversion/createschemas.sql +++ b/conversion/createschemas.sql @@ -16,5 +16,17 @@ -- Karl O. Pinc +# raw: Raw data, right out of the MS Access dump CREATE SCHEMA IF NOT EXISTS raw; GRANT USAGE ON SCHEMA raw TO reader, writer; + +# libconv: Utility code for the conversion process +CREATE SCHEMA IF NOT EXISTS libconv; + +# tidy: Usability improvements -- better data types, maybe indexes +CREATE SCHEMA IF NOT EXISTS tidy; +GRANT USAGE ON SCHEMA tidy TO reader, writer; + +# easy: Like "tidy", but with table names that don't need quoting +CREATE SCHEMA IF NOT EXISTS easy; +GRANT USAGE ON SCHEMA easy TO reader, writer; diff --git a/conversion/libconv/checkdate.sql b/conversion/libconv/checkdate.sql new file mode 100644 index 0000000..d63a822 --- /dev/null +++ b/conversion/libconv/checkdate.sql @@ -0,0 +1,44 @@ +-- Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +-- +-- 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 . +-- +-- Function that checks the date part of a timestamp is '1899-12-30'. +-- +-- Karl O. Pinc + +CREATE OR REPLACE FUNCTION checkdate (tbl TEXT, col TEXT) + RETURNS void + LANGUAGE plpgsql + AS $$ + DECLARE + qry TEXT; + rows BIGINT; + problem RECORD; + BEGIN + qry := ' + SELECT * + FROM ' || quote_ident(tbl) || ' + WHERE ' || quote_ident(col) || '::DATE <> $date$1899-12-30$date$'; + EXECUTE qry INTO problem; + GET DIAGNOSTICS rows := ROW_COUNT; + IF rows <> 0 THEN + RAISE EXCEPTION data_exception USING + MESSAGE = tbl + || ' has a date part in column ' + || col + || ' that is not 1899-12-30' + , DETAIL = 'The row is: ' || problem; + END IF; + END; +$$; diff --git a/conversion/libconv/checktime.sql b/conversion/libconv/checktime.sql new file mode 100644 index 0000000..580d306 --- /dev/null +++ b/conversion/libconv/checktime.sql @@ -0,0 +1,44 @@ +-- Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +-- +-- 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 . +-- +-- Function that checks the time part of a timestamp is '00:00:00'. +-- +-- Karl O. Pinc + +CREATE OR REPLACE FUNCTION checktime (tbl TEXT, col TEXT) + RETURNS void + LANGUAGE plpgsql + AS $$ + DECLARE + qry TEXT; + rows BIGINT; + problem RECORD; + BEGIN + qry := ' + SELECT * + FROM ' || quote_ident(tbl) || ' + WHERE ' || quote_ident(col) || '::TIME <> $time$00:00:00$time$'; + EXECUTE qry INTO problem; + GET DIAGNOSTICS rows := ROW_COUNT; + IF rows <> 0 THEN + RAISE EXCEPTION data_exception USING + MESSAGE = tbl + || ' has a time part in column ' + || col + || ' that is not 00:00:00' + , DETAIL = 'The row is: ' || problem; + END IF; + END; +$$; diff --git a/conversion/make_tidy.sh b/conversion/make_tidy.sh new file mode 100755 index 0000000..797f7e9 --- /dev/null +++ b/conversion/make_tidy.sh @@ -0,0 +1,39 @@ +#!/bin/bash +# Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# 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 . +# +# Tidy up the tidy schema +# +# Syntax: make_tidy.sh OLD NEW +# +# Relies on the following environment variables: +# PSQL_TIDY_SETUP +# PSQL_ARGS +# PSQL_SINGLE_TRANS +# +# Karl O. Pinc +# +# Remarks: +# Exists so we can run bash and set pipefile, instead of /bin/sh. +# This gets us a good exit code when psql gets an error. +# + +# Adjust the tidy schema so it's tidy +set -o pipefail +{ eval "${PSQL_TIDY_SETUP}" ; + ./speedups.sh ; + cat tidy.sql ; +} | psql ${PSQL_ARGS} ${PSQL_SINGLE_TRANS} --set=ON_ERROR_STOP=y \ + | { ! grep -v '^[[:space:]]*$' ; } diff --git a/conversion/tidy.sql b/conversion/tidy.sql new file mode 100644 index 0000000..494b675 --- /dev/null +++ b/conversion/tidy.sql @@ -0,0 +1,263 @@ +-- Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +-- +-- 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 . +-- +-- Copy the tables in the "raw" schema into the "tidy" schema, +-- fixing schema and db setup issues. +-- +-- Karl O. Pinc + +SELECT checkdate('ADJ_TIME', 'true_time'); +SELECT checkdate('ADJ_TIME', 'adj_time'); +ALTER TABLE "ADJ_TIME" + ALTER true_time TYPE TIME WITHOUT TIME ZONE, + ALTER adj_time TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('AGGRESSION_EVENT', 'AE_date'); +SELECT checkdate('AGGRESSION_EVENT', 'AE_time'); +ALTER TABLE "AGGRESSION_EVENT" + ALTER "AE_date" TYPE DATE, + ALTER "AE_time" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('ATTENDANCE', 'A_date'); +SELECT checkdate('ATTENDANCE', 'A_time_start'); +SELECT checkdate('ATTENDANCE', 'A_time_end'); +ALTER TABLE "ATTENDANCE" + ALTER "A_date" TYPE DATE, + ALTER "A_time_start" TYPE TIME WITHOUT TIME ZONE, + ALTER "A_time_end" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('BIOGRAPHY', 'B_Birthdate'); +SELECT checktime('BIOGRAPHY', 'B_BDMin'); +SELECT checktime('BIOGRAPHY', 'B_BDMax'); +SELECT checktime('BIOGRAPHY', 'B_Entrydate'); +SELECT checktime('BIOGRAPHY', 'B_Departdate'); +ALTER TABLE "BIOGRAPHY" + ALTER "B_Birthdate" TYPE DATE, + ALTER "B_BDMin" TYPE DATE, + ALTER "B_BDMax" TYPE DATE, + ALTER "B_Entrydate" TYPE DATE, + ALTER "B_Departdate" TYPE DATE; + +SELECT checktime('BIOGRAPHY_UPDATE_LOG', 'date_of_update'); +ALTER TABLE "BIOGRAPHY_UPDATE_LOG" + ALTER date_of_update TYPE DATE; + +SELECT checktime('BRECORD_NOTES', 'BREC_FOL_date'); +SELECT checkdate('BRECORD_NOTES', 'BREC_time'); +SELECT checktime('BRECORD_NOTES', 'BREC_updated_on'); +ALTER TABLE "BRECORD_NOTES" + ALTER "BREC_FOL_date" TYPE DATE, + ALTER "BREC_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "BREC_updated_on" TYPE DATE; + +SELECT checktime('CHIMPS_ON_TIKIS_LOOKUP', 'tiki_creation_date'); +SELECT checktime('CHIMPS_ON_TIKIS_LOOKUP', 'tiki_deployment_date'); +ALTER TABLE "CHIMPS_ON_TIKIS_LOOKUP" + ALTER tiki_creation_date TYPE DATE, + ALTER tiki_deployment_date TYPE DATE; + +SELECT checktime('COLOBUS', 'COL_encounter_date'); +SELECT checkdate('COLOBUS', 'COL_begin_time'); +SELECT checkdate('COLOBUS', 'COL_begin_time_map'); +SELECT checkdate('COLOBUS', 'COL_end_time'); +ALTER TABLE "COLOBUS" + ALTER "COL_encounter_date" TYPE DATE, + ALTER "COL_begin_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "COL_begin_time_map" TYPE TIME WITHOUT TIME ZONE, + ALTER "COL_end_time" TYPE TIME WITHOUT TIME ZONE; + +-- ALTER TABLE "COMMUNITY_LOOKUP" RENAME TO COMMUNITY_LOOKUP; + +SELECT checktime('COMMUNITY_MEMBERSHIP', 'CM_start_date'); +SELECT checktime('COMMUNITY_MEMBERSHIP', 'CM_end_date'); +ALTER TABLE "COMMUNITY_MEMBERSHIP" + ALTER "CM_start_date" TYPE DATE, + ALTER "CM_end_date" TYPE DATE; + +SELECT checktime('COMMUNITY_MEMBERSHIP_UPDATE_LOG', 'date_of_update'); +ALTER TABLE "COMMUNITY_MEMBERSHIP_UPDATE_LOG" + ALTER date_of_update TYPE DATE; + +SELECT checktime('ELO RANKS DAILY - FEMALES - old', 'Date'); +ALTER TABLE "ELO RANKS DAILY - FEMALES - old" + ALTER "Date" TYPE DATE; + +SELECT checktime('ELO RANKS DAILY - KK FEMALES', 'Date'); +ALTER TABLE "ELO RANKS DAILY - KK FEMALES" + ALTER "Date" TYPE DATE; + +SELECT checktime('ELO RANKS DAILY - KK MALES', 'Date'); +ALTER TABLE "ELO RANKS DAILY - KK MALES" + ALTER "Date" TYPE DATE; + +SELECT checktime('ELO RANKS DAILY - MALES - old', 'Date'); +ALTER TABLE "ELO RANKS DAILY - MALES - old" + ALTER "Date" TYPE DATE; + +SELECT checktime('ELO RANKS DAILY - MT FEMALES', 'Date'); +ALTER TABLE "ELO RANKS DAILY - MT FEMALES" + ALTER "Date" TYPE DATE; + +SELECT checktime('FEMALE REPRODUCTIVE STATES', 'date'); +ALTER TABLE "FEMALE REPRODUCTIVE STATES" + ALTER date TYPE DATE; + +SELECT checktime('FEMALE_REPRO_HISTORY', 'DATE'); +SELECT checktime('FEMALE_REPRO_HISTORY', 'conception_date'); +ALTER TABLE "FEMALE_REPRO_HISTORY" + ALTER "DATE" TYPE DATE, + ALTER conception_date TYPE DATE; + +SELECT checktime('FERTILITY', 'StartDate'); +SELECT checktime('FERTILITY', 'StopDate'); +ALTER TABLE "FERTILITY" + ALTER "StartDate" TYPE DATE, + ALTER "StopDate" TYPE DATE; + +SELECT checktime('FOLLOW', 'FOL_date'); +SELECT checkdate('FOLLOW', 'FOL_time_begin'); +SELECT checkdate('FOLLOW', 'FOL_time_end'); +SELECT checktime('FOLLOW', 'Update'); +ALTER TABLE "FOLLOW" + ALTER "FOL_date" TYPE DATE, + ALTER "FOL_time_begin" TYPE TIME WITHOUT TIME ZONE, + ALTER "FOL_time_end" TYPE TIME WITHOUT TIME ZONE, + ALTER "Update" TYPE DATE; + +SELECT checktime('FOLLOW_ARRIVAL', 'FA_FOL_date'); +SELECT checkdate('FOLLOW_ARRIVAL', 'FA_time_start'); +SELECT checkdate('FOLLOW_ARRIVAL', 'FA_time_end'); +SELECT checktime('FOLLOW_ARRIVAL', 'FA_update'); +ALTER TABLE "FOLLOW_ARRIVAL" + ALTER "FA_FOL_date" TYPE DATE, + ALTER "FA_time_start" TYPE TIME WITHOUT TIME ZONE, + ALTER "FA_time_end" TYPE TIME WITHOUT TIME ZONE, + ALTER "FA_update" TYPE DATE; + +SELECT checktime('FOLLOW_MAP_LOCATION', 'FML_FOL_date'); +SELECT checkdate('FOLLOW_MAP_LOCATION', 'FML_time'); +SELECT checktime('FOLLOW_MAP_LOCATION', 'FML_update'); +ALTER TABLE "FOLLOW_MAP_LOCATION" + ALTER "FML_FOL_date" TYPE DATE, + ALTER "FML_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "FML_update" TYPE DATE; + +SELECT checktime('FOLLOW_MAP_POSITION', 'FMP_FOL_date'); +ALTER TABLE "FOLLOW_MAP_POSITION" + ALTER "FMP_FOL_date" TYPE DATE; + +SELECT checktime('FOLLOW_MAP_TIME', 'FMT_FOL_date'); +SELECT checkdate('FOLLOW_MAP_TIME', 'FMT_time'); +SELECT checktime('FOLLOW_MAP_TIME', 'FMT_update'); +ALTER TABLE "FOLLOW_MAP_TIME" + ALTER "FMT_FOL_date" TYPE DATE, + ALTER "FMT_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "FMT_update" TYPE DATE; + +SELECT checktime('FOLLOW_MAP_TIME_PROBLEMS', 'FMTP_Date'); +SELECT checkdate('FOLLOW_MAP_TIME_PROBLEMS', 'map_time'); +ALTER TABLE "FOLLOW_MAP_TIME_PROBLEMS" + ALTER "FMTP_Date" TYPE DATE, + ALTER map_time TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('FOOD_BOUT', 'FB_FOL_date'); +SELECT checkdate('FOOD_BOUT', 'FB_begin_feed_time'); +SELECT checkdate('FOOD_BOUT', 'FB_end_feed_time'); +SELECT checktime('FOOD_BOUT', 'FB_update'); +ALTER TABLE "FOOD_BOUT" + ALTER "FB_FOL_date" TYPE DATE, + ALTER "FB_begin_feed_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "FB_end_feed_time" TYPE TIME WITHOUT TIME ZONE, + ALTER "FB_update" TYPE DATE; + +-- ALTER TABLE "FOOD_LOOKUP" RENAME TO FOOD_LOOKUP; +-- ALTER TABLE "FOOD_PART_LOOKUP" RENAME TO FOOD_PART_LOOKUP; +-- ALTER TABLE "FOOD_VARIATIONS_LOOKUP" RENAME TO FOOD_VARIATIONS_LOOKUP; + +SELECT checktime('GROOM_BOUT', 'GRM_FOL_date'); +SELECT checkdate('GROOM_BOUT', 'GRM_time_begin'); +SELECT checkdate('GROOM_BOUT', 'GRM_time_end'); +SELECT checktime('GROOM_BOUT', 'DATE_UPDATED'); +ALTER TABLE "GROOM_BOUT" + ALTER "GRM_FOL_date" TYPE DATE, + ALTER "GRM_time_begin" TYPE TIME WITHOUT TIME ZONE, + ALTER "GRM_time_end" TYPE TIME WITHOUT TIME ZONE, + ALTER "DATE_UPDATED" TYPE DATE; + +SELECT checktime('GROOM_SCAN_AREC', 'Date'); +SELECT checkdate('GROOM_SCAN_AREC', 'TIME'); +ALTER TABLE "GROOM_SCAN_AREC" + ALTER "Date" TYPE DATE, + ALTER "TIME" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('GROOM_SCANS', 'GS_date'); +SELECT checkdate('GROOM_SCANS', 'GS_time'); +ALTER TABLE "GROOM_SCANS" + ALTER "GS_date" TYPE DATE, + ALTER "GS_time" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('GROUP_COMP_INPUT', 'DATE'); +SELECT checkdate('GROUP_COMP_INPUT', 'TIME'); +ALTER TABLE "GROUP_COMP_INPUT" + ALTER "DATE" TYPE DATE, + ALTER "TIME" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('MATING_EVENT', 'M_FOL_date'); +SELECT checkdate('MATING_EVENT', 'M_time'); +ALTER TABLE "MATING_EVENT" + ALTER "M_FOL_date" TYPE DATE, + ALTER "M_time" TYPE TIME WITHOUT TIME ZONE; + +SELECT checktime('OTHER_SPECIES', 'OS_FOL_date'); +SELECT checkdate('OTHER_SPECIES', 'OS_time_begin'); +SELECT checkdate('OTHER_SPECIES', 'OS_time_end'); +SELECT checktime('OTHER_SPECIES', 'OS_update'); +ALTER TABLE "OTHER_SPECIES" + ALTER "OS_FOL_date" TYPE DATE, + ALTER "OS_time_begin" TYPE TIME WITHOUT TIME ZONE, + ALTER "OS_time_end" TYPE TIME WITHOUT TIME ZONE, + ALTER "OS_update" TYPE DATE; + +-- ALTER TABLE "OTHER_SPECIES_LOOKUP" RENAME TO OTHER_SPECIES_LOOKUP; + +SELECT checktime('PANTGRUNT_EVENT', 'pg_date'); +SELECT checkdate('PANTGRUNT_EVENT', 'pg_time'); +ALTER TABLE "PANTGRUNT_EVENT" + ALTER "pg_date" TYPE DATE, + ALTER "pg_time" TYPE TIME WITHOUT TIME ZONE; + +-- ALTER TABLE "PANTGRUNT_EVENT_LOG" RENAME TO PANTGRUNT_EVENT_LOG; + +SELECT checktime('SIGHTINGS', 'S_date'); +ALTER TABLE "SIGHTINGS" + ALTER "S_date" TYPE DATE; + +SELECT checktime('SIV_STATUS_BOUT', 'SSB_start_date'); +SELECT checktime('SIV_STATUS_BOUT', 'SSB_end_date'); +SELECT checktime('SIV_STATUS_BOUT', 'SSB_first_test_date'); +SELECT checktime('SIV_STATUS_BOUT', 'SSB_last_test_date'); +ALTER TABLE "SIV_STATUS_BOUT" + ALTER "SSB_start_date" TYPE DATE, + ALTER "SSB_end_date" TYPE DATE, + ALTER "SSB_first_test_date" TYPE DATE, + ALTER "SSB_last_test_date" TYPE DATE; + +-- ALTER TABLE "SUBADULT_ARRIVALS_LOG" RENAME TO SUBADULT_ARRIVALS_LOG; + +SELECT checktime('tbl_adjusted ages for alone arrivals', 'S_Date'); +ALTER TABLE "tbl_adjusted ages for alone arrivals" + ALTER "S_Date" TYPE DATE; + +-- There is also "tlk_brecord_notes_codes", which does not need alteration. -- 2.34.1