From dada3886d9e9dcbda2543c1a86524608d5c8422d Mon Sep 17 00:00:00 2001
From: Sandro Santilli <strk@keybit.net>
Date: Tue, 16 Oct 2012 08:29:26 +0000
Subject: [PATCH] Improve TopologySummary output

Add unregistered layers and orphaned TopoGeom count

git-svn-id: http://svn.osgeo.org/postgis/trunk@10435 b70326c6-7e19-0410-871a-916f4a2858ee
---
 NEWS                                         |  2 +
 topology/sql/manage/TopologySummary.sql.in.c | 42 ++++++++++++++++++--
 2 files changed, 41 insertions(+), 3 deletions(-)

diff --git a/NEWS b/NEWS
index c9ef5375e..b0d9a0d62 100644
--- a/NEWS
+++ b/NEWS
@@ -48,6 +48,8 @@ PostGIS 2.1.0
   - #1780, support ST_GeoHash for geography
   - #2021, Added multi-band support to ST_Union(raster, ...) aggregate function
   - #2006, better support of ST_Area(geography) over poles and dateline
+  - TopologySummary output now includes unregistered layers and a count
+    of missing TopoGeometry objects from their natural layer.
 
 * Fixes *
 
diff --git a/topology/sql/manage/TopologySummary.sql.in.c b/topology/sql/manage/TopologySummary.sql.in.c
index fa4a9162f..78085c741 100644
--- a/topology/sql/manage/TopologySummary.sql.in.c
+++ b/topology/sql/manage/TopologySummary.sql.in.c
@@ -24,6 +24,8 @@ DECLARE
   rec2 RECORD;
   var_topology_id integer;
   n int4;
+  missing int4;
+  sql text;
   ret text;
 BEGIN
 
@@ -87,7 +89,7 @@ BEGIN
       ret = ret || 'X topogeoms in X layers' || E'\n';
   END;
 
-  -- TODO: print informations about layers
+  -- print information about registered layers
   FOR rec IN SELECT * FROM topology.layer l
     WHERE l.topology_id = var_topology_id
     ORDER by layer_id
@@ -130,14 +132,48 @@ BEGIN
       IF rec.feature_column != '' THEN
         ret = ret || quote_ident(rec.schema_name) || '.'
                   || quote_ident(rec.table_name) || '.'
-                  || quote_ident(rec.feature_column)
-                  || E'\n';
+                  || quote_ident(rec.feature_column);
+
+        IF n > 0 THEN
+          sql := 'SELECT count(*) FROM ( SELECT topogeo_id FROM '
+            || quote_ident(atopology)
+            || '.relation r WHERE r.layer_id = ' || rec.layer_id
+            || ' EXCEPT SELECT DISTINCT id('
+            || 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;
+        END IF;
+        ret = ret || E'\n';
+
       ELSE
         ret = ret || E'NONE (detached)\n';
       END IF;
 
   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, ';
+
+    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;
+
+    ret = ret || n || ' topogeoms' || E'\n';
+
+  END LOOP; -- }
+
   EXCEPTION
     WHEN INVALID_SCHEMA_NAME THEN
       ret = ret || E'\n- missing schema - ';
-- 
2.40.0