Revize 07a6c869

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

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');