1

I have the following declarative Sqlalchemy schema:

class Locations(Base):
    __tablename__ = 'locations'
    group = Column(Integer, primary_key=True)
    id = Column(String, primary_key=True)
    shelf = Column(Integer, primary_key=True)
    start = Column(Integer, nullable=False)
    end = Column(Integer, nullable=False)

I would like to create the following index:

CREATE INDEX ixloc ON locations (shelf, "end" DESC) storing (start);

How could I add that?

Storing is an extension to CockroachDB's CREATE INDEX, which stores the given column in the index for faster retrieval.

I've already tried this without success:

Index.argument_for("cockroachdb", "storing", None)
Index('ixloc', Locations.shelf, Locations.end.desc(), cockroachdb_storing='start')
user582175
  • 954
  • 12
  • 17
  • 1
    In addition to registering the custom argument, you also have to provide a custom compiler that uses the argument, like here: https://stackoverflow.com/questions/56662626/how-can-i-add-a-custom-arbitrary-option-to-a-table-in-sqlalchemy, but instead of `CreateTable` you'd provide a compiler for `CreateIndex`. – Ilja Everilä Feb 07 '20 at 11:13

1 Answers1

2

Based on Ilja's answer, the solution is:

from sqlalchemy import Index
from sqlalchemy.schema import CreateIndex

Index.argument_for("cockroachdb", "storing", None)

@compiles(CreateIndex, "cockroachdb")
def compile_create_index(create, compiler, **kw):
    stmt = compiler.visit_create_index(create, **kw)
    cockroachdb_opts = create.element.dialect_options["cockroachdb"]
    storing = cockroachdb_opts.get("storing")

    if storing:
        child_cols = ", ".join([compiler.preparer.quote(c) for c in storing])
        stmt = stmt.rstrip()
        stmt = f"{stmt} STORING ({child_cols})\n\n"
    return stmt

Index('ixloc', Locations.shelf, Locations.end.desc(), cockroachdb_storing=['start'])
user582175
  • 954
  • 12
  • 17