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

@@ -27,6 +27,8 @@ 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__"
 
@@ -40,13 +42,13 @@ setup_test() {
 }
 
 teardown_test() {
-    kill_test_container || echo "Could not kill test container"
-    kill_postgres || echo "Could not kill postgres container"
-    kill_rabbitmq || echo "Could not kill rabbitmq container"
+    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
+        rm -rf $base_tmp_dir || error "Couldn't delete temporary directory"
     fi
-    docker network rm ${NETWORK_NAME} > /dev/null || echo "Could not remove docker network"
+    docker network rm ${NETWORK_NAME} > /dev/null || error "Could not remove docker network"
 }
 
 graceful_exit() {
@@ -134,6 +136,19 @@ CREATE TABLE IF NOT EXISTS g16_abi_radf_l1b_c01 (
 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
@@ -158,9 +173,11 @@ 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}/${C01_GTIFF_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
@@ -174,10 +191,12 @@ with rasterio.open("/data/${gtiff_fn}", "w", driver='GTiff', height=5000, width=
 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
@@ -188,29 +207,91 @@ EOF
     fi
 }
 
-run_basic_single_image() {
+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 tests..."
+    debug "Starting postgres single image tests (legacy tables=$use_legacy_tables)..."
 
     start_rabbitmq
     start_postgres
     # add "existing" database content
     add_postgres_projections
-    add_postgres_legacy_tables
-    gtiff_fn="/data/$(create_fake_geotiff)"
-    add_postgres_content "${gtiff_fn}"
+    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_single_image
+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