def __init__(self):
'''
Constructor
'''
super(SpatialiteDb, self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def __init__(self):
"""Constructor.
"""
super(PostgisDbThread, self).__init__()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.messenger = PostgisDbMessages(self)
def __init__(self, parent = None):
"""Constructor."""
super(self.__class__, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.utils = Utils()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.serverWidget.populateServersCombo()
self.serverWidget.abstractDbLoaded.connect(self.populateListWithDatabasesFromServer)
def __init__(self, parent=None):
"""Constructor."""
super(self.__class__, self).__init__(parent)
self.setupUi(self)
self.utils = Utils()
self.dbFactory = DbFactory()
self.factory = SqlGeneratorFactory()
self.showTabs(show=False)
#setting the sql generator
self.serverWidget.populateServersCombo()
self.serverWidget.abstractDbLoaded.connect(self.checkSuperUser)
self.serverWidget.abstractDbLoaded.connect(
self.populateOtherInterfaces)
self.dbsCustomSelector.setTitle(self.tr('Server Databases'))
self.dbsCustomSelector.selectionChanged.connect(self.showTabs)
self.dbsCustomSelector.selectionChanged.connect(
self.populateStylesInterface)
self.dbsCustomSelector.selectionChanged.connect(
self.populateOtherInterfaces)
self.previousTab = 0
self.dbDict = {'2.1.3': [], 'FTer_2a_Ed': [], 'Non_EDGV': []}
self.correspondenceDict = {
self.tr('Load Database Model EDGV Version 2.1.3'): '2.1.3',
self.tr('Load Database Model EDGV Version FTer_2a_Ed'):
'FTer_2a_Ed',
self.tr('Load Other Database Models'): 'Non_EDGV'
}
def __init__(self):
'''
Constructor
'''
super(SpatialiteDb,self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def __init__(self, iface, parent=None):
"""Constructor."""
super(CreateInomDialog, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
#Sql factory generator
self.isSpatialite = True
self.tabWidget.setCurrentIndex(0)
self.factory = SqlGeneratorFactory()
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
QObject.connect(self.tabWidget, SIGNAL(("currentChanged(int)")), self.restoreInitialState)
QObject.connect(self.pushButtonOpenFile, SIGNAL(("clicked()")), self.loadDatabase)
self.restoreInitialState()
self.db = None
#populating the postgis combobox
self.populatePostGISConnectionsCombo()
self.map_index = UtmGrid()
self.disableAll()
self.setValidCharacters()
self.setMask()
def __init__(self, iface):
"""Constructor."""
super(PostgisDBTool, self).__init__()
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
self.populateServersCombo()
self.srs = None
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.epsg = 4326
def __init__(self, parent=None):
"""Constructor."""
super(LoadByClass, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.selectedClasses = []
#Sql factory generator
self.isSpatialite = True
self.setupUi(self)
self.tabWidget.setCurrentIndex(0)
self.factory = SqlGeneratorFactory()
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.utils = Utils()
self.bar = QgsMessageBar()
self.setLayout(QtGui.QGridLayout(self))
self.layout().setContentsMargins(0,0,0,0)
self.layout().setAlignment(QtCore.Qt.AlignTop)
sizePolicy = QtGui.QSizePolicy(QtGui.QSizePolicy.Minimum, QtGui.QSizePolicy.Fixed)
self.bar.setSizePolicy(sizePolicy)
self.layout().addWidget(self.bar, 0,0,1,1)
#Objects Connections
QtCore.QObject.connect(self.pushButtonOpenFile, QtCore.SIGNAL(("clicked()")), self.loadDatabase)
QtCore.QObject.connect(self.pushButtonCancel, QtCore.SIGNAL(("clicked()")), self.cancel)
QtCore.QObject.connect(self.selectAllCheck, QtCore.SIGNAL(("stateChanged(int)")), self.selectAll)
QtCore.QObject.connect(self.pushButtonOk, QtCore.SIGNAL(("clicked()")), self.okSelected)
QtCore.QObject.connect(self.tabWidget,QtCore.SIGNAL(("currentChanged(int)")), self.restoreInitialState)
self.db = None
#populating the postgis combobox
self.populatePostGISConnectionsCombo()
def __init__(self, parent = None):
"""Constructor."""
super(self.__class__, self).__init__(parent)
self.setupUi(self)
self.utils = Utils()
self.dbFactory = DbFactory()
self.factory = SqlGeneratorFactory()
self.serverWidget.populateServersCombo()
self.serverWidget.abstractDbLoaded.connect(self.populatePostgisSelector)
self.customFileSelector.filesSelected.connect(self.populateSpatialiteSelector)
self.comboDict = {self.tr('Load Database Model EDGV Version 2.1.3'):'2.1.3', self.tr('Load Database Model EDGV Version FTer_2a_Ed'):'FTer_2a_Ed',self.tr('Load Other Database Models'):'Non_EDGV'}
self.dbDict = {'2.1.3':[], 'FTer_2a_Ed':[],'Non_EDGV':[]}
self.selectedDbsDict = dict()
self.stylesDict = dict()
self.postgisCustomSelector.selectionChanged.connect(self.selectedDatabases)
self.spatialiteCustomSelector.selectionChanged.connect(self.selectedFiles)
self.path = None
self.customFileSelector.setCaption(self.tr('Select a DSGTools Spatialite file'))
self.customFileSelector.setFilter(self.tr('Spatialite file databases (*.sqlite)'))
self.customFileSelector.setType('multi')
self.edgvType = None
def __init__(self, iface, parent=None):
"""Constructor."""
super(ComplexWindow, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
#self.enderecoLine.setText('186.228.51.52')
#self.portaLine.setText('2101'
self.iface = iface
QObject.connect(self.dbButton, SIGNAL(("clicked()")), self.getDataSources)
QObject.connect(self.dbCombo, SIGNAL("activated(int)"), self.updateComplexClass)
QObject.connect(self.complexCombo, SIGNAL("activated(int)"), self.loadAssociatedFeatures)
QObject.connect(self.iface, SIGNAL("newProjectCreated()"), self.clearDock)
self.db = None
self.databases = None
self.factory = SqlGeneratorFactory()
self.gen = None
class SpatialiteDb(AbstractDb):
def __init__(self):
'''
Constructor
'''
super(SpatialiteDb,self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def getDatabaseName(self):
'''
Gets the database name
'''
return self.db.databaseName().split('.sqlite')[0].split('/')[-1]
def connectDatabase(self, conn = None):
'''
Connects to database
conn: Database name
'''
if conn is None:
self.connectDatabaseWithGui()
else:
self.db.setDatabaseName(conn)
def connectDatabaseWithGui(self):
'''
Connects to database using user interface dialog
'''
fd = QFileDialog()
filename = fd.getOpenFileName(caption=self.tr('Select a DSGTools Spatialite file'),filter=self.tr('Spatialite file databases (*.sqlite)'))
self.db.setDatabaseName(filename)
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.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
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 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 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 classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in classDict[className].keys():
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def makeOgrConn(self):
'''
Makes a connection string for spatialite databases (e.g just the name)
'''
constring = self.db.databaseName()
return constring
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 classes.keys():
outputClass = self.translateAbstractDbLayerNameToOutputFormat(inputClass,outputAbstractDb)
(schema,className) = self.getTableSchema(inputClass)
if outputClass in outputdbStructure.keys():
outputAttrList = self.reorderTupleList(outputdbStructure[outputClass].keys())
inputAttrList = self.reorderTupleList(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 invalidated['nullLine'].keys():
invalidated['nullLine'][inputClass]=0
invalidated['nullLine'][inputClass]+=1
#validates pks
if id == None and (not nullLine):
if cl not in 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 domainDict.keys():
if inputAttrList[i] in 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 notNullDict.keys():
if outputClass in domainDict.keys():
if inputAttrList[i] in notNullDict[outputClass] and inputAttrList[i] not in domainDict[outputClass].keys():
if (value == None) and (not nullLine) and (inputAttrList[i] not in 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 domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(invalidated, ['nullAttribute',inputClass,id,inputAttrList[i]], value)
if outputClass in 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 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 outputdbStructure[outputClass].keys():
invalidated = self.utils.buildNestedDict(invalidated, ['nullComplexFk',inputClass], [inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def translateAbstractDbLayerNameToOutputFormat(self, lyr, outputAbstractDb):
'''
Translates abstractdb layer name to output format
lyr: layer name that will be translated
outputAbstractDb: output database
'''
if outputAbstractDb.db.driverName() == 'QSQLITE':
return lyr
if outputAbstractDb.db.driverName() == 'QPSQL':
return str(lyr.split('_')[0]+'.'+'_'.join(lyr.split('_')[1::]))
def translateOGRLayerNameToOutputFormat(self, lyr, ogrOutput):
'''
Translates ogr layer name to output format
lyr: layer name that will be translated
ogrOutput: ogr output
'''
if ogrOutput.GetDriver().name == 'SQLite':
return lyr
if ogrOutput.GetDriver().name == 'PostgreSQL':
return str(lyr.split('_')[0]+'.'+'_'.join(lyr.split('_')[1::]))
def getTableSchema(self,lyr):
'''
Gets the table schema
lyr: layer name
'''
schema = lyr.split('_')[0]
className = '_'.join(lyr.split('_')[1::])
return (schema, className)
def convertToPostgis(self, outputAbstractDb, type=None):
'''
Converts this to a postgis database
outputAbstractDb: postgis output
type: conversion type
'''
(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict) = self.prepareForConversion(outputAbstractDb)
invalidated = self.validateWithOutputDatabaseSchema(outputAbstractDb)
hasErrors = self.makeValidationSummary(invalidated)
if type == 'untouchedData':
if hasErrors:
self.signals.updateLog.emit('\n\n\n'+self.tr('Conversion not perfomed due to validation errors! Check log above for more information.'))
return False
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict)
return status
if type == 'fixData':
if hasErrors:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict, invalidated)
return status
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict)
return status
return False
def getDatabaseVersion(self):
'''
Gets the database version
'''
self.checkAndOpenDb()
version = '2.1.3'
sql = self.gen.getEDGVVersion()
query = QSqlQuery(sql, self.db)
# if not query.isActive():
# raise Exception(self.tr("Problem getting database version: ")+query.lastError().text())
while query.next():
version = query.value(0)
return version
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 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 complexQuery.next():
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 associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(associatedDict, [name, complex_uuid, aggregated_class], [ogc_fid])
return associatedDict
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 disassociateComplexFromComplex(self, aggregated_class, link_column, id):
'''
Disassociates a complex from another complex
aggregated_class: aggregated class that will be disassociated
link_column: link column between complex and its aggregated class
id: complex id (uid) to be disassociated
'''
sql = self.gen.disassociateComplexFromComplex('complexos_'+aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
self.db.close()
raise Exception(self.tr('Problem disassociating complex from complex: ') + '\n' + query.lastError().text())
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 getFrameLayerName(self):
'''
Gets the frame layer name
'''
return 'public_aux_moldura_a'
def getOrphanGeomTablesWithElements(self, loading = False):
return []
def getOrphanGeomTables(self):
return []
def checkAndCreateStyleTable(self):
return None
def getStylesFromDb(self,dbVersion):
return None
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 geomDict.keys():
geomDict[type] = []
if layerName not in geomDict[type]:
geomDict[type].append(layerName)
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 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 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 geomDict.keys():
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def createFrame(self, type, scale, param, paramDict = dict()):
mi, inom, frame = self.prepareCreateFrame(type, scale, param)
self.insertFrame(scale, mi, inom, frame.asWkb())
return frame
def insertFrame(self, scale, mi, inom, frame):
self.checkAndOpenDb()
srid = self.findEPSG()
geoSrid = QgsCoordinateReferenceSystem(int(srid)).geographicCRSAuthId().split(':')[-1]
ogr.UseExceptions()
outputDS = self.buildOgrDatabase()
outputLayer=outputDS.GetLayerByName('public_aux_moldura_a')
newFeat=ogr.Feature(outputLayer.GetLayerDefn())
auxGeom = ogr.CreateGeometryFromWkb(frame)
#set geographic srid from frame
geoSrs = ogr.osr.SpatialReference()
geoSrs.ImportFromEPSG(int(geoSrid))
auxGeom.AssignSpatialReference(geoSrs)
#reproject geom
outSpatialRef = outputLayer.GetSpatialRef()
coordTrans = osr.CoordinateTransformation(geoSrs, outSpatialRef)
auxGeom.Transform(coordTrans)
newFeat.SetGeometry(auxGeom)
newFeat.SetField('mi', mi)
newFeat.SetField('inom', inom)
newFeat.SetField('escala', str(scale))
out=outputLayer.CreateFeature(newFeat)
outputDS.Destroy()
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 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 getResolvedGeomType(self, geometryType):
geomDict = {0:'GEOMETRY',
1:'POINT',
2:'LINESTRING',
3:'POLYGON',
4:'MULTIPOINT',
5:'MULTILINESTRING',
6:'MULTIPOLYGON',
7:'GEOMETRYCOLLECTION',
8:'CIRCULARSTRING',
9:'COMPOUNDCURVE',
10:'CURVEPOLYGON',
11:'MULTICURVE',
12:'MULTISURFACE',
13:'CURVE',
14:'SURFACE'}
return geomDict[geometryType]
class LoadByCategory(QtGui.QDialog, load_by_category_dialog.Ui_LoadByCategory):
def __init__(self, parent=None):
"""Constructor."""
super(LoadByCategory, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.categories = []
self.selectedClasses = []
self.point = []
self.line = []
self.polygon = []
self.pointWithElement = []
self.lineWithElement = []
self.polygonWithElement = []
#Sql factory generator
self.isSpatialite = True
self.setupUi(self)
self.tabWidget.setCurrentIndex(0)
self.factory = SqlGeneratorFactory()
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.utils = Utils()
self.parentTreeNode = None
self.comboBoxPostgis.setCurrentIndex(0)
self.checkBoxPoint.setCheckState(0)
self.checkBoxLine.setCheckState(0)
self.checkBoxPolygon.setCheckState(0)
self.checkBoxAll.setCheckState(0)
self.bar = QgsMessageBar()
self.setLayout(QtGui.QGridLayout(self))
self.layout().setContentsMargins(0,0,0,0)
self.layout().setAlignment(QtCore.Qt.AlignTop)
sizePolicy = QtGui.QSizePolicy(QtGui.QSizePolicy.Minimum, QtGui.QSizePolicy.Fixed)
self.bar.setSizePolicy(sizePolicy)
self.layout().addWidget(self.bar, 0,0,1,1)
#Objects Connections
QtCore.QObject.connect(self.pushButtonOpenFile, QtCore.SIGNAL(("clicked()")), self.loadDatabase)
QtCore.QObject.connect(self.pushButtonCancel, QtCore.SIGNAL(("clicked()")), self.cancel)
QtCore.QObject.connect(self.pushButtonOk, QtCore.SIGNAL(("clicked()")), self.okSelected)
QtCore.QObject.connect(self.tabWidget,QtCore.SIGNAL(("currentChanged(int)")), self.restoreInitialState)
QtCore.QObject.connect(self.pushButtonSelectAll, QtCore.SIGNAL(("clicked()")), self.selectAll)
QtCore.QObject.connect(self.pushButtonDeselectAll, QtCore.SIGNAL(("clicked()")), self.deselectAll)
QtCore.QObject.connect(self.pushButtonSelectOne, QtCore.SIGNAL(("clicked()")), self.selectOne)
QtCore.QObject.connect(self.pushButtonDeselectOne, QtCore.SIGNAL(("clicked()")), self.deselectOne)
QtCore.QObject.connect(self.checkBoxAll, QtCore.SIGNAL(("stateChanged(int)")), self.setAllGroup)
self.db = None
#populating the postgis combobox
self.populatePostGISConnectionsCombo()
def __del__(self):
self.closeDatabase()
def closeDatabase(self):
if self.db:
self.db.close()
self.db = None
def restoreInitialState(self):
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.categories = []
self.selectedClasses = []
self.spatialiteFileEdit.setText(self.filename)
self.postGISCrsEdit.setText('')
self.postGISCrsEdit.setReadOnly(True)
self.spatialiteCrsEdit.setText('')
self.spatialiteCrsEdit.setReadOnly(True)
self.listWidgetCategoryFrom.clear()
self.listWidgetCategoryTo.clear()
self.point = []
self.line = []
self.polygon = []
self.pointWithElement = []
self.lineWithElement = []
self.polygonWithElement = []
self.parentTreeNode = None
#Setting the database type
if self.tabWidget.currentIndex() == 0:
self.isSpatialite = True
else:
self.isSpatialite = False
#getting the sql generator according to the database type
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.comboBoxPostgis.setCurrentIndex(0)
self.checkBoxPoint.setCheckState(0)
self.checkBoxLine.setCheckState(0)
self.checkBoxPolygon.setCheckState(0)
self.checkBoxAll.setCheckState(0)
def updateBDField(self):
if self.dbLoaded == True:
self.spatialiteFileEdit.setText(self.filename)
else:
self.filename = ""
self.spatialiteFileEdit.setText(self.filename)
def getDatabaseVersion(self):
self.dbVersion = self.utils.getDatabaseVersion(self.db)
self.qmlPath = self.utils.getQmlDir(self.db)
def listCategoriesFromDatabase(self):
self.listWidgetCategoryFrom.clear()
self.listWidgetCategoryTo.clear()
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
self.getDatabaseVersion()
while query.next():
if self.isSpatialite:
tableName = query.value(0)
layerName = tableName
split = tableName.split('_')
if len(split) < 2:
continue
if self.dbVersion == '3.0' or self.dbVersion == '2.1.3':
schema = split[0]
category = split[1]
categoryName = schema+'.'+category
else:
categoryName = split[0] #done this way to have back compatibility with spatialites already in production
else:
tableSchema = query.value(0)
tableName = query.value(1)
split = tableName.split('_')
category = split[0]
categoryName = tableSchema+'.'+category
layerName = tableSchema+'.'+tableName
if layerName.split("_")[-1] == "p":
self.point.append(layerName)
if layerName.split("_")[-1] == "l":
self.line.append(layerName)
if layerName.split("_")[-1] == "a":
self.polygon.append(layerName)
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
self.insertIntoListView(categoryName)
self.listWidgetCategoryFrom.sortItems()
self.setCRS()
def insertIntoListView(self, item_name):
found = self.listWidgetCategoryFrom.findItems(item_name, Qt.MatchExactly)
if len(found) == 0:
item = QtGui.QListWidgetItem(item_name)
self.listWidgetCategoryFrom.addItem(item)
def selectAll(self):
tam = self.listWidgetCategoryFrom.__len__()
for i in range(tam+1,1,-1):
item = self.listWidgetCategoryFrom.takeItem(i-2)
self.listWidgetCategoryTo.addItem(item)
self.listWidgetCategoryTo.sortItems()
def deselectAll(self):
tam = self.listWidgetCategoryTo.__len__()
for i in range(tam+1,1,-1):
item = self.listWidgetCategoryTo.takeItem(i-2)
self.listWidgetCategoryFrom.addItem(item)
self.listWidgetCategoryFrom.sortItems()
def selectOne(self):
listedItems = self.listWidgetCategoryFrom.selectedItems()
for i in listedItems:
item = self.listWidgetCategoryFrom.takeItem(self.listWidgetCategoryFrom.row(i))
self.listWidgetCategoryTo.addItem(item)
self.listWidgetCategoryTo.sortItems()
def deselectOne(self):
listedItems = self.listWidgetCategoryTo.selectedItems()
for i in listedItems:
item = self.listWidgetCategoryTo.takeItem(self.listWidgetCategoryTo.row(i))
self.listWidgetCategoryFrom.addItem(item)
self.listWidgetCategoryFrom.sortItems()
def setAllGroup(self):
if self.checkBoxAll.isChecked():
self.checkBoxPoint.setCheckState(2)
self.checkBoxLine.setCheckState(2)
self.checkBoxPolygon.setCheckState(2)
else:
self.checkBoxPoint.setCheckState(0)
self.checkBoxLine.setCheckState(0)
self.checkBoxPolygon.setCheckState(0)
def setCRS(self):
try:
self.epsg = self.utils.findEPSG(self.db)
if self.epsg == -1:
self.bar.pushMessage("", self.tr("Coordinate Reference System not set or invalid!"), level=QgsMessageBar.WARNING)
else:
self.crs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if self.isSpatialite:
self.spatialiteCrsEdit.setText(self.crs.description())
self.spatialiteCrsEdit.setReadOnly(True)
else:
self.postGISCrsEdit.setText(self.crs.description())
self.postGISCrsEdit.setReadOnly(True)
except:
pass
@pyqtSlot(int)
def on_comboBoxPostgis_currentIndexChanged(self):
if self.comboBoxPostgis.currentIndex() > 0:
self.loadDatabase()
def loadDatabase(self):
self.closeDatabase()
if self.isSpatialite:
(self.filename, self.db) = self.utils.getSpatialiteDatabase()
if self.filename:
self.spatialiteFileEdit.setText(self.filename)
else:
self.db = self.utils.getPostGISDatabase(self.comboBoxPostgis.currentText())
try:
if not self.db.open():
QgsMessageLog.logMessage(self.db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
else:
self.dbLoaded = True
self.listCategoriesFromDatabase()
except:
pass
def populatePostGISConnectionsCombo(self):
self.comboBoxPostgis.clear()
self.comboBoxPostgis.addItem("Select Database")
self.comboBoxPostgis.addItems(self.utils.getPostGISConnections())
def cancel(self):
self.restoreInitialState()
self.close()
def getSelectedItems(self):
lista = self.classesListWidget.selectedItems()
self.selectedClasses = []
tam = len(lista)
for i in range(tam):
self.selectedClasses.append(lista[i].text())
self.selectedClasses.sort()
def okSelected(self):
try:
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
if self.checkBoxOnlyWithElements.isChecked():
self.setLayersWithElements()
ponto = self.pointWithElement
linha = self.lineWithElement
area = self.polygonWithElement
else:
ponto = self.point
linha = self.line
area = self.polygon
if self.db and self.crs and len(self.listWidgetCategoryTo)>0:
categoriasSelecionadas = []
for i in range(self.listWidgetCategoryTo.__len__()):
categoriasSelecionadas.append(self.listWidgetCategoryTo.item(i).text())
try:
if self.checkBoxPoint.isChecked():
self.loadLayers('p',categoriasSelecionadas,ponto)
if self.checkBoxLine.isChecked():
self.loadLayers('l',categoriasSelecionadas,linha)
if self.checkBoxPolygon.isChecked():
self.loadLayers('a',categoriasSelecionadas,area)
if self.checkBoxPoint.isChecked()== False and self.checkBoxLine.isChecked() == False and self.checkBoxPolygon.isChecked() == False:
self.bar.pushMessage(self.tr("WARNING!"), self.tr("Please, select at least one type of layer!"), level=QgsMessageBar.WARNING)
else:
self.restoreInitialState()
self.close()
except:
qgis.utils.iface.messageBar().pushMessage(self.tr("CRITICAL!"), self.tr("Problem loading the categories!"), level=QgsMessageBar.CRITICAL)
pass
else:
if self.db and not self.crs:
self.bar.pushMessage(self.tr("CRITICAL!"), self.tr("Could not determine the coordinate reference system!"), level=QgsMessageBar.CRITICAL)
if not self.db and not self.crs:
self.bar.pushMessage(self.tr("CRITICAL!"), self.tr("Database not loaded properly!"), level=QgsMessageBar.CRITICAL)
self.bar.pushMessage(self.tr("CRITICAL!"), self.tr("Could not determine the coordinate reference system!"), level=QgsMessageBar.CRITICAL)
if len(self.listWidgetCategoryTo)==0:
self.bar.pushMessage(self.tr("WARNING!"), self.tr("Please, select at least one category!"), level=QgsMessageBar.WARNING)
categoriasSelecionadas = []
self.pointWithElement = []
self.lineWithElement = []
self.polygonWithElement = []
QApplication.restoreOverrideCursor()
except:
QApplication.restoreOverrideCursor()
def loadLayers(self, type, categories, layer_names):
if self.isSpatialite:
self.loadSpatialiteLayers(type, categories, layer_names)
else:
self.loadPostGISLayers(type, categories, layer_names)
def setLayersWithElements(self):
self.pointWithElement = []
self.lineWithElement = []
self.polygonWithElement = []
pontoAux = self.countElements(self.point)
linhaAux = self.countElements(self.line)
areaAux = self.countElements(self.polygon)
for i in pontoAux:
if i[1] > 0:
self.pointWithElement.append(i[0])
for i in linhaAux:
if i[1] > 0:
self.lineWithElement.append(i[0])
for i in areaAux:
if i[1] > 0:
self.polygonWithElement.append(i[0])
def countElements(self, layers):
listaQuantidades = []
for layer in layers:
sql = self.gen.getElementCountFromLayer(layer)
query = QSqlQuery(sql,self.db)
query.next()
number = query.value(0)
if not query.exec_(sql):
QgsMessageLog.logMessage(self.tr("Problem counting elements: ")+query.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
listaQuantidades.append([layer, number])
return listaQuantidades
def loadPostGISLayers(self, type, categories, layer_names):
(database, host, port, user, password) = self.utils.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
uri = QgsDataSourceURI()
uri.setConnection(str(host),str(port), str(database), str(user), str(password))
geom_column = 'geom'
if self.parentTreeNode is None:
self.parentTreeNode = qgis.utils.iface.legendInterface (). addGroup (database, -1)
if type == 'p':
idGrupo = qgis.utils.iface.legendInterface (). addGroup ("Ponto", True,self.parentTreeNode)
for categoria in categories:
self.preparePostGISToLoad(uri, categoria, layer_names, idGrupo, geom_column)
if type == 'l':
idGrupo = qgis.utils.iface.legendInterface (). addGroup ("Linha", True,self.parentTreeNode)
for categoria in categories:
self.preparePostGISToLoad(uri, categoria, layer_names, idGrupo, geom_column)
if type == 'a':
idGrupo = qgis.utils.iface.legendInterface (). addGroup ("Area", True,self.parentTreeNode)
for categoria in categories:
self.preparePostGISToLoad(uri, categoria, layer_names, idGrupo, geom_column)
def preparePostGISToLoad(self, uri, categoria, layer_names, idGrupo, geom_column):
idSubgrupo = qgis.utils.iface.legendInterface().addGroup(categoria, True, idGrupo)
layer_names.sort(reverse=True)
for layer_name in layer_names:
split = layer_name.split('_')
category = split[0]
schema = category.split('.')[0]
name = layer_name.replace(schema+'.', '')
if category == categoria:
sql = self.gen.loadLayerFromDatabase(layer_name)
uri.setDataSource(schema, name, geom_column, sql, 'id')
uri.disableSelectAtId(True)
self.loadEDGVLayer(uri, name, 'postgres', idSubgrupo)
def prepareSpatialiteToLoad(self, uri, categoria, layer_names, idGrupo, geom_column):
idSubgrupo = qgis.utils.iface.legendInterface().addGroup(categoria, True, idGrupo)
layer_names.sort(reverse=True)
for layer_name in layer_names:
split = layer_name.split('_')
if self.dbVersion == '3.0' or self.dbVersion == '2.1.3':
category = split[0]+'.'+split[1]
else:
category = split[0]
if category == categoria:
uri.setDataSource('', layer_name, geom_column)
self.loadEDGVLayer(uri, layer_name, 'spatialite', idSubgrupo)
def loadSpatialiteLayers(self, type, categories, layer_names):
uri = QgsDataSourceURI()
uri.setDatabase(self.filename)
geom_column = 'GEOMETRY'
if self.parentTreeNode is None:
self.parentTreeNode = qgis.utils.iface.legendInterface(). addGroup(self.filename.split('.sqlite')[0].split('/')[-1], -1)
if type == 'p':
idGrupo = qgis.utils.iface.legendInterface(). addGroup("Ponto", True, self.parentTreeNode)
for categoria in categories:
self.prepareSpatialiteToLoad(uri, categoria, layer_names, idGrupo, geom_column)
if type == 'l':
idGrupo = qgis.utils.iface.legendInterface(). addGroup("Linha", True, self.parentTreeNode)
for categoria in categories:
self.prepareSpatialiteToLoad(uri, categoria, layer_names, idGrupo, geom_column)
if type == 'a':
idGrupo = qgis.utils.iface.legendInterface(). addGroup("Area", True, self.parentTreeNode)
for categoria in categories:
self.prepareSpatialiteToLoad(uri, categoria, layer_names, idGrupo, geom_column)
def loadEDGVLayer(self, uri, layer_name, provider, idSubgrupo):
vlayer = QgsVectorLayer(uri.uri(), layer_name, provider)
vlayer.setCrs(self.crs)
QgsMapLayerRegistry.instance().addMapLayer(vlayer) #added due to api changes
if self.isSpatialite and (self.dbVersion == '3.0' or self.dbVersion == '2.1.3'):
lyr = '_'.join(layer_name.replace('\r', '').split('_')[1::])
else:
lyr = layer_name.replace('\r','')
vlayerQml = os.path.join(self.qmlPath, lyr+'.qml')
vlayer.loadNamedStyle(vlayerQml, False)
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
qgis.utils.iface.legendInterface().moveLayer(vlayer, idSubgrupo)
if not vlayer.isValid():
QgsMessageLog.logMessage(vlayer.error().summary(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
class PostgisDBTool(QDialog, FORM_CLASS):
def __init__(self, iface):
"""Constructor."""
super(PostgisDBTool, self).__init__()
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
self.populateServersCombo()
self.srs = None
self.db = None
self.abstractDb = None
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.epsg = 4326
def getParameters(self):
"""
Gets database parameters
"""
return (self.databaseEdit.text(), self.abstractDb, self.versionCombo.currentText(), self.epsg)
@pyqtSlot(bool)
def on_saveButton_clicked(self):
"""
Creates a postgis database
"""
if self.databaseEdit.text() == '':
QgsMessageLog.logMessage('Enter database name!', "DSG Tools Plugin", QgsMessageLog.CRITICAL)
else:
self.db = self.getDatabase()
if self.db:
self.storeConnectionConfiguration(self.serversCombo.currentText(), self.databaseEdit.text())
self.done(1)
@pyqtSlot(bool)
def on_cancelButton_clicked(self):
"""
Cancels everything
"""
self.done(-1)
@pyqtSlot(bool)
def on_configureServerButton_clicked(self):
"""
Opens the ViewServer dialog
"""
dlg = ViewServers(self.iface)
dlg.show()
result = dlg.exec_()
self.populateServersCombo()
@pyqtSlot(bool)
def on_srsButton_clicked(self):
"""
Opens the CRS selector dialog
"""
projSelector = QgsGenericProjectionSelector()
message = 'Select the Spatial Reference System!'
projSelector.setMessage(theMessage=message)
projSelector.exec_()
try:
self.epsg = int(projSelector.selectedAuthId().split(':')[-1])
srs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if srs:
self.srsEdit.setText(srs.description())
else:
self.epsg = 4326
except:
QMessageBox.warning(self, self.tr("Warning!"), message)
def createDatabase(self, name):
"""
Creates the database
"""
sql = self.gen.getCreateDatabase(name)
db = self.getDatabase()
#creating the database
query = QSqlQuery(db)
if not query.exec_(sql):
QMessageBox.warning(self, self.tr("Warning!"), query.lastError().text())
db.close()
return False
db.close()
return True
def getDatabase(self, database = 'postgres'):
"""
Gets a a QSqlDatabase
"""
(host, port, user, password) = self.getServerConfiguration(self.serversCombo.currentText())
self.abstractDb = DbFactory().createDbFactory('QPSQL')
if password == '':
conInfo = 'host='+host+' port='+port+' dbname='+database
self.setCredentials(self.abstractDb.db, conInfo, user)
else:
self.abstractDb.connectDatabaseWithParameters(host, port, database, user, password)
if not self.abstractDb.db.open():
QgsMessageLog.logMessage(self.abstractDb.db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return self.abstractDb
def setCredentials(self, db, conInfo, user):
"""
Sets connection credentials
db: QSqlDatabase used
conInfo: connection information
user: user name
"""
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
else:
db.setPassword(password)
db.setUserName(user)
if not db.open():
self.setCredentials(db, conInfo, user)
else:
QgsCredentials.instance().put(conInfo, user, password)
def updateConnectionName(self):
"""
Updates connection name
"""
server = self.serversCombo.currentText()
database = self.databaseEdit.text()
name = server+'_'+database
self.connectionEdit.setText(name)
def on_serversCombo_currentIndexChanged(self, index):
"""
Slot to update the connection name
"""
self.updateConnectionName()
def on_databaseEdit_textEdited(self, text):
"""
Adjusts the text before updating the connection name
"""
self.updateConnectionName()
def checkFields(self):
"""
Check fields prior the next step
"""
if self.serversCombo.currentText() == '' or self.databaseEdit.text() == '' or self.srsEdit.text() == '':
return False
return True
def getServerConfiguration(self, name):
"""
Gets server configuration from QSettings
name: server name
"""
settings = QSettings()
settings.beginGroup('PostgreSQL/servers/'+name)
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, user, password)
def storeConnectionConfiguration(self, server, database):
"""
Stores the new configuration
server: server name
database: database name
"""
name = self.connectionEdit.text()
(host, port, user, password) = self.getServerConfiguration(server)
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+name)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
def getServers(self):
"""
Gets all servers from QSettings
"""
settings = QSettings()
settings.beginGroup('PostgreSQL/servers')
currentConnections = settings.childGroups()
settings.endGroup()
return currentConnections
def populateServersCombo(self):
"""
Populates the server combo box
"""
self.serversCombo.clear()
currentConnections = self.getServers()
for connection in currentConnections:
self.serversCombo.addItem(connection)
class PostgisDbThread(GenericThread):
def __init__(self, parent = None):
"""
Constructor.
"""
super(PostgisDbThread, self).__init__()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.messenger = PostgisDbMessages(self)
self.dbFactory = DbFactory()
self.parent = parent
def setParameters(self, abstractDb, dbName, version, epsg, stopped):
"""
Sets thread parameters
"""
self.abstractDb = abstractDb #database = postgis
self.dbName = dbName
self.db = None
self.version = version
self.epsg = epsg
self.stopped = stopped
def run(self):
"""
Runs the process
"""
# Processing ending
(ret, msg) = self.createDatabaseStructure()
self.signals.processingFinished.emit(ret, msg, self.getId())
def connectToTemplate(self, setInnerDb = True):
"""
Connects to the template database to speed up database creation
:return:
"""
database = self.abstractDb.getTemplateName(self.version)
host = self.abstractDb.db.hostName()
port = self.abstractDb.db.port()
user = self.abstractDb.db.userName()
password = self.abstractDb.db.password()
template = self.dbFactory.createDbFactory('QPSQL')
template.connectDatabaseWithParameters(host, port, database, user, password)
template.checkAndOpenDb()
if setInnerDb:
self.db = template.db
return template
def createDatabaseStructure(self):
"""
Creates database structure according to the selected edgv version
"""
currentPath = os.path.dirname(__file__)
currentPath = os.path.join(currentPath, '..', '..', 'DbTools', 'PostGISTool')
if self.version == '2.1.3':
edgvPath = os.path.join(currentPath, 'sqls', '213', 'edgv213.sql')
elif self.version == '3.0':
edgvPath = os.path.join(currentPath, 'sqls', '3', 'edgv3.sql')
elif self.version == 'FTer_2a_Ed':
edgvPath = os.path.join(currentPath, 'sqls', 'FTer_2a_Ed', 'edgvFter_2a_Ed.sql')
else:
pass
return self.loadDatabaseStructure(edgvPath)
def loadDatabaseStructure(self, edgvPath):
"""
Loads the database structure
edgvPath: path to the databse sql
"""
commands = []
hasTemplate = self.abstractDb.checkTemplate(self.version)
if hasTemplate:
templateDb = self.connectToTemplate(setInnerDb = False)
mustUpdateTemplate = templateDb.checkTemplateImplementationVersion()
if mustUpdateTemplate:
templateName = templateDb.db.databaseName()
templateDb.__del__()
self.abstractDb.dropDatabase(templateName, dropTemplate = True)
hasTemplate = False
if not hasTemplate:
file = codecs.open(edgvPath, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[epsg]', '4674')
file.close()
commands = [i for i in sql.split('#') if i != '']
# Progress bar steps calculated
self.signals.rangeCalculated.emit(len(commands)+4, self.getId())
if not hasTemplate:
try:
self.abstractDb.createTemplateDatabase(self.version)
self.signals.stepProcessed.emit(self.getId())
self.connectToTemplate()
self.signals.stepProcessed.emit(self.getId())
except Exception as e:
return (0, self.messenger.getProblemFeedbackMessage()+'\n'+':'.join(e.args))
self.db.open()
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not self.stopped[0]:
if not query.exec_(command):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
return (0, self.messenger.getProblemFeedbackMessage())
# Updating progress
self.signals.stepProcessed.emit(self.getId())
else:
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
self.db.commit()
if self.version == '2.1.3':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName()
elif self.version == 'FTer_2a_Ed':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName()
elif self.version == '3.0':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'edgv\',\'complexos\',\'dominios\';' % self.db.databaseName()
if sql:
if not query.exec_(sql):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
#this commit was missing, so alter database statement was not commited.
self.db.commit()
self.db.close()
self.abstractDb.setDbAsTemplate(self.version)
#creates from template
if not self.stopped[0]:
templateName = self.abstractDb.getTemplateName(self.version)
self.abstractDb.createDbFromTemplate(self.dbName, templateName, parentWidget = self.parent)
self.signals.stepProcessed.emit(self.getId())
#5. alter spatial structure
createdDb = self.dbFactory.createDbFactory('QPSQL')
createdDb.connectDatabaseWithParameters(self.abstractDb.db.hostName(), self.abstractDb.db.port(), self.dbName, self.abstractDb.db.userName(), self.abstractDb.db.password())
errorTuple = createdDb.updateDbSRID(self.epsg, parentWidget = self.parent, threading = True)
# if an error occur during the thread we should pass the message to the main thread
if errorTuple:
QgsMessageLog.logMessage(self.messenger.getProblemMessage(errorTuple[0], errorTuple[1]), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
self.signals.stepProcessed.emit(self.getId())
else:
QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (1, self.messenger.getSuccessFeedbackMessage())
def dropDatabase(self,db):
"""
Drops the created database case a problem occurs during database creation
db: QSqlDatabase to be dropped
"""
host = db.hostName()
port = db.port()
user = db.userName()
password = db.password()
database = 'postgres'
pgDB = QSqlDatabase('QPSQL')
pgDB.setHostName(host)
pgDB.setPort(port)
pgDB.setUserName(user)
pgDB.setPassword(password)
pgDB.setDatabaseName(database)
if not pgDB.open():
return False
sql = self.gen.dropDatabase(db.databaseName())
query = QSqlQuery(pgDB)
return query.exec_(sql)
class LoadByClass(QtGui.QDialog, load_by_class_base.Ui_LoadByClass):
def __init__(self, parent=None):
"""Constructor."""
super(LoadByClass, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.selectedClasses = []
#Sql factory generator
self.isSpatialite = True
self.setupUi(self)
self.tabWidget.setCurrentIndex(0)
self.factory = SqlGeneratorFactory()
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.utils = Utils()
self.bar = QgsMessageBar()
self.setLayout(QtGui.QGridLayout(self))
self.layout().setContentsMargins(0,0,0,0)
self.layout().setAlignment(QtCore.Qt.AlignTop)
sizePolicy = QtGui.QSizePolicy(QtGui.QSizePolicy.Minimum, QtGui.QSizePolicy.Fixed)
self.bar.setSizePolicy(sizePolicy)
self.layout().addWidget(self.bar, 0,0,1,1)
#Objects Connections
QtCore.QObject.connect(self.pushButtonOpenFile, QtCore.SIGNAL(("clicked()")), self.loadDatabase)
QtCore.QObject.connect(self.pushButtonCancel, QtCore.SIGNAL(("clicked()")), self.cancel)
QtCore.QObject.connect(self.selectAllCheck, QtCore.SIGNAL(("stateChanged(int)")), self.selectAll)
QtCore.QObject.connect(self.pushButtonOk, QtCore.SIGNAL(("clicked()")), self.okSelected)
QtCore.QObject.connect(self.tabWidget,QtCore.SIGNAL(("currentChanged(int)")), self.restoreInitialState)
self.db = None
#populating the postgis combobox
self.populatePostGISConnectionsCombo()
def __del__(self):
self.closeDatabase()
def closeDatabase(self):
if self.db:
self.db.close()
self.db = None
def restoreInitialState(self):
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.selectedClasses = []
self.spatialiteFileEdit.setText(self.filename)
self.postGISCrsEdit.setText('')
self.postGISCrsEdit.setReadOnly(True)
self.spatialiteCrsEdit.setText('')
self.spatialiteCrsEdit.setReadOnly(True)
tam = self.classesListWidget.__len__()
for i in range(tam+1,1,-1):
item = self.classesListWidget.takeItem(i-2)
self.selectAllCheck.setCheckState(0)
#Setting the database type
if self.tabWidget.currentIndex() == 0:
self.isSpatialite = True
else:
self.isSpatialite = False
#getting the sql generator according to the database type
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.comboBoxPostgis.setCurrentIndex(0)
def updateBDField(self):
if self.dbLoaded == True:
self.spatialiteFileEdit.setText(self.filename)
else:
self.filename = ""
self.spatialiteFileEdit.setText(self.filename)
def getDatabaseVersion(self):
self.dbVersion = self.utils.getDatabaseVersion(self.db)
self.qmlPath = self.utils.getQmlDir(self.db)
def listClassesFromDatabase(self):
self.classesListWidget.clear()
self.getDatabaseVersion()
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
if self.isSpatialite:
tableName = query.value(0)
layerName = tableName
else:
tableSchema = query.value(0)
tableName = query.value(1)
layerName = tableSchema+'.'+tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
item = QtGui.QListWidgetItem(layerName)
self.classesListWidget.addItem(item)
self.classesListWidget.sortItems()
self.setCRS()
def setCRS(self):
try:
self.epsg = self.utils.findEPSG(self.db)
if self.epsg == -1:
self.bar.pushMessage("", self.tr("Coordinate Reference System not set or invalid!"), level=QgsMessageBar.WARNING)
else:
self.crs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if self.isSpatialite:
self.spatialiteCrsEdit.setText(self.crs.description())
self.spatialiteCrsEdit.setReadOnly(True)
else:
self.postGISCrsEdit.setText(self.crs.description())
self.postGISCrsEdit.setReadOnly(True)
except:
pass
@pyqtSlot(int)
def on_comboBoxPostgis_currentIndexChanged(self):
if self.comboBoxPostgis.currentIndex() > 0:
self.loadDatabase()
def loadDatabase(self):
self.closeDatabase()
if self.isSpatialite:
(self.filename, self.db) = self.utils.getSpatialiteDatabase()
if self.filename:
self.spatialiteFileEdit.setText(self.filename)
else:
self.db = self.utils.getPostGISDatabase(self.comboBoxPostgis.currentText())
try:
if not self.db.open():
QgsMessageLog.logMessage(self.db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
else:
self.dbLoaded = True
self.listClassesFromDatabase()
except:
pass
def populatePostGISConnectionsCombo(self):
self.comboBoxPostgis.clear()
self.comboBoxPostgis.addItem(self.tr("Select Database"))
self.comboBoxPostgis.addItems(self.utils.getPostGISConnections())
def cancel(self):
self.restoreInitialState()
self.close()
def selectAll(self):
if self.selectAllCheck.isChecked():
tam = self.classesListWidget.__len__()
for i in range(tam+1):
item = self.classesListWidget.item(i-1)
self.classesListWidget.setItemSelected(item,2)
else:
tam = self.classesListWidget.__len__()
for i in range(tam+1):
item = self.classesListWidget.item(i-1)
self.classesListWidget.setItemSelected(item,0)
def getSelectedItems(self):
lista = self.classesListWidget.selectedItems()
self.selectedClasses = []
tam = len(lista)
for i in range(tam):
self.selectedClasses.append(lista[i].text())
self.selectedClasses.sort()
def okSelected(self):
try:
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
if self.isSpatialite:
self.loadSpatialiteLayers()
else:
self.loadPostGISLayers()
QApplication.restoreOverrideCursor()
except:
QApplication.restoreOverrideCursor()
def loadPostGISLayers(self):
self.getSelectedItems()
(database, host, port, user, password) = self.utils.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
uri = QgsDataSourceURI()
uri.setConnection(str(host),str(port), str(database), str(user), str(password))
if len(self.selectedClasses)>0:
try:
geom_column = 'geom'
for layer in self.selectedClasses:
split = layer.split('.')
schema = split[0]
layerName = split[1]
sql = self.gen.loadLayerFromDatabase(layer)
uri.setDataSource(schema, layerName, geom_column, sql,'id')
uri.disableSelectAtId(True)
self.loadEDGVLayer(uri, layerName, 'postgres')
self.restoreInitialState()
self.close()
except:
self.bar.pushMessage(self.tr("Error!"), self.tr("Could not load the selected classes!"), level=QgsMessageBar.CRITICAL)
else:
self.bar.pushMessage(self.tr("Warning!"), self.tr("Please, select at least one class!"), level=QgsMessageBar.WARNING)
def loadSpatialiteLayers(self):
self.getSelectedItems()
uri = QgsDataSourceURI()
uri.setDatabase(self.filename)
schema = ''
geom_column = 'GEOMETRY'
if len(self.selectedClasses)>0:
try:
for layer_name in self.selectedClasses:
uri.setDataSource(schema, layer_name, geom_column)
self.loadEDGVLayer(uri, layer_name, 'spatialite')
self.restoreInitialState()
self.close()
except:
self.bar.pushMessage(self.tr("Error!"), self.tr("Could not load the layer(s)!"), level=QgsMessageBar.CRITICAL)
else:
self.bar.pushMessage(self.tr("Warning!"), self.tr("Please select at least one layer!"), level=QgsMessageBar.WARNING)
def loadEDGVLayer(self, uri, layer_name, provider):
vlayer = QgsVectorLayer(uri.uri(), layer_name, provider)
vlayer.setCrs(self.crs)
QgsMapLayerRegistry.instance().addMapLayer(vlayer) #added due to api changes
if self.isSpatialite and (self.dbVersion == '3.0' or self.dbVersion == '2.1.3'):
lyr = '_'.join(layer_name.replace('\r', '').split('_')[1::])
else:
lyr = layer_name.replace('\r','')
vlayerQml = os.path.join(self.qmlPath, lyr+'.qml')
vlayer.loadNamedStyle(vlayerQml, False)
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
if not vlayer.isValid():
QgsMessageLog.logMessage(vlayer.error().summary(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
class ServerDBExplorer(QtGui.QDialog, FORM_CLASS):
def __init__(self, parent = None):
"""Constructor."""
super(self.__class__, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.utils = Utils()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.serverWidget.populateServersCombo()
self.serverWidget.abstractDbLoaded.connect(self.populateListWithDatabasesFromServer)
def storeConnection(self, server, database):
(host, port, user, password) = self.getServerConfiguration(server)
connection = server+'_'+database
settings = QSettings()
if not settings.contains('PostgreSQL/connections/'+connection+'/database'):
settings.beginGroup('PostgreSQL/connections/'+connection)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
return True
return False
def getServerConfiguration(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers/'+name)
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, user, password)
def storeConnectionConfiguration(self, server, database):
name = self.connectionEdit.text()
(host, port, user, password) = self.getServerConfiguration(server)
settings = QSettings()
if not settings.contains('PostgreSQL/servers/'+name+'/host'):
settings.beginGroup('PostgreSQL/connections/'+name)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
def populateListWithDatabasesFromServer(self):
self.serverListWidget.clear()
dbList = self.serverWidget.abstractDb.getEDGVDbsFromServer()
dbList.sort()
for (dbname, dbversion) in dbList:
item = QListWidgetItem(self.serverListWidget)
item.setText(dbname+' (EDGV v. '+dbversion+')')
item.setData(Qt.UserRole, dbname)
@pyqtSlot(bool)
def on_createConnectionPushButton_clicked(self):
items = self.serverListWidget.selectedItems()
existentConnections = []
newConnections = []
for item in items:
dbname = item.data(Qt.UserRole)
ret = self.storeConnection(self.serverWidget.serversCombo.currentText(), dbname)
if not ret:
existentConnections.append(dbname)
else:
newConnections.append(dbname)
msg = self.tr('Information:\n')
if len(existentConnections) > 0:
msg += self.tr('The following databases connections already exist:\n')
for conn in existentConnections:
msg += conn + ', '
if len(newConnections) > 0:
msg += self.tr('\nThe following databases connections were created successfully:\n')
for conn in newConnections:
msg += conn+', '
QMessageBox.warning(self, self.tr("Warning!"), msg)
@pyqtSlot(bool)
def on_selectAllPushButton_clicked(self):
count = self.serverListWidget.count()
for row in range(count):
item = self.serverListWidget.item(row)
item.setSelected(True)
@pyqtSlot(bool)
def on_removeMissingPushButton_clicked(self):
servers = self.serverWidget.getServers()
settings = QSettings()
settings.beginGroup('PostgreSQL/connections')
candidates = settings.childGroups()
settings.endGroup()
removedConn = []
dbList = self.serverWidget.abstractDb.getDbsFromServer()
for candidate in candidates:
candidateSettings = QSettings()
candidateSettings.beginGroup('PostgreSQL/connections/'+candidate)
candidateDb = candidateSettings.value('database')
if candidateDb not in dbList:
self.removeConnections(candidate, removedConn)
if len(removedConn)>0:
msg = self.tr('\nThe following databases connections were removed successfully:\n')+', '.join(removedConn)
else:
msg = self.tr('No connections were removed.')
QMessageBox.warning(self, self.tr("Warning!"), msg)
def removeConnections(self,candidate,removedConn):
candidateSettings = QSettings()
candidateSettings.beginGroup('PostgreSQL/connections/'+candidate)
candidateSettings.remove('')
removedConn.append(candidate)
candidateSettings.endGroup()
def __init__(self):
super(PostgisDb,self).__init__()
self.db = QSqlDatabase('QPSQL')
self.gen = SqlGeneratorFactory().createSqlGenerator(False)
class PostgisDbThread(GenericThread):
def __init__(self):
"""Constructor.
"""
super(PostgisDbThread, self).__init__()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.messenger = PostgisDbMessages(self)
def setParameters(self, db, version, epsg, stopped):
self.db = db
self.version = version
self.epsg = epsg
self.stopped = stopped
def run(self):
# Processing ending
(ret, msg) = self.createDatabaseStructure()
self.signals.processingFinished.emit(ret, msg, self.getId())
def createDatabaseStructure(self):
currentPath = os.path.dirname(__file__)
currentPath = os.path.join(currentPath, '..', '..', 'DbTools', 'PostGISTool')
if self.version == '2.1.3':
edgvPath = os.path.join(currentPath, 'sqls', '213', 'edgv213.sql')
elif self.version == '3.0':
edgvPath = os.path.join(currentPath, 'sqls', '30', 'edgv30.sql')
elif self.version == 'FTer_2a_Ed':
edgvPath = os.path.join(currentPath, 'sqls', 'FTer_2a_Ed', 'edgvFter_2a_Ed.sql')
else:
pass
return self.loadDatabaseStructure(edgvPath)
def loadDatabaseStructure(self, edgvPath):
file = codecs.open(edgvPath, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[epsg]', str(self.epsg))
file.close()
commands = sql.split('#')
# Progress bar steps calculated
self.signals.rangeCalculated.emit(len(commands), self.getId())
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not self.stopped[0]:
if not query.exec_(command):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
return (0, self.messenger.getProblemFeedbackMessage())
# Updating progress
self.signals.stepProcessed.emit(self.getId())
else:
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
self.db.commit()
if self.version == '2.1.3':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName()
elif self.version == 'FTer_2a_Ed':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName()
if not query.exec_(sql):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
self.db.close()
QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (1, self.messenger.getSuccessFeedbackMessage())
def dropDatabase(self,db):
host = db.hostName()
port = db.port()
user = db.userName()
password = db.password()
database = 'postgres'
pgDB = QSqlDatabase('QPSQL')
pgDB.setHostName(host)
pgDB.setPort(port)
pgDB.setUserName(user)
pgDB.setPassword(password)
pgDB.setDatabaseName(database)
if not pgDB.open():
return False
sql = self.gen.dropDatabase(db.databaseName())
query = QSqlQuery(pgDB)
return query.exec_(sql)
class SpatialiteDb(AbstractDb):
def __init__(self):
super(SpatialiteDb,self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def getDatabaseName(self):
return self.db.databaseName().split('.sqlite')[0].split('/')[-1]
def connectDatabase(self, conn = None):
if conn is None:
self.connectDatabaseWithGui()
else:
self.db.setDatabaseName(conn)
def connectDatabaseWithGui(self):
fd = QFileDialog()
filename = fd.getOpenFileName(caption=self.tr('Select a DSGTools Spatialite file'),filter=self.tr('Spatialite file databases (*.sqlite)'))
self.db.setDatabaseName(filename)
def connectDatabaseWithQSettings(self, name):
return None
def connectDatabaseWithParameters(self, host, port, database, user, password):
return None
def listGeomClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def listComplexClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def getConnectionFromQSettings(self, conName):
return None
def storeConnection(self, server):
return None
def getServerConfiguration(self, name):
return None
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
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 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 classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def makeOgrConn(self):
constring = self.db.databaseName()
return constring
def getNotNullDict(self):
return None
def getDomainDict(self):
return None
def validateWithOutputDatabaseSchema(self,outputAbstractDb):
try:
self.checkAndOpenDb()
except:
return dict()
invalidated = self.buildInvalidatedDict()
inputdbStructure = self.getStructureDict()
outputdbStructure = outputAbstractDb.getStructureDict()
domainDict = outputAbstractDb.getDomainDict()
classes = self.listClassesWithElementsFromDatabase()
notNullDict = outputAbstractDb.getNotNullDict()
for inputClass in classes.keys():
outputClass = self.translateAbstractDbLayerNameToOutputFormat(inputClass,outputAbstractDb)
(schema,className) = self.getTableSchema(inputClass)
if outputClass in outputdbStructure.keys():
outputAttrList = self.reorderTupleList(outputdbStructure[outputClass].keys())
inputAttrList = self.reorderTupleList(inputdbStructure[inputClass].keys())
sql = self.gen.getFeaturesWithSQL(inputClass,inputAttrList)
query = QSqlQuery(sql, self.db)
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 invalidated['nullLine'].keys():
invalidated['nullLine'][inputClass]=0
invalidated['nullLine'][inputClass]+=1
#validates pks
if id == None and (not nullLine):
if cl not in 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 domainDict.keys():
if inputAttrList[i] in 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 notNullDict.keys():
if outputClass in domainDict.keys():
if inputAttrList[i] in notNullDict[outputClass] and inputAttrList[i] not in domainDict[outputClass].keys():
if (value == None) and (not nullLine) and (inputAttrList[i] not in domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(invalidated, ['nullAttribute',inputClass,id,inputAttrList[i]], value)
else:
if inputAttrList[i] in notNullDict[outputClass]:
if (value == None) and (not nullLine) and (inputAttrList[i] not in domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(invalidated, ['nullAttribute',inputClass,id,inputAttrList[i]], value)
if outputClass in domainDict.keys():
if (inputAttrList[i] not in ['geom','GEOMETRY','id','OGC_FID'] and schema <> 'complexos') or (schema == 'complexos' and inputAttrList[i] <> 'id'):
if inputAttrList[i] not in outputdbStructure[outputClass].keys():
invalidated = self.utils.buildNestedDict(invalidated, ['attributeNotFoundInOutput',inputClass], [inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def translateAbstractDbLayerNameToOutputFormat(self,lyr,outputAbstractDb):
if outputAbstractDb.db.driverName() == 'QSQLITE':
return lyr
if outputAbstractDb.db.driverName() == 'QPSQL':
return str(lyr.split('_')[0]+'.'+'_'.join(lyr.split('_')[1::]))
def translateOGRLayerNameToOutputFormat(self,lyr,ogrOutput):
if ogrOutput.GetDriver().name == 'SQLite':
return lyr
if ogrOutput.GetDriver().name == 'PostgreSQL':
return str(lyr.split('_')[0]+'.'+'_'.join(lyr.split('_')[1::]))
def getTableSchema(self,lyr):
schema = lyr.split('_')[0]
className = '_'.join(lyr.split('_')[1::])
return (schema, className)
def convertToPostgis(self, outputAbstractDb,type=None):
try:
self.checkAndOpenDb()
except:
return False
(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict) = self.prepareForConversion(outputAbstractDb)
invalidated = self.validateWithOutputDatabaseSchema(outputAbstractDb)
hasErrors = self.makeValidationSummary(invalidated)
if type == 'untouchedData':
if hasErrors:
self.signals.updateLog.emit('\n\n\n'+self.tr('Conversion not perfomed due to validation errors! Check log above for more information.'))
return False
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict)
return status
if type == 'fixData':
if hasErrors:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict, invalidated)
return status
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict)
return status
return False
def convertToSpatialite(self, outputAbstractDb,type=None):
return None
def getDatabaseVersion(self):
try:
self.checkAndOpenDb()
except:
return None
version = '2.1.3'
try:
sqlVersion = self.gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, self.db)
while queryVersion.next():
version = queryVersion.value(0)
except:
version = '2.1.3'
return version
def obtainLinkColumn(self, complexClass, aggregatedClass):
try:
self.checkAndOpenDb()
except:
return ''
#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)
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def loadAssociatedFeatures(self, complex):
try:
self.checkAndOpenDb()
except:
return dict()
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)
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)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
while complexQuery.next():
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)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(associatedDict, [name, complex_uuid, aggregated_class], [ogc_fid])
return associatedDict
def isComplexClass(self, className):
try:
self.checkAndOpenDb()
except:
return False
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == 'complexos_'+className:
return True
return False
def disassociateComplexFromComplex(self, aggregated_class, link_column, id):
sql = self.gen.disassociateComplexFromComplex('complexos_'+aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem disassociating complex from complex: ') + '\n' + query.lastError().text())
def getUsers(self):
return None
def getUserRelatedRoles(self, username):
return None
def getRoles(self):
return None
def createRole(self, role, dict):
pass
def dropRole(self, role):
pass
def alterUserPass(self, user, newpassword):
pass
def createUser(self, user, password, isSuperUser):
pass
def removeUser(self, user):
pass
def grantRole(self, user, role):
pass
def revokeRole(self, user, role):
pass
def getTablesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
#table name
ret.append(query.value(0))
return ret
def getRolePrivileges(self, role, dbname):
return None
def getFrameLayerName(self):
return 'public_aux_moldura_a'
def getEDGVDbsFromServer(self,name):
return None
def getDbsFromServer(self):
return None
def checkSuperUser(self):
return None
def dropDatabase(self,abstractCandidate):
return None
def createResolvedDomainViews(self, createViewClause, fromClause):
pass
def getSqlViewFile(self):
pass
class PostgisDb(AbstractDb):
def __init__(self):
super(PostgisDb,self).__init__()
self.db = QSqlDatabase('QPSQL')
self.gen = SqlGeneratorFactory().createSqlGenerator(False)
def getDatabaseName(self):
return self.db.databaseName()
def connectDatabase(self,conn=None):
if conn.split(':')[0] == 'PG':
connSplit = conn.split(' ')
parDict = dict()
for i in connSplit[1::]:
par = i.split('=')
parDict[par[0]]=par[1]
self.connectDatabaseWithParameters(parDict['host'], parDict['port'], parDict['dbname'], parDict['user'], parDict['password'])
else:
self.connectDatabaseWithQSettings(conn)
def connectDatabaseWithGui(self):
return None
def connectDatabaseWithParameters(self, host, port, database, user, password):
self.db.setHostName(host)
if type(port) != 'int':
self.db.setPort(int(port))
else:
self.db.setPort(port)
self.db.setDatabaseName(database)
self.db.setUserName(user)
if not password or password == '':
conInfo = 'host='+host+' port='+port+' dbname='+database
check = False
while not check:
try:
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
self.db.setPassword(password)
check = True
self.checkAndOpenDb()
QgsCredentials.instance().put(conInfo, user, password)
except:
pass
else:
self.db.setPassword(password)
def connectDatabaseWithQSettings(self, name):
(host, port, database, user, password) = self.getConnectionFromQSettings(name)
self.db.setHostName(host)
if type(port) != 'int':
self.db.setPort(int(port))
else:
self.db.setPort(port)
self.db.setDatabaseName(database)
self.db.setUserName(user)
if not password or password == '':
conInfo = 'host='+host+' port='+port+' dbname='+database
check = False
while not check:
try:
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
self.db.setPassword(password)
check = True
self.checkAndOpenDb()
QgsCredentials.instance().put(conInfo, user, password)
except:
pass
else:
self.db.setPassword(password)
def getDatabaseVersion(self):
try:
self.checkAndOpenDb()
except:
return '-1'
sqlVersion = self.gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, self.db)
while queryVersion.next():
version = queryVersion.value(0)
return version
def listGeomClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableSchema = query.value(0)
tableName = query.value(1)
layerName = tableSchema+'.'+tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def listComplexClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableSchema = query.value(0)
tableName = query.value(1)
layerName = tableSchema+'.'+tableName
if tableSchema == 'complexos':
classList.append(layerName)
classList.sort()
return classList
def storeConnection(self, server):
(host, port, user, password) = self.getServerConfiguration(server)
database = self.db.databaseName()
connection = server+'_'+database
settings = QSettings()
if not settings.contains('PostgreSQL/connections/'+connection+'/database'):
settings.beginGroup('PostgreSQL/connections/'+connection)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
return True
return False
def getConnectionFromQSettings(self, conName):
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+conName)
host = settings.value('host')
port = settings.value('port')
database = settings.value('database')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, database, user, password)
def getServerConfiguration(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers/'+name)
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, user, password)
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
while query.next():
className = str(query.value(0))+'.'+str(query.value(1))
fieldName = str(query.value(2))
if str(query.value(0)) == 'complexos' or className.split('_')[-1] in ['p','l','a']:
if className not in classDict.keys():
classDict[className]=dict()
classDict[className][fieldName]=fieldName
if 'geom' in classDict[className].keys():
classDict[className]['geom'] = 'GEOMETRY'
if str(query.value(0)) <> 'complexos' and 'id' in classDict[className].keys():
classDict[className]['id'] = 'OGC_FID'
return classDict
def makeOgrConn(self):
dbName = self.db.databaseName()
dbUser = self.db.userName()
dbHost = self.db.hostName()
dbPass = self.db.password()
dbPort = str(self.db.port())
constring = 'PG: dbname=\''+dbName+'\' user=\''+dbUser+'\' host=\''+dbHost+'\' password=\''+dbPass+'\' port='+dbPort
return constring
def getNotNullDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
if self.getDatabaseVersion() == '2.1.3':
schemaList = ['cb', 'complexos']
elif self.getDatabaseVersion() == 'FTer_2a_Ed':
schemaList = ['pe','ge','ct', 'complexos']
else:
QgsMessageLog.logMessage(self.tr('Operation not defined for this database version!'), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return None
sql = self.gen.getNotNullFields(schemaList)
query = QSqlQuery(sql, self.db)
notNullDict = dict()
while query.next():
schemaName = str(query.value(0))
className = str(query.value(1))
attName = str(query.value(2))
cl = schemaName+'.'+className
if cl not in notNullDict.keys():
notNullDict[cl] = []
notNullDict[cl].append(attName)
return notNullDict
def getDomainDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
if self.getDatabaseVersion() == '2.1.3':
schemaList = ['cb', 'complexos', 'dominios']
elif self.getDatabaseVersion() == 'FTer_2a_Ed':
schemaList = ['pe','ge','ct', 'complexos']
else:
QgsMessageLog.logMessage(self.tr('Operation not defined for this database version!'), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return
sql = self.gen.validateWithDomain(schemaList)
query = QSqlQuery(sql, self.db)
classDict = dict()
domainDict = dict()
while query.next():
schemaName = str(query.value(0))
className = str(query.value(1))
attName = str(query.value(2))
domainName = str(query.value(3))
domainTable = str(query.value(4))
domainQuery = str(query.value(5))
cl = schemaName+'.'+className
query2 = QSqlQuery(domainQuery,self.db)
while query2.next():
value = int(query2.value(0))
classDict = self.utils.buildNestedDict(classDict,[str(cl),str(attName)],[value])
return classDict
def validateWithOutputDatabaseSchema(self,outputAbstractDb):
return None
def translateAbstractDbLayerNameToOutputFormat(self,lyr,outputAbstractDb):
if outputAbstractDb.db.driverName() == 'QSQLITE':
return str(lyr.split('.')[0]+'_'+'_'.join(lyr.split('.')[1::]))
if outputAbstractDb.db.driverName() == 'QPSQL':
return lyr
def translateOGRLayerNameToOutputFormat(self,lyr,ogrOutput):
if ogrOutput.GetDriver().name == 'SQLite':
return str(lyr.split('.')[0]+'_'+'_'.join(lyr.split('.')[1::]))
if ogrOutput.GetDriver().name == 'PostgreSQL':
return lyr
def getTableSchema(self,lyr):
schema = lyr.split('.')[0]
className = '_'.join(lyr.split('.')[1::])
return (schema, className)
def convertToPostgis(self, outputAbstractDb,type=None):
return None
def convertToSpatialite(self, outputAbstractDb,type=None):
(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict) = self.prepareForConversion(outputAbstractDb)
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap, inputLayerList, errorDict)
return status
def obtainLinkColumn(self, complexClass, aggregatedClass):
try:
self.checkAndOpenDb()
except:
return ''
complexClass = complexClass.replace('complexos.', '')
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass, aggregatedClass)
query = QSqlQuery(sql, self.db)
column_name = ''
while query.next():
column_name = query.value(0)
return column_name
def loadAssociatedFeatures(self, complex):
try:
self.checkAndOpenDb()
except:
return dict()
associatedDict = dict()
complex = complex.replace('complexos.', '')
#query to get the possible links to the selected complex in the combobox
sql = self.gen.getComplexLinks(complex)
query = QSqlQuery(sql, self.db)
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)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
while complexQuery.next():
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)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(associatedDict, [name, complex_uuid, aggregated_class], [ogc_fid])
return associatedDict
def isComplexClass(self, className):
try:
self.checkAndOpenDb()
except:
return False
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == className:
return True
return False
def disassociateComplexFromComplex(self, aggregated_class, link_column, id):
sql = self.gen.disassociateComplexFromComplex(aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem disassociating complex from complex: ') + '\n' + query.lastError().text())
def getUsers(self):
try:
self.checkAndOpenDb()
except:
return []
ret = []
sql = self.gen.getUsers()
query = QSqlQuery(sql, self.db)
while query.next():
ret.append(query.value(0))
ret.sort()
return ret
def getUserRelatedRoles(self, username):
try:
self.checkAndOpenDb()
except:
return [], []
installed = []
assigned = []
sql = self.gen.getUserRelatedRoles(username)
query = QSqlQuery(sql, self.db)
while query.next():
rolname = query.value(0)
usename = query.value(1)
if not usename:
installed.append(rolname)
else:
assigned.append(rolname)
installed.sort()
assigned.sort()
return installed, assigned
def getRoles(self):
try:
self.checkAndOpenDb()
except:
return []
ret = []
sql = self.gen.getRoles()
query = QSqlQuery(sql, self.db)
while query.next():
ret.append(query.value(0))
ret.sort()
return ret
def createRole(self, role, dict):
try:
self.checkAndOpenDb()
except:
return
#making this so the instaciated permissions stay with different names
uuid = str(uuid4()).replace('-', '_')
role += '_'+uuid
sql = self.gen.createRole(role, dict)
split = sql.split(';')
query = QSqlQuery(self.db)
#try to revoke the permissions
try:
self.dropRole(role)
except:
pass
for inner in split:
if not query.exec_(inner):
if '42710' in query.lastError().text():
#In this case the role is already created (duplicate object error). We just need to proceed executing the grants.
continue
else:
raise Exception(self.tr('Problem assigning profile: ') +role+'\n'+query.lastError().text())
def dropRole(self, role):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.dropRole(role)
split = sql.split('#')
query = QSqlQuery(self.db)
for inner in split:
if not query.exec_(inner):
if '2BP01' in query.lastError().text():
#In this case the role is still used by other databases, therefore it shouldn't be dropped.
continue
else:
raise Exception(self.tr('Problem removing profile: ') +role+'\n'+query.lastError().text())
def alterUserPass(self, user, newpassword):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.alterUserPass(user, newpassword)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem altering user\'s password: '******'\n'+query.lastError().text())
def createUser(self, user, password, isSuperUser):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.createUser(user, password, isSuperUser)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem creating user: '******'\n'+query.lastError().text())
def removeUser(self, user):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.removeUser(user)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem removing user: '******'\n'+query.lastError().text())
def grantRole(self, user, role):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.grantRole(user, role)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem granting profile: ') +role+'\n'+query.lastError().text())
def revokeRole(self, user, role):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.revokeRole(user, role)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem revoking profile: ') +role+'\n'+query.lastError().text())
def getTablesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
#table name
ret.append(query.value(0))
return ret
def getRolePrivileges(self, role, dbname):
try:
self.checkAndOpenDb()
except:
return dict()
privilegesDict = dict()
sql = self.gen.getRolePrivileges(role, dbname)
query = QSqlQuery(sql, self.db)
while query.next():
schema = query.value(3)
table = query.value(4)
privilege = query.value(5)
if schema in ['cb', 'public', 'complexos', 'pe', 'ge']:
privilegesDict = self.utils.buildNestedDict(privilegesDict, [schema, table], [privilege])
permissionsDict = dict()
for schema in privilegesDict.keys():
for table in privilegesDict[schema].keys():
split = table.split('_')
category = split[0]
layerName = schema+'.'+table
if schema not in permissionsDict.keys():
permissionsDict[schema] = dict()
if category not in permissionsDict[schema].keys():
permissionsDict[schema][category] = dict()
privileges = privilegesDict[schema][table]
write = ['DELETE', 'INSERT', 'SELECT', 'UPDATE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']
if all((permission in privileges for permission in write)):
if layerName not in permissionsDict[schema][category]:
permissionsDict[schema][category][layerName] = dict()
permissionsDict[schema][category][layerName]['read'] = '2'#read yes
permissionsDict[schema][category][layerName]['write'] = '2'#write yes
else:
if layerName not in permissionsDict[schema][category]:
permissionsDict[schema][category][layerName] = dict()
permissionsDict[schema][category][layerName]['read'] = '2'#read yes
permissionsDict[schema][category][layerName]['write'] = '0'#write no
return permissionsDict
def getFrameLayerName(self):
return 'public.aux_moldura_a'
def getEDGVDbsFromServer(self):
#Can only be used in postgres database.
try:
self.checkAndOpenDb()
except:
return []
query = QSqlQuery(self.gen.getDatabasesFromServer(),self.db)
dbList = []
while query.next():
dbList.append(query.value(0))
edvgDbList = []
for database in dbList:
db = None
db = QSqlDatabase("QPSQL")
db.setDatabaseName(database)
db.setHostName(self.db.hostName())
db.setPort(self.db.port())
db.setUserName(self.db.userName())
db.setPassword(self.db.password())
if not db.open():
QgsMessageLog.logMessage('DB :'+database+'| msg: '+db.lastError().databaseText(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
query2 = QSqlQuery(db)
if query2.exec_(self.gen.getEDGVVersion()):
while query2.next():
version = query2.value(0)
if version:
edvgDbList.append((database,version))
else:
QgsMessageLog.logMessage(self.tr('Problem accessing database: ') +database+'\n'+query2.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return edvgDbList
def getDbsFromServer(self):
#Can only be used in postgres database.
try:
self.checkAndOpenDb()
except:
return []
query = QSqlQuery(self.gen.getDatabasesFromServer(),self.db)
dbList = []
while query.next():
dbList.append(query.value(0))
return dbList
def checkSuperUser(self):
try:
self.checkAndOpenDb()
except:
return False
query = QSqlQuery(self.db)
if query.exec_(self.gen.isSuperUser(self.db.userName())):
query.next()
value = query.value(0)
return value
return False
def dropDatabase(self, candidateName):
try:
self.checkAndOpenDb()
except:
return
if self.checkSuperUser():
sql = self.gen.dropDatabase(candidateName)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem dropping database: ') + query.lastError().text())
def createResolvedDomainViews(self, createViewClause, fromClause):
try:
self.checkAndOpenDb()
except:
return
if self.checkSuperUser():
filename = self.getSqlViewFile()
if filename <> None:
file = codecs.open(filename, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[VIEW]', createViewClause).replace('[FROM]', fromClause)
file.close()
commands = sql.split('#')
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not query.exec_(command):
self.db.rollback()
self.db.close()
raise Exception(self.tr('Problem creating views: ') + str(query.lastError().text()))
self.db.commit()
self.db.close()
def getSqlViewFile(self):
try:
self.checkAndOpenDb()
except:
return
currentPath = os.path.dirname(__file__)
dbVersion = self.getDatabaseVersion()
file = None
if dbVersion == '2.1.3':
file = os.path.join(currentPath,'..','..','DbTools','PostGISTool', 'sqls', '213', 'views_213.sql')
if dbVersion == 'FTer_2a_Ed':
file = os.path.join(currentPath,'..','..','DbTools','PostGISTool', 'sqls', 'FTer_2a_Ed', 'views_edgvFter_2a_Ed.sql')
return file
class SpatialiteDb(AbstractDb):
def __init__(self):
'''
Constructor
'''
super(SpatialiteDb, self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def getDatabaseName(self):
'''
Gets the database name
'''
return self.db.databaseName().split('.sqlite')[0].split('/')[-1]
def connectDatabase(self, conn=None):
'''
Connects to database
conn: Database name
'''
if conn is None:
self.connectDatabaseWithGui()
else:
self.db.setDatabaseName(conn)
def connectDatabaseWithGui(self):
'''
Connects to database using user interface dialog
'''
fd = QFileDialog()
filename = fd.getOpenFileName(
caption=self.tr('Select a DSGTools Spatialite file'),
filter=self.tr('Spatialite file databases (*.sqlite)'))
self.db.setDatabaseName(filename)
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.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
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 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 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 classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in classDict[className].keys():
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def makeOgrConn(self):
'''
Makes a connection string for spatialite databases (e.g just the name)
'''
constring = self.db.databaseName()
return constring
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 classes.keys():
outputClass = self.translateAbstractDbLayerNameToOutputFormat(
inputClass, outputAbstractDb)
(schema, className) = self.getTableSchema(inputClass)
if outputClass in outputdbStructure.keys():
outputAttrList = self.reorderTupleList(
outputdbStructure[outputClass].keys())
inputAttrList = self.reorderTupleList(
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 invalidated['nullLine'].keys():
invalidated['nullLine'][inputClass] = 0
invalidated['nullLine'][inputClass] += 1
#validates pks
if id == None and (not nullLine):
if cl not in 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 domainDict.keys():
if inputAttrList[i] in 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 notNullDict.keys():
if outputClass in domainDict.keys():
if inputAttrList[i] in notNullDict[
outputClass] and inputAttrList[
i] not in domainDict[
outputClass].keys():
if (value == None) and (not nullLine) and (
inputAttrList[i] not in
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
domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute',
inputClass, id,
inputAttrList[i]
], value)
if outputClass in 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 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 outputdbStructure[
outputClass].keys():
invalidated = self.utils.buildNestedDict(
invalidated,
['nullComplexFk', inputClass],
[inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def translateAbstractDbLayerNameToOutputFormat(self, lyr,
outputAbstractDb):
'''
Translates abstractdb layer name to output format
lyr: layer name that will be translated
outputAbstractDb: output database
'''
if outputAbstractDb.db.driverName() == 'QSQLITE':
return lyr
if outputAbstractDb.db.driverName() == 'QPSQL':
return str(lyr.split('_')[0] + '.' + '_'.join(lyr.split('_')[1::]))
def translateOGRLayerNameToOutputFormat(self, lyr, ogrOutput):
'''
Translates ogr layer name to output format
lyr: layer name that will be translated
ogrOutput: ogr output
'''
if ogrOutput.GetDriver().name == 'SQLite':
return lyr
if ogrOutput.GetDriver().name == 'PostgreSQL':
return str(lyr.split('_')[0] + '.' + '_'.join(lyr.split('_')[1::]))
def getTableSchema(self, lyr):
'''
Gets the table schema
lyr: layer name
'''
schema = lyr.split('_')[0]
className = '_'.join(lyr.split('_')[1::])
return (schema, className)
def convertToPostgis(self, outputAbstractDb, type=None):
'''
Converts this to a postgis database
outputAbstractDb: postgis output
type: conversion type
'''
(inputOgrDb, outputOgrDb, fieldMap, inputLayerList,
errorDict) = self.prepareForConversion(outputAbstractDb)
invalidated = self.validateWithOutputDatabaseSchema(outputAbstractDb)
hasErrors = self.makeValidationSummary(invalidated)
if type == 'untouchedData':
if hasErrors:
self.signals.updateLog.emit('\n\n\n' + self.tr(
'Conversion not perfomed due to validation errors! Check log above for more information.'
))
return False
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict)
return status
if type == 'fixData':
if hasErrors:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict,
invalidated)
return status
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict)
return status
return False
def getDatabaseVersion(self):
'''
Gets the database version
'''
self.checkAndOpenDb()
version = '2.1.3'
sql = self.gen.getEDGVVersion()
query = QSqlQuery(sql, self.db)
# if not query.isActive():
# raise Exception(self.tr("Problem getting database version: ")+query.lastError().text())
while query.next():
version = query.value(0)
return version
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 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 complexQuery.next():
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 associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class],
[ogc_fid])
return associatedDict
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 disassociateComplexFromComplex(self, aggregated_class, link_column,
id):
'''
Disassociates a complex from another complex
aggregated_class: aggregated class that will be disassociated
link_column: link column between complex and its aggregated class
id: complex id (uid) to be disassociated
'''
sql = self.gen.disassociateComplexFromComplex(
'complexos_' + aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
self.db.close()
raise Exception(
self.tr('Problem disassociating complex from complex: ') +
'\n' + query.lastError().text())
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 getFrameLayerName(self):
'''
Gets the frame layer name
'''
return 'public_aux_moldura_a'
def getOrphanGeomTablesWithElements(self, loading=False):
return []
def getOrphanGeomTables(self):
return []
def checkAndCreateStyleTable(self):
return None
def getStylesFromDb(self, dbVersion):
return None
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 geomDict.keys():
geomDict[type] = []
if layerName not in geomDict[type]:
geomDict[type].append(layerName)
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 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 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 geomDict.keys():
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def createFrame(self, type, scale, param, paramDict=dict()):
mi, inom, frame = self.prepareCreateFrame(type, scale, param)
self.insertFrame(scale, mi, inom, frame.asWkb())
return frame
def insertFrame(self, scale, mi, inom, frame):
self.checkAndOpenDb()
srid = self.findEPSG()
geoSrid = QgsCoordinateReferenceSystem(
int(srid)).geographicCRSAuthId().split(':')[-1]
ogr.UseExceptions()
outputDS = self.buildOgrDatabase()
outputLayer = outputDS.GetLayerByName('public_aux_moldura_a')
newFeat = ogr.Feature(outputLayer.GetLayerDefn())
auxGeom = ogr.CreateGeometryFromWkb(frame)
#set geographic srid from frame
geoSrs = ogr.osr.SpatialReference()
geoSrs.ImportFromEPSG(int(geoSrid))
auxGeom.AssignSpatialReference(geoSrs)
#reproject geom
outSpatialRef = outputLayer.GetSpatialRef()
coordTrans = osr.CoordinateTransformation(geoSrs, outSpatialRef)
auxGeom.Transform(coordTrans)
newFeat.SetGeometry(auxGeom)
newFeat.SetField('mi', mi)
newFeat.SetField('inom', inom)
newFeat.SetField('escala', str(scale))
out = outputLayer.CreateFeature(newFeat)
outputDS.Destroy()
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 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 getResolvedGeomType(self, geometryType):
geomDict = {
0: 'GEOMETRY',
1: 'POINT',
2: 'LINESTRING',
3: 'POLYGON',
4: 'MULTIPOINT',
5: 'MULTILINESTRING',
6: 'MULTIPOLYGON',
7: 'GEOMETRYCOLLECTION',
8: 'CIRCULARSTRING',
9: 'COMPOUNDCURVE',
10: 'CURVEPOLYGON',
11: 'MULTICURVE',
12: 'MULTISURFACE',
13: 'CURVE',
14: 'SURFACE'
}
return geomDict[geometryType]
def __init__(self):
self.factory = SqlGeneratorFactory()
class Utils:
def __init__(self):
self.factory = SqlGeneratorFactory()
def __del__(self):
pass
def getQmlDir(self, db):
currentPath = os.path.dirname(__file__)
if qgis.core.QGis.QGIS_VERSION_INT >= 20600:
qmlVersionPath = os.path.join(currentPath, '..', 'Qmls', 'qgis_26')
else:
qmlVersionPath = os.path.join(currentPath, '..', 'Qmls', 'qgis_22')
version = self.getDatabaseVersion(db)
if version == '3.0':
qmlPath = os.path.join(qmlVersionPath, 'edgv_30')
elif version == '2.1.3':
qmlPath = os.path.join(qmlVersionPath, 'edgv_213')
return qmlPath
def findEPSG(self, db):
gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db))
sql = gen.getSrid()
query = QSqlQuery(sql, db)
srids = []
while query.next():
srids.append(query.value(0))
return srids[0]
def getPostGISConnectionParameters(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+name)
database = settings.value('database')
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (database, host, port, user, password)
def getPostGISConnections(self):
settings = QSettings()
settings.beginGroup('PostgreSQL/connections')
currentConnections = settings.childGroups()
settings.endGroup()
return currentConnections
def getSpatialiteDatabase(self):
db = None
fd = QFileDialog()
filename = fd.getOpenFileName(filter='*.sqlite')
if filename:
db = QSqlDatabase("QSQLITE")
db.setDatabaseName(filename)
return (filename, db)
def getPostGISDatabase(self, postGISConnection):
db = None
db = QSqlDatabase("QPSQL")
(database, host, port, user, password) = self.getPostGISConnectionParameters(postGISConnection)
db.setDatabaseName(database)
db.setHostName(host)
db.setPort(int(port))
db.setUserName(user)
db.setPassword(password)
return db
def getDatabaseVersion(self, db):
gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db))
sqlVersion = gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, db)
version = '2.1.3'
while queryVersion.next():
version = queryVersion.value(0)
return version
def isSpatialiteDB(self, db):
if db.driverName() == 'QPSQL':
isSpatialite = False
elif db.driverName() == 'QSQLITE':
isSpatialite = True
return isSpatialite
class PostgisDBTool(QDialog, Ui_Dialog):
def __init__(self, iface):
"""Constructor."""
super(PostgisDBTool, self).__init__()
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
self.populateServersCombo()
self.srs = None
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.epsg = 4326
def getParameters(self):
return (self.getDatabase(self.databaseEdit.text()), self.versionCombo.currentText(), self.epsg)
@pyqtSlot(bool)
def on_saveButton_clicked(self):
if self.createDatabase(self.databaseEdit.text()):
self.storeConnectionConfiguration(self.serversCombo.currentText(), self.databaseEdit.text())
self.done(1)
else:
self.done(0)
@pyqtSlot(bool)
def on_cancelButton_clicked(self):
self.done(-1)
@pyqtSlot(bool)
def on_srsButton_clicked(self):
projSelector = QgsGenericProjectionSelector()
message = 'Select the Spatial Reference System!'
projSelector.setMessage(theMessage=message)
projSelector.exec_()
try:
self.epsg = int(projSelector.selectedAuthId().split(':')[-1])
srs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if srs:
self.srsEdit.setText(srs.description())
else:
self.epsg = 4326
except:
QMessageBox.warning(self, self.tr("Warning!"), self.tr(message))
def createDatabase(self, name):
sql = self.gen.getCreateDatabase(name)
db = self.getDatabase()
#creating the database
query = QSqlQuery(db)
if not query.exec_(sql):
QMessageBox.warning(self, self.tr("Warning!"), query.lastError().text())
db.close()
return False
db.close()
return True
def getDatabase(self, database = 'postgres'):
(host, port, user, password) = self.getServerConfiguration(self.serversCombo.currentText())
db = QSqlDatabase("QPSQL")
db.setDatabaseName(database)
db.setHostName(host)
db.setPort(int(port))
db.setUserName(user)
db.setPassword(password)
if not db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return db
def updateConnectionName(self):
server = self.serversCombo.currentText()
database = self.databaseEdit.text()
name = server+'_'+database
self.connectionEdit.setText(name)
def on_serversCombo_currentIndexChanged(self, index):
self.updateConnectionName()
def on_databaseEdit_textEdited(self, text):
text = text.lower()
self.databaseEdit.setText(text)
self.updateConnectionName()
def checkFields(self):
if self.serversCombo.currentText() == '' or self.databaseEdit.text() == '' \
or self.srsEdit.text() == '':
return False
return True
def getServerConfiguration(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers/'+name)
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, user, password)
def storeConnectionConfiguration(self, server, database):
name = self.connectionEdit.text()
(host, port, user, password) = self.getServerConfiguration(server)
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+name)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
def getServers(self):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers')
currentConnections = settings.childGroups()
settings.endGroup()
return currentConnections
def populateServersCombo(self):
self.serversCombo.clear()
currentConnections = self.getServers()
for connection in currentConnections:
self.serversCombo.addItem(connection)
class PostgisDBTool(QDialog, FORM_CLASS):
def __init__(self, iface):
"""Constructor."""
super(PostgisDBTool, self).__init__()
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
self.populateServersCombo()
self.srs = None
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.epsg = 4326
def getParameters(self):
return (self.getDatabase(self.databaseEdit.text()), self.versionCombo.currentText(), self.epsg)
@pyqtSlot(bool)
def on_saveButton_clicked(self):
if self.createDatabase(self.databaseEdit.text()):
self.storeConnectionConfiguration(self.serversCombo.currentText(), self.databaseEdit.text())
self.done(1)
else:
self.done(0)
@pyqtSlot(bool)
def on_cancelButton_clicked(self):
self.done(-1)
@pyqtSlot(bool)
def on_configureServerButton_clicked(self):
dlg = ViewServers(self.iface)
dlg.show()
result = dlg.exec_()
self.populateServersCombo()
@pyqtSlot(bool)
def on_srsButton_clicked(self):
projSelector = QgsGenericProjectionSelector()
message = 'Select the Spatial Reference System!'
projSelector.setMessage(theMessage=message)
projSelector.exec_()
try:
self.epsg = int(projSelector.selectedAuthId().split(':')[-1])
srs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if srs:
self.srsEdit.setText(srs.description())
else:
self.epsg = 4326
except:
QMessageBox.warning(self, self.tr("Warning!"), self.tr(message))
def createDatabase(self, name):
sql = self.gen.getCreateDatabase(name)
db = self.getDatabase()
#creating the database
query = QSqlQuery(db)
if not query.exec_(sql):
QMessageBox.warning(self, self.tr("Warning!"), query.lastError().text())
db.close()
return False
db.close()
return True
def getDatabase(self, database = 'postgres'):
(host, port, user, password) = self.getServerConfiguration(self.serversCombo.currentText())
db = QSqlDatabase("QPSQL")
db.setConnectOptions('connect_timeout=10')
db.setDatabaseName(database)
db.setHostName(host)
db.setPort(int(port))
db.setUserName(user)
if password == '':
conInfo = 'host='+host+' port='+port+' dbname='+database
self.setCredentials(db, conInfo, user)
else:
db.setPassword(password)
if not db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return db
def setCredentials(self, db, conInfo, user):
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
else:
db.setPassword(password)
if not db.open():
self.setCredentials(db, conInfo, user)
else:
QgsCredentials.instance().put(conInfo, user, password)
def updateConnectionName(self):
server = self.serversCombo.currentText()
database = self.databaseEdit.text()
name = server+'_'+database
self.connectionEdit.setText(name)
def on_serversCombo_currentIndexChanged(self, index):
self.updateConnectionName()
def on_databaseEdit_textEdited(self, text):
text = text.lower()
self.databaseEdit.setText(text)
self.updateConnectionName()
def checkFields(self):
if self.serversCombo.currentText() == '' or self.databaseEdit.text() == '' or self.srsEdit.text() == '':
return False
return True
def getServerConfiguration(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers/'+name)
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (host, port, user, password)
def storeConnectionConfiguration(self, server, database):
name = self.connectionEdit.text()
(host, port, user, password) = self.getServerConfiguration(server)
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+name)
settings.setValue('database', database)
settings.setValue('host', host)
settings.setValue('port', port)
settings.setValue('username', user)
settings.setValue('password', password)
settings.endGroup()
def getServers(self):
settings = QSettings()
settings.beginGroup('PostgreSQL/servers')
currentConnections = settings.childGroups()
settings.endGroup()
return currentConnections
def populateServersCombo(self):
self.serversCombo.clear()
currentConnections = self.getServers()
for connection in currentConnections:
self.serversCombo.addItem(connection)
class PostgisDbThread(GenericThread):
def __init__(self, parent=None):
"""
Constructor.
"""
super(PostgisDbThread, self).__init__()
self.factory = SqlGeneratorFactory()
#setting the sql generator
self.gen = self.factory.createSqlGenerator(False)
self.messenger = PostgisDbMessages(self)
self.dbFactory = DbFactory()
self.parent = parent
def setParameters(self, abstractDb, dbName, version, epsg, stopped):
"""
Sets thread parameters
"""
self.abstractDb = abstractDb #database = postgis
self.dbName = dbName
self.db = None
self.version = version
self.epsg = epsg
self.stopped = stopped
def run(self):
"""
Runs the process
"""
# Processing ending
(ret, msg) = self.createDatabaseStructure()
self.signals.processingFinished.emit(ret, msg, self.getId())
def connectToTemplate(self, setInnerDb=True):
"""
Connects to the template database to speed up database creation
:return:
"""
database = self.abstractDb.getTemplateName(self.version)
host = self.abstractDb.db.hostName()
port = self.abstractDb.db.port()
user = self.abstractDb.db.userName()
password = self.abstractDb.db.password()
template = self.dbFactory.createDbFactory('QPSQL')
template.connectDatabaseWithParameters(host, port, database, user,
password)
template.checkAndOpenDb()
if setInnerDb:
self.db = template.db
return template
def createDatabaseStructure(self):
"""
Creates database structure according to the selected edgv version
"""
currentPath = os.path.dirname(__file__)
currentPath = os.path.join(currentPath, '..', '..', 'DbTools',
'PostGISTool')
if self.version == '2.1.3':
edgvPath = os.path.join(currentPath, 'sqls', '213', 'edgv213.sql')
elif self.version == '3.0':
edgvPath = os.path.join(currentPath, 'sqls', '3', 'edgv3.sql')
elif self.version == 'FTer_2a_Ed':
edgvPath = os.path.join(currentPath, 'sqls', 'FTer_2a_Ed',
'edgvFter_2a_Ed.sql')
else:
pass
return self.loadDatabaseStructure(edgvPath)
def loadDatabaseStructure(self, edgvPath):
"""
Loads the database structure
edgvPath: path to the databse sql
"""
commands = []
hasTemplate = self.abstractDb.checkTemplate(self.version)
if hasTemplate:
templateDb = self.connectToTemplate(setInnerDb=False)
mustUpdateTemplate = templateDb.checkTemplateImplementationVersion(
)
if mustUpdateTemplate:
templateName = templateDb.db.databaseName()
templateDb.__del__()
self.abstractDb.dropDatabase(templateName, dropTemplate=True)
hasTemplate = False
if not hasTemplate:
file = codecs.open(edgvPath, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[epsg]', '4674')
file.close()
commands = [i for i in sql.split('#') if i != '']
# Progress bar steps calculated
self.signals.rangeCalculated.emit(len(commands) + 4, self.getId())
if not hasTemplate:
try:
self.abstractDb.createTemplateDatabase(self.version)
self.signals.stepProcessed.emit(self.getId())
self.connectToTemplate()
self.signals.stepProcessed.emit(self.getId())
except Exception as e:
return (0, self.messenger.getProblemFeedbackMessage() + '\n' +
':'.join(e.args))
self.db.open()
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not self.stopped[0]:
if not query.exec_(command):
QgsMessageLog.logMessage(
self.messenger.getProblemMessage(command, query),
"DSG Tools Plugin", QgsMessageLog.CRITICAL)
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
return (0, self.messenger.getProblemFeedbackMessage())
# Updating progress
self.signals.stepProcessed.emit(self.getId())
else:
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
QgsMessageLog.logMessage(
self.messenger.getUserCanceledFeedbackMessage(),
"DSG Tools Plugin", QgsMessageLog.INFO)
return (-1,
self.messenger.getUserCanceledFeedbackMessage())
self.db.commit()
if self.version == '2.1.3':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName(
)
elif self.version == 'FTer_2a_Ed':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName(
)
elif self.version == '3.0':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'edgv\',\'complexos\',\'dominios\';' % self.db.databaseName(
)
if sql:
if not query.exec_(sql):
QgsMessageLog.logMessage(
self.messenger.getProblemMessage(command, query),
"DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
#this commit was missing, so alter database statement was not commited.
self.db.commit()
self.db.close()
self.abstractDb.setDbAsTemplate(self.version)
#creates from template
if not self.stopped[0]:
templateName = self.abstractDb.getTemplateName(self.version)
self.abstractDb.createDbFromTemplate(self.dbName,
templateName,
parentWidget=self.parent)
self.signals.stepProcessed.emit(self.getId())
#5. alter spatial structure
createdDb = self.dbFactory.createDbFactory('QPSQL')
createdDb.connectDatabaseWithParameters(
self.abstractDb.db.hostName(), self.abstractDb.db.port(),
self.dbName, self.abstractDb.db.userName(),
self.abstractDb.db.password())
errorTuple = createdDb.updateDbSRID(self.epsg,
parentWidget=self.parent,
threading=True)
# if an error occur during the thread we should pass the message to the main thread
if errorTuple:
QgsMessageLog.logMessage(
self.messenger.getProblemMessage(errorTuple[0],
errorTuple[1]),
"DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
self.signals.stepProcessed.emit(self.getId())
else:
QgsMessageLog.logMessage(
self.messenger.getUserCanceledFeedbackMessage(),
"DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(),
"DSG Tools Plugin", QgsMessageLog.INFO)
return (1, self.messenger.getSuccessFeedbackMessage())
def dropDatabase(self, db):
"""
Drops the created database case a problem occurs during database creation
db: QSqlDatabase to be dropped
"""
host = db.hostName()
port = db.port()
user = db.userName()
password = db.password()
database = 'postgres'
pgDB = QSqlDatabase('QPSQL')
pgDB.setHostName(host)
pgDB.setPort(port)
pgDB.setUserName(user)
pgDB.setPassword(password)
pgDB.setDatabaseName(database)
if not pgDB.open():
return False
sql = self.gen.dropDatabase(db.databaseName())
query = QSqlQuery(pgDB)
return query.exec_(sql)
class ComplexWindow(QtGui.QDockWidget, FORM_CLASS):
def __init__(self, iface, parent=None):
"""Constructor."""
super(ComplexWindow, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
#self.enderecoLine.setText('186.228.51.52')
#self.portaLine.setText('2101'
self.iface = iface
QObject.connect(self.dbButton, SIGNAL(("clicked()")), self.getDataSources)
QObject.connect(self.dbCombo, SIGNAL("activated(int)"), self.updateComplexClass)
QObject.connect(self.complexCombo, SIGNAL("activated(int)"), self.loadAssociatedFeatures)
QObject.connect(self.iface, SIGNAL("newProjectCreated()"), self.clearDock)
self.db = None
self.databases = None
self.factory = SqlGeneratorFactory()
self.gen = None
def __del__(self):
if self.db:
self.db.close()
self.db = None
def clearDock(self):
self.treeWidget.clear()
self.dbCombo.clear()
self.complexCombo.clear()
def isSpatialiteDatabase(self, dbName):
(dataSourceUri, credentials) = self.databases[dbName]
if dataSourceUri.host() == "":
return True
return False
def getUserCredentials(self, lyr):
dataSourceUri = QgsDataSourceURI( lyr.dataProvider().dataSourceUri() )
if dataSourceUri.host() == "":
return (None, None)
connInfo = dataSourceUri.connectionInfo()
(success, user, passwd ) = QgsCredentials.instance().get( connInfo, None, None )
# Put the credentials back (for yourself and the provider), as QGIS removes it when you "get" it
if success:
QgsCredentials.instance().put( connInfo, user, passwd )
return (user, passwd)
def updateComplexClass(self):
if self.db:
self.db.close()
self.db = None
if self.dbCombo.currentIndex() == 0:
return
dbName = self.dbCombo.currentText()
#getting the sql generator
self.gen = self.factory.createSqlGenerator(self.isSpatialiteDatabase(dbName))
(dataSourceUri, credentials) = self.databases[dbName]
#verifying the connection type
if self.isSpatialiteDatabase(dbName):
self.db = QSqlDatabase("QSQLITE")
self.db.setDatabaseName(dbName)
else:
self.db = QSqlDatabase("QPSQL")
self.db.setDatabaseName(dbName)
self.db.setHostName(dataSourceUri.host())
self.db.setPort(int(dataSourceUri.port()))
self.db.setUserName(credentials[0])
self.db.setPassword(credentials[1])
if not self.db.open():
print self.db.lastError().text()
self.populateComboBox()
def populateComboBox(self):
#getting all complex tables
self.complexCombo.clear()
self.complexCombo.addItem(self.tr("select a complex class"))
dbName = self.dbCombo.currentText()
(dataSourceUri, credentials) = self.databases[dbName]
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
self.complexCombo.addItem(query.value(0))
def getDataSources(self):
self.dbCombo.clear()
self.dbCombo.addItem(self.tr("select a database"))
if self.databases:
self.databases.clear()
#dictionary of names and datasourceUri
self.databases = dict()
self.layers = self.iface.mapCanvas().layers()
for layer in self.layers:
dataSourceUri = QgsDataSourceURI( layer.dataProvider().dataSourceUri() )
dbName = dataSourceUri.database()
if dbName not in self.databases:
self.databases[dbName] = (dataSourceUri,self.getUserCredentials(layer))
#populating the combo
self.dbCombo.addItem(dbName)
@pyqtSlot(bool)
def on_managePushButton_clicked(self):
#opens a dialog to manage complexes
if self.isSpatialiteDatabase(self.dbCombo.currentText()):
self.dlg = ManageComplexDialog(self.iface, self.db, self.complexCombo.currentText())
else:
self.dlg = ManageComplexDialog(self.iface, self.db, 'complexos.'+self.complexCombo.currentText())
#connects a signal to update the tree widget when done
QObject.connect(self.dlg, SIGNAL(("tableUpdated()")), self.loadAssociatedFeatures)
#connects a signal to disassociate features from complex before removal
QObject.connect(self.dlg, SIGNAL(("markedToRemove( PyQt_PyObject )")), self.disassociateFeatures)
result = self.dlg.exec_()
if result:
pass
@pyqtSlot(bool)
def on_associatePushButton_clicked(self):
self.associateFeatures()
@pyqtSlot(bool)
def on_zoomButton_clicked(self):
#case no item is selected we should warn the user
if len(self.treeWidget.selectedItems()) == 0:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Please, select an item to zoom."))
return
item = self.treeWidget.selectedItems()[0]
#checking if the item is a complex (it should have depth = 2)
if self.depth(item) == 2:
bbox = QgsRectangle()
for i in range(item.childCount()):
aggregated_item = item.child(i)
aggregated_class = aggregated_item.text(0)
#getting the layer the needs to be updated
aggregated_layer = None
layers = self.iface.mapCanvas().layers()
for layer in layers:
if layer.name() == aggregated_class:
aggregated_layer = layer
break
if not aggregated_layer:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("The associated classes must be loaded in the table of contents."))
return
for j in range(aggregated_item.childCount()):
id = aggregated_item.child(j).text(0)
freq = QgsFeatureRequest()
freq.setFilterFid(int(id))
feature = layer.getFeatures( freq ).next()
if j==0:
bbox=feature.geometry().boundingBox()
bbox.combineExtentWith(feature.geometry().boundingBox())
self.iface.mapCanvas().setExtent(bbox)
self.iface.mapCanvas().refresh()
else:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Select a complex."))
return
def getAdjustedComplexName(self, dbName, complex):
if self.isSpatialiteDatabase(dbName):
complex = '\''+complex.replace("complexos_","")+'\''
else:
complex = '\''+complex+'\''
return complex
def disassociateFeatures(self, toBeRemoved):
for uuid in toBeRemoved:
items = self.treeWidget.findItems(uuid, Qt.MatchRecursive, 1)
if len(items) == 0:
return
complexItem = items[0]
count = complexItem.childCount()
for i in range(count):
self.disassociateAggregatedClass(complexItem.child(i))
def disassociateAggregatedClass(self, item):
aggregated_class = item.text(0)
uuid = item.parent().text(1)
complex = item.parent().parent().text(0)
complex = self.getAdjustedComplexName(self.dbCombo.currentText(), complex)
link_column = self.obtainLinkColumn(complex, aggregated_class)
#getting the layer the needs to be updated
aggregated_layer = None
layers = self.iface.mapCanvas().layers()
for layer in layers:
if layer.name() == aggregated_class:
aggregated_layer = layer
break
if not aggregated_layer:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("The class you're trying to disassociate must loaded in the table of contents."))
return
for i in range(item.childCount()):
#feature id that will be updated
id = item.child(i).text(0)
self.updateLayerOnDisassociate(layer, aggregated_class, link_column, id)
def disassociateAggregatedId(self, item):
aggregated_class = item.parent().text(0)
uuid = item.parent().parent().text(1)
complex = item.parent().parent().parent().text(0)
complex = self.getAdjustedComplexName(self.dbCombo.currentText(), complex)
link_column = self.obtainLinkColumn(complex, aggregated_class)
#getting the layer the needs to be updated
aggregated_layer = None
layers = self.iface.mapCanvas().layers()
for layer in layers:
if layer.name() == aggregated_class:
aggregated_layer = layer
break
if not aggregated_layer:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("The class you're trying to disassociate must loaded in the table of contents."))
return
#feature id that will be updated
id = item.text(0)
self.updateLayerOnDisassociate(layer, aggregated_class, link_column, id)
def updateLayerOnDisassociate(self, layer, aggregated_class, link_column, id):
if self.isComplexClass(aggregated_class):
sql = self.gen.disassociateComplexFromComplex(aggregated_class, link_column, id)
query = QSqlQuery(sql, self.db)
else:
#field index that will be set to NULL
fieldIndex = [i for i in range(len(layer.dataProvider().fields())) if layer.dataProvider().fields()[i].name() == link_column]
#attribute pair that will be changed
attrs = {fieldIndex[0]:None}
#actual update in the database
layer.dataProvider().changeAttributeValues({int(id):attrs})
def isComplexClass(self, className):
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == className:
return True
return False
@pyqtSlot(bool)
def on_disassociatePushButton_clicked(self):
#case no item is selected we should warn the user
if len(self.treeWidget.selectedItems()) == 0:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Please, select an aggregated class or aggregated id."))
return
item = self.treeWidget.selectedItems()[0]
#checking if the item is a complex (it should have depth = 2)
if self.depth(item) == 3:
self.disassociateAggregatedClass(item)
elif self.depth(item) == 4:
self.disassociateAggregatedId(item)
else:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Please, select an aggregated class or aggregated id."))
return
self.loadAssociatedFeatures()
def loadAssociatedFeatures(self):
self.treeWidget.clear()
if self.complexCombo.currentIndex() == 0:
return
complex = self.complexCombo.currentText()
complex = self.getAdjustedComplexName(self.dbCombo.currentText(), complex)
#query to get the possible links to the selected complex in the combobox
sql = self.gen.getComplexLinks(complex)
query = QSqlQuery(sql, self.db)
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)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
while complexQuery.next():
complex_uuid = complexQuery.value(0)
name = complexQuery.value(1)
if not (complex_uuid and name):
continue
#adding the information in the tree widget case there are no associated features
if self.isSpatialiteDatabase(self.dbCombo.currentText()):
self.addAssociatedFeature(complex_schema+"_"+complex, name, complex_uuid, None, None)
else:
self.addAssociatedFeature(complex, name, complex_uuid, None, None)
#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)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
#adding the information in the tree widget
if self.isSpatialiteDatabase(self.dbCombo.currentText()):
self.addAssociatedFeature(str(complex_schema+"_"+complex), str(name), complex_uuid, str(aggregated_schema+"_"+aggregated_class), ogc_fid)
else:
self.addAssociatedFeature(str(complex), str(name), complex_uuid, str(aggregated_class), ogc_fid)
def depth(self, item):
#calculates the depth of the item
depth = 0
while item is not None:
item = item.parent()
depth += 1
return depth
def obtainLinkColumn(self, complexClass, aggregatedClass):
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass, aggregatedClass)
query = QSqlQuery(sql, self.db)
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def associateFeatures(self):
#case no item is selected we should warn the user
if len(self.treeWidget.selectedItems()) == 0:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Please, select a complex."))
return
item = self.treeWidget.selectedItems()[0]
#checking if the item is a complex (it should have depth = 2)
if self.depth(item) != 2:
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), self.tr("Please, select a complex."))
return
complex = self.complexCombo.currentText()
#surrounding the name with ''
complex = self.getAdjustedComplexName(self.dbCombo.currentText(), complex)
#uuid to be adjust on the selected features
uuid = item.text(1)
#getting the selected features
forbiddenLayers = []
self.layers = self.iface.mapCanvas().layers()
for layer in self.layers:
#case no fetures selected we proceed to the next one
selectedFeatures = layer.selectedFeatures()
if len(selectedFeatures) == 0:
continue
#obtaining the link column
column_name = self.obtainLinkColumn(complex, layer.name())
#storing the names of the incompatible layers
if column_name == "":
forbiddenLayers.append(layer.name())
continue
for feature in selectedFeatures:
fieldIndex = [i for i in range(len(layer.dataProvider().fields())) if layer.dataProvider().fields()[i].name() == column_name]
#feature id that will be updated
id = feature.id()
#attribute pair that will be changed
attrs = {fieldIndex[0]:uuid}
#actual update in the database
layer.dataProvider().changeAttributeValues({id:attrs})
#show the message of incompatible classes to associate
if len(forbiddenLayers) > 0:
message = ""
message += self.tr("The following layers cannot be associated to complexes from ")+self.complexCombo.currentText()+":\n"
for text in forbiddenLayers:
message += text+"\n"
QMessageBox.warning(self.iface.mainWindow(), self.tr("Warning!"), message)
#updating the tree widget
self.loadAssociatedFeatures()
def createTreeItem(self, parent, text, uuid = ""):
count = parent.childCount()
children = []
#making a list of item names
for i in range(count):
child = parent.child(i)
children.append(child.text(0))
#checking if the text is already in the tree widget
if text not in children:
#case not it should be created
item = QTreeWidgetItem(parent)
item.setExpanded(True)
item.setText(0,text)
#adding the complex uuid to the tree widget
if uuid != "":
item.setText(1, str(uuid))
else:
#case already exists the correspondind item should be returned
for i in range(count):
child = parent.child(i)
if child.text(0) == text:
item = child
return item
def addAssociatedFeature(self, className, complexName, complexId, associatedClass, associatedId):
#get the corresponding top level item
classNameItem = self.createTreeItem(self.treeWidget.invisibleRootItem(), className)
#get the corresponding complex item
complexNameItem = self.createTreeItem(classNameItem, complexName, complexId)
if associatedClass and associatedId:
#get the corresponding class item
associatedClassItem = self.createTreeItem(complexNameItem, associatedClass)
#creates the corresponding associated item
self.createTreeItem(associatedClassItem, str(associatedId))
def __test(self, x):
if (x.parent() == None) :
return True
else:
return False
class SpatialiteDb(AbstractDb):
def __init__(self):
super(SpatialiteDb, self).__init__()
self.db = QSqlDatabase('QSQLITE')
self.gen = SqlGeneratorFactory().createSqlGenerator(True)
def getDatabaseName(self):
return self.db.databaseName().split('.sqlite')[0].split('/')[-1]
def connectDatabase(self, conn=None):
if conn is None:
self.connectDatabaseWithGui()
else:
self.db.setDatabaseName(conn)
def connectDatabaseWithGui(self):
fd = QFileDialog()
filename = fd.getOpenFileName(
caption=self.tr('Select a DSGTools Spatialite file'),
filter=self.tr('Spatialite file databases (*.sqlite)'))
self.db.setDatabaseName(filename)
def connectDatabaseWithQSettings(self, name):
return None
def connectDatabaseWithParameters(self, host, port, database, user,
password):
return None
def listGeomClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def listComplexClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def getConnectionFromQSettings(self, conName):
return None
def storeConnection(self, server):
return None
def getServerConfiguration(self, name):
return None
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
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 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 classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def makeOgrConn(self):
constring = self.db.databaseName()
return constring
def getNotNullDict(self):
return None
def getDomainDict(self):
return None
def validateWithOutputDatabaseSchema(self, outputAbstractDb):
try:
self.checkAndOpenDb()
except:
return dict()
invalidated = self.buildInvalidatedDict()
inputdbStructure = self.getStructureDict()
outputdbStructure = outputAbstractDb.getStructureDict()
domainDict = outputAbstractDb.getDomainDict()
classes = self.listClassesWithElementsFromDatabase()
notNullDict = outputAbstractDb.getNotNullDict()
for inputClass in classes.keys():
outputClass = self.translateAbstractDbLayerNameToOutputFormat(
inputClass, outputAbstractDb)
(schema, className) = self.getTableSchema(inputClass)
if outputClass in outputdbStructure.keys():
outputAttrList = self.reorderTupleList(
outputdbStructure[outputClass].keys())
inputAttrList = self.reorderTupleList(
inputdbStructure[inputClass].keys())
sql = self.gen.getFeaturesWithSQL(inputClass, inputAttrList)
query = QSqlQuery(sql, self.db)
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 invalidated['nullLine'].keys():
invalidated['nullLine'][inputClass] = 0
invalidated['nullLine'][inputClass] += 1
#validates pks
if id == None and (not nullLine):
if cl not in 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 domainDict.keys():
if inputAttrList[i] in 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 notNullDict.keys():
if outputClass in domainDict.keys():
if inputAttrList[i] in notNullDict[
outputClass] and inputAttrList[
i] not in domainDict[
outputClass].keys():
if (value == None) and (not nullLine) and (
inputAttrList[i] not in
domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute', inputClass,
id, inputAttrList[i]
], value)
else:
if inputAttrList[i] in notNullDict[
outputClass]:
if (value == None) and (not nullLine) and (
inputAttrList[i] not in
domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute', inputClass,
id, inputAttrList[i]
], value)
if outputClass in domainDict.keys():
if (inputAttrList[i] not in [
'geom', 'GEOMETRY', 'id', 'OGC_FID'
] and schema <> 'complexos') or (
schema == 'complexos'
and inputAttrList[i] <> 'id'):
if inputAttrList[i] not in outputdbStructure[
outputClass].keys():
invalidated = self.utils.buildNestedDict(
invalidated, [
'attributeNotFoundInOutput',
inputClass
], [inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def translateAbstractDbLayerNameToOutputFormat(self, lyr,
outputAbstractDb):
if outputAbstractDb.db.driverName() == 'QSQLITE':
return lyr
if outputAbstractDb.db.driverName() == 'QPSQL':
return str(lyr.split('_')[0] + '.' + '_'.join(lyr.split('_')[1::]))
def translateOGRLayerNameToOutputFormat(self, lyr, ogrOutput):
if ogrOutput.GetDriver().name == 'SQLite':
return lyr
if ogrOutput.GetDriver().name == 'PostgreSQL':
return str(lyr.split('_')[0] + '.' + '_'.join(lyr.split('_')[1::]))
def getTableSchema(self, lyr):
schema = lyr.split('_')[0]
className = '_'.join(lyr.split('_')[1::])
return (schema, className)
def convertToPostgis(self, outputAbstractDb, type=None):
try:
self.checkAndOpenDb()
except:
return False
(inputOgrDb, outputOgrDb, fieldMap, inputLayerList,
errorDict) = self.prepareForConversion(outputAbstractDb)
invalidated = self.validateWithOutputDatabaseSchema(outputAbstractDb)
hasErrors = self.makeValidationSummary(invalidated)
if type == 'untouchedData':
if hasErrors:
self.signals.updateLog.emit('\n\n\n' + self.tr(
'Conversion not perfomed due to validation errors! Check log above for more information.'
))
return False
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict)
return status
if type == 'fixData':
if hasErrors:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict,
invalidated)
return status
else:
status = self.translateDS(inputOgrDb, outputOgrDb, fieldMap,
inputLayerList, errorDict)
return status
return False
def convertToSpatialite(self, outputAbstractDb, type=None):
return None
def getDatabaseVersion(self):
try:
self.checkAndOpenDb()
except:
return None
version = '2.1.3'
try:
sqlVersion = self.gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, self.db)
while queryVersion.next():
version = queryVersion.value(0)
except:
version = '2.1.3'
return version
def obtainLinkColumn(self, complexClass, aggregatedClass):
try:
self.checkAndOpenDb()
except:
return ''
#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)
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def loadAssociatedFeatures(self, complex):
try:
self.checkAndOpenDb()
except:
return dict()
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)
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)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
while complexQuery.next():
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)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class],
[ogc_fid])
return associatedDict
def isComplexClass(self, className):
try:
self.checkAndOpenDb()
except:
return False
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == 'complexos_' + className:
return True
return False
def disassociateComplexFromComplex(self, aggregated_class, link_column,
id):
sql = self.gen.disassociateComplexFromComplex(
'complexos_' + aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(
self.tr('Problem disassociating complex from complex: ') +
'\n' + query.lastError().text())
def getUsers(self):
return None
def getUserRelatedRoles(self, username):
return None
def getRoles(self):
return None
def createRole(self, role, dict):
pass
def dropRole(self, role):
pass
def alterUserPass(self, user, newpassword):
pass
def createUser(self, user, password, isSuperUser):
pass
def removeUser(self, user):
pass
def grantRole(self, user, role):
pass
def revokeRole(self, user, role):
pass
def getTablesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
#table name
ret.append(query.value(0))
return ret
def getRolePrivileges(self, role, dbname):
return None
def getFrameLayerName(self):
return 'public_aux_moldura_a'
def getEDGVDbsFromServer(self, name):
return None
def getDbsFromServer(self):
return None
def checkSuperUser(self):
return None
def dropDatabase(self, abstractCandidate):
return None
def createResolvedDomainViews(self, createViewClause, fromClause):
pass
def getSqlViewFile(self):
pass
class CreateInomDialog(QtGui.QDialog, FORM_CLASS):
def __init__(self, iface, parent=None):
"""Constructor."""
super(CreateInomDialog, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.iface = iface
#Sql factory generator
self.isSpatialite = True
self.tabWidget.setCurrentIndex(0)
self.factory = SqlGeneratorFactory()
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
QObject.connect(self.tabWidget, SIGNAL(("currentChanged(int)")), self.restoreInitialState)
QObject.connect(self.pushButtonOpenFile, SIGNAL(("clicked()")), self.loadDatabase)
self.restoreInitialState()
self.db = None
#populating the postgis combobox
self.populatePostGISConnectionsCombo()
self.map_index = UtmGrid()
self.disableAll()
self.setValidCharacters()
self.setMask()
def __del__(self):
self.closeDatabase()
@pyqtSlot()
def on_okButton_clicked(self):
if not self.dbLoaded:
QMessageBox.warning(self, self.tr("Warning!"), self.tr('Please, select a database first.'))
return
if not self.validateMI():
QMessageBox.warning(self, self.tr("Warning!"), self.tr('Map name index not valid!'))
return
frame = self.map_index.getQgsPolygonFrame(self.inomLineEdit.text())
reprojected = self.reprojectFrame(frame)
self.insertFrameIntoLayer(reprojected)
self.done(1)
def insertFrameIntoLayer(self,reprojected):
self.utils = Utils()
self.dbVersion = self.utils.getDatabaseVersion(self.db)
self.qmlPath = self.utils.getQmlDir(self.db)
layer = self.getFrameLayer()
if not layer:
return
layer.startEditing()
feat = QgsFeature()
feat.setFields(layer.dataProvider().fields())
feat.setGeometry(reprojected)
feat.setAttribute(2, self.inomLineEdit.text())
feat.setAttribute(3, self.scaleCombo.currentText())
layer.addFeatures([feat], makeSelected=True)
layer.commitChanges()
bbox = reprojected.boundingBox()
for feature in layer.getFeatures():
bbox.combineExtentWith(feature.geometry().boundingBox())
bbox = self.iface.mapCanvas().mapSettings().layerToMapCoordinates(layer, bbox)
self.iface.mapCanvas().setExtent(bbox)
self.iface.mapCanvas().refresh()
def getFrameLayer(self):
for lyr in self.iface.legendInterface().layers():
if lyr.name() == 'public_aux_moldura_a' or lyr.name() == 'aux_moldura_a':
dbname = self.getDBNameFromLayer(lyr)
if self.isSpatialite and dbname == self.filename:
return lyr
if not self.isSpatialite:
(database, host, port, user, password) = self.utils.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
if dbname == database:
return lyr
if self.isSpatialite:
return self.loadSpatialiteFrame()
else:
return self.loadPostGISFrame()
return None
def getDBNameFromLayer(self, lyr):
dbname = None
splitUri = lyr.dataProvider().dataSourceUri().split(' ')
if len(splitUri) > 0:
dbsplit = splitUri[0].split('=')
if len(dbsplit) > 1 and dbsplit[0] == 'dbname':
dbnameInString = dbsplit[1]
dbnameSplit = dbnameInString.split('\'')
if len(dbnameSplit) > 1:
dbname = dbnameSplit[1]
return dbname
def loadPostGISFrame(self):
self.selectedClasses = ['public.aux_moldura_a']
(database, host, port, user, password) = self.utils.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
uri = QgsDataSourceURI()
uri.setConnection(str(host),str(port), str(database), str(user), str(password))
if len(self.selectedClasses)>0:
try:
geom_column = 'geom'
for layer in self.selectedClasses:
split = layer.split('.')
schema = split[0]
layerName = split[1]
sql = self.gen.loadLayerFromDatabase(layer)
uri.setDataSource(schema, layerName, geom_column, sql,'id')
uri.disableSelectAtId(True)
return self.loadEDGVLayer(uri, layerName, 'postgres')
except:
self.bar.pushMessage(self.tr("Error!"), self.tr("Could not load the selected frame!"), level=QgsMessageBar.CRITICAL)
else:
self.bar.pushMessage(self.tr("Warning!"), self.tr("Please, select at least one class!"), level=QgsMessageBar.WARNING)
def loadSpatialiteFrame(self):
self.selectedClasses = ['public_aux_moldura_a']
uri = QgsDataSourceURI()
uri.setDatabase(self.filename)
schema = ''
geom_column = 'GEOMETRY'
if len(self.selectedClasses)>0:
for layer_name in self.selectedClasses:
uri.setDataSource(schema, layer_name, geom_column)
return self.loadEDGVLayer(uri, layer_name, 'spatialite')
def loadEDGVLayer(self, uri, layer_name, provider):
vlayer = QgsVectorLayer(uri.uri(), layer_name, provider)
vlayer.setCrs(self.crs)
QgsMapLayerRegistry.instance().addMapLayer(vlayer) #added due to api changes
if self.isSpatialite and (self.dbVersion == '3.0' or self.dbVersion == '2.1.3'):
lyr = '_'.join(layer_name.replace('\r','').split('_')[1::])
else:
lyr = layer_name.replace('\r','')
vlayerQml = os.path.join(self.qmlPath, lyr+'.qml')
vlayer.loadNamedStyle(vlayerQml,False)
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
if not vlayer.isValid():
QgsMessageLog.logMessage(vlayer.error().summary(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return None
return vlayer
@pyqtSlot()
def on_cancelButton_clicked(self):
self.done(0)
@pyqtSlot(int)
def on_comboBoxPostgis_currentIndexChanged(self):
if self.comboBoxPostgis.currentIndex() > 0:
self.loadDatabase()
@pyqtSlot(str)
def on_miLineEdit_textChanged(self,s):
if (s!=''):
self.inomen=self.map_index.getINomenFromMI(str(s))
self.inomLineEdit.setText(self.inomen)
@pyqtSlot(str)
def on_mirLineEdit_textChanged(self,s):
if (s!=''):
self.inomen=self.map_index.getINomenFromMIR(str(s))
self.inomLineEdit.setText(self.inomen)
def reprojectFrame(self, poly):
crsSrc = QgsCoordinateReferenceSystem(self.crs.geographicCRSAuthId())
coordinateTransformer = QgsCoordinateTransform(crsSrc, self.crs)
polyline = poly.asMultiPolygon()[0][0]
newPolyline = []
for point in polyline:
newPolyline.append(coordinateTransformer.transform(point))
qgsPolygon = QgsGeometry.fromMultiPolygon([[newPolyline]])
return qgsPolygon
def closeDatabase(self):
if self.db:
self.db.close()
self.db = None
def restoreInitialState(self):
self.filename = ""
self.dbLoaded = False
self.epsg = 0
self.crs = None
self.postGISCrsEdit.setText('')
self.postGISCrsEdit.setReadOnly(True)
self.spatialiteCrsEdit.setText('')
self.spatialiteCrsEdit.setReadOnly(True)
if self.tabWidget.currentIndex() == 0:
self.isSpatialite = True
else:
self.isSpatialite = False
#getting the sql generator according to the database type
self.gen = self.factory.createSqlGenerator(self.isSpatialite)
self.comboBoxPostgis.setCurrentIndex(0)
def setCRS(self):
try:
self.epsg = self.findEPSG()
print self.epsg
if self.epsg == -1:
self.bar.pushMessage("", self.tr("Coordinate Reference System not set or invalid!"), level=QgsMessageBar.WARNING)
else:
self.crs = QgsCoordinateReferenceSystem(self.epsg, QgsCoordinateReferenceSystem.EpsgCrsId)
if self.isSpatialite:
self.spatialiteCrsEdit.setText(self.crs.description())
self.spatialiteCrsEdit.setReadOnly(True)
else:
self.postGISCrsEdit.setText(self.crs.description())
self.postGISCrsEdit.setReadOnly(True)
except:
pass
def loadDatabase(self):
self.closeDatabase()
if self.isSpatialite:
fd = QtGui.QFileDialog()
self.filename = fd.getOpenFileName(filter='*.sqlite')
if self.filename:
self.spatialiteFileEdit.setText(self.filename)
self.db = QSqlDatabase("QSQLITE")
self.db.setDatabaseName(self.filename)
else:
self.db = QSqlDatabase("QPSQL")
(database, host, port, user, password) = self.getPostGISConnectionParameters(self.comboBoxPostgis.currentText())
self.db.setDatabaseName(database)
self.db.setHostName(host)
self.db.setPort(int(port))
self.db.setUserName(user)
self.db.setPassword(password)
try:
if not self.db.open():
print self.db.lastError().text()
else:
self.dbLoaded = True
self.setCRS()
except:
pass
def getPostGISConnectionParameters(self, name):
settings = QSettings()
settings.beginGroup('PostgreSQL/connections/'+name)
database = settings.value('database')
host = settings.value('host')
port = settings.value('port')
user = settings.value('username')
password = settings.value('password')
settings.endGroup()
return (database, host, port, user, password)
def getPostGISConnections(self):
settings = QSettings()
settings.beginGroup('PostgreSQL/connections')
currentConnections = settings.childGroups()
settings.endGroup()
return currentConnections
def populatePostGISConnectionsCombo(self):
self.comboBoxPostgis.clear()
self.comboBoxPostgis.addItem(self.tr("Select Database"))
self.comboBoxPostgis.addItems(self.getPostGISConnections())
def findEPSG(self):
sql = self.gen.getSrid()
query = QSqlQuery(sql, self.db)
srids = []
while query.next():
srids.append(query.value(0))
return srids[0]
def setValidCharacters(self):
self.chars = []
chars = 'NS'
self.chars.append(chars)
chars = 'ABCDEFGHIJKLMNOPQRSTUVZ'
self.chars.append(chars)
chars = ['01','02','03','04','05','06','07','08','09','10',
'11','12','13','14','15','16','17','18','19','20',
'21','22','23','24','25','26','27','28','29','30',
'31','32','33','34','35','36','37','38','39','40',
'41','42','43','44','45','46','47','48','49','50',
'51','52','53','54','55','56','57','58','59','60']
self.chars.append(chars)
chars = 'VXYZ'
self.chars.append(chars)
chars = 'ABCD'
self.chars.append(chars)
chars = ['I','II','III','IV','V','VI']
self.chars.append(chars)
chars = '1234'
self.chars.append(chars)
chars = ['NO','NE','SO','SE']
self.chars.append(chars)
chars = 'ABCDEF'
self.chars.append(chars)
chars = ['I','II','III','IV']
self.chars.append(chars)
chars = '123456'
self.chars.append(chars)
chars = 'ABCD'
self.chars.append(chars)
def setMask(self):
if self.scaleCombo.currentText() == '1000k':
self.inomLineEdit.setInputMask('NN-NN')
elif self.scaleCombo.currentText() == '500k':
self.inomLineEdit.setInputMask('NN-NN-N')
elif self.scaleCombo.currentText() == '250k':
self.inomLineEdit.setInputMask('NN-NN-N-N')
elif self.scaleCombo.currentText() == '100k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn')
elif self.scaleCombo.currentText() == '50k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0')
elif self.scaleCombo.currentText() == '25k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0-NN')
elif self.scaleCombo.currentText() == '10k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0-NN-N')
elif self.scaleCombo.currentText() == '5k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0-NN-N-Nnn')
elif self.scaleCombo.currentText() == '2k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0-NN-N-Nnn-0')
elif self.scaleCombo.currentText() == '1k':
self.inomLineEdit.setInputMask('NN-NN-N-N-Nnn-0-NN-N-Nnn-0-N')
def validateMI(self):
mi = self.inomLineEdit.text()
split = mi.split('-')
for i in range(len(split)):
word = str(split[i])
if len(word) == 0:
return False
if i == 0:
if word[0] not in self.chars[0]:
print word
return False
if word[1] not in self.chars[1]:
print word
return False
elif i == 1:
if word not in self.chars[2]:
print word
return False
elif i == 2:
if word not in self.chars[3]:
print word
return False
elif i == 3:
if word not in self.chars[4]:
print word
return False
elif i == 4:
if word not in self.chars[5]:
print word
return False
elif i == 5:
if word not in self.chars[6]:
print word
return False
elif i == 6:
if word not in self.chars[7]:
print word
return False
elif i == 7:
if word not in self.chars[8]:
print word
return False
elif i == 8:
if word not in self.chars[9]:
print word
return False
elif i == 9:
if word not in self.chars[10]:
print word
return False
elif i == 10:
if word not in self.chars[11]:
print word
return False
return True
def disableAll(self):
self.mirLineEdit.setEnabled(False)
self.miLineEdit.setEnabled(False)
self.inomLineEdit.setEnabled(False)
@pyqtSlot(int)
def on_scaleCombo_currentIndexChanged(self):
self.setMask()
@pyqtSlot(bool)
def on_mirRadioButton_toggled(self, toggled):
if toggled:
self.mirLineEdit.setEnabled(True)
else:
self.mirLineEdit.setEnabled(False)
@pyqtSlot(bool)
def on_miRadioButton_toggled(self, toggled):
if toggled:
self.miLineEdit.setEnabled(True)
else:
self.miLineEdit.setEnabled(False)
@pyqtSlot(bool)
def on_inomRadioButton_toggled(self, toggled):
if toggled:
self.inomLineEdit.setEnabled(True)
else:
self.inomLineEdit.setEnabled(False)