def test_export_spatialite_zz_tables(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] dbconnection = db_utils.DbConnectionManager() mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 """ insert into zz_strat(geoshort,strata) values('land fill','fyll'); insert into zz_stratigraphy_plots (strata,color_mplot,hatch_mplot,color_qt,brush_qt) values('torv','DarkGray','+','darkGray','NoBrush'); insert into zz_capacity (capacity,explanation) values('6 ','mycket god'); insert into zz_capacity (capacity,explanation) values('6+','mycket god'); insert into zz_capacity_plots (capacity,color_qt) values('', 'gray'); """ db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(633466 711659)', 3006))''', dbconnection=dbconnection) dbconnection.execute('''PRAGMA foreign_keys='off' ''') dbconnection.execute('''UPDATE zz_strat SET strata = 'filling' WHERE geoshort = 'land fill' ''') dbconnection.execute('''INSERT INTO zz_stratigraphy_plots (strata,color_mplot,hatch_mplot,color_qt,brush_qt) values ('filling','Yellow','+','darkGray','NoBrush') ''') dbconnection.execute('''UPDATE zz_stratigraphy_plots SET color_mplot = 'OrangeFIX' WHERE strata = 'made ground' ''') dbconnection.execute('''UPDATE zz_capacity SET explanation = 'anexpl' WHERE capacity = 0 ''') dbconnection.execute('''UPDATE zz_capacity_plots SET color_qt = 'whiteFIX' WHERE capacity = 0 ''') #print(str(dbconnection.execute_and_fetchall('select * from zz_strat'))) dbconnection.commit_and_closedb() print("Before export") mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'en_US' self.midvatten.export_spatialite() sql_list = ['''SELECT geoshort, strata FROM zz_strat WHERE geoshort IN ('land fill', 'rock') ''', '''SELECT strata, color_mplot FROM zz_stratigraphy_plots WHERE strata IN ('made ground', 'rock', 'filling') ''', '''SELECT capacity, explanation FROM zz_capacity WHERE capacity IN (0, 1)''', '''SELECT capacity, color_qt FROM zz_capacity_plots WHERE capacity IN (0, 1) '''] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = ['''[''', '''SELECT geoshort, strata FROM zz_strat WHERE geoshort IN ('land fill', 'rock') ''', ''', [(land fill, filling), (rock, rock)], ''', '''SELECT strata, color_mplot FROM zz_stratigraphy_plots WHERE strata IN ('made ground', 'rock', 'filling') ''', ''', [(filling, Yellow), (made ground, OrangeFIX), (rock, red)], ''', '''SELECT capacity, explanation FROM zz_capacity WHERE capacity IN (0, 1)''', ''', [(0, anexpl), (1, above gwl)], ''', '''SELECT capacity, color_qt FROM zz_capacity_plots WHERE capacity IN (0, 1) ''', ''', [(0, whiteFIX), (1, red)]]'''] reference_string = '\n'.join(reference_string) assert test_string == reference_string
def test_export_spatialite_with_umlauts(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_selection, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_selection.return_value = ('åäö', ) mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('åäö', ST_GeomFromText('POINT(633466 711659)', 3006))''' ) db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''') db_utils.sql_alter_db( '''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('åäö', '2015-01-01 00:00:00', 's1', 'comment1')''' ) mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = [ '''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments''' ] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = [ '''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(åäö, POINT(633466 711659))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(åäö, 2015-01-01 00:00:00, s1, comment1)]]''' ] reference_string = '\n'.join(reference_string) print("Ref") print(reference_string) print("Test") print(test_string) assert test_string == reference_string
def test_export_spatialite_with_umlauts(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_selection, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_selection.return_value = ('åäö', ) mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ("åäö", ST_GeomFromText('POINT(633466 711659)', 3006))''') db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''') db_utils.sql_alter_db('''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('åäö', '2015-01-01 00:00:00', 's1', 'comment1')''') mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = ['''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments'''] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = ['''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(åäö, POINT(633466 711659))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(åäö, 2015-01-01 00:00:00, s1, comment1)]]'''] reference_string = '\n'.join(reference_string) assert test_string == reference_string
def export_2_splite(self,target_db, EPSG_code): """ Exports a datagbase to a new spatialite database file :param target_db: The name of the new database file :param EPSG_code: :return: """ dbconnection = db_utils.DbConnectionManager() source_db = dbconnection.dbpath dbconnection.closedb() conn = db_utils.connect_with_spatialite_connect(target_db) self.curs = conn.cursor() self.curs.execute("PRAGMA foreign_keys = ON") self.curs.execute(r"""ATTACH DATABASE '%s' AS a"""%source_db) conn.commit() # commit sql statements so far old_table_column_srid = self.get_table_column_srid(prefix='a') self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), self.verify_table_in_attached_db, 'a.') conn.commit() self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), self.verify_table_in_attached_db, 'a.') conn.commit() self.write_data(self.zz_to_sql, 'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), self.verify_table_in_attached_db, 'a.') conn.commit() db_utils.delete_srids(self.curs, EPSG_code) conn.commit() #Statistics statistics = self.get_table_rows_with_differences() self.curs.execute(r"""DETACH DATABASE a""") self.curs.execute('vacuum') utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('ExportData', "Export done, see differences in log message panel")), log_msg=ru(QCoreApplication.translate('ExportData', "Tables with different number of rows:\n%s"))%statistics) conn.commit() conn.close()
def test_export_spatialite_zz_tables(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] dbconnection = db_utils.DbConnectionManager() mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 """ insert into zz_strat(geoshort,strata) values('land fill','fyll'); insert into zz_stratigraphy_plots (strata,color_mplot,hatch_mplot,color_qt,brush_qt) values('torv','DarkGray','+','darkGray','NoBrush'); insert into zz_capacity (capacity,explanation) values('6 ','mycket god'); insert into zz_capacity (capacity,explanation) values('6+','mycket god'); insert into zz_capacity_plots (capacity,color_qt) values('', 'gray'); """ db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(633466 711659)', 3006))''', dbconnection=dbconnection) dbconnection.execute('''PRAGMA foreign_keys='off' ''') dbconnection.execute( '''UPDATE zz_strat SET strata = 'filling' WHERE geoshort = 'land fill' ''' ) dbconnection.execute( '''INSERT INTO zz_stratigraphy_plots (strata,color_mplot,hatch_mplot,color_qt,brush_qt) values ('filling','Yellow','+','darkGray','NoBrush') ''' ) dbconnection.execute( '''UPDATE zz_stratigraphy_plots SET color_mplot = 'OrangeFIX' WHERE strata = 'made ground' ''' ) dbconnection.execute( '''UPDATE zz_capacity SET explanation = 'anexpl' WHERE capacity = '0' ''' ) dbconnection.execute( '''UPDATE zz_capacity_plots SET color_qt = 'whiteFIX' WHERE capacity = '0' ''' ) #print(str(dbconnection.execute_and_fetchall('select * from zz_strat'))) dbconnection.commit_and_closedb() print("Before export") mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'en_US' self.midvatten.export_spatialite() sql_list = [ '''SELECT geoshort, strata FROM zz_strat WHERE geoshort IN ('land fill', 'rock') ''', '''SELECT strata, color_mplot FROM zz_stratigraphy_plots WHERE strata IN ('made ground', 'rock', 'filling') ''', '''SELECT capacity, explanation FROM zz_capacity WHERE capacity IN ('0', '1')''', '''SELECT capacity, color_qt FROM zz_capacity_plots WHERE capacity IN ('0', '1') ''' ] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = [ '''[''', '''SELECT geoshort, strata FROM zz_strat WHERE geoshort IN ('land fill', 'rock') ''', ''', [(land fill, filling), (rock, rock)], ''', '''SELECT strata, color_mplot FROM zz_stratigraphy_plots WHERE strata IN ('made ground', 'rock', 'filling') ''', ''', [(filling, Yellow), (made ground, OrangeFIX), (rock, red)], ''', '''SELECT capacity, explanation FROM zz_capacity WHERE capacity IN ('0', '1')''', ''', [(0, anexpl), (1, above gwl)], ''', '''SELECT capacity, color_qt FROM zz_capacity_plots WHERE capacity IN ('0', '1') ''', ''', [(0, whiteFIX), (1, red)]]''' ] reference_string = '\n'.join(reference_string) print(str(test_string)) print(str(reference_string)) assert test_string == reference_string
def test_export_spatialite_transform_coordinates( self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3010, True] mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(1 1)', 3006))''' ) db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''') db_utils.sql_alter_db( '''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('P1', '2015-01-01 00:00:00', 's1', 'comment1')''' ) db_utils.sql_alter_db( '''INSERT INTO w_qual_lab (obsid, parameter, report, staff) VALUES ('P1', 'labpar1', 'report1', 's1')''' ) db_utils.sql_alter_db( '''INSERT INTO w_qual_field (obsid, parameter, staff, date_time, unit) VALUES ('P1', 'par1', 's1', '2015-01-01 01:00:00', 'unit1')''' ) db_utils.sql_alter_db( '''INSERT INTO w_flow (obsid, instrumentid, flowtype, date_time, unit) VALUES ('P1', 'inst1', 'Momflow', '2015-04-13 00:00:00', 'l/s')''' ) db_utils.sql_alter_db( '''INSERT INTO w_levels (obsid, date_time, meas) VALUES ('P1', '2015-01-02 00:00:01', '2')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot) VALUES ('P1', 1, 0, 10)''' ) db_utils.sql_alter_db( '''INSERT INTO obs_lines (obsid) VALUES ('L1')''') db_utils.sql_alter_db( '''INSERT INTO seismic_data (obsid, length) VALUES ('L1', '5')''') db_utils.sql_alter_db( '''INSERT INTO meteo (obsid, instrumentid, parameter, date_time) VALUES ('P1', 'meteoinst', 'precip', '2017-01-01 00:19:00')''' ) mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = [ '''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments''', '''select obsid, parameter, report, staff from w_qual_lab''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', '''select obsid, date_time, meas from w_levels''', '''select obsid, stratid, depthtop, depthbot from stratigraphy''', '''select obsid from obs_lines''', '''select obsid, length from seismic_data''', '''select obsid, instrumentid, parameter, date_time from meteo''' ] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) """ # The coordinates aquired from st_transform differs from Linux Mint 18.2 to Linux Mint 19 # In Mint 18, it's -517888.383773 for both postgis and spatialite # In Mint 19, it's -517888.383737 for both postgis and spatialite #// I've made changes to the transformation so the above values no longer exists, but the previous issue probably does. # !!! No idea why reference_string = ['''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(-517888.392089 1.000667))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid, depthtop, depthbot from stratigraphy''', ''', [(P1, 1, 0.0, 10.0)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]'''] """ reference_string = [ '''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(-517888.383737 1.002821))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid, depthtop, depthbot from stratigraphy''', ''', [(P1, 1, 0.0, 10.0)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]''' ] reference_string = '\n'.join(reference_string) print("Test\n" + test_string) print("Ref\n" + reference_string) assert test_string == reference_string
def test_export_spatialite_no_selected(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] dbconnection = db_utils.DbConnectionManager() mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(633466 711659)', 3006))''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('P2', ST_GeomFromText('POINT(1 2)', 3006))''' ) db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('P1', '2015-01-01 00:00:00', 's1', 'comment1')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO w_qual_lab (obsid, parameter, report, staff) VALUES ('P1', 'labpar1', 'report1', 's1')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO w_qual_field (obsid, parameter, staff, date_time, unit) VALUES ('P1', 'par1', 's1', '2015-01-01 01:00:00', 'unit1')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO w_flow (obsid, instrumentid, flowtype, date_time, unit) VALUES ('P1', 'inst1', 'Momflow', '2015-04-13 00:00:00', 'l/s')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO w_levels (obsid, date_time, meas) VALUES ('P1', '2015-01-02 00:00:01', '2')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot) VALUES ('P1', 1, 0, 10)''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO obs_lines (obsid) VALUES ('L1')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO seismic_data (obsid, length) VALUES ('L1', '5')''', dbconnection=dbconnection) db_utils.sql_alter_db( '''INSERT INTO meteo (obsid, instrumentid, parameter, date_time) VALUES ('P1', 'meteoinst', 'precip', '2017-01-01 00:19:00')''', dbconnection=dbconnection) dbconnection.commit_and_closedb() mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = [ '''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments''', '''select obsid, parameter, report, staff from w_qual_lab''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', '''select obsid, date_time, meas from w_levels''', '''select obsid, stratid, depthtop, depthbot from stratigraphy''', '''select obsid from obs_lines''', '''select obsid, length from seismic_data''', '''select obsid, instrumentid, parameter, date_time from meteo''' ] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = [ '''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(633466 711659)), (P2, POINT(1 2))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid, depthtop, depthbot from stratigraphy''', ''', [(P1, 1, 0.0, 10.0)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]''' ] reference_string = '\n'.join(reference_string) print(test_string) print(str(mock_messagebar.mock_calls)) assert test_string == reference_string
def create_new_spatialite_db(self, verno, user_select_CRS='y', EPSG_code='4326', delete_srids=True): #CreateNewDB(self, verno): """Open a new DataBase (create an empty one if file doesn't exists) and set as default DB""" utils.stop_waiting_cursor() set_locale = self.ask_for_locale() utils.start_waiting_cursor() if user_select_CRS=='y': utils.stop_waiting_cursor() EPSGID=str(self.ask_for_CRS(set_locale)[0]) utils.start_waiting_cursor() else: EPSGID=EPSG_code if EPSGID=='0' or not EPSGID: raise utils.UserInterruptError() # If a CRS is selectd, go on and create the database #path and name of new db utils.stop_waiting_cursor() dbpath = ru(utils.get_save_file_name_no_extension(parent=None, caption="New DB", directory="midv_obsdb.sqlite", filter="Spatialite (*.sqlite)")) utils.start_waiting_cursor() if os.path.exists(dbpath): utils.MessagebarAndLog.critical( bar_msg=ru(QCoreApplication.translate('NewDb', 'A database with the chosen name already existed. Cancelling...'))) utils.stop_waiting_cursor() return '' #Create the database conn = db_utils.connect_with_spatialite_connect(dbpath) conn.close() self.db_settings = ru(utils.anything_to_string_representation({'spatialite': {'dbpath': dbpath}})) #dbconnection = db_utils.DbConnectionManager(self.db_settings) try: # creating/connecting the test_db dbconnection = db_utils.DbConnectionManager(self.db_settings) dbconnection.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database dbconnection separately. except Exception as e: utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('NewDb', "Impossible to connect to selected DataBase, see log message panel")), log_msg=ru(QCoreApplication.translate('NewDb', 'Msg:\n') + str(e))) #utils.pop_up_info("Impossible to connect to selected DataBase") utils.stop_waiting_cursor() return '' d =dbconnection.connector #First, find spatialite version versionstext = dbconnection.execute_and_fetchall('select spatialite_version()')[0][0] # load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS # then the syntax defines a Midvatten project db according to the loaded .sql-file if not int(versionstext[0][0]) > 3: # which file to use depends on spatialite version installed utils.pop_up_info(ru(QCoreApplication.translate('NewDb', "Midvatten plugin needs spatialite4.\nDatabase can not be created"))) utils.stop_waiting_cursor() return '' filenamestring = "create_db.sql" SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring) qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db replace_word_replace_with = [('CHANGETORELEVANTEPSGID', ru(EPSGID)), ('CHANGETOPLUGINVERSION', ru(verno)), ('CHANGETOQGISVERSION', ru(qgisverno)), ('CHANGETODBANDVERSION', 'SpatiaLite version %s'%ru(versionstext)), ('CHANGETOLOCALE', ru(set_locale)), (('SPATIALITE ', ''))] with open(SQLFile, 'r') as f: f.readline() # first line is encoding info.... lines = [ru(line) for line in f] sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l] for line in sql_lines: if all([line, not line.startswith("#"), 'POSTGIS' not in line]): sql = self.replace_words(line, replace_word_replace_with) try: dbconnection.execute(sql) except: try: print(str(sql)) except: pass raise if delete_srids: db_utils.delete_srids(dbconnection, EPSGID) self.insert_datadomains(set_locale, dbconnection) execute_sqlfile(get_full_filename("insert_obs_points_triggers.sql"), dbconnection) execute_sqlfile(get_full_filename('qgis3_obsp_fix.sql'), dbconnection) self.add_metadata_to_about_db(dbconnection) #FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS dbconnection.commit() dbconnection.vacuum() dbconnection.commit_and_closedb() #create SpatiaLite Connection in QGIS QSettings settings=qgis.PyQt.QtCore.QSettings() settings.beginGroup('/SpatiaLite/dbconnections') settings.setValue('%s/sqlitepath'%os.path.basename(dbpath),'%s'%dbpath) settings.endGroup() """ #The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while. #Finally add the layer styles info into the data base AddLayerStyles(dbpath) """ utils.stop_waiting_cursor()
def test_export_spatialite_transform_coordinates(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3010, True] mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(1 1)', 3006))''') db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''') db_utils.sql_alter_db('''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('P1', '2015-01-01 00:00:00', 's1', 'comment1')''') db_utils.sql_alter_db('''INSERT INTO w_qual_lab (obsid, parameter, report, staff) VALUES ('P1', 'labpar1', 'report1', 's1')''') db_utils.sql_alter_db('''INSERT INTO w_qual_field (obsid, parameter, staff, date_time, unit) VALUES ('P1', 'par1', 's1', '2015-01-01 01:00:00', 'unit1')''') db_utils.sql_alter_db('''INSERT INTO w_flow (obsid, instrumentid, flowtype, date_time, unit) VALUES ('P1', 'inst1', 'Momflow', '2015-04-13 00:00:00', 'l/s')''') db_utils.sql_alter_db('''INSERT INTO w_levels (obsid, date_time, meas) VALUES ('P1', '2015-01-02 00:00:01', '2')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid) VALUES ('P1', 1)''') db_utils.sql_alter_db('''INSERT INTO obs_lines (obsid) VALUES ('L1')''') db_utils.sql_alter_db('''INSERT INTO seismic_data (obsid, length) VALUES ('L1', '5')''') db_utils.sql_alter_db('''INSERT INTO meteo (obsid, instrumentid, parameter, date_time) VALUES ('P1', 'meteoinst', 'precip', '2017-01-01 00:19:00')''') mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = ['''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments''', '''select obsid, parameter, report, staff from w_qual_lab''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', '''select obsid, date_time, meas from w_levels''', '''select obsid, stratid from stratigraphy''', '''select obsid from obs_lines''', '''select obsid, length from seismic_data''', '''select obsid, instrumentid, parameter, date_time from meteo'''] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) """ # The coordinates aquired from st_transform differs from Linux Mint 18.2 to Linux Mint 19 # In Mint 18, it's -517888.383773 for both postgis and spatialite # In Mint 19, it's -517888.383737 for both postgis and spatialite # !!! No idea why reference_string = ['''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(-517888.383773 1.002821))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid from stratigraphy''', ''', [(P1, 1)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]'''] """ reference_string = ['''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(-517888.383737 1.002821))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid from stratigraphy''', ''', [(P1, 1)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]'''] reference_string = '\n'.join(reference_string) print("Test\n" + test_string) print("Ref\n" + reference_string) assert test_string == reference_string
def test_export_spatialite_no_selected(self, mock_skip_popup, mock_iface, mock_find_layer, mock_newdbpath, mock_verify, mock_locale, mock_createdb_crs_question, mock_messagebar): mock_find_layer.return_value.crs.return_value.authid.return_value = 'EPSG:3006' mock_createdb_crs_question.return_value = [3006, True] dbconnection = db_utils.DbConnectionManager() mock_newdbpath.return_value = (EXPORT_DB_PATH, '') mock_verify.return_value = 0 db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('P1', ST_GeomFromText('POINT(633466 711659)', 3006))''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('P2', ST_GeomFromText('POINT(1 2)', 3006))''') db_utils.sql_alter_db('''INSERT INTO zz_staff (staff) VALUES ('s1')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO comments (obsid, date_time, staff, comment) VALUES ('P1', '2015-01-01 00:00:00', 's1', 'comment1')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO w_qual_lab (obsid, parameter, report, staff) VALUES ('P1', 'labpar1', 'report1', 's1')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO w_qual_field (obsid, parameter, staff, date_time, unit) VALUES ('P1', 'par1', 's1', '2015-01-01 01:00:00', 'unit1')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO w_flow (obsid, instrumentid, flowtype, date_time, unit) VALUES ('P1', 'inst1', 'Momflow', '2015-04-13 00:00:00', 'l/s')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO w_levels (obsid, date_time, meas) VALUES ('P1', '2015-01-02 00:00:01', '2')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid) VALUES ('P1', 1)''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO obs_lines (obsid) VALUES ('L1')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO seismic_data (obsid, length) VALUES ('L1', '5')''', dbconnection=dbconnection) db_utils.sql_alter_db('''INSERT INTO meteo (obsid, instrumentid, parameter, date_time) VALUES ('P1', 'meteoinst', 'precip', '2017-01-01 00:19:00')''', dbconnection=dbconnection) dbconnection.commit_and_closedb() mock_locale.return_value.answer = 'ok' mock_locale.return_value.value = 'sv_SE' self.midvatten.export_spatialite() sql_list = ['''select obsid, ST_AsText(geometry) from obs_points''', '''select staff from zz_staff''', '''select obsid, date_time, staff, comment from comments''', '''select obsid, parameter, report, staff from w_qual_lab''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', '''select obsid, date_time, meas from w_levels''', '''select obsid, stratid from stratigraphy''', '''select obsid from obs_lines''', '''select obsid, length from seismic_data''', '''select obsid, instrumentid, parameter, date_time from meteo'''] conn = db_utils.connect_with_spatialite_connect(EXPORT_DB_PATH) curs = conn.cursor() test_list = [] for sql in sql_list: test_list.append('\n' + sql + '\n') test_list.append(curs.execute(sql).fetchall()) conn.commit() conn.close() test_string = utils_for_tests.create_test_string(test_list) reference_string = ['''[''', '''select obsid, ST_AsText(geometry) from obs_points''', ''', [(P1, POINT(633466 711659)), (P2, POINT(1 2))], ''', '''select staff from zz_staff''', ''', [(s1)], ''', '''select obsid, date_time, staff, comment from comments''', ''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''', '''select obsid, parameter, report, staff from w_qual_lab''', ''', [(P1, labpar1, report1, s1)], ''', '''select obsid, parameter, staff, date_time, comment from w_qual_field''', ''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''', '''select obsid, instrumentid, flowtype, date_time, unit from w_flow''', ''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''', '''select obsid, date_time, meas from w_levels''', ''', [(P1, 2015-01-02 00:00:01, 2.0)], ''', '''select obsid, stratid from stratigraphy''', ''', [(P1, 1)], ''', '''select obsid from obs_lines''', ''', [(L1)], ''', '''select obsid, length from seismic_data''', ''', [(L1, 5.0)], ''', '''select obsid, instrumentid, parameter, date_time from meteo''', ''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]'''] reference_string = '\n'.join(reference_string) print(test_string) print(str(mock_messagebar.mock_calls)) assert test_string == reference_string