issue_comments

Table actions

31 rows where "updated_at" is on date 2020-09-21

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: issue_url, user, author_association, reactions, issue

updated_at (date) 1 ✖

  • 2020-09-21 · 31 ✖

created_at (date) 1 ✖

id ▼ html_url issue_url node_id user created_at updated_at author_association body reactions issue performed_via_github_app
695855646 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695855646 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg1NTY0Ng== simonw 9599 2020-09-21T00:16:11Z 2020-09-21T00:16:11Z MEMBER Should I do this with offset/limit or should I do proper keyset pagination? I think keyset because then it will work well for the full search interface with no filters or search string. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695855723 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695855723 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg1NTcyMw== simonw 9599 2020-09-21T00:16:52Z 2020-09-21T00:17:53Z MEMBER It feels a bit weird to implement keyset pagination against results sorted by `rank` because the ranks could change substantially if the search index gets updated while the user is paginating. I may just ignore that though. If you want reliable pagination you can get it by sorting by date. Maybe it doesn't even make sense to offer pagination if you sort by relevance? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695856398 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695856398 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg1NjM5OA== simonw 9599 2020-09-21T00:22:20Z 2020-09-21T00:22:20Z MEMBER I'm going to try for keyset pagination sorted by relevance just as a learning exercise. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695856967 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695856967 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg1Njk2Nw== simonw 9599 2020-09-21T00:26:59Z 2020-09-21T00:26:59Z MEMBER It's a shame Datasette doesn't currently have an easy way to implement sorted-by-rank keyset-paginated using a TableView or QueryView. I'll have to do this using the custom SQL query constructed in the plugin: https://github.com/dogsheep/dogsheep-beta/blob/bed9df2b3ef68189e2e445427721a28f4e9b4887/dogsheep_beta/__init__.py#L8-L43 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695875274 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695875274 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg3NTI3NA== simonw 9599 2020-09-21T02:28:58Z 2020-09-21T02:28:58Z MEMBER Datasette's implementation is complex because it has to support compound primary keys: https://github.com/simonw/datasette/blob/a258339a935d8d29a95940ef1db01e98bb85ae63/datasette/utils/__init__.py#L88-L114 - but that's not something that's needed for dogsheep-beta. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695877627 https://github.com/dogsheep/dogsheep-beta/issues/16#issuecomment-695877627 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/16 MDEyOklzc3VlQ29tbWVudDY5NTg3NzYyNw== simonw 9599 2020-09-21T02:42:29Z 2020-09-21T02:42:29Z MEMBER Fun twist: assuming `timestamp` is always stored as UTC, I need the interface to be timezone aware so I can see e.g. everything from 4th July 2020 in the San Francisco timezone definition of 4th July 2020. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Timeline view 694493566  
695879237 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695879237 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg3OTIzNw== simonw 9599 2020-09-21T02:53:29Z 2020-09-21T02:53:29Z MEMBER If previous page ended at `2018-02-11T16:32:53+00:00`: ```sql select search_index.rowid, search_index.type, search_index.key, search_index.title, search_index.category, search_index.timestamp, search_index.search_1 from search_index where date("timestamp") = '2018-02-11' and timestamp < '2018-02-11T16:32:53+00:00' order by search_index.timestamp desc, rowid limit 41 ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695879531 https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695879531 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 MDEyOklzc3VlQ29tbWVudDY5NTg3OTUzMQ== simonw 9599 2020-09-21T02:55:28Z 2020-09-21T02:55:54Z MEMBER Actually for the tie-breaker it should be something like https://latest.datasette.io/fixtures?sql=select+pk%2C+created%2C+planet_int%2C+on_earth%2C+state%2C+city_id%2C+neighborhood%2C+tags%2C+complex_array%2C+distinct_some_null+from+facetable+where+%28created+%3E+%3Ap1+or+%28created+%3D+%3Ap1+and+%28%28pk+%3E+%3Ap0%29%29%29%29+order+by+created%2C+pk+limit+11&p0=10&p1=2019-01-16+08%3A00%3A00 ```sql where ( created > :p1 or ( created = :p1 and ((pk > :p0)) ) ) ``` But with `rowid` and `timestamp` in place of `pk` and `created`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Pagination 705215230  
695895960 https://github.com/simonw/datasette/issues/970#issuecomment-695895960 https://api.github.com/repos/simonw/datasette/issues/970 MDEyOklzc3VlQ29tbWVudDY5NTg5NTk2MA== simonw 9599 2020-09-21T04:36:45Z 2020-09-21T04:36:45Z OWNER I like this. It could work with the `--root` option too and automatically sign you in as the root user. {"total_count": 1, "+1": 0, "-1": 0, "laugh": 1, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} request an "-o" option on "datasette server" to open the default browser at the running url 705108492  
695896557 https://github.com/simonw/datasette/issues/970#issuecomment-695896557 https://api.github.com/repos/simonw/datasette/issues/970 MDEyOklzc3VlQ29tbWVudDY5NTg5NjU1Nw== simonw 9599 2020-09-21T04:40:12Z 2020-09-21T04:40:12Z OWNER The Python standard library has a module for this: https://docs.python.org/3/library/webbrowser.html {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} request an "-o" option on "datasette server" to open the default browser at the running url 705108492  
696163452 https://github.com/simonw/datasette/issues/670#issuecomment-696163452 https://api.github.com/repos/simonw/datasette/issues/670 MDEyOklzc3VlQ29tbWVudDY5NjE2MzQ1Mg== snth 652285 2020-09-21T14:46:10Z 2020-09-21T14:46:10Z NONE I'm currently using PostgREST to serve OpenAPI APIs off Postgresql databases. I would like to try out datasette once this becomes available on Postgres. {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Prototoype for Datasette on PostgreSQL 564833696  
696297601 https://github.com/simonw/datasette/issues/971#issuecomment-696297601 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjI5NzYwMQ== simonw 9599 2020-09-21T18:41:07Z 2020-09-21T18:41:07Z OWNER How to detect it? Looks like it's visible in SQLite compile time options: https://latest.datasette.io/-/versions ``` "compile_options": [ "COMPILER=gcc-8.3.0", "ENABLE_COLUMN_METADATA", "ENABLE_DBSTAT_VTAB", ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696297930 https://github.com/simonw/datasette/issues/971#issuecomment-696297930 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjI5NzkzMA== simonw 9599 2020-09-21T18:41:47Z 2020-09-21T18:41:47Z OWNER https://www.sqlite.org/dbstat.html > The DBSTAT virtual table is an eponymous virtual table, meaning that is not necessary to run CREATE VIRTUAL TABLE to create an instance of the dbstat virtual table before using it. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696298614 https://github.com/simonw/datasette/issues/971#issuecomment-696298614 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjI5ODYxNA== simonw 9599 2020-09-21T18:43:07Z 2020-09-21T18:43:07Z OWNER Or, do this: ```sql SELECT 1 FROM dbstat limit 1; ``` And see if it returns a "table does not exist" error. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696302020 https://github.com/simonw/datasette/issues/971#issuecomment-696302020 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjMwMjAyMA== simonw 9599 2020-09-21T18:49:09Z 2020-09-21T18:49:09Z OWNER ... made harder to work on because I apparently don't have the `DBSTAT_VTAB` module on macOS. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696302868 https://github.com/simonw/datasette/issues/971#issuecomment-696302868 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjMwMjg2OA== simonw 9599 2020-09-21T18:50:40Z 2020-09-21T18:50:40Z OWNER Easiest way to get this may be to run `create view dbstat_view as select * from dbstat` on databases that support it. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696304108 https://github.com/simonw/datasette/issues/971#issuecomment-696304108 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjMwNDEwOA== simonw 9599 2020-09-21T18:52:50Z 2020-09-21T18:52:50Z OWNER Looks like the `pysqlite3-binary` package doesn't support `dbstat` either. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696307922 https://github.com/simonw/datasette/issues/971#issuecomment-696307922 https://api.github.com/repos/simonw/datasette/issues/971 MDEyOklzc3VlQ29tbWVudDY5NjMwNzkyMg== simonw 9599 2020-09-21T18:59:52Z 2020-09-21T19:00:02Z OWNER Given `dbstat` isn't as widely available as I thought I'm going to let people who want to use `dbstat` run their own `select * from dbstat` queries rather than bake support directly into Datasette. The experience of exploring `dbstat` will improve if I land support for running facets against arbitrary custom SQL queries, which is half-done in that facets now execute against wrapped subqueries as-of ea66c45df96479ef66a89caa71fff1a97a862646 https://github.com/simonw/datasette/blob/ea66c45df96479ef66a89caa71fff1a97a862646/datasette/facets.py#L192-L200 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support the dbstat table 705827457  
696308847 https://github.com/simonw/datasette/issues/972#issuecomment-696308847 https://api.github.com/repos/simonw/datasette/issues/972 MDEyOklzc3VlQ29tbWVudDY5NjMwODg0Nw== simonw 9599 2020-09-21T19:01:25Z 2020-09-21T19:01:25Z OWNER I did a bunch of initial work for this in #427. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support faceting against arbitrary SQL queries 705840673  
696421240 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696421240 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQyMTI0MA== simonw 9599 2020-09-21T22:53:48Z 2020-09-21T22:53:48Z OWNER I've decided to call this `table.transform()` - I was over-thinking whether people would remember that `.transform()` actually transforms the table, but that's what documentation is for. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696423066 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696423066 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQyMzA2Ng== simonw 9599 2020-09-21T22:59:01Z 2020-09-21T22:59:01Z OWNER I'm rethinking the API design now. Maybe it could look like this: To change the type of the `author_id` column from `text` to `int`: ```python books.transform({"author_id": int}) ``` This would leave the existing columns alone, but would change the type of this column. To rename `author_id` to `author_identifier`: ```python books.transform(rename={"author_id": "author_identifier"}) ``` To drop a column: ```python books.transform(drop=["author_id"]) ``` Since the parameters all operate on columns they don't need to be called `drop_column` and `rename_column`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696423138 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696423138 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQyMzEzOA== simonw 9599 2020-09-21T22:59:17Z 2020-09-21T23:01:06Z OWNER I'm going to sketch out a prototype of this new API design in that branch. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696430843 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696430843 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzMDg0Mw== simonw 9599 2020-09-21T23:21:00Z 2020-09-21T23:21:00Z OWNER For FTS tables associated with the table that is being transformed, should I automatically drop the old FTS table and recreate it against the new one or will it just magically continue to work after the table is renamed? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696431058 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696431058 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzMTA1OA== simonw 9599 2020-09-21T23:21:37Z 2020-09-21T23:21:37Z OWNER I may need to do something special for `rowid` tables to ensure that the `rowid` values in the transformed table match those from the old table. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696432690 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696432690 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzMjY5MA== simonw 9599 2020-09-21T23:26:32Z 2020-09-21T23:27:38Z OWNER To expand on what that first argument - the `columns` argument - does. Say you have a table like this: ``` id integer name text age text ``` Any columns omitted from the `columns=` argument are left alone - they have to be explicitly dropped using `drop=` if you want to drop them. Any new columns are added (at the end of the table): ``` table.tranform({"size": float}) ``` Any columns that have their type changed will have their type changed: ``` table.tranform({"age": int}) ``` Should I also re-order columns if the order doesn't match? I think so. Open question as to what happens to columns that aren't mentioned at all in the dictionary though - what order should they go in? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696433542 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696433542 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzMzU0Mg== simonw 9599 2020-09-21T23:28:58Z 2020-09-21T23:28:58Z OWNER If you want to both change the type of a column AND rename it in the same operation, how would you do that? I think like this: ```python table.transform({"age": int}, rename={"age": "dog_age"}) ``` So any rename logic is applied at the end, after the type transformation or re-ordering logic. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696433778 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696433778 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzMzc3OA== simonw 9599 2020-09-21T23:29:39Z 2020-09-21T23:29:39Z OWNER The `columns=` argument is optional - so you can do just a rename operation like so: ``` table.transform(rename={"age": "dog_age"}) ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696434097 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696434097 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzNDA5Nw== simonw 9599 2020-09-21T23:30:40Z 2020-09-21T23:30:40Z OWNER Since I have a `column_order=None` argument already, maybe I can ignore the order of the columns in that first argument and use that instead? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696434237 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696434237 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzNDIzNw== simonw 9599 2020-09-21T23:31:07Z 2020-09-21T23:31:57Z OWNER Does it make sense to support the `pk=` argument for changing the primary key? If the user requests a primary key that doesn't make sense I think an integrity error will be raised when the SQL is being executed, which should hopefully cancel the transaction and raise an error. Need to check that this is what happens. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696434638 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696434638 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzNDYzOA== simonw 9599 2020-09-21T23:32:26Z 2020-09-21T23:32:26Z OWNER A test that confirms that this mechanism can turn a `rowid` into a non-rowid table would be good too. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  
696435194 https://github.com/simonw/sqlite-utils/issues/114#issuecomment-696435194 https://api.github.com/repos/simonw/sqlite-utils/issues/114 MDEyOklzc3VlQ29tbWVudDY5NjQzNTE5NA== simonw 9599 2020-09-21T23:34:14Z 2020-09-21T23:35:00Z OWNER I think the fiddliest part of the implementation here is code that takes the existing `columns_dict` of the table and the incoming `columns=` and `drop=` and `rename=` parameters and produces the columns dictionary for the new table, ready to be fed to `.create_table()`. This logic probably also needs to return a structure that can be used to build the `INSERT INTO ... SELECT ... FROM` query. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} table.transform() method for advanced alter table 621989740  

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]);