From e327ad76cfd12c8d70b8f9bee5f2b86c975ed073 Mon Sep 17 00:00:00 2001
From: V3n3RiX <venerix@koprulu.sector>
Date: Sat, 31 Aug 2024 00:18:59 +0100
Subject: unify db querying

---
 src/backend/searchdb.py | 87 ++-----------------------------------------------
 1 file changed, 3 insertions(+), 84 deletions(-)

(limited to 'src/backend/searchdb.py')

diff --git a/src/backend/searchdb.py b/src/backend/searchdb.py
index 86a5832..36e3cb6 100644
--- a/src/backend/searchdb.py
+++ b/src/backend/searchdb.py
@@ -6,6 +6,7 @@ import subprocess
 import sisyphus.checkenv
 import sisyphus.getclr
 import sisyphus.getfs
+import sisyphus.querydb
 import sisyphus.syncall
 
 
@@ -17,94 +18,12 @@ signal.signal(signal.SIGINT, sigint_handler)
 
 
 def srch_db(filter, cat='', pn='', desc=''):
-    NOVIRT = "AND cat NOT LIKE 'virtual'"
-    SELECTS = {
-        'all': f'''SELECT
-                    i.category AS cat,
-                    i.name as pn,
-                    i.version as iv,
-                    IFNULL(a.version, 'alien') AS av,
-                    d.description AS desc
-                    FROM local_packages AS i LEFT OUTER JOIN remote_packages as a
-                    ON i.category = a.category
-                    AND i.name = a.name
-                    AND i.slot = a.slot
-    				LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
-                    UNION
-                    SELECT
-                    a.category AS cat,
-                    a.name as pn,
-                    IFNULL(i.version, 'None') AS iv,
-                    a.version as av,
-                    d.description AS desc
-                    FROM remote_packages AS a LEFT OUTER JOIN local_packages AS i
-                    ON a.category = i.category
-                    AND a.name = i.name
-                    AND a.slot = i.slot
-    				LEFT JOIN remote_descriptions AS d ON a.name = d.name AND a.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}''',
-        'installed': f'''SELECT
-                    i.category AS cat,
-                    i.name AS pn,
-                    i.version AS iv,
-                    a.version as av,
-                    d.description AS desc
-                    FROM local_packages AS i
-                    LEFT JOIN remote_packages AS a
-                    ON i.category = a.category
-                    AND i.name = a.name
-                    AND i.slot = a.slot
-    				LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}''',
-        'alien': f'''SELECT
-                    i.category AS cat,
-                    i.name AS pn,
-                    i.version as iv,
-                    IFNULL(a.version, 'alien') AS av,
-                    d.description AS desc
-                    FROM local_packages AS i
-                    LEFT JOIN remote_packages AS a
-                    ON a.category = i.category
-                    AND a.name = i.name
-                    AND a.slot = i.slot
-                    LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
-                    AND av IS 'alien' ''',
-        'available': f'''SELECT
-                    a.category AS cat,
-                    a.name AS pn,
-                    i.version as iv,
-                    a.version AS av,
-                    d.description AS desc
-                    FROM remote_packages AS a
-                    LEFT JOIN local_packages AS i
-                    ON a.category = i.category
-                    AND a.name = i.name
-                    AND a.slot = i.slot
-    				LEFT JOIN remote_descriptions AS d ON a.name = d.name AND a.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
-                    AND iv IS NULL''',
-        'upgradable': f'''SELECT
-                    i.category AS cat,
-                    i.name AS pn,
-                    i.version as iv,
-                    a.version AS av,
-                    d.description AS desc
-                    FROM local_packages AS i
-                    INNER JOIN remote_packages AS a
-                    ON i.category = a.category
-                    AND i.name = a.name
-                    AND i.slot = a.slot
-    				LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
-                    WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
-                    AND iv <> av'''
-    }
+    query = sisyphus.querydb.start(filter, cat, pn, desc)
 
     with sqlite3.connect(sisyphus.getfs.lcl_db) as db:
         db.row_factory = sqlite3.Row
         cursor = db.cursor()
-        cursor.execute(SELECTS[filter])
+        cursor.execute(query)
         rows = cursor.fetchall()
 
     return rows
-- 
cgit v1.2.3