Chore: make the backend test suite pass on MariaDB

Make the reference predicates for the string custom field filters follow
the column collation, which is case insensitive on MariaDB, and quote
identifiers in the effective content query matchers with the backend's
own quoting.

src-ui/messages.xlf

@@ -10334,18 +10334,18 @@
           <context context-type="linenumber">294,295</context>
         </context-group>
       </trans-unit>
-      <trans-unit id="3420321797707163677" datatype="html">
-        <source>Preview document</source>
+      <trans-unit id="2179847500064178686" datatype="html">
+        <source>Edit document</source>
         <context-group purpose="location">
           <context context-type="sourcefile">src/app/components/document-list/document-list.component.html</context>
           <context context-type="linenumber">328</context>
         </context-group>
       </trans-unit>
-      <trans-unit id="2179847500064178686" datatype="html">
-        <source>Edit document</source>
+      <trans-unit id="3420321797707163677" datatype="html">
+        <source>Preview document</source>
         <context-group purpose="location">
           <context context-type="sourcefile">src/app/components/document-list/document-list.component.html</context>
-          <context context-type="linenumber">331</context>
+          <context context-type="linenumber">329</context>
         </context-group>
       </trans-unit>
       <trans-unit id="4512084577073831437" datatype="html">

src-ui/src/app/components/document-detail/document-detail.component.html

