def normalizeValue(self, text):
if getattr(self.driver(), "normalizeValue", None):
return self.driver().normalizeValue(text)
qWarning("PNConnection: El driver %s no dispone de normalizeValue(text)" %
self.driverName())
return text
def refreshQuery(self, curname, fields, table, where, cursor, conn):
sql = "DECLARE %s NO SCROLL CURSOR WITH HOLD FOR SELECT %s FROM %s WHERE %s " % (
curname, fields, table, where)
try:
cursor.execute(sql)
except Exception:
qWarning("CursorTableModel.Refresh\n %s" % traceback.format_exc())
def createTable(self, n_or_tmd):
"""
Crea una tabla en la base de datos.
@param n_tmd Nombre o metadatos de la tabla que se quiere crear
@return Un objeto FLTableMetaData con los metadatos de la tabla que se ha creado, o
0 si no se pudo crear la tabla o ya existía
"""
util = FLUtil()
if n_or_tmd is None:
return False
if isinstance(n_or_tmd, str):
tmd = self.metadata(n_or_tmd)
if not tmd:
return False
if self.existsTable(tmd.name()):
self.listTables_.append(n_or_tmd)
return tmd
else:
qWarning("FLMAnager :: No existe tabla %s" % n_or_tmd)
return self.createTable(tmd)
else:
if n_or_tmd.isQuery() or self.existsTable(n_or_tmd.name(), False):
return n_or_tmd
if not self.db_.createTable(n_or_tmd):
logger.warn("FLManager : %s", util.tr(
"No se ha podido crear la tabla ") + n_or_tmd.name())
return False
return n_or_tmd
def createTable(self, n_or_tmd):
util = FLUtil()
if n_or_tmd == None:
return False
if isinstance(n_or_tmd, str):
tmd = self.metadata(n_or_tmd)
if not tmd:
return False
if self.existsTable(tmd.name()):
self.listTables_.append(n_or_tmd)
return tmd
else:
qWarning("FLMAnager :: No existe tabla %s" % n_or_tmd)
return self.createTable(tmd)
else:
if n_or_tmd.isQuery() or self.existsTable(n_or_tmd.name(), False):
return n_or_tmd
if not self.db_.createTable(n_or_tmd):
print(
"FLManager :",
util.tr("No se ha podido crear la tabla ") +
n_or_tmd.name())
return False
return n_or_tmd
def refreshQuery(self, curname, fields, table, where, cursor, conn):
sql = "DECLARE %s NO SCROLL CURSOR WITH HOLD FOR SELECT %s FROM %s WHERE %s " % (
curname, fields, table, where)
try:
cursor.execute(sql)
except Exception:
qWarning("CursorTableModel.Refresh\n %s" % traceback.format_exc())
def normalizeValue(self, text):
if getattr(self.driver(), "normalizeValue", None):
return self.driver().normalizeValue(text)
qWarning("PNConnection: El driver %s no dispone de normalizeValue(text)" %
self.driverName())
return text
def fetchAll(self, cursor, tablename, where_filter, fields, curname):
ret_ = []
try:
ret_ = cursor.fetchall()
except Exception:
qWarning("PSQLDriver.fetchAll\n %s" % traceback.format_exc())
return ret_
def refreshQuery(self, curname, fields, table, where, cursor, conn):
if curname not in self.cursorsArray_.keys():
self.cursorsArray_[curname] = cursor
sql = "SELECT %s FROM %s WHERE %s " % (fields, table, where)
try:
self.cursorsArray_[curname].execute(sql)
except Exception:
qWarning("CursorTableModel.Refresh\n %s" % traceback.format_exc())
def refreshQuery(self, curname, fields, table, where, cursor, conn):
sql = "DECLARE %s NO SCROLL CURSOR WITH HOLD FOR SELECT %s FROM %s WHERE %s " % (
curname, fields, table, where)
try:
cursor.execute(sql)
except Exception:
logger.warning("Error en consulta %s\n%s", sql, traceback.format_exc(), stack_info=True)
return
qWarning("CursorTableModel.Refresh\n %s" % traceback.format_exc())
def refreshQuery(self, curname, fields, table, where, cursor, conn):
if curname not in self.cursorsArray_.keys():
self.cursorsArray_[curname] = cursor
sql = "SELECT %s FROM %s WHERE %s " % (fields, table, where)
try:
self.cursorsArray_[curname].execute(sql)
except Exception:
qWarning("CursorTableModel.Refresh\n %s" % traceback.format_exc())
def __init__(self, parent, action, load=False):
# if pineboolib.project._DGI.localDesktop(): # Si es local Inicializa
# super(QtWidgets.QWidget, self).__init__(parent)
super(QtWidgets.QWidget, self).__init__(parent)
try:
assert (self.__class__, action) not in self.known_instances
except AssertionError:
print("WARN: Clase %r ya estaba instanciada, reescribiendo!. " % ((self.__class__, action),) +
"Puede que se estén perdiendo datos!")
self.known_instances[(self.__class__, action)] = self
self.ui_ = {}
self.action = action
if type(self).__name__ == "FLFormRecordDB":
self.actionName_ = "formRecord" + action.name
else:
self.actionName_ = "form" + action.name
self.prj = action.prj
self.mod = action.mod
self.layout = QtWidgets.QVBoxLayout()
self.layout.setContentsMargins(1, 1, 1, 1)
self.layout.setSpacing(1)
self.layout.setContentsMargins(1, 1, 1, 1)
self.layout.setSizeConstraint(QtWidgets.QLayout.SetMinAndMaxSize)
self.setLayout(self.layout)
if not self._uiName:
self._uiName = action.form
if not self._scriptForm and getattr(action, "scriptform", None):
self._scriptForm = action.scriptform
if not getattr(action, "alias", None):
qWarning("FLFormDB::Cargando un action XML")
elif pineboolib.project._DGI.localDesktop():
self.setWindowTitle(action.alias)
self.loaded = False
self.idMDI_ = self.action.name
self.script = None
self.iface = None
try:
script = self._scriptForm or None
except AttributeError:
script = None
self.action.load_script(script, self)
self.iconSize = pineboolib.project._DGI.iconSize()
if load:
self.load()
self.initForm()
def execute_query(self, q):
if not self.isOpen():
qWarning("PSQLDriver::execute_query. DB is closed")
return False
cursor = self.conn_.cursor()
try:
cursor.execute(q)
except Exception:
self.setLastError("No se puedo ejecutar la siguiente query", q)
qWarning("PSQLDriver:: No se puedo ejecutar la siguiente query %s % q\n %s" % (q, traceback.format_exc()))
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
q = FLSqlQuery(None, self.db_.dbAux())
c = FLSqlCursor("flmetadata", True, self.db_.dbAux())
buffer = None
table = ""
# q.setForwardOnly(True)
# c.setForwardOnly(True)
if not self.dictKeyMetaData_:
self.dictKeyMetaData_ = {}
else:
self.dictKeyMetaData_.clear()
self.loadTables()
self.db_.managerModules().loadKeyFiles()
self.db_.managerModules().loadAllIdModules()
self.db_.managerModules().loadIdAreas()
q.exec_("SELECT tabla, xml FROM flmetadata")
while q.next():
self.dictKeyMetaData_[str(q.value(0))] = str(q.value(1))
q.exec_(
"SELECT nombre, sha FROM flfiles WHERE nombre LIKE '%.mtd'")
while q.next():
table = str(q.value(0))
table = table.replace(".mtd", "")
if not self.existsTable(table):
self.createTable(table)
tmd = self.metadata(table)
if not tmd:
qWarning("FLManager::cleanupMetaDAta " + QApplication.tr(
"No se ha podido crear los metadatatos para la tabla %1"
).arg(table))
c.select("tabla='%s'" % table)
if c.next():
buffer = c.primeUpdate()
buffer.setValue("xml", str(q.value(1)))
c.update()
self.dictKeyMetaData_[table] = str(q.value(1))
def nextSerialVal(self, table, field):
q = FLSqlQuery()
q.setSelect(u"nextval('" + table + "_" + field + "_seq')")
q.setFrom("")
q.setWhere("")
if not q.exec_():
qWarning("not exec sequence")
return None
if q.first():
return q.value(0)
else:
return None
def nextSerialVal(self, table, field):
q = FLSqlQuery()
q.setSelect(u"nextval('" + table + "_" + field + "_seq')")
q.setFrom("")
q.setWhere("")
if not q.exec_():
qWarning("not exec sequence")
return None
if q.first():
return q.value(0)
else:
return None
def commitTransaction(self):
if not self.isOpen():
qWarning("%s::commitTransaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("COMMIT TRANSACTION")
except Exception:
self.setLastError("No se pudo aceptar la transacción", "COMMIT")
qWarning("%s:: No se pudo aceptar la transacción COMMIT\n %s" %
(self.name_, traceback.format_exc()))
return False
return True
def rollbackTransaction(self):
if not self.isOpen():
qWarning("%s::rollbackTransaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("ROLLBACK TRANSACTION")
except Exception:
self.setLastError("No se pudo deshacer la transacción", "ROLLBACK")
qWarning("%s:: No se pudo deshacer la transacción ROLLBACK\n %s" % (
self.name_, traceback.format_exc()))
return False
return True
def transaction(self):
if not self.isOpen():
qWarning("%s::transaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("START TRANSACTION")
except Exception:
self.setLastError("No se pudo crear la transacción", "BEGIN WORK")
qWarning("%s:: No se pudo crear la transacción BEGIN\n %s" %
(self.name_, traceback.format_exc()))
return False
return True
def rollbackTransaction(self):
if not self.isOpen():
qWarning("%s::rollbackTransaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("ROLLBACK TRANSACTION")
except Exception:
self.setLastError("No se pudo deshacer la transacción", "ROLLBACK")
qWarning("%s:: No se pudo deshacer la transacción ROLLBACK\n %s" % (
self.name_, traceback.format_exc()))
return False
return True
def commitTransaction(self):
if not self.isOpen():
qWarning("%s::commitTransaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("COMMIT TRANSACTION")
except Exception:
self.setLastError("No se pudo aceptar la transacción", "COMMIT")
qWarning("%s:: No se pudo aceptar la transacción COMMIT\n %s" %
(self.name_, traceback.format_exc()))
return False
return True
def transaction(self):
if not self.isOpen():
qWarning("%s::transaction: Database not open" % self.name_)
cursor = self.conn_.cursor()
try:
cursor.execute("START TRANSACTION")
except Exception:
self.setLastError("No se pudo crear la transacción", "BEGIN WORK")
qWarning("%s:: No se pudo crear la transacción BEGIN\n %s" %
(self.name_, traceback.format_exc()))
return False
return True
def connect(self, db_name, db_host, db_port, db_userName, db_password):
self._dbname = db_name
checkDependencies({"MySQLdb": "mysqlclient", "sqlalchemy":"sqlAlchemy"})
import MySQLdb
try:
self.conn_ = MySQLdb.connect(db_host, db_userName, db_password, db_name)
self.engine_ = create_engine('mysql+mysqldb://%s:%s@%s:%s/%s' % (db_userName, db_password, db_host, db_port, db_name))
except MySQLdb.OperationalError as e:
pineboolib.project._splash.hide()
if "Unknown database" in str(e):
ret = QMessageBox.warning(None, "Pineboo",
"La base de datos %s no existe.\n¿Desea crearla?" % db_name,
QMessageBox.Ok | QMessageBox.No)
if ret == QMessageBox.No:
return False
else:
try:
tmpConn = MySQLdb.connect(db_host, db_userName, db_password)
cursor = tmpConn.cursor()
try:
cursor.execute("CREATE DATABASE %s" % db_name)
except Exception:
print("ERROR: FLPSQL.connect",traceback.format_exc())
cursor.execute("ROLLBACK")
cursor.close()
return False
cursor.close()
return self.connect(db_name, db_host, db_port, db_userName, db_password)
except Exception:
qWarning(traceback.format_exc())
QMessageBox.information(
None, "Pineboo", "ERROR: No se ha podido crear la Base de Datos %s" % db_name, QMessageBox.Ok)
print(
"ERROR: No se ha podido crear la Base de Datos %s" % db_name)
return False
else:
QMessageBox.information(
None, "Pineboo", "Error de conexión\n%s" % str(e), QMessageBox.Ok)
return False
if self.conn_:
self.open_ = True
self.conn_.autocommit(True)
self.conn_.set_character_set('utf8')
return self.conn_
def rollbackSavePoint(self, n):
if not self.isOpen():
qWarning("%s::rollbackSavePoint: Database not open" % self.name_)
return False
cursor = self.conn_.cursor()
try:
cursor.execute("ROLLBACK TO SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo rollback a punto de salvaguarda", "ROLLBACK TO SAVEPOINTt sv_%s" % n)
qWarning("%s:: No se pudo rollback a punto de salvaguarda ROLLBACK TO SAVEPOINT sv_%s\n %s" % (
self.name_, n, traceback.format_exc()))
return False
return True
def savePoint(self, n):
if not self.isOpen():
qWarning("%s::savePoint: Database not open" % self.name_)
return False
cursor = self.conn_.cursor()
try:
cursor.execute("SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo crear punto de salvaguarda", "SAVEPOINT sv_%s" % n)
qWarning("PSQLDriver:: No se pudo crear punto de salvaguarda SAVEPOINT sv_%s \n %s " % (
n, traceback.format_exc()))
return False
return True
def execute_query(self, q):
if not self.isOpen():
qWarning("MySQLDriver::execute_query. DB is closed")
return False
cursor = self.cursor()
try:
q = self.fix_query(q)
cursor.execute(q)
except Exception as exc:
print("*****", q, exc)
sys.exit(32)
self.setLastError(
"No se puedo ejecutar la siguiente query %s" % q, q)
qWarning("MySQLDriver:: No se puedo ejecutar la siguiente query %s\n %s" % (q, traceback.format_exc()))
def rollbackSavePoint(self, n):
if not self.isOpen():
qWarning("%s::rollbackSavePoint: Database not open" % self.name_)
return False
cursor = self.conn_.cursor()
try:
cursor.execute("ROLLBACK TO SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo rollback a punto de salvaguarda", "ROLLBACK TO SAVEPOINTt sv_%s" % n)
qWarning("%s:: No se pudo rollback a punto de salvaguarda ROLLBACK TO SAVEPOINT sv_%s\n %s" % (
self.name_, n, traceback.format_exc()))
return False
return True
def savePoint(self, n):
if not self.isOpen():
qWarning("%s::savePoint: Database not open" % self.name_)
return False
cursor = self.conn_.cursor()
try:
cursor.execute("SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo crear punto de salvaguarda", "SAVEPOINT sv_%s" % n)
qWarning("MySQLDriver:: No se pudo crear punto de salvaguarda SAVEPOINT sv_%s \n %s " % (
n, traceback.format_exc()))
return False
return True
def releaseSavePoint(self, n):
if not self.isOpen():
qWarning("PSQLDriver::releaseSavePoint: Database not open")
return False
cursor = self.conn_.cursor()
try:
cursor.execute("RELEASE SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo release a punto de salvaguarda", "RELEASE SAVEPOINT sv_%s" % n)
qWarning("PSQLDriver:: No se pudo release a punto de salvaguarda RELEASE SAVEPOINT sv_%s\n %s" % (
n, traceback.format_exc()))
return False
return True
def createTable(self, tmd):
if not self.db():
return False
sql = self.driver().sqlCreateTable(tmd)
if not sql:
return False
q = self.cursor()
try:
q.execute(sql)
q.execute("COMMIT")
except:
qWarning(traceback.format_exc())
q.execute("ROLLBACK")
return False
return True
def releaseSavePoint(self, n):
if not self.isOpen():
qWarning("PSQLDriver::releaseSavePoint: Database not open")
return False
cursor = self.conn_.cursor()
try:
cursor.execute("RELEASE SAVEPOINT sv_%s" % n)
except Exception:
self.setLastError(
"No se pudo release a punto de salvaguarda", "RELEASE SAVEPOINT sv_%s" % n)
qWarning("PSQLDriver:: No se pudo release a punto de salvaguarda RELEASE SAVEPOINT sv_%s\n %s" % (
n, traceback.format_exc()))
return False
return True
def save (self,filename = None):
print ("sauvegarde")
progress = QProgressDialog ()
progress.setWindowModality(QtCore.Qt.WindowModal)
progress.setLabelText("Sauvegarde")
progress.setMaximum(len(self.getWarriorList())+1)
#db_name = self.database.database.databaseName()
if filename == None :
filename = os.path.join(Config().instance.path_to_sqlite(),self.settings.value("global/current_database"))
try :
print ('current filename', filename)
# backup
filename_bkp = filename+"_"+QtCore.QDateTime.currentDateTime().toString("yyyy-MM-dd-hh-mm-ss")
QFile.copy(filename,filename_bkp)
if QFile.remove(filename) == False :
qWarning("echec suppression ")
else:
qWarning("reussite suppression %s"% filename)
except OSError :
qWarning("echec suppression ")
result = QFile.copy(Config().instance.model_database(),filename)
if result == False :
print("echec de la copy ",Config().instance.model_database(),filename)
return
else:
print("copy du model reussit")
database = DatabaseManager(filename,True)
database.createConnection()
database.setVerbose(True)
for faction in self.factions.values() :
attribs = faction.getDictAttributes ()
database.insert("gm_faction",attribs)
for empire in faction.empires.values():
attribs = empire.getDictAttributes ()
database.insert("gm_empire",attribs)
for kingdom in empire.kingdoms.values():
attribs = kingdom.getDictAttributes ()
database.insert("gm_kingdom",attribs)
for temple in kingdom.temples:
attribs = temple.getDictAttributes ()
database.insert("gm_temple",attribs)
for groupe in kingdom.groupes.values():
attribs = groupe.getDictAttributes ()
database.insert("gm_groupe",attribs)
for sub_groupe in groupe.sub_groupes:
attribs = sub_groupe.getDictAttributes ()
database.insert("gm_groupe",attribs)
for perso in sub_groupe.warriors.values():
attribs = perso.getDictAttributes ()
database.insert("gm_perso",attribs)
for perso in groupe.warriors.values():
attribs = perso.getDictAttributes ()
database.insert("gm_perso",attribs)
progress.setValue(progress.value()+1)
def nextSerialVal(self, table, field):
"""
q = FLSqlQuery()
q.setSelect(u"nextval('" + table + "_" + field + "_seq')")
q.setFrom("")
q.setWhere("")
if not q.exec_():
qWarning("not exec sequence")
return None
if q.first():
return q.value(0)
else:
return None
"""
if not self.isOpen():
qWarning("%s::beginTransaction: Database not open" % self.name_)
return None
if not self.noInnoDB and self.transaction():
self.setLastError(
"No se puede iniciar la transacción", "BEGIN WORK")
return None
res = None
row = None
max = 0
curMax = 0
updateQry = False
strQry = "SELECT MAX(%s) FROM %s" % (field, table)
cursor = self.conn_.cursor()
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: No se pudo crear la transacción BEGIN\n %s" %
(self.name_, traceback.format_exc()))
self.rollbackTransaction()
return
for max_ in result:
res = max_
if res:
row = cursor._fetch_row(res)
if row:
max = int(row[0])
strQry = "SELECT seq FROM flseqs WHERE tabla = '%s' AND campo ='%s'" % (
table, field)
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
self.rollbackTransaction()
return
for curMax_ in result:
res = curMax_
if res:
updateQry = (len(res) > 0)
if updateQry:
row = cursor._fetch_row(res)
if row:
curMax = int(row[0])
strQry = None
if updateQry:
if max > curMax:
strQry = "UPDATE flseq SET seq=%s WHERE tabla = '%s' AND campo = '%s'" % (
max + 1, table, field)
else:
strQry = "INSERT INTO flseq (tabla, campo, seq) VALUES('%s','%s',%s)" % (
table, field, max + 1)
if strQry:
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
strQry = "UPDATE flseq SET seq= LAST INSERT_ID(seq+1) WHERE tabla = '%s' and campo = '%s'" % (
table, field)
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
strQry = "SELECT LAST_INSERT_ID()"
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
for r in result:
res = r
if res:
row = cursor._fetch_row(res)
if row:
ret = int(row[0])
if not self.noInnoDB and self.commitTransaction():
qWarning("%s:: No se puede aceptar la transacción" % self.name_)
return
return ret
def connect(self, db_name, db_host, db_port, db_userName, db_password):
try:
import psycopg2
except ImportError:
qWarning(traceback.format_exc())
qWarning(
"HINT: Instale el paquete python3-psycopg2 e intente de nuevo")
sys.exit(0)
conninfostr = "dbname=%s host=%s port=%s user=%s password=%s connect_timeout=5" % (
db_name, db_host, db_port, db_userName, db_password)
try:
self.conn_ = psycopg2.connect(conninfostr)
except psycopg2.OperationalError as e:
if "does not exist" in str(e):
if QMessageBox.No == QMessageBox.warning(
None, "Pineboo",
"La base de datos %s no existe.\n¿Desea crearla?" %
db_name, QMessageBox.Ok | QMessageBox.No):
return False
else:
conninfostr2 = "dbname=postgres host=%s port=%s user=%s password=%s connect_timeout=5" % (
db_host, db_port, db_userName, db_password)
try:
tmpConn = psycopg2.connect(conninfostr2)
tmpConn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = tmpConn.cursor()
cursor.execute("CREATE DATABASE %s" % db_name)
cursor.close()
return self.connect(db_name, db_host, db_port,
db_userName, db_password)
except Exception:
qWarning(traceback.format_exc())
QMessageBox.information(
None, "Pineboo",
"ERROR: No se ha podido crear la Base de Datos %s"
% db_name, QMessageBox.Ok)
print(
"ERROR: No se ha podido crear la Base de Datos %s"
% db_name)
return False
else:
QMessageBox.information(None, "Pineboo",
"Error de conexión\n%s" % str(e),
QMessageBox.Ok)
return False
#self.conn_.autocommit = True #Posiblemente tengamos que ponerlo a false para que las transacciones funcionen
self.conn_.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
if self.conn_:
self.open_ = True
try:
self.conn_.set_client_encoding("UTF8")
except Exception:
qWarning(traceback.format_exc())
return self.conn_
def sqlCreateTable(self, tmd):
util = FLUtil()
if not tmd:
return None
primaryKey = None
sql = "CREATE TABLE %s (" % tmd.name()
seq = None
fieldList = tmd.fieldList()
unlocks = 0
for field in fieldList:
if field.type() == "unlock":
unlocks = unlocks + 1
if unlocks > 1:
qWarning(u"FLManager : No se ha podido crear la tabla " +
tmd.name())
qWarning(
u"FLManager : Hay mas de un campo tipo unlock. Solo puede haber uno."
)
return None
i = 1
for field in fieldList:
sql = sql + field.name()
if field.type() == "int":
sql = sql + " INT2"
elif field.type() == "uint":
sql = sql + " INT4"
elif field.type() in ("bool", "unlock"):
sql = sql + " BOOLEAN"
elif field.type() == "double":
sql = sql + " FLOAT8"
elif field.type() == "time":
sql = sql + " TIME"
elif field.type() == "date":
sql = sql + " DATE"
elif field.type() == "pixmap":
sql = sql + " TEXT"
elif field.type() == "string":
sql = sql + " VARCHAR"
elif field.type() == "stringlist":
sql = sql + " TEXT"
elif field.type() == "bytearray":
sql = sql + " BYTEA"
elif field.type() == "serial":
seq = "%s_%s_seq" % (tmd.name(), field.name())
q = FLSqlQuery()
q.setForwardOnly(True)
q.exec_("SELECT relname FROM pg_class WHERE relname='%s'" %
seq)
if not q.next():
q.exec_("CREATE SEQUENCE %s" % seq)
sql = sql + " INT4 DEFAULT NEXTVAL('%s')" % seq
del q
longitud = field.length()
if longitud > 0:
sql = sql + "(%s)" % longitud
if field.isPrimaryKey():
if primaryKey == None:
sql = sql + " PRIMARY KEY"
else:
qWarning(
QApplication.tr("FLManager : Tabla-> ") + tmd.name() +
QApplication.
tr(" . Se ha intentado poner una segunda clave primaria para el campo "
) + field.name() +
QApplication.tr(" , pero el campo ") + primaryKey +
QApplication.
tr(" ya es clave primaria. Sólo puede existir una clave primaria en FLTableMetaData, use FLCompoundKey para crear claves compuestas."
))
return None
else:
if field.isUnique():
sql = sql + " UNIQUE"
if not field.allowNull():
sql = sql + " NOT NULL"
else:
sql = sql + " NULL"
if not i == len(fieldList):
sql = sql + ","
i = i + 1
sql = sql + ")"
return sql
def nextSerialVal(self, table, field):
if not self.isOpen():
qWarning("%s::beginTransaction: Database not open" % self.name_)
return None
if not self.noInnoDB and self.transaction():
self.setLastError(
"No se puede iniciar la transacción", "BEGIN WORK")
return None
res = None
row = None
max = 0
cur_max = 0
updateQry = False
ret = None
q = FLSqlQuery()
q.setSelect("max(%s)" % field)
q.setFrom(table)
q.setWhere("1 = 1")
if not q.exec_():
self.logger.warning("not exec sequence")
return None
if q.first() and q.value(0) is not None:
max = q.value(0)
cursor = self.conn_.cursor()
#print(1,"max de %s.%s = %s" % (table, field, max))
strQry = "SELECT seq FROM flseqs WHERE tabla = '%s' AND campo ='%s'" % (table, field)
try:
cur_max = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" % (self.name_, traceback.format_exc()))
self.rollbackTransaction()
return
#print(2,"cur_max de %s.%s = %s" % (table, field , cur_max))
updateQry = cur_max > 0
strQry = None
ret = max + 1
if updateQry:
if ret > cur_max:
strQry = "UPDATE flseqs SET seq=%s WHERE tabla = '%s' AND campo = '%s'" % (ret, table, field)
else:
strQry = "INSERT INTO flseqs (tabla,campo,seq) VALUES('%s','%s',%s)" % (table, field, ret)
result = None
if strQry is not None:
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado\n %s" % (self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
if not self.noInnoDB and self.commitTransaction():
qWarning("%s:: No se puede aceptar la transacción" % self.name_)
return
return ret
def loadFromFile (self):
all_sqlite = self.database.select("*", "gm_perso",False,None)
nb_total = 0
while all_sqlite.next():
nb_total+=1
nb_heros_added = 0
self.progress.setLabelText("Etape 1/2 : Chargement de la base de donnee")
self.progress.setMinimum (0)
self.progress.setMaximum (nb_total*2)
qWarning("debut chargement de la bdd progess max = ")
faction_sqlite = self.database.select("*", "gm_faction",False,None,"ID ASC")
while faction_sqlite.next():
attribs = {'icon':faction_sqlite.value("icon")}
faction = Faction(faction_sqlite.value("ID"), faction_sqlite.value("name"),attribs,self)
self.addFaction(faction)
empire_sqlite = self.database.select("*", "gm_empire",False, "ID_faction=="+str(faction_sqlite.value("ID")),"ID ASC")
while empire_sqlite.next():
attribs = {'color':str(empire_sqlite.value("color"))}
empire = Empire(empire_sqlite.value("ID"), empire_sqlite.value("name"),attribs,faction)
faction.addEmpire(empire)
kingdom_sqlite = self.database.select("*", "gm_kingdom",False,"ID_empire=="+str(empire_sqlite.value("ID")),"ID ASC")
while kingdom_sqlite.next():
attribs = {'armee':kingdom_sqlite.value("armee"),'description':kingdom_sqlite.value("description"),'red':int(kingdom_sqlite.value("couleur").split(",")[0]),'green':int(kingdom_sqlite.value("couleur").split(",")[1]),'blue':int(kingdom_sqlite.value("couleur").split(",")[2]),'alpha':int(kingdom_sqlite.value("couleur").split(",")[3])}
attribs['temples'] = kingdom_sqlite.value("temples").split(',')
kingdom = Kingdom(kingdom_sqlite.value("ID"), kingdom_sqlite.value("name"),attribs,empire)
empire.addKingdom(kingdom)
for t_id in kingdom_sqlite.value("temples").split(','):
temples_sqlite = self.database.select("*", "gm_temple",True,"ID="+t_id)
try :
temples_sqlite.next()
level_dict = {}
for name,background in zip(temples_sqlite.value("levels").split(','),temples_sqlite.value("backgrounds").split(',')):
level_dict[name] = background
pos = QPointF(float(temples_sqlite.value("latitude")),float(temples_sqlite.value("longitude")))
master = temples_sqlite.value("master")
temple = Temple(temples_sqlite.value("ID"), temples_sqlite.value("name"),kingdom,pos,level_dict,master)
kingdom.addTemple(temple)
qWarning("ID : %s" %t_id)
except AttributeError:
qWarning("Temple manquant ID : %s" %t_id)
# for t in kingdom_sqlite.value("temples").split(','):
# if t in self.temples:
# self.temples[t].setOwner(kingdom)
groupe_sqlite = self.database.select("*", "gm_groupe",False,"ID_kingdom=="+str(kingdom_sqlite.value("ID"))+" and parent==0","ID ASC")
while groupe_sqlite.next():
attribs = {'description':groupe_sqlite.value("description"),'color':groupe_sqlite.value("color"),'rank':groupe_sqlite.value("rank")}
groupe = Groupe(groupe_sqlite.value("ID"), groupe_sqlite.value("name"),attribs,kingdom)
kingdom.addGroupe(groupe)
warrior_sqlite = self.database.select("*", "gm_perso",False,"ID_groupe=="+str(groupe_sqlite.value("ID")),"ID ASC")
while warrior_sqlite.next():
attribs = {}
attribs['latitude'] = warrior_sqlite.value("latitude")
attribs['longitude'] = warrior_sqlite.value("longitude")
attribs['place'] = warrior_sqlite.value("place")
attribs['level'] = warrior_sqlite.value("level")
attribs['leader'] = bool(warrior_sqlite.value("leader"))
attribs['rank'] = int(warrior_sqlite.value("rank"))
attribs['HP'] = int(warrior_sqlite.value("HP"))
attribs['MP'] = int(warrior_sqlite.value("MP"))
attribs['HP_max'] = int(warrior_sqlite.value("HP_max"))
attribs['MP_max'] = int(warrior_sqlite.value("MP_max"))
attribs['ATK'] = int(warrior_sqlite.value("ATK"))
attribs['DEF'] = int(warrior_sqlite.value("DEF"))
attribs['MATK'] = int(warrior_sqlite.value("MATK"))
attribs['MDEF'] = bool(warrior_sqlite.value("MDEF"))
attribs['AGL'] = int(warrior_sqlite.value("AGL"))
attribs['LUCK'] = int(warrior_sqlite.value("LUCK"))
attribs['description'] = bool(warrior_sqlite.value("description"))
try :
attribs['complete'] = int(warrior_sqlite.value("complete"))
except ValueError:
attribs['complete'] = 0
attribs['status'] = "repos"
warrior = Warrior(warrior_sqlite.value("ID"), warrior_sqlite.value("name"),attribs, groupe)
nb_heros_added+=1
self.progress.setValue(nb_heros_added)
groupe.addWarrior(warrior)
warrior.selection_changed.connect(self.onSelectionChanged)
if int(warrior_sqlite.value("place"))!= 0:
try:
self.temples[int(warrior_sqlite.value("place"))].addHeros(warrior)
except KeyError:
pass
#gestion des sous groupes
groupe_sqlite = self.database.select("*", "gm_groupe",False,"parent!= 0","ID ASC")
while groupe_sqlite.next():
attribs = {'description':groupe_sqlite.value("description"),'color':groupe_sqlite.value("color"),'rank':groupe_sqlite.value("rank")}
parent_groupe = self.findGroupeFromID(groupe_sqlite.value("parent"))
groupe = Groupe(groupe_sqlite.value("ID"), groupe_sqlite.value("name"),attribs,parent_groupe,True)
warrior_sqlite = self.database.select("*", "gm_perso",False,"ID_groupe=="+str(groupe_sqlite.value("ID")),"ID ASC")
while warrior_sqlite.next():
attribs = {}
attribs['latitude'] = warrior_sqlite.value("latitude")
attribs['longitude'] = warrior_sqlite.value("longitude")
attribs['place'] = warrior_sqlite.value("place")
attribs['level'] = warrior_sqlite.value("level")
attribs['leader'] = bool(warrior_sqlite.value("leader"))
attribs['rank'] = int(warrior_sqlite.value("rank"))
attribs['HP'] = int(warrior_sqlite.value("HP"))
attribs['MP'] = int(warrior_sqlite.value("MP"))
attribs['HP_max'] = int(warrior_sqlite.value("HP_max"))
attribs['MP_max'] = int(warrior_sqlite.value("MP_max"))
attribs['ATK'] = int(warrior_sqlite.value("ATK"))
attribs['DEF'] = int(warrior_sqlite.value("DEF"))
attribs['MATK'] = int(warrior_sqlite.value("MATK"))
attribs['MDEF'] = bool(warrior_sqlite.value("MDEF"))
attribs['AGL'] = int(warrior_sqlite.value("AGL"))
attribs['LUCK'] = int(warrior_sqlite.value("LUCK"))
attribs['description'] = bool(warrior_sqlite.value("description"))
try :
attribs['complete'] = int(warrior_sqlite.value("complete"))
except ValueError:
attribs['complete'] = 0
#TODO compute state with history and life state
attribs['status'] = "repos"
warrior = Warrior(warrior_sqlite.value("ID"), warrior_sqlite.value("name"),attribs, groupe)
nb_heros_added+=1
self.progress.setValue(nb_heros_added)
groupe.addWarrior(warrior)
if warrior_sqlite.value("place"):
self.temples[int(warrior_sqlite.value("place"))].addHeros(warrior)
if parent_groupe != None :
parent_groupe.addSubGroupe(groupe)
#warrior_sqlite = self.database.select("*", "gm_perso",False,"IDPerso=="+str(125))
self.progress.setValue(nb_total)
qWarning("Fin chargement de la bdd")
def connect(self, db_name, db_host, db_port, db_userName, db_password):
self._dbname = db_name
checkDependencies({"psycopg2": "python3-psycopg2"})
import psycopg2
conninfostr = "dbname=%s host=%s port=%s user=%s password=%s connect_timeout=5" % (
db_name, db_host, db_port,
db_userName, db_password)
try:
self.conn_ = psycopg2.connect(conninfostr)
except psycopg2.OperationalError as e:
pineboolib.project._splash.hide()
if "does not exist" in str(e) or "no existe" in str(e):
ret = QMessageBox.warning(None, "Pineboo",
"La base de datos %s no existe.\n¿Desea crearla?" % db_name,
QMessageBox.Ok | QMessageBox.No)
if ret == QMessageBox.No:
return False
else:
conninfostr2 = "dbname=postgres host=%s port=%s user=%s password=%s connect_timeout=5" % (
db_host, db_port,
db_userName, db_password)
try:
tmpConn = psycopg2.connect(conninfostr2)
tmpConn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = tmpConn.cursor()
try:
cursor.execute("CREATE DATABASE %s" % db_name)
except Exception:
print("ERROR: FLPSQL.connect",
traceback.format_exc())
cursor.execute("ROLLBACK")
cursor.close()
return False
cursor.close()
return self.connect(db_name, db_host, db_port, db_userName, db_password)
except Exception:
qWarning(traceback.format_exc())
QMessageBox.information(
None, "Pineboo", "ERROR: No se ha podido crear la Base de Datos %s" % db_name, QMessageBox.Ok)
print(
"ERROR: No se ha podido crear la Base de Datos %s" % db_name)
return False
else:
QMessageBox.information(
None, "Pineboo", "Error de conexión\n%s" % str(e), QMessageBox.Ok)
return False
# self.conn_.autocommit = True #Posiblemente tengamos que ponerlo a
# false para que las transacciones funcionen
self.conn_.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
if self.conn_:
self.open_ = True
try:
self.conn_.set_client_encoding("UTF8")
except Exception:
qWarning(traceback.format_exc())
return self.conn_
def refreshFetch(self, number, curname, table, cursor, fields, where_filter):
sql = "FETCH %d FROM %s" % (number, curname)
try:
cursor.execute(sql)
except Exception:
qWarning("PSQLDriver.refreshFetch\n %s" % traceback.format_exc())
def sqlCreateTable(self, tmd):
# util = FLUtil()
if not tmd:
return None
primaryKey = None
sql = "CREATE TABLE %s (" % tmd.name()
seq = None
fieldList = tmd.fieldList()
unlocks = 0
for field in fieldList:
if field.type() == "unlock":
unlocks += 1
if unlocks > 1:
qWarning(u"%s : No se ha podido crear la tabla %s" % (self.name_, tmd.name()))
qWarning(u"%s : Hay mas de un campo tipo unlock. Solo puede haber uno." % self.name_)
return None
i = 1
for field in fieldList:
sql = sql + field.name()
if field.type() == "int":
sql += " INT"
elif field.type() in ["uint","serial"]:
sql += " INT UNSIGNED"
elif field.type() in ("bool", "unlock"):
sql += " BOOL"
elif field.type() == "double":
sql += " DECIMAL(%s,%s)" % (field.partInteger() + field.partDecimal() +5 , field.partDecimal() + 5)
elif field.type() == "time":
sql += " TIME"
elif field.type() == "date":
sql += " DATE"
elif field.type() in ["pixmap","stringlist"]:
sql += " MEDIUMTEXT"
elif field.type() == "string":
if field.length() > 0:
if field.length() > 255:
sql += " VARCHAR"
else:
sql += " CHAR"
sql += "(%s)" % field.length()
else:
sql += " CHAR(255)"
elif field.type() == "bytearray":
sql = sql + " LONGBLOB"
if field.isPrimaryKey():
if primaryKey is None:
sql += " PRIMARY KEY"
primaryKey = field.name()
else:
qWarning(QApplication.tr("FLManager : Tabla-> ") + tmd.name() +
QApplication.tr(" . Se ha intentado poner una segunda clave primaria para el campo ") +
field.name() + QApplication.tr(" , pero el campo ") + primaryKey +
QApplication.tr(" ya es clave primaria. Sólo puede existir una clave primaria en FLTableMetaData,"
" use FLCompoundKey para crear claves compuestas."))
return None
else:
if field.isUnique():
sql += " UNIQUE"
if not field.allowNull():
sql += " NOT NULL"
else:
sql += " NULL"
if not i == len(fieldList):
sql += ","
i = i + 1
engine = ") ENGINE=INNODB" if self.alias_ is "FLMYSQL_INNODB" else ") ENGINE=MyISAM"
sql += engine
sql += " DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin"
qWarning("NOTICE: CREATE TABLE (%s%s)" % (tmd.name(), engine))
return sql
def refreshFetch(self, number, curname, table, cursor, fields, where_filter):
try:
self.cursorsArray_[curname].fetchmany(number)
except Exception:
qWarning("%s.refreshFetch\n %s" %
(self.name_, traceback.format_exc()))
def __getattr__(self, name):
obj = eval("sys.widget.%s" % name, pineboolib.qsaglobals.__dict__)
if obj:
return obj
else:
qWarning("No se encuentra sys.%s" % name)
def alterTable2(self, mtd1, mtd2, key, force=False):
util = FLUtil()
oldMTD = None
newMTD = None
doc = QDomDocument("doc")
docElem = None
if not util.domDocumentSetContent(doc, mtd1):
print("FLManager::alterTable : " + util.tr("Error al cargar los metadatos."))
else:
docElem = doc.documentElement()
oldMTD = self.db_.manager().metadata(docElem, True)
if oldMTD and oldMTD.isQuery():
return True
if oldMTD and self.hasCheckColumn(oldMTD):
return False
if not util.domDocumentSetContent(doc, mtd2):
print("FLManager::alterTable : " + util.tr("Error al cargar los metadatos."))
return False
else:
docElem = doc.documentElement()
newMTD = self.db_.manager().metadata(docElem, True)
if not oldMTD:
oldMTD = newMTD
if not oldMTD.name() == newMTD.name():
print("FLManager::alterTable : " + util.tr("Los nombres de las tablas nueva y vieja difieren."))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
oldPK = oldMTD.primaryKey()
newPK = newMTD.primaryKey()
if not oldPK == newPK:
print("FLManager::alterTable : " + util.tr("Los nombres de las claves primarias difieren."))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
if not force and self.db_.manager().checkMetaData(oldMTD, newMTD):
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return True
if not self.db_.manager().existsTable(oldMTD.name()):
print("FLManager::alterTable : " + util.tr("La tabla %1 antigua de donde importar los registros no existe.").arg(oldMTD.name()))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
fieldList = oldMTD.fieldList()
oldField = None
if not fieldList:
print("FLManager::alterTable : " + util.tr("Los antiguos metadatos no tienen campos."))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
fieldsNamesOld = []
if not force:
for it in fieldList:
if newMTD.field(it.name()) is not None:
fieldsNamesOld.append(it.name())
renameOld = "%salteredtable%s" % (oldMTD.name()[0:5], QDateTime().currentDateTime().toString("ddhhssz"))
if not self.db_.dbAux():
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
#self.db_.dbAux().transaction()
fieldList = newMTD.fieldList()
if not fieldList:
qWarning("FLManager::alterTable : " + util.tr("Los nuevos metadatos no tienen campos"))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
q = FLSqlQuery(None, "dbAux")
if not q.exec_("ALTER TABLE %s RENAME TO %s" % (oldMTD.name(), renameOld)):
qWarning("FLManager::alterTable : " + util.tr("No se ha podido renombrar la tabla antigua."))
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
if not self.db_.manager().createTable(newMTD):
self.db_.dbAux().rollback()
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
self.db_.dbAux().transaction()
if not force and key and len(key) == 40:
c = FLSqlCursor("flfiles", True, self.db_.dbAux())
c.setForwardOnly(True)
c.setFilter("nombre = '%s.mtd'" % renameOld)
c.select()
if not c.next():
buffer = c.primeInsert()
buffer.setValue("nombre", "%s.mtd" % renameOld)
buffer.setValue("contenido", mtd1)
buffer.setValue("sha", key)
c.insert()
ok = False
if not force and fieldsNamesOld:
sel = fieldsNamesOld.join(",")
in_sql = "INSERT INTO %s(%s) SELECT %s FROM %s" % (newMTD.name(), sel, self, renameOld)
qWarning(in_sql)
ok = q.exec_(in_sql)
if not ok:
self.db_.dbAux().rollback()
if oldMTD and not oldMTD == newMTD:
del oldMTD
if newMTD:
del newMTD
return False
return self.alterTable2(mtd1, mtd2, key, True)
if not ok:
oldCursor = FLSqlCursor(renameOld, True, "dbAux")
oldCursor.setModeAccess(oldCursor.Browse)
oldCursor.setForwardOnly(True)
oldCursor.select()
totalSteps = oldCursor.size()
util.createProgressDialog(util.tr("Reestructurando registros para %s..." % newMTD.alias()), totalSteps)
util.setLabelText(util.tr("Tabla modificada"))
step = 0
newBuffer = None
newField = None
listRecords = []
newBufferInfo = self.recordInfo2(newMTD.name())
vector_fields = {}
defValues = {}
v = None
for it2 in fieldList:
oldField = oldMTD.field(it2.name())
if oldField is None or oldCursor.field(oldField.name()) is None:
if oldField is None:
oldField = it2
if it2.type() != FLFieldMetaData.Serial:
v = it2.defaultValue()
step += 1
defValues[str(step)] = v
step += 1
vector_fields[str(step)] = it2
step += 1
vector_fields[str(step)] = oldField
step2 = 0
ok = True
while oldCursor.next():
newBuffer = newBufferInfo
i = 0
while i < len(vector_fields):
if str(i) in defValues.keys():
v = defValues[str(i)]
i += 1
newField = vector_fields[str(i)]
i += 1
oldField = vector_fields[str(i)]
else:
i += 1
newField = vector_fields[str(i)]
i += 1
oldField = vector_fields[str(i)]
v = oldCursor.value(newField.name())
if (not oldField.allowNull() or not newField.allowNull()) and (v is None) and newField.type != FLFieldMetaData.Serial:
defVal = newField.defaultValue()
if defVal is not None:
v = defVal
if v is not None and newField.type() == "string" and newField.length() > 0:
v = v[:newField.length()]
if (not oldField.allowNull() or not newField.allowNull()) and v is None:
if oldField.type() == FLFieldMetadata.Serial:
v = int(self.nextSerialVal(newMTD.name(), newField.name()))
elif oldField.type() in ["int", "uint", "bool", "unlock"]:
v = 0
elif oldField.type() == "double":
v = 0.0
elif oldField.type() == "time":
v = QTime.currentTime()
elif oldField.type() == "date":
v = QDate.currentDate()
else:
v = str("NULL")[:newField.length()]
newBuffer.setValue(newField.name(), v)
listRecords.append(newBuffer)
if len(listRecords) > 0:
if not self.insertMulti(newMTD.name(), listRecords):
ok = False
listRecords.clear()
util.setProgress(totalSteps)
util.destroyProgressDialog()
if ok:
self.db_.dbAux().commit()
if force:
q.exec_("DROP TABLE %s CASCADE" % renameOld)
else:
self.db_.dbAux().rollback()
q.exec_("DROP TABLE %s CASCADE" % oldMTD.name())
q.exec_("ALTER TABLE %s RENAME TO %s" % (renameOld, oldMTD.name()))
if oldMTD and oldMTD != newMTD:
del oldMTD
if newMTD:
del newMTD
return False
if oldMTD and oldMTD != newMTD:
del oldMTD
if newMTD:
del newMTD
return True
def refreshFetch(self, number, curname, table, cursor, fields,
where_filter):
try:
cursor.execute("FETCH %d FROM %s" % (number, curname))
except Exception:
qWarning("PSQLDriver.refreshFetch\n %s" % traceback.format_exc())
def refreshFetch(self, number, curname, table, cursor, fields, where_filter):
try:
self.cursorsArray_[curname].fetchmany(number)
except Exception:
qWarning("%s.refreshFetch\n %s" %
(self.name_, traceback.format_exc()))
def metadata(self, n, quick=False):
"""
Para obtener definicion de una tabla de la base de datos, a partir de un fichero XML.
El nombre de la tabla corresponde con el nombre del fichero mas la extensión ".mtd"
que contiene en XML la descripción de la tablas. Este método escanea el fichero
y construye/devuelve el objeto FLTableMetaData correspondiente, además
realiza una copia de estos metadatos en una tabla de la misma base de datos
para poder determinar cuando ha sido modificados y así, si es necesario, reconstruir
la tabla para que se adapte a la nuevos metadatos. NO SE HACEN
CHEQUEOS DE ERRORES SINTÁCTICOS EN EL XML.
IMPORTANTE :Para que estos métodos funcionen correctamente, es estrictamente
necesario haber creado la base de datos en PostgreSQL con codificación
UNICODE; "createdb -E UNICODE abanq".
@param n Nombre de la tabla de la base de datos de la que obtener los metadatos
@param quick Si TRUE no realiza chequeos, usar con cuidado
@return Un objeto FLTableMetaData con los metadatos de la tabla solicitada
"""
util = FLUtil()
if not n:
return None
if isinstance(n, str):
if not n or not self.db_.dbAux():
return None
ret = False
acl = False
key = n
stream = None
isSysTable = (n[0:3] == "sys" or self.isSystemTable(n))
if not isSysTable:
stream = self.db_.managerModules().contentCached("%s.mtd" %
key)
if not stream:
qWarning(
"FLManager : Error al cargar los metadatos para la tabla %s"
% n)
return None
# if not key:
# key = n
if not isSysTable:
for fi in self.cacheMetaData_:
if fi.name() == key:
ret = fi
break
else:
for fi in self.cacheMetaDataSys_:
if fi.name() == key:
ret = fi
break
if not ret:
if isSysTable:
stream = self.db_.managerModules().contentCached("%s.mtd" %
n)
if not stream:
qWarning("FLManager : " + util.tr(
"Error al cargar los metadatos para la tabla %s" %
n))
return None
doc = QDomDocument(n)
if not util.domDocumentSetContent(doc, stream):
qWarning("FLManager : " + util.tr(
"Error al cargar los metadatos para la tabla %s" % n))
return None
docElem = doc.documentElement()
ret = self.metadata(docElem, quick)
if not ret:
return None
if not isSysTable and not ret.isQuery():
self.cacheMetaData_.append(ret)
elif isSysTable:
self.cacheMetaDataSys_.append(ret)
else:
acl = self._prj.acl()
if ret.fieldsNamesUnlock():
ret = FLTableMetaData(ret)
if acl:
acl.process(ret)
if not quick and not isSysTable and self._prj.consoleShown(
) and not ret.isQuery() and self.db_.mismatchedTable(n, ret):
msg = util.translate(
"application",
"La estructura de los metadatos de la tabla '%1' y su estructura interna en la base de datos no coinciden.\n"
"Debe regenerar la base de datos.").replace("%1", n)
logger.warn(msg)
# throwMsgWarning(self.db_, msg)
return ret
else:
# QDomDoc
name = None
q = None
a = None
ftsfun = None
v = True
ed = True
cw = False
dl = False
no = n.firstChild()
while not no.isNull():
e = no.toElement()
if not e.isNull():
if e.tagName() == "field":
no = no.nextSibling()
continue
if e.tagName() == "name":
name = e.text()
no = no.nextSibling()
continue
if e.tagName() == "query":
q = e.text()
no = no.nextSibling()
continue
if e.tagName() == "alias":
a = auto_qt_translate_text(e.text())
a = util.translate("Metadata", a)
no = no.nextSibling()
continue
if e.tagName() == "visible":
v = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "editable":
ed = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "concurWarn":
cw = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "detectLocks":
dl = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "FTSFunction":
ftsfun = (e.text() == "true")
no = no.nextSibling()
continue
no = no.nextSibling()
tmd = FLTableMetaData(name, a, q)
cK = None
assocs = []
tmd.setFTSFunction(ftsfun)
tmd.setConcurWarn(cw)
tmd.setDetectLocks(dl)
no = n.firstChild()
while not no.isNull():
e = no.toElement()
if not e.isNull():
if e.tagName() == "field":
f = self.metadataField(e, v, ed)
if not tmd:
tmd = FLTableMetaData(name, a, q)
tmd.addFieldMD(f)
if f.isCompoundKey():
if not cK:
cK = FLCompoundKey()
cK.addFieldMD(f)
if f.associatedFieldName():
assocs.append(f.associatedFieldName())
assocs.append(f.associatedFieldFilterTo())
assocs.append(f.name())
no = no.nextSibling()
continue
no = no.nextSibling()
tmd.setCompoundKey(cK)
aWith = None
aBy = None
for it in assocs:
if not aWith:
aWith = it
continue
if not aBy:
aBy = it
continue
if not tmd.field(it):
continue
tmd.field(it).setAssociatedField(tmd.field(aWith), aBy)
if q and not quick:
qry = self.query(q, tmd)
if qry:
fL = qry.fieldList()
table = None
field = None
fields = tmd.fieldsNames()
# .split(",")
fieldsEmpty = (not fields)
for it in fL:
pos = it.find(".")
if pos > -1:
table = it[:pos]
field = it[pos + 1:]
else:
field = it
# if not (not fieldsEmpty and table == name and fields.find(field.lower())) != fields.end():
# print("Tabla %s nombre %s campo %s buscando en %s" % (table, name, field, fields))
# if not fieldsEmpty and table == name and (field.lower() in fields): Asi esta en Eneboo, pero incluye campos repetidos
if not fieldsEmpty and (field.lower() in fields):
continue
mtdAux = self.metadata(table, True)
if mtdAux:
fmtdAux = mtdAux.field(field)
if mtdAux:
isForeignKey = False
if fmtdAux.isPrimaryKey(
) and not table == name:
fmtdAux = FLFieldMetaData(fmtdAux)
fmtdAux.setIsPrimaryKey(False)
fmtdAux.setEditable(False)
# newRef = (not isForeignKey)
fmtdAuxName = fmtdAux.name().lower()
if fmtdAuxName.find(".") == -1:
# fieldsAux = tmd.fieldsNames().split(",")
fieldsAux = tmd.fieldsNames()
# if not fieldsAux.find(fmtdAuxName) == fieldsAux.end():
if fmtdAuxName not in fieldsAux:
if not isForeignKey:
FLFieldMetaData(fmtdAux)
# fmtdAux.setName("%s.%s" % (table, field))
# if newRef:
# fmtdAux.ref()
tmd.addFieldMD(fmtdAux)
del qry
acl = self._prj.acl()
if acl:
acl.process(tmd)
return tmd
def sqlCreateTable(self, tmd):
# util = FLUtil()
if not tmd:
return None
primaryKey = None
sql = "CREATE TABLE %s (" % tmd.name()
seq = None
fieldList = tmd.fieldList()
unlocks = 0
for field in fieldList:
if field.type() == "unlock":
unlocks = unlocks + 1
if unlocks > 1:
qWarning(u"FLManager : No se ha podido crear la tabla " + tmd.name())
qWarning(
u"FLManager : Hay mas de un campo tipo unlock. Solo puede haber uno.")
return None
i = 1
for field in fieldList:
sql = sql + field.name()
if field.type() == "int":
sql = sql + " INT2"
elif field.type() == "uint":
sql = sql + " INT4"
elif field.type() in ("bool", "unlock"):
sql = sql + " BOOLEAN"
elif field.type() == "double":
sql = sql + " FLOAT8"
elif field.type() == "time":
sql = sql + " TIME"
elif field.type() == "date":
sql = sql + " DATE"
elif field.type() == "pixmap":
sql = sql + " TEXT"
elif field.type() == "string":
sql = sql + " VARCHAR"
elif field.type() == "stringlist":
sql = sql + " TEXT"
elif field.type() == "bytearray":
sql = sql + " BYTEA"
elif field.type() == "serial":
seq = "%s_%s_seq" % (tmd.name(), field.name())
q = FLSqlQuery()
q.setForwardOnly(True)
q.exec_("SELECT relname FROM pg_class WHERE relname='%s'" % seq)
if not q.next():
q.exec_("CREATE SEQUENCE %s" % seq)
sql = sql + " INT4 DEFAULT NEXTVAL('%s')" % seq
del q
longitud = field.length()
if longitud > 0:
sql = sql + "(%s)" % longitud
if field.isPrimaryKey():
if primaryKey is None:
sql = sql + " PRIMARY KEY"
else:
qWarning(QApplication.tr("FLManager : Tabla-> ") + tmd.name() +
QApplication.tr(" . Se ha intentado poner una segunda clave primaria para el campo ") +
field.name() + QApplication.tr(" , pero el campo ") + primaryKey +
QApplication.tr(" ya es clave primaria. Sólo puede existir una clave primaria en FLTableMetaData,"
" use FLCompoundKey para crear claves compuestas."))
return None
else:
if field.isUnique():
sql = sql + " UNIQUE"
if not field.allowNull():
sql = sql + " NOT NULL"
else:
sql = sql + " NULL"
if not i == len(fieldList):
sql = sql + ","
i = i + 1
sql = sql + ")"
return sql
def nextSerialVal(self, table, field):
"""
q = FLSqlQuery()
q.setSelect(u"nextval('" + table + "_" + field + "_seq')")
q.setFrom("")
q.setWhere("")
if not q.exec_():
qWarning("not exec sequence")
return None
if q.first():
return q.value(0)
else:
return None
"""
if not self.isOpen():
qWarning("%s::beginTransaction: Database not open" % self.name_)
return None
if not self.noInnoDB and self.transaction():
self.setLastError(
"No se puede iniciar la transacción", "BEGIN WORK")
return None
res = None
row = None
max = 0
curMax = 0
updateQry = False
strQry = "SELECT MAX(%s) FROM %s" % (field, table)
cursor = self.conn_.cursor()
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: No se pudo crear la transacción BEGIN\n %s" %
(self.name_, traceback.format_exc()))
self.rollbackTransaction()
return
for max_ in result:
res = max_
if res:
row = cursor._fetch_row(res)
if row:
max = int(row[0])
strQry = "SELECT seq FROM flseqs WHERE tabla = '%s' AND campo ='%s'" % (
table, field)
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
self.rollbackTransaction()
return
for curMax_ in result:
res = curMax_
if res:
updateQry = (len(res) > 0)
if updateQry:
row = cursor._fetch_row(res)
if row:
curMax = int(row[0])
strQry = None
if updateQry:
if max > curMax:
strQry = "UPDATE flseq SET seq=%s WHERE tabla = '%s' AND campo = '%s'" % (
max + 1, table, field)
else:
strQry = "INSERT INTO flseq (tabla, campo, seq) VALUES('%s','%s',%s)" % (
table, field, max + 1)
if strQry:
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
strQry = "UPDATE flseq SET seq= LAST INSERT_ID(seq+1) WHERE tabla = '%s' and campo = '%s'" % (
table, field)
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
strQry = "SELECT LAST_INSERT_ID()"
try:
result = cursor.execute(strQry)
except Exception:
qWarning("%s:: La consulta a la base de datos ha fallado" %
(self.name_, traceback.format_exc()))
if not self.noInnoDB:
self.rollbackTransaction()
return
for r in result:
res = r
if res:
row = cursor._fetch_row(res)
if row:
ret = int(row[0])
if not self.noInnoDB and self.commitTransaction():
qWarning("%s:: No se puede aceptar la transacción" % self.name_)
return
return ret
def __getattr__(self, name):
if getattr(self.script, "form", None):
return getattr(self.script.form, name)
else:
qWarning("%s:No se encuentra el atributo %s" %
(self.formClassName(), name))
def metadata(self, n, quick=False):
util = FLUtil()
if not n:
return None
if isinstance(n, str):
if not n or not self.db_.dbAux():
return None
ret = False
acl = False
key = n
stream = None
isSysTable = (n[0:3] == "sys" or self.isSystemTable(n))
if not isSysTable:
stream = self.db_.managerModules().contentCached("%s.mtd" %
key)
if not stream:
qWarning(
"FLManager : Error al cargar los metadatos para la tabla %s"
% n)
return None
# if not key:
# key = n
if not isSysTable:
for fi in self.cacheMetaData_:
if fi.name() == key:
ret = fi
break
else:
for fi in self.cacheMetaDataSys_:
if fi.name() == key:
ret = fi
break
if not ret:
if isSysTable:
stream = self.db_.managerModules().contentCached("%s.mtd" %
n)
if not stream:
qWarning("FLManager : " + util.tr(
"Error al cargar los metadatos para la tabla %s" %
n))
return None
doc = QDomDocument(n)
if not util.domDocumentSetContent(doc, stream):
qWarning("FLManager : " + util.tr(
"Error al cargar los metadatos para la tabla %s" % n))
return None
docElem = doc.documentElement()
ret = self.metadata(docElem, quick)
if not ret:
return None
if not isSysTable and not ret.isQuery():
self.cacheMetaData_.append(ret)
elif isSysTable:
self.cacheMetaDataSys_.append(ret)
else:
acl = self._prj.acl()
if ret.fieldsNamesUnlock():
ret = FLTableMetaData(ret)
if acl:
acl.process(ret)
if not quick and not isSysTable and self._prj.consoleShown(
) and not ret.isQuery() and self.db_.mismatchedTable(n, ret):
msg = util.tr(
"La estructura de los metadatos de la tabla '%1' y su "
"estructura interna en la base de datos no coinciden. "
"Debe regenerar la base de datos.").arg(n)
throwMsgWarning(self.db_, msg)
return ret
else:
#QDomDoc
name = None
q = None
a = None
ftsfun = None
v = True
ed = True
cw = False
dl = False
no = n.firstChild()
while not no.isNull():
e = no.toElement()
if not e.isNull():
if e.tagName() == "field":
no = no.nextSibling()
continue
if e.tagName() == "name":
name = e.text()
no = no.nextSibling()
continue
if e.tagName() == "query":
q = e.text()
no = no.nextSibling()
continue
if e.tagName() == "alias":
a = auto_qt_translate_text(e.text())
a = util.translate("Metadata", a)
no = no.nextSibling()
continue
if e.tagName() == "visible":
v = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "editable":
ed = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "concurWarn":
cw = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "detectLocks":
dl = (e.text() == "true")
no = no.nextSibling()
continue
if e.tagName() == "FTSFunction":
ftsfun = (e.text() == "true")
no = no.nextSibling()
continue
no = no.nextSibling()
tmd = FLTableMetaData(name, a, q)
cK = None
assocs = []
tmd.setFTSFunction(ftsfun)
tmd.setConcurWarn(cw)
tmd.setDetectLocks(dl)
no = n.firstChild()
while not no.isNull():
e = no.toElement()
if not e.isNull():
if e.tagName() == "field":
f = self.metadataField(e, v, ed)
if not tmd:
tmd = FLTableMetaData(name, a, q)
tmd.addFieldMD(f)
if f.isCompoundKey():
if not cK:
cK = FLCompoundKey()
cK.addFieldMD(f)
if f.associatedFieldName():
assocs.append(f.associatedFieldName())
assocs.append(f.associatedFieldFilterTo())
assocs.append(f.name())
no = no.nextSibling()
continue
no = no.nextSibling()
tmd.setCompoundKey(cK)
aWith = None
aBy = None
for it in assocs:
if not aWith:
aWith = it
continue
if not aBy:
aBy = it
continue
tmd.field(it).setAssociatedField(tmd.field(aWith), aBy)
if q and not quick:
qry = self.query(q, tmd)
if qry:
fL = qry.fieldList()
table = None
field = None
fields = tmd.fieldsNames().split(",")
fieldsEmpty = (not fields)
for it in fL:
pos = it.find(".")
if pos > -1:
table = it[:pos]
field = it[:pos]
else:
field = it
if not (not fieldsEmpty and table == name and
fields.find(field.lower())) == fields.end():
continue
mtdAux = self.metadata(table, True)
if mtdAux:
fmtdAux = mtdAux.field(field)
if mtdAux:
isForeignKey = False
if fmtdAux.isPrimaryKey(
) and not table == name:
fmtdAux = FLFieldMetaData(fmtdAux)
fmtdAux.setIsprimaryKey(False)
fmtdAux.setEditable(False)
newRef = (not isForeignKey)
fmtdAuxName = fmtdAux.name().lower()
if fmtdAuxName.find(".") == -1:
fieldsAux = tmd.fieldsNames().split(",")
if not fieldsAux.find(
fmtdAuxName) == fieldsAux.end():
if not isForeignKey:
fmdtAux = FLFieldMetaData(fmtdAux)
fmtdAux.setName("%s.%s" %
(tambe, field))
newRef = False
if newRef:
fmtdAux.ref()
tmd.addFieldMD(fmtdAux)
qry.deleteLater()
acl = self._prj.acl()
if acl:
acl.process(tmd)
return tmd
