From c8e8abd83897872847e6c4c07b909c326b7349af Mon Sep 17 00:00:00 2001 From: Bborie Park Date: Mon, 9 Jan 2012 22:43:35 +0000 Subject: [PATCH] Added column "out_db" to raster_columns. "out_db" is of type boolean[] with each element indicating if that band of same index is out-of-database. Updated relevant AddRasterConstraints and DropRasterConstraints functions. Related ticket is #1440. git-svn-id: http://svn.osgeo.org/postgis/trunk@8733 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/loader/raster2pgsql.c | 4 +- raster/rt_pg/rtpostgis.sql.in.c | 103 ++++++++++++++++++++++++- raster/rt_pg/rtpostgis_drop.sql.in.c | 9 +++ raster/rt_pg/rtpostgis_legacy.sql.in.c | 12 ++- 4 files changed, 120 insertions(+), 8 deletions(-) diff --git a/raster/loader/raster2pgsql.c b/raster/loader/raster2pgsql.c index 910c729dc..223cfe8b4 100644 --- a/raster/loader/raster2pgsql.c +++ b/raster/loader/raster2pgsql.c @@ -1020,7 +1020,7 @@ add_raster_constraints( _table = chartrim(table, "\""); _column = chartrim(column, "\""); - len = strlen("SELECT AddRasterConstraints('','','',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE);") + 1; + len = strlen("SELECT AddRasterConstraints('','','',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE);") + 1; if (_schema != NULL) len += strlen(_schema); len += strlen(_table); @@ -1031,7 +1031,7 @@ add_raster_constraints( fprintf(stderr, _("Could not allocate memory for AddRasterConstraints statement\n")); return 0; } - sprintf(sql, "SELECT AddRasterConstraints('%s','%s','%s',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,%s,TRUE,TRUE,TRUE,%s);", + sprintf(sql, "SELECT AddRasterConstraints('%s','%s','%s',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,%s,TRUE,TRUE,TRUE,TRUE,%s);", (_schema != NULL ? _schema : ""), _table, _column, diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c index b47c8cce7..f000debed 100644 --- a/raster/rt_pg/rtpostgis.sql.in.c +++ b/raster/rt_pg/rtpostgis.sql.in.c @@ -4203,6 +4203,86 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_nodata_values(rastschema name LANGUAGE 'sql' VOLATILE STRICT COST 100; +CREATE OR REPLACE FUNCTION _raster_constraint_info_out_db(rastschema name, rasttable name, rastcolumn name) + RETURNS boolean[] AS $$ + SELECT + trim(both '''' from split_part(replace(replace(split_part(s.consrc, ' = ', 2), ')', ''), '(', ''), '::', 1))::boolean[] + FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s + WHERE n.nspname = $1 + AND c.relname = $2 + AND a.attname = $3 + AND a.attrelid = c.oid + AND s.connamespace = n.oid + AND s.conrelid = c.oid + AND a.attnum = ANY (s.conkey) + AND s.consrc LIKE '%_raster_constraint_out_db(%'; + $$ LANGUAGE sql STABLE STRICT + COST 100; + +CREATE OR REPLACE FUNCTION _raster_constraint_out_db(rast raster) + RETURNS boolean[] AS + $$ SELECT array_agg(isoutdb)::boolean[] FROM st_bandmetadata($1, ARRAY[]::int[]); $$ + LANGUAGE 'sql' STABLE STRICT; + +CREATE OR REPLACE FUNCTION _add_raster_constraint_out_db(rastschema name, rasttable name, rastcolumn name) + RETURNS boolean AS $$ + DECLARE + fqtn text; + cn name; + sql text; + attr boolean[]; + max int; + BEGIN + fqtn := ''; + IF length($1) > 0 THEN + fqtn := quote_ident($1) || '.'; + END IF; + fqtn := fqtn || quote_ident($2); + + cn := 'enforce_out_db_' || $3; + + sql := 'SELECT _raster_constraint_out_db(' || quote_ident($3) + || ') FROM ' || fqtn + || ' LIMIT 1'; + BEGIN + EXECUTE sql INTO attr; + EXCEPTION WHEN OTHERS THEN + RAISE NOTICE 'Unable to get the out-of-database bands of a sample raster'; + RETURN FALSE; + END; + max := array_length(attr, 1); + IF max < 1 OR max IS NULL THEN + RAISE NOTICE 'Unable to get the out-of-database bands of a sample raster'; + RETURN FALSE; + END IF; + + sql := 'ALTER TABLE ' || fqtn + || ' ADD CONSTRAINT ' || quote_ident(cn) + || ' CHECK (_raster_constraint_out_db(' || quote_ident($3) + || ') = ''{'; + FOR x in 1..max LOOP + IF attr[x] IS FALSE THEN + sql := sql || 'FALSE'; + ELSE + sql := sql || 'TRUE'; + END IF; + IF x < max THEN + sql := sql || ','; + END IF; + END LOOP; + sql := sql || '}''::boolean[])'; + + RETURN _add_raster_constraint(cn, sql); + END; + $$ LANGUAGE 'plpgsql' VOLATILE STRICT + COST 100; + +CREATE OR REPLACE FUNCTION _drop_raster_constraint_out_db(rastschema name, rasttable name, rastcolumn name) + RETURNS boolean AS + $$ SELECT _drop_raster_constraint($1, $2, 'enforce_out_db_' || $3) $$ + LANGUAGE 'sql' VOLATILE STRICT + COST 100; + ------------------------------------------------------------------------------ -- AddRasterConstraints ------------------------------------------------------------------------------ @@ -4308,6 +4388,9 @@ CREATE OR REPLACE FUNCTION AddRasterConstraints ( WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN RAISE NOTICE 'Adding nodata value constraint'; rtn := _add_raster_constraint_nodata_values(schema, $2, $3); + WHEN kw IN ('out_db', 'outdb') THEN + RAISE NOTICE 'Adding out-of-database constraint'; + rtn := _add_raster_constraint_out_db(schema, $2, $3); WHEN kw = 'extent' THEN RAISE NOTICE 'Adding maximum extent constraint'; rtn := _add_raster_constraint_extent(schema, $2, $3); @@ -4358,6 +4441,7 @@ CREATE OR REPLACE FUNCTION AddRasterConstraints ( num_bands boolean DEFAULT TRUE, pixel_types boolean DEFAULT TRUE, nodata_values boolean DEFAULT TRUE, + out_db boolean DEFAULT TRUE, extent boolean DEFAULT TRUE ) RETURNS boolean @@ -4405,6 +4489,10 @@ CREATE OR REPLACE FUNCTION AddRasterConstraints ( constraints := constraints || 'nodata_values'::text; END IF; + IF out_db IS TRUE THEN + constraints := constraints || 'out_db'::text; + END IF; + IF extent IS TRUE THEN constraints := constraints || 'extent'::text; END IF; @@ -4427,10 +4515,11 @@ CREATE OR REPLACE FUNCTION AddRasterConstraints ( num_bands boolean DEFAULT TRUE, pixel_types boolean DEFAULT TRUE, nodata_values boolean DEFAULT TRUE, + out_db boolean DEFAULT TRUE, extent boolean DEFAULT TRUE ) RETURNS boolean AS - $$ SELECT AddRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) $$ + $$ SELECT AddRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) $$ LANGUAGE 'sql' VOLATILE STRICT COST 100; @@ -4539,6 +4628,9 @@ CREATE OR REPLACE FUNCTION DropRasterConstraints ( WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN RAISE NOTICE 'Dropping nodata value constraint'; rtn := _drop_raster_constraint_nodata_values(schema, $2, $3); + WHEN kw IN ('out_db', 'outdb') THEN + RAISE NOTICE 'Dropping out-of-database constraint'; + rtn := _drop_raster_constraint_out_db(schema, $2, $3); WHEN kw = 'extent' THEN RAISE NOTICE 'Dropping maximum extent constraint'; rtn := _drop_raster_constraint_extent(schema, $2, $3); @@ -4589,6 +4681,7 @@ CREATE OR REPLACE FUNCTION DropRasterConstraints ( num_bands boolean DEFAULT TRUE, pixel_types boolean DEFAULT TRUE, nodata_values boolean DEFAULT TRUE, + out_db boolean DEFAULT TRUE, extent boolean DEFAULT TRUE ) RETURNS boolean @@ -4636,6 +4729,10 @@ CREATE OR REPLACE FUNCTION DropRasterConstraints ( constraints := constraints || 'nodata_values'::text; END IF; + IF out_db IS TRUE THEN + constraints := constraints || 'out_db'::text; + END IF; + IF extent IS TRUE THEN constraints := constraints || 'extent'::text; END IF; @@ -4658,10 +4755,11 @@ CREATE OR REPLACE FUNCTION DropRasterConstraints ( num_bands boolean DEFAULT TRUE, pixel_types boolean DEFAULT TRUE, nodata_values boolean DEFAULT TRUE, + out_db boolean DEFAULT TRUE, extent boolean DEFAULT TRUE ) RETURNS boolean AS - $$ SELECT DropRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) $$ + $$ SELECT DropRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) $$ LANGUAGE 'sql' VOLATILE STRICT COST 100; @@ -4688,6 +4786,7 @@ CREATE OR REPLACE VIEW raster_columns AS _raster_constraint_info_num_bands(n.nspname, c.relname, a.attname) AS num_bands, _raster_constraint_info_pixel_types(n.nspname, c.relname, a.attname) AS pixel_types, _raster_constraint_info_nodata_values(n.nspname, c.relname, a.attname) AS nodata_values, + _raster_constraint_info_out_db(n.nspname, c.relname, a.attname) AS out_db, _raster_constraint_info_extent(n.nspname, c.relname, a.attname) AS extent FROM pg_class c, diff --git a/raster/rt_pg/rtpostgis_drop.sql.in.c b/raster/rt_pg/rtpostgis_drop.sql.in.c index 94207823d..2aa8800ef 100644 --- a/raster/rt_pg/rtpostgis_drop.sql.in.c +++ b/raster/rt_pg/rtpostgis_drop.sql.in.c @@ -248,6 +248,9 @@ CREATE OR REPLACE FUNCTION _rename_raster_tables() SELECT _rename_raster_tables(); DROP FUNCTION _rename_raster_tables(); +-- inserted new column into view +DROP VIEW IF EXISTS raster_columns; + -- functions no longer supported DROP FUNCTION IF EXISTS AddRasterColumn(varchar, varchar, varchar, varchar, integer, varchar[], boolean, boolean, double precision[], double precision, double precision, integer, integer, geometry); DROP FUNCTION IF EXISTS AddRasterColumn(varchar, varchar, varchar, integer, varchar[], boolean, boolean, double precision[], double precision, double precision, integer, integer, geometry); @@ -259,6 +262,12 @@ DROP FUNCTION IF EXISTS DropRasterTable(varchar, varchar, varchar); DROP FUNCTION IF EXISTS DropRasterTable(varchar, varchar); DROP FUNCTION IF EXISTS DropRasterTable(varchar); +-- function parameters added +DROP FUNCTION IF EXISTS AddRasterConstraints(name, name, name, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); +DROP FUNCTION IF EXISTS AddRasterConstraints(name, name, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); +DROP FUNCTION IF EXISTS DropRasterConstraints(name, name, name, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); +DROP FUNCTION IF EXISTS DropRasterConstraints(name, name, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); + -- function parameters renamed CREATE OR REPLACE FUNCTION _drop_st_samealignment() RETURNS void AS $$ diff --git a/raster/rt_pg/rtpostgis_legacy.sql.in.c b/raster/rt_pg/rtpostgis_legacy.sql.in.c index 7fa367eb5..661ec662e 100644 --- a/raster/rt_pg/rtpostgis_legacy.sql.in.c +++ b/raster/rt_pg/rtpostgis_legacy.sql.in.c @@ -70,9 +70,9 @@ CREATE OR REPLACE VIEW raster_columns AS _raster_constraint_info_num_bands(n.nspname, c.relname, a.attname) AS num_bands, _raster_constraint_info_pixel_types(n.nspname, c.relname, a.attname) AS pixel_types, _raster_constraint_info_nodata_values(n.nspname, c.relname, a.attname) AS nodata_values, + _raster_constraint_info_out_db(n.nspname, c.relname, a.attname) AS out_db, _raster_constraint_info_extent(n.nspname, c.relname, a.attname) AS extent, - a.attname AS r_column, - FALSE AS out_db + a.attname AS r_column FROM pg_class c, pg_attribute a, @@ -99,13 +99,14 @@ CREATE OR REPLACE VIEW raster_overviews AS trim(both from split_part(s.consrc, ',', 2))::integer AS overview_factor, a.attname AS o_column, split_part(split_part(s.consrc, '''::name', 3), '''', 2)::name AS r_column, - FALSE AS out_db + rc.out_db AS out_db FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n, - pg_constraint s + pg_constraint s, + raster_columns rc WHERE t.typname = 'raster'::name AND a.attisdropped = false AND a.atttypid = t.oid @@ -114,5 +115,8 @@ CREATE OR REPLACE VIEW raster_overviews AS AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char") AND s.connamespace = n.oid AND s.conrelid = c.oid + AND n.nspname = rc.r_table_schema + AND c.relname = rc.r_table_name + AND a.attname = rc.r_raster_column AND s.consrc LIKE '%_overview_constraint(%' AND NOT pg_is_other_temp_schema(c.relnamespace); -- 2.40.0