Sqlite-utils

This release introduces a new `sqlite-utils search` command for searching tables, see [Executing searches](https://sqlite-utils.readthedocs.io/en/latest/cli.html#cli-search). ([192](https://github.com/simonw/sqlite-utils/issues/192))

The `table.search()` method has been redesigned, see [Searching with table.search()](https://sqlite-utils.readthedocs.io/en/latest/python-api.html#python-api-fts-search). ([197](https://github.com/simonw/sqlite-utils/issues/197))

The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:

- The `-c` shortcut option for outputting CSV is no longer available. The full `--csv` option is required instead.
- The `-f` shortcut for `--fmt` has also been removed - use `--fmt`.
- The `table.search()` method now defaults to sorting by relevance, not sorting by `rowid`. ([198](https://github.com/simonw/sqlite-utils/issues/198))
- The `table.search()` method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.

Also in this release:

- The `query`, `tables`, `rows` and `search` CLI commands now accept a new `--tsv` option which outputs the results in TSV. ([193](https://github.com/simonw/sqlite-utils/issues/193))
- A new `table.virtual_table_using` property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. `FTS4` or `FTS5`) if it is. It returns `None` if the table is not a virtual table. ([196](https://github.com/simonw/sqlite-utils/issues/196))
- The new `table.search_sql()` method returns the SQL for searching a table, see [Building SQL queries with table.search_sql()](https://sqlite-utils.readthedocs.io/en/latest/python-api.html#python-api-fts-search-sql).
- `sqlite-utils rows` now accepts multiple optional `-c` parameters specifying the columns to return. ([200](https://github.com/simonw/sqlite-utils/issues/200))

Changes since the 3.0a0 alpha release:

- The `sqlite-utils search` command now defaults to returning every result, unless you add a `--limit 20` option.
- The `sqlite-utils search -c` and `table.search(columns=[])` options are now fully respected. ([201](https://github.com/simonw/sqlite-utils/issues/201))

This release introduces a new `sqlite-utils search` command for searching tables, see [Executing searches](https://sqlite-utils.readthedocs.io/en/latest/cli.html#cli-search). ([192](https://github.com/simonw/sqlite-utils/issues/192))

The `table.search()` method has been redesigned, see [Searching with table.search()](https://sqlite-utils.readthedocs.io/en/latest/python-api.html#python-api-fts-search). ([197](https://github.com/simonw/sqlite-utils/issues/197))

The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:

- The `-c` shortcut option for outputting CSV is no longer available. The full `--csv` option is required instead.
- The `-f` shortcut for `--fmt` has also been removed - use `--fmt`.
- The `table.search()` method now defaults to sorting by relevance, not sorting by `rowid`. ([198](https://github.com/simonw/sqlite-utils/issues/198))
- The `table.search()` method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.

Also in this release:

- The `query`, `tables`, `rows` and `search` CLI commands now accept a new `--tsv` option which outputs the results in TSV. ([193](https://github.com/simonw/sqlite-utils/issues/193))
- A new `table.virtual_table_using` property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. `FTS4` or `FTS5`) if it is. It returns `None` if the table is not a virtual table. ([196](https://github.com/simonw/sqlite-utils/issues/196))
- The new `table.search_sql()` method returns the SQL for searching a table, see [Building SQL queries with table.search_sql()](https://sqlite-utils.readthedocs.io/en/latest/python-api.html#python-api-fts-search-sql).
- `sqlite-utils rows` now accepts multiple optional `-c` parameters specifying the columns to return. ([200](https://github.com/simonw/sqlite-utils/issues/200))

- `table.m2m(other_table, records)` method now takes any iterable, not just a list or tuple. Thanks, Adam Wolf. ([189](https://github.com/simonw/sqlite-utils/pull/189))
- `sqlite-utils insert` now displays a progress bar for CSV or TSV imports. ([173](https://github.com/simonw/sqlite-utils/issues/173))
- New `db.register_function(deterministic=True)` option for registering deterministic SQLite functions in Python 3.8 or higher. ([191](https://github.com/simonw/sqlite-utils/issues/191))

- New `--encoding` option for processing CSV and TSV files that use a non-utf-8 encoding, for both the `insert` and `update` commands. ([182](https://github.com/simonw/sqlite-utils/issues/182))
- The `--load-extension` option is now available to many more commands. ([137](https://github.com/simonw/sqlite-utils/issues/137))
- `--load-extension=spatialite` can be used to load SpatiaLite from common installation locations, if it is available. ([136](https://github.com/simonw/sqlite-utils/issues/136))
- Tests now also run against Python 3.9. ([184](https://github.com/simonw/sqlite-utils/issues/184))
- Passing `pk=["id"]` now has the same effect as passing `pk="id"`. ([181](https://github.com/simonw/sqlite-utils/issues/181))

- `table.extract()` and `sqlite-utils extract` now apply much, much faster - one example operation reduced from twelve minutes to just four seconds! ([172](https://github.com/simonw/sqlite-utils/issues/172))
- `sqlite-utils extract` no longer shows a progress bar, because it's fast enough not to need one.
- New `column_order=` option for `table.transform()` which can be used to alter the order of columns in a table. ([175](https://github.com/simonw/sqlite-utils/issues/175))
- `sqlite-utils transform --column-order=` option (with a `-o` shortcut) for changing column order. ([176](https://github.com/simonw/sqlite-utils/issues/176))
- The `table.transform(drop_foreign_keys=)` parameter and the `sqlite-utils transform --drop-foreign-key` option have changed. They now accept just the name of the column rather than requiring all three of the column, other table and other column. This is technically a backwards-incompatible change but I chose not to bump the major version number because the transform feature is so new. ([177](https://github.com/simonw/sqlite-utils/issues/177))
- The table `.disable_fts()`, `.rebuild_fts()`, `.delete()`, `.delete_where()` and `.add_missing_columns()` methods all now `return self`, which means they can be chained together with other table operations.

This release introduces two key new capabilities: **transform** ([114](https://github.com/simonw/sqlite-utils/issues/114)) and **extract** ([#42](https://github.com/simonw/sqlite-utils/issues/42)).

Transform

SQLite's ALTER TABLE has [several documented limitations](https://sqlite.org/lang_altertable.html). The `table.transform()` Python method and `sqlite-utils transform` CLI command work around these limitations using a pattern where a new table with the desired structure is created, data is copied over to it and the old table is then dropped and replaced by the new one.

You can use these tools to drop columns, change column types, rename columns, add and remove `NOT NULL` and defaults, remove foreign key constraints and more. See the [transforming tables (CLI)](https://sqlite-utils.readthedocs.io/en/stable/cli.html#cli-transform-table) and [transforming tables (Python library)](https://sqlite-utils.readthedocs.io/en/stable/python-api.html#python-api-transform) documentation for full details of how to use them.

Extract

Sometimes a database table - especially one imported from a CSV file - will contain duplicate data. A `Trees` table may include a `Species` column with only a few dozen unique values, when the table itself contains thousands of rows.

The `table.extract()` method and `sqlite-utils extract` commands can extract a column - or multiple columns - out into a separate lookup table, and set up a foreign key relationship from the original table.

The Python library [extract() documentation](https://sqlite-utils.readthedocs.io/en/stable/python-api.html#python-api-extract) describes how extraction works in detail, and [Extracting columns into a separate table](https://sqlite-utils.readthedocs.io/en/stable/cli.html#cli-extract) in the CLI documentation includes a detailed example.

Other changes

- The `db.register_function` decorator can be used to quickly register Python functions as custom SQL functions, see [Registering custom SQL functions](https://sqlite-utils.readthedocs.io/en/stable/python-api.html#python-api-register-function). ([162](https://github.com/simonw/sqlite-utils/issues/162))
- The `table.rows_where()` method now accepts an optional `select=` argument for specifying which columns should be selected, see [Listing rows](https://sqlite-utils.readthedocs.io/en/stable/python-api.html#python-api-rows).