Adding literal_processor for datetime types

[Ed Avis]

When debugging it is useful to print out a query you can run in an SQL
session interactively. Many do this using the following:

print(query.statement.compile(compile_kwargs={"literal_binds": True}))

But not all data types are handled; although strings and integers will be
serialized into their literal SQL values, other types cause an error

Don't know how to literal-quote value datetime.date(2014, 11, 5)

(in this case, a datetime was the unhandled value). Now, not all types can
be handled; for large object and binary types it doesn't make sense to
jam them into an SQL string. But date and time types, like integers, are
small and well-behaved, so it would be useful to serialize them. Otherwise
the programmer is deprived of this debugging aid whenever the query uses
date or time parameters.

The patch to add this functionality is not hard; you need to define a
literal_processor method for a few classes in sqltypes.py. I would like to
submit this patch but I don't have test cases for it. Could you suggest where
to start on adding tests for this?

--
Ed Avis <e...@waniasset.com>

[Michael Bayer]

> On Nov 6, 2014, at 9:38 AM, Ed Avis <e...@waniasset.com> wrote:
>
> When debugging it is useful to print out a query you can run in an SQL
> session interactively. Many do this using the following:
>
> print(query.statement.compile(compile_kwargs={"literal_binds": True}))

note that literal_binds is also used for real SQL, particularly in DDL when producing constructs like functional indexes and constraints. so the literal values must be compliant towards target backends.

>
> But not all data types are handled; although strings and integers will be
> serialized into their literal SQL values, other types cause an error
>
> Don't know how to literal-quote value datetime.date(2014, 11, 5)
>
> (in this case, a datetime was the unhandled value). Now, not all types can
> be handled; for large object and binary types it doesn't make sense to
> jam them into an SQL string. But date and time types, like integers, are
> small and well-behaved, so it would be useful to serialize them.

what backends specifically are they “well-behaved”? If you’re on Oracle, the string representation of a date is dependent on many factors, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm#i1003589, including NLS_TERRITORY, NLS_DATE_LANGUAGE and others. If I implement quick and easy dates for Postgresql and MySQL, now I have to do it for the nasty databases like Oracle, SQL Server and Sybase as well, and that’s where it gets to be a crapshow.

For Oracle, we probably need to implement it as TO_DATE sending both the date value as well as a format string as a second argument; otherwise the dialect would need to be inspecting the NLS_ variables to know the default format expected.

>
> The patch to add this functionality is not hard;

like most features, it’s very easy to get the proof of concept out the door; ironing out all the edge cases for Oracle and others, often ends up being lots of work.

> you need to define a
> literal_processor method for a few classes in sqltypes.py.

this is backend-specific functionality so it doesn’t go in sqltypes.py alone. At the moment it would go as new Date, Time and DateTime types in all dialects, but I’d rather avoid having to do that so I think some new API on dialect would be needed, such that, Date/Time/DateTime literal_processor() call out to a function present on each Dialect itself. this would have to degrade gracefully for dialects that don’t support the method yet, such as 3rd party dialects.

> I would like to
> submit this patch but I don't have test cases for it. Could you suggest where
> to start on adding tests for this?

the tests for backends are already done, and are in lib/sqlalchemy/testing/suite/test_types.py - right now SQLite does support literal rendering since SQLAlchemy does it anyway. The supporting dialects are added to test/requirements.py -> def datetime_literals().

Tests for the “Generic” version would also be added, most likely in test/sql/test_types.py.



>
> --
> Ed Avis <e...@waniasset.com>
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.
> To post to this group, send email to sqlalche...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy-devel.
> For more options, visit https://groups.google.com/d/optout.

[Ed Avis]

Michael Bayer <mike_mp@...> writes:

>> print(query.statement.compile(compile_kwargs={"literal_binds": True}))
>
>note that literal_binds is also used for real SQL, particularly in DDL
>when producing constructs like functional indexes and constraints.
>so the literal values must be compliant towards target backends.

Ah, I didn't realize that. Does that mean that currently sqlalchemy does
not support generating DDL for constraints that include a literal date or
time value?

>>But date and time types, like integers, are small and well-behaved,

>If you’re on Oracle, the string
>representation of a date is dependent on many factors, see
>http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

Yuck. I had hoped that 'yyyy-mm-dd' would be accepted everywhere but of
course it cannot be that easy. So the literal string will be understood
on some Oracle instances but not others depending on the default date
format? Which means you have to say

to_date('2000-01-01', 'YYYY-MM-DD')

I am using MSSQL and my instance understands ISO8601 dates - but again you
are saying that not all of them will. So it needs

convert(date, '2000-01-01', 102)

