def export_2_splite(self, target_db, dest_srid):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param dest_srid:
:return:
"""
self.source_dbconnection = db_utils.DbConnectionManager()
self.source_dbconnection.connect2db() #establish connection to the current midv db
self.dest_dbconnection = db_utils.DbConnectionManager(target_db)
self.dest_dbconnection.connect2db()
self.midv_data_importer = midv_data_importer()
self.write_data(self.to_sql, None, defs.get_subset_of_tables_fr_db(category='data_domains'), replace=True)
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'))
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'))
self.dest_dbconnection.commit()
db_utils.delete_srids(self.dest_dbconnection.cursor, dest_srid)
self.dest_dbconnection.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.dest_dbconnection.cursor.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)
self.dest_dbconnection.commit_and_closedb()
self.source_dbconnection.closedb()
def create_and_select_vlayer(self):
self.qgs = QgsApplication([], True)
self.qgs.initQgis()
self.midvatten.ms.settingsdict['secplotdrillstop'] = "%berg%"
dbconnection = db_utils.DbConnectionManager()
uri = dbconnection.uri
uri.setDataSource('', 'obs_points', 'geometry', '', 'obsid')
dbtype = db_utils.get_dbtype(dbconnection.dbtype)
self.vlayer = QgsVectorLayer(uri.uri(), 'TestLayer', dbtype)
features = self.vlayer.getFeatures()
feature_ids = [feature.id() for feature in features]
self.vlayer.selectByIds(feature_ids)
print("1. Valid vlayer '{}'".format(self.vlayer.isValid()))
print("2. feature_ids: " + str(feature_ids))
print("3. QgsVectorLayer.selectedFeatureIds: " +
str(self.vlayer.selectedFeatureIds()))
print("4. QgsVectorLayer.getSelectedFeatures: " +
str([x.id() for x in self.vlayer.getSelectedFeatures()]))
print("5. QgsVectorLayer.getFeature(): " +
str([self.vlayer.getFeature(x).id() for x in feature_ids]))
print("6. QgsVectorLayer.getFeature() type: " +
str([str(type(self.vlayer.getFeature(x))) for x in feature_ids]))
print("7. QgsVectorLayer.getFeatures(): " +
str([x.id() for x in self.vlayer.getFeatures(feature_ids)]))
def __init__(self, iface, settingsdict={}):
self.dbconnection = db_utils.DbConnectionManager()
self.settingsdict = settingsdict
self.strat_layers_dict = defs.PlotTypesDict('english')
self.symbolcolors_dict = defs.PlotColorDict() # This is not used yet
for key, v in list(
self.strat_layers_dict.items()
): #make all the keys only ascii and only lower case and also add 'strat_' as prefix
newkey = 'strat_' + utils.return_lower_ascii_string(key)
self.strat_layers_dict[newkey] = self.strat_layers_dict[key]
del self.strat_layers_dict[key]
for key, v in list(
self.symbolcolors_dict.items()
): #THIS IS NOT USED YET make all the keys only ascii and only lower case and also add 'strat_' as prefix
newkey = 'strat_' + utils.return_lower_ascii_string(key)
self.symbolcolors_dict[newkey] = self.symbolcolors_dict[key]
del self.symbolcolors_dict[key]
self.iface = iface
self.root = QgsProject.instance().layerTreeRoot()
self.remove_views()
self.drop_db_views()
self.create_db_views()
self.dbconnection.commit()
self.add_layers()
self.dbconnection.closedb()
def get_statistics(obsids,
table,
column,
sql_function_order=None,
median=True,
dbconnection=None):
if not isinstance(dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
if sql_function_order is None:
sql_function_order = ['min', 'max', 'avg', 'count']
if not isinstance(obsids, (list, tuple)):
obsids = [obsids]
sql = 'select obsid, %s from %s where obsid in (%s) group by obsid' % (
', '.join(['%s(%s)' % (func, column) for func in sql_function_order
]), table, ', '.join(["'{}'".format(x) for x in obsids]))
_res = db_utils.get_sql_result_as_dict(sql, dbconnection=dbconnection)[1]
res = dict([(obsid, list(v[0])) for obsid, v in _res.items()])
if median:
[
v.append(
db_utils.calculate_median_value(table, column, obsid,
dbconnection))
for obsid, v in res.items()
]
return res
def __init__(self):
dbconnection = db_utils.DbConnectionManager()
self.dbpath = dbconnection.dbpath
try:
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:
pass
#add layer styles
self.add_layer_styles_2_db(dbconnection)
#load style from file and set it as value into the layer styles table
"""
self.style_from_file_into_db('obs_lines', 'obs_lines_tablayout.qml','obs_lines_tablayout.sld')
self.style_from_file_into_db('obs_p_w_strat', 'obs_p_w_strat.qml','obs_p_w_strat.sld')
self.style_from_file_into_db('obs_p_w_lvl', 'obs_p_w_lvl.qml','obs_p_w_lvl.sld')
#osv
"""
self.style_from_file_into_db('obs_points', 'obs_points_tablayout.qml','obs_points_tablayout.sld', dbconnection)
self.style_from_file_into_db('stratigraphy', 'stratigraphy_tablayout.qml','stratigraphy_tablayout.sld', dbconnection)
try:
dbconnection.execute("PRAGMA foreign_keys = OFF")
except:
pass
dbconnection.commit_and_closedb()
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 = sqlite.connect(target_db,
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
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, u'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(
u'ExportData',
u"Export done, see differences in log message panel")),
log_msg=ru(
QCoreApplication.translate(
u'ExportData',
u"Tables with different number of rows:\n%s")) %
statistics)
conn.commit()
conn.close()
def export_2_csv(self,exportfolder):
self.source_dbconnection = db_utils.DbConnectionManager()
self.source_dbconnection.connect2db() #establish connection to the current midv db
self.exportfolder = exportfolder
self.write_data(self.to_csv, None, defs.get_subset_of_tables_fr_db(category='data_domains'))
self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'))
self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'))
self.source_dbconnection.closedb()
def create_and_select_vlayer(self):
self.midvatten.ms.settingsdict['secplotdrillstop'] = "%berg%"
dbconnection = db_utils.DbConnectionManager()
uri = dbconnection.uri
uri.setDataSource('', 'obs_lines', 'geometry', '', 'obsid')
dbtype = db_utils.get_dbtype(dbconnection.dbtype)
self.vlayer = QgsVectorLayer(uri.uri(), 'TestLayer', dbtype)
QgsProject.instance().addMapLayer(self.vlayer)
features = self.vlayer.getFeatures()
feature_ids = [feature.id() for feature in features]
print(str(feature_ids))
self.vlayer.selectByIds(feature_ids)
def test_tables_columns_dbconnection_supplied(self):
""" """
dbconnection = db_utils.DbConnectionManager()
tables_columns = db_utils.tables_columns(dbconnection=dbconnection)
for tablename in [
u'obs_points', u'w_levels', u'w_qual_lab', u'w_lvls_last_geom'
]:
assert tablename in tables_columns
assert u'obsid' in tables_columns[tablename]
for tablename in [u'geometry_columns', u'spatial_ref_sys']:
assert tablename not in tables_columns
def create_and_select_vlayer(self):
self.qgs = QgsApplication([], True)
self.qgs.initQgis()
self.midvatten.ms.settingsdict['secplotdrillstop'] = "%berg%"
dbconnection = db_utils.DbConnectionManager()
uri = dbconnection.uri
uri.setDataSource('', 'obs_lines', 'geometry', '', 'obsid')
dbtype = db_utils.get_dbtype(dbconnection.dbtype)
self.vlayer = QgsVectorLayer(uri.uri(), 'TestLayer', dbtype)
features = self.vlayer.getFeatures()
feature_ids = [feature.id() for feature in features]
self.vlayer.selectByIds(feature_ids)
def test_calculate_statistics(self, mock_selected_features, mock_iface,
mock_messagebar):
dbconnection = db_utils.DbConnectionManager()
db_utils.sql_alter_db(u"""INSERT INTO obs_points(obsid) VALUES('1')""")
db_utils.sql_alter_db(u"""INSERT INTO obs_points(obsid) VALUES('2')""")
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('1', '2017-01-01', '1')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('1', '2017-01-02', '4')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('1', '2017-01-03', '5')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('2', '2017-01-04', '2')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('2', '2017-01-05', '8')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('2', '2017-01-06', '9')"""
)
db_utils.sql_alter_db(
u"""INSERT INTO w_levels(obsid, date_time, meas) VALUES('2', '2017-01-07', '10')"""
)
mock_selected_features.return_value = [u'1', u'2']
ms = MagicMock()
ms.settingsdict = OrderedDict()
calc_stats = calculate_statistics.CalculateStatisticsGui(
self.iface.mainWindow(), ms)
calc_stats.db_browser.table_list = u'w_levels'
calc_stats.db_browser.column_list = u'meas'
calc_stats.calculate()
ref = call.info(
bar_msg=
u'Statistics for table w_levels column meas done, see log for results.',
button=True,
duration=15,
log_msg=
u'Obsid;Min;Median;Average;Max;Nr of values\n1;1.0;4.0;3.33333333333;5.0;3\n2;2.0;8.5;7.25;10.0;4'
)
print(str(mock_messagebar.mock_calls))
assert ref in mock_messagebar.mock_calls
def test_vlayer_2000_strings(self, mock_skippopup, mock_messagebar):
"""
:param mock_skippopup:
:param mock_messagebar:
:return:
"""
dbconnection = db_utils.DbConnectionManager()
cur = dbconnection.cursor
obsids = [
letter + str(_int) for letter in string.ascii_letters
for _int in range(80)
]
cur.execute('''BEGIN TRANSACTION;''')
for obsid in obsids:
cur.execute(
'''INSERT INTO obs_points (obsid) VALUES ('{}');'''.format(
str(obsid)))
cur.execute('''END TRANSACTION;''')
dbconnection.commit()
self.create_vlayer(no_print=True)
self.select_features(no_print=True)
feature_ids = [feature.id() for feature in self.vlayer.getFeatures()]
reference_ids = tuple(range(1, len(obsids) + 1))
print("First 10 ids: " + str(feature_ids[:10]))
print("Last 10 ids: " + str(feature_ids[-10:]))
print("First 10 reference_ids: " + str(reference_ids[:10]))
print("Last 10 reference_ids: " + str(reference_ids[-10:]))
print(str(self.vlayer.featureCount()))
assert self.vlayer.isValid()
assert len(feature_ids) == len(reference_ids)
assert tuple(feature_ids) == reference_ids
assert tuple(sorted([x for x in self.vlayer.selectedFeatureIds()
])) == reference_ids
assert tuple(
sorted([x.id() for x in self.vlayer.getSelectedFeatures()
])) == reference_ids
assert tuple(
sorted([x.id() for x in self.vlayer.getFeatures(feature_ids)
])) == reference_ids
assert self.vlayer.featureCount() == len(reference_ids)
def test_prepare_qgis2threejs(self, mock_iface, mock_messagebar):
self.init_qgis()
dbconnection = db_utils.DbConnectionManager()
dbconnection.execute(
'''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 1, ST_GeomFromText('POINT(1 1)', 3006)); '''
)
dbconnection.execute(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geoshort) VALUES ('1', 1, 0, 1, 'torv'); '''
)
dbconnection.execute(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geoshort) VALUES ('1', 2, 1, 2, 'fyll'); '''
)
dbconnection.commit_and_closedb()
#print(str(db_utils.sql_load_fr_db('''SELECT * FROM stratigraphy;''')))
canvas = MagicMock()
mock_iface.mapCanvas.return_value = canvas
self.midvatten.prepare_layers_for_qgis2threejs()
layers = [
'strat_torv', 'strat_fyll', 'strat_lera', 'strat_silt',
'strat_finsand', 'strat_mellansand', 'strat_sand',
'strat_grovsand', 'strat_fingrus', 'strat_mellangrus',
'strat_grus', 'strat_grovgrus', 'strat_morn', 'strat_berg',
'strat_obs_p_for_qgsi2threejs'
]
view_contents = []
for l in layers:
if l != 'strat_obs_p_for_qgsi2threejs':
view_contents.append(
db_utils.sql_load_fr_db(
'''SELECT rowid, obsid, z_coord, height, ST_AsText(geometry) FROM {};'''
.format(l))[1])
view_contents.append(
db_utils.sql_load_fr_db(
'''SELECT rowid, obsid, ST_AsText(geometry) FROM {};'''.format(
'strat_obs_p_for_qgsi2threejs'))[1])
test = utils.anything_to_string_representation(view_contents)
print(str(test))
ref = '''[[(1, "1", 1.0, -1.0, "POINT(1 1)", )], [(2, "1", 0.0, -1.0, "POINT(1 1)", )], [], [], [], [], [], [], [], [], [], [], [], [], [(1, "1", "POINT(1 1)", )]]'''
assert test == ref
assert not mock_messagebar.mock_calls
def SanityCheckVacuumDB(self, dbconnection=None):
if dbconnection is None:
dbconnection = db_utils.DbConnectionManager()
sanity = utils.Askuser(
"YesNo",
ru(
QCoreApplication.translate(
u'midv_data_importer',
"""It is a strong recommendation that you do vacuum the database now, do you want to do so?\n(If unsure - then answer "yes".)"""
)),
ru(
QCoreApplication.translate(u'midv_data_importer',
'Vacuum the database?')))
if sanity.result == 1:
PyQt4.QtGui.QApplication.setOverrideCursor(
PyQt4.QtCore.Qt.WaitCursor)
dbconnection.vacuum(
) # since a temporary table was loaded and then deleted - the db may need vacuuming
PyQt4.QtGui.QApplication.restoreOverrideCursor()
def export_2_csv(self, exportfolder):
dbconnection = db_utils.DbConnectionManager()
dbconnection.connect2db() #establish connection to the current midv db
self.curs = dbconnection.cursor #get a cursor
self.exportfolder = exportfolder
self.write_data(
self.to_csv, self.ID_obs_points,
defs.get_subset_of_tables_fr_db(category='obs_points'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
self.write_data(
self.to_csv, self.ID_obs_lines,
defs.get_subset_of_tables_fr_db(category='obs_lines'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
self.write_data(
self.zz_to_csv, u'no_obsids',
defs.get_subset_of_tables_fr_db(category='data_domains'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
dbconnection.closedb()
def get_data_from_sql(self, table, obsids, columns):
"""
:param skip_or_keep_reports_with_parameters: a dict like {'IN': ['par1', 'par2'], 'NOT IN': ['par3', 'par4']}
The parameter skip_or_keep_reports_with_parameters is used to filter reports.
:param obsids:
:param dbconnection:
:return:
"""
sql = '''SELECT obsid, date_time, report, parameter, unit, %s FROM %s''' % (
', '.join(columns), table)
if obsids:
sql += ''' WHERE obsid in (%s)''' % sql_list(obsids)
dbconnection = db_utils.DbConnectionManager()
df = pd.read_sql(con=dbconnection.conn,
sql=sql,
parse_dates=['date_time'])
dbconnection.closedb()
return df
def create_vlayer(self, no_print=False):
dbconnection = db_utils.DbConnectionManager()
uri = dbconnection.uri
uri.setDataSource('', 'obs_points', 'geometry', '', 'obsid')
dbtype = db_utils.get_dbtype(dbconnection.dbtype)
self.vlayer = QgsVectorLayer(uri.uri(), 'TestLayer', dbtype)
QgsProject.instance().addMapLayer(self.vlayer)
features = self.vlayer.getFeatures()
feature_ids = [feature.id() for feature in features]
if not no_print:
print("1. Valid vlayer '{}'".format(self.vlayer.isValid()))
print("2. feature_ids: " + str(feature_ids))
print("5. QgsVectorLayer.getFeature(): " +
str([self.vlayer.getFeature(x).id() for x in feature_ids]))
print("6. QgsVectorLayer.getFeature() type: " + str(
[str(type(self.vlayer.getFeature(x))) for x in feature_ids]))
print("7. QgsVectorLayer.getFeatures(): " +
str([x.id() for x in self.vlayer.getFeatures(feature_ids)]))
print("8. QgsVectorLayer.featureCount(): " +
str(self.vlayer.featureCount()))
def strat_symbology(iface, plot_rings, plot_bars, plot_static_bars, bars_xfactor, bars_yfactor, static_bars_xfactor,
static_bars_yfactor):
"""
TODO: There is a logical bug where layers that should get caught as ELSE isn't because the shadow ("maxdepthbot" = "depthbot")
gets them... I might have to put the shadow in other layer...
:param iface:
:return:
"""
root = QgsProject.instance().layerTreeRoot()
dbconnection = db_utils.DbConnectionManager()
plot_types = defs.PlotTypesDict()
bedrock_geoshort = defs.bedrock_geoshort()
bedrock_types = plot_types[bedrock_geoshort]
geo_colors = defs.geocolorsymbols()
hydro_colors = defs.hydrocolors()
groupname = 'Midvatten strat symbology'
try:
add_views_to_db(dbconnection, bedrock_types)
except:
utils.MessagebarAndLog.critical(bar_msg=QCoreApplication.translate('strat_symbology', '''Creating database views failed, see log message panel'''),
log_msg=QCoreApplication.translate('strat_symbology', '''%s''')%str(traceback.format_exc()))
dbconnection.closedb()
return
old_group = root.findGroup(groupname)
previously_visible = ''
if old_group:
for child in old_group.children():
if child.isVisible():
previously_visible = child.name()
else:
previously_visible = 'Bars'
root.removeChildNode(root.findGroup(groupname))
stratigraphy_group = qgis.core.QgsLayerTreeGroup(name=groupname, checked=True)
root.insertChildNode(0, stratigraphy_group)
stratigraphy_group.setIsMutuallyExclusive(True)
settings = {'bars': [plot_bars, bars_xfactor, bars_yfactor],
'static bars': [plot_static_bars, static_bars_xfactor, static_bars_yfactor],
'rings': [plot_rings, None, None]}
for name, stylename, wlvls_stylename, bedrock_stylename in (('Bars', 'bars_strat', 'bars_w_lvls_last_geom', 'bars_bedrock'),
('Static bars', 'bars_static_strat', 'bars_static_w_lvls_last_geom', 'bars_static_bedrock'),
('Rings', 'rings_strat', None, 'rings_bedrock')):
if not settings[name.lower()][0]:
continue
xfactor = settings[name.lower()][1]
yfactor = settings[name.lower()][2]
group = qgis.core.QgsLayerTreeGroup(name=name)
group.setExpanded(False)
stratigraphy_group.insertChildNode(0, group)
layers = []
add_layers_to_list(layers, ['bars_strat', 'bars_strat', 'w_lvls_last_geom', 'bedrock'], geometrycolumn='geometry', dbconnection=dbconnection, layernames=['Geology', 'Hydro', 'W levels', 'Bedrock'])
symbology_using_cloning(plot_types, geo_colors, layers[0], stylename, 'geoshort')
symbology_using_cloning({k: "= '{}'".format(k) for k in sorted(hydro_colors.keys())}, hydro_colors, layers[1], stylename, 'capacity')
scale_geometry_by_factor(layers[0], xfactor=xfactor, yfactor=yfactor)
scale_geometry_by_factor(layers[1], xfactor=xfactor, yfactor=yfactor)
QgsProject.instance().addMapLayers(layers[:2], False)
if wlvls_stylename is not None and 'h_tocags' in layers[2].fields().names():
apply_style(layers[2], wlvls_stylename)
scale_geometry_by_factor(layers[2], xfactor=xfactor, yfactor=yfactor)
QgsProject.instance().addMapLayer(layers[2], False)
group.addLayer(layers[2])
group.children()[-1].setExpanded(False)
if bedrock_stylename is not None:
create_bedrock_symbology(layers[3], bedrock_stylename, bedrock_geoshort, group)
scale_geometry_by_factor(layers[3], xfactor=xfactor, yfactor=yfactor)
color_group = qgis.core.QgsLayerTreeGroup(name='Layers', checked=True)
color_group.setIsMutuallyExclusive(True)
group.insertChildNode(-1, color_group)
color_group.addLayer(layers[0])
color_group.addLayer(layers[1])
for child in stratigraphy_group.children():
if child.name() == previously_visible:
child.setItemVisibilityChecked(True)
iface.mapCanvas().refresh()
def add_layers_new_method(self):
if self.group_name == 'Midvatten_OBS_DB':
position_index = 0
else:
position_index = 1
MyGroup = qgis.core.QgsLayerTreeGroup(name=self.group_name,
checked=True)
self.root.insertChildNode(position_index, MyGroup)
dbconnection = db_utils.DbConnectionManager()
canvas = self.iface.mapCanvas()
layer_list = []
if self.group_name == 'Midvatten_OBS_DB':
add_layers_to_list(layer_list,
self.default_nonspatlayers,
dbconnection=dbconnection)
add_layers_to_list(layer_list,
self.default_layers,
geometrycolumn='geometry',
dbconnection=dbconnection)
elif self.group_name == 'Midvatten_data_domains': #if self.group_name == 'Midvatten_data_domains':
tables_columns = db_utils.tables_columns()
d_domain_tables = [
x for x in list(tables_columns.keys()) if x.startswith('zz_')
]
add_layers_to_list(layer_list,
d_domain_tables,
dbconnection=dbconnection)
w_lvls_last_geom = None
#now loop over all the layers and set styles etc
for layer in layer_list:
# TODO: Made this a comment, but there might be some hidden feature thats still needed!
#map_canvas_layer_list.append(QgsMapCanvasLayer(layer))
QgsProject.instance().addMapLayers([layer], False)
MyGroup.insertLayer(0, layer)
#MyGroup.addLayer(layer)
#TODO: Check if this isn't needed.
#if self.group_name == 'Midvatten_OBS_DB':
# layer.setEditorLayout(1) #perhaps this is unnecessary since it gets set from the loaded qml below?
#now try to load the style file
stylefile_sv = os.path.join(os.sep, os.path.dirname(__file__),
"..", "definitions",
layer.name() + "_sv.qml")
stylefile = os.path.join(os.sep, os.path.dirname(__file__), "..",
"definitions",
layer.name() + ".qml")
if utils.getcurrentlocale()[0] == 'sv_SE' and os.path.isfile(
stylefile_sv
): #swedish forms are loaded only if locale settings indicate sweden
try:
layer.loadNamedStyle(stylefile_sv)
except:
try:
layer.loadNamedStyle(stylefile)
except:
pass
else:
try:
layer.loadNamedStyle(stylefile)
except:
pass
if layer.name() == 'obs_points': #zoom to obs_points extent
obsp_lyr = layer
canvas.setExtent(layer.extent())
elif layer.name() == 'w_lvls_last_geom':
w_lvls_last_geom = layer
if w_lvls_last_geom is not None:
midv = self.root.findGroup('Midvatten_OBS_DB')
w_lvls_last_geom = MyGroup.findLayer(w_lvls_last_geom)
w_lvls_last_geom.setItemVisibilityCheckedRecursive(False)
#finally refresh canvas
dbconnection.closedb()
canvas.refresh()
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_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 populate_postgis_db(self, verno, user_select_CRS='y', EPSG_code='4326'):
dbconnection = db_utils.DbConnectionManager()
db_settings = dbconnection.db_settings
if not isinstance(db_settings, str):
self.db_settings = ru(utils.anything_to_string_representation(dbconnection.db_settings))
else:
self.db_settings = ru(db_settings)
if dbconnection.dbtype != 'postgis':
raise utils.UsageError('Database type postgis not selected, check Midvatten settings!')
dbconnection.execute('CREATE EXTENSION IF NOT EXISTS postgis;')
result = dbconnection.execute_and_fetchall('select version(), PostGIS_full_version();')
versionstext = ', '.join(result[0])
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()
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', 'PostGIS version %s' % ru(versionstext)),
('CHANGETOLOCALE', ru(set_locale)),
('double', 'double precision'),
('"', ''),
('rowid as rowid', 'CTID as rowid'),
('POSTGIS ', '')]
created_tables_sqls = {}
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 linenr, line in enumerate(sql_lines):
if all([line, not line.startswith("#"), 'InitSpatialMetadata' not in line, 'SPATIALITE' not in line,
line.replace(';', '').strip().replace('\n', '').replace('\r', '')]):
sql = self.replace_words(line, replace_word_replace_with)
try:
dbconnection.execute(sql)
except:
try:
print(str(sql))
print("numlines: " + str(len(sql_lines)))
print("Error on line nr {}".format(str(linenr)))
print("before " + sql_lines[linenr - 1])
if linenr + 1 < len(sql_lines):
print("after " + sql_lines[linenr + 1 ])
except:
pass
raise
else:
_sql = sql.lstrip('\r').lstrip('\n').lstrip()
if _sql.startswith('CREATE TABLE'):
tablename = ' '.join(_sql.split()).split()[2]
created_tables_sqls[tablename] = sql
#lines = [self.replace_words(line.decode('utf-8').rstrip('\n').rstrip('\r'), replace_word_replace_with) for line in f if all([line,not line.startswith("#"), 'InitSpatialMetadata' not in line])]
#db_utils.sql_alter_db(lines)
self.insert_datadomains(set_locale, dbconnection)
execute_sqlfile(get_full_filename('insert_obs_points_triggers_postgis.sql'), dbconnection)
execute_sqlfile(get_full_filename('insert_functions_postgis.sql'), dbconnection)
self.add_metadata_to_about_db(dbconnection, created_tables_sqls)
dbconnection.vacuum()
dbconnection.commit_and_closedb()
"""
#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 general_import(self, dest_table, file_data, allow_obs_fk_import=False,
_dbconnection=None, dump_temptable=False, source_srid=None,
skip_confirmation=False, binary_geometry=False):
"""General method for importing a list of list to a table
self.temptableName must be the name of the table containing the new data to import.
:param dest_table: The destination table
:param file_data: a list of list with a header list as first row
:param allow_obs_fk_import: True to allow creation of obsids in obs_points and obs_lines.
:param _dbconnection: A db_utils.DbConnectionManager-instance if other than the currently selected in the midvatten
settings dialog.
:param dump_temptable: True to create a csvfile from internal temporary table.
:param source_srid: The srid of the source geometry column if the geometry is a WKT or WKB
:param skip_confirmation: True to not ask the user to import foreign keys.
:param binary_geometry: True if the source geometry column should be parsed as a WKB, else it's parsed as WKT.
:return:
"""
self.temptable_name = None
if skip_confirmation:
self.foreign_keys_import_question = 1
try:
if file_data is None or not file_data:
return
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', '\nImport to %s starting\n--------------------')) % dest_table)
utils.start_waiting_cursor()
if not isinstance(_dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
else:
dbconnection = _dbconnection
db_utils.activate_foreign_keys(activated=True, dbconnection=dbconnection)
recsinfile = len(file_data[1:])
table_info = db_utils.db_tables_columns_info(table=dest_table, dbconnection=dbconnection)
if not table_info:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'The table %s did not exist. Update the database to latest version.')) % dest_table)
else:
table_info = table_info[dest_table]
#POINT and LINESTRING must be cast as BLOB. So change the type to BLOB.
column_headers_types = db_utils.change_cast_type_for_geometry_columns(dbconnection, table_info, dest_table)
primary_keys = [row[1] for row in table_info if int(row[5])] #Not null columns are allowed if they have a default value.
not_null_columns = [row[1] for row in table_info if int(row[3]) and row[4] is None]
#Only use the columns that exists in the goal table.
existing_columns_in_dest_table = [col for col in file_data[0] if col in column_headers_types]
existing_columns_in_temptable = file_data[0]
missing_columns = [column for column in not_null_columns if column not in existing_columns_in_dest_table]
if missing_columns:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Required columns %s are missing for table %s')) % (', '.join(missing_columns), dest_table))
primary_keys_for_concat = [pk for pk in primary_keys if pk in existing_columns_in_temptable]
self.list_to_table(dbconnection, dest_table, file_data, primary_keys_for_concat)
#Delete records from self.temptable where yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already exist for the same date.
nr_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
if 'date_time' in primary_keys:
self.delete_existing_date_times_from_temptable(primary_keys, dest_table, dbconnection)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_same_date = nr_after - nr_before
if nr_same_date > 0:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows with the same date \non format yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already existed and will not be imported. %s rows remain.'))%(str(nr_same_date), str(nr_after)))
if not nr_after > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported to %s after deleting duplicate date_times')) % dest_table)
return
#Special cases for some tables
if dest_table == 'stratigraphy':
self.check_and_delete_stratigraphy(existing_columns_in_dest_table, dbconnection)
# Dump temptable to csv for debugging
if dump_temptable:
dbconnection.dump_table_2_csv(self.temptable_name)
# Import foreign keys in some special cases
foreign_keys = db_utils.get_foreign_keys(dest_table, dbconnection=dbconnection)
if foreign_keys:
if not allow_obs_fk_import:
for table in ['obs_points', 'obs_lines']:
if table in foreign_keys:
del foreign_keys[table]
if foreign_keys:
if self.foreign_keys_import_question is None:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nForeign keys will be imported silently into "%s" if needed. \n\nProceed?""")) % (', '.join(list(foreign_keys.keys())))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Info!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
else:
self.foreign_keys_import_question = 1
if self.foreign_keys_import_question == 1:
nr_before = nr_after
self.import_foreign_keys(dbconnection, dest_table, self.temptable_name, foreign_keys, existing_columns_in_temptable)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_after_foreign_keys = nr_before - nr_after
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows were deleted due to foreign keys restrictions and "%s" rows remain.'))%(str(nr_after_foreign_keys), str(nr_after)))
if not nr_after > 0:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported, see log message panel')))
#Finally import data:
nr_failed_import = recsinfile - nr_after
if nr_failed_import > 0:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nThere are %s rows in your data that can not be imported!\nDo you really want to import the rest?\nAnswering yes will start, from top of the imported file and only import the first of the duplicates.\n\nProceed?""" ))% (str(nr_failed_import))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Warning!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
# Check if current table has geometry:
geom_columns = db_utils.get_geometry_types(dbconnection, dest_table)
sourcecols = []
for colname in sorted(existing_columns_in_dest_table):
null_replacement = db_utils.cast_null(column_headers_types[colname], dbconnection)
if colname in list(geom_columns.keys()) and colname in existing_columns_in_temptable:
sourcecols.append(self.create_geometry_sql(colname, dest_table, dbconnection, source_srid,
null_replacement, binary_geometry))
else:
sourcecols.append(
"""(CASE WHEN ({colname} !='' AND {colname} !=' ' AND {colname} IS NOT NULL)\n THEN CAST({colname} AS {type}) ELSE {null} END)""".format(
colname=colname,
type=column_headers_types[colname],
null=null_replacement))
sql = """INSERT INTO {dest_table} ({dest_columns})\nSELECT {source_columns}\nFROM {source_table}\n"""
kwargs = {'dest_table': dest_table,
'dest_columns': ', '.join(sorted(existing_columns_in_dest_table)),
'source_table': self.temptable_name,
'source_columns': u',\n '.join(sourcecols)
}
if not_null_columns:
sql += """WHERE {notnullcheck}"""
kwargs['notnullcheck'] = ' AND '.join(['%s IS NOT NULL'%notnullcol
for notnullcol in sorted(not_null_columns)])
sql = sql.format(**kwargs)
recsbefore = dbconnection.execute_and_fetchall('select count(*) from %s' % (dest_table))[0][0]
try:
dbconnection.execute(sql)
except Exception as e:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'INSERT failed while importing to %s. Using INSERT OR IGNORE instead. Msg:\n')) % dest_table + ru(str(e)))
sql = db_utils.add_insert_or_ignore_to_sql(sql, dbconnection)
try:
dbconnection.execute(sql)
except Exception as e:
try:
str(e)
except UnicodeDecodeError:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.')) % (sql), duration=999)
else:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.\nMsg:\n%s')) % (sql, ru(str(e))), duration=999)
recsafter = dbconnection.execute_and_fetchall('select count(*) from %s' % (dest_table))[0][0]
nr_imported = recsafter - recsbefore
nr_excluded = recsinfile - nr_imported
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('midv_data_importer', '%s rows imported and %s excluded for table %s. See log message panel for details'))%(nr_imported, nr_excluded, dest_table),
log_msg='--------------------')
except:
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
try:
dbconnection.closedb()
except:
pass
else:
if self.temptable_name is not None:
#try:
dbconnection.drop_temporary_table(self.temptable_name)
#except:
# pass
utils.stop_waiting_cursor()
raise
else:
dbconnection.commit()
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
try:
dbconnection.closedb()
except:
pass
else:
if self.temptable_name is not None:
#try:
dbconnection.drop_temporary_table(self.temptable_name)
#except:
# pass
utils.stop_waiting_cursor()
def add_layers_new_method(self):
try: #qgis>=2.4
if self.group_name == 'Midvatten_OBS_DB':
position_index = 0
else:
position_index = 1
MyGroup = self.root.insertGroup(position_index, self.group_name)
except: #qgis < 2.4
MyGroup = self.legend.addGroup(self.group_name, 1, -1)
dbconnection = db_utils.DbConnectionManager()
uri = dbconnection.uri
dbtype = dbconnection.dbtype
schema = dbconnection.schemas()
#For QgsVectorLayer, dbtype has to be postgres instead of postgis
dbtype = db_utils.get_dbtype(dbtype)
canvas = self.iface.mapCanvas()
layer_list = []
map_canvas_layer_list = []
if self.group_name == 'Midvatten_OBS_DB':
self.add_layers_to_list(layer_list, uri, schema,
self.default_nonspatlayers, dbtype)
self.add_layers_to_list(layer_list, uri, schema,
self.default_layers, dbtype, 'geometry')
elif self.group_name == 'Midvatten_data_domains': #if self.group_name == 'Midvatten_data_domains':
tables_columns = db_utils.tables_columns()
d_domain_tables = [
x for x in tables_columns.keys() if x.startswith(u'zz_')
]
self.add_layers_to_list(layer_list, uri, schema, d_domain_tables,
dbtype)
#now loop over all the layers and set styles etc
for layer in layer_list:
map_canvas_layer_list.append(QgsMapCanvasLayer(layer))
try: #qgis>=2.4
QgsMapLayerRegistry.instance().addMapLayers([layer], False)
MyGroup.insertLayer(0, layer)
#MyGroup.addLayer(layer)
except: #qgis<2.4
QgsMapLayerRegistry.instance().addMapLayers([layer])
group_index = self.legend.groups().index(self.group_name)
self.legend.moveLayer(self.legend.layers()[0], group_index)
if self.group_name == 'Midvatten_OBS_DB':
layer.setEditorLayout(
1
) #perhaps this is unnecessary since it gets set from the loaded qml below?
#now try to load the style file
stylefile_sv = os.path.join(os.sep, os.path.dirname(__file__),
"..", "definitions",
layer.name() + "_sv.qml")
stylefile = os.path.join(os.sep, os.path.dirname(__file__), "..",
"definitions",
layer.name() + ".qml")
if utils.getcurrentlocale()[0] == 'sv_SE' and os.path.isfile(
stylefile_sv
): #swedish forms are loaded only if locale settings indicate sweden
try:
layer.loadNamedStyle(stylefile_sv)
except:
try:
layer.loadNamedStyle(stylefile)
except:
pass
else:
try:
layer.loadNamedStyle(stylefile)
except:
pass
if layer.name() == 'obs_points': #zoom to obs_points extent
obsp_lyr = layer
canvas.setExtent(layer.extent())
elif layer.name(
) == 'w_lvls_last_geom': #we do not want w_lvls_last_geom to be visible by default
self.legend.setLayerVisible(layer, False)
else:
pass
#finally refresh canvas
canvas.refresh()
def _getDataStep2(self, surveys):
""" STEP 2: get strata information for every point """
dbconnection = db_utils.DbConnectionManager()
for (obsid, survey) in surveys.iteritems():
sql = r"""SELECT stratid, depthtop, depthbot, geology, lower(geoshort), capacity, comment, development FROM """
sql += self.stratitable #MacOSX fix1
sql += r""" WHERE obsid = '"""
sql += str(
obsid
) # THIS IS WHERE THE KEY IS GIVEN TO LOAD STRATIGRAPHY FOR CHOOSEN obsid
sql += """' ORDER BY stratid"""
recs = dbconnection.execute_and_fetchall(sql)
# parse attributes
prev_depthbot = 0
for record in recs:
if utils.isinteger(record[0]) and utils.isfloat(
record[1]) and utils.isfloat(record[2]):
stratigaphy_id = record[0] # Stratigraphy layer no
depthtotop = record[
1] # depth to top of stratrigraphy layer
depthtobot = record[
2] # depth to bottom of stratrigraphy layer
else:
raise DataSanityError(
str(obsid),
ru(
QCoreApplication.translate(
u'SurveyStore',
u"Something bad with stratid, depthtop or depthbot!"
)))
stratigaphy_id = 1 # when something went wrong, put it into first layer
depthtotop = 0
depthtobot = 999 #default value when something went wrong
if record[
3]: # Must check since it is not possible to print null values as text in qt widget
geology = record[3] # Geology full text
else:
geology = " "
geo_short_txt = record[
4] # geo_short might contain national special characters
if geo_short_txt: # Must not try to encode an empty field
geo_short = unicodedata.normalize(
'NFKD', geo_short_txt).encode(
'ascii', 'ignore'
) # geo_short normalized for symbols and color
else: # If the field is empty, then store an empty string
geo_short = ''
hydro = record[5] # waterloss (hydrogeo parameter) for color
if record[
6]: # Must check since it is not possible to print null values as text in qt widget
comment = record[6] #
else:
comment = " "
if record[
7]: # Must check since it is not possible to print null values as text in qt widget
development = record[7] #
else:
development = " "
#Add layer if there is a gap
if depthtotop != prev_depthbot:
stratid = len(survey.strata) + 1
st = StrataInfo(stratid, prev_depthbot, depthtotop)
survey.strata.append(st)
stratid = len(survey.strata) + 1
st = StrataInfo(stratid, depthtotop, depthtobot, geology,
geo_short, hydro, comment, development)
survey.strata.append(st)
prev_depthbot = depthtobot
DataLoadingStatus = True
dbconnection.closedb()
return DataLoadingStatus, surveys
def __init__(self, obsids, settingsdict, general_metadata, geo_metadata,
strat_columns, header_in_table, skip_empty, include_comments,
general_metadata_header, geo_metadata_header,
strat_columns_header, comment_header,
empty_row_between_obsids, topleft_topright_colwidths,
general_colwidth, geo_colwidth, decimal_separator):
reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports')
if not os.path.exists(reportfolder):
os.makedirs(reportfolder)
reportpath = os.path.join(reportfolder, "drill_report.html")
logopath = os.path.join(os.sep, os.path.dirname(__file__), "..",
"templates", "midvatten_logga.png")
imgpath = os.path.join(os.sep, os.path.dirname(__file__), "..",
"templates")
if len(obsids) == 0:
utils.pop_up_info(
ru(
QCoreApplication.translate(
'Drillreport', "Must select one or more obsids!")))
return None
obsids = sorted(set(obsids))
obs_points_translations = {
'obsid':
ru(QCoreApplication.translate('Drillreport2', 'obsid')),
'name':
ru(QCoreApplication.translate('Drillreport2', 'name')),
'place':
ru(QCoreApplication.translate('Drillreport2', 'place')),
'type':
ru(QCoreApplication.translate('Drillreport2', 'type')),
'length':
ru(QCoreApplication.translate('Drillreport2', 'length')),
'drillstop':
ru(QCoreApplication.translate('Drillreport2', 'drillstop')),
'diam':
ru(QCoreApplication.translate('Drillreport2', 'diam')),
'material':
ru(QCoreApplication.translate('Drillreport2', 'material')),
'screen':
ru(QCoreApplication.translate('Drillreport2', 'screen')),
'capacity':
ru(QCoreApplication.translate('Drillreport2', 'capacity')),
'drilldate':
ru(QCoreApplication.translate('Drillreport2', 'drilldate')),
'wmeas_yn':
ru(QCoreApplication.translate('Drillreport2', 'wmeas_yn')),
'wlogg_yn':
ru(QCoreApplication.translate('Drillreport2', 'wlogg_yn')),
'east':
ru(QCoreApplication.translate('Drillreport2', 'east')),
'north':
ru(QCoreApplication.translate('Drillreport2', 'north')),
'ne_accur':
ru(QCoreApplication.translate('Drillreport2', 'ne_accur')),
'ne_source':
ru(QCoreApplication.translate('Drillreport2', 'ne_source')),
'h_toc':
ru(QCoreApplication.translate('Drillreport2', 'h_toc')),
'h_tocags':
ru(QCoreApplication.translate('Drillreport2', 'h_tocags')),
'h_gs':
ru(QCoreApplication.translate('Drillreport2', 'h_gs')),
'h_accur':
ru(QCoreApplication.translate('Drillreport2', 'h_accur')),
'h_syst':
ru(QCoreApplication.translate('Drillreport2', 'h_syst')),
'h_source':
ru(QCoreApplication.translate('Drillreport2', 'h_source')),
'source':
ru(QCoreApplication.translate('Drillreport2', 'source')),
'com_onerow':
ru(QCoreApplication.translate('Drillreport2', 'com_onerow')),
'com_html':
ru(QCoreApplication.translate('Drillreport2', 'com_html'))
}
"""
thelist = [ "obsid", "stratid", "depthtop", "depthbot", "geology", "geoshort", "capacity", "development", "comment"]
>>> y = '\n'.join(["'%s'"%x + ': ' + "ru(QCoreApplication.translate('Drillreport2', '%s')),"%x for x in thelist])
>>> print(y)
"""
dbconnection = db_utils.DbConnectionManager()
obs_points_cols = [
"obsid", "name", "place", "type", "length", "drillstop", "diam",
"material", "screen", "capacity", "drilldate", "wmeas_yn",
"wlogg_yn", "east", "north", "ne_accur", "ne_source", "h_toc",
"h_tocags", "h_gs", "h_accur", "h_syst", "h_source", "source",
"com_onerow", "com_html"
]
all_obs_points_data = ru(db_utils.get_sql_result_as_dict(
'SELECT %s FROM obs_points WHERE obsid IN (%s) ORDER BY obsid' %
(', '.join(obs_points_cols), ', '.join(
["'{}'".format(x) for x in obsids])),
dbconnection=dbconnection)[1],
keep_containers=True)
if strat_columns:
strat_sql_columns_list = [x.split(';')[0] for x in strat_columns]
if 'depth' in strat_sql_columns_list:
strat_sql_columns_list.extend(['depthtop', 'depthbot'])
strat_sql_columns_list.remove('depth')
strat_sql_columns_list = [
x for x in strat_sql_columns_list if x not in ('obsid')
]
all_stratigrapy_data = ru(db_utils.get_sql_result_as_dict(
'SELECT obsid, %s FROM stratigraphy WHERE obsid IN (%s) ORDER BY obsid, stratid'
% (', '.join(strat_sql_columns_list), ', '.join(
["'{}'".format(x) for x in obsids])),
dbconnection=dbconnection)[1],
keep_containers=True)
else:
all_stratigrapy_data = {}
strat_sql_columns_list = []
crs = ru(
db_utils.sql_load_fr_db(
"""SELECT srid FROM geometry_columns where f_table_name = 'obs_points'""",
dbconnection=dbconnection)[1][0][0])
crsname = ru(db_utils.get_srid_name(crs, dbconnection=dbconnection))
dbconnection.closedb()
f, rpt = self.open_file(', '.join(obsids), reportpath)
rpt += r"""<html>"""
for obsid in obsids:
obs_points_data = all_obs_points_data[obsid][0]
general_data_no_rounding = [
x.split(';')[0] for x in general_metadata
]
general_rounding = [
x.split(';')[1] if len(x.split(';')) == 2 else None
for x in general_metadata
]
general_data = [
(obs_points_translations.get(header, header),
obs_points_data[obs_points_cols.index(header) - 1])
for header in general_data_no_rounding
]
if geo_metadata:
geo_metadata_no_rounding = [
x.split(';')[0] for x in geo_metadata
]
geo_rounding = [
x.split(';')[1] if len(x.split(';')) == 2 else None
for x in geo_metadata
]
geo_data = [
(obs_points_translations.get(header, header),
obs_points_data[obs_points_cols.index(header) - 1])
for header in geo_metadata_no_rounding
]
if 'east' in geo_metadata_no_rounding or 'north' in geo_metadata_no_rounding:
geo_data.append(
(ru(
QCoreApplication.translate('Drillreport2',
'XY Reference system')),
'%s' % ('%s, ' % crsname if crsname else '') +
'EPSG:' + crs))
else:
geo_data = []
geo_rounding = []
strat_data = all_stratigrapy_data.get(obsid, None)
if include_comments:
comment_data = [
obs_points_data[obs_points_cols.index(header) - 1]
for header in ('com_onerow', 'com_html') if all([
obs_points_data[obs_points_cols.index(header) - 1]
is not None, obs_points_data[
obs_points_cols.index(header) -
1].replace('NULL', ''), obs_points_data[
obs_points_cols.index(header) -
1].strip(), 'text-indent:0px;"><br /></p>'
not in obs_points_data[obs_points_cols.index(header) -
1], 'text-indent:0px;"></p>'
not in obs_points_data[obs_points_cols.index(header) -
1], 'text-indent:0px;">NULL</p>'
not in obs_points_data[obs_points_cols.index(header) -
1].strip()
])
]
else:
comment_data = []
rpt += self.write_obsid(
obsid,
general_data,
geo_data,
strat_data,
comment_data,
strat_columns,
header_in_table=header_in_table,
skip_empty=skip_empty,
general_metadata_header=general_metadata_header,
geo_metadata_header=geo_metadata_header,
strat_columns_header=strat_columns_header,
comment_header=comment_header,
general_rounding=general_rounding,
geo_rounding=geo_rounding,
strat_sql_columns_list=strat_sql_columns_list,
topleft_topright_colwidths=topleft_topright_colwidths,
general_colwidth=general_colwidth,
geo_colwidth=geo_colwidth,
decimal_separator=decimal_separator)
rpt += r"""<p> </p>"""
if empty_row_between_obsids:
rpt += r"""<p>empty_row_between_obsids</p>"""
rpt += r"""</html>"""
f.write(rpt)
self.close_file(f, reportpath)
def __init__(self, layer, settingsdict={}):
#show the user this may take a long time...
utils.start_waiting_cursor()
self.settingsdict = settingsdict
provider = layer.dataProvider() # OGR provider
kolumnindex = provider.fieldNameIndex(
'obsid') # To find the column named 'obsid'
observations = layer.getSelectedFeatures()
reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports')
if not os.path.exists(reportfolder):
os.makedirs(reportfolder)
reportpath = os.path.join(reportfolder, "w_qual_report.html")
#f = open(reportpath, "wb" )
f = codecs.open(reportpath, "wb", "utf-8")
#write some initiating html
rpt = r"""<head><title>%s</title></head>""" % ru(
QCoreApplication.translate(
'Wqualreport',
'water quality report from Midvatten plugin for QGIS'))
rpt += r""" <meta http-equiv="content-type" content="text/html; charset=utf-8" />""" #NOTE, all report data must be in 'utf-8'
rpt += "<html><body>"
#rpt += "<table width=\"100%\" border=\"1\">\n"
#rpt2 = rpt.encode("utf-8")
f.write(rpt)
dbconnection = db_utils.DbConnectionManager()
for feature in observations:
attributes = feature.attributes()
obsid = attributes[kolumnindex]
try:
print('about to get data for ' + obsid + ', at time: ' +
str(time.time())) #debug
except:
pass
ReportData = self.GetData(
self.settingsdict['database'], obsid,
dbconnection) # one observation at a time
try:
print('done with getting data for ' + obsid + ', at time: ' +
str(time.time())) #debug
except:
pass
if ReportData:
self.WriteHTMLReport(ReportData, f)
try:
print('wrote html report for ' + obsid + ', at time: ' +
str(time.time())) #debug
except:
pass
dbconnection.closedb()
#write some finishing html and close the file
f.write("\n</body></html>")
f.close()
utils.stop_waiting_cursor(
) #now this long process is done and the cursor is back as normal
if ReportData:
QDesktopServices.openUrl(QUrl.fromLocalFile(reportpath))
def general_import(self,
goal_table,
file_data,
allow_obs_fk_import=False,
_dbconnection=None,
dump_temptable=False):
"""General method for importing an sqlite table into a goal_table
self.temptableName must be the name of the table containing the new data to import.
:param goal_table:
:return:
"""
try:
if file_data is None or not file_data:
return
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'\nImport to %s starting\n--------------------')) %
goal_table)
utils.start_waiting_cursor()
self.temptable_name = goal_table + '_temp'
if not isinstance(_dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
else:
dbconnection = _dbconnection
db_utils.activate_foreign_keys(activated=True,
dbconnection=dbconnection)
recsinfile = len(file_data[1:])
table_info = db_utils.db_tables_columns_info(
table=goal_table, dbconnection=dbconnection)
if not table_info:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'The table %s did not exist. Update the database to latest version.'
)) % goal_table)
else:
table_info = table_info[goal_table]
#POINT and LINESTRING must be cast as BLOB. So change the type to BLOB.
column_headers_types = db_utils.change_cast_type_for_geometry_columns(
dbconnection, table_info, goal_table)
primary_keys = [
row[1] for row in table_info if int(row[5])
] #Not null columns are allowed if they have a default value.
not_null_columns = [
row[1] for row in table_info if int(row[3]) and row[4] is None
]
#Only use the columns that exists in the goal table.
existing_columns_in_goal_table = [
col for col in file_data[0] if col in column_headers_types
]
existing_columns_in_temptable = file_data[0]
missing_columns = [
column for column in not_null_columns
if column not in existing_columns_in_goal_table
]
if missing_columns:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'Required columns %s are missing for table %s')) %
(', '.join(missing_columns), goal_table))
primary_keys_for_concat = [
pk for pk in primary_keys
if pk in existing_columns_in_temptable
]
self.list_to_table(dbconnection, file_data,
primary_keys_for_concat)
#Delete records from self.temptable where yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already exist for the same date.
nr_before = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' % (self.temptable_name))[0][0]
if 'date_time' in primary_keys:
self.delete_existing_date_times_from_temptable(
primary_keys, goal_table, dbconnection)
nr_after = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_same_date = nr_after - nr_before
if nr_same_date > 0:
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'In total "%s" rows with the same date \non format yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already existed and will not be imported. %s rows remain.'
)) % (str(nr_same_date), str(nr_after)))
if not nr_after > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Nothing imported to %s after deleting duplicate date_times'
)) % goal_table)
return
#Special cases for some tables
if goal_table == 'stratigraphy':
self.check_and_delete_stratigraphy(
existing_columns_in_goal_table, dbconnection)
# Check if current table has geometry:
geom_columns = db_utils.get_geometry_types(dbconnection,
goal_table)
for geom_col in geom_columns.keys():
if geom_col in existing_columns_in_temptable:
self.calculate_geometry(geom_col, goal_table, dbconnection)
# Dump temptable to csv for debugging
if dump_temptable:
dbconnection.dump_table_2_csv(self.temptable_name)
# Import foreign keys in some special cases
foreign_keys = db_utils.get_foreign_keys(goal_table,
dbconnection=dbconnection)
if foreign_keys:
if not allow_obs_fk_import:
for table in ['obs_points', 'obs_lines']:
if table in foreign_keys:
del foreign_keys[table]
if foreign_keys:
if self.foreign_keys_import_question is None:
msg = ru(
QCoreApplication.translate(
'midv_data_importer',
"""Please note!\nForeign keys will be imported silently into "%s" if needed. \n\nProceed?"""
)) % (', '.join(list(foreign_keys.keys())))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser(
"YesNo", msg,
ru(
QCoreApplication.translate(
'midv_data_importer', "Info!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
else:
self.foreign_keys_import_question = 1
if self.foreign_keys_import_question == 1:
nr_before = nr_after
self.import_foreign_keys(
dbconnection, goal_table, self.temptable_name,
foreign_keys, existing_columns_in_temptable)
nr_after = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' %
(self.temptable_name))[0][0]
nr_after_foreign_keys = nr_before - nr_after
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'In total "%s" rows were deleted due to foreign keys restrictions and "%s" rows remain.'
)) % (str(nr_after_foreign_keys), str(nr_after)))
if not nr_after > 0:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'Nothing imported, see log message panel')))
#Finally import data:
nr_failed_import = recsinfile - nr_after
if nr_failed_import > 0:
msg = ru(
QCoreApplication.translate(
'midv_data_importer',
"""Please note!\nThere are %s rows in your data that can not be imported!\nDo you really want to import the rest?\nAnswering yes will start, from top of the imported file and only import the first of the duplicates.\n\nProceed?"""
)) % (str(nr_failed_import))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser(
"YesNo", msg,
ru(
QCoreApplication.translate('midv_data_importer',
"Warning!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
sql = """INSERT INTO %s (""" % goal_table
sql += ', '.join(sorted(existing_columns_in_goal_table))
sql += """) SELECT """
sql += ', '.join([
"""(CASE WHEN (%s !='' AND %s !=' ' AND %s IS NOT NULL) THEN CAST(%s AS %s) ELSE %s END)"""
% (colname, colname, colname, colname,
column_headers_types[colname],
db_utils.cast_null(column_headers_types[colname],
dbconnection))
for colname in sorted(existing_columns_in_goal_table)
])
sql += """FROM %s""" % (self.temptable_name)
if not_null_columns:
sql += """ WHERE %s""" % ' AND '.join([
'%s IS NOT NULL' % notnullcol
for notnullcol in sorted(not_null_columns)
])
recsbefore = dbconnection.execute_and_fetchall(
'select count(*) from %s' % (goal_table))[0][0]
try:
dbconnection.execute(sql)
except Exception as e:
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'INSERT failed while importing to %s. Using INSERT OR IGNORE instead. Msg:\n'
)) % goal_table + ru(str(e)))
sql = db_utils.add_insert_or_ignore_to_sql(sql, dbconnection)
try:
dbconnection.execute(sql)
except Exception as e:
try:
str(e)
except UnicodeDecodeError:
utils.MessagebarAndLog.critical(
bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Import failed, see log message panel')),
log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Sql\n%s failed.')) % (sql),
duration=999)
else:
utils.MessagebarAndLog.critical(
bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Import failed, see log message panel')),
log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Sql\n%s failed.\nMsg:\n%s')) %
(sql, ru(str(e))),
duration=999)
recsafter = dbconnection.execute_and_fetchall(
'select count(*) from %s' % (goal_table))[0][0]
nr_imported = recsafter - recsbefore
nr_excluded = recsinfile - nr_imported
utils.MessagebarAndLog.info(bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'%s rows imported and %s excluded for table %s. See log message panel for details'
)) % (nr_imported, nr_excluded, goal_table),
log_msg='--------------------')
#If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.commit_and_closedb()
else:
dbconnection.commit()
utils.stop_waiting_cursor()
except:
utils.stop_waiting_cursor()
try:
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.closedb()
else:
pass
except NameError():
pass
except:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate('midv_data_importer',
'Closing database failed!')))
raise
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()
