issue_comments: 813162622

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/datasette/issues/1293#issuecomment-813162622 https://api.github.com/repos/simonw/datasette/issues/1293 813162622 MDEyOklzc3VlQ29tbWVudDgxMzE2MjYyMg== 9599 2021-04-05T03:34:24Z 2021-04-05T03:40:35Z OWNER

This almost works, but throws errors with some queries (anything with a rowid column for example) - it needs a bunch of test coverage.

def columns_for_query(conn, sql):
    rows = conn.execute('explain ' + sql).fetchall()
    table_rootpage_by_register = {r['p1']: r['p2'] for r in rows if r['opcode'] == 'OpenRead'}
    names_by_rootpage = dict(
        conn.execute(
            'select rootpage, name from sqlite_master where rootpage in ({})'.format(
                ', '.join(map(str, table_rootpage_by_register.values()))
            )
        )
    )
    columns_by_column_register = {}
    for row in rows:
        if row['opcode'] == 'Column':
            addr, opcode, table_id, cid, column_register, p4, p5, comment = row
            table = names_by_rootpage[table_rootpage_by_register[table_id]]
            columns_by_column_register[column_register] = (table, cid)
    result_row = [dict(r) for r in rows if r['opcode'] == 'ResultRow'][0]
    registers = list(range(result_row["p1"], result_row["p1"] + result_row["p2"] - 1))
    all_column_names = {}
    for table in names_by_rootpage.values():
        table_xinfo = conn.execute('pragma table_xinfo({})'.format(table)).fetchall()
        for row in table_xinfo:
            all_column_names[(table, row["cid"])] = row["name"]
    final_output = []
    for r in registers:
        try:
            table, cid = columns_by_column_register[r]
            final_output.append((table, all_column_names[table, cid]))
        except KeyError:
            final_output.append((None, None))
    return final_output
{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
849978964