def checkAccessTableHasAdmin(self): sql = "SELECT lower(username) FROM postnas_search_access_control WHERE access = 0" self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if (query.size() > 0): return True else: return False
def __checkLoggingTableExists(self): sql = "SELECT table_name FROM information_schema.tables WHERE table_name = 'postnas_search_logging'"; self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if(query.size() > 0): return True else: return False
def checkAccessTable(self): sql = "SELECT table_name FROM information_schema.tables WHERE table_name = 'postnas_search_access_control'" self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if (query.size() > 0): return True else: return False
def checkUserExists(self): sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE lower(username) = :username" self.__openDB() queryCheckUserExists = QSqlQuery(self.db) queryCheckUserExists.prepare(sql) queryCheckUserExists.bindValue(":username", self.getUsername()) queryCheckUserExists.exec_() if (queryCheckUserExists.lastError().number() == -1): if (queryCheckUserExists.size() > 0): return True else: return False else: return False
def checkUserHasEigentuemerAccess(self): if (self.getUsername() != None): self.__openDB() sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE access IN (0,1) AND lower(username) = :username" queryEigentuemerAccess = QSqlQuery(self.db) queryEigentuemerAccess.prepare(sql) queryEigentuemerAccess.bindValue(":username", self.getUsername()) queryEigentuemerAccess.exec_() if (queryEigentuemerAccess.lastError().number() == -1): if (queryEigentuemerAccess.size() > 0): return True else: return False else: return False else: return False
def setUsername(self, username): self.username = username if (self.checkUserExists()): sql = "SELECT name,access FROM postnas_search_access_control WHERE lower(username) = :username" self.__openDB() queryLoadUserData = QSqlQuery(self.db) queryLoadUserData.prepare(sql) queryLoadUserData.bindValue(":username", self.getUsername()) queryLoadUserData.exec_() if (queryLoadUserData.size() == 1): while (queryLoadUserData.next()): self.setName( queryLoadUserData.value( queryLoadUserData.record().indexOf("name"))) self.setAccess( queryLoadUserData.value( queryLoadUserData.record().indexOf("access")))
def getAccessModes(self): sql = "SELECT id,bezeichnung FROM postnas_search_accessmode" self.__openDB() queryLoadAccessModes = QSqlQuery(self.db) queryLoadAccessModes.prepare(sql) queryLoadAccessModes.exec_() results = [] if (queryLoadAccessModes.size() > 0): while (queryLoadAccessModes.next()): list = { 'id': queryLoadAccessModes.value( queryLoadAccessModes.record().indexOf("id")), 'bezeichnung': queryLoadAccessModes.value( queryLoadAccessModes.record().indexOf("bezeichnung")) } results.append(list) return results
def loadUserAccessTable(self): sql = "SELECT lower(username) as username,name,bezeichnung FROM postnas_search_access_control LEFT JOIN postnas_search_accessmode ON postnas_search_access_control.access = postnas_search_accessmode.id" self.__openDB() queryLoadAccessTable = QSqlQuery(self.db) queryLoadAccessTable.prepare(sql) queryLoadAccessTable.exec_() results = [] if (queryLoadAccessTable.size() > 0): while (queryLoadAccessTable.next()): list = { 'username': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("username")), 'name': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("name")), 'access': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("bezeichnung")) } results.append(list) return results
def test_simple_import_detail_data_multi_lane(self): self.db.open() query = QSqlQuery(self.db) query.exec_("SELECT id FROM comptages.installation \ WHERE name = '00056520';") query.next() installation_id = query.value(0) query.exec_("SELECT id FROM comptages.model \ WHERE name = 'M660_LT';") query.next() model_id = query.value(0) query.exec_("SELECT id FROM comptages.lane \ WHERE id_installation = {} ORDER BY number;".format( installation_id)) query.next() lane_1_id = query.value(0) query.next() lane_2_id = query.value(0) query.exec_("select id from comptages.sensor_type \ where name = 'Tube'") query.next() sensor_type_id = query.value(0) query_str = ( "INSERT INTO comptages.count(id, " "start_process_date, end_process_date, start_service_date, " "end_service_date, id_sensor_type, id_model, id_installation) " "VALUES (1, '2018-09-23', '2018-09-26', '2018-09-23', " "'2018-09-26', {}, {}, {});".format( sensor_type_id, model_id, installation_id)) query.exec_(query_str) task = self.comptages.import_file( os.path.join( self.test_data_path, 'simple_detail_multi_lane.V01'), 1) task.waitForFinished() # Let the time to the db to finish the writing time.sleep(1) query.exec_( "SELECT numbering, timestamp, distance_front_front, \ distance_front_back, speed, length, height, fixed, wrong_way, \ file_name, import_status, id_lane, id_count, id_category \ FROM comptages.count_detail \ WHERE file_name = 'simple_detail_multi_lane.V01' ORDER BY id;") self.assertEqual(2, query.size()) query.next() self.assertEqual(1, query.value(0)) self.assertEqual( '240918 1545 49 800', query.value(1).toString('ddMMyy HHmm ss zzz')) self.assertEqual(12.3, query.value(2)) self.assertEqual(99.9, query.value(3)) self.assertEqual(50, query.value(4)) self.assertEqual(428, query.value(5)) self.assertEqual('L', query.value(6).strip()) self.assertNotEqual(True, query.value(7)) self.assertNotEqual(True, query.value(8)) self.assertEqual('simple_detail_multi_lane.V01', query.value(9)) self.assertEqual(self.layers.IMPORT_STATUS_QUARANTINE, query.value(10)) self.assertEqual(lane_1_id, query.value(11)) self.assertEqual(1, query.value(12)) self.assertEqual(24, query.value(13)) query.next() self.assertEqual(2, query.value(0)) self.assertEqual( '240918 1545 50 900', query.value(1).toString('ddMMyy HHmm ss zzz')) self.assertEqual(3.8, query.value(2)) self.assertEqual(1.4, query.value(3)) self.assertEqual(51, query.value(4)) self.assertEqual(416, query.value(5)) self.assertEqual('VL', query.value(6).strip()) self.assertNotEqual(True, query.value(7)) self.assertNotEqual(True, query.value(8)) self.assertEqual('simple_detail_multi_lane.V01', query.value(9)) self.assertEqual(self.layers.IMPORT_STATUS_QUARANTINE, query.value(10)) self.assertEqual(lane_2_id, query.value(11)) self.assertEqual(1, query.value(12)) self.assertEqual(25, query.value(13)) self.db.close()
def test_refuse_special_case(self): self.db.open() query = QSqlQuery(self.db) query.exec_("SELECT id FROM comptages.installation \ WHERE name = '53109999';") query.next() installation_id = query.value(0) query.exec_("SELECT id FROM comptages.model \ WHERE name = 'M660_LT';") query.next() model_id = query.value(0) query.exec_("select id from comptages.sensor_type \ where name = 'Boucle'") query.next() sensor_type_id = query.value(0) query_str = ( "INSERT INTO comptages.count(id, " "start_process_date, end_process_date, start_service_date, " "end_service_date, id_sensor_type, id_model, id_installation) " "VALUES (1, '2017-03-17', '2017-04-04', '2017-03-17', " "'2017-04-04', {}, {}, {});".format( sensor_type_id, model_id, installation_id)) query.exec_(query_str) task = self.comptages.import_file( os.path.join( self.test_data_path, 'simple_detail_special_case.V01'), 1) task.waitForFinished() # Let the time to the db to finish the writing time.sleep(1) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(12, query.size()) self.layers.delete_count_data( 1, '53116845', self.layers.IMPORT_STATUS_QUARANTINE) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(11, query.size()) self.layers.delete_count_data( 1, '53136855', self.layers.IMPORT_STATUS_QUARANTINE) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(7, query.size()) self.layers.delete_count_data( 1, '53126850', self.layers.IMPORT_STATUS_QUARANTINE) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(5, query.size()) self.layers.delete_count_data( 1, '53146860', self.layers.IMPORT_STATUS_QUARANTINE) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(0, query.size()) self.db.close()
def test_special_case(self): self.db.open() query = QSqlQuery(self.db) query.exec_("SELECT id FROM comptages.installation \ WHERE name = '53109999';") query.next() installation_id = query.value(0) query.exec_("SELECT id FROM comptages.model \ WHERE name = 'M660_LT';") query.next() model_id = query.value(0) query.exec_("SELECT id FROM comptages.lane \ WHERE id_installation = {} ORDER BY number;".format( installation_id)) lane_ids = [] while query.next(): lane_ids.append(query.value(0)) query.exec_("select id from comptages.sensor_type \ where name = 'Boucle'") query.next() sensor_type_id = query.value(0) query_str = ( "INSERT INTO comptages.count(id, " "start_process_date, end_process_date, start_service_date, " "end_service_date, id_sensor_type, id_model, id_installation) " "VALUES (1, '2017-03-17', '2017-04-04', '2017-03-17', " "'2017-04-04', {}, {}, {});".format( sensor_type_id, model_id, installation_id)) query.exec_(query_str) task = self.comptages.import_file( os.path.join( self.test_data_path, 'simple_detail_special_case.V01'), 1) task.waitForFinished() # Let the time to the db to finish the writing time.sleep(1) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01';") self.assertEqual(12, query.size()) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01' and id_lane = {};".format( lane_ids[0])) self.assertEqual(1, query.size()) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01' and id_lane = {};".format( lane_ids[1])) self.assertEqual(2, query.size()) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01' and id_lane = {};".format( lane_ids[2])) self.assertEqual(4, query.size()) query.exec_( "SELECT * \ FROM comptages.count_detail WHERE file_name = \ 'simple_detail_special_case.V01' and id_lane = {};".format( lane_ids[3])) self.assertEqual(5, query.size()) self.db.close()
class QtSqlDBCursor(object): def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1 def close(self): self.qry.finish() def execute(self, operation, parameters=[]): if len(parameters) == 0: if not self.qry.exec_(operation): raise ExecError(self.qry.lastError().databaseText()) else: if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for i in range(len(parameters)): self.qry.bindValue(i, parameters[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) self.rowcount = self.qry.size() self.description = [] for c in range(self.qry.record().count()): f = self.qry.record().field(c) if f.type() == QVariant.Date: t = Date elif f.type() == QVariant.Time: t = Time elif f.type() == QVariant.DateTime: t = Timestamp elif f.type() == QVariant.Double: t = float elif f.type() == QVariant.Int: t = int elif f.type() == QVariant.String: t = str elif f.type() == QVariant.ByteArray: t = str else: continue self.description.append([ f.name(), # name t, # type_code f.length(), # display_size f.length(), # internal_size f.precision(), # precision None, # scale f.requiredStatus() != QSqlField.Required # null_ok ]) def executemany(self, operation, seq_of_parameters): if len(seq_of_parameters) == 0: return if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for r in seq_of_parameters: for i in range(len(r)): self.qry.bindValue(i, r[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) def scroll(self, row): return self.qry.seek(row) def fetchone(self): if not self.qry.next(): return None row = [] for i in range(len(self.description)): value = self.qry.value(i) if (isinstance(value, QDate) or isinstance(value, QTime) or isinstance(value, QDateTime)): value = value.toString() elif isinstance(value, QByteArray): value = u"GEOMETRY" # value = value.toHex() row.append(value) return row def fetchmany(self, size=10): rows = [] while len(rows) < size: row = self.fetchone() if row is None: break rows.append(row) return rows def fetchall(self): rows = [] while True: row = self.fetchone() if row is None: break rows.append(row) return rows def setinputsize(self, sizes): raise ExecError("nyi") def setoutputsize(self, size, column=None): raise ExecError("nyi")
class QtSqlDBCursor(object): def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1 def close(self): self.qry.finish() def execute(self, operation, parameters=[]): if len(parameters) == 0: if not self.qry.exec_(operation): raise ExecError(self.qry.lastError().databaseText()) else: if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for i in range(len(parameters)): self.qry.bindValue(i, parameters[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) self.rowcount = self.qry.size() self.description = [] for c in range(self.qry.record().count()): f = self.qry.record().field(c) if f.type() == QVariant.Date: t = Date elif f.type() == QVariant.Time: t = Time elif f.type() == QVariant.DateTime: t = Timestamp elif f.type() == QVariant.Double: t = float elif f.type() == QVariant.Int: t = int elif f.type() == QVariant.String: t = str elif f.type() == QVariant.ByteArray: t = str else: continue self.description.append([ f.name(), # name t, # type_code f.length(), # display_size f.length(), # internal_size f.precision(), # precision None, # scale f.requiredStatus() != QSqlField.Required # null_ok ]) def executemany(self, operation, seq_of_parameters): if len(seq_of_parameters) == 0: return if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for r in seq_of_parameters: for i in range(len(r)): self.qry.bindValue(i, r[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) def scroll(self, row): return self.qry.seek(row) def fetchone(self): if not next(self.qry): return None row = [] for i in range(len(self.description)): value = self.qry.value(i) if (isinstance(value, QDate) or isinstance(value, QTime) or isinstance(value, QDateTime)): value = value.toString() elif isinstance(value, QByteArray): value = u"GEOMETRY" # value = value.toHex() row.append(value) return row def fetchmany(self, size=10): rows = [] while len(rows) < size: row = self.fetchone() if row is None: break rows.append(row) return rows def fetchall(self): rows = [] while True: row = self.fetchone() if row is None: break rows.append(row) return rows def setinputsize(self, sizes): raise ExecError("nyi") def setoutputsize(self, size, column=None): raise ExecError("nyi")