Perform regex lookup in SQL

This improves performance of some string field lookups, for example
`beet list path::aa`

3 files changed, 13 insertions, 0 deletions

diff --git a/beets/dbcore/db.py b/beets/dbcore/db.py
index c181f7b33..55c78c88d 100755
--- a/beets/dbcore/db.py
+++ b/beets/dbcore/db.py
@@ -975,6 +975,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 +988,14 @@ 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)
+
     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/docs/changelog.rst b/docs/changelog.rst
index 39cd8fa2d..f1cca7570 100644
--- a/docs/changelog.rst
+++ b/docs/changelog.rst
@@ -13,6 +13,7 @@ New features:
 
 * --from-logfile now parses log files using a UTF-8 encoding in `beets/beets/ui/commands.py`.
   :bug:`4693` 
+* :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.