commit b2a9b83dbb223b0d05a4d8b8fa36cdb4103de749
Author: David Friesecký <frieseck@students.zcu.cz>
Date:   Wed Mar 31 23:06:00 2021 +0200

    Re #8471 - Tested direct DB communication
    - implemented tests for Certificates and PrivateKeys tables via DBManager

diff --git a/db/database_sqlite.db b/db/database_sqlite.db
index 8b6fac1..3ec3219 100644
Binary files a/db/database_sqlite.db and b/db/database_sqlite.db differ
diff --git a/src/constants.py b/src/constants.py
index 6cd905c..70f4ba1 100644
--- a/src/constants.py
+++ b/src/constants.py
@@ -1,4 +1,4 @@
-DATABASE_FILE = r"C:\sqlite\db\pythonsqlite.db"
+DATABASE_FILE = "../../../../db/database_sqlite.db"
 
 ROOT_CA_ID = 1
 INTERMEDIATE_CA_ID = 2
diff --git a/src/tests/__init__.py b/src/tests/__init__.py
new file mode 100644
index 0000000..e69de29
diff --git a/src/tests/impl/db_manager/conftest.py b/src/tests/impl/db_manager/conftest.py
new file mode 100644
index 0000000..46fbcaf
--- /dev/null
+++ b/src/tests/impl/db_manager/conftest.py
@@ -0,0 +1,7 @@
+from src.impl.db_manager import DBManager
+import pytest
+
+
+@pytest.fixture
+def repository():
+    return DBManager()
diff --git a/src/tests/impl/db_manager/transaction_certificate_usages.py b/src/tests/impl/db_manager/transaction_certificate_usages.py
new file mode 100644
index 0000000..e69de29
diff --git a/src/tests/impl/db_manager/transaction_certificates.py b/src/tests/impl/db_manager/transaction_certificates.py
new file mode 100644
index 0000000..81a009f
--- /dev/null
+++ b/src/tests/impl/db_manager/transaction_certificates.py
@@ -0,0 +1,58 @@
+import pytest
+from src.constants import *
+
+
+def test_create_first(repository):
+    sql = f"INSERT INTO {TAB_PRIVATE_KEYS} ({COL_PRIVATE_KEY},{COL_PASSWORD}) VALUES (?,?)"
+    repository.create(sql, ["test_pk_1", "test_pwd_1"])
+
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_private_keys = repository.read(sql)
+
+    sql = f"INSERT INTO {TAB_CERTIFICATES} ({COL_COMMON_NAME},{COL_VALID_FROM},{COL_VALID_TO},{COL_PEM_DATA}," \
+          f"{COL_PRIVATE_KEY_ID},{COL_TYPE_ID},{COL_PARENT_ID}) VALUES (?,?,?,?,?,?,?)"
+    check_create = repository.create(sql, ["test_common_name", "test_valid_from", "test_valid_to", "test_pem_data",
+                                           rows_private_keys[0][1], CA_ID, -1])
+
+    sql = f"SELECT * FROM {TAB_CERTIFICATES} WHERE {COL_COMMON_NAME} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert check_create
+    assert rows_select[0][1] == "test_common_name" \
+           and rows_select[0][2] == "test_valid_from" \
+           and rows_select[0][3] == "test_valid_to" \
+           and rows_select[0][4] == "test_pem_data" \
+           and rows_select[0][5] == rows_private_keys[0][1] \
+           and rows_select[0][6] == ROOT_CA_ID \
+           and rows_select[0][7] == -1
+
+
+def test_update(repository):
+    sql = f"SELECT * FROM {TAB_CERTIFICATES} WHERE {COL_COMMON_NAME} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    sql = f"UPDATE {TAB_CERTIFICATES} SET {COL_PARENT_ID} = ? WHERE {COL_ID} = ?"
+    check_update: bool = repository.update(sql, rows_select[0][0], [rows_select[0][0]])
+
+    sql = f"SELECT * FROM {TAB_CERTIFICATES} WHERE {COL_COMMON_NAME} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert check_update
+    assert rows_select[0][7] == rows_select[0][0]
+
+
+def test_delete(repository):
+    sql = f"SELECT * FROM {TAB_CERTIFICATES} WHERE {COL_COMMON_NAME} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    i = 1
+    print("\n")
+    for row in rows_select:
+        sql = f"DELETE FROM {TAB_CERTIFICATES} WHERE {COL_ID} = ?"
+        check_delete = repository.delete(sql, row[0], False)
+        assert check_delete
+
+    sql = f"SELECT * FROM {TAB_CERTIFICATES} WHERE {COL_COMMON_NAME} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert len(rows_select) == 0
diff --git a/src/tests/impl/db_manager/transaction_private_keys.py b/src/tests/impl/db_manager/transaction_private_keys.py
new file mode 100644
index 0000000..7ec4d33
--- /dev/null
+++ b/src/tests/impl/db_manager/transaction_private_keys.py
@@ -0,0 +1,55 @@
+import pytest
+from src.constants import *
+
+
+def test_create_first(repository):
+    sql = f"INSERT INTO {TAB_PRIVATE_KEYS} ({COL_PRIVATE_KEY},{COL_PASSWORD}) VALUES (?,?)"
+    check_create: bool = repository.create(sql, ["test_pk_1", "test_pwd_1"])
+
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert check_create
+    assert rows_select[0][1] == "test_pk_1" and rows_select[0][2] == "test_pwd_1"
+
+
+def test_create_count(repository):
+    sql = f"INSERT INTO {TAB_PRIVATE_KEYS} ({COL_PRIVATE_KEY},{COL_PASSWORD}) VALUES (?,?)"
+    check_create: bool = repository.create(sql, ["test_pk_1", "test_pwd_1"])
+
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert check_create
+    assert len(rows_select) == 2
+
+
+def test_update(repository):
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    sql = f"UPDATE {TAB_PRIVATE_KEYS} SET {COL_PASSWORD} = ? WHERE {COL_ID} = ?"
+    check_update: bool = repository.update(sql, rows_select[0][0], ["test_pwd_updated"])
+
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert check_update
+    assert rows_select[0][2] == "test_pwd_updated"
+
+
+def test_delete(repository):
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    i = 1
+    print("\n")
+    for row in rows_select:
+        sql = f"DELETE FROM {TAB_PRIVATE_KEYS} WHERE {COL_ID} = ?"
+        check_delete = repository.delete(sql, row[0], False)
+        assert check_delete
+
+    sql = f"SELECT * FROM {TAB_PRIVATE_KEYS} WHERE {COL_PRIVATE_KEY} LIKE 'test%'"
+    rows_select = repository.read(sql)
+
+    assert len(rows_select) == 0
