Revize e77c14f9

Re #8706 - Fixed a bug where certificates without usages would not be returned when the usage filter was unspecified. Added a related integration test.

src/dao/certificate_repository.py

@@ -269 +269 @@
         try:
             values = []
             values += list(target_types)
-            values += list(target_usages)
 
             sql = (
                 f"SELECT * "
                 f"FROM {TAB_CERTIFICATES} a "
                 f"WHERE (a.{COL_DELETION_DATE} IS NULL OR a.{COL_DELETION_DATE} = '') "
                 f"AND a.{COL_TYPE_ID} IN ({','.join('?' * len(target_types))}) "
-                f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} "
-                f"WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} "
-                f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))}) "
-                f") > 0 "
             )
+            
+            if target_usages is not None:
+                sql += f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} " \
+                    f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))})) > 0 "
+                values += list(target_usages)
 
             if target_cn_substring is not None:
                 sql += f" AND a.{COL_COMMON_NAME} LIKE '%' || ? || '%'"
@@ -728 +728 @@
         try:
             values = [issuer_id, issuer_id]
             values += list(target_types)
-            values += list(target_usages)
 
             sql = (
                 f"SELECT * "
@@ -736 +735 @@
                 f"WHERE (a.{COL_DELETION_DATE} IS NULL OR a.{COL_DELETION_DATE} = '') "
                 f"AND a.{COL_PARENT_ID} = ? AND a.{COL_ID} != ? "
                 f"AND a.{COL_TYPE_ID} IN ({','.join('?' * len(target_types))}) "
-                f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} "
-                f"WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} "
-                f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))}) "
-                f") > 0 "
+                
             )
+            
+            if target_usages is not None:
+                sql += f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} " \
+                    f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))})) > 0 "
+                values += list(target_usages)
 
             if target_cn_substring is not None:
                 sql += f" AND a.{COL_COMMON_NAME} LIKE '%' || ? || '%'"
-
                 values += [target_cn_substring]
 
             if page is not None and per_page is not None: