How to see the internal content of an index?

Question

When creating an index on a column value:

import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE data(id INTEGER PRIMARY KEY, value TEXT);")
db.execute("CREATE INDEX data_idx ON data(value);")
for v in ["xyz", "abc", "def", "abc", "ijk"]:
    db.execute("INSERT INTO data(value) VALUES (?)", (v,))

how to display the internal content of the index? For learning purposes, and also in more complex cases, seeing what is stored exactly in the index would be helpful.

As data_idx is not a real table, this fails with no such table: data_idx:

db.execute("SELECT * FROM data_idx")

How to display the internal content of a Sqlite index? (it should be a correspondance between sorted values and rowids I guess?)

Answer 1

Using https://sqlite.org/imposter.html, we can create an "imposter" table that shares the same data as the index. We can then display the content of this table, i.e. the content of the index.

Warning (from the doc) :

Imposter tables are intended for analysis and debugging only. This is not a feature that most application developers should understand or even know about. Imposter tables are for experts only. Improper use of imposter tables can cause index corruption, though any corruption created this way can be fixed by running REINDEX.

import sqlite3
db = sqlite3.connect('index.db')
db.executescript("""
CREATE TABLE data(id INTEGER PRIMARY KEY, value TEXT); 
CREATE INDEX data_idx ON data(value);
INSERT INTO data(value) VALUES('xyz'), 
                              ('abc'), 
                              ('def'),
                              ('abc'),
                              ('ijk');

PRAGMA writable_schema=ON;
INSERT INTO sqlite_master(type, name, tbl_name, rootpage, sql)
     VALUES('table', 'data_idx_2', 'data_idx_2', (SELECT rootpage FROM sqlite_master WHERE name='data_idx'),
            'CREATE TABLE data_idx_2(value, id, PRIMARY KEY(value, id)) WITHOUT ROWID')
""")
db.commit()
db.close()  # required to close / open, see documentation
db = sqlite3.connect('index.db')
for r in db.execute("SELECT * FROM data_idx_2"):
    print(r)

Then, this is the content of the index:

('abc', 2)
('abc', 4)
('def', 3)
('ijk', 5)
('xyz', 1)