Supporting Function Indexes on a Minimum Sqlite Version

[Jonathan Vanasco]

I have a project that, in a few rare situations, may run on a version of sqlite that does not support function indexes, and "need" to run a unique index on `lower(name)`.  For simplicity, I'll just use a normal index on correct systems,

I'm trying to figure out the best way to implement this.

1. in terms of sqlite3, what is the best way to access the version Sqlalchemy is using?  the import is in a classmethod, and could either be pysqlite2 or sqlite3?  i seriously doubt anyone would deploy with pysqlite2, but I feel like I should do things the right way.

2. What is the best way to implement this contextual switch?  I thought about a `expression.FunctionElement` with custom `@compiles`.

[Mike Bayer]

On Mon, Mar 8, 2021, at 12:06 PM, 'Jonathan Vanasco' via sqlalchemy wrote:

I have a project that, in a few rare situations, may run on a version of sqlite that does not support function indexes, and "need" to run a unique index on `lower(name)`.  For simplicity, I'll just use a normal index on correct systems,

I'm trying to figure out the best way to implement this.

1. in terms of sqlite3, what is the best way to access the version Sqlalchemy is using?  the import is in a classmethod, and could either be pysqlite2 or sqlite3?  i seriously doubt anyone would deploy with pysqlite2, but I feel like I should do things the right way.

you'd get this from the dbapi:

>>> from sqlalchemy import create_engine

>>> e = create_engine("sqlite://")

>>> e.dialect.dbapi.sqlite_version

'3.34.1'

2. What is the best way to implement this contextual switch?  I thought about a `expression.FunctionElement` with custom `@compiles`.

yeah that is probably the best approach

from sqlalchemy.sql import expression

from sqlalchemy.ext.compiler import compiles

class maybe_lower(expression.FunctionElement):

    type = String()

@compiles(maybe_lower, 'sqlite')

def sl_maybe_lower(element, compiler, **kw):

    args = list(element.clauses)

    if compiler.dialect.dbapi_version < ...:

        return "LOWER(%s)" % (compiler.process(args[0], **kw))

    else:

        return compiler.process(args[0], **kw)

@compiles(maybe_lower)

def default_maybe_lower(element, compiler, **kw):

    args = list(element.clauses)

    return compiler.process(args[0], **kw)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com.

[Jonathan Vanasco]

Thank you so much, Mike!

I roughly had that same @compiles in my tests, but I didn't trust myself... and the .dbapi looked like what I wanted, but I really wasn't sure!