Revize 07a6c869

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

SQLite_database_check.sql

@@ +1 @@
+/* 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
+)
+;

src/config/configuration.py

@@ -7 +7 @@
 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 +88 @@
         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)

src/config/connection_provider.py

@@ +1 @@
+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 +28 @@
         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.")

src/constants.py

@@ -2 +2 @@
 
 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"

src/db/init_queries.py

@@ -1 +1 @@
 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 +83 @@
 
 """
 
-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');

src/db/setup_database.py

@@ +1 @@
+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 +11 @@
     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 +28 @@
 
 
 if __name__ == '__main__':
-    setup_database()
+    setup_database()