commit 07a6c8699b59cef73e331656f32dce1a2c3d60b2
Author: David Friesecký <frieseck@students.zcu.cz>
Date:   Tue May 4 11:31:21 2021 +0200

    Re #8696 - Improved DB creation
    - secured start application without lost data

diff --git a/SQLite_database_check.sql b/SQLite_database_check.sql
new file mode 100644
index 0000000..6492c74
--- /dev/null
+++ b/SQLite_database_check.sql
@@ -0,0 +1,65 @@
+/* Drop Tables */
+DROP TABLE IF EXISTS 'CertificateTypes'
+;
+
+DROP TABLE IF EXISTS 'CertificateUsages'
+;
+
+/* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */
+
+CREATE TABLE IF NOT EXISTS 'Certificates'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'valid_from' TEXT NOT NULL,
+	'valid_to' TEXT NOT NULL,
+	'pem_data' TEXT NOT NULL,
+	'common_name' TEXT NOT NULL,
+	'country_code' TEXT NULL,
+	'locality' TEXT NULL,
+	'province' TEXT NULL,
+	'organization' TEXT NULL,
+	'organizational_unit' TEXT NULL,
+	'email_address' TEXT NULL,
+	'revocation_date' TEXT NULL,
+	'revocation_reason' TEXT NULL,
+	'deletion_date' TEXT NULL,
+	'certificate_type_id' INTEGER NOT NULL,
+	'parent_certificate_id' INTEGER NOT NULL,
+	'private_key_id' INTEGER NOT NULL,
+	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('parent_certificate_id') REFERENCES 'Certificates' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_CertificateTypes' FOREIGN KEY ('certificate_type_id') REFERENCES 'CertificateTypes' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_PrivateKeys' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKeys' ('id') ON DELETE No Action ON UPDATE No Action
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'CertificateTypes'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_type' TEXT NOT NULL
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'CertificateUsages'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_id' INTEGER NOT NULL,
+	'usage_type_id' INTEGER NOT NULL,
+	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('certificate_id') REFERENCES 'Certificates' ('id') ON DELETE Cascade ON UPDATE No Action,
+	CONSTRAINT 'FK_UsageTypes' FOREIGN KEY ('usage_type_id') REFERENCES 'UsageTypes' ('id') ON DELETE No Action ON UPDATE No Action
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'PrivateKeys'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'private_key' TEXT NOT NULL,
+	'password' TEXT NULL
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'UsageTypes'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'usage_type' TEXT NOT NULL
+)
+;
diff --git a/src/config/configuration.py b/src/config/configuration.py
index cbc887f..d994be5 100644
--- a/src/config/configuration.py
+++ b/src/config/configuration.py
@@ -7,7 +7,7 @@ from logging import handlers
 from injector import singleton, inject
 
 from src.constants import DEFAULT_CONNECTION_STRING, TEST_DATABASE_FILE, DEFAULT_SERVER_BASE_URL, LOG_NAME, \
-    LOG_DIR, DEFAULT_LOG_LEVEL
+    LOG_DIR, DEFAULT_LOG_LEVEL, LOG_FILENAME
 from src.constants import LOG_FILE_LOCATION, LOG_FORMAT
 from src.utils.logger import Logger
 
@@ -88,7 +88,7 @@ def configure_logging(config: Configuration):
         os.makedirs(LOG_DIR)
 
     handler = logging.handlers.TimedRotatingFileHandler(
-        os.path.join(LOG_DIR, "application.log"),
+        os.path.join(LOG_DIR, LOG_FILENAME),
         when='H', interval=1)
     formatter = logging.Formatter(LOG_FORMAT)
     handler.setFormatter(formatter)
diff --git a/src/config/connection_provider.py b/src/config/connection_provider.py
index 6bc7a11..63d2ff3 100644
--- a/src/config/connection_provider.py
+++ b/src/config/connection_provider.py
@@ -1,11 +1,13 @@
+import os
 import sqlite3
 from sqlite3 import Connection
 
 from injector import Module, provider, singleton
 
 from src.config.configuration import Configuration
-from src.db.init_queries import DEFAULT_VALUES_SQL
-from src.db.setup_database import SCHEMA_SQL
+from src.constants import DB_DIR
+from src.db.init_queries import DEFAULT_VALUES_SQL, CHECK_SCHEMA_SQL
+from src.db.setup_database import setup_database
 from src.utils.logger import Logger
 
 
@@ -26,9 +28,12 @@ class ConnectionProvider(Module):
         Logger.debug(f"Creating a database connection [{configuration.connection_string}].")
 
         try:
+            if not os.path.exists(DB_DIR):
+                os.makedirs(DB_DIR)
+
             co = sqlite3.connect(database=configuration.connection_string, check_same_thread=False)
             cu = co.cursor()
-            cu.executescript(SCHEMA_SQL)  # TODO change setup_database not to drop tables if they exist
+            cu.executescript(CHECK_SCHEMA_SQL)
             cu.executescript(DEFAULT_VALUES_SQL)
         except sqlite3.Error as e:
             Logger.error(f"Unknown error during database setting.")
diff --git a/src/constants.py b/src/constants.py
index 3a445ab..3e03b6a 100644
--- a/src/constants.py
+++ b/src/constants.py
@@ -2,12 +2,14 @@ from src.utils.file_anchor import FileAnchor
 
 INMEMORY_DATABASE_FILE = ":memory:"
 TEST_DATABASE_FILE = INMEMORY_DATABASE_FILE
