Revize 0e7c3096

Re #8590 - Implemented all fields of subject into DB

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:55:46 				*/
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
@@ -26 +26 @@
 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,
+	'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,
-	'private_key_id' INTEGER NOT 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

src/constants.py

@@ -37 +37 @@
 COL_ID = "id"
 
 # DB column names of Certificates table
-COL_COMMON_NAME = "common_name"
 COL_VALID_FROM = "valid_from"
 COL_VALID_TO = "valid_to"
 COL_PEM_DATA = "pem_data"
+COL_COMMON_NAME = "common_name"
+COL_COUNTRY_CODE = "country_code"
+COL_LOCALITY = "locality"
+COL_PROVINCE = "province"
+COL_ORGANIZATION = "organization"
+COL_ORGANIZATIONAL_UNIT = "organizational_unit"
+COL_EMAIL_ADDRESS = "email_address"
 COL_REVOCATION_DATE = "revocation_date"
 COL_REVOCATION_REASON = "revocation_reason"
 COL_DELETION_DATE = "deletion_date"
-COL_PRIVATE_KEY_ID = "private_key_id"
 COL_TYPE_ID = "certificate_type_id"
 COL_PARENT_ID = "parent_certificate_id"
+COL_PRIVATE_KEY_ID = "private_key_id"
 
 # DB column names of PrivateKeys table
 COL_PRIVATE_KEY = "private_key"

src/dao/certificate_repository.py

@@ -42 +42 @@
 
         try:
             sql = (f"INSERT INTO {TAB_CERTIFICATES} "
-                   f"({COL_COMMON_NAME},"
-                   f"{COL_VALID_FROM},"
+                   f"({COL_VALID_FROM},"
                    f"{COL_VALID_TO},"
                    f"{COL_PEM_DATA},"
-                   f"{COL_PRIVATE_KEY_ID},"
+                   f"{COL_COMMON_NAME},"
+                   f"{COL_COUNTRY_CODE},"
+                   f"{COL_LOCALITY},"
+                   f"{COL_PROVINCE},"
+                   f"{COL_ORGANIZATION},"
+                   f"{COL_ORGANIZATIONAL_UNIT},"
+                   f"{COL_EMAIL_ADDRESS},"
                    f"{COL_TYPE_ID},"
-                   f"{COL_PARENT_ID})"
-                   f"VALUES(?,?,?,?,?,?,?)")
-            values = [certificate.common_name,
-                      certificate.valid_from,
+                   f"{COL_PARENT_ID},"
+                   f"{COL_PRIVATE_KEY_ID}) "
+                   f"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)")
+            values = [certificate.valid_from,
                       certificate.valid_to,
                       certificate.pem_data,
-                      certificate.private_key_id,
+                      certificate.common_name,
+                      certificate.country_code,
+                      certificate.locality,
+                      certificate.province,
+                      certificate.organization,
+                      certificate.organizational_unit,
+                      certificate.email_address,
                       certificate.type_id,
-                      certificate.parent_id]
+                      certificate.parent_id,
+                      certificate.private_key_id]
             self.cursor.execute(sql, values)
             self.connection.commit()
 
             last_id: int = self.cursor.lastrowid
 
-            # TODO assure that this is correct
             if certificate.type_id == ROOT_CA_ID:
                 certificate.parent_id = last_id
                 self.update(last_id, certificate)
@@ -127 +138 @@
             for usage_row in usage_rows:
                 usage_dict[usage_row[2]] = True
 
-            certificate: Certificate = Certificate(certificate_row[0],
-                                                   certificate_row[1],
-                                                   certificate_row[2],
-                                                   certificate_row[3],
-                                                   certificate_row[4],
-                                                   certificate_row[8],
-                                                   certificate_row[9],
-                                                   certificate_row[10],
+            certificate: Certificate = Certificate(certificate_row[0],      # ID
+                                                   certificate_row[1],      # valid from
+                                                   certificate_row[2],      # valid to
+                                                   certificate_row[3],      # pem data
+                                                   certificate_row[14],     # type ID
+                                                   certificate_row[15],     # parent ID
+                                                   certificate_row[16],     # private key ID
                                                    usage_dict,
-                                                   certificate_row[5],
-                                                   certificate_row[6])
+                                                   certificate_row[4],      # common name
+                                                   certificate_row[5],      # country code
+                                                   certificate_row[6],      # locality
+                                                   certificate_row[7],      # province
+                                                   certificate_row[8],      # organization
+                                                   certificate_row[9],      # organizational unit
+                                                   certificate_row[10],     # email address
+                                                   certificate_row[11],     # revocation date
+                                                   certificate_row[12])     # revocation reason
+
         except IntegrityError:
             Logger.error(INTEGRITY_ERROR_MSG)
             raise DatabaseException(INTEGRITY_ERROR_MSG)
