add dbsearch for cli frontend

4 files changed, 304 insertions, 28 deletions

diff --git a/src/backend/__init__.py b/src/backend/__init__.py
index 37d8db6..c13a513 100644
--- a/src/backend/__init__.py
+++ b/src/backend/__init__.py
@@ -6,6 +6,7 @@ from .cache import *
 from .check import *
 from .csvfiles import *
 from .database import *
+from .dbsearch import *
 from .filesystem import *
 from .installbinary import *
 from .installebuild import *
diff --git a/src/backend/dbsearch.py b/src/backend/dbsearch.py
new file mode 100644
index 0000000..d3b66b7
--- /dev/null
+++ b/src/backend/dbsearch.py
@@ -0,0 +1,120 @@
+#!/usr/bin/python3
+
+import sisyphus
+import sqlite3
+
+def searchDB(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, 'None') 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}''',
+        'local': 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 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 NULL''',
+        'remote': 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''',
+        'upgrade': 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'''
+    }
+
+    with sqlite3.connect(sisyphus.filesystem.localDatabase) as db:
+        db.row_factory = sqlite3.Row
+        cursor = db.cursor()
+        cursor.execute(SELECTS[filter])
+        rows = cursor.fetchall()
+
+    return rows
+
+def tosql(string):
+    return '%%' if string == '' else string.replace('*', '%').replace('?', '_')
+
+def showSearch(filter, cat, pn, desc, single = False):
+    print(f"Searching for {filter} packages ...\n")
+    pkglist = searchDB(filter, tosql(cat), tosql(pn), tosql(desc))
+
+    if len(pkglist) == 0:
+        print("No binary package found!")
+    else:
+        for pkg in pkglist:
+            if not single:
+                print(f"*  {pkg['cat']}/{pkg['pn']}")
+                print(f"\tInstalled version: {pkg['iv']}")
+                print(f"\tLatest available version: {pkg['av']}")
+                print(f"\tDescription: {pkg['desc']}\n")
+            else:
+                cpn = f"{pkg['cat']}/{pkg['pn']}"
+                print(f"{cpn:45} i:{str(pkg['iv']):20} a:{str(pkg['av'])}")
+        print(f"\nFound {len(pkglist)} binary package(s)")
+
+    print("To search for source packages, use the '--ebuild' option.")
diff --git a/src/frontend/cli/README.md b/src/frontend/cli/README.md
index 02ac61d..54fc628 100644
--- a/src/frontend/cli/README.md
+++ b/src/frontend/cli/README.md
@@ -60,9 +60,9 @@ The remote can be selected by using the --remote option.
 
 * Examples:
 
-'branch master --remote=gitlab' will pull the branch 'master' from gitlab.com
+    branch master --remote=gitlab   # pull the branch 'master' from gitlab.com
 
-'branch next --remote=pagure' will pull the branch 'next' from pagure.io
+    branch next --remote=pagure     # pull the branch 'next' from pagure.io
 
 !!! WARNING !!!
 
@@ -70,11 +70,19 @@ Once you changed the branch, you must pair it with the correct binary repository
 
 Branch 'master' must be paired with the stable binary repository (odd numbers in 'sisyphus mirror list').
 
-* Examples : 'sisyphus mirror set 1' or 'sisyphus mirror set 5'
+* Examples:
+
+    sisyphus mirror set 1
+
+    sisyphus mirror set 5
 
 Branch 'next' must be paired with the testing binary repository (even numbers in 'sisyphus mirror list').
 
-* Examples : 'sisyphus mirror set 2' or 'sisyphus mirror set 8'
+* Examples:
+
+    sisyphus mirror set 2
+
+    sisyphus mirror set 8
 
 **Usage**:
 
@@ -95,11 +103,11 @@ Use the --ebuild option to install ebuild(source) packages.
 
 * Examples:
 
-'sisyphus install pidgin'
+    sisyphus install pidgin
 
 will install pidgin binary package (if available); if there is none, but the ebuild(source) package for pidgin is found, it will stop and suggest the --ebuild option.
 
-'sisyphus install pidgin --ebuild'
+    sisyphus install pidgin --ebuild
 
 will compile pidgin from source
 
@@ -185,14 +193,73 @@ $ sisyphus rescue [OPTIONS]
 
 Search for binary and/or ebuild (source) packages.
 
