Repository: brightway-lca/brightway2-data

Issue:
D2 Use the SQLite text search instead of a separate Whoosh search index
* [FTS5](https://www.sqlite.org/fts5.html)
* [Tutorial](https://www.sqlitetutorial.net/sqlite-full-text-search/)

Discussion:

Patch:
diff --git a/CHANGES.md b/CHANGES.md
index 4531f85..ec0f86c 100644
--- a/CHANGES.md
+++ b/CHANGES.md
@@ -1,10 +1,5 @@
 # `bw2data` Changelog
 
-## 4.0.DEV46 (2024-07-23)
-
-* [PR #176: Fix un-hashable error](https://github.com/brightway-lca/brightway2-data/pull/176).
-* Import multifunctional if available
-
 ## 4.0.DEV45 (2024-07-06)
 
 * Use peewee instead of raw SQL when processing technosphere and biosphere exchanges, and properly exclude non-process nodes (i.e. those not included in `labels.process_node_types`) from processed arrays.
diff --git a/bw2data/__init__.py b/bw2data/__init__.py
index d2698a9..acb2598 100644
--- a/bw2data/__init__.py
+++ b/bw2data/__init__.py
@@ -33,7 +33,7 @@ __all__ = [
     "weightings",
 ]
 
-__version__ = (4, 0, "dev46")
+__version__ = (4, 0, "dev45")
 
 from .configuration import config, labels
 from .project import projects
@@ -83,10 +83,3 @@ from .updates import Updates
 from .parameters import parameters
 
 Updates.check_status()
-
-
-try:
-    # Will register itself as a database backend provider
-    import multifunctional
-except ImportError:
-    pass
diff --git a/bw2data/backends/base.py b/bw2data/backends/base.py
index 743c9ec..11c718c 100644
--- a/bw2data/backends/base.py
+++ b/bw2data/backends/base.py
@@ -723,7 +723,7 @@ Here are the type values usually used for nodes:
             return
         databases[self.name]["searchable"] = True
         databases.flush()
-        IndexManager(self.filename).delete_database()
+        IndexManager(self.filename).create()
         IndexManager(self.filename).add_datasets(self)
 
     def make_unsearchable(self):
@@ -732,7 +732,7 @@ Here are the type values usually used for nodes:
         IndexManager(self.filename).delete_database()
 
     def delete(self, keep_params=False, warn=True, vacuum=True):
-        """Delete all data from SQLite database and Whoosh index"""
+        """Delete all data from SQLite database and search index"""
         if warn:
             MESSAGE = """
             Please use `del databases['{}']` instead.
@@ -965,7 +965,7 @@ Here are the type values usually used for nodes:
         * ``filter``: Dictionary of criteria that search results must meet, e.g. ``{'categories': 'air'}``. Keys must be one of the above fields.
         * ``mask``: Dictionary of criteria that exclude search results. Same format as ``filter``.
         * ``facet``: Field to facet results. Must be one of ``name``, ``product``, ``categories``, ``location``, or ``database``.
-        * ``proxy``: Return ``Activity`` proxies instead of raw Whoosh documents. Default is ``True``.
+        * ``proxy``: Return ``Activity`` proxies instead of dictionary index Models. Default is ``True``.
 
         Returns a list of ``Activity`` datasets."""
         with Searcher(self.filename) as s:
diff --git a/bw2data/method.py b/bw2data/method.py
index 39320c2..212de54 100644
--- a/bw2data/method.py
+++ b/bw2data/method.py
@@ -56,7 +56,7 @@ class Method(ImpactAssessmentDataStore):
         except UnknownObject:
             raise UnknownObject(
                 "Can't find flow `{}`, specified in CF row `{}` for method `{}`".format(
-                    {tuple(row[0]) if isinstance(row[0], list) else row[0]}, row, self.name
+                    {row[0]}, row, self.name
                 )
             )
         except KeyError:
diff --git a/bw2data/search/indices.py b/bw2data/search/indices.py
index 7fbff9c..fd7c56f 100644
--- a/bw2data/search/indices.py
+++ b/bw2data/search/indices.py
@@ -1,28 +1,95 @@
+import operator
 import os
-import shutil
 
-from whoosh import index
+from peewee import OP, Expression
+from playhouse.sqlite_ext import SqliteExtDatabase
 
+from .schema import BW2Schema
 from .. import projects
-from .schema import bw2_schema
+
+MODELS = (BW2Schema,)
+
+
+class ProxyExpression:
+    """
+    allows exposing a custom expression in the search api
+    without leaking the underlying peewee implementation
+    """
+
+    def __init__(self, attribute):
+        self._attribute = attribute
+
+    def _e(op, inv=False):
+        """
+        Lightweight factory which returns a method that builds an Expression
+        consisting of the left-hand and right-hand operands, using `op`.
+        """
+
+        def inner(self, rhs):
+            if inv:
+                return Expression(rhs, op, self._attribute)
+            return Expression(self._attribute, op, rhs)
+
+        return inner
+
+    __and__ = operator.and_
+    __or__ = _e(OP.OR)
+
+    __add__ = _e(OP.ADD)
+    __sub__ = _e(OP.SUB)
+    __mul__ = _e(OP.MUL)
+    __div__ = __truediv__ = _e(OP.DIV)
+    __xor__ = _e(OP.XOR)
+    __radd__ = _e(OP.ADD, inv=True)
+    __rsub__ = _e(OP.SUB, inv=True)
+    __rmul__ = _e(OP.MUL, inv=True)
+    __rdiv__ = __rtruediv__ = _e(OP.DIV, inv=True)
+    __rand__ = _e(OP.AND, inv=True)
+    __ror__ = _e(OP.OR, inv=True)
+    __rxor__ = _e(OP.XOR, inv=True)
+
+    def __eq__(self, rhs):
+        op = OP.IS if rhs is None else OP.EQ
+        return Expression(self._attribute, op, rhs)
+
+    def __ne__(self, rhs):
+        op = OP.IS_NOT if rhs is None else OP.NE
+        return Expression(self._attribute, op, rhs)
+
+    __lt__ = _e(OP.LT)
+    __le__ = _e(OP.LTE)
+    __gt__ = _e(OP.GT)
+    __ge__ = _e(OP.GTE)
+    __lshift__ = _e(OP.IN)
+    __rshift__ = _e(OP.IS)
+    __mod__ = _e(OP.LIKE)
+    __pow__ = _e(OP.ILIKE)
+
+    like = _e(OP.LIKE)
+    ilike = _e(OP.ILIKE)
+
+    bin_and = _e(OP.BIN_AND)
+    bin_or = _e(OP.BIN_OR)
+    in_ = _e(OP.IN)
+    not_in = _e(OP.NOT_IN)
+    regexp = _e(OP.REGEXP)
+    iregexp = _e(OP.IREGEXP)
 
 
 class IndexManager:
-    def __init__(self, database_path, dir_name="whoosh"):
-        self.path = os.path.join(projects.request_directory("whoosh"), database_path)
+    def __init__(self, database_path):
+        self.path = os.path.join(projects.request_directory("search"), database_path)
+        self.db = SqliteExtDatabase(self.path)
         if not os.path.exists(self.path):
-            os.mkdir(self.path)
+            self.create()
 
     def get(self):
-        try:
-            return index.open_dir(self.path)
-        except index.EmptyIndexError:
-            return self.create()
+        return self
 
     def create(self):
         self.delete_database()
-        os.mkdir(self.path)
-        return index.create_in(self.path, bw2_schema)
+        with self.db.bind_ctx(MODELS):
+            self.db.create_tables(MODELS)
 
     def _format_dataset(self, ds):
         def _fix_location(string):
@@ -51,19 +118,83 @@ class IndexManager:
         self.add_datasets([ds])
 
     def add_datasets(self, datasets):
-        writer = self.get().writer()
-        for ds in datasets:
-            writer.add_document(**self._format_dataset(ds))
-        writer.commit()
+        all_dataset = list(datasets)
+        with self.db.bind_ctx(MODELS):
+            for chunk_range in range(0, len(datasets), 100):
+                for model in MODELS:
+                    model.insert_many(
+                        [
+                            self._format_dataset(ds)
+                            for ds in all_dataset[chunk_range: chunk_range + 100]
+                        ]
+                    ).execute()
 
     def update_dataset(self, ds):
-        writer = self.get().writer()
-        writer.update_document(**self._format_dataset(ds))
-        writer.commit()
+        with self.db.bind_ctx(MODELS):
+            for model in MODELS:
+                model.delete().where(model.code == ds["code"], model.database == ds["database"]).execute()
+                model.insert(**self._format_dataset(ds)).execute()
 
     def delete_dataset(self, ds):
-        index = self.get()
-        index.delete_by_term("code", ds["code"])
+        with self.db.bind_ctx(MODELS):
+            for model in MODELS:
+                model.delete().where(model.code == ds["code"], model.database == ds["database"]).execute()
 
     def delete_database(self):
-        shutil.rmtree(self.path)
+        with self.db.bind_ctx(MODELS):
+            self.db.drop_tables(MODELS)
+
+    def close(self):
+        self.db.close()
+
+    def _process_condition(self, conditions, entity):
+        statements = []
+        string_keys = set([k for k, v in conditions.items() if type(v) is str])
+        if string_keys:
+            statements.append(
+                entity.match(" AND ".join("{}:'{}'".format(k, v) for k, v in conditions.items() if k in string_keys))
+            )
+        for k, v in [(k, v) for k, v in conditions.items() if k not in string_keys]:
+            attribute = getattr(entity, k)
+            if callable(v):
+                statements.append(v(ProxyExpression(attribute)))
+            else:
+                raise ValueError("unsupported argument type: {}".format(type(v)))
+        return statements
+
+    def search(self, string, limit=None, weights=None, mask=None, filter=None):
+        conditions = []
+        if mask:
+            mask_entity = BW2Schema.alias("mask")
+            conditions.append(
+                BW2Schema.rowid.not_in(
+                    mask_entity.select(mask_entity.rowid).where(
+                        *self._process_condition(mask, mask_entity)
+                    )
+                )
+            )
+        if filter:
+            filter_entity = BW2Schema.alias("filter")
+            conditions.append(
+                BW2Schema.rowid.in_(
+                    filter_entity.select(filter_entity.rowid).where(
+                        *self._process_condition(filter, filter_entity)
+                    )
+                )
+            )
+
+        with self.db.bind_ctx(MODELS):
+            if string == '*':
+                query = BW2Schema
+            else:
+                query = BW2Schema.search_bm25(string, weights=weights)
+            if conditions:
+                query = query.where(*conditions)
+            return list(
+                query.
+                select(BW2Schema.name, BW2Schema.comment, BW2Schema.product, BW2Schema.categories,
+                       BW2Schema.synonyms, BW2Schema.location, BW2Schema.database, BW2Schema.code).
+                limit(limit).
+                dicts().
+                execute()
+            )
diff --git a/bw2data/search/schema.py b/bw2data/search/schema.py
index 89b649d..f54d38d 100644
--- a/bw2data/search/schema.py
+++ b/bw2data/search/schema.py
@@ -1,17 +1,55 @@
-from whoosh.analysis import StandardAnalyzer
-from whoosh.fields import ID, TEXT, Schema
-
-bw2_schema = Schema(
-    name=TEXT(
-        stored=True,
-        sortable=True,
-        analyzer=StandardAnalyzer(stoplist=frozenset(), minsize=1),
-    ),
-    comment=TEXT(stored=True),
-    product=TEXT(stored=True, sortable=True),
-    categories=TEXT(stored=True),
-    synonyms=TEXT(stored=True),
-    location=TEXT(stored=True, sortable=True),
-    database=TEXT(stored=True),
-    code=ID(unique=True, stored=True),
-)
+from peewee import SQL
+from playhouse.sqlite_ext import FTSModel, SearchField, RowIDField
+
+
+class BwDataFTSModel(FTSModel):
+    """
+    Upstream FTS3/4 models does not exclude the primary field from
+    being passed to the `matchinfo` method.
+    """
+
+    @classmethod
+    def _search(cls, term, weights, with_score, score_alias, score_fn,
+                explicit_ordering):
+        if not weights:
+            rank = score_fn()
+        elif isinstance(weights, dict):
+            weight_args = []
+            for field in cls._meta.sorted_fields:
+                # Attempt to get the specified weight of the field by looking
+                # it up using it's field instance followed by name.
+                if isinstance(field, SearchField) and not field.unindexed:
+                    weight_args.append(
+                        weights.get(field, weights.get(field.name, 1.0))
+                    )
+            rank = score_fn(*weight_args)
+        else:
+            rank = score_fn(*weights)
+
+        selection = ()
+        order_by = rank
+        if with_score:
+            selection = (cls, rank.alias(score_alias))
+        if with_score and not explicit_ordering:
+            order_by = SQL(score_alias)
+
+        return (cls
+                .select(*selection)
+                .where(cls.match(term))
+                .order_by(order_by))
+
+
+class BW2Schema(BwDataFTSModel):
+    rowid = RowIDField()
+    name = SearchField()
+    comment = SearchField()
+    product = SearchField()
+    categories = SearchField()
+    synonyms = SearchField()
+    location = SearchField()
+    database = SearchField()
+    code = SearchField()
+
+    class Meta:
+        # Use the porter stemming algorithm to tokenize content.
+        options = {'tokenize': 'porter'}
diff --git a/bw2data/search/search.py b/bw2data/search/search.py
index b90dab5..256fe4d 100644
--- a/bw2data/search/search.py
+++ b/bw2data/search/search.py
@@ -1,5 +1,6 @@
-from whoosh.qparser import MultifieldParser
-from whoosh.query import And, Term
+from itertools import groupby
+
+import peewee
 
 from .indices import IndexManager
 
@@ -10,6 +11,15 @@ def keysplit(strng):
 
 
 class Searcher:
+    search_fields = {
+        "name",
+        "comment",
+        "product",
+        "categories",
+        "synonyms",
+        "location",
+    }
+
     def __init__(self, database):
         self._database = database
 
@@ -21,30 +31,21 @@ class Searcher:
         self.index.close()
 
     def search(
-        self,
-        string,
-        limit=25,
-        facet=None,
-        proxy=True,
-        boosts=None,
-        filter=None,
-        mask=None,
-        node_class=None,
+            self,
+            string,
+            limit=25,
+            facet=None,
+            proxy=True,
+            boosts=None,
+            filter=None,
+            mask=None,
+            node_class=None,
     ):
         from .. import get_activity
 
         lowercase = lambda x: x.lower() if hasattr(x, "lower") else x
         string = lowercase(string)
 
-        fields = [
-            "name",
-            "comment",
-            "product",
-            "categories",
-            "synonyms",
-            "location",
-        ]
-
         boosts = boosts or {
             "name": 5,
             "comment": 1,
@@ -54,46 +55,37 @@ class Searcher:
             "location": 3,
         }
 
-        qp = MultifieldParser(fields, self.index.schema, fieldboosts=boosts)
-
         kwargs = {"limit": limit}
         if filter is not None:
             assert isinstance(filter, dict), "`filter` must be a dictionary"
-            for k in filter:
-                assert k in fields, "`filter` field {} not in search schema".format(k)
-            if len(filter) == 1:
-                kwargs["filter"] = [Term(k, lowercase(v)) for k, v in filter.items()][0]
-            else:
-                kwargs["filter"] = And(
-                    [Term(k, lowercase(v)) for k, v in filter.items()]
-                )
+            assert set(filter.keys()).issubset(self.search_fields), "`filter` fields {} not in search schema".format(
+                set(filter.keys()).difference(self.search_fields))
+
+            kwargs["filter"] = filter
+
         if mask is not None:
             assert isinstance(mask, dict), "`mask` must be a dictionary"
-            for k in mask:
-                assert k in fields, "`mask` field {} not in search schema".format(k)
-            if len(mask) == 1:
-                kwargs["mask"] = [Term(k, lowercase(v)) for k, v in mask.items()][0]
-            else:
-                kwargs["mask"] = And([Term(k, lowercase(v)) for k, v in mask.items()])
-
-        with self.index.searcher() as searcher:
-            if facet is None:
-                results = searcher.search(qp.parse(string), **kwargs)
-                if "mask" in kwargs or "filter" in kwargs:
-                    print(
-                        "Excluding {} filtered results".format(results.filtered_count)
-                    )
-                results = [dict(obj.items()) for obj in results]
-            else:
-                kwargs.pop("limit")
-                results = {
-                    k: [searcher.stored_fields(i) for i in v]
-                    for k, v in searcher.search(
-                        qp.parse(string), groupedby=facet, **kwargs
-                    )
-                    .groups()
-                    .items()
-                }
+            assert set(mask.keys()).issubset(self.search_fields), "`mask` fields {} not in search schema".format(
+                set(mask.keys()).difference(self.search_fields))
+
+            kwargs["mask"] = mask
+        if facet:
+            kwargs.pop("limit")
+
+        with self:
+            try:
+                results = self.index.search(string, weights=boosts, **kwargs)
+            except peewee.OperationalError as e:
+                if "no such table" in str(e):
+                    results = None
+                else:
+                    raise
+
+        if facet:
+            results = {
+                k: list(v)
+                for k, v in groupby(results, lambda x: x.get(facet))
+            }
 
         if proxy and facet is not None:
             return {
diff --git a/bw2data/updates.py b/bw2data/updates.py
index 3367240..2107cd4 100644
--- a/bw2data/updates.py
+++ b/bw2data/updates.py
@@ -114,6 +114,11 @@ class Updates:
             "automatic": True,
             "explanation": "bw2data 4.0 release requires migrations filename changes",
         },
+        "4.0 database search directories": {
+            "method": "database_search_directories_40",
+            "automatic": True,
+            "explanation": "bw2data 4.0 release switched to a new database search implementation",
+        },
     }
 
     @classmethod
@@ -141,7 +146,7 @@ class Updates:
                 key
                 for key in cls.UPDATES
                 if not preferences["updates"].get(key)
-                and not cls.UPDATES[key]["automatic"]
+                   and not cls.UPDATES[key]["automatic"]
             ]
         )
         if updates and verbose:
