unify db queryingv6.2408.0

3 files changed, 93 insertions, 84 deletions

diff --git a/src/backend/__init__.py b/src/backend/__init__.py
index 2f87096..0e54ce9 100644
--- a/src/backend/__init__.py
+++ b/src/backend/__init__.py
@@ -7,6 +7,7 @@ from .killemerge import *
 from .pkgadd import *
 from .pkgremove import *
 from .purgeenv import *
+from .querydb import *
 from .recoverdb import *
 from .solvedeps import *
 from .solverevdeps import *
diff --git a/src/backend/querydb.py b/src/backend/querydb.py
new file mode 100644
index 0000000..bc87123
--- /dev/null
+++ b/src/backend/querydb.py
@@ -0,0 +1,89 @@
+#!usr/bin/python3
+
+def start(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 = 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}
+                    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 = 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}''',
+        '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 = 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 iv <> av'''
+    }
+
+    return SELECTS[filter]
+
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