Revize e1c31cac

Re #8590 - Implemented new table of subjects
- from table of certificates is a linkage 1:1_N (Subject : Certificates)
- table contains all fields of subject (CN, C, L, ST, O, OU, Mail)

SQLite_database.sql

@@ -1 +1 @@
 /* ---------------------------------------------------- */
 /*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 27-dub-2021 11:31:54 				*/
+/*  Created On : 29-dub-2021 11:19:55 				*/
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
@@ -18 +18 @@
 DROP TABLE IF EXISTS 'PrivateKeys'
 ;
 
+DROP TABLE IF EXISTS 'Subjects'
+;
+
 DROP TABLE IF EXISTS 'UsageTypes'
 ;
 
@@ -26 +29 @@
 CREATE TABLE 'Certificates'
 (
 	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'common_name' TEXT NOT NULL,
 	'valid_from' TEXT NOT NULL,
 	'valid_to' TEXT NOT NULL,
 	'pem_data' TEXT NOT NULL,
 	'revocation_date' TEXT NULL,
-	'revocation_reason' TEXT NULL,
 	'deletion_date' TEXT NULL,
-	'private_key_id' INTEGER NOT NULL,
+	'revocation_reason' TEXT NULL,
 	'certificate_type_id' INTEGER NOT NULL,
 	'parent_certificate_id' INTEGER NOT NULL,
+	'subject_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
+	CONSTRAINT 'FK_PrivateKeys' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKeys' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_Subjects' FOREIGN KEY ('subject_id') REFERENCES 'Subjects' ('id') ON DELETE No Action ON UPDATE No Action
 )
 ;
 
@@ -67 +71 @@
 )
 ;
 
+CREATE TABLE 'Subjects'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'common_name' TEXT NULL,
+	'country_code' TEXT NULL,
+	'locality' TEXT NULL,
+	'province' TEXT NULL,
+	'organization' TEXT NULL,
+	'organizational_unit' TEXT NULL,
+	'email_address' TEXT NULL
+)
+;
+
 CREATE TABLE 'UsageTypes'
 (
 	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,