Revize 8b049f43

Re #8578 - Updated certificate and certificate_repository
- certificate initialize revocation_date and revocation_reason on "" if input is None
- certificate_repository
- repaired error after tests
- edited comments (return)
- stylistics

src/dao/certificate_repository.py

@@ -29 +29 @@
         """
 
         try:
+            if certificate.revocation_date != "" and certificate.revocation_reason == "":
+                certificate.revocation_reason = REV_REASON_UNSPECIFIED
+            elif certificate.revocation_date == "":
+                certificate.revocation_reason = ""
+
             sql = (f"INSERT INTO {TAB_CERTIFICATES} "
                    f"({COL_COMMON_NAME},"
                    f"{COL_VALID_FROM},"
                    f"{COL_VALID_TO},"
                    f"{COL_PEM_DATA},"
-                   f"{COL_REVOCATION_DATE},"
-                   f"{COL_REVOCATION_REASON}"
                    f"{COL_PRIVATE_KEY_ID},"
                    f"{COL_TYPE_ID},"
                    f"{COL_PARENT_ID})"
@@ -44 +47 @@
                       certificate.valid_from,
                       certificate.valid_to,
                       certificate.pem_data,
-                      certificate.revocation_date,
-                      certificate.revocation_reason,
                       certificate.private_key_id,
                       certificate.type_id,
                       certificate.parent_id]
@@ -137 +138 @@
                                  f"SELECT {COL_ID} FROM {TAB_CERTIFICATE_TYPES} WHERE {COL_ID} = ?)")
                 values = [filter_type]
 
-            sql = (f"SELECT * FROM {TAB_CERTIFICATES}{sql_extension}")
+            sql = f"SELECT * FROM {TAB_CERTIFICATES}{sql_extension}"
             self.cursor.execute(sql, values)
             certificate_rows = self.cursor.fetchall()
 
@@ -185 +186 @@
         """
 
         try:
+            if certificate.revocation_date != "" and certificate.revocation_reason == "":
+                certificate.revocation_reason = REV_REASON_UNSPECIFIED
+            elif certificate.revocation_date == "":
+                certificate.revocation_reason = ""
+
             sql = (f"UPDATE {TAB_CERTIFICATES} "
                    f"SET {COL_COMMON_NAME} = ?, "
                    f"{COL_VALID_FROM} = ?, "
                    f"{COL_VALID_TO} = ?, "
                    f"{COL_PEM_DATA} = ?, "
-                   f"{COL_REVOCATION_DATE} = ?, "
-                   f"{COL_REVOCATION_REASON} = ?, "
                    f"{COL_PRIVATE_KEY_ID} = ?, "
                    f"{COL_TYPE_ID} = ?, "
                    f"{COL_PARENT_ID} = ? "
@@ -200 +204 @@
                       certificate.valid_from,
                       certificate.valid_to,
                       certificate.pem_data,
-                      certificate.revocation_date,
-                      certificate.revocation_reason,
                       certificate.private_key_id,
                       certificate.type_id,
                       certificate.parent_id,
@@ -228 +230 @@
         except Error as e:
             return e
 
-        return True
+        if self.cursor.rowcount > 0:
+            return True
+
+        return False
 
     def delete(self, certificate_id: int):
         """
@@ -251 +256 @@
         return self.cursor.rowcount > 0
 
     def set_certificate_revoked(
-            self, certificate_id: int, revocation_date: str, revocation_reason: str = "unspecified"):
+            self, certificate_id: int, revocation_date: str, revocation_reason: str = REV_REASON_UNSPECIFIED):
         """
         Revoke a certificate
 
@@ -259 +264 @@
         :param revocation_date: Date, when the certificate is revoked
         :param revocation_reason: Reason of the revocation
 
-        :return: the result of whether the revocation was successful
+        :return:
+            the result of whether the revocation was successful OR
+            sqlite3.Error if an exception is thrown
         """
 
         try:
+            if revocation_date != "" and revocation_reason == "":
+                revocation_reason = REV_REASON_UNSPECIFIED
+            elif revocation_date == "":
+                return False
+
             sql = (f"UPDATE {TAB_CERTIFICATES} "
                    f"SET {COL_REVOCATION_DATE} = ?, "
-                   f"{COL_REVOCATION_REASON} = ?, "
+                   f"{COL_REVOCATION_REASON} = ? "
                    f"WHERE {COL_ID} = ? AND ({COL_REVOCATION_DATE} IS NULL OR {COL_REVOCATION_DATE} = '')")
             values = [revocation_date,
                       revocation_reason,
@@ -275 +287 @@
         except Error as e:
             return e
 
-        return True
+        if self.cursor.rowcount > 0:
+            return True
+
+        return False
 
     def clear_certificate_revocation(self, certificate_id: int):
         """
@@ -283 +298 @@
 
         :param certificate_id: ID of specific certificate
 
-        :return: the result of whether the clear revocation was successful
+        :return:
+            the result of whether the clear revocation was successful OR
+            sqlite3.Error if an exception is thrown
         """
 
         try:
             sql = (f"UPDATE {TAB_CERTIFICATES} "
                    f"SET {COL_REVOCATION_DATE} = '', "
-                   f"{COL_REVOCATION_REASON} = '', "
+                   f"{COL_REVOCATION_REASON} = '' "
                    f"WHERE {COL_ID} = ?")
             values = [certificate_id]
             self.cursor.execute(sql, values)
@@ -306 +323 @@
         :param certificate_id: ID of specific certificate
 
         :return:
-            # list of the certificates
-            # None if the list is empty
-            # sqlite3.Error if an exception is thrown
+            list of the certificates OR
+            None if the list is empty OR
+            sqlite3.Error if an exception is thrown
         """
 
         try:
@@ -356 +373 @@
         :param certificate_id: ID of specific certificate
 
         :return:
-            # list of the certificates
-            # None if the list is empty
-            # sqlite3.Error if an exception is thrown
+            list of the certificates OR
+            None if the list is empty OR
+            sqlite3.Error if an exception is thrown
         """
 
         try:
             sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_PARENT_ID} = ?")
-            values = [certificate_id]
+                   f"WHERE {COL_PARENT_ID} = ? AND {COL_ID} != ?")
+            values = [certificate_id, certificate_id]
             self.cursor.execute(sql, values)
             certificate_rows = self.cursor.fetchall()