sections
10 rows where page = "full_text_search" sorted by references descending
This data as json, CSV (advanced)
id | page | ref | title | content | breadcrumbs | references ▲ |
---|---|---|---|---|---|---|
full_text_search:configuring-fts-using-csvs-to-sqlite | full_text_search | configuring-fts-using-csvs-to-sqlite | Configuring FTS using csvs-to-sqlite | If your data starts out in CSV files, you can use Datasette's companion tool csvs-to-sqlite to convert that file into a SQLite database and enable full-text search on specific columns. For a file called items.csv where you want full-text search to operate against the name and description columns you would run the following: csvs-to-sqlite items.csv items.db -f name -f description | ["Full-text search", "Enabling full-text search for a SQLite table"] | [{"href": "https://github.com/simonw/csvs-to-sqlite", "label": "csvs-to-sqlite"}] |
full_text_search:full-text-search-custom-sql | full_text_search | full-text-search-custom-sql | Searches using custom SQL | You can include full-text search results in custom SQL queries. The general pattern with SQLite search is to run the search as a sub-select that returns rowid values, then include those rowids in another part of the query. You can see the syntax for a basic search by running that search on a table page and then clicking "View and edit SQL" to see the underlying SQL. For example, consider this search for manafort is the US FARA database : /fara/FARA_All_ShortForms?_search=manafort If you click View and edit SQL you'll see that the underlying SQL looks like this: select rowid, Short_Form_Termination_Date, Short_Form_Date, Short_Form_Last_Name, Short_Form_First_Name, Registration_Number, Registration_Date, Registrant_Name, Address_1, Address_2, City, State, Zip from FARA_All_ShortForms where rowid in ( select rowid from FARA_All_ShortForms_fts where FARA_All_ShortForms_fts match escape_fts(:search) ) order by rowid limit 101 | ["Full-text search"] | [{"href": "https://fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort", "label": "manafort is the US FARA database"}, {"href": "https://fara.datasettes.com/fara?sql=select%0D%0A++rowid%2C%0D%0A++Short_Form_Termination_Date%2C%0D%0A++Short_Form_Date%2C%0D%0A++Short_Form_Last_Name%2C%0D%0A++Short_Form_First_Name%2C%0D%0A++Registration_Number%2C%0D%0A++Registration_Date%2C%0D%0A++Registrant_Name%2C%0D%0A++Address_1%2C%0D%0A++Address_2%2C%0D%0A++City%2C%0D%0A++State%2C%0D%0A++Zip%0D%0Afrom%0D%0A++FARA_All_ShortForms%0D%0Awhere%0D%0A++rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++FARA_All_ShortForms_fts%0D%0A++++where%0D%0A++++++FARA_All_ShortForms_fts+match+escape_fts%28%3Asearch%29%0D%0A++%29%0D%0Aorder+by%0D%0A++rowid%0D%0Alimit%0D%0A++101&search=manafort", "label": "View and edit SQL"}] |
full_text_search:full-text-search-table-view-api | full_text_search | full-text-search-table-view-api | The table page and table view API | Table views that support full-text search can be queried using the ?_search=TERMS query string parameter. This will run the search against content from all of the columns that have been included in the index. Try this example: fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort SQLite full-text search supports wildcards. This means you can easily implement prefix auto-complete by including an asterisk at the end of the search term - for example: /dbname/tablename/?_search=rob* This will return all records containing at least one word that starts with the letters rob . You can also run searches against just the content of a specific named column by using _search_COLNAME=TERMS - for example, this would search for just rows where the name column in the FTS index mentions Sarah : /dbname/tablename/?_search_name=Sarah | ["Full-text search"] | [{"href": "https://fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort", "label": "fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort"}] |
full_text_search:full-text-search-fts-versions | full_text_search | full-text-search-fts-versions | FTS versions | There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can tell which versions are supported by your instance of Datasette by checking the /-/versions page. FTS5 is the most advanced module but may not be available in the SQLite version that is bundled with your Python installation. Most importantly, FTS5 is the only version that has the ability to order by search relevance without needing extra code. If you can't be sure that FTS5 will be available, you should use FTS4. | ["Full-text search"] | [] |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [sections] ( [id] TEXT PRIMARY KEY, [page] TEXT, [ref] TEXT, [title] TEXT, [content] TEXT, [breadcrumbs] TEXT, [references] TEXT );