Custom Database interface: overwrite query class?

Using Django Using the ORM
1

I have to connect to a legacy system with a legacy database. The database uses an advantage database server. I’ve forked adsdb-django and modified it to work with python 3 and modern django versions.

I’m now experience the following incompatibility:
the ads db fails with “2154 The data type of a parameter cannot be determined” for queries like e.g.
SELECT ? as "a" in .... with a parameter 1
According to the database documentation these queries are not supported:

Problem: A parameter by itself in the SELECT list or ORDER BY clause has an ambiguous data type. For example, given the statements “SELECT ? FROM mytable” or “SELECT * FROM myTable ORDER BY ?”, the data type of the parameter in either statement cannot be determined.

Solution: Remove the ambiguous parameter from the statement. The use of the parameter in the SELECT list or ORDER BY clause is generally not meaningful.

The offending queries are generated by e.g. Query.exists(), which calls q.add_annotation(Value(1), "a"). As a workaround I’ve implemented a filter in the SQLCompiler like this:

class SQLCompiler(compiler.SQLCompiler):

    def filter_query(self):
        annotations_to_readd = {}
        
        # find offending annotations
        for key, annotation in self.query.annotations.items():
            if isinstance(annotation, Value) and isinstance(annotation.value, int):
                annotations_to_readd[key] = annotation.value

        # remove annotations
        for key in annotations_to_readd.keys():
            self.query.annotations.pop(key, None)

        # Re-add the annotations
        for key, value in annotations_to_readd.items():
            self.query.add_extra({key: value}, None, None, None, None, None)
        self.query.set_extra_mask(list(annotations_to_readd.keys()))

    def as_sql(self, with_limits=True, with_col_aliases=True):
        self.filter_query()

        query, params = super(SQLCompiler, self).as_sql(...)

Is there a better way to achieve my goal?
I would think overriding Query.exists() would be better, but can I somehow have a custom query class for my database interface?

2

I haven’t tried it out myself, and I’m not familiar with advantage, but could you also use a CAST to make sure the proper type is inferred?

In all cases case there are two approaches you can use to generate per-backend specific SQL.

The first one is providing your own SQLCompiler subclass like you did. The only change I’d make is to avoid using extra interfaces as they will eventually be deprecated. I’d replace the annotations with RawSQL instance instead.

def filter_query(self):
    for key, annotation in self.query.annotations.items():
        if (
            isinstance(annotation, Value)
            and isinstance(annotation.value, int)
        ):
            expr = RawSQL(
                str(annotation.value),
                (),
                output_field=annotation.output_field(),
            )
            self.query.annotations[key] = expr

A less invasive solution would be to attach a as_<vendor_name> (I assume as_advantage) method on Value

def value_as_advantage(self, compiler, connection):
    if isinstance(self.value, int):
        return str(self.value), ()
    return self.as_sql(compiler, connection)

from django.db.models import Value

Value.as_advantage = value_as_advantage
1 Like
3

Thanks, your answer was super helpful! The cast idea also works, so I decided to monkey patch the Value class by adding

from django.db.models import Value

def value_as_adsdb(self, compiler, connection):
    if isinstance(self.value, int):
        return "CAST(%s as SQL_INTEGER)", (self.value,)
    return self.as_sql(compiler, connection)

Value.as_adsdb = value_as_adsdb

to my compiler.py. I guess this is the safest and least invasive way to do this.

4

Actually this isn’t working in all cases:
[SAP][Advantage SQL Engine]Value in an IN expression must be a simple value'

I’m now back to filtering the query in the compiler

    def filter_query(self):
        for key, annotation in self.query.annotations.items():
            if isinstance(annotation, Value) and isinstance(annotation.value, int):
                expr = RawSQL(
                    "CAST(%s as SQL_INTEGER)",
                    (annotation.value,),
                    output_field=annotation.output_field,
                )
                self.query.annotations[key] = expr
5

Assuming this is coming from a filter(some_field__in=...) lookup you could have most likely adapted django.db.models.lookups.In.as_adsdb like you did with Value.