From e327ad76cfd12c8d70b8f9bee5f2b86c975ed073 Mon Sep 17 00:00:00 2001 From: V3n3RiX Date: Sat, 31 Aug 2024 00:18:59 +0100 Subject: unify db querying --- src/backend/__init__.py | 1 + src/backend/querydb.py | 89 +++++++++++++++++++++++++++++++++++++++++++++++++ src/backend/searchdb.py | 87 ++--------------------------------------------- 3 files changed, 93 insertions(+), 84 deletions(-) create mode 100644 src/backend/querydb.py (limited to 'src/backend') 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 -- cgit v1.2.3