def Mr_Proper(self):
util = FLUtil()
self.db_.dbAux().transaction()
rx = QRegExp("^.*[\\d][\\d][\\d][\\d].[\\d][\\d].*[\\d][\\d]$")
rx2 = QRegExp("^.*alteredtable[\\d][\\d][\\d][\\d].*$")
qry = FLSqlQuery(None, self.db_.dbAux())
qry2 = FLSqlQuery(None, self.db_.dbAux())
steps = 0
item = ""
rx3 = QRegExp("^.*\\d{6,9}$")
listOldBks = rx3 in self.tables("")
qry.exec_("select nombre from flfiles")
util.createProgressDialog(util.tr("Borrando backups"),
len(listOldBks) + qry.size() + 5)
while qry.next():
item = qry.value(0)
if item.find(rx) > -1 or item.find(rx2) > -1:
util.setLabelText(util.tr("Borrando regisro %1").arg(item))
qry2.exec_("delete from flfiles where nombre = '%s'" % item)
if item.find("alteredtable") > -1:
if item.replace(".mtd", "") in self.tables(""):
util.setLabelText(
util.tr("Borrando tabla %1").arg(item))
qry2.exec_("drop table %s" %
(item.replace(".mtd", "")))
steps = steps + 1
util.setProgress(steps)
for item in listOldBks:
if item in self.tables(""):
util.setLabelText(util.tr("Borrando tabla %1").arg(item))
qry2.exec_("drop table %s" % item)
steps = steps + 1
util.setProgress(steps)
util.setLabelText(util.tr("Inicializando cachés"))
steps = steps + 1
util.setProgress(steps)
qry.exec_("delete from flmetadata")
qry.exec_("delete from flvar")
self.db_.manager().cleanupMetaData()
self.db_.dbAux().commit()
util.setLabelText(util.tr("Vacunando base de datos"))
steps = steps + 1
util.setProgress(steps)
qry2.exec_("vacuum")
steps = steps + 1
util.setProgress(steps)
util.destryProgressDialog()
def cleanupMetaData(self):
"""
Limpieza la tabla flmetadata, actualiza el cotenido xml con el de los fichero .mtd
actualmente cargados
"""
# util = FLUtil()
if not self.existsTable("flfiles") or not self.existsTable("flmetadata"):
return
buffer = None
table = ""
# q.setForwardOnly(True)
# c.setForwardOnly(True)
if self.dictKeyMetaData_:
self.dictKeyMetaData_.clear()
else:
self.dictKeyMetaData_ = {}
self.loadTables()
self.db_.managerModules().loadKeyFiles()
self.db_.managerModules().loadAllIdModules()
self.db_.managerModules().loadIdAreas()
q = FLSqlQuery(None, self.db_.dbAux())
q.exec_("SELECT tabla,xml FROM flmetadata")
while q.next():
self.dictKeyMetaData_[str(q.value(0))] = str(q.value(1))
c = FLSqlCursor("flmetadata", True, self.db_.dbAux())
q2 = FLSqlQuery(None, self.db_.dbAux())
q2.exec_("SELECT nombre,sha FROM flfiles WHERE nombre LIKE '%.mtd'")
while q2.next():
table = str(q2.value(0))
table = table.replace(".mtd", "")
tmd = self.metadata(table)
if not tmd:
#qWarning("FLManager::cleanupMetaData " + QApplication.tr(self, "No se ha podido crear los metadatatos para la tabla %s") % table)
continue
if not self.existsTable(table):
self.createTable(table)
c.select("tabla='%s'" % table)
if c.next():
buffer = c.primeUpdate()
buffer.setValue("xml", str(q2.value(1)))
c.update()
self.dictKeyMetaData_[table] = str(q2.value(1))
def fetchLargeValue(self, refKey):
if refKey == None:
return None
if not refKey[0:3] == "RK@":
return None
q = FLSqlQuery()
q.setSelect("contenido")
q.setFrom("fllarge")
q.setWhere(" refkey = '%s'" % refKey)
if q.exec_() and q.first():
v = q.value(0)
del q
#print(v)
if v.find("{"):
v = v[v.find("{") + 3:]
v = v[:v.find("};") + 1]
#v = v.replace("\t", "")
v = v.replace("\n", "")
#v = v.replace("\",", ",")
#v = v.replace(",\"", ",")
v = v.replace("\t", " ")
#v = v.replace("\n\"", "")
#v = v.split(",")
v = v.split('","')
#print(v)
return v
def fetchLargeValue(self, refKey):
"""
Obtiene el valor de gran tamaño segun su clave de referencia.
@param refKey Clave de referencia. Esta clave se suele obtener mediante FLManager::storeLargeValue
@return Valor de gran tamaño almacenado
"""
if refKey is None:
return None
if not refKey[0:3] == "RK@":
return None
q = FLSqlQuery()
q.setSelect("contenido")
q.setFrom("fllarge")
q.setWhere(" refkey = '%s'" % refKey)
if q.exec_() and q.first():
v = q.value(0)
del q
# print(v)
if v.find("{"):
v = v[v.find("{") + 3:]
v = v[:v.find("};") + 1]
# v = v.replace("\t", "")
v = v.replace("\n", "")
# v = v.replace("\",", ",")
# v = v.replace(",\"", ",")
v = v.replace("\t", " ")
# v = v.replace("\n\"", "")
# v = v.split(",")
v = v.split('","')
# print(v)
return v
def makeRuleUser(self, q, d, iduser):
if not iduser or not q or not d:
return
ac = FLAccesccControlFactory.create(str(q.value(1)))
if ac:
ac.setName(str(q.value(2)))
ac.setUser(iduser)
ac.setPerm(str(q.value(6)))
qAcos = FLSqlQuery()
qAcos.setTablesList("flacos")
qAcos.setSelect("nombre,permiso")
qAcos.setFrom("flacos")
qAcos.setWhere("idac ='%s'" % q.value(0))
qAcos.setForwardOnly()
acos = None
if qAcos.exec_():
while qAcos.next():
acos << str(qAcos.value(0))
acos << str(qAcos.value(1))
ac.setAcos(acos)
ac.get(d)
del ac
def tables(self, typeName=None):
tl = []
if not self.isOpen():
return tl
t = FLSqlQuery()
t.setForwardOnly(True)
if not typeName or typeName == "Tables":
t.exec_(
"select relname from pg_class where ( relkind = 'r' ) AND ( relname !~ '^Inv' ) AND relname !~ '^pg_' ) "
)
while t.next():
tl.append(str(t.value(0)))
if not typeName or typeName == "Views":
t.exec_(
"select relname from pg_class where ( relkind = 'v' ) AND ( relname !~ '^Inv' ) AND relname !~ '^pg_' ) "
)
while t.next():
tl.append(str(t.value(0)))
if not typeName or typeName == "SystemTables":
t.exec_(
"select relname from pg_class where ( relkind = 'r' ) AND relname like 'pg_%' ) "
)
while t.next():
tl.append(str(t.value(0)))
del t
return tl
def tables(self, typeName=None):
tl = []
if not self.isOpen():
return tl
t = FLSqlQuery()
t.setForwardOnly(True)
if typeName == "Tables" and typeName == "Views":
t.exec_(
"SELECT name FROM sqlite_master WHERE type='table' OR type='view'"
)
elif not typeName or typeName == "Tables":
t.exec_("SELECT name FROM sqlite_master WHERE type='table'")
elif not typeName or typeName == "Views":
t.exec_("SELECT name FROM sqlite_master WHERE type='view'")
while t.next():
tl.append(str(t.value(0)))
if not typeName or typeName == "SystemTables":
tl.append("sqlite_master")
del t
return tl
def fetchLargeValue(self, refKey):
"""
Obtiene el valor de gran tamaño segun su clave de referencia.
@param refKey Clave de referencia. Esta clave se suele obtener mediante FLManager::storeLargeValue
@return Valor de gran tamaño almacenado
"""
if refKey is None:
return None
if not refKey[0:3] == "RK@":
return None
if self._prj.singleFLLarge():
tableName = "fllarge"
else:
tableName = "fllarge_" + refKey.split("@")[1]
if not self.existsTable(tableName):
return None
q = FLSqlQuery()
q.setSelect("contenido")
q.setFrom(tableName)
q.setWhere(" refkey = '%s'" % refKey)
if q.exec_() and q.first():
v = q.value(0)
del q
# print(v)
v = clearXPM(v)
# print(v)
return v
def makeRuleGroup(self, q, d, idgroup):
"""
Crea varios nodos DOM correspondientes a un registro de la tabla "flacs" y para un grupo de usuarios determinado.
La función de este método es crear una regla para cada uno de los usuarios miembros del grupo, utilizando
FLAccessControlLists::makeRuleUser.
@param q Consulta sobre la tabla "flacs" posicionada en el registro a utilizar para construir las reglas.
@param d Documento DOM/XML en el que insertarán los nodos que describe las reglas de control de acceso.
@param idgroup Identificador del grupo de usuarios.
"""
if not idgroup or not q or not d:
return
qU = FLSqlQuery()
qU.setTablesList("flusers")
qU.setSelect("iduser")
qU.setFrom("flusers")
qU.setWhere("'idgroup='%s'" % idgroup)
qU.setForwardOnly(True)
if qU.exec_():
while qU.next():
self.makeRuleUser(q, d, str(qU.value(0)))
def sqlSelect(self, f, s, w, tL=None, size=0, connName="default"):
if w == None or w == "":
return False
q = FLSqlQuery(None, connName)
if tL:
q.setTablesList(tL)
else:
q.setTablesList(f)
q.setSelect(s)
q.setFrom(f)
q.setWhere(w)
#q.setForwardOnly(True)
if not q.exec():
return False
if q.next():
valor = q.value(0)
if isinstance(valor, datetime.date):
valor = str(valor)
return valor
if size:
return False
def existsTable(self, n, cache=True):
#if not self.db_:
# return False
#if n == None:
# return False
#if cache and self.listTables_:
# for name in self.listTables_:
# if name == n:
# return True
# return False
#else:
# return self.db_.existsTable(n)
#if cache:
# modId = self.db_.managerModules().idModuleOfFile(n +".mtd")
# res = os.path.exists(filedir("../tempdata/cache/%s/%s/file.mtd/%s" %(self.db_.db_name, modId, n)))
# if res == False:
# res == os.path.exists(filedir("../share/pineboo/tables/%s.mtd" %(n)))
# return res
#else:
q = FLSqlQuery()
sql_query = "SELECT * FROM %s WHERE 1 = 1" % n
q.setTablesList(n)
q.setSelect("*")
q.setFrom(n)
q.setWhere("1 = 1 LIMIT 1")
return q.exec_()
def init(self):
"""
Acciones de inicialización.
"""
self.initCount_ = self.initCount_ + 1
self.createSystemTable("flmetadata")
self.createSystemTable("flseqs")
if not self.db_.dbAux():
return
q = FLSqlQuery(None, self.db_.dbAux())
# q.setForwardOnly(True)
self.createSystemTable("flsettings")
if not q.exec_("SELECT * FROM flsettings WHERE flkey = 'sysmodver'"):
if pineboolib.project.conn.driver().cascadeSupport():
q.exec_("DROP TABLE flsettings CASCADE")
else:
q.exec_("DROP TABLE flsettings")
self.createSystemTable("flsettings")
if not self.dictKeyMetaData_:
self.dictKeyMetaData_ = {}
# self.dictKeyMetaData_.setAutoDelete(True)
else:
self.dictKeyMetaData_.clear()
q.exec_("SELECT tabla,xml FROM flmetadata")
while q.next():
self.dictKeyMetaData_[q.value(0)] = q.value(1)
q.exec_("SELECT * FROM flsettings WHERE flkey = 'sysmodver'")
if not q.next():
if pineboolib.project.conn.driver().cascadeSupport():
q.exec_("DROP TABLE flmetadata CASCADE")
else:
q.exec_("DROP TABLE flmetadata")
self.createSystemTable("flmetadata")
c = FLSqlCursor("flmetadata", True, self.db_.dbAux())
for key, value in self.dictKeyMetaData_:
buffer = c.primeInsert()
buffer.setValue("tabla", key)
buffer.setValue("xml", value)
c.insert()
if not self.cacheMetaData_:
self.cacheMetaData_ = []
if not self.cacheAction_:
self.cacheAction_ = []
if not self.cacheMetaDataSys_:
self.cacheMetaDataSys_ = []
def quickSqlSelect(self, f, s, w, connName="default"):
from pineboolib.fllegacy.FLSqlConnections import FLSqlConnections
if not w:
sql = "select " + s + " from " + f
else:
sql = "select " + s + " from " + f + " where " + w
q = FLSqlQuery(sql, FLSqlConnections().database(connName).db())
return q.value(0) if q.next() else False
def validateForm(self):
if not self.cursor_:
return True
mtd = self.cursor_.metadata()
if not mtd:
return True
if self.cursor_.modeAccess() == FLSqlCursor.Edit and mtd.concurWarn():
colFields = []
colFields = self.cursor_.concurrencyFields()
if colFields:
pKN = mtd.primaryKey()
pKWhere = self.cursor_.db().manager().formatAssignValue(
mtd.field(pKN), self.cursor_.valueBuffer(pKN))
q = FLSqlQuery(None, self.cursor_.db().connectionName())
q.setTablesList(mtd.name())
q.setSelect(colFields)
q.setFrom(mtd.name())
q.setWhere(pKWhere)
q.setForwardOnly(True)
if q.exec_() and q.next():
i = 0
for field in colFields:
# msg = "El campo '%s' con valor '%s' ha sido modificado\npor otro usuario con el valor '%s'" % (
# mtd.fieldNameToAlias(field), self.cursor_.valueBuffer(field), q.value(i))
res = QtWidgets.QMessageBox.warning(
QtWidgets.QApplication.focusWidget(),
"Aviso de concurrencia",
"\n\n ¿ Desea realmente modificar este campo ?\n\n"
"Sí : Ignora el cambio del otro usuario y utiliza el valor que acaba de introducir\n"
"No : Respeta el cambio del otro usuario e ignora el valor que ha introducido\n"
"Cancelar : Cancela el guardado del registro y vuelve a la edición del registro\n\n",
QtWidgets.QMessageBox.Yes
| QtWidgets.QMessageBox.Default,
QtWidgets.QMessageBox.No,
QtWidgets.QMessageBox.Cancel
| QtWidgets.QMessageBox.Escape)
if res == QtWidgets.QMessageBox.Cancel:
return False
if res == QtWidgets.QMessageBox.No:
self.cursor_.setValueBuffer(field, q.value(i))
if self.iface and self.cursor_.modeAccess(
) == FLSqlCursor.Insert or self.cursor_.modeAccess(
) == FLSqlCursor.Edit:
if self.iface:
try:
v = self.iface.validateForm()
except Exception:
pass
if v and not isinstance(v, bool):
return False
return True
def query(self, n, parent=None):
"""
Para obtener una consulta de la base de datos, a partir de un fichero XML.
El nombre de la consulta corresponde con el nombre del fichero mas la extensión ".qry"
que contiene en XML la descripción de la consulta. Este método escanea el fichero
y construye/devuelve el objeto FLSqlQuery. NO SE HACEN
CHEQUEOS DE ERRORES SINTÁCTICOS EN EL XML.
@param n Nombre de la consulta de la base de datos que se quiere obtener
@return Un objeto FLSqlQuery que representa a la consulta que se quiere obtener
"""
qryName = "%s.qry" % n
qry_ = self.db_.managerModules().contentCached(qryName)
if not qry_:
return None
# parser_ = etree.XMLParser(
# ns_clean=True,
# encoding="UTF-8",
# remove_blank_text=True,
#)
q = FLSqlQuery(parent, self.db_.connectionName())
root_ = etree.ElementTree.fromstring(qry_)
q.setSelect(
root_.find("select").text.replace(" ",
"").replace("\n", "").replace(
"\t", "").replace("\r", ""))
q.setFrom(root_.find("from").text.strip(' \t\n\r'))
q.setWhere(root_.find("where").text.strip(' \t\n\r'))
q.setTablesList(root_.find("tables").text.strip(' \t\n\r'))
orderBy_ = None
try:
orderBy_ = root_.find("order").text.strip(' \t\n\r')
q.setOrderBy(orderBy_)
except Exception:
pass
groupXml_ = root_.findall("group")
if not groupXml_:
groupXml_ = []
# group_ = []
i = 0
while i < len(groupXml_):
gr = groupXml_[i]
if float(gr.find("level").text.strip(' \t\n\r')) == i:
# print("LEVEL %s -> %s" % (i,gr.xpath("field/text()")[0].strip(' \t\n\r')))
q.addGroup(
FLGroupByQuery(i,
gr.find("field").text.strip(' \t\n\r')))
i = i + 1
return q
def readDBSettingEntry(self, key):
q = FLSqlQuery()
q.setSelect("valor")
q.setFrom("flsettings")
q.setWhere("flkey = '%s'" % key)
q.setTablesList("flsettings")
if q.exec() and q.first():
return q.value(0)
return None
def storeLargeValue(self, mtd, largeValue):
if largeValue[0:3] == "RK@" or not mtd:
return None
tableName = mtd.name()
if self.isSystemTable(tableName):
return None
tableLarge = None
if self._prj.singleFLLarge():
tableLarge = "fllarge"
else:
tableLarge = "fllarge_%s" % tableName
if not self.existsTable(tableLarge):
mtdLarge = FLTableMetaData(tableLarge, tableLarge)
fieldLarge = FLFieldMetaData("refkey", "refkey", False, True,
"string", 100)
mtdLarge.addFieldMD(fieldLarge)
fieldLarge2 = FLFieldMetaData("sha", "sha", True, False,
"string", 50)
mtdLarge.addFieldMD(fieldLarge2)
fieldLarge3 = FLFieldMetaData("contenido", "contenido", True,
False, "stringlist")
mtdLarge.addFieldMD(fieldLarge3)
mtdAux = self.createTable(mtdLarge)
mtd.insertChild(mtdLarge)
if not mtdAux:
return None
util = FLUtil()
sha = str(util.sha1(largeValue))
refKey = "RK@%s@%s" % (tableName, sha)
q = FLSqlQuery()
q.setSelect("refkey")
q.setFrom("fllarge")
q.setWhere(" refkey = '%s'" % refKey)
if q.exec_() and q.first():
if not q.value(0) == sha:
sql = "UPDATE %s SET contenido = '%s' WHERE refkey ='%s'" % (
tableLarge, largeValue, refKey)
if not util.execSql(sql, "Aux"):
print("FLManager::ERROR:StoreLargeValue.Update %s.%s" %
(tableLarge, refKey))
return None
else:
sql = "INSERT INTO %s (contenido,refkey) VALUES ('%s','%s')" % (
tableLarge, largeValue, refKey)
if not util.execSql(sql, "Aux"):
print("FLManager::ERROR:StoreLargeValue.Insert %s.%s" %
(tableLarge, refKey))
return None
return refKey
def nextSerialVal(self, table, field):
q = FLSqlQuery()
q.setSelect("max(%s)" % field)
q.setFrom(table)
q.setWhere("1 = 1")
if not q.exec_(): # FIXME: exec es palabra reservada
print("not exec sequence")
return None
if q.first():
return int(q.value(0)) + 1
else:
return None
def loadIdAreas(self):
if not self._prj.conn.dbAux():
return
self.listIdAreas_ = []
q = FLSqlQuery(None, self._prj.conn.dbAux())
# q.setForwardOnly(True)
q.exec_("SELECT idarea from flareas WHERE idarea <> 'sys'")
while q.next():
self.listIdAreas_.append(str(q.value(0)))
self.listIdAreas_.append("sys")
def existsTable(self, name):
if not self.isOpen():
return False
t = FLSqlQuery()
t.setForwardOnly(True)
ok = t.exec_("SELECT * FROM %s WHERE 1 = 1 LIMIT 1" % name)
if ok:
ok = t.next()
del t
return ok
def nextSerialVal(self, table, field):
q = FLSqlQuery()
q.setSelect(u"nextval('" + table + "_" + field + "_seq')")
q.setFrom("")
q.setWhere("")
if not q.exec():
print("not exec sequence")
return None
if q.first():
return q.value(0)
else:
return None
def loadKeyFiles(self):
self.dictKeyFiles = {}
self.dictModFiles = {}
q = FLSqlQuery(None, self._prj.conn.dbAux())
# q.setForwardOnly(True)
q.exec_("SELECT nombre, sha, idmodulo FROM flfiles")
name = None
while q.next():
name = str(q.value(0))
self.dictKeyFiles[name] = str(q.value(1))
self.dictModFiles[name.upper()] = str(q.value(2))
def createTable(self, *tmd):
if self.driverName_ is None or not self.tmd or not self.dbAux_:
return False
dr = self.dbAux_.driver()
sql = dr.createTable(tmd)
if sql is None:
return False
q = FLSqlQuery()
if q.exec_(sql):
print("FLManager: SQL - %s" % sql)
def fetchLargeValue(self, refKey):
if not refKey[0:3] == "RK@":
return None
q = FLSqlQuery()
q.setSelect("contenido")
q.setFrom("fllarge")
q.setWhere(" refkey = '%s'" % refKey)
if q.exec_() and q.first():
v = q.value(0)
del q
return v
return None
def existsTable(self, name):
if not self.isOpen():
return False
t = FLSqlQuery()
t.setForwardOnly(True)
ok = t.exec_("select relname from pg_class where relname = '%s'" %
name)
if ok:
ok = t.next()
del t
return ok
def existsTable(self, n, cache=True):
if cache:
modId = self.db_.managerModules().idModuleOfFile(n + ".mtd")
return os.path.exists(
filedir("../tempdata/cache/%s/file.mtd/%s" % (modId, n)))
q = FLSqlQuery()
sql_query = "SELECT * FROM %s WHERE 1 = 1" % n
q.setTablesList(n)
q.setSelect("*")
q.setFrom(n)
q.setWhere("1 = 1 LIMIT 1")
return q.exec_()
def quickSqlSelect(self, f, s, w, connName="default"):
"""
Versión rápida de sqlSelect. Ejecuta directamente la consulta sin realizar comprobaciones.
Usar con precaución.
"""
if not w:
sql = "select " + s + " from " + f
else:
sql = "select " + s + " from " + f + " where " + w
q = FLSqlQuery(None, connName)
if not q.exec_(sql):
return False
return q.value(0) if q.first() else False
def makeRuleGroup(self, q, d, idgroup):
if not idgroup or not q or not d:
return
qU = FLSqlQuery()
qU.setTablesList("flusers")
qU.setSelect("iduser")
qU.setFrom("flusers")
qU.setWhere("'idgroup='%s'" % idgroup)
qU.setForwardOnly(True)
if qU.exec_():
while qU.next():
self.makeRuleUser(q, d, str(qU.value(0)))
def shaOfFile(self, n):
if self._prj.conn.dbAux(
) and not n[:3] == "sys" and not self._prj.conn.manager(
).isSystemTable(n):
formatVal = self._prj.conn.manager().formatAssignValue(
"nombre", "string", n, True)
q = FLSqlQuery(None, self._prj.conn.dbAux())
# q.setForwardOnly(True)
q.exec_("SELECT sha FROM flfiles WHERE %s" % formatVal)
if q.next():
return str(q.value(0))
return None
else:
return None
def readDBSettingEntry(self, key):
"""
Lee el valor de un setting en la tabla flsettings
@param key. Clave identificadora del setting
@return Valor del setting
"""
q = FLSqlQuery()
q.setSelect("valor")
q.setFrom("flsettings")
q.setWhere("flkey = '%s'" % key)
q.setTablesList("flsettings")
if q.exec_() and q.first():
return q.value(0)
return None
