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()