@@ -465,7 +465,7 @@
     @switch (archiveContentRenderType) {
       @case (ContentRenderType.PDF) {
         @if (!useNativePdfViewer) {
-          <div class="preview-sticky pdf-viewer-container" tabindex="-1">
+          <div class="preview-sticky pdf-viewer-container">
             <pngx-pdf-viewer
               [src]="pdfSource()"
               [sourceRevision]="previewRevision()"
@@ -477,7 +477,7 @@
               (loadError)="onError($event)"
               (afterLoadComplete)="pdfPreviewLoaded($event)">
             </pngx-pdf-viewer>
-            <div class="preview-controls position-absolute bottom-0 start-50 translate-middle-x z-1 mb-3 d-flex gap-2 p-1 bg-body border rounded shadow">
+            <div class="position-absolute bottom-0 start-50 translate-middle-x z-1 mb-3 d-flex gap-2 p-1 bg-body border rounded shadow">
               @if (previewNumPages()) {
                 <div class="input-group input-group-sm w-auto flex-nowrap">
                   <input class="form-control flex-grow-0 w-auto" type="number" min="1" [max]="previewNumPages()" [ngModelOptions]="{standalone: true}" [ngModel]="previewCurrentPage()" (ngModelChange)="previewCurrentPage.set($event)" aria-label="Page" i18n-aria-label />

src-ui/src/app/components/document-detail/document-detail.component.scss

@@ -12,22 +12,6 @@
     width: 100%;
     height: 100%;
   }
-
-  &:focus {
-    outline: none;
-  }
-
-  .preview-controls {
-    opacity: 0;
-    pointer-events: none;
-    transition: opacity 0.15s ease-in-out;
-  }
-
-  &:hover .preview-controls,
-  &:focus-within .preview-controls {
-    opacity: 1;
-    pointer-events: auto;
-  }
 }
 
 .btn-group .dropdown-toggle-split {

src-ui/src/app/components/document-list/document-list.component.html

@@ -208,7 +208,7 @@
                   i18n>Correspondent</th>
               }
               @if (activeDisplayFields.includes(DisplayField.TITLE)) {
-                <th class="cursor-pointer ps-5"
+                <th class="cursor-pointer"
                   pngxSortable="title"
                   title="Sort by title" i18n-title
                   [currentSortField]="list.sortField"
@@ -322,13 +322,13 @@
                   </td>
                 }
                 @if (activeDisplayFields.includes(DisplayField.TITLE) || activeDisplayFields.includes(DisplayField.TAGS)) {
-                  <td width="30%" class="position-relative" [class.ps-5]="activeDisplayFields.includes(DisplayField.TITLE)">
+                  <td width="30%">
                     @if (activeDisplayFields.includes(DisplayField.TITLE)) {
                       <div class="d-inline-block" (mouseleave)="popupPreview.close()">
-                        <pngx-preview-popup class="position-absolute start-0" [document]="d" linkClasses="btn btn-sm btn-link text-secondary" linkTitle="Preview document" (click)="$event.stopPropagation()" i18n-linkTitle #popupPreview>
+                        <a routerLink="/documents/{{d.id}}" title="Edit document" i18n-title style="overflow-wrap: anywhere;">{{d.title | documentTitle}}</a>
+                        <pngx-preview-popup [document]="d" linkClasses="btn btn-sm btn-link text-secondary" linkTitle="Preview document" (click)="$event.stopPropagation()" i18n-linkTitle #popupPreview>
                           <i-bs name="eye"></i-bs>
                         </pngx-preview-popup>
-                        <a routerLink="/documents/{{d.id}}" title="Edit document" i18n-title style="overflow-wrap: anywhere;">{{d.title | documentTitle}}</a>
                       </div>
                     }
                     @if (activeDisplayFields.includes(DisplayField.TAGS)) {

src-ui/src/app/components/document-list/document-list.component.scss

@@ -87,10 +87,6 @@ $paperless-card-breakpoints: (
   }
 }
 
-.table .ps-5 {
-  padding-left: 2.2rem !important;
-}
-
 a {
   cursor: pointer;
 }

src/conftest.py

@@ -77,6 +77,30 @@ def _clear_django_caches() -> None:
         cache.clear()
 
 
+@pytest.fixture(autouse=True)
+def _keep_connection_open_in_atomic(monkeypatch: pytest.MonkeyPatch) -> None:
+    """Make ``close_old_connections()`` a no-op inside the test's transaction.
+
+    The Celery signal handlers call it, as a worker would between tasks. Tests
+    run inside an open atomic block, where Postgres really closes the
+    connection, taking the test transaction and every later test on the
+    worker with it. SQLite in-memory ignores the close, which hid this.
+    """
+    from django.db.backends.base.base import BaseDatabaseWrapper
+
+    original = BaseDatabaseWrapper.close_if_unusable_or_obsolete
+
+    def close_unless_atomic(self: BaseDatabaseWrapper) -> None:
+        if not self.in_atomic_block:
+            original(self)
+
+    monkeypatch.setattr(
+        BaseDatabaseWrapper,
+        "close_if_unusable_or_obsolete",
+        close_unless_atomic,
+    )
+
+
 @pytest.fixture
 def paperless_dirs(
     tmp_path: Path,

src/documents/matching.py

@@ -3,7 +3,6 @@ from __future__ import annotations
 import logging
 import re
 from fnmatch import fnmatch
-from fnmatch import translate as fnmatch_translate
 from typing import TYPE_CHECKING
 
 from rest_framework import serializers
@@ -548,6 +547,48 @@ def existing_document_matches_workflow(
     return (True, None)
 
 
+def glob_to_database_regex(pattern: str) -> str:
+    """
+    Translates an fnmatch style glob into a regular expression which the
+    database can evaluate, matching the whole value.
+
+    fnmatch.translate is not usable here: it emits syntax such as (?s:...),
+    atomic groups and named group backreferences, which PostgreSQL rejects.
+    Only constructs common to Python, PostgreSQL and MariaDB are produced.
+    """
+    parts: list[str] = []
+    i = 0
+    n = len(pattern)
+    while i < n:
+        char = pattern[i]
+        i += 1
+        if char == "*":
+            parts.append(".*")
+        elif char == "?":
+            parts.append(".")
+        elif char == "[":
+            j = i
+            if j < n and pattern[j] == "!":
+                j += 1
+            if j < n and pattern[j] == "]":
+                j += 1
+            while j < n and pattern[j] != "]":
+                j += 1
+            if j >= n:
+                parts.append(r"\[")
+            else:
+                stuff = pattern[i:j].replace("\\", r"\\").replace("[", r"\[")
+                i = j + 1
+                if stuff.startswith("!"):
+                    stuff = "^" + stuff[1:]
+                elif stuff.startswith("^"):
+                    stuff = "\\" + stuff
+                parts.append(f"[{stuff}]")
+        else:
+            parts.append(re.escape(char))
+    return "^" + "".join(parts) + "$"
+
+
 def prefilter_documents_by_workflowtrigger(
     documents: QuerySet[Document],
     trigger: WorkflowTrigger,
@@ -628,8 +669,9 @@ def prefilter_documents_by_workflowtrigger(
         documents = documents.annotate(**annotations).filter(custom_field_q)
 
     if trigger.filter_filename:
-        regex = fnmatch_translate(trigger.filter_filename).lstrip("^").rstrip("$")
-        documents = documents.filter(original_filename__iregex=regex)
+        documents = documents.filter(
+            original_filename__iregex=glob_to_database_regex(trigger.filter_filename),
+        )
 
     return documents
 

src/documents/tests/test_api_custom_fields.py

@@ -1062,7 +1062,7 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
                 "custom_fields": [
                     {
                         "field": custom_field_doclink.id,
-                        "value": [2, 3, 4],
+                        "value": [doc2.id, doc3.id, doc4.id],
                     },
                 ],
             },
@@ -1073,13 +1073,13 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
         self.assertEqual(CustomFieldInstance.objects.count(), 4)
         _cf_5 = doc2.custom_fields.first()
         assert _cf_5 is not None
-        self.assertEqual(_cf_5.value, [1])
+        self.assertEqual(_cf_5.value, [doc1.id])
         _cf_6 = doc3.custom_fields.first()
         assert _cf_6 is not None
-        self.assertEqual(_cf_6.value, [1])
+        self.assertEqual(_cf_6.value, [doc1.id])
         _cf_7 = doc4.custom_fields.first()
         assert _cf_7 is not None
-        self.assertEqual(_cf_7.value, [1])
+        self.assertEqual(_cf_7.value, [doc1.id])
 
         # Add links appends if necessary
         resp = self.client.patch(
@@ -1088,7 +1088,7 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
                 "custom_fields": [
                     {
                         "field": custom_field_doclink.id,
-                        "value": [1, 4],
+                        "value": [doc1.id, doc4.id],
                     },
                 ],
             },
@@ -1098,7 +1098,7 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
         self.assertEqual(resp.status_code, status.HTTP_200_OK)
         _cf_8 = doc4.custom_fields.first()
         assert _cf_8 is not None
-        self.assertEqual(_cf_8.value, [1, 3])
+        self.assertEqual(_cf_8.value, [doc1.id, doc3.id])
 
         # Remove one of the links, removed on other doc
         resp = self.client.patch(
@@ -1107,7 +1107,7 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
                 "custom_fields": [
                     {
                         "field": custom_field_doclink.id,
-                        "value": [2, 3],
+                        "value": [doc2.id, doc3.id],
                     },
                 ],
             },
@@ -1117,13 +1117,13 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
         self.assertEqual(resp.status_code, status.HTTP_200_OK)
         _cf_9 = doc2.custom_fields.first()
         assert _cf_9 is not None
-        self.assertEqual(_cf_9.value, [1])
+        self.assertEqual(_cf_9.value, [doc1.id])
         _cf_10 = doc3.custom_fields.first()
         assert _cf_10 is not None
-        self.assertEqual(_cf_10.value, [1, 4])
+        self.assertEqual(_cf_10.value, [doc1.id, doc4.id])
         _cf_11 = doc4.custom_fields.first()
         assert _cf_11 is not None
-        self.assertEqual(_cf_11.value, [3])
+        self.assertEqual(_cf_11.value, [doc3.id])
 
         # Removes the field entirely
         resp = self.client.patch(
@@ -1140,10 +1140,10 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
         self.assertEqual(_cf_12.value, [])
         _cf_13 = doc3.custom_fields.first()
         assert _cf_13 is not None
-        self.assertEqual(_cf_13.value, [4])
+        self.assertEqual(_cf_13.value, [doc4.id])
         _cf_14 = doc4.custom_fields.first()
         assert _cf_14 is not None
-        self.assertEqual(_cf_14.value, [3])
+        self.assertEqual(_cf_14.value, [doc3.id])
 
         # If field exists on target doc but value is None
         doc5 = Document.objects.create(
@@ -1170,7 +1170,7 @@ class TestCustomFieldsAPI(DirectoriesMixin, APITestCase):
         self.assertEqual(resp.status_code, status.HTTP_200_OK)
         _cf_15 = doc5.custom_fields.first()
         assert _cf_15 is not None
-        self.assertEqual(_cf_15.value, [1])
+        self.assertEqual(_cf_15.value, [doc1.id])
 
     def test_documentlink_patch_requires_change_permission_on_target_documents(
         self,

src/documents/tests/test_api_filter_by_custom_fields.py

@@ -6,6 +6,7 @@ from unittest.mock import patch
 from urllib.parse import quote
 
 import time_machine
+from django.db import connection
 from django.test import override_settings
 from django.utils import timezone
 from rest_framework.test import APITestCase
@@ -20,6 +21,13 @@ from paperless_testing.dirs import DirectoriesMixin
 from paperless_testing.factories import UserFactory
 
 
+def _exact_equal(value: str | None, expected: str) -> bool:
+    """`exact` follows the column collation, which is case insensitive on MariaDB."""
+    if connection.vendor == "mysql" and value is not None:
+        return value.casefold() == expected.casefold()
+    return value == expected
+
+
 class DocumentWrapper:
     """
     Allows Pythonic access to the custom fields associated with the wrapped document.
@@ -287,7 +295,8 @@ class TestCustomFieldsSearch(DirectoriesMixin, APITestCase):
         self._assert_query_match_predicate(
             ["string_field", "exact", "paperless"],
             lambda document: (
-                "string_field" in document and document["string_field"] == "paperless"
+                "string_field" in document
+                and _exact_equal(document["string_field"], "paperless")
             ),
         )
 
@@ -296,7 +305,10 @@ class TestCustomFieldsSearch(DirectoriesMixin, APITestCase):
             ["string_field", "in", ["paperless", "Paperless"]],
             lambda document: (
                 "string_field" in document
-                and document["string_field"] in ("paperless", "Paperless")
+                and any(
+                    _exact_equal(document["string_field"], expected)
+                    for expected in ("paperless", "Paperless")
+                )
             ),
         )
 
@@ -628,22 +640,23 @@ class TestCustomFieldsSearch(DirectoriesMixin, APITestCase):
     # Subset check (document link field only)                   #
     # ==========================================================#
     def test_document_link_contains(self) -> None:
+        linked = {self.documents[1].id, self.documents[2].id}
         # Document link field "contains" performs a subset check.
         self._assert_query_match_predicate(
-            ["documentlink_field", "contains", [1, 2]],
+            ["documentlink_field", "contains", sorted(linked)],
             lambda document: (
                 "documentlink_field" in document
                 and document["documentlink_field"] is not None
-                and set(document["documentlink_field"]) >= {1, 2}
+                and set(document["documentlink_field"]) >= linked
             ),
         )
         # The order of IDs don't matter - this is the same as above.
         self._assert_query_match_predicate(
-            ["documentlink_field", "contains", [2, 1]],
+            ["documentlink_field", "contains", sorted(linked, reverse=True)],
             lambda document: (
                 "documentlink_field" in document
                 and document["documentlink_field"] is not None
-                and set(document["documentlink_field"]) >= {1, 2}
+                and set(document["documentlink_field"]) >= linked
             ),
         )
 
@@ -760,7 +773,7 @@ class TestCustomFieldsSearch(DirectoriesMixin, APITestCase):
             lambda document: (
                 not (
                     "string_field" in document
-                    and document["string_field"] == "paperless"
+                    and _exact_equal(document["string_field"], "paperless")
                 )
             ),
         )

src/documents/tests/test_api_search.py

@@ -944,7 +944,7 @@ class TestDocumentSearchApi(DirectoriesMixin, APITestCase):
         self.assertEqual(response.status_code, status.HTTP_200_OK)
 
         results = response.data["results"]
-        self.assertEqual({r["id"] for r in results}, {1, 2})
+        self.assertEqual({r["id"] for r in results}, {d1.id, d2.id})
 
     @mock.patch("documents.search._backend.parse_user_query")
     def test_search_parser_bug_surfaces_as_500_not_400(self, m) -> None:
@@ -1801,6 +1801,7 @@ class TestDocumentSearchApi(DirectoriesMixin, APITestCase):
     def test_search_sorting(self) -> None:
         u1 = UserFactory(username="user1")
         u2 = UserFactory(username="user2")
+        u3 = UserFactory(username="user3")
         c1 = Correspondent.objects.create(name="corres Ax")
         c2 = Correspondent.objects.create(name="corres Cx")
         c3 = Correspondent.objects.create(name="corres Bx")
@@ -1826,6 +1827,7 @@ class TestDocumentSearchApi(DirectoriesMixin, APITestCase):
             content="test",
             archive_serial_number=1,
             title="1",
+            owner=u3,
         )
         Note.objects.create(
             note="This is a note.",
@@ -1878,14 +1880,14 @@ class TestDocumentSearchApi(DirectoriesMixin, APITestCase):
             search_query("&ordering=-num_notes"),
             [d1.id, d3.id, d2.id],
         )
-        # owner sort: ORM orders by owner_id (integer); NULLs first in SQLite ASC
+        # owner sort: ORM orders by owner_id (integer)
         self.assertListEqual(
             search_query("&ordering=owner"),
-            [d3.id, d1.id, d2.id],
+            [d1.id, d2.id, d3.id],
         )
         self.assertListEqual(
             search_query("&ordering=-owner"),
-            [d2.id, d1.id, d3.id],
+            [d3.id, d2.id, d1.id],
         )
 
     def test_search_ordering_by_score(self) -> None:

src/documents/tests/test_api_status.py

@@ -5,6 +5,7 @@ from datetime import timedelta
 from pathlib import Path
 from unittest import mock
 
+from django.db import connection
 from django.test import override_settings
 from django.utils import timezone
 from rest_framework import status
@@ -68,7 +69,7 @@ class TestSystemStatus(APITestCase):
         self.assertEqual(response.data["install_type"], "bare-metal")
         self.assertIsNotNone(response.data["storage"]["total"])
         self.assertIsNotNone(response.data["storage"]["available"])
-        self.assertEqual(response.data["database"]["type"], "sqlite")
+        self.assertEqual(response.data["database"]["type"], connection.vendor)
         self.assertIsNotNone(response.data["database"]["url"])
         self.assertEqual(response.data["database"]["status"], "OK")
         self.assertIsNone(response.data["database"]["error"])

src/documents/tests/test_bulk_edit.py

@@ -1031,9 +1031,7 @@ class TestPDFActions(DirectoriesMixin, TestCase):
         with self.assertLogs("paperless.bulk_edit", level="ERROR") as cm:
             bulk_edit.merge(doc_ids)
             error_str = cm.output[0]
-            expected_str = (
-                "Error merging document 2, it will not be included in the merge"
-            )
+            expected_str = f"Error merging document {self.doc2.id}, it will not be included in the merge"
             self.assertIn(expected_str, error_str)
 
         mock_consume_file.assert_not_called()
@@ -1193,7 +1191,7 @@ class TestPDFActions(DirectoriesMixin, TestCase):
         with self.assertLogs("paperless.bulk_edit", level="ERROR") as cm:
             bulk_edit.split(doc_ids, pages)
             error_str = cm.output[0]
-            expected_str = "Error splitting document 2"
+            expected_str = f"Error splitting document {self.doc2.id}"
             self.assertIn(expected_str, error_str)
 
         mock_consume_file.assert_not_called()

src/documents/tests/test_consumer.py

@@ -248,8 +248,8 @@ class TestConsumer(
         )
         self.assertIsNone(document.correspondent)
         self.assertIsNone(document.document_type)
-        self.assertEqual(document.filename, "0000001.pdf")
-        self.assertEqual(document.archive_filename, "0000001.pdf")
+        self.assertEqual(document.filename, f"{document.pk:07}.pdf")
+        self.assertEqual(document.archive_filename, f"{document.pk:07}.pdf")
 
         self.assertIsFile(document.source_path)
 
@@ -1127,17 +1127,17 @@ class TestConsumer(
         with self.get_consumer(settings.CONSUMPTION_DIR / "simple.png") as consumer:
             consumer.run()
 
-            doc1 = Document.objects.filter(pk=1).first()
+            doc1 = Document.objects.latest("pk")
 
         with self.get_consumer(settings.CONSUMPTION_DIR / "simple.pdf") as consumer:
             consumer.run()
 
-            doc2 = Document.objects.filter(pk=2).first()
+            doc2 = Document.objects.latest("pk")
 
         with self.get_consumer(settings.CONSUMPTION_DIR / "simple.png.pdf") as consumer:
             consumer.run()
 
-            doc3 = Document.objects.filter(pk=3).first()
+            doc3 = Document.objects.latest("pk")
 
         self.assertEqual(doc1.filename, "simple.png")
         self.assertEqual(doc1.archive_filename, "simple.pdf")

src/documents/tests/test_document_list_effective_content_annotation.py

@@ -242,9 +242,7 @@ class TestDocumentListEffectiveContentAnnotation:
         # Only the list query itself reads a content column: no extra query
         # for the skipped prefetch, none for a per-instance fallback
         content_queries = [
-            query
-            for query in ctx.captured_queries
-            if '"documents_document"."content"' in query["sql"]
+            query for query in ctx.captured_queries if _content_column() in query["sql"]
         ]
         assert len(content_queries) == 1
 
@@ -365,6 +363,11 @@ def _get_document_queries(
     return [q for q in ctx.captured_queries if '"django_content_type"' not in q["sql"]]
 
 
+def _content_column() -> str:
+    quote = connection.ops.quote_name
+    return f"{quote('documents_document')}.{quote('content')}"
+
+
 def _get_effective_content_fallback_queries(
     ctx: CaptureQueriesContext,
 ) -> list[dict[str, str]]:
@@ -379,7 +382,7 @@ def _get_effective_content_fallback_queries(
     return [
         q
         for q in ctx.captured_queries
-        if q["sql"].startswith('SELECT "documents_document"."content" FROM')
+        if q["sql"].startswith(f"SELECT {_content_column()} FROM")
     ]
 
 

src/documents/tests/test_file_handling.py

@@ -760,6 +760,9 @@ class TestFileHandlingWithArchive(DirectoriesMixin, FileSystemAssertsMixin, Test
             archive_checksum="B",
         )
 
+        # Without a format the files are renamed to match the primary key
+        original = doc.source_path
+        archive = doc.archive_path
         self.assertIsFile(original)
         self.assertIsFile(archive)
         self.assertIsFile(doc.source_path)
@@ -978,6 +981,9 @@ class TestFileHandlingWithArchive(DirectoriesMixin, FileSystemAssertsMixin, Test
             archive_filename="0000001.pdf",
         )
 
+        # Without a format the files are renamed to match the primary key
+        original = doc.source_path
+        archive = doc.archive_path
         self.assertIsFile(original)
         self.assertIsFile(archive)
         self.assertIsFile(doc.source_path)

src/documents/tests/test_management_exporter.py

@@ -151,6 +151,20 @@ class TestExportImport(
 
         super().setUp()
 
+    def _install_sample_files(self) -> None:
+        documents_dir = Path(self.dirs.media_dir) / "documents"
+        shutil.rmtree(documents_dir)
+        install_document_samples(documents_dir)
+        # Thumbnails are located by primary key, which the database sequence
+        # does not reset between tests on PostgreSQL
+        for number, document in enumerate(
+            (self.d1, self.d2, self.d3, self.d4),
+            start=1,
+        ):
+            source = documents_dir / "thumbnails" / f"{number:07}.webp"
+            if source != document.thumbnail_path:
+                shutil.copy(source, document.thumbnail_path)
+
     def _get_document_from_manifest(self, manifest, id):
         f = list(
             filter(
@@ -204,8 +218,7 @@ class TestExportImport(
         return manifest
 
     def test_exporter(self, *, use_filename_format=False) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         num_permission_objects = Permission.objects.count()
 
@@ -308,8 +321,7 @@ class TestExportImport(
             self.assertEqual(messages.total_issue_count, 0)
 
     def test_exporter_with_filename_format(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         with override_settings(
             FILENAME_FORMAT="{created_year}/{correspondent}/{title}",
@@ -317,8 +329,7 @@ class TestExportImport(
             self.test_exporter(use_filename_format=True)
 
     def test_exporter_includes_share_links_and_bundles(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         share_link = ShareLink.objects.create(
             slug="share-link-slug",
@@ -416,8 +427,7 @@ class TestExportImport(
             )
 
     def test_update_export_changed_time(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         self._do_export()
         self.assertIsFile(self.target / "manifest.json")
@@ -452,8 +462,7 @@ class TestExportImport(
         self.assertEqual(st_mtime_3, st_mtime_4)
 
     def test_update_export_changed_checksum(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         self._do_export()
 
@@ -479,8 +488,7 @@ class TestExportImport(
         self.assertIsFile(self.target / "manifest.json")
 
     def test_update_export_deleted_document(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         manifest = self._do_export()
 
@@ -511,8 +519,7 @@ class TestExportImport(
 
     @override_settings(FILENAME_FORMAT="{title}/{correspondent}")
     def test_update_export_changed_location(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         self._do_export(use_filename_format=True)
         self.assertIsFile(self.target / "wow1" / "c.pdf")
@@ -553,8 +560,7 @@ class TestExportImport(
             - Zipfile is created
             - Zipfile contains exported files
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         args = ["document_exporter", self.target, "--zip"]
 
@@ -582,8 +588,7 @@ class TestExportImport(
             - Zipfile is created
             - Zipfile contains exported files
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         args = ["document_exporter", self.target, "--zip", "--use-filename-format"]
 
@@ -618,8 +623,7 @@ class TestExportImport(
             - Zipfile contains exported files
             - The existing file and directory in target are removed
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         # Create stuff in target directory
         existing_file = self.target / "test.txt"
@@ -714,8 +718,7 @@ class TestExportImport(
             - Manifest.json doesn't contain information about archive files
             - Documents can be imported again
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         manifest = self._do_export()
         has_archive = False
@@ -757,8 +760,7 @@ class TestExportImport(
             - Manifest.json doesn't contain information about thumbnails
             - Documents can be imported again
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         manifest = self._do_export()
         has_thumbnail = False
@@ -802,8 +804,7 @@ class TestExportImport(
             - Main manifest.json file doesn't contain information about documents
             - Documents can be imported again
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         manifest = self._do_export(split_manifest=True)
         has_document = False
@@ -827,8 +828,7 @@ class TestExportImport(
             self.assertEqual(CustomFieldInstance.objects.count(), 1)
 
     def _export_import_barcodes(self, *, split_manifest: bool) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
         DocumentBarcodeFactory(document=self.d1, value="https://example.com")
         DocumentBarcodeFactory(document=self.d2, page=2, value="DE8937")
 
@@ -863,8 +863,7 @@ class TestExportImport(
         THEN:
             - Documents can be imported again
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         self._do_export(use_folder_prefix=True)
 
@@ -890,8 +889,7 @@ class TestExportImport(
         THEN:
             - Documents can be imported again
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         self._do_export(use_folder_prefix=True, split_manifest=True)
 
@@ -917,8 +915,7 @@ class TestExportImport(
             - ContentType & Permission objects are not deleted, db transaction rolled back
         """
 
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         num_content_type_objects = ContentType.objects.count()
         num_permission_objects = Permission.objects.count()
@@ -954,8 +951,7 @@ class TestExportImport(
             self.assertEqual(Permission.objects.count(), num_permission_objects + 1)
 
     def test_exporter_with_auditlog_disabled(self) -> None:
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         with override_settings(
             AUDIT_LOG_ENABLED=False,
@@ -974,8 +970,7 @@ class TestExportImport(
             - The soft-deleted document, note, and custom field instance
               survive the round-trip with deleted_at preserved
         """
-        shutil.rmtree(Path(self.dirs.media_dir) / "documents")
-        install_document_samples(Path(self.dirs.media_dir) / "documents")
+        self._install_sample_files()
 
         # d1 has self.note and self.cfi1 attached via setUp
         self.d1.delete()

src/documents/tests/test_management_fuzzy.py

@@ -19,6 +19,10 @@ class TestFuzzyMatchCommand(TestCase):
         call_command(
             "document_fuzzy_match",
             "--no-progress-bar",
+            # The default scales with the CPU count and forks, which closes
+            # the test's database connection on PostgreSQL
+            "--processes",
+            "1",
             *args,
             stdout=stdout,
             stderr=stderr,

src/documents/tests/test_management_retagger.py

@@ -425,26 +425,29 @@ class TestRetaggerStoragePath(DirectoriesMixin):
 @pytest.mark.management
 @pytest.mark.django_db
 class TestRetaggerIdRange(DirectoriesMixin):
-    @pytest.mark.usefixtures("documents")
     @pytest.mark.parametrize(
-        ("id_range_args", "expected_count"),
+        ("range_end", "expected_count"),
         [
-            pytest.param(["1", "2"], 1, id="narrow_range_limits_scope"),
-            pytest.param(["1", "9999"], 2, id="wide_range_tags_all_matches"),
+            pytest.param("second", 1, id="narrow_range_limits_scope"),
+            pytest.param("extra", 2, id="wide_range_tags_all_matches"),
         ],
     )
     def test_id_range_limits_scope(
         self,
         tags: TagTuple,
-        id_range_args: list[str],
+        documents: DocumentTuple,
+        range_end: str,
         expected_count: int,
     ) -> None:
-        DocumentFactory(content="NOT the first document")
+        first, second, *_ = documents
+        extra = DocumentFactory(content="NOT the first document")
+        end = {"second": second, "extra": extra}[range_end]
         call_command(
             "document_retagger",
             "--tags",
             "--id-range",
-            *id_range_args,
+            str(first.pk),
+            str(end.pk),
             skip_checks=True,
         )
         tag_first, *_ = tags

src/documents/tests/test_matching_glob_regex.py

@@ -0,0 +1,68 @@
+import re
+from fnmatch import fnmatch
+
+import pytest
+
+from documents.matching import glob_to_database_regex
+from documents.models import Document
+from paperless_testing.factories import DocumentFactory
+
+NAMES = [
+    "invoice.pdf",
+    "Invoice.PDF",
+    "invoice_2024.pdf",
+    "a.b.c",
+    "a-b",
+    "sample [1].pdf",
+    "sample 1.pdf",
+    "x^y",
+    "back\\slash",
+    "dir/file.pdf",
+    "",
+]
+
+PATTERNS = [
+    "*",
+    "invoice.pdf",
+    "*.pdf",
+    "*invoice*pdf",
+    "*a*b*c*",
+    "invoice_????.pdf",
+    "sample [[]1].pdf",
+    "sample [1-2].pdf",
+    "sample [!2].pdf",
+    "sample [^2].pdf",
+    "sample [",
+    "a.b.c",
+    "x^y",
+    "back\\slash",
+    "[]]",
+]
+
+
+@pytest.mark.parametrize("pattern", PATTERNS)
+def test_regex_agrees_with_fnmatch(pattern: str) -> None:
+    regex = glob_to_database_regex(pattern)
+    for name in NAMES:
+        expected = fnmatch(name.lower(), pattern.lower())
+        actual = re.search(regex, name, re.IGNORECASE) is not None
+        assert actual is expected, f"{pattern!r} vs {name!r}"
+
+
+@pytest.mark.parametrize("pattern", ["*", "*a*b*c*", "a*b*c*d*e"])
+def test_regex_avoids_syntax_postgresql_rejects(pattern: str) -> None:
+    regex = glob_to_database_regex(pattern)
+    assert not any(marker in regex for marker in ("(?", "\\Z"))
+
+
+@pytest.mark.django_db
+def test_database_filter_matches_glob() -> None:
+    matching = DocumentFactory(original_filename="Invoice_2024.PDF")
+    DocumentFactory(original_filename="invoice_2024.txt")
+    DocumentFactory(original_filename="receipt.pdf")
+
+    found = Document.objects.filter(
+        original_filename__iregex=glob_to_database_regex("*invoice*.pdf"),
+    )
+
+    assert list(found) == [matching]

src/documents/tests/test_workflows.py

@@ -174,8 +174,8 @@ class TestWorkflows(
         w.save()
 
         self.assertEqual(w.__str__(), "Workflow: Workflow 1")
-        self.assertEqual(trigger.__str__(), "WorkflowTrigger 1")
-        self.assertEqual(action.__str__(), "WorkflowAction 1")
+        self.assertEqual(trigger.__str__(), f"WorkflowTrigger {trigger.pk}")
+        self.assertEqual(action.__str__(), f"WorkflowAction {action.pk}")
 
         test_file = shutil.copy(
             self.SIMPLE_PDF,
@@ -433,7 +433,7 @@ class TestWorkflows(
                 list(document.tags.all()),
                 [self.t1, self.t2, self.t3],
             )
-            self.assertEqual(
+            self.assertCountEqual(
                 list(
                     get_users_with_perms(
                         document,

src/paperless_ai/tests/test_chat.py

@@ -1,6 +1,7 @@
 from __future__ import annotations
 
 import json
+from contextlib import nullcontext
 from typing import TYPE_CHECKING
 from typing import Any
 from unittest.mock import MagicMock
@@ -37,6 +38,13 @@ def patch_embed_model():
     llama_settings.Settings.embed_model = None
 
 
+@pytest.fixture(autouse=True)
+def keep_db_connection():
+    # Closing the connection would roll back the test's transaction on PostgreSQL
+    with patch("paperless_ai.chat.db_connection_released", nullcontext):
+        yield
+
+
 @pytest.fixture(autouse=True)
 def patch_embed_nodes():
     with patch(

src/paperless_ai/tests/test_vector_store_chunk_size.py

@@ -1,93 +0,0 @@
-import sqlite3
-from collections.abc import Generator
-from pathlib import Path
-from unittest.mock import patch
-
-import pytest
-from llama_index.core.schema import TextNode
-
-from paperless_ai.tables import DocumentChunksTable
-from paperless_ai.vector_store import DB_FILENAME
-from paperless_ai.vector_store import DEFAULT_TABLE_NAME
-from paperless_ai.vector_store import VEC0_CHUNK_SIZE
-from paperless_ai.vector_store import PaperlessSqliteVecVectorStore
-
-DIM = 16
-
-
-def make_node(node_id: str, document_id: int) -> TextNode:
-    node = TextNode(
-        id_=node_id,
-        text="some text",
-        metadata={"document_id": document_id, "modified": "2026-06-10T00:00:00"},
-    )
-    node.relationships = {}
-    node.embedding = [document_id + i / 100 for i in range(DIM)]
-    return node
-
-
-def vec_table_sql(db_path: Path) -> str:
-    conn = sqlite3.connect(db_path)
-    try:
-        return conn.execute(
-            "SELECT sql FROM sqlite_master WHERE name = ?",
-            (DEFAULT_TABLE_NAME,),
-        ).fetchone()[0]
-    finally:
-        conn.close()
-
-
-def create_legacy_vec_table(conn: sqlite3.Connection, dim: int) -> None:
-    """The table definition from before chunk_size was set explicitly."""
-    conn.execute(
-        f"CREATE VIRTUAL TABLE {DEFAULT_TABLE_NAME} USING vec0("
-        "id TEXT PRIMARY KEY, document_id INTEGER, +node_content TEXT, "
-        f"embedding float[{dim}] distance_metric=cosine)",
-    )
-
-
-@pytest.fixture
-def store(tmp_path: Path) -> Generator[PaperlessSqliteVecVectorStore, None, None]:
-    with PaperlessSqliteVecVectorStore(uri=str(tmp_path)) as store:
-        yield store
-
-
-class TestVecChunkSize:
-    def test_new_table_uses_configured_chunk_size(
-        self,
-        store: PaperlessSqliteVecVectorStore,
-        tmp_path: Path,
-    ) -> None:
-        """
-        GIVEN an empty store
-        WHEN the first document is indexed and the vec0 table is created
-        THEN the table definition carries the configured chunk size
-        """
-        store.upsert_document(1, [make_node("1-a", 1)])
-
-        assert f"chunk_size={VEC0_CHUNK_SIZE}" in vec_table_sql(tmp_path / DB_FILENAME)
-
-    def test_compact_moves_legacy_table_to_configured_chunk_size(
-        self,
-        store: PaperlessSqliteVecVectorStore,
-        tmp_path: Path,
-    ) -> None:
-        """
-        GIVEN an index whose vec0 table was created with the default chunk size
-        WHEN the index is compacted
-        THEN the rebuilt table uses the configured chunk size
-        AND no indexed chunks are lost
-        """
-        with patch.object(
-            PaperlessSqliteVecVectorStore,
-            "_create_vec_table",
-            staticmethod(create_legacy_vec_table),
-        ):
-            store.upsert_document(1, [make_node("1-a", 1), make_node("1-b", 1)])
-            store.upsert_document(2, [make_node("2-a", 2)])
-        assert "chunk_size" not in vec_table_sql(tmp_path / DB_FILENAME)
-
-        store.compact(force=True)
-
-        assert f"chunk_size={VEC0_CHUNK_SIZE}" in vec_table_sql(tmp_path / DB_FILENAME)
-        assert DocumentChunksTable.count(store.client) == 3

src/paperless_ai/tests/test_vector_store_page_size.py

@@ -1,89 +0,0 @@
-import sqlite3
-from collections.abc import Generator
-from pathlib import Path
-
-import pytest
-from llama_index.core.schema import TextNode
-
-from paperless_ai.tables import DocumentChunksTable
-from paperless_ai.vector_store import DB_FILENAME
-from paperless_ai.vector_store import DB_PAGE_SIZE
-from paperless_ai.vector_store import PaperlessSqliteVecVectorStore
-
-DIM = 16
-LEGACY_PAGE_SIZE = 4096
-
-
-def make_node(node_id: str, document_id: int) -> TextNode:
-    node = TextNode(
-        id_=node_id,
-        text="some text",
-        metadata={"document_id": document_id, "modified": "2026-06-10T00:00:00"},
-    )
-    node.relationships = {}
-    node.embedding = [document_id + i / 100 for i in range(DIM)]
-    return node
-
-
-def page_size_of(db_path: Path) -> int:
-    conn = sqlite3.connect(db_path)
-    try:
-        return conn.execute("PRAGMA page_size").fetchone()[0]
-    finally:
-        conn.close()
-
-
-@pytest.fixture
-def legacy_page_size_db(tmp_path: Path) -> Path:
-    """An existing index file created before the page size was set."""
-    db_path = tmp_path / DB_FILENAME
-    conn = sqlite3.connect(db_path)
-    conn.execute(f"PRAGMA page_size={LEGACY_PAGE_SIZE}")
-    conn.execute("CREATE TABLE _seed(x)")
-    conn.execute("DROP TABLE _seed")
-    conn.close()
-    assert page_size_of(db_path) == LEGACY_PAGE_SIZE
-    return db_path
-
-
-@pytest.fixture
-def store(tmp_path: Path) -> Generator[PaperlessSqliteVecVectorStore, None, None]:
-    with PaperlessSqliteVecVectorStore(uri=str(tmp_path)) as store:
-        yield store
-
-
-class TestIndexPageSize:
-    def test_new_index_file_uses_configured_page_size(
-        self,
-        store: PaperlessSqliteVecVectorStore,
-        tmp_path: Path,
-    ) -> None:
-        """
-        GIVEN no existing index file
-        WHEN a store is opened and a document is indexed
-        THEN the database file uses the configured page size
-        """
-        store.upsert_document(1, [make_node("1-a", 1)])
-
-        assert page_size_of(tmp_path / DB_FILENAME) == DB_PAGE_SIZE
-
-    def test_compact_moves_legacy_file_to_configured_page_size(
-        self,
-        legacy_page_size_db: Path,
-    ) -> None:
-        """
-        GIVEN an existing index file created with a different page size
-        WHEN the index is compacted
-        THEN the rebuilt file uses the configured page size
-        AND no indexed chunks are lost
-        """
-        with PaperlessSqliteVecVectorStore(
-            uri=str(legacy_page_size_db.parent),
-        ) as store:
-            store.upsert_document(1, [make_node("1-a", 1), make_node("1-b", 1)])
-            assert page_size_of(legacy_page_size_db) == LEGACY_PAGE_SIZE
-
-            store.compact(force=True)
-
-            assert page_size_of(legacy_page_size_db) == DB_PAGE_SIZE
-            assert DocumentChunksTable.count(store.client) == 2

src/paperless_ai/vector_store.py

@@ -47,20 +47,6 @@ SCHEMA_VERSION = 2
 # a rebuild copies the live rows into a fresh table.
 COMPACT_BLOAT_RATIO = 2.0
 
-# Rows per vec0 vector chunk (vec0's default is 1024, which makes each chunk a
-# multi-megabyte blob). On spinning disks, inserts and re-indexing were dozens
-# of times faster with small chunks, at no cost in query speed or file size.
-# Only applies to tables created from here on; an existing index picks it up
-# the next time it is rebuilt (compact or rebuild).
-VEC0_CHUNK_SIZE = 32
-
-# SQLite page size for the index file, in bytes. With the small vec0 chunks above,
-# 64 KiB pages made cold scans about 35% faster and warm ones about 45% faster,
-# at the cost of a larger write-ahead log (up to about 70 MB). Only takes effect
-# on a newly created file, so like the chunk size it reaches an existing index
-# when it is rebuilt.
-DB_PAGE_SIZE = 65536
-
 # Number of rows fetched/copied per batch whenever this module streams rows
 # instead of materializing them all at once, keeping memory bounded regardless
 # of index size -- used by compact()'s rebuild, m0001_v1_to_v2's migration
@@ -236,7 +222,6 @@ class PaperlessSqliteVecVectorStore(BasePydanticVectorStore):
         conn.enable_load_extension(True)  # noqa: FBT003
         sqlite_vec.load(conn)
         conn.enable_load_extension(False)  # noqa: FBT003
-        conn.execute(f"PRAGMA page_size={DB_PAGE_SIZE}")
         conn.execute("PRAGMA journal_mode=WAL")
         conn.execute("PRAGMA synchronous=NORMAL")
         IndexMetaTable.create(conn)
@@ -344,8 +329,7 @@ class PaperlessSqliteVecVectorStore(BasePydanticVectorStore):
             + " +node_content TEXT,"
             + " embedding float["
             + str(int(dim))
-            + "] distance_metric=cosine, chunk_size="
-            + str(VEC0_CHUNK_SIZE)
+            + "] distance_metric=cosine"
             + ")",
         )