Skip to content

Instantly share code, notes, and snippets.

@rmarianski

rmarianski/cleanup.sql

Created April 28, 2016 16:54
Show Gist options
  • Save rmarianski/7cf4c594374e7d75bde10f372d141302 to your computer and use it in GitHub Desktop.
Save rmarianski/7cf4c594374e7d75bde10f372d141302 to your computer and use it in GitHub Desktop.
Download ZIP
dev-delta-migration-20160428
Raw
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;
Raw
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;
Raw
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.*);
Raw
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;
Raw
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;
Raw
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
Raw
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment