Switch database 'start_time' to timestamp without time zone

See https://gitlab.ssec.wisc.edu/cspp_geo/geosphere/geosphere-deploy/-/issues/4

and geosphere-mapserver!3 (merged)

This includes a lot of new unit tests.

ci/test_tile_gen_exec.sh

+#!/usr/bin/env bash
+
+debug() {
+    >&2 echo "DEBUG: $@"
+}
+
+error() {
+    >&2 echo "ERROR: $@"
+}
+
+if [[ $# -ne 2 ]]; then
+    error "Usage: ./test_tile_gen_image.sh <image_url> <image_tag>"
+    exit 1
+fi
+
+image_url=$1
+image_tag=$2
+PG_PORT=5432
+PG_SERVER_NAME="postgres_db"
+RABBITMQ_SERVER_NAME="rabbitmq"
+RABBITMQ_USERNAME="guest"
+RABBITMQ_PASSWORD="guest"
+RABBITMQ_PORT="5672"
+POSTGRES_USER="postgres"
+POSTGRES_PASSWORD="1234"
+NETWORK_NAME=`basename "$0"`
+NETWORK_NAME=${NETWORK_NAME/.sh/}
+C01_GTIFF_NAME="GOES-16_ABI_RadF_C01_20220302_194032_GOES-West.tif"
+C01_ISOTIME="2022-03-02T19:40:32"
+C01_GTIFF_NAME2="GOES-16_ABI_RadF_C01_20220302_195032_GOES-West.tif"
+C01_ISOTIME2="2022-03-02T19:50:32"
+BASE_WORK_DIR=${BASE_WORK_DIR:-${TMPDIR:-/tmp}}
+base_tmp_dir="__TOBECREATED__"
+
+setup_test() {
+    base_tmp_dir=$(mktemp -d ${BASE_WORK_DIR}/tile-gen-tests-XXXXXXX)
+    cd "${base_tmp_dir}"
+    chmod 777 .
+    echo "Temporary directory: ${base_tmp_dir}"
+
+    docker network create ${NETWORK_NAME}
+}
+
+teardown_test() {
+    kill_test_container || error "Could not kill test container"
+    kill_postgres || error "Could not kill postgres container"
+    kill_rabbitmq || error "Could not kill rabbitmq container"
+    if [ -d $base_tmp_dir ]; then
+        rm -rf $base_tmp_dir || error "Couldn't delete temporary directory"
+    fi
+    docker network rm ${NETWORK_NAME} > /dev/null || error "Could not remove docker network"
+}
+
+graceful_exit() {
+    debug "FAIL"
+    debug "Graceful exit"
+    teardown_test
+}
+
+start_test_container() {
+    debug "Starting test docker container (${image_url}:${image_tag})..."
+    docker run --rm -d --network ${NETWORK_NAME} --name test -e RABBITMQ_HOST=${RABBITMQ_SERVER_NAME} -e RABBITMQ_USERNAME=${RABBITMQ_USERNAME} -e RABBITMQ_PASSWORD=${RABBITMQ_PASSWORD} $@ ${image_url}:${image_tag} ./run.sh
+    start_status=$?
+    # just wait a bit to let the server start
+    sleep 2
+    debug "Container started."
+    return $start_status
+}
+
+start_pg_test_container() {
+    mkdir pg_secrets
+    echo "${POSTGRES_PASSWORD}" > pg_secrets/fake_file
+    start_test_container -v "$(pwd)":"/data" -e POSTGRES_HOST=${PG_SERVER_NAME} -e POSTGRES_PORT=${PG_PORT} -e POSTGRES_PASSWORD_FILE="/data/pg_secrets/fake_file"
+}
+
+start_postgres() {
+    debug "Starting Postgres database..."
+    docker run --rm -d --network ${NETWORK_NAME} --name ${PG_SERVER_NAME} -e POSTGRES_PASSWORD="${POSTGRES_PASSWORD}" postgis/postgis
+    debug "Sleeping for 5 seconds for DB to start up..."
+    sleep 5
+}
+
+start_rabbitmq() {
+    debug "Starting rabbitmq database..."
+    docker run --rm -d --network ${NETWORK_NAME} --name ${RABBITMQ_SERVER_NAME} rabbitmq:management
+    create_status=$?
+    debug "Sleeping for 5 seconds for rabbitmq to start up..."
+    sleep 5
+    docker exec -i ${RABBITMQ_SERVER_NAME} rabbitmqadmin declare exchange name=satellite type=topic auto_delete=false durable=true internal=false
+    return $creation_status && $?
+}
+
+kill_test_container() {
+    debug "Killing docker container..."
+    debug "-----------------------------------------"
+    docker logs test
+    debug "-----------------------------------------"
+    docker kill test >/dev/null
+    debug "Done killing docker container."
+}
+
+kill_postgres() {
+    debug "Killing postgres container..."
+    docker kill ${PG_SERVER_NAME} >/dev/null
+    debug "Done killing postgres container."
+}
+
+kill_rabbitmq() {
+    debug "Killing rabbitmq container..."
+#     debug "-----------------------------------------"
+#     docker logs ${RABBITMQ_SERVER_NAME}
+#     debug "-----------------------------------------"
+    docker kill ${RABBITMQ_SERVER_NAME} >/dev/null
+    debug "Done killing rabbitmq container."
+}
+
+add_postgres_projections() {
+    # copied from tile gen
+    debug "Creating PostGIS projections"
+    docker exec -i ${PG_SERVER_NAME} psql -U ${POSTGRES_USER} <<EOF
+INSERT into spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text)
+values (930916, 'EPSG', 4269, 'PROJCRS["GOES-16 ABI Fixed Grid",BASEGEOGCRS["GOES-16 ABI Fixed Grid",DATUM["North American Datum 1983",ELLIPSOID["GRS 1980",6378137,298.257222101,LENGTHUNIT["metre",1]],ID["EPSG",6269]],PRIMEM["Greenwich",0,ANGLEUNIT["degree",0.0174532925199433],ID["EPSG",8901]]],CONVERSION["unknown",METHOD["Geostationary Satellite (Sweep X)"],PARAMETER["Longitude of natural origin",-75,ANGLEUNIT["degree",0.0174532925199433],ID["EPSG",8802]],PARAMETER["Satellite Height",35786023,LENGTHUNIT["metre",1,ID["EPSG",9001]]],PARAMETER["False easting",0,LENGTHUNIT["metre",1],ID["EPSG",8806]],PARAMETER["False northing",0,LENGTHUNIT["metre",1],ID["EPSG",8807]]],CS[Cartesian,2],AXIS["(E)",east,ORDER[1],LENGTHUNIT["metre",1,ID["EPSG",9001]]],AXIS["(N)",north,ORDER[2],LENGTHUNIT["metre",1,ID["EPSG",9001]]]]', '+proj=geos +sweep=x +lon_0=-75 +h=35786023 +x_0=0 +y_0=0 +datum=NAD83 +units=m +no_defs +type=crs') ON CONFLICT (srid) DO NOTHING
+EOF
+}
+
+add_postgres_legacy_tables() {
+    # Create tables with legacy CHAR(19) start_times instead of timestamp
+    debug "Creating PostGIS table"
+    docker exec -i ${PG_SERVER_NAME} psql -U ${POSTGRES_USER} <<EOF
+CREATE TABLE IF NOT EXISTS g16_abi_radf_l1b_c01 (
+    gid SERIAL PRIMARY KEY,
+    start_time CHAR(19) NOT NULL UNIQUE,
+    location VARCHAR(255) NOT NULL,
+    bbox_geometry geometry(POLYGON, 930916)
+    )
+EOF
+}
+
+add_postgres_modern_tables() {
+    # Create tables with legacy CHAR(19) start_times instead of timestamp
+    debug "Creating PostGIS table"
+    docker exec -i ${PG_SERVER_NAME} psql -U ${POSTGRES_USER} <<EOF
+CREATE TABLE IF NOT EXISTS g16_abi_radf_l1b_c01 (
+    gid SERIAL PRIMARY KEY,
+    start_time TIMESTAMP WITHOUT TIME ZONE NOT NULL UNIQUE,
+    location VARCHAR(255) NOT NULL,
+    bbox_geometry geometry(POLYGON, 930916)
+    )
+EOF
+}
+
+add_postgres_content() {
+    debug "Creating values for fake DB row for C01"
+    gtiff_location=$1
+    insert_values=$(docker run --rm -i --name fake_insert ${image_url}:${image_tag} python3 <<EOF
+from shapely.geometry import box
+from shapely import wkb
+
+dt_str = "${C01_ISOTIME}"
+location = "${gtiff_location}"
+bbox = box(-25000000, -25000000, 25000000, 25000000)
+bbox_wkb = wkb.dumps(bbox, hex=True, srid=930916)
+values = (dt_str, location, bbox_wkb)
+print(repr(values))
+EOF
+)
+    debug "Inserting fake DB row for C01"
+    docker exec -i ${PG_SERVER_NAME} psql -U ${POSTGRES_USER} <<EOF
+INSERT into g16_abi_radf_l1b_c01 (start_time, location, bbox_geometry)
+    VALUES ${insert_values} ON CONFLICT (start_time) DO NOTHING
+    RETURNING (start_time)
+EOF
+}
+
+create_fake_geotiff() {
+    fn=$1
+    debug "Creating fake geotiff: ${fn}"
+    bucket_name="g16_abi_radf_l1b_C01"
+    docker run --rm -i --name fake_gtiff -v "$(pwd)":"/data" ${image_url}:${image_tag} bash -c "mkdir -p /data/${bucket_name}; chmod -R a+rwX /data/${bucket_name}"
+    gtiff_fn="${bucket_name}/${fn}"
+    docker run --rm -i --name fake_gtiff -v "$(pwd)":"/data" ${image_url}:${image_tag} python3 <<EOF
+import rasterio
+from rasterio.transform import Affine
+import numpy as np
+
+transform = Affine.translation(-2500 * 1000 - 500, 2500 * 1000 - 500) * Affine.scale(1000, 1000)
+with rasterio.open("/data/${gtiff_fn}", "w", driver='GTiff', height=5000, width=5000, dtype=np.uint8, count=1,
+        crs="+proj=geos +sweep=x +lon_0=-75.0 +h=35786023 +x_0=0 +y_0=0 +ellps=GRS80 +units=m +no_defs", transform=transform) as gtiff_file:
+    data = np.repeat(np.linspace(0, 255, 5000, dtype=np.uint8)[None, :], 5000, axis=0)
+    gtiff_file.write(data, 1)
+EOF
+    creation_status=$?
+    echo ${gtiff_fn}
+    debug "Geotiff created"
+    return $creation_status
+}
+
+check_start_time_column_dtype() {
+    debug "Checking data type of start_time column"
+    res=$(docker exec -i ${PG_SERVER_NAME} psql -t -U ${POSTGRES_USER} <<EOF
+SELECT data_type FROM information_schema.columns WHERE table_name = 'g16_abi_radf_l1b_c01' AND column_name = 'start_time'
+EOF
+)
+    if [[ "$res" != *"timestamp"* ]]; then
+        error "Database table wasn't altered with timestamp data type. Data type is ${res}."
+        return 1
+    fi
+}
+
+check_num_records() {
+    debug "Checking number of records in the database"
+    res=$(docker exec -i ${PG_SERVER_NAME} psql -t -U ${POSTGRES_USER} <<EOF
+SELECT count(start_time) FROM g16_abi_radf_l1b_c01
+EOF
+)
+    if [[ $res -ne 2 ]]; then
+        error "Database didn't have expected 2 rows. Had ${res}."
+        return 1
+    fi
+    debug "Database had ${res} rows."
+}
+
+run_basic_existing_single_image() {
+    use_legacy_tables=$1  # 1 for True, 0 for False
+    setup_test
+    debug "Starting postgres single image tests (legacy tables=$use_legacy_tables)..."
+
+    start_rabbitmq
+    start_postgres
+    # add "existing" database content
+    add_postgres_projections
+    if [[ $use_legacy_tables -eq 1 ]]; then
+        add_postgres_legacy_tables
+    else
+        add_postgres_modern_tables
+    fi
+    gtiff_fn1="/data/$(create_fake_geotiff ${C01_GTIFF_NAME})"
+    add_postgres_content "${gtiff_fn1}"
+    start_pg_test_container
+
+    check_start_time_column_dtype
+
+    gtiff_fn2="/data/$(create_fake_geotiff ${C01_GTIFF_NAME2})"
+    debug "Submitting rabbitmq event for second geotiff"
+    gtiff_rel2=${gtiff_fn2/\/data\//}
+    docker exec -i ${RABBITMQ_SERVER_NAME} rabbitmqadmin publish exchange=satellite routing_key='data.goes.g16.abi.radf.l1b.geotiff.all.complete' payload="{\"satellite_family\": \"goes\", \"satellite_ID\": \"g16\", \"instrument\": \"abi\", \"data_type\": \"radf\", \"path\": \"${gtiff_rel2}\"}"
+    sleep 1  # give time for database entry
+    check_num_records
+
+    debug "SUCCESS: Single image test completed successfully"
+    teardown_test
+}
+
+run_basic_existing_single_image_legacy_tables() {
+    run_basic_existing_single_image 1
+}
+
+run_basic_existing_single_image_modern_tables() {
+    run_basic_existing_single_image 0
+}
+
+run_basic_two_new_images() {
+    setup_test
+    debug "Starting postgres two new images tests..."
+
+    start_rabbitmq
+    start_postgres
+    start_pg_test_container
+
+    gtiff_fn1="/data/$(create_fake_geotiff ${C01_GTIFF_NAME})"
+    gtiff_fn2="/data/$(create_fake_geotiff ${C01_GTIFF_NAME2})"
+
+    check_start_time_column_dtype
+
+    debug "Submitting rabbitmq event for second geotiff"
+    gtiff_rel1=${gtiff_fn1/\/data\//}
+    docker exec -i ${RABBITMQ_SERVER_NAME} rabbitmqadmin publish exchange=satellite routing_key='data.goes.g16.abi.radf.l1b.geotiff.all.complete' payload="{\"satellite_family\": \"goes\", \"satellite_ID\": \"g16\", \"instrument\": \"abi\", \"data_type\": \"radf\", \"path\": \"${gtiff_rel1}\"}"
+    gtiff_rel2=${gtiff_fn2/\/data\//}
+    docker exec -i ${RABBITMQ_SERVER_NAME} rabbitmqadmin publish exchange=satellite routing_key='data.goes.g16.abi.radf.l1b.geotiff.all.complete' payload="{\"satellite_family\": \"goes\", \"satellite_ID\": \"g16\", \"instrument\": \"abi\", \"data_type\": \"radf\", \"path\": \"${gtiff_rel2}\"}"
+    sleep 1  # give time for database entry
+    check_num_records
+
+    debug "SUCCESS: Two new images test completed successfully"
+    teardown_test
+}
+
+trap graceful_exit EXIT
+
+set -e
+run_basic_existing_single_image_legacy_tables
+echo "#######"
+run_basic_existing_single_image_modern_tables
+echo "#######"
+run_basic_two_new_images
+echo "#######"
+
+trap - EXIT  # tests should have cleared this already, otherwise produces extra output
+debug "SUCCESS"