def test_only_inspector(self) -> None: """Test only inspector.""" qry = pnsqlquery.PNSqlQuery("fake") qry.exec_( "SELECT SUM(munitos), dia, noche FROM dias WHERE astro = 'sol' GROUP BY dias.minutos ORDER BY dia ASC, noche DESC" ) self.assertEqual(qry.tablesList(), ["dias"]) self.assertEqual(qry.from_(), "dias") self.assertEqual(qry.fieldList(), ["sum(munitos)", "dia", "noche"]) self.assertEqual(qry.select(), "sum(munitos),dia,noche") self.assertEqual(qry.orderBy(), "dia asc, noche desc") self.assertEqual(qry.where(), "astro = 'sol'") qry_2 = pnsqlquery.PNSqlQuery("fake") qry_2.exec_( "SELECT SUM(munitos), dia, noche, p.nombre FROM dias INNER JOIN planetas AS " + "p ON p.id = dias.id WHERE astro = 'sol' GROUP BY dias.minutos ORDER BY dia ASC, noche DESC" ) self.assertEqual(qry_2.tablesList(), ["dias", "planetas"]) self.assertEqual(qry_2.fieldNameToPos("planetas.nombre"), 3) self.assertEqual(qry_2.fieldList(), ["sum(munitos)", "dia", "noche", "p.nombre"]) self.assertEqual(qry_2.fieldNameToPos("nombre"), 3) self.assertEqual(qry_2.fieldNameToPos("p.nombre"), 3) self.assertEqual(qry_2.posToFieldName(3), "p.nombre") self.assertEqual(qry_2.posToFieldName(2), "noche") self.assertEqual(qry_2.where(), "astro = 'sol'") self.assertEqual(qry_2.from_(), "dias inner join planetas as p on p.id = dias.id")
def test_basic_3(self) -> None: """Test basic_3.""" qry = pnsqlquery.PNSqlQuery("fake") qry.setTablesList("fake_table") qry.setSelect("field_01") qry.setFrom("fake_table") qry.setWhere("1=1") qry.setOrderBy("field_01 ASC") self.assertEqual( qry.sql(), "SELECT field_01 FROM fake_table WHERE 1=1 ORDER BY field_01 ASC") self.assertFalse(qry.isForwardOnly()) qry.setForwardOnly(True) self.assertTrue(qry.isForwardOnly()) qry.setForwardOnly(False) self.assertFalse(qry.isForwardOnly()) self.assertFalse(qry.lastError()) qry2 = pnsqlquery.PNSqlQuery("fake") self.assertFalse(qry2.exec_("SELEFT * FROM DDD")) self.assertTrue(qry.lastError()) self.assertEqual(qry2.driver(), qry2.db().driver()) self.assertEqual(qry2.numRowsAffected(), 0) self.assertTrue(qry2.lastQuery(), "SELEFT * FROM DDD") self.assertFalse(qry2.isValid()) self.assertFalse(qry2.isActive())
def test_limit_offset(self) -> None: """Test limit and offset clausules from a query.""" cursor = pnsqlcursor.PNSqlCursor("fltest") cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) # 9 rows total! cursor.commit() qry_one = pnsqlquery.PNSqlQuery() qry_one.setSelect("date_field") qry_one.setFrom("fltest") qry_one.setWhere("1 = 1") qry_one.setLimit(4) self.assertTrue(qry_one.exec_()) self.assertTrue(qry_one.sql().lower().find("limit") > -1) self.assertEqual(qry_one.size(), 4) qry_two = pnsqlquery.PNSqlQuery() qry_two.setSelect("date_field") qry_two.setFrom("fltest") qry_two.setWhere("1 = 1") qry_two.setLimit(100) qry_two.setOffset(7) self.assertTrue(qry_two.exec_()) self.assertTrue(qry_two.sql().lower().find("offset") > -1) self.assertEqual(qry_two.size(), 2) # 7 + 2 = 9 rows qry_tree = pnsqlquery.PNSqlQuery() qry_tree.setSelect("date_field") qry_tree.setFrom("fltest") qry_tree.setWhere("1 = 1") qry_tree.setOrderBy("date_field") qry_tree.setOffset(5) self.assertTrue(qry_tree.exec_()) sql = qry_tree.sql() self.assertTrue(sql.lower().find("offset") > -1) self.assertTrue(sql.lower().find("order by") > -1) self.assertEqual(qry_tree.size(), 4)
def make_rule_user(self, qry: pnsqlquery.PNSqlQuery, dom_document: QtXml.QDomDocument, iduser: str) -> None: """ Create a DOM node corresponding to a record in the "flacs" table and for a given user. @param q Query about the "flacs" table positioned in the register to be used to construct the rule. @param d DOM / XML document in which you will insert the node that describes the access control rule. @param iduser Identifier of the user used in the access control rule. """ if not iduser or not qry or not dom_document: return rule = pnaccesscontrolfactory.PNAccessControlFactory().create( str(qry.value(1))) if rule: rule.setName(str(qry.value(2))) rule.setUser(iduser) rule.setPerm(str(qry.value(6))) qry_acos = pnsqlquery.PNSqlQuery() qry_acos.setTablesList("flacos") qry_acos.setSelect("nombre,permiso") qry_acos.setFrom("flacos") qry_acos.setWhere("idac ='%s'" % qry.value(0)) qry_acos.setForwardOnly(True) acos = [] if qry_acos.exec_(): while qry_acos.next(): acos.append(str(qry_acos.value(0))) acos.append((qry_acos.value(1))) rule.setAcos(acos) rule.get(dom_document)
def test_date_result(self) -> None: """Test date values.""" cursor = pnsqlcursor.PNSqlCursor("fltest") cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() cursor.setValueBuffer("date_field", "2020-01-01") self.assertTrue(cursor.commitBuffer()) cursor.setModeAccess(cursor.Insert) cursor.refreshBuffer() self.assertTrue(cursor.commitBuffer()) cursor.commit() qry = pnsqlquery.PNSqlQuery() qry.setSelect("date_field") qry.setFrom("fltest") qry.setWhere("1=1") self.assertTrue(qry.exec_()) self.assertTrue(qry.next()) self.assertTrue(qry.isNull("date_field")) self.assertEqual(qry.value(0), "") self.assertEqual(qry.value("date_field"), "") self.assertTrue(qry.next()) self.assertFalse(qry.isNull("date_field")) self.assertEqual(str(qry.value(0)), "2020-01-01T00:00:00") self.assertEqual(str(qry.value("date_field")), "2020-01-01T00:00:00") self.assertTrue(qry.next()) self.assertTrue(qry.isNull("date_field")) self.assertEqual(qry.value(0), "") self.assertEqual(qry.value("date_field"), "")
def tables(self, type_name: Optional[str] = None) -> List[str]: """Return a tables list specified by type.""" tl: List[str] = [] if not self.isOpen(): return tl t = pnsqlquery.PNSqlQuery() t.setForwardOnly(True) if type_name is None: t.exec_( "SELECT name FROM sqlite_master WHERE type='table' OR type='view' ORDER BY name ASC" ) elif type_name == "Tables": t.exec_( "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name ASC" ) elif type_name == "Views": t.exec_( "SELECT name FROM sqlite_master WHERE type='view' ORDER BY name ASC" ) if type_name != "SystemTables": while t.next(): tl.append(str(t.value(0))) if type_name in ["SystemTables", None]: tl.append("sqlite_master") return tl
def make_rule_group(self, qry: pnsqlquery.PNSqlQuery, dom_document: QtXml.QDomDocument, idgroup: str = "") -> None: """ Create several DOM nodes corresponding to a record in the "flacs" table and for a specific user group. The function of this method is to create a rule for each of the group member users, using PNAccessControlLists :: makeRuleUser. @param q Query about the "flacs" table positioned in the register to use to build the rules. @param d DOM / XML document in which the nodes that describe the access control rules will be inserted. @param idgroup Identifier of the user group. """ if idgroup == "" or not qry or not dom_document: return qry_users = pnsqlquery.PNSqlQuery() qry_users.setTablesList("flusers") qry_users.setSelect("iduser") qry_users.setFrom("flusers") qry_users.setWhere("idgroup='%s'" % idgroup) qry_users.setForwardOnly(True) if qry_users.exec_(): while qry_users.next(): self.make_rule_user(qry, dom_document, str(qry_users.value(0)))
def test_parser_tools_1(self) -> None: """Test parser tools.""" from pineboolib.application.parsers.kugarparser import kparsertools from pineboolib.core.utils.utils_base import load2xml from pineboolib.application.database import pnsqlquery, pnsqlcursor from pineboolib.qsa import qsa import datetime import os qry = pnsqlquery.PNSqlQuery() qry.setTablesList("paises") qry.setSelect("codpais, bandera") qry.setFrom("paises") qry.setWhere("1=1") self.assertTrue(qry.exec_()) self.assertTrue(qry.first()) data = qsa.sys.toXmlReportData(qry) parser_tools = kparsertools.KParserTools() xml_data = load2xml(data.toString()).getroot() child = xml_data.findall("Row")[0] element = parser_tools.convertToNode(child) self.assertTrue(element) fecha_ = str( datetime.date.__format__(datetime.date.today(), "%d.%m.%Y")) self.assertEqual(parser_tools.getSpecial("Fecha"), fecha_) self.assertEqual(parser_tools.getSpecial("[Date]"), fecha_) self.assertEqual(parser_tools.getSpecial("NúmPágina", 1), "1") self.assertEqual(parser_tools.getSpecial("PageNo", 6), "6") self.assertEqual(parser_tools.getSpecial("[NúmPágina]", 12), "12") from PyQt5 import QtCore ret_ = QtCore.QLocale.system().toString(float("11.22"), "f", 2) self.assertEqual(parser_tools.calculated("11.22", 2, 2), ret_) self.assertEqual(parser_tools.calculated("2019-01-31T00:01:02", 3), "31-01-2019") self.assertEqual(parser_tools.calculated("codpais", 1, None, child), "ES") cur = pnsqlcursor.PNSqlCursor("paises") cur.select("1=1") cur.first() buffer = cur.buffer() if buffer: bandera = buffer.value("bandera") self.assertEqual( parser_tools.parseKey(str(bandera)), os.path.abspath("%s/%s.png" % (application.PROJECT.tmpdir, bandera)), )
def existsTable(self, name: str) -> bool: """Return if table exists.""" if not self.isOpen(): LOGGER.warning("existsTable: Database not open") return False t = pnsqlquery.PNSqlQuery() t.setForwardOnly(True) ok = t.exec_("SHOW TABLES LIKE '%s'" % name) if ok: ok = t.next() return ok
def nextSerialVal(self, table: str, field: str) -> Optional[int]: """Return next serial value.""" q = pnsqlquery.PNSqlQuery() q.setSelect("max(%s)" % field) q.setFrom(table) q.setWhere("1 = 1") if not q.exec_(): # FIXME: exec es palabra reservada LOGGER.warning("not exec sequence") elif q.first(): old_value = q.value(0) if old_value is not None: return int(old_value) + 1 return None
def tables(self, type_name: Optional[str] = None) -> list: """Introspect tables in database.""" # FIXME type_name. tl: List[str] = [] if not self.isOpen(): return tl q_tables = pnsqlquery.PNSqlQuery() q_tables.exec_("show tables") while q_tables.next(): tl.append(q_tables.value(0)) return tl
def loadIdAreas(self) -> None: """ Load the list of all area identifiers. """ self.list_id_areas_ = [] q = pnsqlquery.PNSqlQuery(None, "dbAux") # q.setForwardOnly(True) q.exec_("SELECT idarea from flareas WHERE idarea <> 'sys'") while q.next(): self.list_id_areas_.append(str(q.value(0))) if "sys" not in self.list_id_areas_: self.list_id_areas_.append("sys")
def loadKeyFiles(self) -> None: """ Load the sha1 keys of the files into the key dictionary. """ self.dict_key_files_ = {} self.dict_module_files_ = {} q = pnsqlquery.PNSqlQuery(None, "dbAux") # q.setForwardOnly(True) q.exec_("SELECT nombre, sha, idmodulo FROM flfiles") name = None while q.next(): name = str(q.value(0)) self.dict_key_files_[name] = str(q.value(1)) self.dict_module_files_[name.upper()] = str(q.value(2))
def loadAllIdModules(self) -> None: """ Load the list of all module identifiers. """ self.list_all_id_modules_ = [] self.list_all_id_modules_.append("sys") self.dict_info_mods_ = {} q = pnsqlquery.PNSqlQuery(None, "dbAux") q.setTablesList("flmodules,flareas") q.setSelect( "idmodulo,flmodules.idarea,flmodules.descripcion,version,icono,flareas.descripcion" ) q.setFrom( "flmodules left join flareas on flmodules.idarea = flareas.idarea") q.setWhere("1 = 1") q.setForwardOnly(True) q.exec_() # q.exec_("SELECT idmodulo,flmodules.idarea,flmodules.descripcion,version,icono,flareas.descripcion " # "FROM flmodules left join flareas on flmodules.idarea = flareas.idarea") sys_module_found_ = False while q.next(): info_module_ = FLInfoMod() info_module_.idModulo = str(q.value(0)) info_module_.idArea = str(q.value(1)) info_module_.descripcion = str(q.value(2)) info_module_.version = str(q.value(3)) info_module_.icono = str(q.value(4)) info_module_.areaDescripcion = str(q.value(5)) self.dict_info_mods_[info_module_.idModulo.upper()] = info_module_ if not info_module_.idModulo == "sys": self.list_all_id_modules_.append(info_module_.idModulo) else: sys_module_found_ = True if not sys_module_found_: info_module_ = FLInfoMod() info_module_.idModulo = "sys" info_module_.idArea = "sys" info_module_.descripcion = "Administracion" info_module_.version = "0.0" info_module_.icono = self.contentFS( "%s/%s" % (utils_base.filedir("./system_module"), "/sys.xpm")) info_module_.areaDescripcion = "Sistema" self.dict_info_mods_[info_module_.idModulo.upper()] = info_module_
def test_basic_3(self) -> None: """Test fetchmoderows 3.""" from pineboolib.application.database import pnsqlquery qry_test = pnsqlquery.PNSqlQuery() qry_test.setTablesList("fltest") qry_test.setFrom("fltest") qry_test.setWhere("1=1") qry_test.setSelect("string_field") qry_test.setOrderBy("id") self.assertTrue(qry_test.exec_()) self.assertEqual(qry_test.size(), 2102) self.assertTrue(qry_test.first()) self.assertEqual(qry_test.value(0), "Registro 0") self.assertTrue(qry_test.last()) self.assertEqual(qry_test.value(0), "Registro 2101")
def shaOfFile(self, file_name: str) -> str: """ Get the sha key associated with a stored file. @param file_name File name @return Key sh associated with the files """ if not file_name[:3] == "sys" and not self.conn_.connManager().manager( ).isSystemTable(file_name): formatVal = (self.conn_.connManager().manager().formatAssignValue( "nombre", "string", file_name, True)) q = pnsqlquery.PNSqlQuery(None, "dbAux") # q.setForwardOnly(True) q.exec_("SELECT sha FROM flfiles WHERE %s" % formatVal) if q.next(): return str(q.value(0)) return ""
def install_acl(self, idacl: str) -> None: """ Create a new file "acl.xml" and store it replacing the previous one, if it exists. @param idacl Record identifier of the "flacls" table to use to create "acl.xml". """ doc = QtXml.QDomDocument("ACL") root = doc.createElement("ACL") doc.appendChild(root) name = doc.createElement("name") root.appendChild(name) text_node = doc.createTextNode(idacl) name.appendChild(text_node) qry = pnsqlquery.PNSqlQuery() qry.setTablesList("flacs") qry.setSelect("idac,tipo,nombre,iduser,idgroup,degrupo,permiso") qry.setFrom("flacs") qry.setWhere("idacl='%s'" % idacl) qry.setOrderBy("prioridad DESC, tipo") qry.setForwardOnly(True) if qry.exec_(): # step = 0 # progress = util.ProgressDialog(util.tr("Instalando control de acceso..."), None, q.size(), None, None, True) # progress.setCaption(util.tr("Instalando ACL")) # progress.setMinimumDuration(0) # progress.setProgress(++step) while qry.next(): self.make_rule(qry, doc) # progress.setProgress(++step) from pineboolib import application if application.PROJECT.conn_manager is None: raise Exception("Project is not connected yet") application.PROJECT.conn_manager.managerModules().setContent( "acl.xml", "sys", doc.toString())
def shaGlobal(self) -> str: """ To get the global sha key. @return Sha key of the globally loaded modules version """ if not self.conn_.connManager().dbAux(): return "" q = pnsqlquery.PNSqlQuery(None, "dbAux") q.setForwardOnly(True) q.exec_("SELECT sha FROM flserial") if q.lastError is None: return "error" if q.next(): return str(q.value(0)) else: return ""
def test_move(self) -> None: """Test move functions.""" cursor_6 = pnsqlcursor.PNSqlCursor("flareas") cursor_6.setModeAccess(cursor_6.Insert) cursor_6.refreshBuffer() cursor_6.setValueBuffer("bloqueo", True) cursor_6.setValueBuffer("idarea", "O") cursor_6.setValueBuffer("descripcion", "Área de prueba T") self.assertTrue(cursor_6.commitBuffer()) cursor_6.setModeAccess(cursor_6.Insert) cursor_6.refreshBuffer() cursor_6.setValueBuffer("bloqueo", True) cursor_6.setValueBuffer("idarea", "P") cursor_6.setValueBuffer("descripcion", "Área de prueba T") self.assertTrue(cursor_6.commitBuffer()) cursor_6.commit() qry = pnsqlquery.PNSqlQuery("") qry.setTablesList("flareas") qry.setSelect("idarea") qry.setFrom("flareas") qry.setWhere("1=1") qry.setOrderBy("idarea ASC") self.assertTrue(qry.exec_()) self.assertTrue(qry.first()) val_first = qry.value(0) size_ = qry.size() self.assertTrue(qry.last()) val_last = qry.value(0) self.assertNotEqual(qry.value("idarea"), val_first) self.assertEqual(qry.value("idarea"), qry.value(0)) self.assertTrue(qry.prev()) self.assertTrue(qry.seek(0)) self.assertFalse(qry.isNull("idarea")) self.assertEqual(qry.value(0), val_first) self.assertFalse(qry.seek(1000)) self.assertFalse(qry.seek(1000, True)) self.assertTrue(qry.seek(size_ - 1, True)) # last self.assertEqual(qry.value(0), val_last)
def listAllIdModules(self) -> List[str]: """ Return the list of identifiers of all modules loaded in the system. @return List of module identifiers """ if self.list_all_id_modules_: return self.list_all_id_modules_ ret: List[str] = [] if not self.conn_.connManager().dbAux(): return ret ret.append("sys") q = pnsqlquery.PNSqlQuery(None, "dbAux") q.setForwardOnly(True) q.exec_("SELECT idmodulo FROM flmodules WHERE idmodulo <> 'sys'") while q.next(): ret.append(str(q.value(0))) return ret
def test_basic_2(self) -> None: """Test basic_2.""" qry = pnsqlquery.PNSqlQuery("fake") qry.setTablesList("fake_table") self.assertEqual(qry.tablesList(), ["fake_table"]) qry.setTablesList(["fake_table_1", "fake_table_2"]) self.assertEqual(qry.tablesList(), ["fake_table_1", "fake_table_2"]) qry.setSelect("field_01") qry.setFrom("fake_table") qry.setWhere("1=1") qry.setOrderBy("field_01 ASC") self.assertEqual( qry.sql(), "SELECT field_01 FROM fake_table WHERE 1=1 ORDER BY field_01 ASC") self.assertEqual(qry.fieldNameToPos("field_01"), 0) self.assertFalse(qry.exec_()) self.assertEqual(qry.fieldList(), ["field_01"]) self.assertFalse(qry.isValid()) self.assertTrue(qry.isNull("field_01")) self.assertEqual(qry.value("field_01"), None)
def listIdAreas(self) -> List[str]: """ Return the list of area identifiers loaded in the system. @return List of area identifiers """ if self.list_id_areas_: return self.list_id_areas_ ret: List[str] = [] if not self.conn_.connManager().dbAux(): return ret q = pnsqlquery.PNSqlQuery(None, "dbAux") q.setForwardOnly(True) q.exec_("SELECT idarea FROM flareas WHERE idarea <> 'sys'") while q.next(): ret.append(str(q.value(0))) ret.append("sys") return ret
def readDBSettingEntry(cls, key: str) -> Any: """ Read the value of a setting in the flsettings table. @param key. Setting identification key. @return Setting value. """ ret = None q = pnsqlquery.PNSqlQuery() q.setSelect("valor") q.setFrom("flsettings") q.setWhere("flkey = '%s'" % key) q.setTablesList("flsettings") if q.exec_() and q.first(): ret = q.value(0) if ret in ["false", "False"]: ret = False elif ret in ["true", "True"]: ret = True return ret
def Mr_Proper(self) -> None: """Cleanup database like mr.proper.""" if not self.isOpen(): raise Exception("Mr_Proper: Database not open") util = flutil.FLUtil() if not self.db_: raise Exception("must be connected") self.db_.connManager().dbAux().transaction() qry = pnsqlquery.PNSqlQuery(None, "dbAux") qry2 = pnsqlquery.PNSqlQuery(None, "dbAux") qry3 = pnsqlquery.PNSqlQuery(None, "dbAux") # qry4 = pnsqlquery.PNSqlQuery(None, "dbAux") # qry5 = pnsqlquery.PNSqlQuery(None, "dbAux") steps = 0 self.active_create_index = False rx = Qt.QRegExp("^.*\\d{6,9}$") if rx in self.tables(): listOldBks = self.tables()[rx] else: listOldBks = [] qry.exec_( "select nombre from flfiles where nombre regexp" "'.*[[:digit:]][[:digit:]][[:digit:]][[:digit:]]-[[:digit:]][[:digit:]].*:[[:digit:]][[:digit:]]$' or nombre regexp" "'.*alteredtable[[:digit:]][[:digit:]][[:digit:]][[:digit:]].*' or (bloqueo=0 and nombre like '%.mtd')" ) util.createProgressDialog( util.translate("SqlDriver", "Borrando backups"), len(listOldBks) + qry.size() + 2 ) while qry.next(): item = qry.value(0) util.setLabelText(util.translate("SqlDriver", "Borrando registro %s") % item) qry2.exec_("DELETE FROM flfiles WHERE nombre ='%s'" % item) if item.find("alteredtable") > -1: if self.existsTable(item.replace(".mtd", "")): util.setLabelText(util.translate("SqlDriver", "Borrando tabla %s" % item)) qry2.exec_("DROP TABLE %s CASCADE" % item.replace(".mtd", "")) steps = steps + 1 util.setProgress(steps) for item in listOldBks: if self.existsTable(item): util.setLabelText(util.translate("SqlDriver", "Borrando tabla %s" % item)) qry2.exec_("DROP TABLE %s CASCADE" % item) steps = steps + 1 util.setProgress(steps) util.setLabelText(util.translate("SqlDriver", "Inicializando cachés")) steps = steps + 1 util.setProgress(steps) qry.exec_("DELETE FROM flmetadata") qry.exec_("DELETE FROM flvar") self.db_.connManager().manager().cleanupMetaData() # self.db_.connManager().driver().commit() util.destroyProgressDialog() steps = 0 qry3.exec_("SHOW TABLES") util.createProgressDialog( util.translate("SqlDriver", "Comprobando base de datos"), qry3.size() ) while qry3.next(): item = qry3.value(0) # print("Comprobando", item) # qry2.exec_("alter table %s convert to character set utf8 collate utf8_bin" % item) mustAlter = self.mismatchedTable(item, item) if mustAlter: conte = self.db_.connManager().managerModules().content("%s.mtd" % item) if conte: msg = util.translate( "SqlDriver", "La estructura de los metadatos de la tabla '%s' y su " "estructura interna en la base de datos no coinciden. " "Intentando regenerarla." % item, ) LOGGER.warning("%s", msg) self.alterTable2(conte, conte, None, True) steps = steps + 1 util.setProgress(steps) self.db_.connManager().dbAux().driver().transaction() self.active_create_index = True steps = 0 # sqlCursor = pnsqlcursor.PNSqlCursor(None, True, self.db_.connManager().dbAux()) engine = "MyISAM" if self.noInnoDB else "INNODB" convert_engine = False do_ques = True sqlQuery = pnsqlquery.PNSqlQuery(None, self.db_.connManager().dbAux()) sql_query2 = pnsqlquery.PNSqlQuery(None, self.db_.connManager().dbAux()) if sqlQuery.exec_("SHOW TABLES"): util.setTotalSteps(sqlQuery.size()) while sqlQuery.next(): item = sqlQuery.value(0) steps = steps + 1 util.setProgress(steps) util.setLabelText(util.translate("SqlDriver", "Creando índices para %s" % item)) mtd = self.db_.connManager().manager().metadata(item, True) if not mtd: continue fL = mtd.fieldList() if not fL: continue for it in fL: if not it or not it.type() == "pixmap": continue cur = pnsqlcursor.PNSqlCursor(item, True, self.db_.connManager().dbAux()) cur.select(it.name() + " not like 'RK@%'") while cur.next(): v = cur.valueBuffer(it.name()) if v is None: continue v = self.db_.connManager().manager().storeLargeValue(mtd, v) if v: buf = cur.primeUpdate() buf.setValue(it.name(), v) cur.update(False) # sqlCursor.setName(item, True) # self.db_.connManager().dbAux().driver().commit() sql_query2.exec_( "show table status where Engine='%s' and Name='%s'" % (engine, item) ) if not sql_query2.next(): if do_ques: res = QtWidgets.QMessageBox.question( QtWidgets.QWidget(), util.translate("SqlDriver", "Mr. Proper"), util.translate( "SqlDriver", "Existen tablas que no son del tipo %s utilizado por el driver de la conexión actual.\n" "Ahora es posible convertirlas, pero asegurése de tener una COPIA DE SEGURIDAD,\n" "se pueden peder datos en la conversión de forma definitiva.\n\n" "¿ Quiere convertirlas ?" % (engine), ), QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.No, ) if res == QtWidgets.QMessageBox.Yes: convert_engine = True do_ques = False if convert_engine: conte = self.db_.connManager().managerModules().content("%s.mtd" % item) self.alterTable2(conte, conte, None, True) self.active_create_index = False util.destroyProgressDialog()
def validateForm(self) -> bool: """ Form validation. Call the "validateForm" function of the associated script when the form and only continue with the commit commit when that function of script returns TRUE. If FLTableMetaData :: concurWarn () is true and two or more sessions / users are. Modifying the same fields will display a warning notice. @return TRUE if the form has been validated correctly. """ if not self.cursor_: return True mtd = self.cursor_.metadata() if not mtd: return True if self.cursor_.modeAccess( ) == pnsqlcursor.PNSqlCursor.Edit and mtd.concurWarn(): colFields = self.cursor_.concurrencyFields() if colFields: pKN = mtd.primaryKey() pKWhere = (self.cursor_.db().connManager().manager(). formatAssignValue(mtd.field(pKN), self.cursor_.valueBuffer(pKN))) q = pnsqlquery.PNSqlQuery(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", cast( QtWidgets.QMessageBox.StandardButtons, QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.Default, ), cast( QtWidgets.QMessageBox.StandardButton, 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() == pnsqlcursor.PNSqlCursor.Insert or self.cursor_.modeAccess() == pnsqlcursor.PNSqlCursor.Edit): ret_ = True fun_ = getattr(self.iface, "validateForm", None) if fun_ is not None and fun_ != self.validateForm: try: ret_ = fun_() except Exception: # script_name = self.iface.__module__ from pineboolib.core.error_manager import error_manager from pineboolib import application flapplication.aqApp.msgBoxWarning( error_manager( traceback.format_exc(limit=-6, chain=False)), application.PROJECT.DGI, ) return ret_ if isinstance(ret_, bool) else True return True
def Mr_Proper(self) -> None: """Clear all garbage data.""" LOGGER.warning("FLSQLITE: FIXME: Mr_Proper no regenera tablas") util = flutil.FLUtil() if self.db_ is None: raise Exception("MR_Proper: self.db_ is None") if not self.isOpen(): raise Exception("MR_Proper: Cannot proceed: SQLLITE not open") self.db_.connManager().dbAux().transaction() rx = Qt.QRegExp("^.*[\\d][\\d][\\d][\\d].[\\d][\\d].*[\\d][\\d]$") rx2 = Qt.QRegExp("^.*alteredtable[\\d][\\d][\\d][\\d].*$") qry = pnsqlquery.PNSqlQuery(None, "dbAux") qry2 = pnsqlquery.PNSqlQuery(None, "dbAux") qry3 = pnsqlquery.PNSqlQuery(None, "dbAux") steps = 0 item = "" rx3 = Qt.QRegExp("^.*\\d{6,9}$") # listOldBks = self.tables("").grep(rx3) listOldBks_prev = self.tables("Tables") listOldBks = [] for l in listOldBks_prev: if rx3.indexIn(l) > -1: listOldBks.append(l) qry.exec_("select nombre from flfiles") util.createProgressDialog( util.translate("SqlDriver", "Borrando backups"), len(listOldBks) + qry.size() + 5) while qry.next(): item = qry.value(0) if rx.indexIn(item) > -1 or rx2.indexIn(item) > -1: util.setLabelText( util.translate("SqlDriver", "Borrando regisro %s" % 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.translate("SqlDriver", "Borrando tabla %s" % 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.translate("SqlDriver", "Borrando tabla %s" % item) util.setLabelText( util.translate("SqlDriver", "Borrando tabla %s" % item)) qry2.exec_("drop table %s" % item) steps = steps + 1 util.setProgress(steps) util.setLabelText(util.translate("SqlDriver", "Inicializando cachés")) steps = steps + 1 util.setProgress(steps) qry.exec_("delete from flmetadata") qry.exec_("delete from flvar") self.db_.connManager().manager().cleanupMetaData() self.db_.connManager().dbAux().commit() util.setLabelText( util.translate("SqlDriver", "Vacunando base de datos")) steps = steps + 1 util.setProgress(steps) qry3.exec_("vacuum") steps = steps + 1 util.setProgress(steps) util.destroyProgressDialog()
def test_basic_1(self) -> None: """Test basic_1.""" from pineboolib.application.database import pnparameterquery from pineboolib.application.database import pngroupbyquery qry = pnsqlquery.PNSqlQuery("fltest2") from_param = pnparameterquery.PNParameterQuery("from", "from", 2) to_param = pnparameterquery.PNParameterQuery("to", "to", 2) from_param.setValue(0) to_param.setValue(1) qry.addParameter(from_param) qry.addParameter(to_param) data = {} data[from_param.name()] = from_param.value() data[to_param.name()] = to_param.value() qry.setParameterDict(data) self.assertEqual(qry.valueParam("to"), 1) qry.setValueParam("to", 2) self.assertEqual(qry.valueParam("to"), 2) self.assertEqual( qry.sql(), "SELECT id,string_field,date_field,time_field,double_field,bool_field,uint_field,bloqueo FROM fltest" + " WHERE id>='0' AND id<='2' ORDER BY fltest.id", ) gr_01 = pngroupbyquery.PNGroupByQuery(0, "string_field") qry.addGroup(gr_01) group = {} group[gr_01.level()] = gr_01.field() qry2 = pnsqlquery.PNSqlQuery("fltest") qry2.setSelect( "id,string_field,date_field,time_field,double_field,bool_field,uint_field,bloqueo" ) qry2.setFrom("fltest") qry2.setWhere("id>='0' AND id<='1'") qry2.setGroupDict(group) self.assertEqual( qry2.sql(), "SELECT id,string_field,date_field,time_field,double_field,bool_field,uint_field,bloqueo FROM fltest" + " WHERE id>='0' AND id<='1' ORDER BY string_field", ) self.assertEqual(qry.name(), "fltest2") self.assertEqual(qry.where(), "id>=[from] AND id<=[to]") self.assertEqual(qry.orderBy(), "fltest.id") qry.setSelect(["id", "fltest.string_field"]) self.assertEqual(len(qry.parameterDict()), 2) self.assertEqual(len(qry.groupDict()), 1) self.assertEqual(qry.fieldList(), ["id", "fltest.string_field"]) self.assertEqual(qry.posToFieldName(0), "id") self.assertEqual(qry.posToFieldName(1), "fltest.string_field") self.assertEqual(qry.fieldNameToPos("fltest.string_field"), 1) self.assertEqual(qry.fieldNameToPos("string_field"), 1) qry.setName("fltest2_dos") self.assertEqual(qry.name(), "fltest2_dos") self.assertEqual(len(qry.fieldMetaDataList()), 2)
def nextSerialVal(self, table: str, field: str) -> Any: """Get next serial value for given table and field.""" if not self.isOpen(): raise Exception("beginTransaction: Database not open") # if not self.transaction(): # self.setLastError("No se puede iniciar la transacción", "BEGIN WORK") # return None max = 0 cur_max = 0 updateQry = False ret = None q = pnsqlquery.PNSqlQuery() q.setSelect("max(%s)" % field) q.setFrom(table) q.setWhere("1 = 1") if not q.exec_(): LOGGER.warning("not exec sequence") return None elif q.first(): v = q.value(0) if v is not None: max = v if not self.conn_: raise Exception("must be connected") cursor = self.conn_.cursor() strQry: Optional[str] = "SELECT seq FROM flseqs WHERE tabla = '%s' AND campo ='%s'" % ( table, field, ) try: cur_max = 0 cursor.execute(strQry) line = cursor.fetchone() if line: cur_max = line[0] except Exception: LOGGER.warning( "%s:: La consulta a la base de datos ha fallado", self.name_, traceback.format_exc() ) self.rollbackTransaction() return if cur_max > 0: updateQry = True ret = cur_max else: ret = max ret += 1 strQry = None 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, ) if strQry is not None: try: cursor.execute(strQry) except Exception: LOGGER.warning( "%s:: La consulta a la base de datos ha fallado\n %s", self.name_, traceback.format_exc(), ) self.rollbackTransaction() return # if not self.commitTransaction(): # LOGGER.warning("%s:: No se puede aceptar la transacción" % self.name_) # return None return ret
def alterTable2(self, mtd1: str, mtd2: str, key: Optional[str], force: bool = False) -> bool: """Alter a table following mtd instructions.""" if not self.isOpen(): raise Exception("alterTable2: Database not open") return False if not self.db_: raise Exception("must be connected") util = flutil.FLUtil() old_mtd = None new_mtd = None if not mtd1: print( "FLManager::alterTable : " + util.translate("SqlDriver", "Error al cargar los metadatos.") ) else: xml = ElementTree.fromstring(mtd1) old_mtd = self.db_.connManager().manager().metadata(xml, True) if old_mtd and old_mtd.isQuery(): return True if old_mtd and self.hasCheckColumn(old_mtd): return False if not mtd2: print( "FLManager::alterTable : " + util.translate("SqlDriver", "Error al cargar los metadatos.") ) return False else: xml = ElementTree.fromstring(mtd2) new_mtd = self.db_.connManager().manager().metadata(xml, True) if not old_mtd: old_mtd = new_mtd if not old_mtd.name() == new_mtd.name(): print( "FLManager::alterTable : " + util.translate("SqlDriver", "Los nombres de las tablas nueva y vieja difieren.") ) if old_mtd and not old_mtd == new_mtd: del old_mtd if new_mtd: del new_mtd return False oldPK = old_mtd.primaryKey() newPK = new_mtd.primaryKey() if not oldPK == newPK: print( "FLManager::alterTable : " + util.translate("SqlDriver", "Los nombres de las claves primarias difieren.") ) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False if not force and self.db_.connManager().manager().checkMetaData(old_mtd, new_mtd): if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return True if not self.db_.connManager().manager().existsTable(old_mtd.name()): print( "FLManager::alterTable : " + util.translate( "SqlDriver", "La tabla %s antigua de donde importar los registros no existe." % old_mtd.name(), ) ) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False fieldList = old_mtd.fieldList() # oldField = None if not fieldList: print( "FLManager::alterTable : " + util.translate("SqlDriver", "Los antiguos metadatos no tienen campos.") ) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False fieldNamesOld = [] if not force: for it in fieldList: if new_mtd.field(it.name()) is not None: fieldNamesOld.append(it.name()) renameOld = "%salteredtable%s" % ( old_mtd.name()[0:5], QtCore.QDateTime().currentDateTime().toString("ddhhssz"), ) if not self.db_.connManager().dbAux(): if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False # self.db_.connManager().dbAux().transaction() fieldList = new_mtd.fieldList() if not fieldList: LOGGER.warning( "FLManager::alterTable : " + util.translate("SqlDriver", "Los nuevos metadatos no tienen campos") ) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False q = pnsqlquery.PNSqlQuery(None, "dbAux") in_sql = "ALTER TABLE %s RENAME TO %s" % (old_mtd.name(), renameOld) LOGGER.warning(in_sql) if not q.exec_(in_sql): LOGGER.warning( "FLManager::alterTable : " + util.translate("SqlDriver", "No se ha podido renombrar la tabla antigua.") ) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False if not self.db_.connManager().manager().createTable(new_mtd): self.db_.connManager().dbAux().rollbackTransaction() if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False self.db_.connManager().dbAux().transaction() if not force and key and len(key) == 40: c = pnsqlcursor.PNSqlCursor("flfiles", True, self.db_.connManager().dbAux()) # oldCursor.setModeAccess(oldCursor.Browse) c.setForwardOnly(True) c.setFilter("nombre='%s.mtd'" % renameOld) c.select() if not c.next(): # c.setModeAccess(c.Insert) # c.refreshBuffer() # c.setValueBuffer("nombre","%s.mtd" % renameOld) # c.setValueBuffer("contenido", mtd1) # c.setValueBuffer("sha", key) # c.commitBuffer() in_sql = ( "INSERT INTO flfiles(nombre,contenido,idmodulo,sha) VALUES ('%s.mtd','%s','%s','%s')" % ( renameOld, mtd1, self.db_.connManager() .managerModules() .idModuleOfFile("%s.mtd" % old_mtd.name()), key, ) ) LOGGER.warning(in_sql) q.exec_(in_sql) ok = False if force and fieldNamesOld: # sel = fieldNamesOld.join(",") # in_sql = "INSERT INTO %s(%s) SELECT %s FROM %s" % (new_mtd.name(), sel, sel, renameOld) # LOGGER.warning(in_sql) # ok = q.exec_(in_sql) if not ok: self.db_.connManager().dbAux().rollbackTransaction() if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return self.alterTable2(mtd1, mtd2, key, True) if not ok: oldCursor = self.conn_.cursor(self.dict_cursor()) # print("Lanzando!!", "SELECT * FROM %s WHERE 1 = 1" % (renameOld)) oldCursor.execute("SELECT * FROM %s WHERE 1 = 1" % (renameOld)) result_set = oldCursor.fetchall() totalSteps = len(result_set) # oldCursor = pnsqlcursor.PNSqlCursor(renameOld, True, "dbAux") # oldCursor.setModeAccess(oldCursor.Browse) # oldCursor.setForwardOnly(True) # oldCursor.select() # totalSteps = oldCursor.size() util.createProgressDialog( util.translate("SqlDriver", "Reestructurando registros para %s...") % new_mtd.alias(), totalSteps, ) util.setLabelText(util.translate("SqlDriver", "Tabla modificada")) step = 0 newBuffer = None newField = None listRecords = [] newBufferInfo = self.recordInfo2(new_mtd.name()) vector_fields = {} default_values = {} v = None for it2 in fieldList: oldField = old_mtd.field(it2.name()) if ( oldField is None or not result_set or oldField.name() not in result_set[0].keys() ): if oldField is None: oldField = it2 if it2.type() != pnfieldmetadata.PNFieldMetaData.Serial: v = it2.defaultValue() step += 1 default_values[str(step)] = v step += 1 vector_fields[str(step)] = it2 step += 1 vector_fields[str(step)] = oldField # step2 = 0 ok = True x = 0 for row in result_set: x += 1 newBuffer = newBufferInfo i = 0 while i < step: v = None if str(i + 1) in default_values.keys(): i += 1 v = default_values[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 = row[newField.name()] if ( (not oldField.allowNull() or not newField.allowNull()) and (v is None) and newField.type() != pnfieldmetadata.PNFieldMetaData.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() == pnfieldmetadata.PNFieldMetaData.Serial: v = int(self.nextSerialVal(new_mtd.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 = QtCore.QTime.currentTime() elif oldField.type() == "date": v = QtCore.QDate.currentDate() else: v = "NULL"[: newField.length()] # new_b = [] for buffer in newBuffer: if buffer[0] == newField.name(): new_buffer = [] new_buffer.append(buffer[0]) new_buffer.append(buffer[1]) new_buffer.append(newField.allowNull()) new_buffer.append(buffer[3]) new_buffer.append(buffer[4]) new_buffer.append(v) new_buffer.append(buffer[6]) listRecords.append(new_buffer) break # newBuffer.setValue(newField.name(), v) if listRecords: if not self.insertMulti(new_mtd.name(), listRecords): ok = False listRecords = [] util.setProgress(totalSteps) util.destroyProgressDialog() if ok: self.db_.connManager().dbAux().commit() if force: q.exec_("DROP TABLE %s CASCADE" % renameOld) else: self.db_.connManager().dbAux().rollbackTransaction() q.exec_("DROP TABLE %s CASCADE" % old_mtd.name()) q.exec_("ALTER TABLE %s RENAME TO %s" % (renameOld, old_mtd.name())) if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return False if old_mtd and old_mtd != new_mtd: del old_mtd if new_mtd: del new_mtd return True
def parseKey(self, ref_key: Optional[str] = None) -> Optional[str]: """ Get filename of .png file cached on tempdata. If it does not exist it is created. @param. String of related tuple in fllarge. @return. Path to the file in tempdata. """ ret = None table_name = "fllarge" if ref_key is not None: from PyQt5.QtGui import QPixmap value = None tmp_dir = config.value("ebcomportamiento/temp_dir") img_file = "%s/%s.png" % (tmp_dir, ref_key) if not os.path.exists(img_file) and ref_key[0:3] == "RK@": single_query = pnsqlquery.PNSqlQuery() single_query.exec_( "SELECT valor FROM flsettings WHERE flkey='FLLargeMode'") one_fllarge = True if single_query.next(): if single_query.value(0) == "True": one_fllarge = False if ( not one_fllarge ): # Si no es FLLarge modo único añadimos sufijo "_nombre" a fllarge table_name += "_%s" % ref_key.split("@")[1] qry = pnsqlquery.PNSqlQuery() qry.exec_("SELECT contenido FROM %s WHERE refkey='%s'" % (table_name, ref_key)) if qry.next(): value = xpm.cache_xpm(qry.value(0)) if value: ret = img_file pix = QPixmap(value) if not pix.save(img_file): self.logger.warning( "%s:refkey2cache No se ha podido guardar la imagen %s" % (__name__, img_file)) ret = None else: ret = img_file elif ref_key.endswith(".xpm"): pix = QPixmap(ref_key) img_file = ref_key.replace(".xpm", ".png") if not pix.save(img_file): self.logger.warning( "%s:refkey2cache No se ha podido guardar la imagen %s" % (__name__, img_file)) ret = None else: ret = img_file else: ret = img_file return ret