Revize 6425fa36

Re #8670 - Modified deletion for historical storage of certificates

src/dao/certificate_repository.py

@@ +1 @@
+import time
 from sqlite3 import Connection, Error
 from typing import Dict, List
 
@@ -81 +82 @@
 
         try:
             sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_ID} = ?")
+                   f"WHERE {COL_ID} = ? AND ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
             values = [certificate_id]
             self.cursor.execute(sql, values)
             certificate_row = self.cursor.fetchone()
@@ -103 +104 @@
                                                    certificate_row[2],
                                                    certificate_row[3],
                                                    certificate_row[4],
-                                                   certificate_row[7],
                                                    certificate_row[8],
                                                    certificate_row[9],
+                                                   certificate_row[10],
                                                    usage_dict,
                                                    certificate_row[5],
                                                    certificate_row[6])
@@ -127 +128 @@
             sql_extension = ""
             values = []
             if filter_type is not None:
-                sql_extension = (f" WHERE {COL_TYPE_ID} = ("
+                sql_extension = (f" AND {COL_TYPE_ID} = ("
                                  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} "
+                   f"WHERE ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = ''){sql_extension}")
             self.cursor.execute(sql, values)
             certificate_rows = self.cursor.fetchall()
 
@@ -152 +154 @@
                                                 certificate_row[2],
                                                 certificate_row[3],
                                                 certificate_row[4],
-                                                certificate_row[7],
                                                 certificate_row[8],
                                                 certificate_row[9],
+                                                certificate_row[10],
                                                 usage_dict,
                                                 certificate_row[5],
                                                 certificate_row[6]))
@@ -193 +195 @@
                       certificate.type_id,
                       certificate.parent_id,
                       certificate_id]
+
+            print(f"Parent: {certificate.parent_id}")
+
             self.cursor.execute(sql, values)
             self.connection.commit()
 
@@ -227 +232 @@
         """
 
         try:
-            sql = (f"DELETE FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_ID} = ?")
-            values = [certificate_id]
+            sql = (f"UPDATE {TAB_CERTIFICATES} "
+                   f"SET {COL_DELETION_DATE} = ? "
+                   f"WHERE {COL_ID} = ? AND ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
+            values = [int(time.time()),
+                      certificate_id]
             self.cursor.execute(sql, values)
             self.connection.commit()
         except Error as e:
@@ -331 +338 @@
                                                 certificate_row[2],
                                                 certificate_row[3],
                                                 certificate_row[4],
-                                                certificate_row[7],
                                                 certificate_row[8],
                                                 certificate_row[9],
+                                                certificate_row[10],
                                                 usage_dict,
                                                 certificate_row[5],
                                                 certificate_row[6]))
@@ -356 +363 @@
 
         try:
             sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_PARENT_ID} = ? AND {COL_ID} != ?")
+                   f"WHERE {COL_PARENT_ID} = ? AND {COL_ID} != ? AND "
+                   f"({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
             values = [certificate_id, certificate_id]
             self.cursor.execute(sql, values)
             certificate_rows = self.cursor.fetchall()
@@ -378 +386 @@
                                                 certificate_row[2],
                                                 certificate_row[3],
                                                 certificate_row[4],
-                                                certificate_row[7],
                                                 certificate_row[8],
                                                 certificate_row[9],
+                                                certificate_row[10],
                                                 usage_dict,
                                                 certificate_row[5],
                                                 certificate_row[6]))