From 05dcc0e4e60aeadce91f8e62edb0e01a4f17824e Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Wed, 21 Jan 2015 12:24:57 +0000 Subject: [PATCH] Avoid exceptions from TopologySummary (#3015) Includes testcase for the function git-svn-id: http://svn.osgeo.org/postgis/trunk@13186 b70326c6-7e19-0410-871a-916f4a2858ee --- topology/sql/manage/TopologySummary.sql.in | 109 ++++++++++-------- topology/test/Makefile.in | 1 + topology/test/regress/topologysummary.sql | 37 ++++++ .../test/regress/topologysummary_expected | 52 +++++++++ 4 files changed, 149 insertions(+), 50 deletions(-) create mode 100644 topology/test/regress/topologysummary.sql create mode 100644 topology/test/regress/topologysummary_expected diff --git a/topology/sql/manage/TopologySummary.sql.in b/topology/sql/manage/TopologySummary.sql.in index 9130f7846..b32298ada 100644 --- a/topology/sql/manage/TopologySummary.sql.in +++ b/topology/sql/manage/TopologySummary.sql.in @@ -27,6 +27,7 @@ DECLARE missing int4; sql text; ret text; + tgcount int4; BEGIN ret := 'Topology ' || quote_ident(atopology) ; @@ -36,57 +37,62 @@ BEGIN -- TODO: catch to give a nice error message var_topology_id := rec.id; - ret := ret || ' (' || rec.id || '), '; - ret := ret || 'SRID ' || rec.srid || ', ' + ret := ret || ' (id ' || rec.id || ', ' + || 'SRID ' || rec.srid || ', ' || 'precision ' || rec.precision; IF rec.hasz THEN ret := ret || ', has Z'; END IF; - ret := ret || E'\n'; + ret := ret || E')\n'; EXCEPTION WHEN NO_DATA_FOUND THEN - ret := ret || E' (X)\n'; + ret := ret || E' (unregistered)\n'; END; BEGIN - - BEGIN - EXECUTE 'SELECT count(node_id) FROM ' || quote_ident(atopology) + EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology) || '.node ' INTO STRICT n; ret = ret || n || ' nodes, '; EXCEPTION WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN - ret = ret || 'X nodes, '; + IF NOT EXISTS ( + SELECT * FROM pg_catalog.pg_namespace WHERE nspname = atopology + ) + THEN + ret = ret || 'missing schema'; + RETURN ret; + ELSE + ret = ret || 'missing nodes, '; + END IF; END; BEGIN - EXECUTE 'SELECT count(edge_id) FROM ' || quote_ident(atopology) - || '.edge_data ' INTO STRICT n; + EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology) + || '.edge' INTO STRICT n; ret = ret || n || ' edges, '; EXCEPTION WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN - ret = ret || 'X edges, '; + ret = ret || 'missing edges, '; END; BEGIN - EXECUTE 'SELECT count(face_id) FROM ' || quote_ident(atopology) - || '.face WHERE face_id != 0' INTO STRICT n; - ret = ret || n || ' faces, '; + EXECUTE 'SELECT count(*) FROM ' || quote_ident(atopology) + || '.face' INTO STRICT n; + ret = ret || greatest(n-1,0) || ' faces, '; -- -1 is face=0 EXCEPTION WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN - ret = ret || 'X faces, '; + ret = ret || 'missing faces, '; END; BEGIN - - EXECUTE 'SELECT count(*) FROM (SELECT DISTINCT layer_id,topogeo_id FROM ' - || quote_ident(atopology) || '.relation ) foo ' INTO STRICT n; - ret = ret || n || ' topogeoms, '; - - EXECUTE 'SELECT count(layer_id) FROM topology.layer WHERE topology_id = ' - || rec.id INTO STRICT n; - ret = ret || n || ' layers' || E'\n'; + EXECUTE 'SELECT count(distinct layer_id) AS ln, ' + || 'count(distinct (layer_id,topogeo_id)) AS tn FROM ' + || quote_ident(atopology) || '.relation' INTO STRICT rec; + tgcount := rec.tn; + ret = ret || rec.tn || ' topogeoms in ' || rec.ln || E' layers\n'; EXCEPTION - WHEN UNDEFINED_TABLE OR INVALID_SCHEMA_NAME THEN - ret = ret || 'X topogeoms in X layers' || E'\n'; + WHEN UNDEFINED_TABLE THEN + ret = ret || E'missing relations\n'; + WHEN UNDEFINED_COLUMN THEN + ret = ret || E'corrupted relations\n'; END; -- print information about registered layers @@ -119,7 +125,8 @@ BEGIN ret = ret || n || ' topogeoms' || E'\n'; - EXCEPTION WHEN UNDEFINED_TABLE THEN + EXCEPTION WHEN UNDEFINED_TABLE OR UNDEFINED_COLUMN THEN + n := NULL; ret = ret || 'X topogeoms' || E'\n'; END; @@ -142,10 +149,17 @@ BEGIN || quote_ident(rec.feature_column) || ') FROM ' || quote_ident(rec.schema_name) || '.' || quote_ident(rec.table_name) || ') as foo'; - EXECUTE sql INTO STRICT missing; - IF missing > 0 THEN - ret = ret || ' (' || missing || ' missing topogeoms)'; - END IF; + BEGIN + EXECUTE sql INTO STRICT missing; + IF missing > 0 THEN + ret = ret || ' (' || missing || ' missing topogeoms)'; + END IF; + EXCEPTION + WHEN UNDEFINED_TABLE THEN + ret = ret || ' ( unexistent table )'; + WHEN UNDEFINED_COLUMN THEN + ret = ret || ' ( unexistent column )'; + END; END IF; ret = ret || E'\n'; @@ -156,31 +170,26 @@ BEGIN END LOOP; -- } -- print information about unregistered layers containing topogeoms - sql := 'SELECT layer_id FROM ' - || quote_ident(atopology) || '.relation EXCEPT SELECT layer_id' - || ' FROM topology.layer WHERE topology_id = ' - || var_topology_id || 'ORDER BY layer_id'; - --RAISE DEBUG '%', sql; - FOR rec IN EXECUTE sql - LOOP -- { - ret = ret || 'Layer ' || rec.layer_id::text || ', UNREGISTERED, '; + IF tgcount > 0 THEN -- { - EXECUTE 'SELECT count(*) FROM ( SELECT DISTINCT topogeo_id FROM ' - || quote_ident(atopology) - || '.relation r WHERE r.layer_id = ' || rec.layer_id - || ' ) foo ' INTO STRICT n; + sql := 'SELECT layer_id FROM ' + || quote_ident(atopology) || '.relation EXCEPT SELECT layer_id' + || ' FROM topology.layer WHERE topology_id = $1 ORDER BY layer_id'; + --RAISE DEBUG '%', sql; + FOR rec IN EXECUTE sql USING var_topology_id + LOOP -- { + ret = ret || 'Layer ' || rec.layer_id::text || ', UNREGISTERED, '; - ret = ret || n || ' topogeoms' || E'\n'; + EXECUTE 'SELECT count(*) FROM ( SELECT DISTINCT topogeo_id FROM ' + || quote_ident(atopology) + || '.relation r WHERE r.layer_id = ' || rec.layer_id + || ' ) foo ' INTO STRICT n; - END LOOP; -- } + ret = ret || n || ' topogeoms' || E'\n'; - EXCEPTION - WHEN INVALID_SCHEMA_NAME THEN - ret = ret || E'\n- missing schema - '; - WHEN OTHERS THEN - RAISE EXCEPTION 'Got % (%)', SQLERRM, SQLSTATE; - END; + END LOOP; -- } + END IF; -- } RETURN ret; END diff --git a/topology/test/Makefile.in b/topology/test/Makefile.in index d947bc39e..87110955b 100644 --- a/topology/test/Makefile.in +++ b/topology/test/Makefile.in @@ -53,6 +53,7 @@ TESTS = regress/legacy_validate.sql regress/legacy_predicate.sql \ regress/topogeo_addpolygon.sql \ regress/topogeometry_type.sql \ regress/topojson.sql \ + regress/topologysummary.sql \ regress/topo2.5d.sql \ regress/totopogeom.sql \ regress/droptopology.sql \ diff --git a/topology/test/regress/topologysummary.sql b/topology/test/regress/topologysummary.sql new file mode 100644 index 000000000..37df046b3 --- /dev/null +++ b/topology/test/regress/topologysummary.sql @@ -0,0 +1,37 @@ +SELECT E'--missing--\n' || TopologySummary('test'); +CREATE SCHEMA test; +SELECT E'--empty--\n' || TopologySummary('test'); +CREATE TABLE test.node(id int); +SELECT E'--node--\n' || TopologySummary('test'); +CREATE TABLE test.edge(id int); +SELECT E'--node+edge--\n' || TopologySummary('test'); +CREATE TABLE test.face(id int); +SELECT E'--node+edge+face--\n' || TopologySummary('test'); +CREATE TABLE test.relation(id int); +SELECT E'--node+edge+face+corrupted_relation--\n' || TopologySummary('test'); +ALTER TABLE test.relation ADD layer_id int, ADD topogeo_id int; +SELECT E'--node+edge+face+relation--\n' || TopologySummary('test'); +INSERT INTO test.relation (layer_id, topogeo_id) VALUES (1,1); +SELECT E'--node+edge+face+relation+topogeom--\n' || TopologySummary('test'); +INSERT INTO topology.topology (id,name,srid,precision,hasz) + VALUES(1,'test',10,20,'f'); +SELECT E'--registered_topo--\n' || TopologySummary('test'); +INSERT INTO topology.layer (topology_id,layer_id, schema_name, table_name, feature_column, feature_type, level, child_id) + VALUES(1,1,'test','t','c', 1, 0, null); +SELECT E'--registered_missing_layer_table--\n' || TopologySummary('test'); +CREATE TABLE test.t(i int); +SELECT E'--registered_missing_layer_column--\n' || TopologySummary('test'); +ALTER TABLE test.t ADD c TopoGeometry; +SELECT E'--registered_layer_missing_topogeom--\n' || TopologySummary('test'); +INSERT INTO test.t(c) VALUES ( (1,1,1,1) ); +SELECT E'--registered_layer--\n' || TopologySummary('test'); +-- TODO: test hierarchical +DROP TABLE test.t; +DROP TABLE test.node; +DROP TABLE test.edge; +DROP TABLE test.face; +DROP TABLE test.relation; +DROP SCHEMA test; +SELECT E'--registered+missing--\n' || TopologySummary('test'); +DELETE FROM topology.layer WHERE topology_id = 1; +DELETE FROM topology.topology WHERE id = 1; diff --git a/topology/test/regress/topologysummary_expected b/topology/test/regress/topologysummary_expected new file mode 100644 index 000000000..573402f79 --- /dev/null +++ b/topology/test/regress/topologysummary_expected @@ -0,0 +1,52 @@ +--missing-- +Topology test (unregistered) +missing schema +--empty-- +Topology test (unregistered) +missing nodes, missing edges, missing faces, missing relations +--node-- +Topology test (unregistered) +0 nodes, missing edges, missing faces, missing relations +--node+edge-- +Topology test (unregistered) +0 nodes, 0 edges, missing faces, missing relations +--node+edge+face-- +Topology test (unregistered) +0 nodes, 0 edges, 0 faces, missing relations +--node+edge+face+corrupted_relation-- +Topology test (unregistered) +0 nodes, 0 edges, 0 faces, corrupted relations +--node+edge+face+relation-- +Topology test (unregistered) +0 nodes, 0 edges, 0 faces, 0 topogeoms in 0 layers +--node+edge+face+relation+topogeom-- +Topology test (unregistered) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, UNREGISTERED, 1 topogeoms +--registered_topo-- +Topology test (id 1, SRID 10, precision 20) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, UNREGISTERED, 1 topogeoms +--registered_missing_layer_table-- +Topology test (id 1, SRID 10, precision 20) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, type Puntal (1), 1 topogeoms + Deploy: test.t.c ( unexistent table ) +--registered_missing_layer_column-- +Topology test (id 1, SRID 10, precision 20) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, type Puntal (1), 1 topogeoms + Deploy: test.t.c ( unexistent column ) +--registered_layer_missing_topogeom-- +Topology test (id 1, SRID 10, precision 20) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, type Puntal (1), 1 topogeoms + Deploy: test.t.c (1 missing topogeoms) +--registered_layer-- +Topology test (id 1, SRID 10, precision 20) +0 nodes, 0 edges, 0 faces, 1 topogeoms in 1 layers +Layer 1, type Puntal (1), 1 topogeoms + Deploy: test.t.c +--registered+missing-- +Topology test (id 1, SRID 10, precision 20) +missing schema -- 2.50.1