Repository: simonw/datasette

Issue:
Automatically hide type=shadow tables in SQLite 3.37 or higher
> For filtering virtual table's "shadow tables" (ex the FTS5 _content and most the spatialite tables),  you can use `pragma_table_list` (first appeared in SQLite 3.37 (2021-11-27), which has a `type` column that calls out `type="shadow"` tables https://www.sqlite.org/pragma.html#pragma_table_list

_Originally posted by @asg017 in https://github.com/simonw/datasette/issues/2104#issuecomment-1641082395_
            

Discussion:

Patch:
diff --git a/datasette/app.py b/datasette/app.py
index 1f9e9d30..fa5e90e3 100644
--- a/datasette/app.py
+++ b/datasette/app.py
@@ -1,3 +1,4 @@
+from asgi_csrf import Errors
 import asyncio
 from typing import Any, Dict, Iterable, List, Optional, Sequence, Tuple, Union
 import asgi_csrf
@@ -1476,6 +1477,8 @@ class Datasette:
             routes.append((regex, view))
 
         add_route(IndexView.as_view(self), r"/(\.(?P<format>jsono?))?$")
+        add_route(IndexView.as_view(self), r"/-/(\.(?P<format>jsono?))?$")
+        add_route(permanent_redirect("/-/"), r"/-$")
         # TODO: /favicon.ico and /-/static/ deserve far-future cache expires
         add_route(favicon, "/favicon.ico")
 
@@ -1655,6 +1658,17 @@ class Datasette:
                 if not database.is_mutable:
                     await database.table_counts(limit=60 * 60 * 1000)
 
+        async def custom_csrf_error(scope, send, message_id):
+            await asgi_send(
+                send,
+                content=await self.render_template(
+                    "csrf_error.html",
+                    {"message_id": message_id, "message_name": Errors(message_id).name},
+                ),
+                status=403,
+                content_type="text/html; charset=utf-8",
+            )
+
         asgi = asgi_csrf.asgi_csrf(
             DatasetteRouter(self, routes),
             signing_secret=self._secret,
@@ -1662,6 +1676,7 @@ class Datasette:
             skip_if_scope=lambda scope: any(
                 pm.hook.skip_csrf(datasette=self, scope=scope)
             ),
+            send_csrf_failed=custom_csrf_error,
         )
         if self.setting("trace_debug"):
             asgi = AsgiTracer(asgi)
diff --git a/datasette/database.py b/datasette/database.py
index 71c134d1..8d51befd 100644
--- a/datasette/database.py
+++ b/datasette/database.py
@@ -20,6 +20,7 @@ from .utils import (
     table_columns,
     table_column_details,
 )
+from .utils.sqlite import sqlite_version
 from .inspect import inspect_hash
 
 connections = threading.local()
@@ -459,22 +460,95 @@ class Database:
         )
 
     async def hidden_table_names(self):
