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 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 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 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 getTableSchemaFromDb(self, table):
self.checkAndOpenDb()
sql = self.gen.getFullTablesName(table)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting full table name: ") +
query.lastError().text())
while query.next():
return query.value(0).split('_')[0]
def getEdgvDomainsFromTableName(self, table, field=None):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
: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.
"""
ret = dict()
currentLayer = table if isinstance(
table, QgsVectorLayer) else self.layerByName(table)
if currentLayer.isValid():
try:
uri = currentLayer.dataProvider().uri()
field = field or self.currentField()
if field in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
category = self.currentLayerName().split("_")[0]
field = "{attribute}_{cat}".format(attribute=field,
cat=category)
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'.format(
field=field)
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'.format(
field=field)
if not db.open():
db.close()
return ret
query = QSqlQuery(sql, db)
if not query.isActive():
return ret
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
db.close()
except:
pass
return ret
def implementationVersion(self):
"""
Database models may be updated. DSGTools has a couple of EDGV different
implementations. This method returns current implementation version and,
if not available, it will return an empty string.
:return: (str) database's implementation version (e.g. '5.2').
"""
self.checkAndOpenDb()
query = QSqlQuery(self.gen.implementationVersion(), self.db)
if not query.isActive():
return ''
while query.next():
version = query.value(0)
break
return version if version is not None else -1
def findEPSG(self, parameters=dict()):
"""
Finds the database EPSG
"""
self.checkAndOpenDb()
sql = self.gen.getSrid(parameters=parameters)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem finding EPSG: ") + query.lastError().text())
srid = -1
while query.next():
srid = query.value(0)
break
return srid
def getAggregationAttributes(self):
"""
Gets complex link columns
"""
self.checkAndOpenDb()
columns = []
sql = self.gen.getAggregationColumn()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting aggregation attributes: ") +
query.lastError().text())
while query.next():
value = query.value(0)
columns.append(value)
return columns
def listClassesFromDatabase(self):
"""
Gets a list with all classes from database.
:return: (str) list of all classes in the database.
"""
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
classList.append(str(query.value(0)))
return classList
def isComplexClass(self, className):
'''
Checks if a class is a complex class
className: class name to be checked
'''
self.checkAndOpenDb()
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
query.lastError().text())
while query.next():
if query.value(0) == 'complexos_' + className:
return True
return False
def getQmlRecordDict(self, inputLayer):
self.checkAndOpenDb()
if isinstance(inputLayer, list):
sql = self.gen.getQmlRecords(inputLayer)
else:
sql = self.gen.getQmlRecords([inputLayer])
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting qmlRecordDict: ") +
query.lastError().text())
qmlDict = dict()
while query.next():
if isinstance(inputLayer, list):
qmlDict[query.value(0)] = query.value(1)
else:
return query.value(1)
return qmlDict
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 listGeomClassesFromDatabase(self, primitiveFilter=[]):
'''
Gets a list with geometry classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName[-2:].lower() in ["_p", "_l", "_a"]:
classList.append(layerName)
return classList
def getTablesFromDatabase(self):
'''
Gets all tables from database
'''
self.checkAndOpenDb()
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting tables from database: ") +
query.lastError().text())
while query.next():
#table name
ret.append(query.value(0))
return ret
def listComplexClassesFromDatabase(self):
'''
Gets a list with complex classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing complex classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def obtainLinkColumn(self, complexClass, aggregatedClass):
'''
Obtains the link column between complex and aggregated class
complexClass: complex class name
aggregatedClass: aggregated class name
'''
self.checkAndOpenDb()
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass.replace('complexos_', ''),
aggregatedClass)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem obtaining link column: ") +
query.lastError().text())
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def getGeomColumnDict(self):
'''
Dict in the form 'geomName':[-list of table names-]
'''
self.checkAndOpenDb()
sql = self.gen.getGeomColumnDict()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom column dict: ") +
query.lastError().text())
geomDict = dict()
while query.next():
geomColumn = query.value(0)
tableName = query.value(1)
lyrName = '_'.join(tableName.split('_')[1::])
if geomColumn not in list(geomDict.keys()):
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def getGeomDict(self, getCentroids=False):
'''
returns a dict like this:
{'tablePerspective' : {
'layerName' :
'''
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomTablesFromGeometryColumns(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tables from db: ") +
query.lastError().text())
geomDict = dict()
geomDict['primitivePerspective'] = self.getGeomTypeDict()
geomDict['tablePerspective'] = dict()
while query.next():
isCentroid = False
srid = query.value(0)
if edgvVersion in ('2.1.3', 'FTer_2a_Ed'):
geometryType = query.value(2)
else:
geometryType = self.getResolvedGeomType(query.value(2))
tableName = query.value(3)
tableSchema = tableName.split('_')[0]
geometryColumn = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if layerName not in list(geomDict['tablePerspective'].keys()):
geomDict['tablePerspective'][layerName] = dict()
geomDict['tablePerspective'][layerName]['schema'] = tableSchema
geomDict['tablePerspective'][layerName]['srid'] = str(srid)
geomDict['tablePerspective'][layerName][
'geometryColumn'] = geometryColumn
geomDict['tablePerspective'][layerName][
'geometryType'] = geometryType
geomDict['tablePerspective'][layerName][
'tableName'] = tableName
return geomDict
def getGeomTypeDict(self, loadCentroids=False):
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomByPrimitive(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom types from db: ") +
query.lastError().text())
geomDict = dict()
while query.next():
if edgvVersion in ('2.1.3', 'FTer_2a_Ed'):
type = query.value(0)
else:
type = self.getResolvedGeomType(query.value(0))
tableName = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if type not in list(geomDict.keys()):
geomDict[type] = []
if layerName not in geomDict[type]:
geomDict[type].append(layerName)
return geomDict
def getTablesFromDatabase(self):
"""
Gets all tables from database except for configuration tables.
"""
# reimplementation
self.checkAndOpenDb()
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting tables from database: ") +
query.lastError().text())
while query.next():
#table name
table = query.value(0)
if 'gpkg_' not in table.lower() and 'rtree_' not in table.lower(
) and table.lower() != 'sqlite_sequence':
ret.append(table)
return ret
def databaseInfo(self):
"""
Gives information about all tables present in the database. Output is composed by
schema, layer, geometry column, geometry type and srid, in that order.
:return: (list-of-dict) database information.
"""
self.checkAndOpenDb()
sql = self.gen.databaseInfo()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom schemas from db: ") +
query.lastError().text())
out = []
while query.next():
rowDict = dict()
rowDict['schema'] = query.value(0).split('_')[0]
rowDict['layer'] = query.value(0)[len(rowDict['schema']) + 1:]
rowDict['geomCol'] = query.value(1)
rowDict['geomType'] = query.value(2)
rowDict['srid'] = str(query.value(3))
out.append(rowDict)
return out
def validateWithOutputDatabaseSchema(self, outputAbstractDb):
'''
Validates the conversion with the output database.
It generates a dictionary (invalidated) that stores conversion problems
'''
self.checkAndOpenDb()
invalidated = self.buildInvalidatedDict()
inputdbStructure = self.getStructureDict()
outputdbStructure = outputAbstractDb.getStructureDict()
domainDict = outputAbstractDb.getDomainDict()
classes = self.listClassesWithElementsFromDatabase()
notNullDict = outputAbstractDb.getNotNullDict()
for inputClass in list(classes.keys()):
outputClass = self.translateAbstractDbLayerNameToOutputFormat(
inputClass, outputAbstractDb)
(schema, className) = self.getTableSchema(inputClass)
if outputClass in list(outputdbStructure.keys()):
outputAttrList = self.reorderTupleList(
list(outputdbStructure[outputClass].keys()))
inputAttrList = self.reorderTupleList(
list(inputdbStructure[inputClass].keys()))
sql = self.gen.getFeaturesWithSQL(inputClass, inputAttrList)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
query.lastError().text())
while query.next():
id = query.value(0)
#detects null lines
for i in range(len(inputAttrList)):
nullLine = True
value = query.value(i)
if value != None:
nullLine = False
break
if nullLine:
if cl not in list(invalidated['nullLine'].keys()):
invalidated['nullLine'][inputClass] = 0
invalidated['nullLine'][inputClass] += 1
#validates pks
if id == None and (not nullLine):
if cl not in list(invalidated['nullPk'].keys()):
invalidated['nullPk'][inputClass] = 0
invalidated['nullPk'][inputClass] += 1
for i in range(len(inputAttrList)):
value = query.value(i)
#validates domain
if outputClass in list(domainDict.keys()):
if inputAttrList[i] in list(
domainDict[outputClass].keys()):
if value not in domainDict[outputClass][
inputAttrList[i]] and (not nullLine):
invalidated = self.utils.buildNestedDict(
invalidated, [
'notInDomain', inputClass, id,
inputAttrList[i]
], value)
#validates not nulls
if outputClass in list(notNullDict.keys()):
if outputClass in list(domainDict.keys()):
if inputAttrList[i] in notNullDict[
outputClass] and inputAttrList[
i] not in list(
domainDict[outputClass].keys(
)):
if (value == None) and (not nullLine) and (
inputAttrList[i] not in list(
domainDict[outputClass].keys())
):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute', inputClass,
id, inputAttrList[i]
], value)
else:
if inputAttrList[i] in notNullDict[
outputClass]:
try:
if value.isNull():
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute',
inputClass, id,
inputAttrList[i]
], value)
except:
if (value == None) and (
not nullLine
) and (inputAttrList[i] not in list(
domainDict[outputClass].keys())
):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute',
inputClass, id,
inputAttrList[i]
], value)
if outputClass in list(domainDict.keys()):
if (inputAttrList[i] not in [
'geom', 'GEOMETRY', 'geometry', 'id',
'OGC_FID'
] and schema != 'complexos') or (
schema == 'complexos'
and inputAttrList[i] != 'id'):
if inputAttrList[i] not in list(
outputdbStructure[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'attributeNotFoundInOutput',
inputClass
], [inputAttrList[i]])
#validates fk field
if 'id_' == inputAttrList[0:3]:
if not self.validateUUID(value):
if inputAttrList[i] not in list(
outputdbStructure[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated,
['nullComplexFk', inputClass],
[inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def loadAssociatedFeatures(self, complex):
'''
Loads all the features associated to the complex
complex: complex class name
'''
self.checkAndOpenDb()
associatedDict = dict()
#query to get the possible links to the selected complex in the combobox
complexName = complex.replace('complexos_', '')
sql = self.gen.getComplexLinks(complexName)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem loading associated features: ") +
query.lastError().text())
while query.next():
#setting the variables
complex_schema = query.value(0)
complex = query.value(1)
aggregated_schema = query.value(2)
aggregated_class = query.value(3)
column_name = query.value(4)
if aggregated_class.split('_')[-1] not in ['p', 'l', 'a']:
continue
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
if not complexQuery.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
complexQuery.lastError().text())
while next(complexQuery):
complex_uuid = complexQuery.value(0)
name = complexQuery.value(1)
if not (complex_uuid and name):
continue
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class], [])
#query to obtain the id of the associated feature
sql = self.gen.getAssociatedFeaturesData(
aggregated_schema, aggregated_class, column_name,
complex_uuid)
associatedQuery = QSqlQuery(sql, self.db)
if not associatedQuery.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
associatedQuery.lastError().text())
while next(associatedQuery):
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class],
[ogc_fid])
return associatedDict