or for older MSSQL versions, convert(datetime,...). That will also work
for Sybase.

To be clear, your position is that you will accept a patch adding literal
date serialization for all database backends, but not a patch that only
adds it for some of them (such as the plain string representation used in
the line of code at the top)?

What about if the first version of the patch only handles dates, not
time and datetime types?

>...backend-specific functionality so it doesn’t go in sqltypes.py alone.

Is sqltypes.py the default implementation, and then some backends will
override the methods?

>At the moment it would go as new Date, Time and DateTime types in all
>dialects, but I’d rather avoid having to do that so I think some
>new API on dialect would be needed, such that, Date/Time/DateTime
>literal_processor() call out to a function present on each Dialect
>itself. this would have to degrade gracefully for dialects that don’t
>support the method yet, such as 3rd party dialects.

The alternative would be to provide the default implementation in
sqltypes.py and if a 3rd party dialect hasn't overridden it, it gets the
default representation of 'yyyy-mm-dd'. Might that not be the best way to
degrade gracefully, since most SQL dialects out there in practical use do
at least support this date format?

>the tests for backends are already done, and are in
>lib/sqlalchemy/testing/suite/test_types.py - right
>now SQLite does support literal rendering since SQLAlchemy does it
>anyway. The supporting dialects are
>added to test/requirements.py -> def datetime_literals().
>
>Tests for the “Generic” version would also be added, most likely in
>test/sql/test_types.py.

Thanks. I may start work on a test for the generic version first pending
your decision on the best way to extend it out to the dialects.

--
Ed Avis <e...@waniasset.com>

[Michael Bayer]

> On Nov 7, 2014, at 7:00 AM, Ed Avis <e...@waniasset.com> wrote:
>
> Michael Bayer <mike_mp@...> writes:
>
>>> print(query.statement.compile(compile_kwargs={"literal_binds": True}))
>>
>> note that literal_binds is also used for real SQL, particularly in DDL
>> when producing constructs like functional indexes and constraints.
>> so the literal values must be compliant towards target backends.
>
> Ah, I didn't realize that. Does that mean that currently sqlalchemy does
> not support generating DDL for constraints that include a literal date or
> time value?

it does but you just have to spell out the SQL representation literally using literal_column(), or putting the whole expression inside of text().

