class QvLog(Singleton):
def __init__(self, family='QVISTA', logname='DESKTOP', dbLog=_DB_QVISTA):
if hasattr(self, 'db'): # Solo se inicializa una vez
return
self.family = family.upper()
self.logname = logname.upper()
self.usuari = getpass.getuser().upper()
self.db = self.connexio(dbLog)
self.sessio = str(uuid.uuid1())
self.query = QSqlQuery()
def connexio(self, dbLog):
db = QSqlDatabase.addDatabase(dbLog['Database'])
if db.isValid():
db.setHostName(dbLog['HostName'])
db.setPort(dbLog['Port'])
db.setDatabaseName(dbLog['DatabaseName'])
db.setUserName(dbLog['UserName'])
db.setPassword(dbLog['Password'])
if db.open():
return db
return None
def desconnexio(self):
if self.db is None:
return
conName = self.db.connectionName()
self.db.close()
self.db = None
QSqlDatabase.removeDatabase(conName)
def registre(self, topic, params=None):
if self.db is None or self.query is None:
return False
self.query.prepare(
"CALL QV_LOG_WRITE(:IDUSER, :IDSESSION, :FAMILY, :LOGNAME, :TOPIC, :PARAMS)"
)
self.query.bindValue(':IDUSER', self.usuari)
self.query.bindValue(':IDSESSION', self.sessio)
self.query.bindValue(':FAMILY', self.family)
self.query.bindValue(':LOGNAME', self.logname)
self.query.bindValue(':TOPIC', topic)
self.query.bindValue(':PARAMS', params)
ok = self.query.exec_()
return ok
def inici(self):
ok = self.registre('LOG_INICI')
return ok
def fi(self):
ok = self.registre('LOG_FI')
self.desconnexio()
return ok
def error(self):
if self.db is None or self.query is None:
return None
else:
return self.query.lastError().text()
def execSQLCommand(self, sql, ignore_errors=False):
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
res = query.exec_(sql)
if not ignore_errors:
self.assertTrue(res, sql + ': ' + query.lastError().text())
query.finish()
def getStructureDict(self):
'''
Gets database structure according to the edgv version
'''
self.checkAndOpenDb()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting database structure: ") +
query.lastError().text())
while query.next():
className = str(query.value(0))
classSql = str(query.value(1))
if className.split('_')[0] == 'complexos' or className.split(
'_')[-1] in ['p', 'l', 'a']:
if className not in list(classDict.keys()):
classDict[className] = dict()
classSql = classSql.split(className)[1]
sqlList = classSql.replace('(', '').replace(')', '').replace(
'\"', '').replace('\'', '').split(',')
for s in sqlList:
fieldName = str(s.strip().split(' ')[0])
classDict[className][fieldName] = fieldName
if 'GEOMETRY' in list(classDict[className].keys()):
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in list(classDict[className].keys()):
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in list(classDict[className].keys()):
classDict[className]['OGC_FID'] = 'id'
return classDict
def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True):
"""
Add geom to the layer and check everything is OK
"""
table = layer.dataProvider().uri().table()
# insert geom in layer
self.assertTrue(layer.startEditing())
feature = QgsFeature(layer.fields())
geom = QgsGeometry.fromWkt(wkt)
feature.setAttributes([pk])
feature.setGeometry(geom)
self.assertTrue(layer.addFeature(feature))
self.assertTrue(layer.commitChanges())
if check_valid:
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format(
table, pk)
res = query.exec_(sql)
self.assertTrue(res, sql + ': ' + query.lastError().text())
query.next()
valid = query.value(0)
self.assertTrue(
valid,
"geometry '{}' inserted in database is not valid".format(wkt))
query.finish()
expected_wkt = wkt if wkt_ref is None else wkt_ref
res_wkt = layer.getFeature(pk).geometry().asWkt()
self.assertTrue(
compareWkt(res_wkt, expected_wkt, 0.00001),
"\nactual = {}\nexpected = {}".format(res_wkt, expected_wkt))
def getDbsFromServer(self, name):
"""
Gets server databases
name: server name
"""
gen = self.factory.createSqlGenerator(driver=DsgEnums.DriverPostGIS)
(host, port, user, password) = self.getServerConfiguration(name)
database = 'postgres'
postgisDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS)
postgisDb.connectDatabaseWithParameters(host, port, database, user,
password)
if not postgisDb.db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSGTools Plugin",
Qgis.Critical)
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr('A problem occurred! Check log for details.'))
query = QSqlQuery(gen.getDatabasesFromServer(), postgisDb.db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr("Problem executing query: ") +
query.lastError().text())
dbList = []
while query.next():
dbList.append(query.value(0))
postgisDb.closeDatabase()
return self.browseServer(dbList, host, port, user, password)
def delete_data_in_database(self):
"""Deletes the project (= database schema) in the database.
Returns:
False: If something went wrong with deleting in the database.
Otherwise True.
"""
try:
db = open_psql_db(self.db_host, self.db_name, self.db_port,
self.db_admin, self.db_admin_pwd)
sql = "BEGIN;"
sql += "DROP SCHEMA IF EXISTS %s CASCADE;" % self.db_schema
sql += "DELETE FROM geometry_columns WHERE f_table_schema='%s' ;" % self.db_schema
sql += "COMMIT;"
query = db.exec_(sql)
if not query.isActive():
message = "Error occured while deleting project in database."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
db.close()
del db
return True
except Exception as e:
message = "Something went wrong while deleting the project."
raise VerisoError(message, e)
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 deleteUser(self):
sql = "DELETE FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryDeleteUser = QSqlQuery(self.db)
queryDeleteUser.prepare(sql)
queryDeleteUser.bindValue(":username", self.getUsername())
queryDeleteUser.exec_()
if (queryDeleteUser.lastError().number() == -1):
return True
else:
QgsMessageLog.logMessage(
"Datenbankfehler beim Löschen: " +
queryDeleteUser.lastError().text(), 'PostNAS-Suche',
Qgis.Critical)
return False
def update_project_database_sqlite(self):
"""Deletes the deleted project from the sqlite project database.
Returns:
False: If something went wrong. Otherwise True.
"""
try:
db = get_projects_db()
sql = "DELETE FROM projects WHERE dbschema = '%s';" % (
self.db_schema)
query = db.exec_(sql)
if not query.isActive():
message = "Error while reading from projects database."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
db.close()
del db
return True
except Exception as e:
message = "Something went wrong while updating projects database."
raise VerisoError(message, e)
def delete_data_in_database(self):
"""Deletes the project (= database schema) in the database.
Returns:
False: If something went wrong with deleting in the database.
Otherwise True.
"""
try:
if self.settings.value("options/general/use_pg_projects_database",
False,
type=bool):
db = get_default_db()
else:
db = open_psql_db(self.db_host, self.db_name, self.db_port,
self.db_admin, self.db_admin_pwd)
sql = "BEGIN;"
sql += "DROP SCHEMA IF EXISTS %s CASCADE;" % self.db_schema
sql += "COMMIT;"
query = db.exec_(sql)
if not query.isActive():
message = "Error occured while deleting project in database."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
db.close()
del db
return True
except Exception as e:
message = "Something went wrong while deleting the project."
raise VerisoError(message, e)
def update_projects_database_sqlite(self):
"""Updates the sqlite projects database.
Returns:
False: When there an error occured. Otherswise True.
"""
error_message = ("Something went wrong while updating projects "
"database. You need to delete the database schema "
"manually.")
try:
# Create a new projects database if there is none (copy one from
# the templates).
if self.projects_database == "":
template = get_absolute_path("templates/template_projects.db")
self.projects_database = QDir.toNativeSeparators(
QDir.cleanPath(self.projects_root_directory +
"/projects.db"))
shutil.copyfile(template, self.projects_database)
self.settings.setValue("options/general/projects_database",
self.projects_database)
db = get_projects_db()
project_root_directory = QDir.toNativeSeparators(
QDir.cleanPath(self.projects_root_directory + "/" +
str(self.db_schema)))
values = (self.db_schema, self.db_schema, self.db_host,
self.db_name, self.db_port, self.db_schema, self.db_user,
self.db_pwd, self.db_admin, self.db_admin_pwd, self.epsg,
self.ili, self.app_module, self.app_module_name,
self.projects_root_directory, project_root_directory,
self.data_date, self.notes, self.itf, self.max_scale)
values = """VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
'%s', '%s', 'postgres', '%s', '%s', '%s', '%s', '%s',
'%s', '%s', '%s', '%s', '%s')""" % values
sql = "INSERT INTO projects (id, displayname, dbhost, dbname, " \
"dbport, dbschema, dbuser, dbpwd, dbadmin, dbadminpwd, " \
"provider, epsg, ilimodelname, appmodule, appmodulename, " \
"projectrootdir, projectdir, datadate, notes, itf, " \
"max_scale)" + values
query = db.exec_(sql)
if not query.isActive():
message = "Error while updating projects database."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
db.close()
self.projectsDatabaseHasChanged.emit()
return True
except Exception as e:
raise VerisoError(error_message, e)
def execute_query(self, sql):
query = QSqlQuery(self.db)
res = query.exec_(sql)
if res is False:
QApplication.restoreOverrideCursor()
raise VerisoErrorWithBar(self.iface.messageBar(),
"Error " + (query.lastError().text()))
def 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 updateUser(self, username_old):
if (self.getUsername() != None):
self.__openDB()
sql = "UPDATE postnas_search_access_control SET username = :username, name = :name, access = :access WHERE username = :username_old"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":username", self.getUsername().lower())
query.bindValue(":username_old", username_old)
query.bindValue(":name", self.name)
query.bindValue(":access", self.access)
query.exec_()
if (query.lastError().number() == -1):
return True
else:
QgsMessageLog.logMessage(
"Datenbankfehler beim Update: " + query.lastError().text(),
'PostNAS-Suche', Qgis.Critical)
return False
else:
return False
def executeQuery(self, db, sql, feat=None):
try:
fparams = self.preparesql(feat)
if self.debug:
self.info.log("sql:", sql)
self.info.log("fparams:", fparams)
sqlexe = sql % fparams
if self.debug: self.info.log("SQL execute:", sqlexe)
# if db is not specified, or is invalid, the application's default database is used.
# If query is not an empty string, it will be executed
query = QSqlQuery(db)
# query = db.exec_(sql)#obsolete
query.prepare(sqlexe)
self.result = query.exec_()
if not self.result:
self.info.log(query.lastError().databaseText())
self.info.gtoWarning("Database Error:" +
query.lastError().databaseText())
except Exception as e:
self.info.err(e)
def __createLoggingTable(self):
file_path = os.path.dirname(os.path.realpath(__file__)) + "/create_loggingtable/create_logging_table.sql"
sql = open(file_path).read()
self.__openDB()
query = QSqlQuery(self.db)
query.exec_(sql)
if(query.lastError().number() == -1):
return True
else:
return False
def findEPSG(self, parameters=dict()):
"""
Finds the database EPSG
"""
self.checkAndOpenDb()
sql = self.gen.getSrid(parameters=parameters)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem finding EPSG: ") + query.lastError().text())
srid = -1
while query.next():
srid = query.value(0)
break
return srid
def checkUserExists(self):
sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryCheckUserExists = QSqlQuery(self.db)
queryCheckUserExists.prepare(sql)
queryCheckUserExists.bindValue(":username", self.getUsername())
queryCheckUserExists.exec_()
if (queryCheckUserExists.lastError().number() == -1):
if (queryCheckUserExists.size() > 0):
return True
else:
return False
else:
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 insertUser(self):
if (self.getUsername() != None):
self.__openDB()
sql = "INSERT INTO postnas_search_access_control (username,name,access) VALUES (:username,:name,:access)"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":username", self.getUsername().lower())
query.bindValue(":name", self.name)
query.bindValue(":access", self.access)
query.exec_()
if (query.lastError().number() == -1):
return True
else:
return False
else:
return False
def getAggregationAttributes(self):
"""
Gets complex link columns
"""
self.checkAndOpenDb()
columns = []
sql = self.gen.getAggregationColumn()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting aggregation attributes: ") +
query.lastError().text())
while query.next():
value = query.value(0)
columns.append(value)
return columns
def __insertLogEntry(self,requestType,search,result):
self.__openDB()
sql = "INSERT INTO postnas_search_logging (datum,username,requestType,search,result) VALUES (:datum,:username,:requestType,:search,:result)"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":datum",datetime.datetime.now().isoformat())
query.bindValue(":username",self.username)
query.bindValue(":requestType",requestType)
query.bindValue(":search",search)
query.bindValue(":result",str(result).replace("u'","'").replace("\'","\"").replace("[","{").replace("]","}"))
query.exec_()
if(query.lastError().number() == -1):
return True
else:
return False
def checkUserHasEigentuemerAccess(self):
if (self.getUsername() != None):
self.__openDB()
sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE access IN (0,1) AND lower(username) = :username"
queryEigentuemerAccess = QSqlQuery(self.db)
queryEigentuemerAccess.prepare(sql)
queryEigentuemerAccess.bindValue(":username", self.getUsername())
queryEigentuemerAccess.exec_()
if (queryEigentuemerAccess.lastError().number() == -1):
if (queryEigentuemerAccess.size() > 0):
return True
else:
return False
else:
return False
else:
return False
def listClassesFromDatabase(self):
"""
Gets a list with all classes from database.
:return: (str) list of all classes in the database.
"""
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
classList.append(str(query.value(0)))
return classList
def getQmlRecordDict(self, inputLayer):
self.checkAndOpenDb()
if isinstance(inputLayer, list):
sql = self.gen.getQmlRecords(inputLayer)
else:
sql = self.gen.getQmlRecords([inputLayer])
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting qmlRecordDict: ") +
query.lastError().text())
qmlDict = dict()
while query.next():
if isinstance(inputLayer, list):
qmlDict[query.value(0)] = query.value(1)
else:
return query.value(1)
return qmlDict
def 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 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 listGeomClassesFromDatabase(self, primitiveFilter=[]):
'''
Gets a list with geometry classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName[-2:].lower() in ["_p", "_l", "_a"]:
classList.append(layerName)
return classList
def getGeomColumnDict(self):
'''
Dict in the form 'geomName':[-list of table names-]
'''
self.checkAndOpenDb()
sql = self.gen.getGeomColumnDict()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom column dict: ") +
query.lastError().text())
geomDict = dict()
while query.next():
geomColumn = query.value(0)
tableName = query.value(1)
lyrName = '_'.join(tableName.split('_')[1::])
if geomColumn not in list(geomDict.keys()):
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def 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 fetchall(self, db, sql):
rows = []
qry = QSqlQuery(db)
if qry.exec_(sql):
rec = qry.record()
while qry.next():
row = {}
for i in range(0, rec.count()):
v = "%s" % qry.value(i)
if v == "NULL":
v = ''
row[rec.fieldName(i)] = v.strip()
rows.append(row)
else:
qDebug("Exec failed: " + qry.lastError().text())
return rows
class QtSqlDBCursor(object):
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = str
elif f.type() == QVariant.ByteArray:
t = str
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not next(self.qry):
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or
isinstance(value, QTime) or
isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
def execSQLCommand(self, sql, ignore_errors=False):
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_(sql), sql + ': ' + query.lastError().text())
query.finish()
