[postgis-tickets] r15093 - Fix schema support in RT_CreateOveriew
Sandro Santilli
strk at kbt.io
Fri Sep 9 07:28:26 PDT 2016
Author: strk
Date: 2016-09-09 07:28:26 -0700 (Fri, 09 Sep 2016)
New Revision: 15093
Modified:
branches/2.2/NEWS
branches/2.2/raster/rt_pg/rtpostgis.sql.in
branches/2.2/raster/test/regress/rt_createoverview.sql
branches/2.2/raster/test/regress/rt_createoverview_expected
Log:
Fix schema support in RT_CreateOveriew
Patch by hypostase
Closes #3615
Include tests
Modified: branches/2.2/NEWS
===================================================================
--- branches/2.2/NEWS 2016-09-09 14:23:24 UTC (rev 15092)
+++ branches/2.2/NEWS 2016-09-09 14:28:26 UTC (rev 15093)
@@ -3,6 +3,7 @@
* Bug Fixes *
+ - #3615, Fix schema support in RT_CreateOveriew (Cait Smith)
- #3628, Fix lack of support for capitalized-Y in SwapOrdinates
- #3524, Add interruptibility to geography brute-force distance
calculation
Modified: branches/2.2/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.2/raster/rt_pg/rtpostgis.sql.in 2016-09-09 14:23:24 UTC (rev 15092)
+++ branches/2.2/raster/rt_pg/rtpostgis.sql.in 2016-09-09 14:28:26 UTC (rev 15093)
@@ -8797,7 +8797,8 @@
PERFORM AddOverviewConstraints(sinfo.sch, ttab, col,
sinfo.sch, sinfo.tab, col, factor);
- RETURN ttab;
+ -- return the schema as well as the table
+ RETURN sinfo.sch||'.'||ttab;
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT;
Modified: branches/2.2/raster/test/regress/rt_createoverview.sql
===================================================================
--- branches/2.2/raster/test/regress/rt_createoverview.sql 2016-09-09 14:23:24 UTC (rev 15092)
+++ branches/2.2/raster/test/regress/rt_createoverview.sql 2016-09-09 14:28:26 UTC (rev 15093)
@@ -1,4 +1,5 @@
SET client_min_messages TO warning;
+-- Test for table without explicit schema
CREATE TABLE res1 AS SELECT
ST_AddBand(
ST_MakeEmptyRaster(10, 10, x, y, 1, -1, 0, 0, 0)
@@ -46,9 +47,94 @@
(SELECT count(*) r16 from o_16_res1)
;
+-- End of overview test on table without explicit schema
DROP TABLE o_16_res1;
DROP TABLE o_8_res1;
DROP TABLE o_4_res1;
DROP TABLE o_2_res1;
+-- Keep the source table
+
+
+-- Test overview with table in schema
+--
+CREATE SCHEMA oschm;
+-- offset the schema tableto distinguish it from original
+-- let it be small to reduce time cost.
+CREATE TABLE oschm.res1 AS SELECT
+ ST_AddBand(
+ ST_MakeEmptyRaster(10, 10, x, y, 1, -1, 0, 0, 0)
+ , 1, '8BUI', 0, 0
+ ) r
+FROM generate_series(100, 270, 10) x,
+ generate_series(140, -30, -10) y;
+SELECT addrasterconstraints('oschm'::name,'res1'::name, 'r'::name);
+
+-- add overview with explicit schema
+SELECT ST_CreateOverview('oschm.res1', 'r', 8)::text = 'oschm.o_8_res1';
+
+-- set search path to 'oschm' first
+SET search_path to oschm,public;
+
+-- create overview with schema in search_path
+SELECT ST_CreateOverview('res1', 'r', 4)::text = 'o_4_res1';
+
+-- Create overview for table in public schema with explict path
+-- at same factor of schema table
+SELECT ST_CreateOverview('public.res1', 'r', 8)::text = 'public.o_8_res1';
+
+-- Reset the search_path
+SET search_path to public;
+
+-- Create public overview of public table with same name
+-- and scale as schema table above using reset search path.
+SELECT ST_CreateOverview('res1', 'r', 4)::text = 'o_4_res1';
+
+-- Check scale and extent
+-- Offset means that original raster overviews won't match
+-- extent and values only mach on schema table not public one
+SELECT r_table_schema, r_table_name tab, r_raster_column c,
+ srid s, scale_x sx, scale_y sy,
+ blocksize_x w, blocksize_y h, same_alignment a,
+ -- regular_blocking (why not regular?)
+ --extent::box2d e,
+ st_covers(extent::box2d, 'BOX(100 -30,270 140)'::box2d) ec,
+ st_xmin(extent::box2d) = 100 as eix,
+ st_ymax(extent::box2d) = 140 as eiy,
+ (st_xmax(extent::box2d) - 280) <= scale_x as eox,
+ --(st_xmax(extent::box2d) - 170) eoxd,
+ abs(st_ymin(extent::box2d) + 40) <= abs(scale_y) as eoy
+ --,abs(st_ymin(extent::box2d) + 80) eoyd
+ FROM raster_columns
+WHERE r_table_name like '%res1'
+ORDER BY r_table_schema, scale_x, r_table_name;
+
+-- this test may be redundant - it's just confirming that the
+-- overview factor is correct, which we do for the original table.
+SELECT o_table_schema, o_table_name, o_raster_column,
+ r_table_schema, r_table_name, r_raster_column, overview_factor
+FROM raster_overviews
+WHERE r_table_name = 'res1'
+AND r_table_schema = 'oschm'
+ORDER BY o_table_schema,overview_factor;
+-- get the oschm table sizes
+SELECT 'count',
+(SELECT count(*) r1 from oschm.res1),
+(SELECT count(*) r4 from oschm.o_4_res1),
+(SELECT count(*) r8 from oschm.o_8_res1) ;
+
+-- clean up scheam oschm
+DROP TABLE oschm.o_8_res1;
+DROP TABLE oschm.o_4_res1;
+DROP TABLE oschm.res1;
+DROP SCHEMA oschm;
+
+-- Drop the tables for noschema test
+DROP TABLE o_8_res1;
+DROP TABLE o_4_res1;
DROP TABLE res1;
+
+-- Reset the session environment
+-- possibly a bit harsh, but we had to set the search_path
+-- and need to reset it back to default.
+DISCARD ALL;
Modified: branches/2.2/raster/test/regress/rt_createoverview_expected
===================================================================
--- branches/2.2/raster/test/regress/rt_createoverview_expected 2016-09-09 14:23:24 UTC (rev 15092)
+++ branches/2.2/raster/test/regress/rt_createoverview_expected 2016-09-09 14:28:26 UTC (rev 15093)
@@ -13,3 +13,17 @@
o_8_res1|r|res1|r|8
o_16_res1|r|res1|r|16
count|544|136|36|10|3
+t
+t
+t
+t
+t
+oschm|res1|r|0|1|-1|10|10|t|t|t|t|t|t
+oschm|o_4_res1|r|0|4|-4|10|10|t|t|t|t|t|t
+oschm|o_8_res1|r|0|8|-8|10|10|t|t|t|t|t|t
+public|res1|r|0|1|-1|10|10|t|f|f|f|t|f
+public|o_4_res1|r|0|4|-4|10|10|t|f|f|f|t|f
+public|o_8_res1|r|0|8|-8|10|10|t|f|f|f|t|f
+oschm|o_4_res1|r|oschm|res1|r|4
+oschm|o_8_res1|r|oschm|res1|r|8
+count|324|25|9
More information about the postgis-tickets
mailing list