add: graphs et rushs

4 files changed, 69 insertions, 0 deletions

diff --git a/graphs/sql/vault_cataloging/req01.sql b/graphs/sql/vault_cataloging/req01.sql
new file mode 100644
index 0000000..25394d0
--- /dev/null
+++ b/graphs/sql/vault_cataloging/req01.sql
@@ -0,0 +1,12 @@
+SELECT
+    F.id,
+    F.filename,
+    CASE
+        WHEN F.decrypted THEN 'File was successfully decrypted.'
+        WHEN P.decrypted THEN 'File was successfully decrypted because its containing folder was successfully decrypted.'
+        ELSE 'File remains encrypted.'
+    END AS decryption_status
+FROM dtf.madelines_files_results AS F
+LEFT JOIN dtf.madelines_files_results AS P
+ON P.id = F.parent_id
+ORDER BY F.id
diff --git a/graphs/sql/vault_cataloging/req02.sql b/graphs/sql/vault_cataloging/req02.sql
new file mode 100644
index 0000000..bdfecaa
--- /dev/null
+++ b/graphs/sql/vault_cataloging/req02.sql
@@ -0,0 +1,14 @@
+SELECT
+    id,
+    madelines_files_results.size AS stored_size,
+    d.decrypted,
+    COALESCE(madelines_files_results.size, d.size) AS calculated_size
+FROM dtf.madelines_files_results
+LEFT JOIN
+(
+    SELECT decrypted,AVG(size)::bigint AS size FROM dtf.madelines_files_results
+    WHERE parent_id IS NOT NULL
+    GROUP BY decrypted
+) AS d ON madelines_files_results.decrypted = d.decrypted
+WHERE parent_id IS NOT NULL
+ORDER BY madelines_files_results.id
diff --git a/graphs/sql/vault_cataloging/req03.sql b/graphs/sql/vault_cataloging/req03.sql
new file mode 100644
index 0000000..12dd9cf
--- /dev/null
+++ b/graphs/sql/vault_cataloging/req03.sql
@@ -0,0 +1,11 @@
+SELECT
+    filename,
+    LEAST(rsa_time,hyper_pulse_time,quantum_x_time,aes_time,d_crypt_time) AS best_time,
+    GREATEST(rsa_time,hyper_pulse_time,quantum_x_time,aes_time,d_crypt_time) AS worst_time
+FROM dtf.madelines_files_results
+ORDER BY (
+    CASE
+        WHEN filename LIKE '.%' THEN 1
+        ELSE 2
+    END
+),filename
diff --git a/graphs/sql/vault_cataloging/req04.sql b/graphs/sql/vault_cataloging/req04.sql
new file mode 100644
index 0000000..9276b1c
--- /dev/null
+++ b/graphs/sql/vault_cataloging/req04.sql
@@ -0,0 +1,32 @@
+WITH filtered_times AS (
+    SELECT
+        COALESCE(
+            NULLIF(rsa_time,0),
+            (SELECT MAX(rsa_time) FROM dtf.madelines_files_results)
+        ) AS rsa_time,
+        COALESCE(
+            NULLIF(hyper_pulse_time,0),
+            (SELECT MAX(hyper_pulse_time) FROM dtf.madelines_files_results)
+        ) AS hyper_pulse_time,
+        COALESCE(
+            NULLIF(quantum_x_time,0),
+            (SELECT MAX(quantum_x_time) FROM dtf.madelines_files_results)
+        ) AS quantum_x_time,
+        COALESCE(
+            NULLIF(aes_time,0),
+            (SELECT MAX(aes_time) FROM dtf.madelines_files_results)
+        ) AS aes_time,
+        COALESCE(
+            NULLIF(d_crypt_time,0),
+            (SELECT MAX(d_crypt_time) FROM dtf.madelines_files_results)
+        ) AS d_crypt_time
+    FROM dtf.madelines_files_results
+)
+
+SELECT
+    AVG(rsa_time)::numeric(10,2) AS avg_rsa_time,
+    AVG(hyper_pulse_time)::numeric(10,2) AS avg_hyper_pulse_time,
+    AVG(quantum_x_time)::numeric(10,2) AS avg_quantum_x_time,
+    AVG(aes_time)::numeric(10,2) AS avg_aes_time,
+    AVG(d_crypt_time)::numeric(10,2) AS avg_d_crypt_time
+FROM filtered_times