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