Extract indexes from a table — get_tbl

Create a list of indexes from a table. The list reports on which columns the index exists and whether has the UNIQUE constraint. The function processes output from sqlite_master_to_df.

get_tbl_idx(conn, on_tbl, temp = FALSE)

Arguments

conn: An object of class SQLiteConnection to a sqlite database.
on_tbl: The name of the table.
temp: Should sqlite_temp_master be queried, instead of sqlite_master? Default is FALSE. This can be useful when looking for temporary tables and indexes on them.

Value

A named list of lists. Each list corresponds to one index on on_tbl. A elements (top-level) of the list are named according to the name of the indexes in the database. Each element is a list with two entries:

idx_unique: A logical indicating whether the index satisfies the UNIQUE constraint as in CREATE UNIQUE INDEX.
idx_cols: A character vector with the index columns.

Examples

conn <- connect_to_db(db_example("AcademicGraph.sqlite"))
#> The database connection is: 
#> src:  sqlite 3.38.5 [/tmp/RtmptPxKJr/temp_libpath72cc939d74f39/magutils/extdata/AcademicGraph.sqlite]
#> tbls: AuthorAffiliation, FieldsOfStudy, FirstNamesGender, author_coauthor,
#>   author_output, current_links, current_links_advisors, pq_advisors,
#>   pq_authors, pq_fields_mag, pq_unis
get_tbl_idx(conn, "author_output")
#> $idx_ao_AuthorIdYear
#> $idx_ao_AuthorIdYear$idx_unique
#> [1] TRUE
#> 
#> $idx_ao_AuthorIdYear$idx_cols
#> [1] "AuthorId" "YEAR"    
#> 
#>