issue_comments

Table actions

3 rows where issue = 539590148

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: user, author_association, created_at (date), updated_at (date)

id ▼ html_url issue_url node_id user created_at updated_at author_association body reactions issue performed_via_github_app
568268746 https://github.com/simonw/datasette/issues/651#issuecomment-568268746 https://api.github.com/repos/simonw/datasette/issues/651 MDEyOklzc3VlQ29tbWVudDU2ODI2ODc0Ng== simonw 9599 2019-12-22T14:37:37Z 2019-12-22T14:37:37Z OWNER I've not yet been able to figure out what the escaping rule are for FTS5 queries. If we figure out how those work maybe we can bundle them as a custom function? select ... where docs_fts match fts_escape(:search) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} fts5 syntax error when using punctuation 539590148  
568269476 https://github.com/simonw/datasette/issues/651#issuecomment-568269476 https://api.github.com/repos/simonw/datasette/issues/651 MDEyOklzc3VlQ29tbWVudDU2ODI2OTQ3Ng== simonw 9599 2019-12-22T14:46:37Z 2019-12-22T14:47:03Z OWNER https://stackoverflow.com/a/43756146 says that an escaping mechanism that works is this one: select * from blah where term match '"bacon" "and" "eggs"' So split on whitespace and then encapsulate each search term in double quotes. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} fts5 syntax error when using punctuation 539590148  
579675357 https://github.com/simonw/datasette/issues/651#issuecomment-579675357 https://api.github.com/repos/simonw/datasette/issues/651 MDEyOklzc3VlQ29tbWVudDU3OTY3NTM1Nw== clausjuhl 2181410 2020-01-29T09:45:00Z 2021-07-14T19:26:06Z NONE Hi Simon Thank you for adding the escape_function, but it does not work on my datasette-installation (0.33). I've added the following file to my datasette-dir: `/plugins/sql_functions.py`: ```python from datasette import hookimpl def escape_fts_query(query): bits = query.split() return ' '.join('"{}"'.format(bit.replace('"', '')) for bit in bits) @hookimpl def prepare_connection(conn): conn.create_function("escape_fts_query", 1, escape_fts_query)` ``` It has no effect on the standard queries to the tables though, as they still produce errors when including any characters like '-', '/', '+' or '?' Does the function only work when using costum queries, where I can include the escape_fts-function explicitly in the sql-query? PS. I'm calling datasette with --plugins=plugins, and my other plugins work just fine. PPS. The fts5 virtual table is created with 'sqlite3' like so: `CREATE VIRTUAL TABLE "cases_fts" USING FTS5( title, subtitle, resume, suggestion, presentation, detail = full, content_rowid = 'id', content = 'cases', tokenize='unicode61', 'remove_diacritics 2', 'tokenchars "-_"' );` Thanks! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} fts5 syntax error when using punctuation 539590148  

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