Use PostgreSQL schemas to partition layers.

Rather than putting everything in the 'postgis' schema using ':' as
hierarchy separator.

When the destination dataset is not PostgreSQL, the layers names are
prefixed using '$SCHEMA.' instead of '$SCHEMA:'

1 files changed, 14 insertions, 16 deletions

diff --git a/webmap-cgi b/webmap-cgi
index 96dba33..2e33016 100755
--- a/webmap-cgi
+++ b/webmap-cgi
@@ -31,6 +31,7 @@ import atexit
 from psycopg import connect, RawCursor # pylint: disable=import-error
 
 import common
+from common import escape_identifier, getEscapedTableNamePG, getQualifiedLayerName
 
 def get_table_map() -> dict[tuple[str, str], str]:
     """Get mapping of pairs (MVT group name, layername) to table name."""
@@ -53,17 +54,19 @@ def get_table_map() -> dict[tuple[str, str], str]:
                 ret[k] = layername
     return ret
 
-SCHEMA_NAME : Final[str] = 'postgis'
 def get_query_map(layernames : set[str]) -> dict[str,bytes]:
     """Get GeoJSON-producing query map."""
     ret = {}
     # pylint: disable-next=no-member
     with PG_CONN.cursor(binary=True, scrollable=False, withhold=False) as cur:
         for layername in layernames:
+            (schema_name, table_name) = getQualifiedLayerName(layername)
+            if schema_name is None:
+                raise RuntimeError(f'Layername {layername} is not qualified')
             cur.execute('SELECT f_geometry_column, coord_dimension, srid, type '
-                          'FROM ' + common.escape_identifier(SCHEMA_NAME) + '.geometry_columns '
+                          'FROM public.geometry_columns '
                           'WHERE f_table_schema = $1 AND f_table_name = $2',
-                        params=(SCHEMA_NAME, layername),
+                        params=(schema_name, table_name),
                         prepare=False)
             resp = cur.fetchone()
             if resp is None:
@@ -108,8 +111,7 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]:
                             'JOIN pg_attribute a '
                                   ' ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) '
                           'WHERE i.indrelid = $1::regclass AND i.indisprimary',
-                        params=(common.escape_identifier(SCHEMA_NAME) + '.' +
-                                    common.escape_identifier(layername),),
+                        params=(getEscapedTableNamePG(layername),),
                         prepare=False)
             resp = cur.fetchone()
             if resp is None:
@@ -129,7 +131,7 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]:
             column_names = []
             cur.execute('SELECT column_name FROM information_schema.columns '
                           'WHERE table_schema = $1 AND table_name = $2',
-                        params=(SCHEMA_NAME, layername),
+                        params=(schema_name, table_name),
                         prepare=False)
             # never empty since the we know the table exists and has a primary key
             resp = cur.fetchone()
@@ -146,22 +148,21 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]:
                 resp = cur.fetchone()
 
             query = 'WITH feature AS ('
-            query +=  'SELECT m.' + common.escape_identifier(pkey_col) + ' AS ogc_fid,'
+            query +=  'SELECT m.' + escape_identifier(pkey_col) + ' AS ogc_fid,'
             for column_name in column_names:
-                query +=     'm.' + common.escape_identifier(column_name) + ','
+                query +=     'm.' + escape_identifier(column_name) + ','
             if force2d:
-                geom_col2d_esc = 'ST_Force2D(m.' + common.escape_identifier(geom_cols[0]) + ')'
+                geom_col2d_esc = 'ST_Force2D(m.' + escape_identifier(geom_cols[0]) + ')'
             else:
-                geom_col2d_esc = 'm.' + common.escape_identifier(geom_cols[0])
+                geom_col2d_esc = 'm.' + escape_identifier(geom_cols[0])
             if d == 2:
                 query +=     'ST_Area(' + geom_col2d_esc +') AS geom_area,'
                 query +=     'ST_Perimeter(' + geom_col2d_esc +') AS geom_perimeter,'
             elif d == 1:
                 query +=     'ST_Length(' + geom_col2d_esc +') AS geom_length,'
             query +=         '$1 AS layer_group,$2 AS layer '
-            query +=      'FROM ' + common.escape_identifier(SCHEMA_NAME) + '.'
-            query +=                common.escape_identifier(layername) + ' m '
-            query +=      'WHERE m.' + common.escape_identifier(pkey_col) + ' = $3'
+            query +=      'FROM ' + getEscapedTableNamePG(layername) + ' m '
+            query +=      'WHERE m.' + escape_identifier(pkey_col) + ' = $3'
             query += ') '
             query += 'SELECT json_serialize(to_json(feature) RETURNING bytea) FROM feature'
             # The query never returns more than one row since we filter on a single FID.
@@ -283,9 +284,6 @@ TABLE_MAP : Final[dict[tuple[str, str], str]] = get_table_map()
 QUERY_MAP : Final[dict[str, bytes]] = get_query_map(set(TABLE_MAP.values()))
 
 PG_CONN.execute( # pylint: disable=no-member
-    'SET search_path TO ' + common.escape_identifier(SCHEMA_NAME) + ',public',
-    prepare=False)
-PG_CONN.execute( # pylint: disable=no-member
     'SET statement_timeout TO 15000', # 15s
     prepare=False)