def convert_db_to_pandas(sPathToDB="../data/sensitivity_results/sensitivity_results.db"):
# check if db is existend
if not util.check_if_file_exists(sPathToDB):
raise Exception("database %s is not existend" % sPathToDB)
dbData = dbcon.Dbcon(sPathToDB)
# get all tests and parse it to panda dataframe
oSensitivityTestData = pd.read_sql_query(
"SELECT i.name as 'image', c.name as 'collection', h.hash, (ht.name || ' ' || ht.params) as 'hashalgorithm' FROM images as i INNER JOIN collections as c on c.id = i.collection_id INNER JOIN images_hashes as ih on ih.image_id = i.id INNER JOIN hashes as h on h.id = ih.hash_id INNER JOIN hash_types as ht on ht.id = h.hash_type_id;", dbData.con)
return oSensitivityTestData
def __init__(self,
sPathToDB="sensitivity_results.db",
sBaseFolder="../data/sensitivity_results/",
aHashes=[],
lNumberOfThreads=4):
# set hash algos
self.aHashes = aHashes
# set nr of threads
self.lNumberOfThreads = lNumberOfThreads
# create folders if not existent
self.sBaseFolder = util.create_path(sBaseFolder)
# create db file if not existent
self.sPathToDB = self.sBaseFolder + sPathToDB
open(self.sPathToDB, 'a').close()
dbData = dbcon.Dbcon(self.sPathToDB)
sDbSchema = """
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `images_hashes` (
`image_id` INTEGER NOT NULL,
`hash_id` INTEGER NOT NULL,
FOREIGN KEY(`hash_id`) REFERENCES `hashes`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(`image_id`) REFERENCES `images`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
PRIMARY KEY(`image_id`,`hash_id`)
);
CREATE TABLE IF NOT EXISTS `images` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT NOT NULL,
`collection_id` INTEGER NOT NULL,
FOREIGN KEY(`collection_id`) REFERENCES `collections`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS `hashes` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`hash` NPARRAY NOT NULL,
`hash_type_id` TEXT NOT NULL,
FOREIGN KEY(`hash_type_id`) REFERENCES `hash_types`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS `hash_types` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT NOT NULL,
`params` TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS `collections` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT NOT NULL UNIQUE
);
COMMIT;
"""
dbData.execute_sql_query_manipulation_script(sDbSchema)
def get_dbcon(self):
""" get db connection object"""
return dbcon.Dbcon(self.sPathToDB)
def __init__(self,
sBaseFolder="../data/stability_results/",
sPathToDB="stability_results.db",
aAttacks=[],
aHashes=[],
fnDeviation=None,
lNumberOfThreads=4):
# create folders if not existent
self.sBaseFolder = util.create_path(sBaseFolder)
# set attack list
self.aAttacks = aAttacks
# set hash function list
self.aHashes = aHashes
# set deviation function
self.fnDeviation = fnDeviation
# set number of threads
self.lNumberOfThreads = lNumberOfThreads
# create db file if not existent
self.sPathToDB = sBaseFolder + sPathToDB
open(self.sPathToDB, 'a').close()
dbData = dbcon.Dbcon(self.sPathToDB)
sDbSchema = """
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `tests` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`attack` INTEGER NOT NULL,
`hash_type` INTEGER NOT NULL,
`image` INTEGER NOT NULL,
`original_hash` INTEGER NOT NULL,
`attacked_hash` INTEGER NOT NULL,
`deviation_hash` REAL,
FOREIGN KEY(`image`) REFERENCES `images`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(`attack`) REFERENCES `attacks`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(`attacked_hash`) REFERENCES `hashes`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(`hash_type`) REFERENCES `hash_types`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(`original_hash`) REFERENCES `hashes`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
UNIQUE(`attack`,`hash_type`,`image`) --ON CONFLICT REPLACE
);
CREATE TABLE IF NOT EXISTS `images` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS `hashes` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`hash_value` NPARRAY NOT NULL
);
CREATE TABLE IF NOT EXISTS `hash_types` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`hash_fn` TEXT NOT NULL,
`hash_params` TEXT NOT NULL,
UNIQUE(`hash_fn`, `hash_params`)
);
CREATE TABLE IF NOT EXISTS `attacks` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`attack_fn` TEXT NOT NULL,
`attack_params` TEXT NOT NULL,
UNIQUE(`attack_fn`, `attack_params`)
);
COMMIT;
"""
dbData.execute_sql_query_manipulation_script(sDbSchema)