def create_views(srid: int,
pg_service: str,
qgep_reach_extra: str = None,
qgep_wastewater_structure_extra: str = None):
"""
Creates the views for QGEP
:param srid: the EPSG code for geometry columns
:param pg_service: the PostgreSQL service, if not given it will be determined from environment variable in Pirogue
:param qgep_reach_extra: YAML file path of the definition of additional columns for vw_qgep_reach view
:param qgep_wastewater_structure_extra: YAML file path of the definition of additional columns for vw_qgep_wastewater_structure_extra view"""
variables = {'SRID': srid}
# open YAML files
if qgep_reach_extra:
qgep_reach_extra = safe_load(open(qgep_reach_extra))
if qgep_wastewater_structure_extra:
qgep_wastewater_structure_extra = safe_load(open(qgep_wastewater_structure_extra))
run_sql('view/drop_views.sql', pg_service, variables)
SingleInheritance('qgep_od.structure_part', 'qgep_od.access_aid', view_name='vw_access_aid', pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part', 'qgep_od.benching', view_name='vw_benching', pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part', 'qgep_od.backflow_prevention', view_name='vw_backflow_prevention', pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part', 'qgep_od.cover', view_name='vw_cover', pkey_default_value=True, pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part', 'qgep_od.dryweather_downspout', view_name='vw_dryweather_downspout', pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part', 'qgep_od.dryweather_flume', view_name='vw_dryweather_flume', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure', 'qgep_od.channel', view_name='vw_channel', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure', 'qgep_od.manhole', view_name='vw_manhole', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure', 'qgep_od.discharge_point', view_name='vw_discharge_point', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure', 'qgep_od.special_structure', view_name='vw_special_structure', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_networkelement', 'qgep_od.reach', view_name='vw_reach', pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_networkelement', 'qgep_od.wastewater_node', view_name='vw_wastewater_node', pg_service=pg_service).create()
MultipleInheritance(safe_load(open("view/vw_maintenance_examination.yaml")), drop=True, pg_service=pg_service).create()
MultipleInheritance(safe_load(open("view/vw_damage.yaml")), drop=True, pg_service=pg_service).create()
vw_qgep_wastewater_structure(srid, pg_service=pg_service, extra_definition=qgep_wastewater_structure_extra)
vw_qgep_reach(pg_service=pg_service, extra_definition=qgep_reach_extra)
run_sql('view/vw_file.sql', pg_service, variables)
MultipleInheritance(safe_load(open("view/vw_oo_overflow.yaml")), create_joins=True, variables=variables, pg_service=pg_service, drop=True).create()
MultipleInheritance(safe_load(open("view/vw_oo_organisation.yaml")), drop=True, pg_service=pg_service).create()
run_sql('view/vw_catchment_area_connections.sql', pg_service, variables)
run_sql('view/vw_change_points.sql', pg_service, variables)
run_sql('view/vw_qgep_import.sql', pg_service, variables)
def test_insert_update_delete(self):
yaml_definition = yaml.safe_load(
open("test/multiple_inheritance.yaml"))
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
# insert
self.cur.execute(
"INSERT INTO pirogue_test.vw_merge_animal (animal_type,name,year,fk_cat_breed,eye_color) VALUES ('cat','felix',1985,2,'black');"
)
self.cur.execute(
"SELECT animal_type, year, fk_cat_breed FROM pirogue_test.vw_merge_animal WHERE name = 'felix';"
)
res = self.cur.fetchone()
self.assertEqual(res[0], 'cat')
self.assertEqual(res[1], 1985)
self.assertEqual(res[2], 2)
# update
self.cur.execute(
"SELECT * FROM pirogue_test.cat WHERE eye_color = 'black';")
self.assertIsNotNone(self.cur.fetchone())
self.cur.execute(
"UPDATE pirogue_test.vw_merge_animal SET animal_type = 'dog' WHERE name = 'felix';"
)
self.cur.execute(
"SELECT * FROM pirogue_test.cat WHERE eye_color = 'black';")
self.assertIsNone(self.cur.fetchone())
# delete
self.cur.execute(
"SELECT * FROM pirogue_test.vw_merge_animal WHERE name = 'felix';")
self.assertIsNotNone(self.cur.fetchone())
self.cur.execute(
"DELETE FROM pirogue_test.vw_merge_animal WHERE name = 'felix';")
self.cur.execute(
"SELECT * FROM pirogue_test.vw_merge_animal WHERE name = 'felix';")
self.assertIsNone(self.cur.fetchone())
def test_merge_no_columns(self):
yaml_definition = yaml.safe_load(
open("test/multiple_inheritance.yaml"))
yaml_definition['joins']['aardvark']['skip_columns'] = [
'aid', 'father'
]
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
def test_pkey_default_value(self):
yaml_definition = yaml.safe_load(
open("test/multiple_inheritance.yaml"))
yaml_definition['pkey_default_value'] = True
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
self.assertEqual(
default_value(self.cur, 'pirogue_test', 'animal', 'aid'),
default_value(self.cur, 'pirogue_test', 'vw_merge_animal', 'aid'))
def test_invalid_definition(self):
yaml_definition = yaml.safe_load(open("test/multiple_inheritance.yaml"))
yaml_definition['MyBadKey'] = 'Ouch'
error_caught = False
try:
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
except InvalidDefinition:
error_caught = True
self.assertTrue(error_caught)
def test_type_change_not_allowed(self):
yaml_definition = yaml.safe_load(open("test/multiple_inheritance.yaml"))
yaml_definition['view_name'] = 'vw_animal_no_type_change'
yaml_definition['allow_type_change'] = False
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
self.cur.execute("INSERT INTO pirogue_test.vw_animal_no_type_change (animal_type,name,year,fk_cat_breed,eye_color) VALUES ('dog','albert',1933,2,'yellow');")
error_caught = False
try:
self.cur.execute("UPDATE pirogue_test.vw_animal_no_type_change SET animal_type = 'cat';")
except psycopg2.errors.RaiseException:
error_caught = True
self.assertTrue(error_caught)
def test_based_on_view(self):
yaml_definition = yaml.safe_load(open("test/multiple_inheritance.yaml"))
MultipleInheritance(yaml_definition, pg_service=pg_service).create()
yaml_definition = yaml.safe_load(open("test/simple_joins_based_on_view.yaml"))
SimpleJoins(yaml_definition, pg_service=pg_service).create()
def create_views(srid: int,
pg_service: str,
qgep_reach_extra: str = None,
qgep_wastewater_structure_extra: str = None):
"""
Creates the views for QGEP
:param srid: the EPSG code for geometry columns
:param pg_service: the PostgreSQL service, if not given it will be determined from environment variable in Pirogue
:param qgep_reach_extra: YAML file path of the definition of additional columns for vw_qgep_reach view
:param qgep_wastewater_structure_extra: YAML file path of the definition of additional columns for vw_qgep_wastewater_structure_extra view"""
variables = {'SRID': srid}
# open YAML files
if qgep_reach_extra:
qgep_reach_extra = safe_load(open(qgep_reach_extra))
if qgep_wastewater_structure_extra:
qgep_wastewater_structure_extra = safe_load(
open(qgep_wastewater_structure_extra))
run_sql('view/drop_views.sql', pg_service, variables)
SingleInheritance('qgep_od.structure_part',
'qgep_od.access_aid',
view_name='vw_access_aid',
pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part',
'qgep_od.benching',
view_name='vw_benching',
pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part',
'qgep_od.backflow_prevention',
view_name='vw_backflow_prevention',
pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part',
'qgep_od.cover',
view_name='vw_cover',
pkey_default_value=True,
pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part',
'qgep_od.dryweather_downspout',
view_name='vw_dryweather_downspout',
pg_service=pg_service).create()
SingleInheritance('qgep_od.structure_part',
'qgep_od.dryweather_flume',
view_name='vw_dryweather_flume',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure',
'qgep_od.channel',
view_name='vw_channel',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure',
'qgep_od.manhole',
view_name='vw_manhole',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure',
'qgep_od.discharge_point',
view_name='vw_discharge_point',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_structure',
'qgep_od.special_structure',
view_name='vw_special_structure',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_networkelement',
'qgep_od.reach',
view_name='vw_reach',
pg_service=pg_service).create()
SingleInheritance('qgep_od.wastewater_networkelement',
'qgep_od.wastewater_node',
view_name='vw_wastewater_node',
pkey_default_value=True,
pg_service=pg_service).create()
SingleInheritance('qgep_od.connection_object',
'qgep_od.individual_surface',
view_name='vw_individual_surface',
pkey_default_value=True,
pg_service=pg_service).create()
MultipleInheritance(safe_load(
open("view/vw_maintenance_examination.yaml")),
drop=True,
pg_service=pg_service).create()
MultipleInheritance(safe_load(open("view/vw_damage.yaml")),
drop=True,
pg_service=pg_service).create()
vw_qgep_wastewater_structure(
srid,
pg_service=pg_service,
extra_definition=qgep_wastewater_structure_extra)
vw_qgep_reach(pg_service=pg_service, extra_definition=qgep_reach_extra)
run_sql('view/vw_file.sql', pg_service, variables)
MultipleInheritance(safe_load(open("view/vw_oo_overflow.yaml")),
create_joins=True,
variables=variables,
pg_service=pg_service,
drop=True).create()
MultipleInheritance(safe_load(open("view/vw_oo_organisation.yaml")),
drop=True,
pg_service=pg_service).create()
run_sql('view/vw_catchment_area_connections.sql', pg_service, variables)
run_sql('view/vw_change_points.sql', pg_service, variables)
run_sql('view/vw_qgep_import.sql', pg_service, variables)
# Recreate swmm views
run_sql('swmm_views/01_vw_swmm_create_schema.sql', pg_service, variables)
run_sql('swmm_views/02_vw_swmm_junctions.sql', pg_service, variables)
run_sql('swmm_views/03_vw_swmm_aquifers.sql', pg_service, variables)
run_sql('swmm_views/04_vw_swmm_conduits.sql', pg_service, variables)
run_sql('swmm_views/05_vw_swmm_dividers.sql', pg_service, variables)
run_sql('swmm_views/06_vw_swmm_landuses.sql', pg_service, variables)
run_sql('swmm_views/07_vw_swmm_losses.sql', pg_service, variables)
run_sql('swmm_views/08_vw_swmm_outfalls.sql', pg_service, variables)
run_sql('swmm_views/09_vw_swmm_subcatchments.sql', pg_service, variables)
run_sql('swmm_views/10_vw_swmm_vertices.sql', pg_service, variables)
run_sql('swmm_views/11_vw_swmm_pumps.sql', pg_service, variables)
run_sql('swmm_views/12_vw_swmm_polygons.sql', pg_service, variables)
run_sql('swmm_views/13_vw_swmm_storages.sql', pg_service, variables)
run_sql('swmm_views/14_vw_swmm_xsections.sql', pg_service, variables)
run_sql('swmm_views/15_vw_swmm_coordinates.sql', pg_service, variables)
run_sql('swmm_views/16_vw_swmm_tags.sql', pg_service, variables)
SimpleJoins(safe_load(open('view/export/vw_export_reach.yaml')),
pg_service).create()
SimpleJoins(
safe_load(open('view/export/vw_export_wastewater_structure.yaml')),
pg_service).create()