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")
