sqlite-utils indexes command

[simonw]

While working on #260 I realized there's no command to show indexes in a database, even though there is one for showing tables and one for triggers.

I should implement #261 first.

[simonw]

Here's how sqlite-utils triggers works:

sqlite-utils/sqlite_utils/cli.py

Lines 1266 to 1277 in 9c67cb9

	):
	"Show triggers configured in this database"
	sql = "select name, tbl_name as [table], sql from sqlite_master where type = 'trigger'"
	if tables:
	quote = sqlite_utils.Database(memory=True).quote
	sql += " and [table] in ({})".format(
	", ".join(quote(table) for table in tables)
	)
	ctx.invoke(
	query,
	path=path,
	sql=sql,

Running it from a SQL query makes it easy to support modifiers like --csv and -t.

[simonw]

Figuring out the right queries:

https://covid-19.datasettes.com/covid?sql=select+sqlite_master.name%2C+i.*+from+sqlite_master%0D%0Ajoin+pragma_index_list%28sqlite_master.name%29+i%0D%0Awhere+type+%3D+%27table%27

This query shows all columns across all indexes across all tables:

select i.name as index_name, xinfo.* from sqlite_master
join pragma_index_list(sqlite_master.name) i
join pragma_index_xinfo(index_name) xinfo
where sqlite_master.type = 'table'

https://covid-19.datasettes.com/covid?sql=select+i.name+as+index_name%2C+xinfo.*+from+sqlite_master%0D%0Ajoin+pragma_index_list%28sqlite_master.name%29+i%0D%0Ajoin+pragma_index_xinfo%28index_name%29+xinfo%0D%0Awhere+sqlite_master.type+%3D+%27table%27

[simonw]

By default I won't return auxiliary columns, but I'll offer a --aux option to return them.

[simonw]

This looks good:

% sqlite-utils indexes /tmp/covid.db -t      
index_name                                                seqno    cid  name                 desc  coll      key
------------------------------------------------------  -------  -----  -----------------  ------  ------  -----
idx_johns_hopkins_csse_daily_reports_combined_key             0     12  combined_key            0  BINARY      1
idx_johns_hopkins_csse_daily_reports_country_or_region        0      1  country_or_region       0  BINARY      1
idx_johns_hopkins_csse_daily_reports_province_or_state        0      2  province_or_state       0  BINARY      1
idx_johns_hopkins_csse_daily_reports_day                      0      0  day                     0  BINARY      1
idx_ny_times_us_counties_date                                 0      0  date                    1  BINARY      1
idx_ny_times_us_counties_fips                                 0      3  fips                    0  BINARY      1
idx_ny_times_us_counties_county                               0      1  county                  0  BINARY      1
idx_ny_times_us_counties_state                                0      2  state                   0  BINARY      1

% sqlite-utils indexes /tmp/covid.db -t --aux
index_name                                                seqno    cid  name                 desc  coll      key
------------------------------------------------------  -------  -----  -----------------  ------  ------  -----
idx_johns_hopkins_csse_daily_reports_combined_key             0     12  combined_key            0  BINARY      1
idx_johns_hopkins_csse_daily_reports_combined_key             1     -1                          0  BINARY      0
idx_johns_hopkins_csse_daily_reports_country_or_region        0      1  country_or_region       0  BINARY      1
idx_johns_hopkins_csse_daily_reports_country_or_region        1     -1                          0  BINARY      0
idx_johns_hopkins_csse_daily_reports_province_or_state        0      2  province_or_state       0  BINARY      1
idx_johns_hopkins_csse_daily_reports_province_or_state        1     -1                          0  BINARY      0
idx_johns_hopkins_csse_daily_reports_day                      0      0  day                     0  BINARY      1
idx_johns_hopkins_csse_daily_reports_day                      1     -1                          0  BINARY      0
idx_ny_times_us_counties_date                                 0      0  date                    1  BINARY      1
idx_ny_times_us_counties_date                                 1     -1                          0  BINARY      0
idx_ny_times_us_counties_fips                                 0      3  fips                    0  BINARY      1
idx_ny_times_us_counties_fips                                 1     -1                          0  BINARY      0
idx_ny_times_us_counties_county                               0      1  county                  0  BINARY      1
idx_ny_times_us_counties_county                               1     -1                          0  BINARY      0
idx_ny_times_us_counties_state                                0      2  state                   0  BINARY      1
idx_ny_times_us_counties_state                                1     -1                          0  BINARY      0

[simonw]

Needs to show the table each index applies to.

[simonw]

Documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#listing-indexes