>
>>> But date and time types, like integers, are small and well-behaved,
>
>> If you’re on Oracle, the string
>> representation of a date is dependent on many factors, see
>> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm
>
> Yuck. I had hoped that 'yyyy-mm-dd' would be accepted everywhere but of
> course it cannot be that easy. So the literal string will be understood
> on some Oracle instances but not others depending on the default date
> format? Which means you have to say
>
> to_date('2000-01-01', 'YYYY-MM-DD’)

yes i think that’s best for Oracle.

>
> I am using MSSQL and my instance understands ISO8601 dates - but again you
> are saying that not all of them will. So it needs
>
> convert(date, '2000-01-01', 102)
>
> or for older MSSQL versions, convert(datetime,...). That will also work
> for Sybase.

OK.

>
> To be clear, your position is that you will accept a patch adding literal
> date serialization for all database backends, but not a patch that only
> adds it for some of them (such as the plain string representation used in
> the line of code at the top)?

if we can cover out to Oracle and MSSQL at least (obviously PG, MySQL and SQLite) that would be fine.

>
> What about if the first version of the patch only handles dates, not
> time and datetime types?

it would be preferable if we could get all three going at once, they should all be pretty similar. there shouldn’t be any hurry to commit this before it’s in good shape, as there’s workarounds now to getting dates to render (such as subclassing Date).

>
>> ...backend-specific functionality so it doesn’t go in sqltypes.py alone.
>
> Is sqltypes.py the default implementation, and then some backends will
> override the methods?

yes, all dialects have their own subset of type objects that modify or augment the base types. Take a look at lib/sqlalchemy/dialects/type_migration_guidelines.txt which was added to illustrate how the type system works.

>
>> At the moment it would go as new Date, Time and DateTime types in all
>> dialects, but I’d rather avoid having to do that so I think some
>> new API on dialect would be needed, such that, Date/Time/DateTime
>> literal_processor() call out to a function present on each Dialect
>> itself. this would have to degrade gracefully for dialects that don’t
>> support the method yet, such as 3rd party dialects.
>
> The alternative would be to provide the default implementation in
> sqltypes.py and if a 3rd party dialect hasn't overridden it, it gets the
> default representation of 'yyyy-mm-dd'. Might that not be the best way to
> degrade gracefully, since most SQL dialects out there in practical use do
> at least support this date format?

I think if date rendering is not supported on a certain backend it should be raising a NotImplementedError. Otherwise, some non-working string format will be sent out and that will break anyway, and possibly not even explicitly which would be bad.

>
>> the tests for backends are already done, and are in
>> lib/sqlalchemy/testing/suite/test_types.py - right
>> now SQLite does support literal rendering since SQLAlchemy does it
>> anyway. The supporting dialects are
>> added to test/requirements.py -> def datetime_literals().
>>
>> Tests for the “Generic” version would also be added, most likely in
>> test/sql/test_types.py.
>
> Thanks. I may start work on a test for the generic version first pending
> your decision on the best way to extend it out to the dialects.

OK!

[Ed Avis]

Michael Bayer <mike_mp@...> writes:

>I think if date rendering is not supported on a certain backend it should
>be raising a NotImplementedError.

I agree - or perhaps it could continue raising the current error message

Don't know how to literal-quote value datetime.date(2014, 11, 5)

However, the question is not what should happen when date rendering isn't
supported, but what to do about backends that might or might not support
date rendering - where it isn't known for certain whether it works or not.
The cautious approach is to make them all fail until the backend maintainer
explicitly sets the literal rendering. An alternative would be to say that
'2014-11-05' will be the default, and if a backend hasn't been updated since
date support was added, that's what it will get. In practice I think that
would work fine - though I suppose there may be some odd DBMS out there
which will silently interpret the date string as something rubbish and so
insert the wrong values.

Assuming you prefer the cautious approach, how about this plan? Do not add
any default implementation in sqltypes.py. Instead each backend that
supports these types will have its own implementation. (Serializing a date
or time is only a couple of lines of code, so I don't think this will result
in lots of copy-pasted boilerplate.) Those backends that don't support it
(yet) will continue to fail with the current error message.

After a release or two, when all backend maintainers have had a chance to
look at the new data types, the code can be refactored a bit to move the
common implementations into sqltypes.py.

--
Ed Avis <e...@waniasset.com>

[Michael Bayer]

> On Nov 7, 2014, at 10:45 AM, Ed Avis <e...@waniasset.com> wrote:
>
> Michael Bayer <mike_mp@...> writes:
>
>> I think if date rendering is not supported on a certain backend it should
>> be raising a NotImplementedError.
>
> I agree - or perhaps it could continue raising the current error message
>
> Don't know how to literal-quote value datetime.date(2014, 11, 5)
>
> However, the question is not what should happen when date rendering isn't
> supported, but what to do about backends that might or might not support
> date rendering - where it isn't known for certain whether it works or not.

> The cautious approach is to make them all fail until the backend maintainer
> explicitly sets the literal rendering. An alternative would be to say that
> '2014-11-05' will be the default, and if a backend hasn't been updated since
> date support was added, that's what it will get. In practice I think that
> would work fine - though I suppose there may be some odd DBMS out there
> which will silently interpret the date string as something rubbish and so
> insert the wrong values.
>
> Assuming you prefer the cautious approach, how about this plan? Do not add
> any default implementation in sqltypes.py.

oh OK, maybe I get what you mean. The “default” dialect is what gets used when you stringify a SQL element without any other dialect given. This never happens when you’re actually using an engine connected to a database, there’s always a DB-specific dialect in use. But, all the DB-specific dialects are subclasses of DefaultDialect. So if we make the “default” dialect produce a generic stringification of dates, we’d want to do it such that this stringification never takes place for any subclasses of that default dialect - it always looks for a dialect-specific method.

In this sense, I can see this is a little bit of a new thing for the Dialect->DefaultDialect-><MyDB>Dialect system, where typically DefaultDialect’s default implementation always takes place when a subclass doesn’t say otherwise.

We’re really just trying to make a special place for stringification without a dialect. Let’s just make that:

class DefaultDialect(Dialect):
def render_date(self, date):
raise CompileError(“don’t know how…”)

class GenericDialect(DefaultDialect):
def render_date(self, date):
return date.strftime(…)

then we just change the default compiler when you say something.compile() to use GenericDialect instead of DefaultDialect. It will be a new place that things like this can go.


One step further, we probably want to use a distinct compiler object for the best extensibility, e.g.:

class DefaultDialect(Dialect):
value_renderer = compiler.DefaultValueRenderer

class GenericDialect(DefaultDialect):
value_renderer = compiler.GenericValueRenderer


a ValueRenderer will live alongside other sub-compilers like SQLCompiler, DDLCompiler and TypeCompiler. The TypeEngine.literal_processor system will defer to using these compiler objects for typed value rendering. Of course, any type can still implement its own literal_processor() method.