def __view(self) -> str:
"""
Create the SQL code for the view
:return: the SQL code
"""
sql = """
CREATE OR REPLACE VIEW {vs}.{vn} AS SELECT
{parent_cols}
{child_cols}
FROM {ps}.{pt}
{joins};
""".format(vs=self.view_schema,
vn=self.view_name,
parent_cols=select_columns(self.cursor,
self.parent_schema,
self.parent_table,
table_alias=self.parent_table,
remove_pkey=False),
child_cols='\n '.join([
select_columns(self.cursor,
child_def.schema_name,
child_def.table_name,
table_alias=alias,
remap_columns=child_def.remap_columns,
prefix=child_def.prefix,
separate_first=True)
for alias, child_def in self.child_tables.items()
]),
ps=self.parent_schema,
pt=self.parent_table,
joins='\n '.join([
"LEFT JOIN {cs}.{ct} {alias} ON {alias}.{cpk} = {pt}.{rpk}".
format(cs=child.schema_name,
ct=child.table_name,
alias=alias,
cpk=child.pkey,
pt=self.parent_table,
rpk=child.parent_referenced_key)
for alias, child in self.child_tables.items()
]))
return sql
def __view(self) -> str:
"""
Create the SQL code for the view
:return: the SQL code
"""
sql = """
CREATE OR REPLACE VIEW {vs}.{vn} AS SELECT
{child_cols},
{parent_cols}
FROM {cs}.{ct}
LEFT JOIN {ps}.{pt} ON {pt}.{prk} = {ct}.{rpk};
""".format(vs=self.view_schema,
vn=self.view_name,
parent_cols=select_columns(self.cursor, self.parent_schema, self.parent_table, table_alias=self.parent_table, remove_pkey=True),
child_cols=select_columns(self.cursor, self.child_schema, self.child_table, table_alias=self.child_table),
cs=self.child_schema,
ct=self.child_table,
ps=self.parent_schema,
pt=self.parent_table,
rpk=self.ref_parent_key,
prk=self.parent_pkey)
return sql
def vw_qgep_reach(pg_service: str = None, extra_definition: dict = None):
"""
Creates qgep_reach view
:param pg_service: the PostgreSQL service name
:param extra_definition: a dictionary for additional read-only columns
"""
if not pg_service:
pg_service = os.getenv('PGSERVICE')
assert pg_service
extra_definition = extra_definition or {}
conn = psycopg2.connect("service={0}".format(pg_service))
cursor = conn.cursor()
view_sql = """
DROP VIEW IF EXISTS qgep_od.vw_qgep_reach;
CREATE OR REPLACE VIEW qgep_od.vw_qgep_reach AS
SELECT
re.obj_id,
re.clear_height,
re.material,
ch.usage_current AS ch_usage_current,
ch.function_hierarchic AS ch_function_hierarchic,
ws.status AS ws_status,
ws.fk_owner AS ws_fk_owner,
ch.function_hydraulic AS ch_function_hydraulic,
CASE
WHEN pp.height_width_ratio IS NOT NULL THEN round(re.clear_height::numeric * pp.height_width_ratio)::smallint
ELSE clear_height
END AS width,
CASE
WHEN rp_from.level > 0 AND rp_to.level > 0 THEN round((rp_from.level - rp_to.level)/re.length_effective*1000,1)
ELSE NULL
END AS _slope_per_mill,
{extra_cols}
{re_cols},
{ne_cols},
{ch_cols},
{ws_cols},
{rp_from_cols},
{rp_to_cols}
FROM qgep_od.reach re
LEFT JOIN qgep_od.wastewater_networkelement ne ON ne.obj_id = re.obj_id
LEFT JOIN qgep_od.reach_point rp_from ON rp_from.obj_id = re.fk_reach_point_from
LEFT JOIN qgep_od.reach_point rp_to ON rp_to.obj_id = re.fk_reach_point_to
LEFT JOIN qgep_od.wastewater_structure ws ON ne.fk_wastewater_structure = ws.obj_id
LEFT JOIN qgep_od.channel ch ON ch.obj_id = ws.obj_id
LEFT JOIN qgep_od.pipe_profile pp ON re.fk_pipe_profile = pp.obj_id
{extra_joins};
""".format(
extra_cols='\n '.join([
select_columns(pg_cur=cursor,
table_schema=table_parts(table_def['table'])[0],
table_name=table_parts(table_def['table'])[1],
skip_columns=table_def.get('skip_columns', []),
remap_columns=table_def.get('remap_columns', {}),
prefix=table_def.get('prefix', None),
table_alias=table_def.get('alias', None)) + ','
for table_def in extra_definition.get('joins', {}).values()
]),
re_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
table_alias='re',
remove_pkey=True,
indent=4,
skip_columns=[
'clear_height', 'material',
'fk_reach_point_from', 'fk_reach_point_to'
]),
ne_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
table_alias='ne',
remove_pkey=True,
indent=4,
skip_columns=['fk_wastewater_structure']),
ch_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
table_alias='ch',
prefix='ch_',
remove_pkey=True,
indent=4,
skip_columns=[
'usage_current', 'function_hierarchic',
'function_hydraulic'
]),
ws_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
table_alias='ws',
prefix='ws_',
remove_pkey=False,
indent=4,
skip_columns=[
'detail_geometry_geometry', 'status',
'fk_owner', 'fk_dataowner', 'fk_provider',
'_usage_current', '_function_hierarchic',
'_label', '_depth', 'fk_main_cover'
]),
rp_from_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
table_alias='rp_from',
prefix='rp_from_',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry']),
rp_to_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
table_alias='rp_to',
prefix='rp_to_',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry']),
extra_joins='\n '.join([
'LEFT JOIN {tbl} {alias} ON {jon}'.format(tbl=table_def['table'],
alias=table_def.get(
'alias', ''),
jon=table_def['join_on'])
for table_def in extra_definition.get('joins', {}).values()
]))
cursor.execute(view_sql)
trigger_insert_sql = """
-- REACH INSERT
-- Function: vw_qgep_reach_insert()
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_reach_insert()
RETURNS trigger AS
$BODY$
BEGIN
-- Synchronize geometry with level
NEW.progression_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression_geometry),0,
ST_MakePoint(ST_X(ST_StartPoint(NEW.progression_geometry)),ST_Y(ST_StartPoint(NEW.progression_geometry)),COALESCE(NEW.rp_from_level,'NaN'))));
NEW.progression_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression_geometry),ST_NumPoints(NEW.progression_geometry)-1,
ST_MakePoint(ST_X(ST_EndPoint(NEW.progression_geometry)),ST_Y(ST_EndPoint(NEW.progression_geometry)),COALESCE(NEW.rp_to_level,'NaN'))));
{rp_from}
{rp_to}
{ws}
{ch}
{ne}
{re}
RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER vw_qgep_reach_insert INSTEAD OF INSERT ON qgep_od.vw_qgep_reach
FOR EACH ROW EXECUTE PROCEDURE qgep_od.ft_vw_qgep_reach_insert();
""".format(
rp_from=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_from_',
remove_pkey=False,
indent=2,
skip_columns=[],
coalesce_pkey_default=True,
insert_values={
'situation_geometry':
'ST_StartPoint(NEW.progression_geometry)'
},
returning='obj_id INTO NEW.rp_from_obj_id'),
rp_to=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_to_',
remove_pkey=False,
indent=2,
skip_columns=[],
coalesce_pkey_default=True,
insert_values={
'situation_geometry':
'ST_EndPoint(NEW.progression_geometry)'
},
returning='obj_id INTO NEW.rp_to_obj_id'),
ws=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
prefix='ws_',
remove_pkey=False,
indent=2,
skip_columns=[
'detail_geometry_geometry', 'fk_dataowner',
'fk_provider', '_usage_current',
'_function_hierarchic', '_label', '_depth',
'fk_main_cover'
]),
ch=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
prefix='ch_',
remove_pkey=False,
indent=2,
remap_columns={'obj_id': 'ws_obj_id'},
skip_columns=[]),
ne=insert_command(
pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
remove_pkey=False,
indent=2,
remap_columns={'fk_wastewater_structure': 'ws_obj_id'}),
re=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
remove_pkey=False,
indent=2,
insert_values={
'fk_reach_point_from': 'NEW.rp_from_obj_id',
'fk_reach_point_to': 'NEW.rp_to_obj_id'
}),
)
cursor.execute(trigger_insert_sql)
trigger_update_sql = """
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_reach_update()
RETURNS trigger AS
$BODY$
BEGIN
-- Synchronize geometry with level
IF NEW.rp_from_level <> OLD.rp_from_level OR (NEW.rp_from_level IS NULL AND OLD.rp_from_level IS NOT NULL) OR (NEW.rp_from_level IS NOT NULL AND OLD.rp_from_level IS NULL) THEN
NEW.progression_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression_geometry),0,
ST_MakePoint(ST_X(ST_StartPoint(NEW.progression_geometry)),ST_Y(ST_StartPoint(NEW.progression_geometry)),COALESCE(NEW.rp_from_level,'NaN'))));
ELSE
IF ST_Z(ST_StartPoint(NEW.progression_geometry)) <> ST_Z(ST_StartPoint(OLD.progression_geometry)) THEN
NEW.rp_from_level = NULLIF(ST_Z(ST_StartPoint(NEW.progression_geometry)),'NaN');
END IF;
END IF;
-- Synchronize geometry with level
IF NEW.rp_to_level <> OLD.rp_to_level OR (NEW.rp_to_level IS NULL AND OLD.rp_to_level IS NOT NULL) OR (NEW.rp_to_level IS NOT NULL AND OLD.rp_to_level IS NULL) THEN
NEW.progression_geometry = ST_ForceCurve(ST_SetPoint(ST_CurveToLine(NEW.progression_geometry),ST_NumPoints(NEW.progression_geometry)-1,
ST_MakePoint(ST_X(ST_EndPoint(NEW.progression_geometry)),ST_Y(ST_EndPoint(NEW.progression_geometry)),COALESCE(NEW.rp_to_level,'NaN'))));
ELSE
IF ST_Z(ST_EndPoint(NEW.progression_geometry)) <> ST_Z(ST_EndPoint(OLD.progression_geometry)) THEN
NEW.rp_to_level = NULLIF(ST_Z(ST_EndPoint(NEW.progression_geometry)),'NaN');
END IF;
END IF;
{rp_from}
{rp_to}
{ch}
{ws}
{ne}
{re}
RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE;
""".format(
rp_from=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_from_',
remove_pkey=True,
indent=6,
update_values={
'situation_geometry':
'ST_StartPoint(NEW.progression_geometry)'
}),
rp_to=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_to_',
remove_pkey=True,
indent=6,
update_values={
'situation_geometry':
'ST_EndPoint(NEW.progression_geometry)'
}),
ch=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
prefix='ch_',
remove_pkey=True,
indent=6,
remap_columns={'obj_id': 'ws_obj_id'}),
ws=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
prefix='ws_',
remove_pkey=True,
indent=6,
remap_columns={
'fk_dataowner': 'fk_dataowner',
'fk_provider': 'fk_provider',
'last_modification': 'last_modification'
},
skip_columns=[
'detail_geometry_geometry', '_usage_current',
'_function_hierarchic', '_label', '_depth',
'fk_main_cover'
]),
ne=update_command(
pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
remove_pkey=True,
indent=6,
remap_columns={'fk_wastewater_structure': 'ws_obj_id'}),
re=update_command(
pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
remove_pkey=True,
indent=6,
skip_columns=['fk_reach_point_to', 'fk_reach_point_from']),
)
cursor.execute(trigger_update_sql)
trigger_delete_sql = """
CREATE TRIGGER vw_qgep_reach_update
INSTEAD OF UPDATE
ON qgep_od.vw_qgep_reach
FOR EACH ROW
EXECUTE PROCEDURE qgep_od.ft_vw_qgep_reach_update();
-- REACH DELETE
-- Rule: vw_qgep_reach_delete()
CREATE OR REPLACE RULE vw_qgep_reach_delete AS ON DELETE TO qgep_od.vw_qgep_reach DO INSTEAD (
DELETE FROM qgep_od.reach WHERE obj_id = OLD.obj_id;
);
"""
cursor.execute(trigger_delete_sql)
extras = """
ALTER VIEW qgep_od.vw_qgep_reach ALTER obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach');
ALTER VIEW qgep_od.vw_qgep_reach ALTER rp_from_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach_point');
ALTER VIEW qgep_od.vw_qgep_reach ALTER rp_to_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach_point');
ALTER VIEW qgep_od.vw_qgep_reach ALTER ws_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','channel');
"""
cursor.execute(extras)
conn.commit()
conn.close()
def __view(self) -> str:
"""
:return:
"""
sorted_joins = sorted(self.joins.items())
sql = """
CREATE OR REPLACE VIEW {vs}.{vn} AS
SELECT
CASE
{types}
ELSE {no_subtype}
END AS {type_name}
{master_columns}{merge_columns}
{joined_columns}{additional_columns}
FROM {mt}.{ms} {sa}
{joined_tables}{additional_joins};
""".format(vs=self.view_schema,
vn=self.view_name,
types='\n '.join([
"WHEN {shal}.{mrf} IS NOT NULL THEN '{al}'::{vs}.{tn}".format(
shal=table_def['short_alias'],
mrf=table_def['ref_master_key'],
al=alias,
vs=self.view_schema,
tn=self.type_name) for alias, table_def in sorted_joins
]),
no_subtype="'{type}'::{vs}.{tn}".format(
type=self.view_alias if self.allow_parent_only else 'unknown',
vs=self.view_schema,
tn=self.type_name),
type_name=self.type_name,
master_columns=select_columns(
self.cursor,
self.master_schema,
self.master_table,
table_alias=self.view_alias,
skip_columns=self.master_skip_colums,
prefix=self.master_prefix,
remap_columns=self.master_remap_columns,
indent=4,
separate_first=True),
merge_columns='\n , '.join([
'\n , CASE'
'\n {conditions}'
'\n ELSE NULL{cast}'
'\n END AS {col}'.format(
col=col,
conditions='\n '.join([
'WHEN {ta}.{rmk} IS NOT NULL THEN {ta}.{col}'.format(
ta=table_def['short_alias'],
rmk=table_def['ref_master_key'],
col=col) for alias, table_def in sorted_joins
if col in columns(self.cursor,
table_def['table_schema'],
table_def['table_name'],
skip_columns=table_def.get(
'skip_columns', []))
]),
cast=self.merge_column_cast.get(col, ''))
for col in self.merge_columns
]),
joined_columns='\n '.join([
select_columns(self.cursor,
table_def['table_schema'],
table_def['table_name'],
table_alias=table_def['short_alias'],
skip_columns=table_def.get('skip_columns', []) +
[table_def['ref_master_key']],
safe_skip_columns=self.merge_columns,
prefix=table_def.get('prefix', None),
remove_pkey=False,
remap_columns=table_def.get('remap_columns', {}),
indent=4,
separate_first=True)
for alias, table_def in sorted_joins
]),
additional_columns=''.join([
',\n {cdef} AS {alias}'.format(cdef=cdef, alias=alias)
for alias, cdef in self.additional_columns.items()
]),
mt=self.master_schema,
ms=self.master_table,
sa=self.short_alias,
joined_tables='\n '.join([
"LEFT JOIN {tbl} {tal} ON {tal}.{rmk} = {msa}.{mpk}".format(
tbl=table_def[1]['table'],
tal=table_def[1]['short_alias'],
rmk=table_def[1]['ref_master_key'],
msa=self.short_alias,
mpk=self.master_pkey) for table_def in sorted_joins
]),
additional_joins='\n {ad}'.format(
ad=self.additional_joins) if self.additional_joins else '')
return sql
def vw_qgep_wastewater_structure(srid: int,
pg_service: str = None,
extra_definition: dict = None):
"""
Creates qgep_wastewater_structure view
:param srid: EPSG code for geometries
:param pg_service: the PostgreSQL service name
:param extra_definition: a dictionary for additional read-only columns
"""
if not pg_service:
pg_service = os.getenv('PGSERVICE')
assert pg_service
extra_definition = extra_definition or {}
variables = {'SRID': int(srid)}
conn = psycopg2.connect("service={0}".format(pg_service))
cursor = conn.cursor()
view_sql = """
DROP VIEW IF EXISTS qgep_od.vw_qgep_wastewater_structure;
CREATE OR REPLACE VIEW qgep_od.vw_qgep_wastewater_structure AS
SELECT
ws.identifier as identifier,
CASE
WHEN ma.obj_id IS NOT NULL THEN 'manhole'
WHEN ss.obj_id IS NOT NULL THEN 'special_structure'
WHEN dp.obj_id IS NOT NULL THEN 'discharge_point'
WHEN ii.obj_id IS NOT NULL THEN 'infiltration_installation'
ELSE 'unknown'
END AS ws_type,
ma.function AS ma_function,
ss.function as ss_function,
ws.fk_owner,
ws.status,
{extra_cols}
{ws_cols},
main_co_sp.identifier AS co_identifier,
main_co_sp.remark AS co_remark,
main_co_sp.renovation_demand AS co_renovation_demand,
{main_co_cols},
aggregated_wastewater_structure.situation_geometry,
{ma_columns},
{ss_columns},
{ii_columns},
{dp_columns},
{wn_cols},
{ne_cols},
ws._label,
ws._usage_current AS _channel_usage_current,
ws._function_hierarchic AS _channel_function_hierarchic
FROM (
SELECT ws.obj_id,
ST_Collect(co.situation_geometry)::geometry(MULTIPOINTZ, %(SRID)s) AS situation_geometry
FROM qgep_od.wastewater_structure ws
LEFT JOIN qgep_od.structure_part sp ON sp.fk_wastewater_structure = ws.obj_id
LEFT JOIN qgep_od.cover co ON co.obj_id = sp.obj_id
GROUP BY ws.obj_id
) aggregated_wastewater_structure
LEFT JOIN qgep_od.wastewater_structure ws ON ws.obj_id = aggregated_wastewater_structure.obj_id
LEFT JOIN qgep_od.cover main_co ON main_co.obj_id = ws.fk_main_cover
LEFT JOIN qgep_od.structure_part main_co_sp ON main_co_sp.obj_id = ws.fk_main_cover
LEFT JOIN qgep_od.manhole ma ON ma.obj_id = ws.obj_id
LEFT JOIN qgep_od.special_structure ss ON ss.obj_id = ws.obj_id
LEFT JOIN qgep_od.discharge_point dp ON dp.obj_id = ws.obj_id
LEFT JOIN qgep_od.infiltration_installation ii ON ii.obj_id = ws.obj_id
LEFT JOIN qgep_od.wastewater_networkelement ne ON ne.obj_id = ws.fk_main_wastewater_node
LEFT JOIN qgep_od.wastewater_node wn ON wn.obj_id = ws.fk_main_wastewater_node
{extra_joins};
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','wastewater_structure');
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER co_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','structure_part');
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER wn_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','wastewater_node');
""".format(
extra_cols='\n '.join([
select_columns(pg_cur=cursor,
table_schema=table_parts(table_def['table'])[0],
table_name=table_parts(table_def['table'])[1],
skip_columns=table_def.get('skip_columns', []),
remap_columns=table_def.get('remap_columns', {}),
prefix=table_def.get('prefix', None),
table_alias=table_def.get('alias', None)) + ','
for table_def in extra_definition.get('joins', {}).values()
]),
ws_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
table_alias='ws',
remove_pkey=False,
indent=4,
skip_columns=[
'identifier', 'fk_owner', 'status',
'_label', '_usage_current',
'_function_hierarchic', 'fk_main_cover',
'fk_main_wastewater_node',
'detail_geometry_geometry'
]),
main_co_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='cover',
table_alias='main_co',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry'],
prefix='co_',
remap_columns={'cover_shape': 'co_shape'},
columns_at_end=['obj_id']),
ma_columns=select_columns(
pg_cur=cursor,
table_schema='qgep_od',
table_name='manhole',
table_alias='ma',
remove_pkey=True,
indent=4,
skip_columns=['function'],
prefix='ma_',
remap_columns={'_orientation': 'ma_orientation'}),
ss_columns=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='special_structure',
table_alias='ss',
remove_pkey=True,
indent=4,
skip_columns=['function'],
prefix='ss_',
remap_columns={}),
ii_columns=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='infiltration_installation',
table_alias='ii',
remove_pkey=True,
indent=4,
skip_columns=[],
prefix='ii_',
remap_columns={}),
dp_columns=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='discharge_point',
table_alias='dp',
remove_pkey=True,
indent=4,
skip_columns=[],
prefix='dp_',
remap_columns={}),
wn_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_node',
table_alias='wn',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry'],
prefix='wn_',
remap_columns={},
columns_at_end=['obj_id']),
ne_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
table_alias='ne',
remove_pkey=True,
indent=4,
skip_columns=[],
prefix='wn_',
remap_columns={}),
extra_joins='\n '.join([
'LEFT JOIN {tbl} {alias} ON {jon}'.format(tbl=table_def['table'],
alias=table_def.get(
'alias', ''),
jon=table_def['join_on'])
for table_def in extra_definition.get('joins', {}).values()
]))
cursor.execute(view_sql, variables)
trigger_insert_sql = """
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_wastewater_structure_INSERT()
RETURNS trigger AS
$BODY$
BEGIN
NEW.identifier = COALESCE(NEW.identifier, NEW.obj_id);
{insert_ws}
CASE
WHEN NEW.ws_type = 'manhole' THEN
-- Manhole
{insert_ma}
-- Special Structure
WHEN NEW.ws_type = 'special_structure' THEN
{insert_ss}
-- Discharge Point
WHEN NEW.ws_type = 'discharge_point' THEN
{insert_dp}
-- Infiltration Installation
WHEN NEW.ws_type = 'infiltration_installation' THEN
{insert_ii}
ELSE
RAISE NOTICE 'Wastewater structure type not known (%)', NEW.ws_type; -- ERROR
END CASE;
{insert_wn}
UPDATE qgep_od.wastewater_structure
SET fk_main_wastewater_node = NEW.wn_obj_id
WHERE obj_id = NEW.obj_id;
{insert_vw_cover}
UPDATE qgep_od.wastewater_structure
SET fk_main_cover = NEW.co_obj_id
WHERE obj_id = NEW.obj_id;
RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
DROP TRIGGER IF EXISTS vw_qgep_wastewater_structure_INSERT ON qgep_od.vw_qgep_wastewater_structure;
CREATE TRIGGER vw_qgep_wastewater_structure_INSERT INSTEAD OF INSERT ON qgep_od.vw_qgep_wastewater_structure
FOR EACH ROW EXECUTE PROCEDURE qgep_od.ft_vw_qgep_wastewater_structure_INSERT();
""".format(
insert_ws=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
table_alias='ws',
remove_pkey=False,
indent=2,
skip_columns=[
'_label', '_usage_current',
'_function_hierarchic', 'fk_main_cover',
'fk_main_wastewater_node',
'detail_geometry_geometry'
]),
insert_ma=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='manhole',
table_alias='ma',
prefix='ma_',
remove_pkey=False,
indent=6,
skip_columns=['_orientation'],
remap_columns={'obj_id': 'obj_id'}),
insert_ss=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='special_structure',
table_alias='ss',
prefix='ss_',
remove_pkey=False,
indent=6,
remap_columns={'obj_id': 'obj_id'}),
insert_dp=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='discharge_point',
table_alias='dp',
prefix='dp_',
remove_pkey=False,
indent=6,
remap_columns={'obj_id': 'obj_id'}),
insert_ii=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='infiltration_installation',
table_alias='ii',
prefix='ii_',
remove_pkey=False,
indent=6,
remap_columns={'obj_id': 'obj_id'}),
insert_wn=insert_command(
pg_cur=cursor,
table_schema='qgep_od',
table_name='vw_wastewater_node',
table_type='view',
table_alias='wn',
prefix='wn_',
remove_pkey=False,
pkey='obj_id',
indent=6,
insert_values={
'identifier':
"COALESCE(NULLIF(NEW.wn_identifier,''), NEW.identifier)",
'situation_geometry':
'ST_GeometryN( NEW.situation_geometry, 1 )',
'last_modification': 'NOW()',
'fk_provider':
"COALESCE(NULLIF(NEW.wn_fk_provider,''), NEW.fk_provider)",
'fk_dataowner':
"COALESCE(NULLIF(NEW.wn_fk_dataowner,''), NEW.fk_dataowner)",
'fk_wastewater_structure': 'NEW.obj_id'
}),
insert_vw_cover=insert_command(
pg_cur=cursor,
table_schema='qgep_od',
table_name='vw_cover',
table_type='view',
table_alias='co',
prefix='co_',
remove_pkey=False,
pkey='obj_id',
indent=6,
remap_columns={'cover_shape': 'co_shape'},
insert_values={
'identifier':
"COALESCE(NULLIF(NEW.co_identifier,''), NEW.identifier)",
'situation_geometry':
'ST_GeometryN( NEW.situation_geometry, 1 )',
'last_modification': 'NOW()',
'fk_provider': 'NEW.fk_provider',
'fk_dataowner': 'NEW.fk_dataowner',
'fk_wastewater_structure': 'NEW.obj_id'
}))
print(trigger_insert_sql)
cursor.execute(trigger_insert_sql)
update_trigger_sql = """
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_wastewater_structure_UPDATE()
RETURNS trigger AS
$BODY$
DECLARE
dx float;
dy float;
BEGIN
{update_co}
{update_sp}
{update_ws}
{update_wn}
IF OLD.ws_type <> NEW.ws_type THEN
CASE
WHEN OLD.ws_type = 'manhole' THEN DELETE FROM qgep_od.manhole WHERE obj_id = OLD.obj_id;
WHEN OLD.ws_type = 'special_structure' THEN DELETE FROM qgep_od.special_structure WHERE obj_id = OLD.obj_id;
WHEN OLD.ws_type = 'discharge_point' THEN DELETE FROM qgep_od.discharge_point WHERE obj_id = OLD.obj_id;
WHEN OLD.ws_type = 'infiltration_installation' THEN DELETE FROM qgep_od.infiltration_installation WHERE obj_id = OLD.obj_id;
ELSE -- do nothing
END CASE;
CASE
WHEN NEW.ws_type = 'manhole' THEN INSERT INTO qgep_od.manhole (obj_id) VALUES(OLD.obj_id);
WHEN NEW.ws_type = 'special_structure' THEN INSERT INTO qgep_od.special_structure (obj_id) VALUES(OLD.obj_id);
WHEN NEW.ws_type = 'discharge_point' THEN INSERT INTO qgep_od.discharge_point (obj_id) VALUES(OLD.obj_id);
WHEN NEW.ws_type = 'infiltration_installation' THEN INSERT INTO qgep_od.infiltration_installation (obj_id) VALUES(OLD.obj_id);
ELSE -- do nothing
END CASE;
END IF;
CASE
WHEN NEW.ws_type = 'manhole' THEN
{update_ma}
WHEN NEW.ws_type = 'special_structure' THEN
{update_ss}
WHEN NEW.ws_type = 'discharge_point' THEN
{update_dp}
WHEN NEW.ws_type = 'infiltration_installation' THEN
{update_ii}
ELSE -- do nothing
END CASE;
-- Cover geometry has been moved
IF NOT ST_Equals( OLD.situation_geometry, NEW.situation_geometry) THEN
dx = ST_XMin(NEW.situation_geometry) - ST_XMin(OLD.situation_geometry);
dy = ST_YMin(NEW.situation_geometry) - ST_YMin(OLD.situation_geometry);
-- Move wastewater node as well
-- comment: TRANSLATE((ST_MakePoint(500, 900, 'NaN')), 10, 20, 0) would return NaN NaN NaN - so we have this workaround
UPDATE qgep_od.wastewater_node WN
SET situation_geometry = ST_SetSRID( ST_MakePoint(
ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation_geometry), ST_Y(WN.situation_geometry)), dx, dy )),
ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation_geometry), ST_Y(WN.situation_geometry)), dx, dy )),
ST_Z(WN.situation_geometry)), %(SRID)s )
WHERE obj_id IN
(
SELECT obj_id FROM qgep_od.wastewater_networkelement
WHERE fk_wastewater_structure = NEW.obj_id
);
-- Move covers
UPDATE qgep_od.cover CO
SET situation_geometry = ST_SetSRID( ST_MakePoint(
ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation_geometry), ST_Y(CO.situation_geometry)), dx, dy )),
ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation_geometry), ST_Y(CO.situation_geometry)), dx, dy )),
ST_Z(CO.situation_geometry)), %(SRID)s )
WHERE obj_id IN
(
SELECT obj_id FROM qgep_od.structure_part
WHERE fk_wastewater_structure = NEW.obj_id
);
-- Move reach(es) as well
UPDATE qgep_od.reach RE
SET progression_geometry =
ST_ForceCurve (ST_SetPoint(
ST_CurveToLine (RE.progression_geometry ),
0, -- SetPoint index is 0 based, PointN index is 1 based.
ST_SetSRID( ST_MakePoint(
ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression_geometry, 1)), ST_Y(ST_PointN(RE.progression_geometry, 1))), dx, dy )),
ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression_geometry, 1)), ST_Y(ST_PointN(RE.progression_geometry, 1))), dx, dy )),
ST_Z(ST_PointN(RE.progression_geometry, 1))), %(SRID)s )
) )
WHERE fk_reach_point_from IN
(
SELECT RP.obj_id FROM qgep_od.reach_point RP
LEFT JOIN qgep_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
WHERE NE.fk_wastewater_structure = NEW.obj_id
);
UPDATE qgep_od.reach RE
SET progression_geometry =
ST_ForceCurve( ST_SetPoint(
ST_CurveToLine( RE.progression_geometry ),
ST_NumPoints(RE.progression_geometry) - 1,
ST_SetSRID( ST_MakePoint(
ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression_geometry)), ST_Y(ST_EndPoint(RE.progression_geometry))), dx, dy )),
ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression_geometry)), ST_Y(ST_EndPoint(RE.progression_geometry))), dx, dy )),
ST_Z(ST_PointN(RE.progression_geometry, 1))), %(SRID)s )
) )
WHERE fk_reach_point_to IN
(
SELECT RP.obj_id FROM qgep_od.reach_point RP
LEFT JOIN qgep_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
WHERE NE.fk_wastewater_structure = NEW.obj_id
);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS vw_qgep_wastewater_structure_UPDATE ON qgep_od.vw_qgep_wastewater_structure;
CREATE TRIGGER vw_qgep_wastewater_structure_UPDATE INSTEAD OF UPDATE ON qgep_od.vw_qgep_wastewater_structure
FOR EACH ROW EXECUTE PROCEDURE qgep_od.ft_vw_qgep_wastewater_structure_UPDATE();
""".format(
update_co=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='cover',
table_alias='co',
prefix='co_',
indent=6,
skip_columns=['situation_geometry'],
remap_columns={'cover_shape': 'co_shape'}),
update_sp=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='structure_part',
table_alias='sp',
prefix='co_',
indent=6,
skip_columns=['fk_wastewater_structure'],
update_values={
'last_modification':
'NEW.last_modification',
'fk_dataowner': 'NEW.fk_dataowner',
'fk_provider': 'NEW.fk_provider'
}),
update_ws=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
table_alias='ws',
remove_pkey=False,
indent=6,
skip_columns=[
'detail_geometry_geometry',
'last_modification', '_usage_current',
'_function_hierarchic', '_label',
'fk_main_cover',
'fk_main_wastewater_node', '_depth'
],
update_values={}),
update_ma=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='manhole',
table_alias='ws',
prefix='ma_',
remove_pkey=True,
indent=6,
skip_columns=['_orientation'],
remap_columns={'obj_id': 'obj_id'}),
update_ss=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='special_structure',
table_alias='ss',
prefix='ss_',
remove_pkey=True,
indent=6,
skip_columns=[],
remap_columns={'obj_id': 'obj_id'}),
update_dp=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='discharge_point',
table_alias='dp',
prefix='dp_',
remove_pkey=True,
indent=6,
skip_columns=[],
remap_columns={'obj_id': 'obj_id'}),
update_ii=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='infiltration_installation',
table_alias='ii',
prefix='ii_',
remove_pkey=True,
indent=6,
skip_columns=[],
remap_columns={'obj_id': 'obj_id'}),
update_wn=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_node',
table_alias='wn',
prefix='wn_',
indent=6,
skip_columns=['situation_geometry']))
cursor.execute(update_trigger_sql, variables)
trigger_delete_sql = """
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_wastewater_structure_DELETE()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
DELETE FROM qgep_od.wastewater_structure WHERE obj_id = OLD.obj_id;
RETURN OLD;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
DROP TRIGGER IF EXISTS vw_qgep_wastewater_structure_DELETE ON qgep_od.vw_qgep_wastewater_structure;
CREATE TRIGGER vw_qgep_wastewater_structure_DELETE INSTEAD OF DELETE ON qgep_od.vw_qgep_wastewater_structure
FOR EACH ROW EXECUTE PROCEDURE qgep_od.ft_vw_qgep_wastewater_structure_DELETE();
"""
cursor.execute(trigger_delete_sql, variables)
extras = """
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','wastewater_structure');
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER co_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','structure_part');
ALTER VIEW qgep_od.vw_qgep_wastewater_structure ALTER wn_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','wastewater_node');
"""
cursor.execute(extras)
conn.commit()
conn.close()
def vw_sign_symbol(srid: int, pg_service: str = None):
"""
Creates sign_symbol view
:param srid: EPSG code for geometries
:param pg_service: the PostgreSQL service name
"""
if not pg_service:
pg_service = os.getenv('PGSERVICE')
assert pg_service
variables = {'SRID': int(srid)}
conn = psycopg2.connect("service={0}".format(pg_service))
cursor = conn.cursor()
view_sql = """
CREATE OR REPLACE VIEW siro_od.vw_sign_symbol AS
WITH joined_tables AS (
SELECT
{sign_columns}
, azimut.azimut
, {frame_columns}
, sign.rank AS sign_rank
, support.id AS support_id
, support.geometry::geometry(Point,%(SRID)s) AS support_geometry
, official_sign.value_de as _symbol_value_de
, official_sign.value_fr as _symbol_value_fr
, official_sign.value_it as _symbol_value_it
, official_sign.value_ro as _symbol_value_ro
, official_sign.img_de as _img_de
, official_sign.img_fr as _img_fr
, official_sign.img_it as _img_it
, official_sign.img_ro as _img_ro
, official_sign.img_height as _symbol_height
, official_sign.img_width as _symbol_width
FROM siro_od.sign
LEFT JOIN siro_od.frame ON frame.id = sign.fk_frame
LEFT JOIN siro_od.azimut ON azimut.id = frame.fk_azimut
LEFT JOIN siro_od.support ON support.id = azimut.fk_support
LEFT JOIN siro_vl.official_sign ON official_sign.id = sign.fk_official_sign
),
ordered_recto_signs AS (
SELECT
joined_tables.*
, ROW_NUMBER () OVER ( PARTITION BY support_id, azimut ORDER BY frame_rank, sign_rank ) AS _final_rank
FROM joined_tables
WHERE verso IS FALSE
ORDER BY support_id, azimut, _final_rank
),
ordered_shifted_recto_signs AS (
SELECT
ordered_recto_signs.*
, SUM( _symbol_height ) OVER ( PARTITION BY support_id, azimut ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS _symbol_shift
, NULLIF(FIRST_VALUE(id) OVER (PARTITION BY support_id, azimut, frame_rank ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ), id) AS _previous_sign_in_frame
, NULLIF(LAST_VALUE(id) OVER ( PARTITION BY support_id, azimut, frame_rank ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ), id) AS _next_sign_in_frame
, NULLIF(FIRST_VALUE(frame_id) OVER ( PARTITION BY support_id, azimut ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ), frame_id) AS _previous_frame
, NULLIF(LAST_VALUE(frame_id) OVER ( PARTITION BY support_id, azimut ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ), frame_id) AS _next_frame
FROM
ordered_recto_signs
ORDER BY
support_id, azimut, _final_rank
)
SELECT * FROM ordered_shifted_recto_signs
UNION
SELECT jt.*, osrs._final_rank, osrs._symbol_shift, NULL::uuid AS previous_sign_in_frame, NULL::uuid AS next_sign_in_frame, NULL::uuid AS previous_frame, NULL::uuid AS next_frame
FROM joined_tables jt
LEFT JOIN ordered_shifted_recto_signs osrs ON osrs.support_id = jt.support_id AND osrs.frame_id = jt.frame_id AND jt.sign_rank = osrs.sign_rank
WHERE jt.verso IS TRUE
;
ALTER VIEW siro_od.vw_sign_symbol ALTER verso SET DEFAULT false;
""".format(
sign_columns=select_columns(pg_cur=cursor,
table_schema='siro_od',
table_name='sign',
remove_pkey=False,
indent=4,
skip_columns=['rank', 'fk_frame']),
frame_columns=select_columns(pg_cur=cursor,
table_schema='siro_od',
table_name='frame',
remove_pkey=False,
indent=4,
prefix='frame_'),
vl_official_sign_columns=select_columns(pg_cur=cursor,
table_schema='siro_vl',
table_name='official_sign',
remove_pkey=False,
indent=4,
prefix='vl_official_sign_'),
)
trigger_insert_sql = """
CREATE OR REPLACE FUNCTION siro_od.ft_vw_sign_symbol_INSERT()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.frame_id IS NULL THEN
{insert_frame}
END IF;
{insert_sign}
RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
DROP TRIGGER IF EXISTS vw_sign_symbol_INSERT ON siro_od.vw_sign_symbol;
CREATE TRIGGER vw_sign_symbol_INSERT INSTEAD OF INSERT ON siro_od.vw_sign_symbol
FOR EACH ROW EXECUTE PROCEDURE siro_od.ft_vw_sign_symbol_INSERT();
""".format(insert_frame=insert_command(pg_cur=cursor,
table_schema='siro_od',
table_name='frame',
remove_pkey=True,
indent=4,
skip_columns=[],
returning='id INTO NEW.frame_id',
prefix='frame_'),
insert_sign=insert_command(pg_cur=cursor,
table_schema='siro_od',
table_name='sign',
remove_pkey=True,
indent=4,
skip_columns=[],
remap_columns={
'fk_frame': 'frame_id',
'rank': 'sign_rank'
},
returning='id INTO NEW.id'))
trigger_update_sql = """
CREATE OR REPLACE FUNCTION siro_od.ft_vw_siro_sign_symbol_UPDATE()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
{update_sign}
{update_frame}
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS ft_vw_siro_sign_symbol_UPDATE ON siro_od.vw_sign_symbol;
CREATE TRIGGER vw_sign_symbol_UPDATE INSTEAD OF UPDATE ON siro_od.vw_sign_symbol
FOR EACH ROW EXECUTE PROCEDURE siro_od.ft_vw_siro_sign_symbol_UPDATE();
""".format(update_sign=update_command(pg_cur=cursor,
table_schema='siro_od',
table_name='sign',
indent=4,
skip_columns=[],
remap_columns={
'fk_frame': 'frame_id',
'rank': 'sign_rank'
}),
update_frame=update_command(pg_cur=cursor,
table_schema='siro_od',
table_name='frame',
prefix='frame_',
indent=4,
skip_columns=[],
remap_columns={}))
trigger_delete_sql = """
CREATE OR REPLACE FUNCTION siro_od.ft_vw_sign_symbol_DELETE()
RETURNS trigger AS
$BODY$
DECLARE
_sign_count integer;
BEGIN
DELETE FROM siro_od.sign WHERE id = OLD.id;
SELECT count(id) INTO _sign_count FROM siro_od.sign WHERE fk_frame = OLD.frame_id;
IF _sign_count = 0 THEN
DELETE FROM siro_od.frame WHERE id = OLD.frame_id;
END IF;
RETURN OLD;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
DROP TRIGGER IF EXISTS vw_sign_symbol_DELETE ON siro_od.vw_sign_symbol;
CREATE TRIGGER vw_sign_symbol_DELETE INSTEAD OF DELETE ON siro_od.vw_sign_symbol
FOR EACH ROW EXECUTE PROCEDURE siro_od.ft_vw_sign_symbol_DELETE();
"""
for sql in (view_sql, trigger_insert_sql, trigger_update_sql,
trigger_delete_sql):
try:
cursor.execute(sql, variables)
except psycopg2.Error as e:
print("*** Failing:\n{}\n***".format(sql))
raise e
conn.commit()
conn.close()
def vw_qgep_reach(pg_service: str = None,
extra_definition: dict = None):
"""
Creates qgep_reach view
:param pg_service: the PostgreSQL service name
:param extra_definition: a dictionary for additional read-only columns
"""
if not pg_service:
pg_service = os.getenv('PGSERVICE')
assert pg_service
extra_definition = extra_definition or {}
conn = psycopg2.connect("service={0}".format(pg_service))
cursor = conn.cursor()
view_sql = """
DROP VIEW IF EXISTS qgep_od.vw_qgep_reach;
CREATE OR REPLACE VIEW qgep_od.vw_qgep_reach AS
SELECT
re.obj_id,
re.clear_height,
re.material,
ch.usage_current AS ch_usage_current,
ch.function_hierarchic AS ch_function_hierarchic,
ws.status AS ws_status,
ws.fk_owner AS ws_fk_owner,
ch.function_hydraulic AS ch_function_hydraulic,
CASE
WHEN pp.height_width_ratio IS NOT NULL THEN round(re.clear_height::numeric * pp.height_width_ratio)::smallint
ELSE clear_height
END AS width,
CASE
WHEN rp_from.level > 0 AND rp_to.level > 0 THEN round((rp_from.level - rp_to.level)/re.length_effective*1000,1)
ELSE NULL
END AS _slope_per_mill,
{extra_cols}
{re_cols},
{ne_cols},
{ch_cols},
{ws_cols},
{rp_from_cols},
{rp_to_cols}
FROM qgep_od.reach re
LEFT JOIN qgep_od.wastewater_networkelement ne ON ne.obj_id = re.obj_id
LEFT JOIN qgep_od.reach_point rp_from ON rp_from.obj_id = re.fk_reach_point_from
LEFT JOIN qgep_od.reach_point rp_to ON rp_to.obj_id = re.fk_reach_point_to
LEFT JOIN qgep_od.wastewater_structure ws ON ne.fk_wastewater_structure = ws.obj_id
LEFT JOIN qgep_od.channel ch ON ch.obj_id = ws.obj_id
LEFT JOIN qgep_od.pipe_profile pp ON re.fk_pipe_profile = pp.obj_id
{extra_joins};
""".format(extra_cols='\n '.join([select_columns(pg_cur=cursor,
table_schema=table_parts(table_def['table'])[0],
table_name=table_parts(table_def['table'])[1],
skip_columns=table_def.get('skip_columns', []),
remap_columns=table_def.get('remap_columns', {}),
prefix=table_def.get('prefix', None),
table_alias=table_def.get('alias', None)
) + ','
for table_def in extra_definition.get('joins', {}).values()]),
re_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
table_alias='re',
remove_pkey=True,
indent=4,
skip_columns=['clear_height', 'material',
'fk_reach_point_from', 'fk_reach_point_to']),
ne_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
table_alias='ne',
remove_pkey=True,
indent=4),
ch_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
table_alias='ch',
prefix='ch_',
remove_pkey=True,
indent=4,
skip_columns=['usage_current', 'function_hierarchic', 'function_hydraulic']),
ws_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
table_alias='ws',
prefix='ws_',
remove_pkey=False,
indent=4,
skip_columns=['detail_geometry_geometry', 'status', 'fk_owner',
'fk_dataowner', 'fk_provider', '_usage_current',
'_function_hierarchic', '_label', '_depth', 'fk_main_cover']),
rp_from_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
table_alias='rp_from',
prefix='rp_from_',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry']),
rp_to_cols=select_columns(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
table_alias='rp_to',
prefix='rp_to_',
remove_pkey=False,
indent=4,
skip_columns=['situation_geometry']),
extra_joins='\n '.join(['LEFT JOIN {tbl} {alias} ON {jon}'.format(tbl=table_def['table'],
alias=table_def.get('alias', ''),
jon=table_def['join_on'])
for table_def in extra_definition.get('joins', {}).values()])
)
cursor.execute(view_sql)
trigger_insert_sql="""
-- REACH INSERT
-- Function: vw_qgep_reach_insert()
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_reach_insert()
RETURNS trigger AS
$BODY$
BEGIN
-- Synchronize geometry with level
NEW.progression_geometry = ST_SetPoint(
NEW.progression_geometry,
0,
ST_MakePoint(
ST_X(ST_StartPoint(NEW.progression_geometry)),
ST_Y(ST_StartPoint(NEW.progression_geometry)),
COALESCE(NEW.rp_from_level,'NaN')));
NEW.progression_geometry = ST_SetPoint(
NEW.progression_geometry,
ST_NumPoints(NEW.progression_geometry)-1,
ST_MakePoint(
ST_X(ST_EndPoint(NEW.progression_geometry)),
ST_Y(ST_EndPoint(NEW.progression_geometry)),
COALESCE(NEW.rp_to_level,'NaN')));
{rp_from}
{rp_to}
{ws}
{ch}
{ne}
{re}
RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER vw_qgep_reach_insert INSTEAD OF INSERT ON qgep_od.vw_qgep_reach
FOR EACH ROW EXECUTE PROCEDURE qgep_od.ft_vw_qgep_reach_insert();
""".format(rp_from=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_from_',
remove_pkey=False,
indent=2,
skip_columns=[],
coalesce_pkey_default=True,
insert_values={'situation_geometry': 'ST_StartPoint(NEW.progression_geometry)'},
returning='obj_id INTO NEW.rp_from_obj_id'),
rp_to=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_to_',
remove_pkey=False,
indent=2,
skip_columns=[],
coalesce_pkey_default=True,
insert_values={'situation_geometry': 'ST_EndPoint(NEW.progression_geometry)'},
returning='obj_id INTO NEW.rp_to_obj_id'),
ws=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
prefix='ws_',
remove_pkey=False,
indent=2,
insert_values={'obj_id': "COALESCE(NEW.fk_wastewater_structure, qgep_sys.generate_oid('qgep_od','channel'))"},
returning='obj_id INTO NEW.fk_wastewater_structure',
skip_columns=['detail_geometry_geometry', 'fk_dataowner', 'fk_provider',
'_usage_current', '_function_hierarchic', '_label', '_depth', 'fk_main_cover']),
ch=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
prefix='ch_',
remove_pkey=False,
indent=2,
remap_columns={'obj_id': 'fk_wastewater_structure'},
skip_columns=[]),
ne=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
remove_pkey=False,
indent=2,
insert_values={'obj_id': "COALESCE(NEW.obj_id,qgep_sys.generate_oid('qgep_od','reach'))"},
returning='obj_id INTO NEW.obj_id'),
re=insert_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
remove_pkey=False,
indent=2,
insert_values={'fk_reach_point_from': 'NEW.rp_from_obj_id',
'fk_reach_point_to': 'NEW.rp_to_obj_id'}),
)
cursor.execute(trigger_insert_sql)
trigger_update_sql="""
CREATE OR REPLACE FUNCTION qgep_od.ft_vw_qgep_reach_update()
RETURNS trigger AS
$BODY$
BEGIN
-- Synchronize geometry with level
IF NEW.rp_from_level <> OLD.rp_from_level OR
(NEW.rp_from_level IS NULL AND OLD.rp_from_level IS NOT NULL) OR
(NEW.rp_from_level IS NOT NULL AND OLD.rp_from_level IS NULL) THEN
NEW.progression_geometry = ST_SetPoint(
NEW.progression_geometry,
0,
ST_MakePoint(
ST_X(ST_StartPoint(NEW.progression_geometry)),
ST_Y(ST_StartPoint(NEW.progression_geometry)),
COALESCE(NEW.rp_from_level,'NaN')));
ELSE
IF ST_Z(ST_StartPoint(NEW.progression_geometry)) <> ST_Z(ST_StartPoint(OLD.progression_geometry)) THEN
NEW.rp_from_level = NULLIF(ST_Z(ST_StartPoint(NEW.progression_geometry)),'NaN');
END IF;
END IF;
-- Synchronize geometry with level
IF NEW.rp_to_level <> OLD.rp_to_level OR
(NEW.rp_to_level IS NULL AND OLD.rp_to_level IS NOT NULL) OR
(NEW.rp_to_level IS NOT NULL AND OLD.rp_to_level IS NULL) THEN
NEW.progression_geometry = ST_SetPoint(
NEW.progression_geometry,
ST_NumPoints(NEW.progression_geometry)-1,
ST_MakePoint(
ST_X(ST_EndPoint(NEW.progression_geometry)),
ST_Y(ST_EndPoint(NEW.progression_geometry)),
COALESCE(NEW.rp_to_level,'NaN')));
ELSE
IF ST_Z(ST_EndPoint(NEW.progression_geometry)) <> ST_Z(ST_EndPoint(OLD.progression_geometry)) THEN
NEW.rp_to_level = NULLIF(ST_Z(ST_EndPoint(NEW.progression_geometry)),'NaN');
END IF;
END IF;
{rp_from}
{rp_to}
{ch}
{ws}
{ne}
{re}
RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE;
""".format(rp_from=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_from_',
remove_pkey=True,
indent=6,
update_values={'situation_geometry': 'ST_StartPoint(NEW.progression_geometry)'}),
rp_to=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach_point',
prefix='rp_to_',
remove_pkey=True,
indent=6,
update_values={'situation_geometry': 'ST_EndPoint(NEW.progression_geometry)'}),
ch=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='channel',
prefix='ch_',
remove_pkey=True,
indent=6,
remap_columns={'obj_id': 'fk_wastewater_structure'}),
ws=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_structure',
prefix='ws_',
remove_pkey=True,
indent=6,
remap_columns={'obj_id': 'fk_wastewater_structure',
'fk_dataowner': 'fk_dataowner',
'fk_provider': 'fk_provider',
'last_modification': 'last_modification'},
skip_columns=['detail_geometry_geometry', '_usage_current', '_function_hierarchic',
'_label', '_depth', 'fk_main_cover']),
ne=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='wastewater_networkelement',
remove_pkey=True,
indent=6),
re=update_command(pg_cur=cursor,
table_schema='qgep_od',
table_name='reach',
remove_pkey=True,
indent=6,
skip_columns=['fk_reach_point_to', 'fk_reach_point_from']),
)
cursor.execute(trigger_update_sql)
trigger_delete_sql="""
CREATE TRIGGER vw_qgep_reach_update
INSTEAD OF UPDATE
ON qgep_od.vw_qgep_reach
FOR EACH ROW
EXECUTE PROCEDURE qgep_od.ft_vw_qgep_reach_update();
-- REACH DELETE
-- Rule: vw_qgep_reach_delete()
CREATE OR REPLACE RULE vw_qgep_reach_delete AS ON DELETE TO qgep_od.vw_qgep_reach DO INSTEAD (
DELETE FROM qgep_od.reach WHERE obj_id = OLD.obj_id;
);
"""
cursor.execute(trigger_delete_sql)
extras = """
ALTER VIEW qgep_od.vw_qgep_reach ALTER obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach');
ALTER VIEW qgep_od.vw_qgep_reach ALTER rp_from_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach_point');
ALTER VIEW qgep_od.vw_qgep_reach ALTER rp_to_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od','reach_point');
ALTER VIEW qgep_od.vw_qgep_reach ALTER fk_wastewater_structure SET DEFAULT qgep_sys.generate_oid('qgep_od','channel');
"""
cursor.execute(extras)
conn.commit()
conn.close()