@@ -195,6 +200,16 @@ class Updates:
                 print("Reindexing database {}".format(db))
                 Database(db).make_searchable()
 
+    @classmethod
+    def database_search_directories_40(cls):
+        shutil.rmtree(projects.request_directory("whoosh"))
+        shutil.rmtree(projects.request_directory("search"))
+        for db in databases:
+            if databases[db].get("searchable"):
+                databases[db]["searchable"] = False
+                print("Reindexing database {}".format(db))
+                Database(db).make_searchable()
+
     @classmethod
     def processed_data_format_change_23(cls):
         processed_dir = projects.dir / "processed"
diff --git a/pyproject.toml b/pyproject.toml
index aa244c8..a0cedc1 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -44,7 +44,6 @@ dependencies = [
     "stats_arrays",
     "tqdm",
     "voluptuous",
-    "whoosh",
     "wrapt",
 ]
 
@@ -89,6 +88,10 @@ norecursedirs = [
     ".tox"
 ]
 testpaths = ["tests/*.py"]
+log_cli = true
+log_cli_level = "INFO"
+log_cli_format = "%(asctime)s [%(levelname)8s] %(message)s (%(filename)s:%(lineno)s)"
+log_cli_date_format = "%Y-%m-%d %H:%M:%S"
 
 [tool.flake8]
 # Some sane defaults for the code style checker flake8


