dev-migration-20160503

line.sql

UPDATE planet_osm_line
  SET mz_landuse_min_zoom = mz_calculate_min_zoom_landuse(planet_osm_line.*)
  WHERE
    (highway IN ('pedestrian', 'footway') OR
     aeroway IN ('runway', 'taxiway') OR
     man_made IN ('pier', 'breakwater', 'groyne', 'dike', 'cutline'))
    AND COALESCE(mz_calculate_min_zoom_landuse(planet_osm_line.*), 999) <> COALESCE(mz_landuse_min_zoom, 999);

UPDATE planet_osm_line
  SET mz_road_level = mz_calculate_min_zoom_roads(planet_osm_line.*)
  WHERE
    highway IN ('footway', 'steps')
    AND COALESCE(mz_calculate_min_zoom_roads(planet_osm_line.*), 999) <> COALESCE(mz_road_level, 999);

UPDATE planet_osm_line
  SET mz_water_min_zoom = mz_calculate_min_zoom_water(planet_osm_line.*)
  WHERE
    waterway='dam'
    AND COALESCE(mz_calculate_min_zoom_water(planet_osm_line.*), 999) <> COALESCE(mz_water_min_zoom, 999);

point.sql

UPDATE planet_osm_point
  SET mz_water_min_zoom = mz_calculate_min_zoom_water(planet_osm_point.*)
  WHERE
    waterway='dam'
    AND COALESCE(mz_calculate_min_zoom_water(planet_osm_point.*), 999) <> COALESCE(mz_water_min_zoom, 999);

polygon.sql

UPDATE planet_osm_polygon
  SET mz_landuse_min_zoom = mz_calculate_min_zoom_landuse(planet_osm_polygon.*)
  WHERE
    (highway IN ('pedestrian', 'footway') OR
     aeroway IN ('runway', 'taxiway') OR
     man_made IN ('pier', 'breakwater', 'groyne', 'dike', 'cutline'))
    AND COALESCE(mz_calculate_min_zoom_landuse(planet_osm_polygon.*), 999) <> COALESCE(mz_landuse_min_zoom, 999);

UPDATE planet_osm_polygon
  SET mz_water_min_zoom = mz_calculate_min_zoom_water(planet_osm_polygon.*)
  WHERE
    waterway='dam'
    AND COALESCE(mz_calculate_min_zoom_water(planet_osm_polygon.*), 999) <> COALESCE(mz_water_min_zoom, 999);

run.sh

#!/bin/bash

# this is meant to run on the tileutility instance

migration_dir=${0%/*}
datasource_dir=/etc/tilequeue/vector-datasource

# first, run any "pre-function" migrations. these might be necessary if the
# migration alters tables to add columns referenced in the functions, in
# which case the function creation would fail.
for sql in ${migration_dir}/*.sql; do
    # break the loop if the file doesn't exist - this is generally the case
    # if the glob matches nothing and we end up looking for a file which is
    # called literally '*.sql'.
    [ -f $sql ] || break

    if [[ $sql = *prefunction*.sql ]]; then
        psql -f "$sql" $*
    else
        echo "SKIPPING $sql - this will be run after the functions."
    fi
done

# next run functions and triggers, bailing if either of these fail, as they
# are required by later steps.
psql --set ON_ERROR_STOP=1 -f "${datasource_dir}/data/functions.sql" $*
if [ $? -ne 0 ]; then echo "Installing new functions failed.">&2; exit 1; fi
python ${datasource_dir}/data/migrations/create-sql-functions.py | psql --set ON_ERROR_STOP=1 $*
if [ $? -ne 0 ]; then echo "Installing generated functions failed.">&2; exit 1; fi
psql --set ON_ERROR_STOP=1 -f "${datasource_dir}/data/triggers.sql" $*
if [ $? -ne 0 ]; then echo "Installing new triggers failed.">&2; exit 1; fi

# then disable triggers
for table in planet_osm_point planet_osm_line planet_osm_polygon planet_osm_rels; do
    psql -c "ALTER TABLE ${table} DISABLE TRIGGER USER" $*
done

# run updates in parallel. note that we don't bail here, as we want to
# re-enable the triggers regardless of whether we failed or not.
for sql in ${migration_dir}/*.sql; do
    # break the loop if the file doesn't exist - this is generally the case
    # if the glob matches nothing and we end up looking for a file which is
    # called literally '*.sql'.
    [ -f $sql ] || break

    if [[ $sql = *cleanup*.sql ]]; then
        echo "SKIPPING $sql - run this after the code migration."
    elif [[ $sql = *prefunction*.sql ]]; then
        echo "SKIPPING $sql - this was already run before the functions."
    else
        psql -f "$sql" $* &
    fi
done

wait

# run cleanup - we're in dev so we don't care about any potential errors
if [ -f ${migration_dir}/cleanup.sql ]; then
  psql -f ${migration_dir}/cleanup.sql $*
fi

# re-enable triggers
for table in planet_osm_point planet_osm_line planet_osm_polygon planet_osm_rels; do
    psql -c "ALTER TABLE ${table} ENABLE TRIGGER USER" $*
done

# re-generate the functions to avoid issues when a migration updates
# the schema
python ${datasource_dir}/data/migrations/create-sql-functions.py | psql --set ON_ERROR_STOP=1 $*
if [ $? -ne 0 ]; then echo "Installing generated functions second time failed.">&2; exit 1; fi

# analyze tables in case index updates influenced query plans
for table in planet_osm_point planet_osm_line planet_osm_polygon; do
    psql -c "ANALYZE ${table}" $* &
done
wait