-        # Mark tables 'hidden' if they relate to FTS virtual tables
-        hidden_tables = [
-            r[0]
-            for r in (
-                await self.execute(
+        hidden_tables = []
+        # Add any tables marked as hidden in config
+        db_config = self.ds.config.get("databases", {}).get(self.name, {})
+        if "tables" in db_config:
+            hidden_tables += [
+                t for t in db_config["tables"] if db_config["tables"][t].get("hidden")
+            ]
+
+        if sqlite_version()[1] >= 37:
+            hidden_tables += [
+                x[0]
+                for x in await self.execute(
+                    """
+                      with shadow_tables as (
+                        select name
+                        from pragma_table_list
+                        where [type] = 'shadow'
+                        order by name
+                      ),
+                      core_tables as (
+                        select name
+                        from sqlite_master
+                        WHERE  name in ('sqlite_stat1', 'sqlite_stat2', 'sqlite_stat3', 'sqlite_stat4')
+                          OR substr(name, 1, 1) == '_'
+                      ),
+                      combined as (
+                        select name from shadow_tables
+                        union all
+                        select name from core_tables
+                      )
+                      select name from combined order by 1
+                    """
+                )
+            ]
+        else:
+            hidden_tables += [
+                x[0]
+                for x in await self.execute(
+                    """
+                      WITH base AS (
+                        SELECT name
+                        FROM sqlite_master
+                        WHERE  name IN ('sqlite_stat1', 'sqlite_stat2', 'sqlite_stat3', 'sqlite_stat4')
+                          OR substr(name, 1, 1) == '_'
+                      ),
+                      fts_suffixes AS (
+                        SELECT column1 AS suffix
+                        FROM (VALUES ('_data'), ('_idx'), ('_docsize'), ('_content'), ('_config'))
+                      ),
+                      fts5_names AS (
+                        SELECT name
+                        FROM sqlite_master
+                        WHERE sql LIKE '%VIRTUAL TABLE%USING FTS%'
+                      ),
+                      fts5_shadow_tables AS (
+                        SELECT
+                          printf('%s%s', fts5_names.name, fts_suffixes.suffix) AS name
+                        FROM fts5_names
+                        JOIN fts_suffixes
+                      ),
+                      fts3_suffixes AS (
+                        SELECT column1 AS suffix
+                        FROM (VALUES ('_content'), ('_segdir'), ('_segments'), ('_stat'), ('_docsize'))
+                      ),
+                      fts3_names AS (
+                        SELECT name
+                        FROM sqlite_master
+                        WHERE sql LIKE '%VIRTUAL TABLE%USING FTS3%'
+                          OR sql LIKE '%VIRTUAL TABLE%USING FTS4%'
+                      ),
+                      fts3_shadow_tables AS (
+                        SELECT
+                          printf('%s%s', fts3_names.name, fts3_suffixes.suffix) AS name
+                        FROM fts3_names
+                        JOIN fts3_suffixes
+                      ),
+                      final AS (
+                        SELECT name FROM base
+                        UNION ALL
+                        SELECT name FROM fts5_shadow_tables
+                        UNION ALL
+                        SELECT name FROM fts3_shadow_tables
+                      )
+                      SELECT name FROM final ORDER BY 1
+
                     """
-                select name from sqlite_master
-                where rootpage = 0
-                and (
-                    sql like '%VIRTUAL TABLE%USING FTS%'
-                ) or name in ('sqlite_stat1', 'sqlite_stat2', 'sqlite_stat3', 'sqlite_stat4')
-                or name like '\\_%' escape '\\'
-            """
                 )
-            ).rows
-        ]
+            ]
+
         has_spatialite = await self.execute_fn(detect_spatialite)
         if has_spatialite:
             # Also hide Spatialite internal tables
@@ -503,19 +577,6 @@ class Database:
                     )
                 ).rows
             ]
-        # Add any tables marked as hidden in config
-        db_config = self.ds.config.get("databases", {}).get(self.name, {})
-        if "tables" in db_config:
-            hidden_tables += [
-                t for t in db_config["tables"] if db_config["tables"][t].get("hidden")
-            ]
-        # Also mark as hidden any tables which start with the name of a hidden table
-        # e.g. "searchable_fts" implies "searchable_fts_content" should be hidden
-        for table_name in await self.table_names():
-            for hidden_table in hidden_tables[:]:
-                if table_name.startswith(hidden_table):
-                    hidden_tables.append(table_name)
-                    continue
 
         return hidden_tables
 
diff --git a/datasette/templates/csrf_error.html b/datasette/templates/csrf_error.html
new file mode 100644
index 00000000..7cd4b42b
--- /dev/null
+++ b/datasette/templates/csrf_error.html
@@ -0,0 +1,13 @@
+{% extends "base.html" %}
+{% block title %}CSRF check failed){% endblock %}
+{% block content %}
+<h1>Form origin check failed</h1>
+
+<p>Your request's origin could not be validated. Please return to the form and submit it again.</p>
+
+<details><summary>Technical details</summary>
+  <p>Developers: consult Datasette's <a href="https://docs.datasette.io/en/latest/internals.html#csrf-protection">CSRF protection documentation</a>.</p>
+  <p>Error code is {{ message_name }}.</p>
+</details>
+
+{% endblock %}
diff --git a/datasette/templates/index.html b/datasette/templates/index.html
index 6e95126d..a3595a39 100644
--- a/datasette/templates/index.html
+++ b/datasette/templates/index.html
@@ -2,6 +2,10 @@
 
 {% block title %}{{ metadata.title or "Datasette" }}: {% for database in databases %}{{ database.name }}{% if not loop.last %}, {% endif %}{% endfor %}{% endblock %}
 
+{% block extra_head %}
+{% if noindex %}<meta name="robots" content="noindex">{% endif %}
+{% endblock %}
+
 {% block body_class %}index{% endblock %}
 
 {% block content %}
diff --git a/datasette/views/index.py b/datasette/views/index.py
index a3178f53..63cc067d 100644
--- a/datasette/views/index.py
+++ b/datasette/views/index.py
@@ -152,8 +152,9 @@ class IndexView(BaseView):
                 extra_links = await await_me_maybe(hook)
                 if extra_links:
                     homepage_actions.extend(extra_links)
+            alternative_homepage = request.path == "/-/"
             return await self.render(
-                ["index.html"],
+                ["default:index.html" if alternative_homepage else "index.html"],
                 request=request,
                 context={
                     "databases": databases,
@@ -166,5 +167,6 @@ class IndexView(BaseView):
                         "top_homepage", self.ds, request
                     ),
                     "homepage_actions": homepage_actions,
+                    "noindex": request.path == "/-/",
                 },
             )
diff --git a/datasette/views/table.py b/datasette/views/table.py
index fa2c80de..ba0dd4f3 100644
--- a/datasette/views/table.py
+++ b/datasette/views/table.py
@@ -364,7 +364,7 @@ class TableInsertView(BaseView):
         def _errors(errors):
             return None, errors, {}
 
-        if request.headers.get("content-type") != "application/json":
+        if not request.headers.get("content-type").startswith("application/json"):
             # TODO: handle form-encoded data
             return _errors(["Invalid content-type, must be application/json"])
         body = await request.post_body()
diff --git a/docs/pages.rst b/docs/pages.rst
index 239c9f80..78d5520f 100644
--- a/docs/pages.rst
+++ b/docs/pages.rst
@@ -23,6 +23,8 @@ Add ``/.json`` to the end of the URL for the JSON version of the underlying data
 * `global-power-plants.datasettes.com/.json <https://global-power-plants.datasettes.com/.json>`_
 * `register-of-members-interests.datasettes.com/.json <https://register-of-members-interests.datasettes.com/.json>`_
 
+The index page can also be accessed at ``/-/``, useful for if the default index page has been replaced using an :ref:`index.html custom template <customization_custom_templates>`. The ``/-/`` page will always render the default Datasette ``index.html`` template.
+
 .. _DatabaseView:
 
 Database
diff --git a/setup.py b/setup.py
index c69404f8..22ec7963 100644
--- a/setup.py
+++ b/setup.py
@@ -55,7 +55,7 @@ setup(
         "uvicorn>=0.11",
         "aiofiles>=0.4",
         "janus>=0.6.2",
-        "asgi-csrf>=0.9",
+        "asgi-csrf>=0.10",
         "PyYAML>=5.3",
         "mergedeep>=1.1.1",
         "itsdangerous>=1.1",
@@ -71,7 +71,7 @@ setup(
     extras_require={
         "docs": [
             "Sphinx==7.4.7",
-            "furo==2024.7.18",
+            "furo==2024.8.6",
             "sphinx-autobuild",
             "codespell>=2.2.5",
             "blacken-docs",
@@ -84,7 +84,7 @@ setup(
             "pytest-xdist>=2.2.1",
             "pytest-asyncio>=0.17",
             "beautifulsoup4>=4.8.1",
-            "black==24.4.2",
+            "black==24.8.0",
             "blacken-docs==1.18.0",
             "pytest-timeout>=1.4.2",
             "trustme>=0.7",


Test patch:
diff --git a/tests/test_api.py b/tests/test_api.py
index 431ab5ce..01c9bb79 100644
--- a/tests/test_api.py
+++ b/tests/test_api.py
@@ -389,6 +389,29 @@ async def test_database_page(ds_client):
             },
             "private": False,
         },
+        {
+            "name": "searchable_fts",
+            "columns": [
+                "text1",
+                "text2",
+                "name with . and spaces",
+            ]
+            + (
+                [
+                    "searchable_fts",
+                    "docid",
+                    "__langid",
+                ]
+                if supports_table_xinfo()
+                else []
+            ),
+            "primary_keys": [],
+            "count": 2,
+            "hidden": False,
+            "fts_table": "searchable_fts",
+            "foreign_keys": {"incoming": [], "outgoing": []},
+            "private": False,
+        },
         {
             "name": "searchable_tags",
             "columns": ["searchable_id", "tag"],
@@ -525,29 +548,6 @@ async def test_database_page(ds_client):
             "foreign_keys": {"incoming": [], "outgoing": []},
             "private": False,
         },
-        {
-            "name": "searchable_fts",
-            "columns": [
-                "text1",
-                "text2",
-                "name with . and spaces",
-            ]
-            + (
-                [
-                    "searchable_fts",
-                    "docid",
-                    "__langid",
-                ]
-                if supports_table_xinfo()
-                else []
-            ),
-            "primary_keys": [],
-            "count": 2,
-            "hidden": True,
-            "fts_table": "searchable_fts",
-            "foreign_keys": {"incoming": [], "outgoing": []},
-            "private": False,
-        },
         {
             "name": "searchable_fts_docsize",
             "columns": ["docid", "size"],
diff --git a/tests/test_api_write.py b/tests/test_api_write.py
index b442113b..9c2b9b45 100644
--- a/tests/test_api_write.py
+++ b/tests/test_api_write.py
@@ -37,12 +37,22 @@ def _headers(token):
 
 
 @pytest.mark.asyncio
-async def test_insert_row(ds_write):
+@pytest.mark.parametrize(
+    "content_type",
+    (
+        "application/json",
+        "application/json; charset=utf-8",
+    ),
+)
+async def test_insert_row(ds_write, content_type):
     token = write_token(ds_write)
     response = await ds_write.client.post(
         "/data/docs/-/insert",
         json={"row": {"title": "Test", "score": 1.2, "age": 5}},
-        headers=_headers(token),
+        headers={
+            "Authorization": "Bearer {}".format(token),
+            "Content-Type": content_type,
+        },
     )
     expected_row = {"id": 1, "title": "Test", "score": 1.2, "age": 5}
     assert response.status_code == 201
diff --git a/tests/test_html.py b/tests/test_html.py
index 5b60d2f5..4d95a8fa 100644
--- a/tests/test_html.py
+++ b/tests/test_html.py
@@ -1,4 +1,6 @@
+from asgi_csrf import Errors
 from bs4 import BeautifulSoup as Soup
+from datasette.app import Datasette
 from datasette.utils import allowed_pragmas
 from .fixtures import (  # noqa
     app_client,
@@ -39,18 +41,40 @@ def test_homepage(app_client_two_attached_databases):
     assert "extra database" == h2.text.strip()
     counts_p, links_p = h2.find_all_next("p")[:2]
     assert (
-        "2 rows in 1 table, 5 rows in 4 hidden tables, 1 view" == counts_p.text.strip()
+        "4 rows in 2 tables, 3 rows in 3 hidden tables, 1 view" == counts_p.text.strip()
     )
     # We should only show visible, not hidden tables here:
     table_links = [
         {"href": a["href"], "text": a.text.strip()} for a in links_p.findAll("a")
     ]
     assert [
+        {"href": r"/extra+database/searchable_fts", "text": "searchable_fts"},
         {"href": r"/extra+database/searchable", "text": "searchable"},
         {"href": r"/extra+database/searchable_view", "text": "searchable_view"},
     ] == table_links
 
 
+@pytest.mark.asyncio
+@pytest.mark.parametrize("path", ("/", "/-/"))
+async def test_homepage_alternative_location(path, tmp_path_factory):
+    template_dir = tmp_path_factory.mktemp("templates")
+    (template_dir / "index.html").write_text("Custom homepage", "utf-8")
+    datasette = Datasette(template_dir=str(template_dir))
+    response = await datasette.client.get(path)
+    assert response.status_code == 200
+    html = response.text
+    if path == "/":
+        assert html == "Custom homepage"
+    else:
+        assert '<meta name="robots" content="noindex">' in html
+
+
+@pytest.mark.asyncio
+async def test_homepage_alternative_redirect(ds_client):
+    response = await ds_client.get("/-")
+    assert response.status_code == 301
+
+
 @pytest.mark.asyncio
 async def test_http_head(ds_client):
     response = await ds_client.head("/")
@@ -1136,3 +1160,17 @@ async def test_database_color(ds_client):
 
             pdb.set_trace()
         assert any(fragment in response.text for fragment in expected_fragments)
+
+
+@pytest.mark.asyncio
+async def test_custom_csrf_error(ds_client):
+    response = await ds_client.post(
+        "/-/messages",
+        data={
+            "message": "A message",
+        },
+        cookies={"csrftoken": "x"},
+    )
+    assert response.status_code == 403
+    assert response.headers["content-type"] == "text/html; charset=utf-8"
+    assert "Error code is FORM_URLENCODED_MISMATCH." in response.text
diff --git a/tests/test_internals_database.py b/tests/test_internals_database.py
index 1c155cf3..bc3c8fcf 100644
--- a/tests/test_internals_database.py
+++ b/tests/test_internals_database.py
@@ -4,7 +4,7 @@ Tests for the datasette.database.Database class
 
 from datasette.app import Datasette
 from datasette.database import Database, Results, MultipleValues
-from datasette.utils.sqlite import sqlite3
+from datasette.utils.sqlite import sqlite3, sqlite_version
 from datasette.utils import Column
 from .fixtures import app_client, app_client_two_attached_databases_crossdb_enabled
 import pytest
@@ -664,3 +664,50 @@ async def test_in_memory_databases_forbid_writes(app_client):
     # Using db.execute_write() should work:
     await db.execute_write("create table foo (t text)")
     assert await db.table_names() == ["foo"]
+
+
+def pragma_table_list_supported():
+    return sqlite_version()[1] >= 37
+
+
+@pytest.mark.asyncio
+@pytest.mark.skipif(
+    not pragma_table_list_supported(), reason="Requires PRAGMA table_list support"
+)
+async def test_hidden_tables(app_client):
+    ds = app_client.ds
+    db = ds.add_database(Database(ds, is_memory=True, is_mutable=True))
+    assert await db.hidden_table_names() == []
+    await db.execute("create virtual table f using fts5(a)")
+    assert await db.hidden_table_names() == [
+        "f_config",
+        "f_content",
+        "f_data",
+        "f_docsize",
+        "f_idx",
+    ]
+
+    await db.execute("create virtual table r using rtree(id, amin, amax)")
+    assert await db.hidden_table_names() == [
+        "f_config",
+        "f_content",
+        "f_data",
+        "f_docsize",
+        "f_idx",
+        "r_node",
+        "r_parent",
+        "r_rowid",
+    ]
+
+    await db.execute("create table _hideme(_)")
+    assert await db.hidden_table_names() == [
+        "_hideme",
+        "f_config",
+        "f_content",
+        "f_data",
+        "f_docsize",
+        "f_idx",
+        "r_node",
+        "r_parent",
+        "r_rowid",
+    ]