issue_comments

Table actions

10,495 rows sorted by node_id

✎ View and edit SQL

This data as json, CSV (advanced)

id html_url issue_url node_id ▼ user created_at updated_at author_association body reactions issue performed_via_github_app
894454087 https://github.com/simonw/datasette/issues/1423#issuecomment-894454087 https://api.github.com/repos/simonw/datasette/issues/1423 IC_kwDOBm6k_c41UElH simonw 9599 2021-08-06T18:51:42Z 2021-08-06T18:51:42Z OWNER The invisible tooltip could say "Showing 30 items, more available" (helping save you from counting up to 20 if you know about the secret feature). The numbers could then be fully displayed on the "..." page. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show count of facet values if ?_facet_size=max 962391325  
894454644 https://github.com/simonw/datasette/issues/1423#issuecomment-894454644 https://api.github.com/repos/simonw/datasette/issues/1423 IC_kwDOBm6k_c41UEt0 simonw 9599 2021-08-06T18:52:49Z 2021-08-06T18:52:49Z OWNER This means that the counts would be unavailable to users who cannot see tooltips (e.g. mobile users) on pages that did not have any facets that broke the 30 limit and hence displayed that "..." link. I think I'm OK with that, for the moment. May revisit in the future. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show count of facet values if ?_facet_size=max 962391325  
894589140 https://github.com/simonw/datasette/issues/1422#issuecomment-894589140 https://api.github.com/repos/simonw/datasette/issues/1422 IC_kwDOBm6k_c41UljU simonw 9599 2021-08-07T01:58:16Z 2021-08-07T01:58:24Z OWNER Also need to consider this hidden field - it should pass the `_hide_sql` or `_show_sql` parameters depending on the same logic: https://github.com/simonw/datasette/blob/acc22436622ff8476c30acf45ed60f54b4aaa5d9/datasette/templates/query.html#L47-L49 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Ability to default to hiding the SQL for a canned query 961367843  
894606843 https://github.com/simonw/datasette/issues/1421#issuecomment-894606843 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41Up37 simonw 9599 2021-08-07T05:17:12Z 2021-08-07T05:17:12Z OWNER Marking this blocked because I don't have a way around the needing-a-SQLite-SQL-parser problem at the moment. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894606796 https://github.com/simonw/datasette/issues/1421#issuecomment-894606796 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41Up3M simonw 9599 2021-08-07T05:16:39Z 2021-08-07T05:16:39Z OWNER Urgh, yeah I've seen this one before. Fixing it pretty much requires writing a full SQLite SQL syntax parser in Python, which is frustratingly complicated for solving this issue! You can work around this for a canned query by using the optional `params:` argument documented here: https://docs.datasette.io/en/stable/sql_queries.html#canned-query-parameters {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894607989 https://github.com/simonw/datasette/issues/1422#issuecomment-894607989 https://api.github.com/repos/simonw/datasette/issues/1422 IC_kwDOBm6k_c41UqJ1 simonw 9599 2021-08-07T05:31:57Z 2021-08-07T05:31:57Z OWNER Demo: https://latest.datasette.io/fixtures/neighborhood_search Documentation: https://docs.datasette.io/en/latest/sql_queries.html#additional-canned-query-options {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Ability to default to hiding the SQL for a canned query 961367843  
894922145 https://github.com/simonw/datasette/issues/1421#issuecomment-894922145 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V22h simonw 9599 2021-08-09T03:07:38Z 2021-08-09T03:07:38Z OWNER I hoped this would work: ```sql with foo as ( explain select * from facetable where state = :state and on_earth = :on_earth and neighborhood not like '00:04' ) select p4 from foo where opcode = 'Variable' ``` But sadly [it returns an error](https://latest.datasette.io/fixtures?sql=with+foo+as+%28%0D%0A++explain+select+*+from+facetable%0D%0A++where+state+%3D+%3Astate%0D%0A++and+on_earth+%3D+%3Aon_earth%0D%0A++and+neighborhood+not+like+%2700%3A04%27%0D%0A%29%0D%0Aselect+p4+from+foo+where+opcode+%3D+%27Variable%27&state=&on_earth=&04=): > near "explain": syntax error {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894922703 https://github.com/simonw/datasette/issues/1421#issuecomment-894922703 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V2_P simonw 9599 2021-08-09T03:09:29Z 2021-08-09T03:09:29Z OWNER Relevant code: https://github.com/simonw/datasette/blob/ad90a72afa21b737b162e2bbdddc301a97d575cd/datasette/views/database.py#L225-L231 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894921512 https://github.com/simonw/datasette/issues/1421#issuecomment-894921512 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V2so simonw 9599 2021-08-09T03:05:26Z 2021-08-09T03:05:26Z OWNER I may have a way to work around this, using `explain`. Consider this query: ```sql select * from facetable where state = :state and on_earth = :on_earth and neighborhood not like '00:04' ``` Datasette currently gets confused and shows three form fields: https://latest.datasette.io/fixtures?sql=select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth=&04= <img width="698" alt="fixtures__select___from_facetable_where_state____state_and_on_earth____on_earth_and_neighborhood_not_like__00_04__and_pyinfra_pip_py_at_current_·_Fizzadar_pyinfra" src="https://user-images.githubusercontent.com/9599/128656369-8af860bf-f7c1-4c9c-beba-1eb6887a8336.png"> But... if I run `explain` [against that](https://latest.datasette.io/fixtures?sql=explain+select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth=&04=) I get this (truncated): addr | opcode | p1 | p2 | p3 | p4 | p5 | comment -- | -- | -- | -- | -- | -- | -- | -- 20 | ResultRow | 6 | 10 | 0 |   | 0 |   21 | Next | 0 | 3 | 0 |   | 1 |   22 | Halt | 0 | 0 | 0 |   | 0 |   23 | Transaction | 0 | 0 | 35 | 0 | 1 |   24 | Variable | 1 | 2 | 0 | :state | 0 |   25 | Variable | 2 | 3 | 0 | :on_earth | 0 |   26 | String8 | 0 | 4 | 0 | 00:04 | 0 |   27 | Goto | 0 | 1 | 0 |   | 0 |   Could it be as simple as pulling out those `Variable` rows to figure out the names of the variables in the query? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894925437 https://github.com/simonw/datasette/issues/1421#issuecomment-894925437 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V3p9 simonw 9599 2021-08-09T03:19:00Z 2021-08-09T03:19:00Z OWNER This may not work: > `ERROR: sql = 'explain select 1 + :one + :two', params = None: You did not supply a value for binding 1.` The `explain` queries themselves want me to pass them parameters. I could try using the regex to pull out candidates and passing `None` for each of those, including incorrect ones like `:31`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894925914 https://github.com/simonw/datasette/issues/1421#issuecomment-894925914 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V3xa simonw 9599 2021-08-09T03:20:42Z 2021-08-09T03:20:42Z OWNER I think this works! ```python _re_named_parameter = re.compile(":([a-zA-Z0-9_]+)") async def derive_named_parameters(db, sql): explain = 'explain {}'.format(sql.strip().rstrip(";")) possible_params = _re_named_parameter.findall(sql) try: results = await db.execute(explain, {p: None for p in possible_params}) return [row["p4"].lstrip(":") for row in results if row["opcode"] == "Variable"] except sqlite3.DatabaseError: return [] ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894927185 https://github.com/simonw/datasette/issues/1421#issuecomment-894927185 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V4FR simonw 9599 2021-08-09T03:25:01Z 2021-08-09T03:25:01Z OWNER One catch with this approach: if the SQL query is invalid, the parameters will not be extracted and shown as form fields. Maybe that's completely fine? Why display a form if it's going to break when the user actually runs the query? But it does bother me. I worry that someone who is still iterating on and editing their query before actually starting to use it might find the behaviour confusing. So maybe if the query raises an exception it could fall back on the regular expression results? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894929080 https://github.com/simonw/datasette/issues/1421#issuecomment-894929080 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V4i4 simonw 9599 2021-08-09T03:33:02Z 2021-08-09T03:33:02Z OWNER Fixed! Fantastic, this one has been bothering me for *years*. https://latest.datasette.io/fixtures?sql=select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27 <img width="707" alt="fixtures__select___from_facetable_where_state____state_and_on_earth____on_earth_and_neighborhood_not_like__00_04__and_pyinfra_pip_py_at_current_·_Fizzadar_pyinfra" src="https://user-images.githubusercontent.com/9599/128657807-80ac818d-6fd9-4f70-ad26-900cec6a7482.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894929769 https://github.com/simonw/datasette/issues/1421#issuecomment-894929769 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V4tp simonw 9599 2021-08-09T03:36:49Z 2021-08-09T03:36:49Z OWNER SQLite carries a warning about using `EXPLAIN` like this: https://www.sqlite.org/lang_explain.html > The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented. I think that's OK here, because of the regular expression fallback. If the format changes in the future in a way that breaks the query the error should be caught and the regex-captured parameters should be returned instead. Hmmm... actually that's not entirely true: https://github.com/simonw/datasette/blob/b1fed48a95516ae84c0f020582303ab50ab817e2/datasette/utils/__init__.py#L1084-L1091 If the format changes such that the same columns are returned but the `[row["p4"].lstrip(":") for row in results if row["opcode"] == "Variable"]` list comprehension returns an empty array it will break Datasette! I'm going to take that risk for the moment, but I'll actively watch out for problems in the future. If this does turn out to be bad I can always go back to the pure regular expression mechanism. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894930013 https://github.com/simonw/datasette/issues/1421#issuecomment-894930013 https://api.github.com/repos/simonw/datasette/issues/1421 IC_kwDOBm6k_c41V4xd simonw 9599 2021-08-09T03:38:06Z 2021-08-09T03:38:06Z OWNER Amusing edge-case: if you run this against a `explain ...` query it falls back to using regular expressions, because `explain explain select ...` is invalid SQL. https://latest.datasette.io/fixtures?sql=explain+select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth= {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095  
894865323 https://github.com/simonw/datasette/issues/1425#issuecomment-894865323 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41Vo-r simonw 9599 2021-08-08T22:33:19Z 2021-08-08T22:33:19Z OWNER I can do this with the `await_me_maybe()` function, as seen here: https://github.com/simonw/datasette/blob/a21853c9dade240734abc6b4f750fae09a3e840a/datasette/app.py#L864-L873 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894864682 https://github.com/simonw/datasette/issues/1424#issuecomment-894864682 https://api.github.com/repos/simonw/datasette/issues/1424 IC_kwDOBm6k_c41Vo0q simonw 9599 2021-08-08T22:26:46Z 2021-08-08T22:26:46Z OWNER Note that the `sqlite3` exceptions are in `sqlite3` if using the Python standard library but are in `pysqlite3` if that module is being used instead. So maybe encourage people to use them from `datasette.sqlite.sqlite3` instead, which will point to the correct package. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Document exceptions that can be raised by db.execute() and friends 963527045  
894864744 https://github.com/simonw/datasette/issues/1424#issuecomment-894864744 https://api.github.com/repos/simonw/datasette/issues/1424 IC_kwDOBm6k_c41Vo1o simonw 9599 2021-08-08T22:27:31Z 2021-08-08T22:27:31Z OWNER https://docs.python.org/3/library/sqlite3.html#exceptions is useful - it looks like `sqlite3.DatabaseError` is the super-class of all of the other exceptions that we might see. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Document exceptions that can be raised by db.execute() and friends 963527045  
894864404 https://github.com/simonw/datasette/issues/1424#issuecomment-894864404 https://api.github.com/repos/simonw/datasette/issues/1424 IC_kwDOBm6k_c41VowU simonw 9599 2021-08-08T22:24:06Z 2021-08-08T22:24:06Z OWNER Relevant code: https://github.com/simonw/datasette/blob/de5ce2e56339ad8966f417a4758f7c210c017dec/datasette/database.py#L176-L200 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Document exceptions that can be raised by db.execute() and friends 963527045  
894864616 https://github.com/simonw/datasette/issues/1424#issuecomment-894864616 https://api.github.com/repos/simonw/datasette/issues/1424 IC_kwDOBm6k_c41Vozo simonw 9599 2021-08-08T22:26:08Z 2021-08-08T22:26:08Z OWNER - `datasette.database.QueryInterrupted` for queries that were interrupted - `sqlite3.OperationalError` - `sqlite3.DatabaseError` and more {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Document exceptions that can be raised by db.execute() and friends 963527045  
894869692 https://github.com/simonw/datasette/issues/1425#issuecomment-894869692 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41VqC8 simonw 9599 2021-08-08T23:08:29Z 2021-08-08T23:08:29Z OWNER Updated documentation: https://docs.datasette.io/en/latest/plugin_hooks.html#render-cell-value-column-table-database-datasette {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894881448 https://github.com/simonw/datasette/issues/1425#issuecomment-894881448 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41Vs6o simonw 9599 2021-08-09T00:24:25Z 2021-08-09T00:24:39Z OWNER My hunch is that the "skip this `render_cell()` result if it returns `None`" logic isn't working correctly, ever since I added the `await_me_maybe` line. Could that be because Pluggy handles the "do the next if `None` is returned" logic itself, but I'm no-longer returning `None`, I'm returning an awaitable which when awaited returns `None`. This would suggest that all of the `await_me_maybe()` plugin hooks have the same bug. That's definitely possible - it may well be that no-one has yet stumbled across a bug caused by a plugin returning an awaitable and hence not being skipped, because plugin hooks that return awaitable are rare enough that no-one has tried two plugins which both use that trick. Still don't see why it would pass on my laptop but fail in CI though. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894881016 https://github.com/simonw/datasette/issues/1425#issuecomment-894881016 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41Vsz4 simonw 9599 2021-08-09T00:21:53Z 2021-08-09T00:21:53Z OWNER Still one test failure: ``` def test_hook_render_cell_link_from_json(app_client): sql = """ select '{"href": "http://example.com/", "label":"Example"}' """.strip() path = "/fixtures?" + urllib.parse.urlencode({"sql": sql}) response = app_client.get(path) td = Soup(response.body, "html.parser").find("table").find("tbody").find("td") a = td.find("a") > assert a is not None, str(a) E AssertionError: None E assert None is not None ``` The weird thing about this one is that I can't replicate it on my laptop - but it happens in CI every time, including when I shell in and try to run that single test. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894882123 https://github.com/simonw/datasette/issues/1425#issuecomment-894882123 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41VtFL simonw 9599 2021-08-09T00:27:43Z 2021-08-09T00:27:43Z OWNER Good news: `render_cell()` is the only hook to use `firstresult=True`: https://github.com/simonw/datasette/blob/f3c9edb376a13c09b5ecf97c7390f4e49efaadf2/datasette/hookspecs.py#L62-L64 https://pluggy.readthedocs.io/en/latest/#first-result-only {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894882642 https://github.com/simonw/datasette/issues/1425#issuecomment-894882642 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41VtNS simonw 9599 2021-08-09T00:29:57Z 2021-08-09T00:29:57Z OWNER Here's the code in `pluggy` that implements this: https://github.com/pytest-dev/pluggy/blob/0a064fe275060dbdb1fe6e10c888e72bc400fb33/src/pluggy/callers.py#L31-L43 ```python if hook_impl.hookwrapper: try: gen = hook_impl.function(*args) next(gen) # first yield teardowns.append(gen) except StopIteration: _raise_wrapfail(gen, "did not yield") else: res = hook_impl.function(*args) if res is not None: results.append(res) if firstresult: # halt further impl calls break ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894883664 https://github.com/simonw/datasette/issues/1425#issuecomment-894883664 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41VtdQ simonw 9599 2021-08-09T00:33:56Z 2021-08-09T00:33:56Z OWNER I could extract that code out and write my own function which implements the equivalent of calling `pm.hook.render_cell(...)` but runs `await_me_maybe()` before checking if `res is not None`. That's pretty nasty. Could I instead call the plugin hook normally, but then have additional logic which says "if I await it and it returns `None` then try calling the hook again but skip this one" - not sure if there's a way to do that either. I could remove the `firstresult=True` from the hookspec - which would cause it to call and return ALL hooks - but then in my own code use only the first one. This is slightly less efficient (since it calls all the hooks and then discards all-but-one value) but it's the least unpleasant in terms of the code I would have to write - plus I don't think it's going to be THAT common for someone to have multiple expensive `render_cell()` hooks installed at once (they are usually pretty cheap). {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894884874 https://github.com/simonw/datasette/issues/1425#issuecomment-894884874 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41VtwK simonw 9599 2021-08-09T00:38:20Z 2021-08-09T00:38:20Z OWNER I'm trying the version where I remove `firstresult=True`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894893319 https://github.com/simonw/datasette/issues/1425#issuecomment-894893319 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41Vv0H simonw 9599 2021-08-09T01:08:56Z 2021-08-09T01:09:12Z OWNER Demo: https://latest.datasette.io/fixtures/simple_primary_key shows `RENDER_CELL_ASYNC_RESULT` where the CSV version shows `RENDER_CELL_ASYNC`: https://latest.datasette.io/fixtures/simple_primary_key.csv - because of this test plugin code: https://github.com/simonw/datasette/blob/a390bdf9cef01d8723d025fc3348e81345ff4856/tests/plugins/my_plugin.py#L98-L122 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
894900267 https://github.com/simonw/datasette/issues/1425#issuecomment-894900267 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41Vxgr simonw 9599 2021-08-09T01:31:22Z 2021-08-09T01:31:22Z OWNER I used this to build a new plugin: https://github.com/simonw/datasette-query-links Demo here: https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++%27select+*+from+[facetable]%27+as+query%0D%0Aunion%0D%0Aselect%0D%0A++%27select+sqlite_version()%27%0D%0Aunion%0D%0Aselect%0D%0A++%27select+this+is+invalid+SQL+so+will+not+be+linked%27 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
895003796 https://github.com/simonw/datasette/issues/1425#issuecomment-895003796 https://api.github.com/repos/simonw/datasette/issues/1425 IC_kwDOBm6k_c41WKyU abdusco 3243482 2021-08-09T07:14:35Z 2021-08-09T07:14:35Z CONTRIBUTOR I believe this also provides a workaround for the problem I face in https://github.com/simonw/datasette/issues/1300. Now I should be able to get table PKs and generate a row URL. I'll test this out and report my findings. ```py from datasette.utils import path_from_row_pks pks = await db.primary_keys(table) url = self.ds.urls.row_blob( database, table, path_from_row_pks(row, pks, not pks), column, ) ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} render_cell() hook should support returning an awaitable 963528457  
895500565 https://github.com/simonw/datasette/issues/1426#issuecomment-895500565 https://api.github.com/repos/simonw/datasette/issues/1426 IC_kwDOBm6k_c41YEEV simonw 9599 2021-08-09T20:00:04Z 2021-08-09T20:00:04Z OWNER A few options for how this would work: - `datasette ... --robots allow` - `datasette ... --setting robots allow` Options could be: - `allow` - allow all crawling - `deny` - deny all crawling - `limited` - allow access to the homepage and the index pages for each database and each table, but disallow crawling any further than that The "limited" mode is particularly interesting. Could even make it the default, but I think that may be a bit too confusing. Idea would be to get the key pages indexed but use `nofollow` to discourage crawlers from indexing individual row pages or deep pages like `https://datasette.io/content/repos?_facet=owner&_facet=language&_facet_array=topics&topics__arraycontains=sqlite#facet-owner`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Manage /robots.txt in Datasette core, block robots by default 964322136  
895509536 https://github.com/simonw/datasette/issues/1426#issuecomment-895509536 https://api.github.com/repos/simonw/datasette/issues/1426 IC_kwDOBm6k_c41YGQg simonw 9599 2021-08-09T20:12:57Z 2021-08-09T20:12:57Z OWNER I could try out the `X-Robots` HTTP header too: https://developers.google.com/search/docs/advanced/robots/robots_meta_tag#xrobotstag {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Manage /robots.txt in Datasette core, block robots by default 964322136  
895510773 https://github.com/simonw/datasette/issues/1426#issuecomment-895510773 https://api.github.com/repos/simonw/datasette/issues/1426 IC_kwDOBm6k_c41YGj1 simonw 9599 2021-08-09T20:14:50Z 2021-08-09T20:19:22Z OWNER https://twitter.com/mal/status/1424825895139876870 > True pinging google should be part of the build process on a static site :) That's another aspect of this: if you DO want your site crawled, teaching the `datasette publish` command how to ping Google when a deploy has gone out could be a nice improvement. Annoyingly it looks like you need to configure an auth token of some sort in order to use their API though, which is likely too much hassle to be worth building into Datasette itself: https://developers.google.com/search/apis/indexing-api/v3/using-api ``` curl -X POST https://indexing.googleapis.com/v3/urlNotifications:publish -d '{ "url": "https://careers.google.com/jobs/google/technical-writer", "type": "URL_UPDATED" }' -H "Content-Type: application/json" { "error": { "code": 401, "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.", "status": "UNAUTHENTICATED" } } ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Manage /robots.txt in Datasette core, block robots by default 964322136  
895522818 https://github.com/simonw/datasette/issues/1426#issuecomment-895522818 https://api.github.com/repos/simonw/datasette/issues/1426 IC_kwDOBm6k_c41YJgC simonw 9599 2021-08-09T20:34:10Z 2021-08-09T20:34:10Z OWNER At the very least Datasette should serve a blank `/robots.txt` by default - I'm seeing a ton of 404s for it in the logs. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Manage /robots.txt in Datasette core, block robots by default 964322136  
905899177 https://github.com/simonw/datasette/issues/859#issuecomment-905899177 https://api.github.com/repos/simonw/datasette/issues/859 IC_kwDOBm6k_c41_uyp brandonrobertz 2670795 2021-08-25T21:48:00Z 2021-08-25T21:48:00Z CONTRIBUTOR Upon first stab, there's two issues here: - DB/table/row counts (as discussed above). This isn't too bad if the DBs are actually above the MAX limit check. - Populating the internal DB. On first load of a giant set of DBs, it can take 10-20 mins to populate. By altering datasette and persisting the internal DB to disk, this problem is vastly improved, but I'm sure this will cause problems elsewhere. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Database page loads too slowly with many large tables (due to table counts) 642572841  
905900807 https://github.com/simonw/datasette/issues/859#issuecomment-905900807 https://api.github.com/repos/simonw/datasette/issues/859 IC_kwDOBm6k_c41_vMH simonw 9599 2021-08-25T21:51:10Z 2021-08-25T21:51:10Z OWNER 10-20 minutes to populate `_internal`! How many databases and tables is that for? I may have to rethink the `_internal` mechanism entirely. One possible alternative would be for the Datasette homepage to just show a list of available databases (maybe only if there are more than X connected) and then load in their metadata only the first time they are accessed. I need to get my own stress testing rig setup for this. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Database page loads too slowly with many large tables (due to table counts) 642572841  
905904540 https://github.com/simonw/datasette/issues/859#issuecomment-905904540 https://api.github.com/repos/simonw/datasette/issues/859 IC_kwDOBm6k_c41_wGc brandonrobertz 2670795 2021-08-25T21:59:14Z 2021-08-25T21:59:55Z CONTRIBUTOR I did two tests: one with 1000 5-30mb DBs and a second with 20 multi gig DBs. For the second, I created them like so: `for i in {1..20}; do sqlite-generate db$i.db --tables ${i}00 --rows 100,2000 --columns 5,100 --pks 0 --fks 0; done` This was for deciding whether to use lots of small DBs or to group things into a smaller number of bigger DBs. The second strategy wins. By simply persisting the `_internal` DB to disk, I was able to avoid most of the performance issues I was experiencing previously. (To do this, I changed the `datasette/internal_db.py:init_internal_db` creates to if not exists, and changed the `_internal` DB instantiation in `datasette/app.py:Datasette.__init__` to a path with `is_mutable=True`.) Super rough, but the pages now load so I can continue testing ideas. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Database page loads too slowly with many large tables (due to table counts) 642572841  
898063815 https://github.com/simonw/datasette/issues/1293#issuecomment-898063815 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41h13H simonw 9599 2021-08-13T00:33:17Z 2021-08-13T00:33:17Z OWNER Improved version of that function: ```python def columns_for_query(conn, sql): """ Given a SQLite connection ``conn`` and a SQL query ``sql``, returns a list of ``(table_name, column_name)`` pairs, one per returned column. ``(None, None)`` if no table and column could be derived. """ rows = conn.execute('explain ' + sql).fetchall() table_rootpage_by_register = {r['p1']: r['p2'] for r in rows if r['opcode'] == 'OpenRead'} names_by_rootpage = dict( conn.execute( 'select rootpage, name from sqlite_master where rootpage in ({})'.format( ', '.join(map(str, table_rootpage_by_register.values())) ) ) ) columns_by_column_register = {} for row in rows: if row['opcode'] in ('Rowid', 'Column'): addr, opcode, table_id, cid, column_register, p4, p5, comment = row table = names_by_rootpage[table_rootpage_by_register[table_id]] columns_by_column_register[column_register] = (table, cid) result_row = [dict(r) for r in rows if r['opcode'] == 'ResultRow'][0] registers = list(range(result_row["p1"], result_row["p1"] + result_row["p2"])) all_column_names = {} for table in names_by_rootpage.values(): table_xinfo = conn.execute('pragma table_xinfo({})'.format(table)).fetchall() for row in table_xinfo: all_column_names[(table, row["cid"])] = row["name"] final_output = [] for r in registers: try: table, cid = columns_by_column_register[r] final_output.append((table, all_column_names[table, cid])) except KeyError: final_output.append((None, None)) return final_output ``` It works! <img width="1440" alt="Banners_and_Alerts_and_fixtures__select_attraction_id__roadside_attractions_name__characteristic_id__attraction_characteristic_name_as_characteristic_from_roadside_attraction_characteristics_join_roadside_attractions_on_roadside_attractions" src="… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898065011 https://github.com/simonw/datasette/issues/1293#issuecomment-898065011 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41h2Jz simonw 9599 2021-08-13T00:36:30Z 2021-08-13T00:36:30Z OWNER > https://latest.datasette.io/fixtures?sql=explain+select+*+from+paginated_view will be an interesting test query - because `paginated_view` is defined like this: > > ```sql > CREATE VIEW paginated_view AS > SELECT > content, > '- ' || content || ' -' AS content_extra > FROM no_primary_key; > ``` > > So this will help test that the mechanism isn't confused by output columns that are created through a concatenation expression. Here's what it does for that: <img width="748" alt="fixtures__select___from_paginated_view" src="https://user-images.githubusercontent.com/9599/129286962-426bfa56-3946-447a-996d-668b4d80f5c1.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898065948 https://github.com/simonw/datasette/issues/1293#issuecomment-898065948 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41h2Yc simonw 9599 2021-08-13T00:38:58Z 2021-08-13T00:38:58Z OWNER Trying to run `explain select * from facetable` fails with an error in my prototype, because it tries to execute `explain explain select * from facetable` - so I need to spot that error and ignore it. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898066466 https://github.com/simonw/datasette/issues/1293#issuecomment-898066466 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41h2gi simonw 9599 2021-08-13T00:40:24Z 2021-08-13T00:40:24Z OWNER It figures out renamed columns too: <img width="694" alt="fixtures__select_created__state_as_the_state_from_facetable" src="https://user-images.githubusercontent.com/9599/129287208-1347fe80-f62e-4ed2-80c6-06a223cbe749.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898072940 https://github.com/simonw/datasette/issues/1431#issuecomment-898072940 https://api.github.com/repos/simonw/datasette/issues/1431 IC_kwDOBm6k_c41h4Fs simonw 9599 2021-08-13T00:58:40Z 2021-08-13T00:58:40Z OWNER While I'm doing this I should rename this internal variable to avoid confusion in the future: https://github.com/simonw/datasette/blob/e837095ef35ae155b4c78cc9a8b7133a48c94f03/datasette/app.py#L203 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} `--help-config` should be called `--help-settings` 969840302  
898074849 https://github.com/simonw/datasette/issues/1432#issuecomment-898074849 https://api.github.com/repos/simonw/datasette/issues/1432 IC_kwDOBm6k_c41h4jh simonw 9599 2021-08-13T01:03:40Z 2021-08-13T01:03:40Z OWNER Also this method: https://github.com/simonw/datasette/blob/77f46297a88ac7e49dad2139410b01ee56d5f99c/datasette/app.py#L422-L424 And the places that use it: https://github.com/simonw/datasette/blob/fc4846850fffd54561bc125332dfe97bb41ff42e/datasette/views/base.py#L617 https://github.com/simonw/datasette/blob/fc4846850fffd54561bc125332dfe97bb41ff42e/datasette/views/database.py#L459 Which is used in this template: https://github.com/simonw/datasette/blob/77f46297a88ac7e49dad2139410b01ee56d5f99c/datasette/templates/table.html#L204 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rename Datasette.__init__(config=) parameter to settings= 969855774  
898079507 https://github.com/simonw/datasette/issues/1432#issuecomment-898079507 https://api.github.com/repos/simonw/datasette/issues/1432 IC_kwDOBm6k_c41h5sT simonw 9599 2021-08-13T01:08:42Z 2021-08-13T01:09:41Z OWNER This is going to break some plugins: https://ripgrep.datasette.io/-/ripgrep?pattern=config%3D&literal=on&glob=%21datasette%2F** > ### datasette-cluster-map/tests/test_cluster_map.py > > @pytest.mark.asyncio > > async def test_respects_base_url(): > ds = Datasette([], memory=True, config={"base_url": "/foo/"}) > response = await ds.client.get("/:memory:?sql=select+1+as+latitude,+2+as+longitude") > assert ( > > ### datasette-export-notebook/tests/test_export_notebook.py > > @pytest.mark.asyncio > > async def test_notebook_no_csv(db_path): > datasette = Datasette([db_path], config={"allow_csv_stream": False}) > response = await datasette.client.get("/db/big.Notebook") > assert ".csv" not in response.text > > ### datasette-publish-vercel/tests/test_publish_vercel.py > metadata=metadata, > cors=True, > config={"default_page_size": 10, "sql_time_limit_ms": 2000} > ).app() > """ > > ### datasette-publish-vercel/datasette_publish_vercel/__init__.py > metadata=metadata{extras}, > cors=True, > config={settings} > > ).app() > > """.strip() > > ### datasette-search-all/tests/test_search_all.py > > async def test_base_url(db_path, path): > sqlite_utils.Database(db_path)["creatures"].enable_fts(["name", "description"]) > datasette = Datasette([db_path], config={"base_url": "/foo/"}) > response = await datasette.client.get(path) > assert response.status_code == 200 I should fix those as soon as this goes out in a release. I won't close this issue until then. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rename Datasette.__init__(config=) parameter to settings= 969855774  
898084675 https://github.com/simonw/datasette/issues/1432#issuecomment-898084675 https://api.github.com/repos/simonw/datasette/issues/1432 IC_kwDOBm6k_c41h69D simonw 9599 2021-08-13T01:11:30Z 2021-08-13T01:11:30Z OWNER It's only `datasette-publish-vercel` that will break the actual functionality - the others will have broken tests. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rename Datasette.__init__(config=) parameter to settings= 969855774  
897960049 https://github.com/simonw/datasette/issues/1429#issuecomment-897960049 https://api.github.com/repos/simonw/datasette/issues/1429 IC_kwDOBm6k_c41hchx simonw 9599 2021-08-12T20:53:04Z 2021-08-12T20:53:04Z OWNER Maybe something like this: > [Next page](#) - 100 per page ([show 1,000 per page](#)) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} UI for setting `?_size=max` on table page 969548935  
897996296 https://github.com/simonw/datasette/issues/942#issuecomment-897996296 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hlYI simonw 9599 2021-08-12T22:01:36Z 2021-08-12T22:01:36Z OWNER I'm going with `"columns": {"name-of-column": "description-of-column"}`. If I decide to make `"col"` and `"nocol"` available in metadata I'll use those as the keys in the metadata, for consistency with the existing query string parameters. I'm OK with having both `"columns": ...` and `"col": ...` keys in the metadata, even though they could be a tiny bit confusing without the documentation. {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898021895 https://github.com/simonw/datasette/issues/942#issuecomment-898021895 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hroH simonw 9599 2021-08-12T22:51:36Z 2021-08-12T22:51:36Z OWNER Prototype: <img width="650" alt="fixtures__sortable__201_rows" src="https://user-images.githubusercontent.com/9599/129279808-08d56b2f-a4d6-4147-b3a6-542c62b566c1.png"> ```diff diff --git a/datasette/static/app.css b/datasette/static/app.css index c6be1e9..5ca64cb 100644 --- a/datasette/static/app.css +++ b/datasette/static/app.css @@ -784,9 +784,14 @@ svg.dropdown-menu-icon { font-size: 0.7em; color: #666; margin: 0; - padding: 0; padding: 4px 8px 4px 8px; } +.dropdown-menu .dropdown-column-description { + margin: 0; + color: #666; + padding: 4px 8px 4px 8px; + max-width: 20em; +} .dropdown-menu li { border-bottom: 1px solid #ccc; } diff --git a/datasette/static/table.js b/datasette/static/table.js index 991346d..a903112 100644 --- a/datasette/static/table.js +++ b/datasette/static/table.js @@ -9,6 +9,7 @@ var DROPDOWN_HTML = `<div class="dropdown-menu"> <li><a class="dropdown-not-blank" href="#">Show not-blank rows</a></li> </ul> <p class="dropdown-column-type"></p> +<p class="dropdown-column-description"></p> </div>`; var DROPDOWN_ICON_SVG = `<svg xmlns="http://www.w3.org/2000/svg" width="14" height="14" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"> @@ -166,6 +167,14 @@ var DROPDOWN_ICON_SVG = `<svg xmlns="http://www.w3.org/2000/svg" width="14" heig } else { columnTypeP.style.display = "none"; } + + var columnDescriptionP = menu.querySelector(".dropdown-column-description"); + if (th.dataset.columnDescription) { + columnDescriptionP.innerText = th.dataset.columnDescription; + columnDescriptionP.style.display = 'block'; + } else { + columnDescriptionP.style.display = 'none'; + } menu.style.position = "absolute"; menu.style.top = menuTop + 6 + "px"; menu.style.left = menuLeft + "px"; diff --git a/datasette/templates/_table.html b/datasette/templates/_table.html index d765937..64… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898022235 https://github.com/simonw/datasette/issues/942#issuecomment-898022235 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hrtb simonw 9599 2021-08-12T22:52:23Z 2021-08-12T22:52:23Z OWNER I like this. Need to solve for mobile though where the cog menu isn't visible - I think I'll do that with a definition list at the top of the page. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898032118 https://github.com/simonw/datasette/issues/942#issuecomment-898032118 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41huH2 zaneselvans 596279 2021-08-12T23:12:00Z 2021-08-12T23:12:00Z NONE This looks awesome. We'll definitely make extensive use of this feature! On Thu, Aug 12, 2021 at 5:52 PM Simon Willison ***@***.***> wrote: > I like this. Need to solve for mobile though where the cog menu isn't > visible - I think I'll do that with a definition list at the top of the > page. > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <https://github.com/simonw/datasette/issues/942#issuecomment-898022235>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AAERSNYEF6QRZO2HRJGRIWDT4RGDFANCNFSM4QEC6ATA> > . > Triage notifications on the go with GitHub Mobile for iOS > <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> > or Android > <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> > . > -- Zane A. Selvans, PhD Chief Data Wrangler Catalyst Cooperative https://catalyst.coop ***@***.*** Signal/WhatsApp/SMS: +1 720 443 1363 Twitter: @ZaneSelvans <https://twitter.com/ZaneSelvans> PGP <https://www.gnupg.org/>: 0x64F7B56F3A127B04 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898037456 https://github.com/simonw/datasette/issues/942#issuecomment-898037456 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hvbQ simonw 9599 2021-08-12T23:23:34Z 2021-08-12T23:23:34Z OWNER Prototype with a `<dl>`: <img width="721" alt="fixtures__sortable__201_rows" src="https://user-images.githubusercontent.com/9599/129282175-c94dcf74-beb3-4f0a-9106-f75d19369fb0.png"> ```diff diff --git a/datasette/static/app.css b/datasette/static/app.css index c6be1e9..bf068fd 100644 --- a/datasette/static/app.css +++ b/datasette/static/app.css @@ -836,6 +841,16 @@ svg.dropdown-menu-icon { background-repeat: no-repeat; } +dl.column-descriptions dt { + font-weight: bold; +} +dl.column-descriptions dd { + padding-left: 1.5em; + white-space: pre-wrap; + line-height: 1.1em; + color: #666; +} + .anim-scale-in { animation-name: scale-in; animation-duration: 0.15s; diff --git a/datasette/templates/table.html b/datasette/templates/table.html index 211352b..466e8a4 100644 --- a/datasette/templates/table.html +++ b/datasette/templates/table.html @@ -51,6 +51,14 @@ {% block description_source_license %}{% include "_description_source_license.html" %}{% endblock %} +{% if metadata.columns %} +<dl class="column-descriptions"> + {% for column_name, column_description in metadata.columns.items() %} + <dt>{{ column_name }}</dt><dd>{{ column_description }}</dd> + {% endfor %} +</dl> +{% endif %} + {% if filtered_table_rows_count or human_description_en %} <h3>{% if filtered_table_rows_count or filtered_table_rows_count == 0 %}{{ "{:,}".format(filtered_table_rows_count) }} row{% if filtered_table_rows_count == 1 %}{% else %}s{% endif %}{% endif %} {% if human_description_en %}{{ human_description_en }}{% endif %} ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898037650 https://github.com/simonw/datasette/issues/942#issuecomment-898037650 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hveS simonw 9599 2021-08-12T23:23:54Z 2021-08-12T23:23:54Z OWNER I like this enough that I'm going to ship it as an alpha and try it out on a couple of live projects. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898043575 https://github.com/simonw/datasette/pull/1430#issuecomment-898043575 https://api.github.com/repos/simonw/datasette/issues/1430 IC_kwDOBm6k_c41hw63 codecov[bot] 22429695 2021-08-12T23:39:36Z 2021-08-12T23:49:51Z NONE # [Codecov](https://codecov.io/gh/simonw/datasette/pull/1430?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) Report > Merging [#1430](https://codecov.io/gh/simonw/datasette/pull/1430?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (9419947) into [main](https://codecov.io/gh/simonw/datasette/commit/b1fed48a95516ae84c0f020582303ab50ab817e2?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (b1fed48) will **increase** coverage by `0.00%`. > The diff coverage is `100.00%`. [![Impacted file tree graph](https://codecov.io/gh/simonw/datasette/pull/1430/graphs/tree.svg?width=650&height=150&src=pr&token=eSahVY7kw1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison)](https://codecov.io/gh/simonw/datasette/pull/1430?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) ```diff @@ Coverage Diff @@ ## main #1430 +/- ## ======================================= Coverage 91.71% 91.71% ======================================= Files 34 34 Lines 4417 4418 +1 ======================================= + Hits 4051 4052 +1 Misses 366 366 ``` | [Impacted Files](https://codecov.io/gh/simonw/datasette/pull/1430?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) | Coverage Δ | | |---|---|---| | [datasette/views/table.py](https://codecov.io/gh/simonw/datasette/pull/1430/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison#diff-ZGF0YXNldHRlL3ZpZXdzL3RhYmxlLnB5) | `96.00% <100.00%> (+<0.01%)` | :arrow_up: | ------ [Con… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Column metadata 969758038  
898051645 https://github.com/simonw/datasette/issues/942#issuecomment-898051645 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hy49 simonw 9599 2021-08-13T00:02:25Z 2021-08-13T00:02:25Z OWNER And on mobile: ![5FAF8D73-7199-4BB7-A5B8-9E46DCB4A985](https://user-images.githubusercontent.com/9599/129284817-dc13cbf4-144e-4f4c-8fb7-470602e2eea0.jpeg) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898050457 https://github.com/simonw/datasette/issues/942#issuecomment-898050457 https://api.github.com/repos/simonw/datasette/issues/942 IC_kwDOBm6k_c41hymZ simonw 9599 2021-08-12T23:59:53Z 2021-08-12T23:59:53Z OWNER Documentation: https://docs.datasette.io/en/latest/metadata.html#column-descriptions Live demo: https://latest.datasette.io/fixtures/roadside_attractions <img width="1045" alt="fixtures__roadside_attractions__4_rows" src="https://user-images.githubusercontent.com/9599/129284675-ef2fd6ed-54ba-4f6a-add5-7a9253921279.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support column descriptions in metadata.json 681334912  
898056013 https://github.com/simonw/datasette/issues/1293#issuecomment-898056013 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41hz9N simonw 9599 2021-08-13T00:12:09Z 2021-08-13T00:12:09Z OWNER Having added column metadata in #1430 (ref #942) I could also include a definition list at the top of the query results page exposing the column descriptions for any columns, using the same EXPLAIN mechanism. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898185944 https://github.com/simonw/datasette/issues/1429#issuecomment-898185944 https://api.github.com/repos/simonw/datasette/issues/1429 IC_kwDOBm6k_c41iTrY simonw 9599 2021-08-13T04:37:41Z 2021-08-13T04:37:41Z OWNER If a count is available and the count is less than 1,000 it could say "Show all" instead. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} UI for setting `?_size=max` on table page 969548935  
898450402 https://github.com/simonw/datasette/pull/1433#issuecomment-898450402 https://api.github.com/repos/simonw/datasette/issues/1433 IC_kwDOBm6k_c41jUPi codecov[bot] 22429695 2021-08-13T13:15:55Z 2021-08-13T13:15:55Z NONE # [Codecov](https://codecov.io/gh/simonw/datasette/pull/1433?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) Report > Merging [#1433](https://codecov.io/gh/simonw/datasette/pull/1433?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (ddba6cc) into [main](https://codecov.io/gh/simonw/datasette/commit/2883098770fc66e50183b2b231edbde20848d4d6?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (2883098) will **not change** coverage. > The diff coverage is `n/a`. [![Impacted file tree graph](https://codecov.io/gh/simonw/datasette/pull/1433/graphs/tree.svg?width=650&height=150&src=pr&token=eSahVY7kw1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison)](https://codecov.io/gh/simonw/datasette/pull/1433?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) ```diff @@ Coverage Diff @@ ## main #1433 +/- ## ======================================= Coverage 91.82% 91.82% ======================================= Files 34 34 Lines 4418 4418 ======================================= Hits 4057 4057 Misses 361 361 ``` ------ [Continue to review full report at Codecov](https://codecov.io/gh/simonw/datasette/pull/1433?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison). > **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) > `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data` > Powered by [Codecov](https://codecov… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Update trustme requirement from <0.9,>=0.7 to >=0.7,<0.10 970386262  
898506647 https://github.com/simonw/datasette/issues/1293#issuecomment-898506647 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jh-X simonw 9599 2021-08-13T14:43:19Z 2021-08-13T14:43:19Z OWNER Work will continue in PR #1434. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898517872 https://github.com/simonw/datasette/issues/1293#issuecomment-898517872 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jktw simonw 9599 2021-08-13T15:00:50Z 2021-08-13T15:00:50Z OWNER The primary key column (or `rowid`) often resolves to an `index` record in the `sqlite_master` table, e.g. the second row in this: type | name | tbl_name | rootpage | sql -- | -- | -- | -- | -- table | simple_primary_key | simple_primary_key | 2 | CREATE TABLE simple_primary_key ( id varchar(30) primary key, content text ) index | sqlite_autoindex_simple_primary_key_1 | simple_primary_key | 3 |   {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898519924 https://github.com/simonw/datasette/issues/1293#issuecomment-898519924 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jlN0 simonw 9599 2021-08-13T15:03:36Z 2021-08-13T15:03:36Z OWNER Weird edge-case: adding an `order by` changes the order of the columns with respect to the information I am deriving about them. Without order by this gets it right: <img width="702" alt="fixtures__select_neighborhood__facet_cities_name__state_from_facetable_join_facet_cities_on_facetable_city_id___facet_cities_id_where_neighborhood_like_________text________" src="https://user-images.githubusercontent.com/9599/129377247-ec1f67fd-5fc5-46a2-92ef-629276446621.png"> With order by: <img width="708" alt="fixtures__select_neighborhood__facet_cities_name__state_from_facetable_join_facet_cities_on_facetable_city_id___facet_cities_id_where_neighborhood_like_________text________order_by_neighborhood" src="https://user-images.githubusercontent.com/9599/129377339-5b338432-6db8-43ac-9408-48a87c03e5e9.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898524057 https://github.com/simonw/datasette/issues/1293#issuecomment-898524057 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jmOZ simonw 9599 2021-08-13T15:06:37Z 2021-08-13T15:06:37Z OWNER Comparing the `explain` for the two versions of that query - one with the order by and one without: <img width="1031" alt="fixtures__explain_select_neighborhood__facet_cities_name__state_from_facetable_join_facet_cities_on_facetable_city_id___facet_cities_id_where_neighborhood_like_________text________order_by_neighborhood_and_fixtures__explain_select_neighborh" src="https://user-images.githubusercontent.com/9599/129377790-52af28ab-5110-470f-bb1b-a400455e6717.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898527525 https://github.com/simonw/datasette/issues/1293#issuecomment-898527525 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jnEl simonw 9599 2021-08-13T15:08:03Z 2021-08-13T15:08:03Z OWNER Am I going to need to look at the `ResultRow` and its columns but then wind back to that earlier `MakeRecord` and its columns? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898536181 https://github.com/simonw/datasette/issues/1293#issuecomment-898536181 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jpL1 simonw 9599 2021-08-13T15:17:20Z 2021-08-13T15:20:33Z OWNER Documentation for `MakeRecord`: https://www.sqlite.org/opcode.html#MakeRecord Running `explain` inside `sqlite3` provides extra comments and indentation which make it easier to understand: ``` sqlite> explain select neighborhood, facet_cities.name, state ...> from facetable ...> join facet_cities ...> on facetable.city_id = facet_cities.id ...> where neighborhood like '%bob%'; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 15 0 00 Start at 15 1 OpenRead 0 43 0 7 00 root=43 iDb=0; facetable 2 OpenRead 1 42 0 2 00 root=42 iDb=0; facet_cities 3 Rewind 0 14 0 00 4 Column 0 6 3 00 r[3]=facetable.neighborhood 5 Function0 1 2 1 like(2) 02 r[1]=func(r[2..3]) 6 IfNot 1 13 1 00 7 Column 0 5 4 00 r[4]=facetable.city_id 8 SeekRowid 1 13 4 00 intkey=r[4] 9 Column 0 6 5 00 r[5]=facetable.neighborhood 10 Column 1 1 6 00 r[6]=facet_cities.name 11 Column 0 4 7 00 r[7]=facetable.state 12 ResultRow 5 3 0 00 output=r[5..7] 13 Next 0 4 0 01 14 Halt 0 0 0 00 15 Transaction 0 0 35 0 01 usesStmtJournal=0 16 String8 0 2 0 %bob% 00 r[2]='%bob%' 17 Goto 0 1 0 00 ``` Compared with: ``` sqlite> explain select neighborhood, facet… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898540260 https://github.com/simonw/datasette/issues/1293#issuecomment-898540260 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jqLk simonw 9599 2021-08-13T15:23:28Z 2021-08-13T15:23:28Z OWNER SorterInsert: > Register P2 holds an SQL index key made using the MakeRecord instructions. This opcode writes that key into the sorter P1. Data for the entry is nil. SorterData: > Write into register P2 the current sorter data for sorter cursor P1. Then clear the column header cache on cursor P3. > > This opcode is normally use to move a record out of the sorter and into a register that is the source for a pseudo-table cursor created using OpenPseudo. That pseudo-table cursor is the one that is identified by parameter P3. Clearing the P3 column cache as part of this opcode saves us from having to issue a separate NullRow instruction to clear that cache. OpenPseudo: > Open a new cursor that points to a fake table that contains a single row of data. The content of that one row is the content of memory register P2. In other words, cursor P1 becomes an alias for the MEM_Blob content contained in register P2. > > A pseudo-table created by this opcode is used to hold a single row output from the sorter so that the row can be decomposed into individual columns using the Column opcode. The Column opcode is the only cursor opcode that works with a pseudo-table. > > P3 is the number of fields in the records that will be stored by the pseudo-table. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898541543 https://github.com/simonw/datasette/issues/1293#issuecomment-898541543 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jqfn simonw 9599 2021-08-13T15:25:26Z 2021-08-13T15:25:26Z OWNER But the debug output here seems to be saying what we want it to say: ``` 17 SorterSort 2 24 0 00 18 SorterData 2 10 3 00 r[10]=data 19 Column 3 2 8 00 r[8]=state 20 Column 3 1 7 00 r[7]=facet_cities.name 21 Column 3 0 6 00 r[6]=neighborhood 22 ResultRow 6 3 0 00 output=r[6..8] ``` We want to get back `neighborhood`, `facet_cities.name`, `state`. Why then are we seeing `[('facet_cities', 'name'), ('facetable', 'state'), (None, None)]`? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898541972 https://github.com/simonw/datasette/issues/1293#issuecomment-898541972 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jqmU simonw 9599 2021-08-13T15:26:06Z 2021-08-13T15:29:06Z OWNER ResultRow: > The registers P1 through P1+P2-1 contain a single row of results. This opcode causes the sqlite3_step() call to terminate with an SQLITE_ROW return code and it sets up the sqlite3_stmt structure to provide access to the r(P1)..r(P1+P2-1) values as the result row. Column: > Interpret the data that cursor P1 points to as a structure built using the MakeRecord instruction. (See the MakeRecord opcode for additional information about the format of the data.) Extract the P2-th column from this record. If there are less that (P2+1) values in the record, extract a NULL. > > The value extracted is stored in register P3. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898545815 https://github.com/simonw/datasette/issues/1293#issuecomment-898545815 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jriX simonw 9599 2021-08-13T15:31:53Z 2021-08-13T15:31:53Z OWNER My hunch here is that registers or columns are being reused in a way that makes my code break - my code is pretty dumb, there are places in it where maybe the first mention of a register wins instead of the last one? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898554427 https://github.com/simonw/datasette/issues/1293#issuecomment-898554427 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jto7 simonw 9599 2021-08-13T15:45:32Z 2021-08-13T15:45:32Z OWNER Some useful debug output: ``` table_rootpage_by_register={0: 43, 1: 42} names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} result_registers=[6, 7, 8] columns_by_column_register={3: ('facetable', 6), 4: ('facetable', 5), 6: ('facet_cities', 1), 7: ('facetable', 4), 5: ('facetable', 6)} all_column_names={('facet_cities', 0): 'id', ('facet_cities', 1): 'name', ('facetable', 0): 'pk', ('facetable', 1): 'created', ('facetable', 2): 'planet_int', ('facetable', 3): 'on_earth', ('facetable', 4): 'state', ('facetable', 5): 'city_id', ('facetable', 6): 'neighborhood', ('facetable', 7): 'tags', ('facetable', 8): 'complex_array', ('facetable', 9): 'distinct_some_null'} ``` The `result_registers` should each correspond to the correct entry in `columns_by_column_register` but they do not. Python code: ```python def columns_for_query(conn, sql, params=None): """ Given a SQLite connection ``conn`` and a SQL query ``sql``, returns a list of ``(table_name, column_name)`` pairs corresponding to the columns that would be returned by that SQL query. Each pair indicates the source table and column for the returned column, or ``(None, None)`` if no table and column could be derived (e.g. for "select 1") """ if sql.lower().strip().startswith("explain"): return [] opcodes = conn.execute("explain " + sql, params).fetchall() table_rootpage_by_register = { r["p1"]: r["p2"] for r in opcodes if r["opcode"] == "OpenRead" } print(f"{table_rootpage_by_register=}") names_and_types_by_rootpage = dict( [(r[0], (r[1], r[2])) for r in conn.execute( "select rootpage, name, type from sqlite_master where rootpage in ({})".format( ", ".join(map(str, table_rootpage_by_register.values())) ) )] ) print(f"{names_and_types_by_rootpage=}") columns_by_column_register = {} for opcode in opcodes: if opcode["opcode"] in ("Rowid", "Column"): … {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898554859 https://github.com/simonw/datasette/issues/1293#issuecomment-898554859 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jtvr simonw 9599 2021-08-13T15:46:18Z 2021-08-13T15:46:18Z OWNER So it looks like the bug is in the code that populates `columns_by_column_register`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898567974 https://github.com/simonw/datasette/issues/1293#issuecomment-898567974 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jw8m simonw 9599 2021-08-13T16:07:00Z 2021-08-13T16:07:00Z OWNER So this line: ``` 19 Column 3 2 8 00 r[8]=state ``` Means "Take column 2 of table 3 (the pseudo-table) and store it in register 8" {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898564705 https://github.com/simonw/datasette/issues/1293#issuecomment-898564705 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jwJh simonw 9599 2021-08-13T16:02:12Z 2021-08-13T16:04:06Z OWNER More debug output: ``` table_rootpage_by_register={0: 43, 1: 42} names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_id=0 cid=6 column_register=3 table_id=0 cid=5 column_register=4 table_id=1 cid=1 column_register=6 table_id=0 cid=4 column_register=7 table_id=0 cid=6 column_register=5 table_id=3 cid=2 column_register=8 table_id=3 cid=2 column_register=8 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=8 = (table='facetable', cid=2) table_id=3 cid=1 column_register=7 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=7 = (table='facetable', cid=1) table_id=3 cid=0 column_register=6 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=6 = (table='facetable', cid=0) result_registers=[6, 7, 8] columns_by_column_register={3: ('facetable', 6), 4: ('facetable', 5), 6: ('facet_cities', 1), 7: ('facetable', 4), 5: ('facetable', 6)} all_column_names={('facet_cities', 0): 'id', ('facet_cities', 1): 'name', ('facetable', 0): 'pk', ('facetable', 1): 'created', ('facetable', 2): 'planet_int', ('facetable', 3): 'on_earth', ('facetable', 4): 'state', ('facetable', 5): 'city_id', ('facetable', 6): 'neighborhood', ('facetable', 7): 'tags', ('faceta… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898572065 https://github.com/simonw/datasette/issues/1293#issuecomment-898572065 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jx8h simonw 9599 2021-08-13T16:13:16Z 2021-08-13T16:13:16Z OWNER Aha! That `MakeRecord` line says `r[5..7]` - and r5 = neighborhood, r6 = facet_cities.name, r7 = facetable.state So if the `MakeRecord` defines what goes into that pseudo-table column 2 of that pseudo-table would be `state` - which is what we want. This is really convoluted. I'm no longer confident I can get this to work in a sensible way, especially since I've not started exploring what complex nested tables with CTEs and sub-selects do yet. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898569319 https://github.com/simonw/datasette/issues/1293#issuecomment-898569319 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jxRn simonw 9599 2021-08-13T16:09:01Z 2021-08-13T16:10:48Z OWNER Need to figure out what column 2 of that pseudo-table is. I think the answer is here: ``` 4 Rewind 0 16 0 00 5 Column 0 6 3 00 r[3]=facetable.neighborhood 6 Function0 1 2 1 like(2) 02 r[1]=func(r[2..3]) 7 IfNot 1 15 1 00 8 Column 0 5 4 00 r[4]=facetable.city_id 9 SeekRowid 1 15 4 00 intkey=r[4] 10 Column 1 1 6 00 r[6]=facet_cities.name 11 Column 0 4 7 00 r[7]=facetable.state 12 Column 0 6 5 00 r[5]=facetable.neighborhood 13 MakeRecord 5 3 9 00 r[9]=mkrec(r[5..7]) 14 SorterInsert 2 9 5 3 00 key=r[9] 15 Next 0 5 0 01 16 OpenPseudo 3 10 5 00 5 columns in r[10] ``` I think the `OpenPseduo` line puts five columns in `r[10]` - and those five columns are the five from the previous block - maybe the five leading up to the `MakeRecord` call on line 13. In which case column 2 would be `facet_cities.name` - assuming we start counting from 0. But the debug code said "r[8]=state". {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898576097 https://github.com/simonw/datasette/issues/1293#issuecomment-898576097 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41jy7h simonw 9599 2021-08-13T16:19:57Z 2021-08-13T16:19:57Z OWNER I think I need to look out for `OpenPseudo` and, when that occurs, take a look at the most recent `SorterInsert` and use that to find the `MakeRecord` and then use the `MakeRecord` to figure out the columns that went into it. After all of that I'll be able to resolve that "table 3" reference. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898760020 https://github.com/simonw/datasette/issues/1293#issuecomment-898760020 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41kf1U simonw 9599 2021-08-13T23:00:28Z 2021-08-13T23:01:27Z OWNER New theory: this is all about `SorterOpen` and `SorterInsert`. Consider the following with extra annotations at the end of the lines after the `--`: ``` addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 25 0 00 Start at 25 1 SorterOpen 2 5 0 k(1,B) 00 -- New SORTER in r2 with 5 slots 2 OpenRead 0 43 0 7 00 root=43 iDb=0; facetable 3 OpenRead 1 42 0 2 00 root=42 iDb=0; facet_cities 4 Rewind 0 16 0 00 5 Column 0 6 3 00 r[3]=facetable.neighborhood 6 Function0 1 2 1 like(2) 02 r[1]=func(r[2..3]) 7 IfNot 1 15 1 00 8 Column 0 5 4 00 r[4]=facetable.city_id 9 SeekRowid 1 15 4 00 intkey=r[4] 10 Column 1 1 6 00 r[6]=facet_cities.name 11 Column 0 4 7 00 r[7]=facetable.state 12 Column 0 6 5 00 r[5]=facetable.neighborhood 13 MakeRecord 5 3 9 00 r[9]=mkrec(r[5..7]) 14 SorterInsert 2 9 5 3 00 key=r[9]-- WRITES record from r9 (line above) into sorter in r2 15 Next 0 5 0 01 16 OpenPseudo 3 10 5 00 5 columns in r[10] 17 SorterSort 2 24 0 00 -- runs the sort, not relevant to my goal 18 SorterData 2 10 3 00 r[10]=data -- "Write into register P2 (r10) the current sorter data for sorter cursor P1 (sorter 2)" 19 Column 3 2 8 … {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898760808 https://github.com/simonw/datasette/issues/1293#issuecomment-898760808 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41kgBo simonw 9599 2021-08-13T23:03:01Z 2021-08-13T23:03:01Z OWNER Another idea: strip out any `order by` clause to try and keep this simpler. I doubt that's going to cope with complex nested queries though. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898788262 https://github.com/simonw/datasette/issues/1293#issuecomment-898788262 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41kmum simonw 9599 2021-08-14T01:22:26Z 2021-08-14T01:51:08Z OWNER Tried a more complicated query: ```sql explain select pk, text1, text2, [name with . and spaces] from searchable where rowid in (select rowid from searchable_fts where searchable_fts match escape_fts(:search)) order by text1 desc limit 101 ``` Here's the explain: ``` sqlite> explain select pk, text1, text2, [name with . and spaces] from searchable where rowid in (select rowid from searchable_fts where searchable_fts match escape_fts(:search)) order by text1 desc limit 101 ...> ; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 41 0 00 Start at 41 1 OpenEphemeral 2 6 0 k(1,-B) 00 nColumn=6 2 Integer 101 1 0 00 r[1]=101; LIMIT counter 3 OpenRead 0 32 0 4 00 root=32 iDb=0; searchable 4 Integer 16 3 0 00 r[3]=16; return address 5 Once 0 16 0 00 6 OpenEphemeral 3 1 0 k(1,) 00 nColumn=1; Result of SELECT 1 7 VOpen 1 0 0 vtab:7FCBCA72BE80 00 8 Function0 1 7 6 unknown(-1) 01 r[6]=func(r[7]) 9 Integer 5 4 0 00 r[4]=5 10 Integer 1 5 0 00 r[5]=1 11 VFilter 1 16 4 00 iplan=r[4] zplan='' 12 Rowid 1 8 0 00 r[8]=rowid 13 MakeRecord 8 1 9 C 00 r[9]=mkrec(r[8]) 14 IdxInsert 3 9 8 1 00 key=r[9] 15 VNext 1 12 0 00 16 Return 3 0 0 00 17 Rewind 3 33 0 00 18 Column 3… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898913554 https://github.com/simonw/datasette/issues/1293#issuecomment-898913554 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41lFUS simonw 9599 2021-08-14T16:13:40Z 2021-08-14T16:13:40Z OWNER I think I need to care about the following: - `ResultRow` and `Column` for the final result - `OpenRead` for opening tables - `OpenEphemeral` then `MakeRecord` and `IdxInsert` for writing records into ephemeral tables `Column` may reference either a table (from `OpenRead`) or an ephemeral table (from `OpenEphemeral`). That *might* be enough. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898913629 https://github.com/simonw/datasette/issues/1293#issuecomment-898913629 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41lFVd simonw 9599 2021-08-14T16:14:12Z 2021-08-14T16:14:12Z OWNER I would feel a lot more comfortable about all of this if I had a robust mechanism for running the Datasette test suite against multiple versions of SQLite itself. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898936068 https://github.com/simonw/datasette/issues/1293#issuecomment-898936068 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41lK0E simonw 9599 2021-08-14T17:44:54Z 2021-08-14T17:44:54Z OWNER Another interesting query to consider: https://latest.datasette.io/fixtures?sql=explain+select+*+from++pragma_table_info%28+%27123_starts_with_digits%27%29 That one shows `VColumn` instead of `Column`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898933865 https://github.com/simonw/datasette/issues/1293#issuecomment-898933865 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41lKRp simonw 9599 2021-08-14T17:27:16Z 2021-08-14T17:28:29Z OWNER Maybe I split this out into a separate Python library that gets tested against *every* SQLite release I can possibly try it against, and then bakes out the supported release versions into the library code itself? Datasette could depend on that library. The library could be released independently of Datasette any time a new SQLite version comes out. I could even run a separate git scraper repo that checks for new SQLite releases and submits PRs against the library when a new release comes out. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
898961535 https://github.com/simonw/datasette/issues/1293#issuecomment-898961535 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41lRB_ simonw 9599 2021-08-14T21:37:24Z 2021-08-14T21:37:24Z OWNER Did some more research into building SQLite custom versions via `pysqlite3` - here's what I figured out for macOS (which should hopefully work for Linux too): https://til.simonwillison.net/sqlite/build-specific-sqlite-pysqlite-macos {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
899915829 https://github.com/simonw/datasette/issues/1293#issuecomment-899915829 https://api.github.com/repos/simonw/datasette/issues/1293 IC_kwDOBm6k_c41o6A1 simonw 9599 2021-08-17T01:02:35Z 2021-08-17T01:02:35Z OWNER New approach: this time I'm building a simplified executor for the bytecode operations themselves. ```python def execute_operations(operations, max_iterations = 100, trace=None): trace = trace or (lambda *args: None) registers: Dict[int, Any] = {} cursors: Dict[int, Tuple[str, Dict]] = {} instruction_pointer = 0 iterations = 0 result_row = None while True: iterations += 1 if iterations > max_iterations: break operation = operations[instruction_pointer] trace(instruction_pointer, dict(operation)) opcode = operation["opcode"] if opcode == "Init": if operation["p2"] != 0: instruction_pointer = operation["p2"] continue else: instruction_pointer += 1 continue elif opcode == "Goto": instruction_pointer = operation["p2"] continue elif opcode == "Halt": break elif opcode == "OpenRead": cursors[operation["p1"]] = ("database_table", { "rootpage": operation["p2"], "connection": operation["p3"], }) elif opcode == "OpenEphemeral": cursors[operation["p1"]] = ("ephemeral", { "num_columns": operation["p2"], "index_keys": [], }) elif opcode == "MakeRecord": registers[operation["p3"]] = ("MakeRecord", { "registers": list(range(operation["p1"] + operation["p2"])) }) elif opcode == "IdxInsert": record = registers[operation["p2"]] cursors[operation["p1"]][1]["index_keys"].append(record) elif opcode == "Rowid": registers[operation["p2"]] = ("rowid", { "table": operation["p1"] }) elif opcode == "Sequence": registers[operation["p2"]] = ("sequence", { "next_from_cursor": operat… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show column metadata plus links for foreign keys on arbitrary query results 849978964  
899744109 https://github.com/simonw/datasette/issues/1423#issuecomment-899744109 https://api.github.com/repos/simonw/datasette/issues/1423 IC_kwDOBm6k_c41oQFt simonw 9599 2021-08-16T18:58:29Z 2021-08-16T18:58:29Z OWNER I didn't bother with the tooltip, just the visible display if `?_facet_size=max`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show count of facet values if ?_facet_size=max 962391325  
899749881 https://github.com/simonw/datasette/issues/1423#issuecomment-899749881 https://api.github.com/repos/simonw/datasette/issues/1423 IC_kwDOBm6k_c41oRf5 simonw 9599 2021-08-16T19:07:02Z 2021-08-16T19:07:02Z OWNER Demo: https://latest.datasette.io/fixtures/compound_three_primary_keys?_facet=content&_facet_size=max&_facet=pk1&_facet=pk2 <img width="686" alt="fixtures__compound_three_primary_keys__1_001_rows" src="https://user-images.githubusercontent.com/9599/129616596-cc51b668-7cb8-482f-9e20-e0d8ca4b71be.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Show count of facet values if ?_facet_size=max 962391325  
900681413 https://github.com/simonw/datasette/issues/1438#issuecomment-900681413 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41r07F simonw 9599 2021-08-17T22:47:44Z 2021-08-17T22:47:44Z OWNER I deployed another copy of `fixtures.db` on Vercel at https://til.simonwillison.net/fixtures so I can compare it with `fixtures.db` on Cloud Run at https://latest.datasette.io/fixtures {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900690998 https://github.com/simonw/datasette/issues/1438#issuecomment-900690998 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41r3Q2 simonw 9599 2021-08-17T23:11:16Z 2021-08-17T23:12:25Z OWNER I have completely failed to replicate this initial bug - but it's still there on the `thesession.vercel.app` deployment (even though my own deployments to Vercel do not exhibit it). Here's a one-liner to replicate it against that deployment: `curl -s 'https://thesession.vercel.app/thesession?sql=select+*+from+tunes+where+name+like+%22%25wise+maid%25%22' | rg '.csv'` Whit outputs this: `<p class="export-links">This data as <a href="/thesession.json?sql=select * from tunes where name like &#34;%wise maid%&#34;">json</a>, <a href="/thesession.csv?sql=select * from tunes where name like &#34;%wise maid%&#34;&amp;_size=max">CSV</a></p>` It looks like, rather than being URL-encoded, the original query string is somehow making it through to Jinja and then being auto-escaped there. The weird thing is that the equivalent query executed against my `til.simonwillison.net` Vercel instance does this: `curl -s 'https://til.simonwillison.net/fixtures?sql=select+*+from+searchable+where+text1+like+%22%25a%25%22' | rg '.csv'` `<p class="export-links">This data as <a href="/fixtures.json?sql=select%20*%20from%20searchable%20where%20text1%20like%20%22%25a%25%22">json</a>, <a href="/fixtures.csv?sql=select%20*%20from%20searchable%20where%20text1%20like%20%22%25a%25%22&amp;_size=max">CSV</a></p>` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900699670 https://github.com/simonw/datasette/issues/1439#issuecomment-900699670 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r5YW simonw 9599 2021-08-17T23:34:23Z 2021-08-17T23:34:23Z OWNER The challenge comes down to telling the difference between the following: - `/db/table` - an HTML table page - `/db/table.csv` - the CSV version of `/db/table` - `/db/table.csv` - no this one is actually a database table called `table.csv` - `/db/table.csv.csv` - the CSV version of `/db/table.csv` - `/db/table.csv.csv.csv` and so on... {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900705226 https://github.com/simonw/datasette/issues/1439#issuecomment-900705226 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r6vK simonw 9599 2021-08-17T23:50:32Z 2021-08-17T23:50:47Z OWNER An alternative solution would be to use some form of escaping for the characters that form the name of the table. The obvious way to do this would be URL-encoding - but it doesn't hold for `.` characters. The hex for that is `%2E` but watch what happens with that in a URL: ``` # Against Cloud Run: curl -s 'https://datasette.io/-/asgi-scope/foo/bar%2Fbaz%2E' | rg path 'path': '/-/asgi-scope/foo/bar/baz.', 'raw_path': b'/-/asgi-scope/foo/bar%2Fbaz.', 'root_path': '', # Against Vercel: curl -s 'https://til.simonwillison.net/-/asgi-scope/foo/bar%2Fbaz%2E' | rg path 'path': '/-/asgi-scope/foo/bar%2Fbaz%2E', 'raw_path': b'/-/asgi-scope/foo/bar%2Fbaz%2E', 'root_path': '', ``` Surprisingly in this case Vercel DOES keep it intact, but Cloud Run does not. It's still no good though: I need a solution that works on Vercel, Cloud Run and every other potential hosting provider too. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900709703 https://github.com/simonw/datasette/issues/1439#issuecomment-900709703 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r71H simonw 9599 2021-08-18T00:03:09Z 2021-08-18T00:03:09Z OWNER But... what if I invent my own escaping scheme? I actually did this once before, in https://github.com/simonw/datasette/commit/9fdb47ca952b93b7b60adddb965ea6642b1ff523 - while I was working on porting Datasette to ASGI in https://github.com/simonw/datasette/issues/272#issuecomment-494192779 because ASGI didn't yet have the `raw_path` mechanism. I could bring that back - it looked like this: ``` "table/and/slashes" => "tableU+002FandU+002Fslashes" "~table" => "U+007Etable" "+bobcats!" => "U+002Bbobcats!" "U+007Etable" => "UU+002B007Etable" ``` But I didn't particularly like it - it was quite verbose. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900711967 https://github.com/simonw/datasette/issues/1439#issuecomment-900711967 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r8Yf simonw 9599 2021-08-18T00:08:09Z 2021-08-18T00:08:09Z OWNER Here's an alternative I just made up which I'm calling "dot dash" encoding: ```python def dot_dash_encode(s): return s.replace("-", "--").replace(".", "-.") def dot_dash_decode(s): return s.replace("-.", ".").replace("--", "-") ``` And some examples: ```python for example in ( "hello", "hello.csv", "hello-and-so-on.csv", "hello-.csv", "hello--and--so--on-.csv", "hello.csv.", "hello.csv.-", "hello.csv.--", ): print(example) print(dot_dash_encode(example)) print(example == dot_dash_decode(dot_dash_encode(example))) print() ``` Outputs: ``` hello hello True hello.csv hello-.csv True hello-and-so-on.csv hello--and--so--on-.csv True hello-.csv hello---.csv True hello--and--so--on-.csv hello----and----so----on---.csv True hello.csv. hello-.csv-. True hello.csv.- hello-.csv-.-- True hello.csv.-- hello-.csv-.---- True ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900712981 https://github.com/simonw/datasette/issues/1439#issuecomment-900712981 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r8oV simonw 9599 2021-08-18T00:09:59Z 2021-08-18T00:12:32Z OWNER So given the original examples, a table called `table.csv` would have the following URLs: - `/db/table-.csv` - the HTML version - `/db/table-.csv.csv` - the CSV version - `/db/table-.csv.json` - the JSON version And if for some horific reason you had a table with the name `/db/table-.csv.csv` (so `/db/` was the first part of the actual table name in SQLite) the URLs would look like this: - `/db/%2Fdb%2Ftable---.csv-.csv` - the HTML version - `/db/%2Fdb%2Ftable---.csv-.csv.csv` - the CSV version - `/db/%2Fdb%2Ftable---.csv-.csv.json` - the JSON version {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900714630 https://github.com/simonw/datasette/issues/1439#issuecomment-900714630 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r9CG simonw 9599 2021-08-18T00:13:33Z 2021-08-18T00:13:33Z OWNER The documentation should definitely cover how table names become URLs, in case any third party code needs to be able to calculate this themselves. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900715375 https://github.com/simonw/datasette/issues/1439#issuecomment-900715375 https://api.github.com/repos/simonw/datasette/issues/1439 IC_kwDOBm6k_c41r9Nv simonw 9599 2021-08-18T00:15:28Z 2021-08-18T00:15:28Z OWNER Maybe I should use `-/` to encode forward slashes too, to defend against any ASGI servers that might not implement `raw_path` correctly. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Rethink how .ext formats (v.s. ?_format=) works before 1.0 973139047  
900500824 https://github.com/simonw/datasette/issues/1438#issuecomment-900500824 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41rI1Y simonw 9599 2021-08-17T17:38:16Z 2021-08-17T17:38:16Z OWNER Relevant template code: https://github.com/simonw/datasette/blob/adb5b70de5cec3c3dd37184defe606a082c232cf/datasette/templates/query.html#L71 `renderers` comes from here: https://github.com/simonw/datasette/blob/2883098770fc66e50183b2b231edbde20848d4d6/datasette/views/base.py#L593-L608 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900502364 https://github.com/simonw/datasette/issues/1438#issuecomment-900502364 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41rJNc simonw 9599 2021-08-17T17:40:41Z 2021-08-17T17:40:41Z OWNER Bug is likely in `path_with_format` itself: https://github.com/simonw/datasette/blob/adb5b70de5cec3c3dd37184defe606a082c232cf/datasette/utils/__init__.py#L710-L729 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900513267 https://github.com/simonw/datasette/issues/1438#issuecomment-900513267 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41rL3z simonw 9599 2021-08-17T17:57:05Z 2021-08-17T17:57:05Z OWNER I'm having trouble replicating this bug outside of Vercel. Against Cloud Run: view-source:https://latest.datasette.io/fixtures?sql=select+*+from+searchable+where+text1+like+%22%25cat%25%22 The HTML here is: ```html <p class="export-links">This data as <a href="/fixtures.json?sql=select+*+from+searchable+where+text1+like+%22%25cat%25%22">json</a>, ... <a href="/fixtures.csv?sql=select+*+from+searchable+where+text1+like+%22%25cat%25%22&amp;_size=max">CSV</a> </p> ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900516826 https://github.com/simonw/datasette/issues/1438#issuecomment-900516826 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41rMva simonw 9599 2021-08-17T18:02:27Z 2021-08-17T18:02:27Z OWNER The key difference I can spot between Vercel and Cloud Run is that `+` in a query string gets converted to `%20` by Vercel before it gets to my app, but does not for Cloud Run: ``` # Vercel ~ % curl -s 'https://til.simonwillison.net/-/asgi-scope?sql=select+*+from+tunes+where+name+like+%22%25wise+maid%25%22%0D%0A' | rg 'query_string' -C 2 'method': 'GET', 'path': '/-/asgi-scope', 'query_string': b'sql=select%20*%20from%20tunes%20where%20name%20like%20%22%25' b'wise%20maid%25%22%0D%0A', 'raw_path': b'/-/asgi-scope', # Cloud Run ~ % curl -s 'https://latest-with-plugins.datasette.io/-/asgi-scope?sql=select+*+from+tunes+where+name+like+%22%25wise+maid%25%22%0D%0A' | rg 'query_string' -C 2 'method': 'GET', 'path': '/-/asgi-scope', 'query_string': b'sql=select+*+from+tunes+where+name+like+%22%25wise+maid%25%2' b'2%0D%0A', 'raw_path': b'/-/asgi-scope', ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  
900518343 https://github.com/simonw/datasette/issues/1438#issuecomment-900518343 https://api.github.com/repos/simonw/datasette/issues/1438 IC_kwDOBm6k_c41rNHH simonw 9599 2021-08-17T18:04:42Z 2021-08-17T18:04:42Z OWNER Here's how `request.query_string` works: https://github.com/simonw/datasette/blob/adb5b70de5cec3c3dd37184defe606a082c232cf/datasette/utils/asgi.py#L86-L88 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Query page .csv and .json links are not correctly URL-encoded on Vercel under unknown specific conditions 972918533  

Next page

Advanced export

JSON shape: default, array, newline-delimited, object

CSV options: 

CREATE TABLE [issue_comments] (
   [html_url] TEXT,
   [issue_url] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [user] INTEGER REFERENCES [users]([id]),
   [created_at] TEXT,
   [updated_at] TEXT,
   [author_association] TEXT,
   [body] TEXT,
   [reactions] TEXT,
   [issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
                ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
                ON [issue_comments] ([user]);