def getAllEdgvDomainsFromTableName(self, table):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
It checks for all attributes found.
:param table: (str) layer to be checked for its EDGV mapping.
:param table: (QgsVectorLayer) overloaded method - layer to be checked for its EDGV mapping.
:param field: (str) field to be checked.
:return: (dict) value map for all attributes that have one.
"""
ret = defaultdict(dict)
currentLayer = table if isinstance(table, QgsVectorLayer) else self.layerByName(table)
if currentLayer.isValid():
try:
uri = currentLayer.dataProvider().uri()
if uri.host() == '':
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(
uri.uri().split("|")[0].strip() if uri.uri().split("|")[0].strip().endswith(".gpkg") \
else uri.database()
)
sql = 'select code, code_name from dominios_{field} order by code'
else:
db = QSqlDatabase('QPSQL')
db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())
sql = 'select code, code_name from dominios.{field} order by code'
if not db.open():
db.close()
return ret
for field in currentLayer.fields():
fieldName = field.name()
if fieldName in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
if self.edgvVersion(db) in ("2.1.3 Pro", "3.0 Pro"):
cat = table if isinstance(table, str) else table.name()
# Pro versions now follow the logic "{attribute}_{CLASS_NAME}"
cat = cat.rsplit("_", 1)[0].split("_", 1)[-1]
else:
cat = (table if isinstance(table, str) else table.name()).split("_")[0]
fieldN = "{attribute}_{cat}".format(attribute=fieldName, cat=cat)
query = QSqlQuery(sql.format(field=fieldN), db)
else:
query = QSqlQuery(sql.format(field=fieldName), db)
if not query.isActive():
continue
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[fieldName][code_name] = code
db.close()
except:
pass
return ret
def testCreateEmptyLayer(self):
# cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)
uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True)
self.assertEqual(exporter.errorCount(), 0)
self.assertEqual(exporter.errorCode(), 0)
# check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')
# check that metadata table has been correctly populated
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
self.assertTrue(query.next())
self.assertEqual(query.value(0), "GEOM")
self.assertEqual(query.value(1), 4326)
query.finish()
# no feature, so we cannot guess the geometry type, so the layer is not valid
# but srid is set for provider in case you want to add a feature even if the layer is invalid!
# layer sourceCrs is empty because the layer is not considered spatial (not know geometry type)
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertFalse(vl.isValid())
self.assertEqual(vl.dataProvider().sourceCrs().authid(), "EPSG:4326")
# so we set the geometry type
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable type=POINT table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertTrue(vl.isValid())
self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")
f = QgsFeature(vl.fields())
f.setGeometry(QgsGeometry.fromWkt('POINT (43.5 1.42)'))
vl.dataProvider().addFeatures([f])
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_('SELECT "l"."GEOM"."SDO_SRID" from "QGIS"."EMPTY_LAYER" "l"'))
self.assertTrue(query.next())
self.assertEqual(query.value(0), 4326)
query.finish()
# now we can autodetect geom type and srid
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertTrue(vl.isValid())
self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")
def get_count_dates(self):
query = QSqlQuery(self.db)
query_str = ("select start_process_date, end_process_date "
"from comptages.count where id = {};".format(
self.count_id))
query.exec_(query_str)
result = []
while query.next():
start_date = query.value(0)
end_date = query.value(1)
i = 0
while True:
date = start_date.addDays(i)
if date <= end_date:
result.append(
(int(date.toString('yyyy')), int(date.toString('MM')),
int(date.toString('dd'))))
i += 1
else:
break
return result
def getLayersWithElementsV2(self, layerList, useInheritance=False):
self.checkAndOpenDb()
lyrWithElemList = []
for layer in layerList:
if isinstance(layer, dict):
schema = layer['tableSchema']
lyr = layer['tableName']
else:
if '.' in layer:
schema, lyr = layer.replace('"', '').split('.')
else:
lyr = layer
schema = self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayerV2(schema, lyr,
useInheritance)
query = QSqlQuery(sql, self.db)
if not query.next():
# use may not have permission to read the table from schema
QgsMessageLog.logMessage(
self.tr("Unable to read table {0}. Error message: '{1}'")\
.format(
self.db.databaseName(),
self.db.lastError().databaseText()
),
"DSGTools Plugin",
Qgis.Warning
)
continue
if query.value(0) > 0:
lyrWithElemList.append(lyr)
return lyrWithElemList
def getDbsFromServer(self, name):
"""
Gets server databases
name: server name
"""
gen = self.factory.createSqlGenerator(driver=DsgEnums.DriverPostGIS)
(host, port, user, password) = self.getServerConfiguration(name)
database = 'postgres'
postgisDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS)
postgisDb.connectDatabaseWithParameters(host, port, database, user,
password)
if not postgisDb.db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSGTools Plugin",
Qgis.Critical)
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr('A problem occurred! Check log for details.'))
query = QSqlQuery(gen.getDatabasesFromServer(), postgisDb.db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr("Problem executing query: ") +
query.lastError().text())
dbList = []
while query.next():
dbList.append(query.value(0))
postgisDb.closeDatabase()
return self.browseServer(dbList, host, port, user, password)
def get_detail_category_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select cat.code, cat.name, count(det.id_category) from "
"comptages.count_detail as det "
"join comptages.category as cat "
"on det.id_category = cat.id "
"join comptages.lane as lan "
"on det.id_lane = lan.id "
"where det.id_count = {} "
"and det.import_status = {} "
"and lan.id_section = '{}' "
"group by det.id_category, cat.code, cat.name "
"order by cat.code;".format(count_id, status, section_id))
query.exec_(query_str)
labels = []
values = []
while query.next():
labels.append("{} ({})".format(str(query.value(1)),
str(query.value(0))))
values.append(query.value(2))
return labels, values
def write_row_into_db(self, line):
row = self.parse_data_line(line)
if not row:
return
query = QSqlQuery(self.db)
query_str = ("insert into comptages.count_detail ("
"numbering, timestamp, "
"distance_front_front, distance_front_back, "
"speed, length, height, "
"file_name, import_status, "
"id_lane, id_count, id_category) values ("
"{}, '{}', {}, {}, {}, {}, '{}', '{}', {}, {}, "
"{}, {})".format(
row['numbering'],
row['timestamp'],
row['distance_front_front'],
row['distance_front_back'],
row['speed'],
row['length'],
row['height'],
self.basename,
Layers.IMPORT_STATUS_QUARANTINE,
self.lanes[int(row['lane'])+1],
self.count_id,
self.categories[row['category']]))
query.exec_(query_str)
def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True):
"""
Add geom to the layer and check everything is OK
"""
table = layer.dataProvider().uri().table()
# insert geom in layer
self.assertTrue(layer.startEditing())
feature = QgsFeature(layer.fields())
geom = QgsGeometry.fromWkt(wkt)
feature.setAttributes([pk])
feature.setGeometry(geom)
self.assertTrue(layer.addFeature(feature))
self.assertTrue(layer.commitChanges())
if check_valid:
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format(
table, pk)
res = query.exec_(sql)
self.assertTrue(res, sql + ': ' + query.lastError().text())
query.next()
valid = query.value(0)
self.assertTrue(
valid,
"geometry '{}' inserted in database is not valid".format(wkt))
query.finish()
expected_wkt = wkt if wkt_ref is None else wkt_ref
res_wkt = layer.getFeature(pk).geometry().asWkt()
self.assertTrue(
compareWkt(res_wkt, expected_wkt, 0.00001),
"\nactual = {}\nexpected = {}".format(res_wkt, expected_wkt))
def getStructureDict(self):
'''
Gets database structure according to the edgv version
'''
self.checkAndOpenDb()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting database structure: ") +
query.lastError().text())
while query.next():
className = str(query.value(0))
classSql = str(query.value(1))
if className.split('_')[0] == 'complexos' or className.split(
'_')[-1] in ['p', 'l', 'a']:
if className not in list(classDict.keys()):
classDict[className] = dict()
classSql = classSql.split(className)[1]
sqlList = classSql.replace('(', '').replace(')', '').replace(
'\"', '').replace('\'', '').split(',')
for s in sqlList:
fieldName = str(s.strip().split(' ')[0])
classDict[className][fieldName] = fieldName
if 'GEOMETRY' in list(classDict[className].keys()):
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in list(classDict[className].keys()):
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in list(classDict[className].keys()):
classDict[className]['OGC_FID'] = 'id'
return classDict
def get_aggregate_category_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select sum(cls.value), cat.code, cat.name from "
"comptages.count_aggregate as agg "
"join comptages.count_aggregate_value_cls as cls "
"on agg.id = cls.id_count_aggregate "
"join comptages.category as cat "
"on cls.id_category = cat.id "
"join comptages.lane as lan "
"on agg.id_lane = lan.id "
"where agg.id_count = {} and agg.type = 'CLS' "
"and agg.import_status = {} "
"and lan.id_section = '{}' "
"group by cat.code, cat.name "
"order by cat.code;".format(count_id, status, section_id))
query.exec_(query_str)
labels = []
values = []
while query.next():
labels.append("{} ({})".format(str(query.value(2)),
str(query.value(1))))
values.append(query.value(0))
return labels, values
def test_data_detail(self):
self.db.open()
query = QSqlQuery(self.db)
query.exec_("SELECT id FROM comptages.installation \
WHERE name = '64080011';")
query.next()
installation_id = query.value(0)
query.exec_("SELECT id FROM comptages.model \
WHERE name = 'M660_LT';")
query.next()
model_id = query.value(0)
query.exec_("select id from comptages.sensor_type \
where name = 'Tube'")
query.next()
sensor_type_id = query.value(0)
query.exec_("select id from comptages.class \
where name = 'SWISS10'")
query.next()
class_id = query.value(0)
query_str = (
"INSERT INTO comptages.count(id, "
"start_process_date, end_process_date, start_service_date, "
"end_service_date, id_sensor_type, id_model, id_class, "
"id_installation) "
"VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', "
"'2018-12-20', {}, {}, {}, {});".format(
sensor_type_id, model_id, class_id, installation_id))
query.exec_(query_str)
task = self.comptages.import_file(
os.path.join(
self.test_data_path,
'data_loader_simple_detail.V01'),
1)
task.waitForFinished()
# Let the time to the db to finish the writing
time.sleep(1)
data_loader = DataLoader(
1, '64080011', self.layers.IMPORT_STATUS_QUARANTINE)
count_data = data_loader.load()
self.assertEqual(
[1, 0, 3, 0, 1, 3, 0, 1, 0, 0, 0, 0, 2],
count_data.day_data[0].hour_data[15].direction_data[0].speed_data)
self.assertEqual(
[1, 1, 2, 1, 1, 1, 1, 1, 1, 1],
count_data.day_data[0].hour_data[15].direction_data[0].category_data)
self.assertEqual(20, count_data.day_data[0].total())
self.assertEqual(16, count_data.day_data[0].light_vehicles())
self.assertEqual(4, count_data.day_data[0].heavy_vehicles())
self.assertEqual(20.0, count_data.day_data[0].percent_heavy_vehicles())
def get_sections_with_data_of_count(self, count_id, status):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = (
"select distinct(lan.id_section) from comptages.count_aggregate "
"as cou "
"join comptages.lane as lan "
"on lan.id = cou.id_lane "
"where cou.id_count = {0} "
"and import_status = {1} "
"union "
"select distinct(lan.id_section) from comptages.count_detail "
"as cou "
"join comptages.lane as lan "
"on lan.id = cou.id_lane "
"where cou.id_count = {0} "
"and import_status = {1} ".format(count_id, status))
result = []
query.exec_(query_str)
while query.next():
result.append(query.value(0))
return result
def get_aggregate_speed_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select sum(spd.value), spd.low, spd.high from "
"comptages.count_aggregate as agg "
"join comptages.count_aggregate_value_spd as spd "
"on agg.id = spd.id_count_aggregate "
"join comptages.lane as lan "
"on agg.id_lane = lan.id "
"where agg.id_count = {} and agg.type = 'SPD' "
"and agg.import_status = {} "
"and lan.id_section = '{}'"
"group by spd.low, spd.high "
"order by spd.low;".format(count_id, status, section_id))
query.exec_(query_str)
x = []
y = []
while query.next():
x.append("{}-{} km/h".format(str(query.value(1)),
str(query.value(2))))
y.append(query.value(0))
return x, y
def delete_count_data(self, count_id, section_id, status):
self.init_db_connection()
query = QSqlQuery(self.db)
query_strs = []
query_strs.append("delete from comptages.count_aggregate as cou "
"using comptages.lane as lan "
"where cou.id_lane = lan.id "
"and cou.id_count = {} "
"and cou.import_status = {} "
"and lan.id_section = '{}' ".format(
count_id, status, section_id))
query_strs.append("delete from comptages.count_detail as cou "
"using comptages.lane as lan "
"where cou.id_lane = lan.id "
"and cou.id_count = {} "
"and cou.import_status = {} "
"and lan.id_section = '{}' ".format(
count_id, status, section_id))
for _ in query_strs:
query.exec_(_)
push_info("Les données ont été supprimées")
def change_status_of_count_data(self, count_id, section_id, new_status):
self.init_db_connection()
query = QSqlQuery(self.db)
query_strs = []
query_strs.append(
"update comptages.count_aggregate as cou set import_status = {} "
"from comptages.lane as lan "
"where cou.id_lane = lan.id "
"and id_count = {} "
"and lan.id_section = '{}'".format(new_status, count_id,
section_id))
query_strs.append(
"update comptages.count_detail as cou set import_status = {} "
"from comptages.lane as lan "
"where cou.id_lane = lan.id "
"and id_count = {} "
"and lan.id_section = '{}'".format(new_status, count_id,
section_id))
for _ in query_strs:
query.exec_(_)
push_info("Les données ont été importées")
def get_runs(self):
q = QSqlQuery(db=self.db)
q.exec_('select run from hsrr.run_info order by run')
runs = []
while q.next():
runs.append(q.value(0))
return runs
def __init__(self):
self.file = os.path.join(os.path.dirname('__file__'), 'settings.db')
#self.file = 'C:/Users/da2/AppData/Roaming/QGIS/QGIS3/profiles/default/python/plugins/CatastroV3/settings.db'
print(self.file)
self.db = self.get_db()
self.query = QSqlQuery(self.db)
config = {
'host':
'localhost',
'dbname':
'',
'port':
5432,
'user':
'',
'pswd':
'',
'schema':
'public',
'layers': [
"departamento", "circunscripcion", "seccion", "chacra",
"quinta", "fraccion", "manzana", "parcela", "subparcela"
],
'partidos':
self.get_partidos()
}
self.set_config(config)
#print(config)
config2 = self.get_config()
print(config2)
def get_detail_time_chart_data_day_by_direction(self, count_id, day,
status, direction,
section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select date_part('hour', timestamp), "
"date_part('hour', timestamp) + 1, "
"count(date_part('hour', timestamp)) "
"from comptages.count_detail as cou "
"join comptages.lane as lan on "
"cou.id_lane = lan.id "
"where id_count = {} and "
"date_trunc('day', timestamp) = '{}' "
"and import_status = {} "
"and lan.direction = {} "
"and lan.id_section = '{}' "
"group by date_part('hour', timestamp);".format(
count_id, day, status, direction, section_id))
query.exec_(query_str)
x = [
"00h-01h", "01h-02h", "02h-03h", "03h-04h", "04h-05h", "05h-06h",
"06h-07h", "07h-08h", "08h-09h", "09h-10h", "10h-11h", "11h-12h",
"12h-13h", "13h-14h", "14h-15h", "15h-16h", "16h-17h", "17h-18h",
"18h-19h", "19h-20h", "20h-21h", "21h-22h", "22h-23h", "23h-00h"
]
y = [None] * 24
while query.next():
y[int(query.value(0))] = query.value(2)
return x, y
def testCreateEmptyLayer(self):
# cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)
uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True)
self.assertEqual(exporter.errorCount(), 0)
self.assertEqual(exporter.errorCode(), 0)
# check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')
# check that metadata table has been correctly populated
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
self.assertTrue(query.next())
self.assertEqual(query.value(0), "GEOM")
self.assertEqual(query.value(1), 4326)
query.finish()
vl = QgsVectorLayer(
self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" sql=',
'test', 'oracle')
self.assertTrue(vl.isValid())
def get_bcgw_column_key(db_table,user_name,user_pass):
# estimate a unique id column for an oracle table if OBJECTID does not exist
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
sql = f"SELECT cols.column_name \
FROM all_tab_cols cols where cols.table_name = '{table}' and cols.COLUMN_NAME like \'OBJECTID\'"
q.exec(sql)
if q.first():
key_c = q.value(0)
else:
sql = f"SELECT COLUMN_NAME FROM all_tab_cols where table_name = '{table}' \
order by COLUMN_ID FETCH FIRST 1 ROWS ONLY"
q.exec(sql)
if q.first():
key_c = q.value(0)
db.close()
return key_c
def getGeomColumnTupleList(self, showViews=False):
"""
list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)]
centroids are hidden by default
"""
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomColumnTupleList(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tuple list: ") +
query.lastError().text())
geomList = []
while query.next():
if edgvVersion in ['2.1.3', 'FTer_2a_Ed']:
geomList.append((query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]),
query.value(1), query.value(2), 'BASE TABLE'))
else:
geomList.append(
(query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]), query.value(1),
self.getResolvedGeomType(int(query.value(2))),
'BASE TABLE'))
return geomList
def setUp(self):
self.db.open()
query = QSqlQuery(self.db)
query.exec_("DELETE FROM comptages.count;")
query.exec_("DELETE FROM comptages.count_detail;")
self.db.close()
def execSQLCommand(self, sql, ignore_errors=False):
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
res = query.exec_(sql)
if not ignore_errors:
self.assertTrue(res, sql + ': ' + query.lastError().text())
query.finish()
def geocod(self, tipusVia, variant, codi, numIni, lletraIni='', numFi='', lletraFi=''):
self.dbLogConnexio()
if self.dbLog is None:
return None, None
if self.queryGeo is None:
self.queryGeo = QSqlQuery(self.dbLog)
try:
self.queryGeo.prepare("CALL QV_GEOCOD(:TIPUSVIA, :VARIANTE, :CODIGO, " +
":NUMINI, :LETRAINI, :NUMFIN, :LETRAFIN, :X, :Y)")
self.queryGeo.bindValue(':TIPUSVIA', tipusVia)
self.queryGeo.bindValue(':VARIANTE', variant)
self.queryGeo.bindValue(':CODIGO', codi)
self.queryGeo.bindValue(':NUMINI', numIni)
self.queryGeo.bindValue(':LETRAINI', lletraIni)
self.queryGeo.bindValue(':NUMFIN', numFi)
self.queryGeo.bindValue(':LETRAFIN', lletraFi)
self.queryGeo.bindValue(':X', 0.0, QSql.Out)
self.queryGeo.bindValue(':Y', 0.0, QSql.Out)
ok = self.queryGeo.exec_()
if ok:
x = self.queryGeo.boundValue(':X')
if not isinstance(x, float):
x = None
y = self.queryGeo.boundValue(':Y')
if not isinstance(y, float):
y = None
return x, y
except Exception:
return None, None
def execute_query(self, sql):
query = QSqlQuery(self.db)
res = query.exec_(sql)
if res is False:
QApplication.restoreOverrideCursor()
raise VerisoErrorWithBar(self.iface.messageBar(),
"Error " + (query.lastError().text()))
def __init__(self, family='QVISTA', logname='DESKTOP', dbLog=_DB_QVISTA):
if hasattr(self, 'db'): # Solo se inicializa una vez
return
self.family = family.upper()
self.logname = logname.upper()
self.usuari = getpass.getuser().upper()
self.db = self.connexio(dbLog)
self.sessio = str(uuid.uuid1())
self.query = QSqlQuery()
def checkAccessTableHasAdmin(self):
sql = "SELECT lower(username) FROM postnas_search_access_control WHERE access = 0"
self.__openDB()
query = QSqlQuery(self.db)
query.exec_(sql)
if (query.size() > 0):
return True
else:
return False
def _query(self, querystring, **mappings):
querystring = querystring.replace(r"\r\n", " ")
query = QSqlQuery(self.db)
query.prepare(querystring)
for key, value in mappings.items():
bindvalue = ":{}".format(key)
if re.search(r"{}\b".format(bindvalue), querystring):
query.bindValue(bindvalue, value)
return query
def getAllEdgvDomainsFromTableName(self, schema, table):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
It checks for all attributes found.
:param table: (str) layer to be checked for its EDGV mapping.
:return: (dict) value map for all attributes that have one.
"""
self.abstractDb.checkAndOpenDb()
ret = defaultdict(dict)
db = self.abstractDb.db
edgv = self.abstractDb.getDatabaseVersion()
domainMap = self.domainMapping(edgv)
fullTablaName = schema + "_" + table
sql = 'select code, code_name from dominios_{field} order by code'
for fieldName in self.tableFields(fullTablaName):
if fullTablaName in domainMap:
domains = domainMap[fullTablaName]
# if domain mapping is not yet available for current version
if fieldName in domains:
# replace this method over querying db for the table...
domainTable = domains[fieldName][0]
else:
# non-mapped attribute
continue
query = QSqlQuery(sql.format(field=domainTable), db)
elif fieldName in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
if edgv in ("2.1.3 Pro", "3.0 Pro"):
# Pro versions now follow the logic "{attribute}_{CLASS_NAME}"
cat = table.rsplit("_", 1)[0].split("_", 1)[-1]
else:
cat = table.split("_")[0]
attrTable = "{attribute}_{cat}".format(attribute=fieldName, cat=cat)
query = QSqlQuery(sql.format(field=attrTable), db)
else:
query = QSqlQuery(sql.format(field=fieldName), db)
if not query.isActive():
continue
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[fieldName][code_name] = code
return ret
def __checkLoggingTableExists(self):
sql = "SELECT table_name FROM information_schema.tables WHERE table_name = 'postnas_search_logging'";
self.__openDB()
query = QSqlQuery(self.db)
query.exec_(sql)
if(query.size() > 0):
return True
else:
return False
