Merge pull request #4741 from snejus/use-regex-for-path-lookup

Perform regex and `bareasc` lookups using SQL

- Define a custom function which performs regex lookups natively in SQL. This improves
performance of lookups like beet list path::hello.
- Define a SQL function which runs unidecode for the bareasc lookups

4 files changed, 25 insertions, 0 deletions

diff --git a/beets/dbcore/db.py b/beets/dbcore/db.py
index c181f7b33..94396f81b 100755
--- a/beets/dbcore/db.py
+++ b/beets/dbcore/db.py
@@ -23,6 +23,8 @@ import threading
 import sqlite3
 import contextlib
 
+from unidecode import unidecode
+
 import beets
 from beets.util import functemplate
 from beets.util import py3_path
@@ -975,6 +977,7 @@ class Database:
         conn = sqlite3.connect(
             py3_path(self.path), timeout=self.timeout
         )
+        self.add_functions(conn)
 
         if self.supports_extensions:
             conn.enable_load_extension(True)
@@ -987,6 +990,15 @@ class Database:
         conn.row_factory = sqlite3.Row
         return conn
 
+    def add_functions(self, conn):
+        def regexp(value, pattern):
+            if isinstance(value, bytes):
+                value = value.decode()
+            return re.search(pattern, str(value)) is not None
+
+        conn.create_function("regexp", 2, regexp)
+        conn.create_function("unidecode", 1, unidecode)
+
     def _close(self):
         """Close the all connections to the underlying SQLite database
         from all threads. This does not render the database object
diff --git a/beets/dbcore/query.py b/beets/dbcore/query.py
index a0d79da70..016fe2c1a 100644
--- a/beets/dbcore/query.py
+++ b/beets/dbcore/query.py
@@ -231,6 +231,9 @@ class RegexpQuery(StringFieldQuery):
                                                  "a regular expression",
                                                  format(exc))
 
+    def col_clause(self):
+        return f" regexp({self.field}, ?)", [self.pattern.pattern]
+
     @staticmethod
     def _normalize(s):
         """Normalize a Unicode string's representation (used on both
diff --git a/beetsplug/bareasc.py b/beetsplug/bareasc.py
index 218369364..3343786f9 100644
--- a/beetsplug/bareasc.py
+++ b/beetsplug/bareasc.py
@@ -42,6 +42,14 @@ class BareascQuery(StringFieldQuery):
         val = unidecode(val)
         return pattern in val
 
+    def col_clause(self):
+        """Compare ascii version of the pattern."""
+        clause = f"unidecode({self.field})"
+        if self.pattern.islower():
+            clause = f"lower({clause})"
+
+        return rf"{clause} LIKE ? ESCAPE '\'", [f"%{unidecode(self.pattern)}%"]
+
 
 class BareascPlugin(BeetsPlugin):
     """Plugin to provide bare-ASCII option for beets matching."""
diff --git a/docs/changelog.rst b/docs/changelog.rst
index 01f160f8e..397f4af2c 100644
--- a/docs/changelog.rst
+++ b/docs/changelog.rst
@@ -15,6 +15,8 @@ New features:
 * :ref:`list-cmd` `singleton:1` and `singleton:0` can now alternatively be used in queries, same as `comp`
 * --from-logfile now parses log files using a UTF-8 encoding in `beets/beets/ui/commands.py`.
   :bug:`4693` 
+* :doc:`/plugins/bareasc` lookups have been made faster
+* :ref:`list-cmd` lookups using the pattern operator `::` have been made faster
 * Added additional error handling for `spotify` plugin.
   :bug:`4686`
 * We now import the remixer field from Musicbrainz into the library.