-DATABASE_FILE = "db/database_sqlite.db"
+DB_DIR = "db"
+DATABASE_FILE = f"{DB_DIR}/database_sqlite.db"
 DATABASE_FILE_LOCATION = FileAnchor("aswi2021jmsd", DATABASE_FILE)
 DATETIME_FORMAT = "%d.%m.%Y %H:%M:%S"
 
 LOG_DIR = "logs"
-LOG_FILE = f"{LOG_DIR}/application.log"
+LOG_FILENAME = "application.log"
+LOG_FILE = f"{LOG_DIR}/{LOG_FILENAME}"
 LOG_FILE_LOCATION = FileAnchor("aswi2021jmsd", LOG_FILE)
 LOG_FORMAT = "%(levelname)-8s %(asctime)s - %(message)s"
 LOG_NAME = "app_logger"
diff --git a/src/db/init_queries.py b/src/db/init_queries.py
index 71117ac..fb38931 100644
--- a/src/db/init_queries.py
+++ b/src/db/init_queries.py
@@ -1,7 +1,7 @@
 SCHEMA_SQL = """
 /* ---------------------------------------------------- */
 /*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 29-dub-2021 11:55:46 				*/
+/*  Created On : 29-dub-2021 11:55:46 				    */
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
@@ -83,13 +83,76 @@ CREATE TABLE 'UsageTypes'
 
 """
 
-DEFAULT_VALUES_SQL = """
-/* ---------------------------------------------------- */
-/*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 26-bře-2021 13:33:05 				*/
-/*  DBMS       : SQLite 								*/
-/* ---------------------------------------------------- */
+CHECK_SCHEMA_SQL = """
+/* Drop Tables */
+DROP TABLE IF EXISTS 'CertificateTypes'
+;
+
+DROP TABLE IF EXISTS 'CertificateUsages'
+;
+
+/* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */
+
+CREATE TABLE IF NOT EXISTS 'Certificates'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'valid_from' TEXT NOT NULL,
+	'valid_to' TEXT NOT NULL,
+	'pem_data' TEXT NOT NULL,
+	'common_name' TEXT NOT NULL,
+	'country_code' TEXT NULL,
+	'locality' TEXT NULL,
+	'province' TEXT NULL,
+	'organization' TEXT NULL,
+	'organizational_unit' TEXT NULL,
+	'email_address' TEXT NULL,
+	'revocation_date' TEXT NULL,
+	'revocation_reason' TEXT NULL,
+	'deletion_date' TEXT NULL,
+	'certificate_type_id' INTEGER NOT NULL,
+	'parent_certificate_id' INTEGER NOT NULL,
+	'private_key_id' INTEGER NOT NULL,
+	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('parent_certificate_id') REFERENCES 'Certificates' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_CertificateTypes' FOREIGN KEY ('certificate_type_id') REFERENCES 'CertificateTypes' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_PrivateKeys' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKeys' ('id') ON DELETE No Action ON UPDATE No Action
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'CertificateTypes'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_type' TEXT NOT NULL
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'CertificateUsages'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_id' INTEGER NOT NULL,
+	'usage_type_id' INTEGER NOT NULL,
+	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('certificate_id') REFERENCES 'Certificates' ('id') ON DELETE Cascade ON UPDATE No Action,
+	CONSTRAINT 'FK_UsageTypes' FOREIGN KEY ('usage_type_id') REFERENCES 'UsageTypes' ('id') ON DELETE No Action ON UPDATE No Action
+)
+;
 
+CREATE TABLE IF NOT EXISTS 'PrivateKeys'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'private_key' TEXT NOT NULL,
+	'password' TEXT NULL
+)
+;
+
+CREATE TABLE IF NOT EXISTS 'UsageTypes'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'usage_type' TEXT NOT NULL
+)
+;
+
+"""
+
+DEFAULT_VALUES_SQL = """
 /* Insert default values */
 
 INSERT INTO CertificateTypes(certificate_type) VALUES('ROOT_CA');
diff --git a/src/db/setup_database.py b/src/db/setup_database.py
index 07014d6..a713b41 100644
--- a/src/db/setup_database.py
+++ b/src/db/setup_database.py
@@ -1,6 +1,8 @@
+import os
 import sqlite3
+from pathlib import Path
 
-from src.constants import DATABASE_FILE_LOCATION
+from src.constants import DATABASE_FILE_LOCATION, DB_DIR
 from src.db.init_queries import SCHEMA_SQL, DEFAULT_VALUES_SQL
 from src.utils.logger import Logger
 
@@ -9,6 +11,11 @@ def setup_database():
     Logger.debug(f"Database is setting.")
 
     try:
+        # TODO improve creating DB dir
+        if not os.path.exists(DB_DIR):
+            os.makedirs("../../" + DB_DIR)
+
+        # TODO improve creating DB file (now via FileAnchor)
         co = sqlite3.connect(DATABASE_FILE_LOCATION.shortest_relative_path())
         cu = co.cursor()
         cu.executescript(SCHEMA_SQL)
@@ -21,4 +28,4 @@ def setup_database():
 
 
 if __name__ == '__main__':
-    setup_database()
\ No newline at end of file
+    setup_database()
