Index Creation Query

Convert a list of CREATE INDEX statements into SQLite's sqlite_master schema rows.

sqlite create-index schema in-memory

Write a function `index_creation_query(table_name: str, index_specs: list[dict]) -> list[dict]` that returns metadata about indexes created on a given table.

**Details:**
- `table_name` is the name of an existing table. You must create that table in an in-memory SQLite database with at least the following schema:
  ```sql
  CREATE TABLE <table_name> (id INTEGER PRIMARY KEY, value TEXT NOT NULL);
  ```
  The given `index_specs` will only reference columns `id` and/or `value`.
- Each spec in `index_specs` is a dict with exactly two keys:
  - `'index_name'`: a string (valid SQL identifier)
  - `'columns'`: a non-empty list of strings, each either `'id'` or `'value'`.
- For each spec, execute:
  ```sql
  CREATE INDEX <index_name> ON <table_name> (<column1>, <column2>, ...);
  ```
  using the columns in the given order.
- After creating all indexes, query `sqlite_master` for rows where `type = 'index'` **and** `tbl_name = <table_name>` **and** `name NOT LIKE 'sqlite_%'` (to exclude the implicit `sqlite_autoindex`). Return a list of dicts for each such row, with keys `type`, `name`, `tbl_name`, and `sql`, in that order. Order the list by `name` ascending.
- Use `sqlite3` module. Do not use any external libraries.
- Your implementation should be deterministic: it must not depend on rowid order or any environment state.

**Constraint:** The number of specs is at most 10, and the total number of columns per index is at most 2.

Constraints

1 <= len(index_specs) <= 10, 1 <= len(columns) <= 2, columns are either 'id' or 'value'. All index names are unique.

Example

>>> index_creation_query('users', [{'index_name': 'idx_users_name', 'columns': ['value']}, {'index_name': 'idx_users_id_value', 'columns': ['id', 'value']}])
[{'type': 'index', 'name': 'idx_users_id_value', 'tbl_name': 'users', 'sql': 'CREATE INDEX idx_users_id_value ON users(id,value)'}, {'type': 'index', 'name': 'idx_users_name', 'tbl_name': 'users', 'sql': 'CREATE INDEX idx_users_name ON users(value)'}]

20 points ~20 min

Recent Submissions

No submissions yet — hit Run Tests to try!

Hints

Create a connection to ':memory:' using sqlite3.connect(':memory:').

After executing CREATE INDEX statements, query sqlite_master with a WHERE clause that filters type='index' and tbl_name=?, and exclude sqlite_autoindex rows.

Order the result by name using ORDER BY name.

Convert each row to a dict with keys 'type', 'name', 'tbl_name', 'sql'.

Test Results

Press Ctrl+Enter or click Run Tests to execute your code.