From 9936c19597b5125c70d9892a5c60fafeaeb30cce Mon Sep 17 00:00:00 2001 From: Paul Ramsey Date: Fri, 13 Nov 2009 22:26:00 +0000 Subject: [PATCH] Create version-specific upgrade scripts, untested so far (#202) git-svn-id: http://svn.osgeo.org/postgis/branches/1.4@4805 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis/Makefile.in | 12 +- postgis/postgis.sql.in.c | 41 ++++++- postgis/sqldefines.h.in | 2 +- utils/postgis_proc_upgrade.pl | 222 +++++++++++++++++++++++++++------- 4 files changed, 229 insertions(+), 48 deletions(-) diff --git a/postgis/Makefile.in b/postgis/Makefile.in index 382a1398e..fd47bb342 100644 --- a/postgis/Makefile.in +++ b/postgis/Makefile.in @@ -13,7 +13,7 @@ MODULE_big=postgis-@POSTGIS_MAJOR_VERSION@.@POSTGIS_MINOR_VERSION@ # Files to be copied to the contrib/ directory -DATA_built=postgis.sql uninstall_postgis.sql postgis_upgrade.sql +DATA_built=postgis.sql uninstall_postgis.sql postgis_upgrade_14_minor.sql postgis_upgrade_13_to_14.sql postgis_upgrade_12_to_14.sql DATA=../spatial_ref_sys.sql # SQL objects (files requiring C pre-processing) @@ -75,8 +75,14 @@ include $(PGXS) %.sql: %.sql.in sed 's,MODULE_PATHNAME,$$libdir/$*-@POSTGIS_MAJOR_VERSION@.@POSTGIS_MINOR_VERSION@,g' $< >$@ -postgis_upgrade.sql: postgis.sql - $(PERL) ../utils/postgis_proc_upgrade.pl $< > $@ +postgis_upgrade_14_minor.sql: postgis.sql + $(PERL) ../utils/postgis_proc_upgrade.pl $< 1.4 > $@ + +postgis_upgrade_13_to_14.sql: postgis.sql + $(PERL) ../utils/postgis_proc_upgrade.pl $< 1.3 > $@ + +postgis_upgrade_12_to_14.sql: postgis.sql + $(PERL) ../utils/postgis_proc_upgrade.pl $< 1.2 > $@ # Generate any .sql.in files from .sql.in.c files by running them through the C pre-processor $(SQL_OBJS): %.in: %.in.c diff --git a/postgis/postgis.sql.in.c b/postgis/postgis.sql.in.c index f441a385e..5aaa17cc0 100644 --- a/postgis/postgis.sql.in.c +++ b/postgis/postgis.sql.in.c @@ -21,8 +21,11 @@ #include "sqldefines.h" +-- INSTALL VERSION: POSTGIS_LIB_VERSION + BEGIN; + ------------------------------------------------------------------- -- SPHEROID TYPE ------------------------------------------------------------------- @@ -6250,5 +6253,41 @@ CREATE OR REPLACE FUNCTION ST_MinimumBoundingCircle(geometry) RETURNS geometry AS 'SELECT ST_MinimumBoundingCircle($1, 48)' LANGUAGE 'sql' IMMUTABLE STRICT; -COMMIT; + +------------------------------------------------------------------- +-- DROPPED FUNCTIONS FROM OLD VERSIONS +------------------------------------------------------------------- + +DROP FUNCTION IF EXISTS asgml(geometry, integer, integer); +DROP FUNCTION IF EXISTS asukml(geometry, integer, integer); +DROP FUNCTION IF EXISTS asukml(geometry, integer); +DROP FUNCTION IF EXISTS asukml(geometry); +DROP FUNCTION IF EXISTS create_histogram2d(box2d, integer); +DROP FUNCTION IF EXISTS dump(geometry); +DROP FUNCTION IF EXISTS jtsnoop(geometry); +DROP FUNCTION IF EXISTS postgis_jts_version(); +DROP FUNCTION IF EXISTS st_pointn(geometry); +DROP FUNCTION IF EXISTS st_create_histogram2d(box2d,integer); + +-- +-- Problem: can't conditionally drop functions that have arguments +-- with types that don't exist. Makes cleaning up old databases harder. +-- +-- DROP TYPE IF EXISTS histogram2d CASCADE; +-- DROP FUNCTION IF EXISTS estimate_histogram2d(histogram2d, box2d); +-- DROP FUNCTION IF EXISTS explode_histogram2d(histogram2d, text); +-- DROP FUNCTION IF EXISTS build_histogram2d(histogram2d, text, text); +-- DROP FUNCTION IF EXISTS build_histogram2d(histogram2d, text, text, text); +-- DROP FUNCTION IF EXISTS st_build_histogram2d(histogram2d, text, text, text); +-- DROP FUNCTION IF EXISTS st_estimate_histogram2d(histogram2d,box2d); +-- DROP FUNCTION IF EXISTS st_explode_histogram2d(histogram2d,text); +-- DROP FUNCTION IF EXISTS st_build_histogram2d(histogram2d,text,text); +-- DROP FUNCTION IF EXISTS st_histogram2d_out(histogram2d); +-- DROP FUNCTION IF EXISTS st_histogram2d_in(cstring); +-- DROP FUNCTION IF EXISTS histogram2d_out(histogram2d); +-- DROP FUNCTION IF EXISTS histogram2d_in(cstring); + +------------------------------------------------------------------- + +COMMIT; diff --git a/postgis/sqldefines.h.in b/postgis/sqldefines.h.in index 81dbd0791..0a8b46a7e 100644 --- a/postgis/sqldefines.h.in +++ b/postgis/sqldefines.h.in @@ -7,7 +7,7 @@ #define POSTGIS_PGSQL_VERSION @POSTGIS_PGSQL_VERSION@ #define POSTGIS_GEOS_VERSION @POSTGIS_GEOS_VERSION@ #define POSTGIS_PROJ_VERSION @POSTGIS_PROJ_VERSION@ - +#define POSTGIS_LIB_VERSION @POSTGIS_LIB_VERSION@ /* * Define the build date and the version number * (these substitiutions are done with extra quotes sinces CPP diff --git a/utils/postgis_proc_upgrade.pl b/utils/postgis_proc_upgrade.pl index c50648c85..dbd9e274b 100755 --- a/utils/postgis_proc_upgrade.pl +++ b/utils/postgis_proc_upgrade.pl @@ -3,7 +3,7 @@ # # This script produces an .sql file containing # CREATE OR REPLACE calls for each function -# in lwpostgis.sql +# in postgis.sql # # In addition, the transaction contains # a check for Major postgis_lib_version() @@ -20,99 +20,235 @@ eval "exec perl -w $0 $@" use strict; -($#ARGV == 0) || -die "Usage: perl postgis_proc_upgrade.pl []\nCreates a new SQL script to upgrade all of the PostGIS functions.\n" - if ( @ARGV < 1 || @ARGV > 2 ); +# +# Conditionally upgraded types and operators. Only include these +# if the major numbers in version_from are less than the version_to +# number. +# +my $objs = { + "104" => { + "types" => { + "box3d_extent" => 1, + "pgis_abs" => 1 + } + }, + "105" => { + "operators" => { + "geography >" => 1, + "geography >=" => 1, + "geography =" => 1, + "geography <=" => 1, + "geography <" => 1, + "geography &&" => 1 + }, + "opclasses" => { + "gist_geography_ops" => 1, + "btree_geography_ops" => 1 + }, + "types" => { + "geography" => 1, + "gidx" => 1 + } + } +}; -my $NEWVERSION = "UNDEF"; -my %newtypes = ( "box3d_extent", 1, "pgis_abs", 1 ); +# +# Commandline argument handling +# +($#ARGV == 0) || +die "Usage: perl postgis_proc_upgrade.pl []\nCreates a new SQL script to upgrade all of the PostGIS functions.\n" + if ( @ARGV < 1 || @ARGV > 3 ); -print "BEGIN;\n"; +my $sql_file = $ARGV[0]; +my $version_to = ""; +my $version_to_num = 0; +my $version_from = $ARGV[1]; +my $version_from_num = 0; +my $schema = ""; +$schema = $ARGV[2] if @ARGV > 2; -print "SET search_path TO $ARGV[1];\n" if @ARGV>1; +if ( $version_from =~ /^(\d+)\.(\d+)/ ) +{ + $version_from_num = 100 * $1 + $2; +} +else +{ + die "Version from number invalid, must be of form X.X\n"; +} -open( INPUT, $ARGV[0] ) || die "Couldn't open file: $ARGV[0]\n"; +die "Unable to open input SQL file $sql_file\n" + if ( ! -f $sql_file ); -FUNC: +# +# Search the SQL file for the target version number (the +# version we are upgrading *to*. +# +open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n"; while() { # # Since 1.1.0 scripts/lib/release versions are the same # - if (m/^create or replace function postgis_scripts_installed()/i) + if (/INSTALL VERSION: (.*)/) { - while() - { - if ( m/SELECT .'(\d\.\d\..*).'::text/i ) - { - $NEWVERSION = $1; - last FUNC; - } - } + $version_to = $1; + last; } } +close(INPUT); + +die "Unable to locate target new version number in $sql_file\n" + if( ! $version_to ); + +if ( $version_to =~ /(\d+)\.(\d+)\..*/ ) +{ + $version_to = $1 . "." . $2; + $version_to_num = 100 * $1 + $2; +} +else +{ + die "Version to number invalid, must be of form X.X.X\n"; +} -print "-- $NEWVERSION\n"; +print qq{ +-- +-- UPGRADE SCRIPT FROM PostGIS $version_from TO PostGIS $version_to +-- +}; + +print "BEGIN;\n"; +print "SET search_path TO $schema;\n" if $schema; + +# +# Add in the conditional check function to ensure this script is +# not being applied to a major version update. +# while() { - s/NEWVERSION/$NEWVERSION/g; + s/NEWVERSION/$version_to/g; print; } -close(INPUT); - -open( INPUT, $ARGV[0] ) || die "Couldn't open file: $ARGV[0]\n"; +# +# Go through the SQL file and strip out objects that cannot be +# applied to an existing, loaded database: types and operators +# and operator classes that have already been defined. +# +open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n"; while() { - my $checkit = 0; + if (m/^create or replace function/i) { - $checkit = 1 if m/postgis_scripts_installed()/i; print $_; while() { - if ( $checkit && m/SELECT .'(\d\.\d\.\d).'::text/i ) - { - $NEWVERSION = $1; - } print $_; last if m/^\s*LANGUAGE '/; } } - if (m/^create type (\S+)/i) + + if ( m/^create type (\w+)/i ) { my $newtype = $1; - print $_ if $newtypes{$newtype}; + my $def .= $_; while() { - print $_ if $newtypes{$newtype}; + $def .= $_; last if m/\)/; } + my $ver = $version_from_num + 1; + while( $version_from_num < $version_to_num && $ver <= $version_to_num ) + { + if( $objs->{$ver}->{"types"}->{$newtype} ) + { + print $def; + last; + } + $ver++; + } } + # This code handles casts by dropping and recreating them. + if ( /^create cast\s+\(\s*(\w+)\s+as\s+(\w+)\)/i ) + { + my $type1 = $1; + my $type2 = $2; + my $def = $_; + print "DROP CAST IF EXISTS ($type1 AS $type2);\n"; + print $def; + } # This code handles aggregates by dropping and recreating them. - # The DROP would fail on aggregates as they would not exist - # in old postgis installations, thus we avoid this until we - # find a better strategy. - - if (/^create aggregate\s+(\S+)\s*\(/i) + if ( /^create aggregate\s+(\S+)\s*\(/i ) { my $aggname = $1; - my $basetype = 'unknown'; + my $aggtype = 'unknown'; my $def = $_; while() { $def .= $_; - $basetype = $1 if (m/basetype\s*=\s*([^,]*)\s*,/i); + $aggtype = $1 if (m/basetype\s*=\s*([^,]*)\s*,/i); last if m/\);/; } - print "DROP AGGREGATE IF EXISTS $aggname($basetype);\n"; - print "$def"; + print "DROP AGGREGATE IF EXISTS $aggname($aggtype);\n"; + print $def; + } + + # This code handles operators by creating them if we are doing a major upgrade + if ( /^create operator\s+(\S+)\s*\(/i ) + { + my $opname = $1; + my $optype = 'unknown'; + my $def = $_; + while() + { + $def .= $_; + $optype = $1 if ( m/leftarg\s*=\s*(\w+)\s*,/i ); + last if m/\);/; + } + my $opsig = $optype . " " . $opname; + my $ver = $version_from_num + 1; + while( $version_from_num < $version_to_num && $ver <= $version_to_num ) + { + if( $objs->{$ver}->{"operators"}->{$opsig} ) + { + print $def; + last; + } + $ver++; + } } + # This code handles operator classes by creating them if we are doing a major upgrade + if ( /^create operator class\s+(\w+)\s*/i ) + { + my $opclassname = $1; + my $opctype = 'unknown'; + my $opcidx = 'unknown'; + my $def = $_; + while() + { + $def .= $_; + $opctype = $1 if ( m/for type (\w+) /i ); + $opcidx = $1 if ( m/using (\w+) /i ); + last if m/\);/; + } + $opctype =~ tr/A-Z/a-z/; + $opcidx =~ tr/A-Z/a-z/; + my $ver = $version_from_num + 1; + while( $version_from_num < $version_to_num && $ver <= $version_to_num ) + { + if( $objs->{$ver}->{"opclasses"}->{$opclassname} ) + { + print $def; + last; + } + $ver++; + } + } } close( INPUT ); @@ -150,7 +286,7 @@ BEGIN SELECT into new_maj substring(new_scripts from 1 for 2); IF old_maj != new_maj THEN - RAISE EXCEPTION ''Scripts upgrade from version % to version % requires a dump/reload. See postgis manual for instructions'', old_scripts, new_scripts; + RAISE EXCEPTION ''Upgrade from version % to version % requires a dump/reload. See PostGIS manual for instructions'', old_scripts, new_scripts; ELSE RETURN ''Scripts versions checked for upgrade: ok''; END IF; -- 2.40.0