@@ -195 +213 @@
                 for usage_row in usage_rows:
                     usage_dict[usage_row[2]] = True
 
-                certificates.append(Certificate(certificate_row[0],
-                                                certificate_row[1],
-                                                certificate_row[2],
-                                                certificate_row[3],
-                                                certificate_row[4],
-                                                certificate_row[8],
-                                                certificate_row[9],
-                                                certificate_row[10],
+                certificates.append(Certificate(certificate_row[0],      # ID
+                                                certificate_row[1],      # valid from
+                                                certificate_row[2],      # valid to
+                                                certificate_row[3],      # pem data
+                                                certificate_row[14],     # type ID
+                                                certificate_row[15],     # parent ID
+                                                certificate_row[16],     # private key ID
                                                 usage_dict,
-                                                certificate_row[5],
-                                                certificate_row[6]))
+                                                certificate_row[4],      # common name
+                                                certificate_row[5],      # country code
+                                                certificate_row[6],      # locality
+                                                certificate_row[7],      # province
+                                                certificate_row[8],      # organization
+                                                certificate_row[9],      # organizational unit
+                                                certificate_row[10],     # email address
+                                                certificate_row[11],     # revocation date
+                                                certificate_row[12]))    # revocation reason
         except IntegrityError:
             Logger.error(INTEGRITY_ERROR_MSG)
             raise DatabaseException(INTEGRITY_ERROR_MSG)
@@ -243 +267 @@
 
         try:
             sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_COMMON_NAME} = ?, "
-                   f"{COL_VALID_FROM} = ?, "
+                   f"SET {COL_VALID_FROM} = ?, "
                    f"{COL_VALID_TO} = ?, "
                    f"{COL_PEM_DATA} = ?, "
-                   f"{COL_PRIVATE_KEY_ID} = ?, "
+                   f"{COL_COMMON_NAME} = ?, "
+                   f"{COL_COUNTRY_CODE} = ?, "
+                   f"{COL_LOCALITY} = ?, "
+                   f"{COL_PROVINCE} = ?, "
+                   f"{COL_ORGANIZATION} = ?, "
+                   f"{COL_ORGANIZATIONAL_UNIT} = ?, "
+                   f"{COL_EMAIL_ADDRESS} = ?, "
                    f"{COL_TYPE_ID} = ?, "
-                   f"{COL_PARENT_ID} = ? "
+                   f"{COL_PARENT_ID} = ?, "
+                   f"{COL_PRIVATE_KEY_ID} = ? "
                    f"WHERE {COL_ID} = ?")
-            values = [certificate.common_name,
-                      certificate.valid_from,
+            values = [certificate.valid_from,
                       certificate.valid_to,
                       certificate.pem_data,
-                      certificate.private_key_id,
+                      certificate.common_name,
+                      certificate.country_code,
+                      certificate.locality,
+                      certificate.province,
+                      certificate.organization,
+                      certificate.organizational_unit,
+                      certificate.email_address,
                       certificate.type_id,
                       certificate.parent_id,
+                      certificate.private_key_id,
                       certificate_id]
 
             self.cursor.execute(sql, values)
@@ -409 +445 @@
 
         try:
             sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_REVOCATION_DATE} = '', "
-                   f"{COL_REVOCATION_REASON} = '' "
+                   f"SET {COL_REVOCATION_DATE} = NULL, "
+                   f"{COL_REVOCATION_REASON} = NULL "
                    f"WHERE {COL_ID} = ?")
             values = [certificate_id]
             self.cursor.execute(sql, values)
@@ -469 +505 @@
                 for usage_row in usage_rows:
                     usage_dict[usage_row[2]] = True
 
