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
