def __init__(self, parent_table: str, child_table: str,
pg_service: str = None,
view_schema: str = None,
view_name: str = None,
pkey_default_value: bool = False,
inner_defaults: dict = {}):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
pg_service
if not given, it is determined using environment variable PGSERVICE
parent_table
the parent table, can be schema specified
child_table
the child table, can be schema specified
view_schema
the schema where the view will written to
view_name
the name of the created view, defaults to vw_{parent_table}_{join_table}
pkey_default_value
the primary key column of the view will have a default value according to the child primary key table
inner_defaults
dictionary of other columns to default to in case the provided value is null or empty
"""
if pg_service is None:
pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(pg_service))
self.cursor = self.conn.cursor()
self.pkey_default_value = pkey_default_value
self.inner_defaults = inner_defaults
(self.parent_schema, self.parent_table) = table_parts(parent_table)
(self.child_schema, self.child_table) = table_parts(child_table)
if view_schema is None:
if self.parent_schema != self.child_schema:
raise ValueError('Destination schema cannot be guessed if different on sources tables.')
else:
self.view_schema = self.parent_schema
else:
self.view_schema = view_schema
self.view_name = view_name or "vw_{pt}_{ct}".format(pt=self.parent_table, ct=self.child_table)
(self.ref_parent_key, parent_referenced_key) = reference_columns(self.cursor,
self.child_schema, self.child_table,
self.parent_schema, self.parent_table)
try:
self.child_pkey = primary_key(self.cursor, self.child_schema, self.child_table)
except TableHasNoPrimaryKey:
self.child_pkey = self.ref_parent_key
self.parent_pkey = primary_key(self.cursor, self.parent_schema, self.parent_table)
assert self.parent_pkey == parent_referenced_key
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 __init__(self,
definition: dict,
pg_service: str = None,
variables: dict = {},
create_joins: bool = False,
drop: bool = False):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
definition
the YAML definition of the multiple inheritance
pg_service
if not given, it is determined using environment variable PGSERVICE
variables
dictionary for variables to be used in SQL deltas ( name => value )
create_joins
if True, simple joins will be created for all joined tables
drop
if True, will drop any existing view, type or trigger that will be created later
"""
self.variables = variables
self.create_joins = create_joins
self.drop = drop
self.pg_service = pg_service
if self.pg_service is None:
self.pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(self.pg_service))
self.cursor = self.conn.cursor()
# check definition validity
for key in definition.keys():
if key not in ('table', 'view_schema', 'view_name', 'alias',
'short_alias', 'type_name', 'joins',
'insert_trigger', 'update_trigger',
'allow_type_change', 'allow_parent_only',
'additional_columns', 'additional_joins',
'merge_columns', 'merge_geometry_columns',
'pkey_default_value'):
raise InvalidDefinition('key {k} is not a valid'.format(k=key))
# check joins validity
for alias, table_def in definition['joins'].items():
for key in table_def.keys():
if key not in ('table', 'short_alias', 'skip_columns', 'fkey',
'remap_columns', 'prefix', 'insert_values',
'update_values'):
raise InvalidDefinition(
'in join {a} key "{k}" is not valid'.format(a=alias,
k=key))
for mandatory_key in ['joins']:
if mandatory_key not in definition:
raise InvalidDefinition(
'Missing key: "{k}" should be provided.'.format(
k=mandatory_key))
# check trigger modifiers validity
for trigger in ('insert_trigger', 'update_trigger'):
for key in definition.get(trigger, {}):
if key not in ('declare', 'pre', 'post'):
raise InvalidDefinition(
'key {k} is not valid in trigger definitions'.format(
k=key))
(self.master_schema,
self.master_table) = table_parts(definition.get('table', None))
self.master_skip_colums = definition.get('skip_columns', [])
self.master_prefix = definition.get('prefix', None)
self.master_remap_columns = definition.get('remap_columns', {})
self.pkey_default_value = definition.get('pkey_default_value', False)
# global options:
self.view_schema = definition.get('view_schema', self.master_schema)
self.view_name = definition.get(
'view_name', "vw_merge_{t}".format(t=self.master_table))
self.view_alias = definition.get('alias', self.master_table)
self.short_alias = definition.get('short_alias', self.view_alias)
self.type_name = definition.get('type_name',
'{al}_type'.format(al=self.view_alias))
self.insert_trigger = definition.get('insert_trigger', {})
self.update_trigger = definition.get('update_trigger', {})
self.allow_parent_only = definition.get('allow_parent_only', True)
self.allow_type_change = definition.get('allow_type_change', True)
self.additional_joins = definition.get('additional_joins', None)
self.additional_columns = definition.get('additional_columns', {})
try:
self.master_pkey = primary_key(self.cursor, self.master_schema,
self.master_table)
except TableHasNoPrimaryKey:
raise TableHasNoPrimaryKey(
'{vn} has no primary key, specify it with "key"'.format(
vn=self.view_alias))
# parse the joins definition
self.joins = definition['joins']
self.joined_ref_master_key = []
for alias, table_def in self.joins.items():
(table_def['table_schema'],
table_def['table_name']) = table_parts(table_def['table'])
table_def['short_alias'] = table_def.get('short_alias', alias)
if 'fkey' in table_def:
table_def['ref_master_key'] = table_def['fkey']
else:
table_def['ref_master_key'] = reference_columns(
self.cursor, table_def['table_schema'],
table_def['table_name'], self.master_schema,
self.master_table)[0]
try:
table_def['pkey'] = primary_key(self.cursor,
table_def['table_schema'],
table_def['table_name'])
except TableHasNoPrimaryKey:
table_def['pkey'] = table_def['ref_master_key']
# pre-process merged columns
self.merge_column_cast = {}
# for geometry columns, we need to get the type to cast the NULL value
merge_geometry_columns = definition.get('merge_geometry_columns', [])
for col in merge_geometry_columns:
for table_def in self.joins.values():
gt = geometry_type(self.cursor, table_def['table_schema'],
table_def['table_name'], col)
if gt:
self.merge_column_cast[
col] = '::geometry({type},{srid})'.format(type=gt[0],
srid=gt[1])
break
if col not in self.merge_column_cast:
raise InvalidDefinition(
'There is no geometry column "{col}" in joined tables'.
format(col=col))
self.merge_columns = definition.get('merge_columns',
[]) + merge_geometry_columns
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 __init__(self, definition: dict, pg_service: str = None):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
definition
the YAML definition of the multiple inheritance
pg_service
if not given, it is determined using environment variable PGSERVICE
"""
# check definition validity
for key in definition.keys():
if key not in ('table', 'view_schema', 'joins', 'pkey',
'view_name'):
raise InvalidDefinition('key {k} is not a valid'.format(k=key))
# check joins validity
for alias, table_def in definition['joins'].items():
for key in table_def.keys():
if key not in ('table', 'short_alias', 'fkey', 'prefix',
'skip_columns', 'remap_columns'):
raise InvalidDefinition(
'in join {a} key "{k}" is not valid'.format(a=alias,
k=key))
if pg_service is None:
pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(pg_service))
self.cursor = self.conn.cursor()
(self.parent_schema,
self.parent_table) = table_parts(definition['table'])
self.view_schema = definition.get('view_schema', self.parent_schema)
self.view_name = definition.get('view_name',
"vw_{pt}".format(pt=self.parent_table))
try:
self.parent_pkey = primary_key(self.cursor, self.parent_schema,
self.parent_table)
except TableHasNoPrimaryKey:
self.parent_pkey = definition['pkey']
class Table:
def __init__(self):
self.schema_name = None
self.table_name = None
self.pkey = None
self.ref_parent_key = None
self.parent_referenced_key = None
self.skip_columns = None
self.remap_columns = None
self.prefix = None
self.child_tables = {}
for alias, table_def in definition['joins'].items():
child = Table()
(child.schema_name,
child.table_name) = table_parts(table_def['table'])
child.pkey = primary_key(self.cursor, child.schema_name,
child.table_name)
try:
(child.parent_referenced_key,
child.ref_parent_key) = reference_columns(
self.cursor, self.parent_schema, self.parent_table,
child.schema_name, child.table_name)
assert child.pkey == child.ref_parent_key
except NoReferenceFound:
child.parent_referenced_key = table_def['fkey']
child.skip_columns = table_def.get('skip_columns', {})
child.remap_columns = table_def.get('remap_columns', {})
child.prefix = table_def.get('prefix', None)
self.child_tables[alias] = child
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()