dev-delta-migration-20160428

cleanup.sql

DROP FUNCTION IF EXISTS mz_is_path_named_or_designated(text, text, text, text, text, text, text);
DROP INDEX IF EXISTS planet_osm_line_natural_geom_index;

line.sql

UPDATE planet_osm_line
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(planet_osm_line.*)
  WHERE mz_calculate_min_zoom_earth(planet_osm_line.*) IS NOT NULL;

CREATE INDEX new_planet_osm_line_earth_geom_9_index  ON planet_osm_line USING gist(way) WHERE mz_earth_min_zoom <= 9;
CREATE INDEX new_planet_osm_line_earth_geom_12_index ON planet_osm_line USING gist(way) WHERE mz_earth_min_zoom <= 12;
CREATE INDEX new_planet_osm_line_earth_geom_15_index ON planet_osm_line USING gist(way) WHERE mz_earth_min_zoom <= 15;

BEGIN;

DROP INDEX IF EXISTS planet_osm_line_earth_geom_9_index;
DROP INDEX IF EXISTS planet_osm_line_earth_geom_12_index;
DROP INDEX IF EXISTS planet_osm_line_earth_geom_15_index;

ALTER INDEX new_planet_osm_line_earth_geom_9_index RENAME TO planet_osm_line_earth_geom_9_index;
ALTER INDEX new_planet_osm_line_earth_geom_12_index RENAME TO planet_osm_line_earth_geom_12_index;
ALTER INDEX new_planet_osm_line_earth_geom_15_index RENAME TO planet_osm_line_earth_geom_15_index;

COMMIT;

other.sql

UPDATE ne_110m_land
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(ne_110m_land.*)
  WHERE mz_calculate_min_zoom_earth(ne_110m_land.*) IS NOT NULL;

UPDATE ne_50m_land
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(ne_50m_land.*)
  WHERE mz_calculate_min_zoom_earth(ne_50m_land.*) IS NOT NULL;

UPDATE ne_10m_land
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(ne_10m_land.*)
  WHERE mz_calculate_min_zoom_earth(ne_10m_land.*) IS NOT NULL;

UPDATE land_polygons
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(land_polygons.*)
  WHERE mz_calculate_min_zoom_earth(land_polygons.*) IS NOT NULL;

UPDATE water_polygons SET mz_water_min_zoom = mz_calculate_min_zoom_water(water_polygons.*);

point.sql

UPDATE planet_osm_point
  SET mz_places_min_zoom = mz_calculate_min_zoom_places(planet_osm_point.*)
  WHERE
    place IN ('continent');

UPDATE planet_osm_point
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(planet_osm_point.*)
  WHERE mz_calculate_min_zoom_earth(planet_osm_point.*) IS NOT NULL;

CREATE INDEX new_planet_osm_point_min_zoom_earth_9_index  ON planet_osm_point USING gist(way) WHERE mz_earth_min_zoom <= 9;
CREATE INDEX new_planet_osm_point_min_zoom_earth_12_index ON planet_osm_point USING gist(way) WHERE mz_earth_min_zoom <= 12;
CREATE INDEX new_planet_osm_point_min_zoom_earth_15_index ON planet_osm_point USING gist(way) WHERE mz_earth_min_zoom <= 15;

BEGIN;

DROP INDEX IF EXISTS planet_osm_point_min_zoom_earth_9_index;
DROP INDEX IF EXISTS planet_osm_point_min_zoom_earth_12_index;
DROP INDEX IF EXISTS planet_osm_point_min_zoom_earth_15_index;

ALTER INDEX new_planet_osm_point_min_zoom_earth_9_index RENAME TO planet_osm_point_min_zoom_earth_9_index;
ALTER INDEX new_planet_osm_point_min_zoom_earth_12_index RENAME TO planet_osm_point_min_zoom_earth_12_index;
ALTER INDEX new_planet_osm_point_min_zoom_earth_15_index RENAME TO planet_osm_point_min_zoom_earth_15_index;

COMMIT;

polygon.sql

UPDATE planet_osm_polygon
  SET mz_earth_min_zoom = mz_calculate_min_zoom_earth(planet_osm_polygon.*)
  WHERE mz_calculate_min_zoom_earth(planet_osm_polygon.*) IS NOT NULL;

UPDATE planet_osm_polygon
  SET mz_poi_min_zoom = mz_calculate_min_zoom_pois(planet_osm_polygon.*)
  WHERE COALESCE(mz_poi_min_zoom, 999) <> COALESCE(mz_calculate_min_zoom_pois(planet_osm_polygon.*), 999);

CREATE INDEX new_planet_osm_polygon_earth_geom_9_index ON planet_osm_polygon USING gist(way) WHERE mz_earth_min_zoom <= 9;
CREATE INDEX new_planet_osm_polygon_earth_geom_12_index ON planet_osm_polygon USING gist(way) WHERE mz_earth_min_zoom <= 12;
CREATE INDEX new_planet_osm_polygon_earth_geom_15_index ON planet_osm_polygon USING gist(way) WHERE mz_earth_min_zoom <= 15;

BEGIN;

DROP INDEX IF EXISTS planet_osm_polygon_earth_geom_9_index;
DROP INDEX IF EXISTS planet_osm_polygon_earth_geom_12_index;
DROP INDEX IF EXISTS planet_osm_polygon_earth_geom_15_index;

ALTER INDEX new_planet_osm_polygon_earth_geom_9_index RENAME TO planet_osm_polygon_earth_geom_9_index;
ALTER INDEX new_planet_osm_polygon_earth_geom_12_index RENAME TO planet_osm_polygon_earth_geom_12_index;
ALTER INDEX new_planet_osm_polygon_earth_geom_15_index RENAME TO planet_osm_polygon_earth_geom_15_index;

COMMIT;

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; do
  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

schema-prefunction.sql

CREATE OR REPLACE function tmp_add_col(_tbl regclass, _col text)
RETURNS integer AS $$
BEGIN
 IF NOT EXISTS (
   SELECT 1 FROM pg_attribute
   WHERE attrelid = _tbl
   AND attname = _col
   AND NOT attisdropped) THEN

   EXECUTE format('ALTER TABLE %s ADD COLUMN %s SMALLINT', _tbl, _col);
 END IF;
 RETURN 1;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN

PERFORM tmp_add_col('public.planet_osm_polygon', 'mz_earth_min_zoom');
PERFORM tmp_add_col('public.planet_osm_line',    'mz_earth_min_zoom');
PERFORM tmp_add_col('public.planet_osm_point',   'mz_earth_min_zoom');

PERFORM tmp_add_col('public.ne_10m_land',  'mz_earth_min_zoom');
PERFORM tmp_add_col('public.ne_50m_land',  'mz_earth_min_zoom');
PERFORM tmp_add_col('public.ne_110m_land', 'mz_earth_min_zoom');

PERFORM tmp_add_col('public.land_polygons',      'mz_earth_min_zoom');

PERFORM tmp_add_col('public.planet_osm_point', 'mz_earth_min_zoom');
PERFORM tmp_add_col('public.planet_osm_polygon', 'mz_earth_min_zoom');
PERFORM tmp_add_col('public.planet_osm_line', 'mz_earth_min_zoom');

END$$;

DROP FUNCTION tmp_add_col(regclass, text);