+By default will search for binary packages, using internal database.
+The search term can be provided also in the category/name format, e.g:
+
+    sisyphus search openbox
+
+        OR
+
+    sisyphus search x11-wm/openbox
+
+Using * and ? wildcards is supported. An empty string will match everything (similar to *).
+
+* Examples:
+
+to search for all packages belonging to a category, use '*' or leave the name empty:
+
+    sisyphus search x11-wm/
+
+    sisyphus search x11-wm/*
+
+In addition, search can be performed by package description, using the -d (--description) option:
+
+    sisyphus search x11/open -d 'window manager'
+
+(use single or double quotes when the description contains spaces)
+
+Use the -s (--state) filters to select only packages of interest. Possible values:
+
+    all (default) - search the entire database
+
+    installed - search in all installed packages
+
+    local - search for installed packages but not available
+    (this filter can match packages installed from e-builds or packages no longer maintained as binaries)
+
+    remote - search for available packages but not installed
+
+    upgrade - search for installed packages where installed version is different from available version
+
+!!! NOTE !!! bash will expand a single * character as current folder listing.
+To search for all '--state' packages escape it, or surround it with quotes, or use an empty string:
+
+    sisyphus search * -s installed          # this is not valid!
+
+    sisyphus search \* -s local             # OK
+
+    sisyphus search '*' -s remote           # OK
+
+    sisyphus search '' -s upgrade           # OK
+
+
+To search for all (including source) packages, use the --ebuild option.
+This is slower since will perform an emerge --search actually.
+With this option, more than one package can be provided as search term.
+'-d', '-s' and '-q' (quiet) options are ignored in this mode.
+
 **Usage**:
 
 ```console
-$ sisyphus search [OPTIONS] PKGNAME...
+$ sisyphus search [OPTIONS] PACKAGE...
 ```
 
 **Options**:
 
+* `-d, --description TEXT`: Match description.
+* `-s, --state [all|installed|local|remote|upgrade]`: [default: all]
+* `-q`: Short (one line) output.
+* `-e, --ebuild`: Search in ebuilds (slower).
 * `--help`: Show this message and exit.
 
 ## `sisyphus spmsync`
@@ -234,11 +301,11 @@ This will not allways be possible, as the reverse dependency chain may be way to
 
 * Examples:
 
-'sisyphus uninstall firefox'
+    sisyphus uninstall firefox
 
 will succeed, nothing depends on it
 
-'sisyphus uninstall pulseaudio'
+    sisyphus uninstall pulseaudio
 
 will fail, many packages depend on it
 
@@ -249,11 +316,11 @@ Upgrading the system may pull the packages back in, to fix the reverse dependenc
 
 * Examples :
 
-'sisyphus uninstall pulseaudio --force'
+    sisyphus uninstall pulseaudio --force
 
 will succeed, but you may no longer have audio
 
-'sisyphus uninstall openrc --force'
+    sisyphus uninstall openrc --force
 
 will succeed, but the system will be broken
 
@@ -291,11 +358,11 @@ Use the --ebuild option to upgrade **EVERYTHING**, binary and/or ebuild(source)
 
 * Examples:
 
-'sisyphus upgrade'
+    sisyphus upgrade
 
 will upgrade the system using binary packages; if any ebuild(source) package upgrade is detected, it will stop and suggest the --ebuild option
 
-'sisyphus upgrade --ebuild'
+    sisyphus upgrade --ebuild
 
 will upgrade the system using both binary and/or ebuild(source) packages
 
diff --git a/src/frontend/cli/sisyphus-cli.py b/src/frontend/cli/sisyphus-cli.py
index 0be7b45..e5eb999 100755
--- a/src/frontend/cli/sisyphus-cli.py
+++ b/src/frontend/cli/sisyphus-cli.py
@@ -20,10 +20,90 @@ def app_callback(ctx: typer.Context):
     """
     ctx.info_name = 'sisyphus'
 
+class State(str, Enum):
+    all = 'all'
+    installed = 'installed'
+    local = 'local'
+    remote = 'remote'
+    upgrade = 'upgrade'
+
+def state_completion():
+    return ["all", "installed", "local", "remote", "upgrade"]
+
 @app.command("search")
-def search(pkgname: List[str]):
-    """Search for binary and/or ebuild (source) packages."""
-    sisyphus.search.start(pkgname)
+def search(package: List[str] = typer.Argument(...),
+           desc: str = typer.Option('', '--description', '-d', help = 'Match description.'),
+           state: State = typer.Option(State.all, '--state', '-s', show_default=True, autocompletion=state_completion),
+           quiet: bool = typer.Option(False, '-q', help='Short (one line) output.'),
+           ebuild: bool = typer.Option(False, "--ebuild", "-e", help = 'Search in ebuilds (slower).')):
+    """Search for binary and/or ebuild (source) packages.
+
+    By default will search for binary packages, using internal database.
+    The search term can be provided also in the category/name format, e.g:
+
+        sisyphus search openbox
+
+            OR
+
+        sisyphus search x11-wm/openbox
+
+    Using * and ? wildcards is supported. An empty string will match everything (similar to *).
+
+    * Examples:
+
+    to search for all packages belonging to a category, use '*' or leave the name empty:
+
+        sisyphus search x11-wm/
+
+        sisyphus search x11-wm/*
+
+    In addition, search can be performed by package description, using the -d (--description) option:
+
+        sisyphus search x11/open -d 'window manager'
+
+    (use single or double quotes when the description contains spaces)
+
+    Use the -s (--state) filters to select only packages of interest. Possible values:
+
+        all (default) - search the entire database
+
+        installed - search in all installed packages
+
+        local - search for installed packages but not available
+        (this filter can match packages installed from e-builds or packages no longer maintained as binaries)
+
+        remote - search for available packages but not installed
+
+        upgrade - search for installed packages where installed version is different from available version
+
+    !!! NOTE !!! bash will expand a single * character as current folder listing.
+    To search for all '--state' packages escape it, or surround it with quotes, or use an empty string:
+
+        sisyphus search * -s installed          # this is not valid!
+
+        sisyphus search \* -s local             # OK
+
+        sisyphus search '*' -s remote           # OK
+
+        sisyphus search '' -s upgrade           # OK
+
+
+    To search for all (including source) packages, use the --ebuild option.
+    This is slower since will perform an emerge --search actually.
+    With this option, more than one package can be provided as search term.
+    '-d', '-s' and '-q' (quiet) options are ignored in this mode.
+    """
+    if not ebuild:
+        if '/' in package[0]:
+            cat, pn = package[0].split('/')
+        else:
+            cat, pn = '', package[0]
+        sisyphus.dbsearch.showSearch(state.value, cat, pn, desc, quiet)
+    else:
+        if not package:
+            raise typer.Exit('No search term provided, try: sisyphus search --help')
+        else:
+            sisyphus.search.start(package)
 
 @app.command("install")
 def install(pkgname: List[str], ebuild: bool = typer.Option(False, "--ebuild", "-e")):
@@ -33,11 +113,11 @@ def install(pkgname: List[str], ebuild: bool = typer.Option(False, "--ebuild", "
 
     * Examples:
 
-    'sisyphus install pidgin'
+        sisyphus install pidgin
 
     will install pidgin binary package (if available); if there is none, but the ebuild(source) package for pidgin is found, it will stop and suggest the --ebuild option.
 
-    'sisyphus install pidgin --ebuild'
+        sisyphus install pidgin --ebuild
 
     will compile pidgin from source
 
@@ -58,11 +138,11 @@ def uninstall(pkgname: List[str], force: bool = typer.Option(False, "--force", "
 
     * Examples:
 
-    'sisyphus uninstall firefox'
+        sisyphus uninstall firefox
 
     will succeed, nothing depends on it
 
-    'sisyphus uninstall pulseaudio'
+        sisyphus uninstall pulseaudio
 
     will fail, many packages depend on it
 
@@ -73,11 +153,11 @@ def uninstall(pkgname: List[str], force: bool = typer.Option(False, "--force", "
 
     * Examples :
 
-    'sisyphus uninstall pulseaudio --force'
+        sisyphus uninstall pulseaudio --force
 
     will succeed, but you may no longer have audio
 
-    'sisyphus uninstall openrc --force'
+        sisyphus uninstall openrc --force
 
     will succeed, but the system will be broken
     """
@@ -109,11 +189,11 @@ def upgrade(ebuild: bool = typer.Option(False, "--ebuild", "-e")):
 
     * Examples:
 
-    'sisyphus upgrade'
+        sisyphus upgrade
 
     will upgrade the system using binary packages; if any ebuild(source) package upgrade is detected, it will stop and suggest the --ebuild option
 
-    'sisyphus upgrade --ebuild'
+        sisyphus upgrade --ebuild
 
     will upgrade the system using both binary and/or ebuild(source) packages
 
@@ -161,9 +241,9 @@ def branch(branch: Branch = typer.Argument(...), remote: Remote = typer.Option(R
 
     * Examples:
 
-    'branch master --remote=gitlab' will pull the branch 'master' from gitlab.com
+        branch master --remote=gitlab   # pull the branch 'master' from gitlab.com
 
-    'branch next --remote=pagure' will pull the branch 'next' from pagure.io
+        branch next --remote=pagure     # pull the branch 'next' from pagure.io
 
     !!! WARNING !!!
 
@@ -171,11 +251,19 @@ def branch(branch: Branch = typer.Argument(...), remote: Remote = typer.Option(R
 
     Branch 'master' must be paired with the stable binary repository (odd numbers in 'sisyphus mirror list').
 
-    * Examples : 'sisyphus mirror set 1' or 'sisyphus mirror set 5'
+    * Examples:
+
+        sisyphus mirror set 1
+
+        sisyphus mirror set 5
 
     Branch 'next' must be paired with the testing binary repository (even numbers in 'sisyphus mirror list').
 
-    * Examples : 'sisyphus mirror set 2' or 'sisyphus mirror set 8'
+    * Examples:
+
+        sisyphus mirror set 2
+
+        sisyphus mirror set 8
     """
     sisyphus.branchsetup.start(branch.value, remote.value)