home / github / issues

Menu
  • Search all tables
  • GraphQL API

issues: 275164558

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
275164558 MDU6SXNzdWUyNzUxNjQ1NTg= 129 Hide FTS-created tables by default on the database index page 9599 closed 0     2 2017-11-19T14:50:42Z 2017-11-22T20:22:02Z 2017-11-22T20:19:04Z OWNER  

SQLite databases that use FTS include a number of automatically generated tables, e.g.:

https://sf-trees-search.now.sh/sf-trees-search-a899b92

Of these, only the Street_Tree_List table is actually relevant to the user.

We can detect which tables are FTS tables by first finding the virtual tables:

sqlite> .headers on
sqlite> select * from sqlite_master where rootpage = 0;
type|name|tbl_name|rootpage|sql
table|Search|Search|0|CREATE VIRTUAL TABLE "Street_Tree_List_fts" USING FTS4 ("qAddress", "qCaretaker", "qSpecies")

Then parsing the above to figure out which ones are USING FTS? - then assume that any table which starts with that Street_Tree_List_fts prefix was created to support search:

sqlite> select * from sqlite_master where type='table' and tbl_name like 'Street_Tree_List_fts%';
type|name|tbl_name|rootpage|sql
table|Search_content|Search_content|10355|CREATE TABLE 'Street_Tree_List_fts_content'(docid INTEGER PRIMARY KEY, 'c0qAddress', 'c1qCaretaker', 'c2qSpecies')
table|Search_segments|Search_segments|10356|CREATE TABLE 'Street_Tree_List_fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB)
table|Search_segdir|Search_segdir|10357|CREATE TABLE 'Street_Tree_List_fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))
table|Search_docsize|Search_docsize|10359|CREATE TABLE 'Street_Tree_List_fts_docsize'(docid INTEGER PRIMARY KEY, size BLOB)
table|Search_stat|Search_stat|10360|CREATE TABLE 'Street_Tree_List_fts_stat'(id INTEGER PRIMARY KEY, value BLOB)

We won't hide these completely - instead, we'll default the database index view to not showing them with a message that says "5 hidden tables" and support ?_hidden=1 to display them.

107914493 issue    
{
    "url": "https://api.github.com/repos/simonw/datasette/issues/129/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
  completed

Links from other tables

  • 0 rows from issues_id in issues_labels
  • 2 rows from issue in issue_comments
Powered by Datasette · Queries took 1.05ms · About: github-to-sqlite