-                certificates.append(Certificate(certificate_row[0],
-                                                certificate_row[1],
-                                                certificate_row[2],
-                                                certificate_row[3],
-                                                certificate_row[4],
-                                                certificate_row[8],
-                                                certificate_row[9],
-                                                certificate_row[10],
+                certificates.append(Certificate(certificate_row[0],      # ID
+                                                certificate_row[1],      # valid from
+                                                certificate_row[2],      # valid to
+                                                certificate_row[3],      # pem data
+                                                certificate_row[14],     # type ID
+                                                certificate_row[15],     # parent ID
+                                                certificate_row[16],     # private key ID
                                                 usage_dict,
-                                                certificate_row[5],
-                                                certificate_row[6]))
+                                                certificate_row[4],      # common name
+                                                certificate_row[5],      # country code
+                                                certificate_row[6],      # locality
+                                                certificate_row[7],      # province
+                                                certificate_row[8],      # organization
+                                                certificate_row[9],      # organizational unit
+                                                certificate_row[10],     # email address
+                                                certificate_row[11],     # revocation date
+                                                certificate_row[12]))    # revocation reason
         except IntegrityError:
             Logger.error(INTEGRITY_ERROR_MSG)
             raise DatabaseException(INTEGRITY_ERROR_MSG)
@@ -535 +577 @@
                 for usage_row in usage_rows:
                     usage_dict[usage_row[2]] = True
 
-                certificates.append(Certificate(certificate_row[0],
-                                                certificate_row[1],
-                                                certificate_row[2],
-                                                certificate_row[3],
-                                                certificate_row[4],
-                                                certificate_row[8],
-                                                certificate_row[9],
-                                                certificate_row[10],
+                certificates.append(Certificate(certificate_row[0],      # ID
+                                                certificate_row[1],      # valid from
+                                                certificate_row[2],      # valid to
+                                                certificate_row[3],      # pem data
+                                                certificate_row[14],     # type ID
+                                                certificate_row[15],     # parent ID
+                                                certificate_row[16],     # private key ID
                                                 usage_dict,
-                                                certificate_row[5],
-                                                certificate_row[6]))
+                                                certificate_row[4],      # common name
+                                                certificate_row[5],      # country code
+                                                certificate_row[6],      # locality
+                                                certificate_row[7],      # province
+                                                certificate_row[8],      # organization
+                                                certificate_row[9],      # organizational unit
+                                                certificate_row[10],     # email address
+                                                certificate_row[11],     # revocation date
+                                                certificate_row[12]))    # revocation reason
         except IntegrityError:
             Logger.error(INTEGRITY_ERROR_MSG)
             raise DatabaseException(INTEGRITY_ERROR_MSG)

src/db/init_queries.py

@@ -1 +1 @@
 SCHEMA_SQL = """
 /* ---------------------------------------------------- */
 /*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 27-dub-2021 11:31:54 				*/
+/*  Created On : 29-dub-2021 11:55:46 				*/
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
@@ -27 +27 @@
 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,
+	'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,
-	'private_key_id' INTEGER NOT 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

src/model/certificate.py

@@ -3 +3 @@
 
 
 class Certificate:
+
     def __init__(self,
                  certificate_id: int,
-                 common_name: str,
                  valid_from: str,
                  valid_to: str,
                  pem_data: str,
-                 private_key_id: int,
                  type_id: int,
                  parent_id: int,
+                 private_key_id: int,
                  usages: Dict[int, bool],
-                 revocation_date: str = "",
-                 revocation_reason: str = ""):
+                 common_name: str,
+                 country_code: str = None,
+                 locality: str = None,
+                 province: str = None,
+                 organization: str = None,
+                 organizational_unit: str = None,
+                 email_address: str = None,
+                 revocation_date: str = None,
+                 revocation_reason: str = None):
         self.certificate_id: int = certificate_id
-        self.common_name: str = common_name
         self.valid_from: str = valid_from
         self.valid_to: str = valid_to
         self.pem_data: str = pem_data
-        self.revocation_date = revocation_date
-        self.revocation_reason = revocation_reason
-        self.private_key_id: int = private_key_id
         self.type_id: int = type_id
         self.parent_id: int = parent_id
+        self.private_key_id: int = private_key_id
+        self.common_name: str = common_name
+        self.country_code: str = country_code
+        self.locality: str = locality
+        self.province: str = province
+        self.organization: str = organization
+        self.organizational_unit: str = organizational_unit
+        self.email_address: str = email_address
+        self.revocation_date: str = revocation_date
+        self.revocation_reason: str = revocation_reason
         self.usages: Dict[int, bool] = DICT_USAGES.copy()
 
-        if revocation_date is None:
-            self.revocation_date = ""
+        #if revocation_date is None:
+        #    self.revocation_date = ""
 
-        if revocation_reason is None:
-            self.revocation_reason = ""
+        #if revocation_reason is None:
+        #    self.revocation_reason = ""
 
         for usage_id, usage_value in usages.items():
             self.usages[usage_id] = usage_value