home / github

Menu
  • GraphQL API

issue_comments

Table actions
  • GraphQL API for issue_comments

32 rows where "updated_at" is on date 2021-08-13

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: issue_url, author_association, issue, created_at (date)

updated_at (date) 1 ✖

  • 2021-08-13 · 32 ✖
id ▼ html_url issue_url node_id user created_at updated_at author_association body reactions issue performed_via_github_app
813134386 https://github.com/simonw/datasette/issues/1293#issuecomment-813134386 https://api.github.com/repos/simonw/datasette/issues/1293 MDEyOklzc3VlQ29tbWVudDgxMzEzNDM4Ng== simonw 9599 2021-04-05T01:20:28Z 2021-08-13T00:42:30Z OWNER ... that output might also provide a better way to extract variables than the current mechanism using a regular expression, by looking for the `Variable` opcodes. [UPDATE: it did indeed do that, see #1421] {"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  
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  
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  
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  
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  
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  
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  
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  
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  
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  

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]);
Powered by Datasette · Queries took 621.088ms · About: simonw/datasette-graphql