Oracle Query Builder Non Functional

[reedjosh]

I am trying to add Oracle support to Tortoise ORM. See PR https://github.com/tortoise/tortoise-orm/pull/341

The Oracle query builder seems to be just a pass through and is not customized for Oracle at all.

Some examples I've been working around include: Limit functionality/Offset Functionality. https://github.com/kayak/pypika/issues/211

Quotechars used prevent the query from working. https://github.com/kayak/pypika/issues/37

My current work around is to build my own OracleQuery class as follows:

class OracleQueryBuilder(QueryBuilder):  # type: ignore
    def __init__(self, **kwargs):
        super(OracleQueryBuilder, self).__init__(dialect=Dialects.ORACLE, **kwargs)

    def get_sql(self, *args, **kwargs):
        return super(OracleQueryBuilder, self).get_sql(*args, groupby_alias=False, **kwargs)

    def _limit_sql(self):
        return f" FETCH NEXT {self._limit} ROWS ONLY"

class OracleQuery(Query):  # type: ignore
    """
    Defines a query class for use with Oracle.
    """

    @classmethod
    def _builder(cls, **kwargs):
        builder = OracleQueryBuilder(**kwargs)  # type: ignore
        builder.QUOTE_CHAR = ""
        return builder

Could some expanded form of this be integrated into PyPika itself?

[twheys]

Hi, please take a look at this issue about limits in oracle and let me know if it's a acceptable approach for you https://github.com/kayak/pypika/issues/211

Generally we try to avoid overriding functions for different dialects to yield different results in favor of yielding a python API that matches the resulting SQL. If you prefer to use the syntax FETCH NEXT {self._limit} ROWS ONLY, could you please open a PR adding some function with a similar name to the OracleQuery class?

[reedjosh]

211 is the issue I linked initially. My main goal is having a consistent function to call across the dialects. Right now calling limit on OracleQuery breaks the resulting query.

So if the limit function added WHERE ROWNUM <= 2, that would be fine, but I'm not sure how to implement that so that it gets added properly.

Mostly I was hoping since this wouldn't be in the core of Pypika we would be able to integrate the workaround as above.

Otherwise the only way to use Pypika is to workaround the limit function downstream.

[twheys]

pypika doesn't aim to provide a common API across different sql database vendors, rather to provide a syntax that reads exactly like the SQL it's producing, so using different flavors of SQL will require building different pypika queries.

[reedjosh]

That's fine and kinda what I expected. Still, without changing quotechars (as above) no Oracle queries work. The OracleQuery class as provided is broken.

If there is going to be a limit function in the base and an Oracle query super class within PyPika, then it would be nice if calling limit raised an exception via an overridden limit function in the super. It took me a while to dig into an upstream project to eventually finger PyPika for these two issues.

I'm sure there are other things that the OracleQuery class need work on as well, and maybe an over/next function for the OracleQuery would make sense.

I think either those fixes and probably more should be implemented or PyPika should remove the OracleQuery class altogether. As it is it will continue to confuse future travellers whom expect it to work.

[reedjosh]

Also, I just realized quotechar is just not implemented correctly for Oracle syntax.

If we desired to use quotechars as they are useful, they should come out something like:

SELECT 
    'tesexecplan.masklayername' AS "0"
FROM 
     tesexecplan

In Oracle, identifiers are different from strings and strings can be used to refer to table/field.

So "blah" = identifier 'blah' = string.

PyPika currently builds:

SELECT 
    "tesexecplan"."masklayername" AS "0"
FROM 
     "tesexecplan"

Where quotations aren't allowed for table then field, but only the full table/field.

And, the table itself cannot be selected via string so it should probably not have any quoting.