Revize 47e0e828

Re #8471 - Added FK of parent certificate

SQLite_database.sql

@@ -1 +1 @@
 /* ---------------------------------------------------- */
 /*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 31-b?e-2021 10:24:41 				*/
+/*  Created On : 31-b?e-2021 13:28:01 				*/
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
@@ -54 +54 @@
 	'pem_data' TEXT NOT NULL,
 	'private_key_id' INTEGER NOT NULL,
 	'certificate_type_id' INTEGER NOT NULL,
+	'parent_certificate_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

@@ -26 +26 @@
 COL_PEM_DATA = "pem_data"
 COL_PRIVATE_KEY_ID = "private_key_id"
 COL_TYPE_ID = "certificate_type_id"
+COL_PARENT_ID = "parent_certificate_id"
 
 # DB column names of PrivateKeys table
 COL_PRIVATE_KEY = "private_key"

src/db_objects/certificate.py

@@ -11 +11 @@
                  pem_data: str,
                  private_key_id: int,
                  type_id: int,
+                 parent_id: int,
                  usages: Dict[int, bool]):
         self.certificate_id: int = certificate_id
         self.common_name: str = common_name
@@ -19 +20 @@
         self.pem_data: str = pem_data
         self.private_key_id: int = private_key_id
         self.type_id: int = type_id
+        self.parent_id: int = parent_id
         self.usages: Dict[int, bool] = DICT_USAGES.copy()
 
         for usage_id, usage_value in usages.items():

src/impl/certificate_repository_impl.py

@@ -9 +9 @@
 class CertificateRepositoryImpl(IRepository):
 
     def create(self, common_name: str, valid_from: str, valid_to: str, pem_data: str,
-               private_key_id: int, type_id: int, usages: Dict[int, bool]) -> bool:
+               private_key_id: int, type_id: int, parent_id: int, usages: Dict[int, bool]) -> bool:
         sql = (f"INSERT INTO {TAB_CERTIFICATES} ({COL_COMMON_NAME},{COL_VALID_FROM},{COL_VALID_TO},{COL_PEM_DATA},"
-               f"{COL_PRIVATE_KEY_ID},{COL_TYPE_ID})"
+               f"{COL_PRIVATE_KEY_ID},{COL_TYPE_ID},{COL_PARENT_ID})"
                f"VALUES(?,?,?,?,?,?,?)")
-        result = DBManager.create(sql, [common_name, valid_from, valid_to, pem_data, private_key_id, type_id], usages)
+        result = DBManager.create(sql, [common_name, valid_from, valid_to, pem_data,
+                                        private_key_id, type_id, parent_id], usages)
         return result
 
     def read(self, certificate_id: int = None):
@@ -42 +43 @@
                                             certificate_row[4],
                                             certificate_row[5],
                                             certificate_row[6],
+                                            certificate_row[7],
                                             usage_dict))
 
         if certificate_id is not None:
@@ -50 +52 @@
         return certificates
 
     def update(self, certificate_id: int, common_name: str = None, valid_from: str = None, valid_to: str = None,
-               pem_data: str = None, private_key_id: int = None, type_id: int = None,
+               pem_data: str = None, private_key_id: int = None, type_id: int = None, parent_id: int = None,
                usages: Dict[int, bool] = None) -> bool:
         updated_list = []
         values = []
@@ -72 +74 @@
         if type_id is not None:
             updated_list.append(f"{COL_TYPE_ID} = ?")
             values.append(type_id)
+        if parent_id is not None:
+            updated_list.append(f"{COL_PARENT_ID} = ?")
+            values.append(parent_id)
 
         updated_str = ", ".join(updated_list)
         sql = f"UPDATE {TAB_CERTIFICATES} SET {updated_str} WHERE {COL_ID} = ?"