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 ++----------------------------- src/frontend/gui/sisyphus-gui.py | 107 +++++---------------------------------- src/frontend/gui/ui/mirrorcfg.ui | 2 +- 5 files changed, 106 insertions(+), 180 deletions(-) create mode 100644 src/backend/querydb.py (limited to 'src') 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 diff --git a/src/frontend/gui/sisyphus-gui.py b/src/frontend/gui/sisyphus-gui.py index 1eb4b72..3225b88 100644 --- a/src/frontend/gui/sisyphus-gui.py +++ b/src/frontend/gui/sisyphus-gui.py @@ -23,7 +23,7 @@ class Sisyphus(QtWidgets.QMainWindow): self.applicationFilter.setCurrentText('Package Name') self.applicationFilter.currentIndexChanged.connect( self.setApplicationFilter) - Sisyphus.applicationView = self.filterApplications['Package Name'] + Sisyphus.appFilter = self.filterApplications['Package Name'] self.filterDatabases = OrderedDict([ ('All Packages', 'all'), @@ -35,7 +35,7 @@ class Sisyphus(QtWidgets.QMainWindow): self.databaseFilter.addItems(self.filterDatabases.keys()) self.databaseFilter.setCurrentText('All Packages') self.databaseFilter.currentIndexChanged.connect(self.setDatabaseFilter) - Sisyphus.databaseView = self.filterDatabases['All Packages'] + Sisyphus.dbFilter = self.filterDatabases['All Packages'] Sisyphus.searchTerm = "'%%'" @@ -124,108 +124,26 @@ class Sisyphus(QtWidgets.QMainWindow): (Sisyphus.pkgCount, Sisyphus.pkgSelect)) def setApplicationFilter(self): - Sisyphus.applicationView = self.filterApplications[self.applicationFilter.currentText( + Sisyphus.appFilter = self.filterApplications[self.applicationFilter.currentText( )] self.loadDatabase() def setDatabaseFilter(self): - Sisyphus.databaseView = self.filterDatabases[self.databaseFilter.currentText( + Sisyphus.dbFilter = self.filterDatabases[self.databaseFilter.currentText( )] Sisyphus.SELECT = self.databaseFilter.currentText() self.loadDatabase() def loadDatabase(self): - noVirtual = "AND cat NOT LIKE 'virtual'" - self.SELECTS = OrderedDict([ - ('all', '''SELECT - i.category AS cat, - i.name as pn, - i.version as iv, - IFNULL(a.version, 'alien') AS av, - d.description AS descr - 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 %s LIKE %s %s - UNION - SELECT - a.category AS cat, - a.name as pn, - IFNULL(i.version, 'None') AS iv, - a.version as av, - d.description AS descr - 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 %s LIKE %s %s - ''' % (Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual, Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual)), - ('installed', '''SELECT - i.category AS cat, - i.name AS pn, - i.version AS iv, - a.version as av, - d.description AS descr - 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 %s LIKE %s %s - ''' % (Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual)), - ('alien', '''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 %s LIKE %s %s - AND av IS 'alien' - ''' % (Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual)), - ('available', '''SELECT - a.category AS cat, - a.name AS pn, - i.version as iv, - a.version AS av, - d.description AS descr - 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 %s LIKE %s %s - AND iv IS NULL - ''' % (Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual)), - ('upgradable', '''SELECT - i.category AS cat, - i.name AS pn, - i.version as iv, - a.version AS av, - d.description AS descr - 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 %s LIKE %s %s - AND iv <> av - ''' % (Sisyphus.applicationView, Sisyphus.searchTerm, noVirtual)), - ]) + filter = Sisyphus.dbFilter + cat = '%' + self.inputBox.text() + '%' if self.applicationFilter.currentText() == 'Package Category' else '' + pn = '%' + self.inputBox.text() + '%' if self.applicationFilter.currentText() == 'Package Name' else '' + desc = '%' + self.inputBox.text() + '%' if self.applicationFilter.currentText() == 'Package Description' else '' + + query = sisyphus.querydb.start(filter, cat, pn, desc) with sqlite3.connect(sisyphus.getfs.lcl_db) as db: cursor = db.cursor() - cursor.execute('%s' % (self.SELECTS[Sisyphus.databaseView])) + cursor.execute(query) rows = cursor.fetchall() Sisyphus.pkgCount = len(rows) Sisyphus.pkgSelect = 0 @@ -241,8 +159,7 @@ class Sisyphus(QtWidgets.QMainWindow): self.showPackageCount() def searchDatabase(self): - search = self.inputBox.text() - Sisyphus.searchTerm = "'%" + search + "%'" + Sisyphus.searchTerm = "'%" + self.inputBox.text() + "%'" self.loadDatabase() def updateSystem(self): diff --git a/src/frontend/gui/ui/mirrorcfg.ui b/src/frontend/gui/ui/mirrorcfg.ui index f282d41..95b096b 100644 --- a/src/frontend/gui/ui/mirrorcfg.ui +++ b/src/frontend/gui/ui/mirrorcfg.ui @@ -106,7 +106,7 @@ p, li { white-space: pre-wrap; } <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Sans'; font-size:10pt;">Ionel Busuioc &lt;bionel&gt;</span></p> <p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'Sans'; font-size:10pt;"><br /></p> <p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'Sans'; font-size:10pt;"><br /></p> -<p align="right" style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Sans';">Copyleft (ɔ) 2016 - 2021 Redcore Linux Project</span></p></body></html> +<p align="right" style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Sans';">Copyleft (ɔ) 2016 - 2024 Redcore Linux Project</span></p></body></html> -- cgit v1.2.3