A way of creating indexes on newly created tables

[simonw]

I'm writing code for simonw/git-history#33 that creates a table inside a loop:

item_pk = db[item_table].lookup(
    {"_item_id": item_id},
    item_to_insert,
    column_order=("_id", "_item_id"),
    pk="_id",
)

I need to look things up by _item_id on this table, which means I need an index on that column (the table can get very big).

But there's no mechanism in SQLite utils to detect if the table was created for the first time and add an index to it. And I don't want to run CREATE INDEX IF NOT EXISTS every time through the loop.

This should work like the foreign_keys= mechanism.

[simonw]

(I ended up not needing this here since .lookup() already creates a unique index on _item_id for you. Still could be a useful feature though.)

[simonw]

I think the syntax design of this looks like:

item_pk = db[item_table].lookup(
    {"_item_id": item_id},
    item_to_insert,
    column_order=("_id", "_item_id"),
    pk="_id",
    indexes=("_version",),
)

So it's a sequence of column names... or a sequence of tuples for creating compound indexes:

db["dogs"].insert(
  {"name": "Cleo", "species": "Mutt", "hobbies": "Raiding picnics"},
  indexes=(("name", "species"), "hobbies"),
)

[simonw]

In terms of types, I think that means it looks like this:

IndexesType = Iterable[
    Union[str, Iterable[str]]
]

def create(
    self,
    columns: Dict[str, Any],
    pk: Optional[Any] = None,
    ...
    indexes: Optional[IndexesType] = None,
):