commit 8b049f4392c23a470bdb62f2f69476f580a80dc8
Author: David Friesecký <frieseck@students.zcu.cz>
Date:   Tue Apr 13 00:59:47 2021 +0200

    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

diff --git a/src/dao/certificate_repository.py b/src/dao/certificate_repository.py
index 474d73d..dc0d2a8 100644
--- a/src/dao/certificate_repository.py
+++ b/src/dao/certificate_repository.py
@@ -29,13 +29,16 @@ class CertificateRepository:
         """
 
         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,8 +47,6 @@ class CertificateRepository:
                       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,7 +138,7 @@ class CertificateRepository:
                                  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,13 +186,16 @@ class CertificateRepository:
         """
 
         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,8 +204,6 @@ class CertificateRepository:
                       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,7 +230,10 @@ class CertificateRepository:
         except Error as e:
             return e
 
-        return True
+        if self.cursor.rowcount > 0:
+            return True
+
+        return False
 
     def delete(self, certificate_id: int):
         """
@@ -251,7 +256,7 @@ class CertificateRepository:
         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,13 +264,20 @@ class CertificateRepository:
         :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,7 +287,10 @@ class CertificateRepository:
         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,13 +298,15 @@ class CertificateRepository:
 
         :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,9 +323,9 @@ class CertificateRepository:
         :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,15 +373,15 @@ class CertificateRepository:
         :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()
 
diff --git a/src/model/certificate.py b/src/model/certificate.py
index ea73f27..2854949 100644
--- a/src/model/certificate.py
+++ b/src/model/certificate.py
@@ -27,5 +27,11 @@ class Certificate:
         self.parent_id: int = parent_id
         self.usages: Dict[int, bool] = DICT_USAGES.copy()
 
+        if revocation_date is None:
+            self.revocation_date = ""
+
+        if revocation_reason is None:
+            self.revocation_reason = ""
+
         for usage_id, usage_value in usages.items():
             self.usages[usage_id] = usage_value