Test patch:
diff --git a/tests/conftest.py b/tests/conftest.py
index 9446b14..db094fa 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -1,3 +1,5 @@
 """Fixtures for bw2data"""
+import sqlite3
 
 # import pytest
+sqlite3.enable_callback_tracebacks(True)
diff --git a/tests/search.py b/tests/search.py
index 3fd80e8..6cb2cf4 100644
--- a/tests/search.py
+++ b/tests/search.py
@@ -218,6 +218,19 @@ def test_limit():
         assert len(s.search("lollipop", limit=25, proxy=False)) == 25
 
 
+@bw2test
+def test_star_search():
+    im = IndexManager("foo")
+    im.add_datasets(
+        [
+            {"database": "foo", "code": "bar", "name": "lollipop {}".format(x)}
+            for x in range(50)
+        ]
+    )
+    with Searcher("foo") as s:
+        assert len(s.search("*", limit=25, proxy=False)) == 25
+
+
 @bw2test
 def test_search_faceting():
     im = IndexManager("foo")
@@ -302,6 +315,8 @@ def test_case_sensitivity_filter():
     assert len(db.search("lollipop")) == 2
     assert len(db.search("lollipop", filter={"location": "fr"})) == 1
     assert len(db.search("lollipop", filter={"location": "FR"})) == 1
+    assert len(db.search("lollipop", filter={"location": lambda col: col.ilike('FR')})) == 1
+    assert len(db.search("lollipop", filter={"location": lambda col: col == 'FR'})) == 0
 
 
 @bw2test
@@ -325,6 +340,8 @@ def test_case_sensitivity_mask():
     db.write(ds)
     assert len(db.search("lollipop")) == 2
     assert len(db.search("lollipop", mask={"product": "ZEbra"})) == 1
+    assert len(db.search("lollipop", mask={"product": lambda col: col.ilike('%ZEBRA%')})) == 1
+    assert len(db.search("lollipop", mask={"product": lambda col: col == 'ZEBRA'})) == 2
 
 
 @bw2test