diff options
author | Šarūnas Nejus <snejus@protonmail.com> | 2023-04-04 10:25:27 +0100 |
---|---|---|
committer | Šarūnas Nejus <snejus@protonmail.com> | 2023-04-09 18:43:32 +0100 |
commit | 0f1b5d6f7c63402761cdf0bafc5287887888df3a (patch) | |
tree | 05dcba9a12b4a3b91dda0399502854de9e3199d2 | |
parent | 6955c53349a5bfa126f957e96c05917c9beac732 (diff) |
Perform regex lookup in SQL
This improves performance of some string field lookups, for example
`beet list path::aa`
-rwxr-xr-x | beets/dbcore/db.py | 9 | ||||
-rw-r--r-- | beets/dbcore/query.py | 3 | ||||
-rw-r--r-- | docs/changelog.rst | 1 |
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. |