DateField, Database Parameter Types and Jython Backends
Leo Soto M.
I was forced to do some ugly gymnastic to convert query parameters
(those passed to cursor.execute) to values which keeps JDBC happy.
As part of this Summer of Code, I'm writing a better implementation.
Some of the problems seen on the prototype can be solved at the zxJDBC
(DBAPI <-> JDBC bridge) layer. But I found a problem which currently
can't be solved without hacks:
dates, times, and datetimes are converted to string _before_ being
passed to the backend. At least with the PostgreSQL JDBC driver,
strings can't be used to specify dates (and friends). So I would have
to parse the string. Or make special-cases on the corresponding
get_db_prep_save methods, as currently done for TimeField:
def get_db_prep_save(self, value):
# Casts dates into string format for entry into database.
if value is not None:
# MySQL will throw a warning if microseconds are given, because it
# doesn't support microseconds.
if settings.DATABASE_ENGINE == 'mysql' and hasattr(value,
'microsecond'):
value = value.replace(microsecond=0)
if settings.DATABASE_ENGINE == 'oracle':
# cx_Oracle expects a datetime.datetime to persist
into TIMESTAMP field.
if isinstance(value, datetime.time):
value = datetime.datetime(1900, 1, 1, value.hour,
value.minute,
value.second, value.microsecond)
elif isinstance(value, basestring):
value = datetime.datetime(*(time.strptime(value,
'%H:%M:%S')[:6]))
else:
value = smart_unicode(value)
return Field.get_db_prep_save(self, value)
But that will get messy once I add more "*_zxjdbc" backends (such as
mysql_zxjdbc and oracle_zxjdbc)
So, the specific problem is DateField and friends making the
assumption that all backends will be happy with a string as a value,
which is questionable. The general problem is that Field extensibility
may be limited by differences on requirements of backends parameter
types. Anyway, if we don't have another concrete example of this
general problem (GeoDjango folks, maybe?), I don't think that coming
up with a general solution is worth the effort.
Current ideas for solving the specific {date, time, datetime} problem are:
a) Return a "boxed" value from get_db_prep_save, which behaves like a
string (so existing backends aren't affected) but can be identified by
backends that don't support strings as values for dates. Such backends
should intercept the values either wrapping the underlying cursor and
examining execute() parameters. Or maybe with a custom Query class
overriding execute_sql.
b) Pass the backend (or its DatabaseOperations) to
Field.get_db_prep_save. We could then add a
DatabaseOperation.to_db_date(value) method, which would be called from
get_db_prep_save. In other words, date (and friends) conversion would
be delegated to the backend.
I'm far from knowing much on the subject, so I'd like to hear what
other people think on this topic.
--
Leo Soto M.
http://blog.leosoto.com
Jacob Kaplan-Moss
> dates, times, and datetimes are converted to string _before_ being
> passed to the backend.
Oops :)
> b) Pass the backend (or its DatabaseOperations) to
> Field.get_db_prep_save. We could then add a
> DatabaseOperation.to_db_date(value) method, which would be called from
> get_db_prep_save. In other words, date (and friends) conversion would
> be delegated to the backend.
This is probably the better solution, though you don't need to pass
the backend into ``Field.get_db_prep_save`` since ``db_prep_save`` can
get at the backend through ``django.db.connection.backend``.
Jacob
Leo Soto M.
I went through this path, but found a few problems I think may be
worth to point out here:
- It seems that the type of the value passed to `get_db_prep_*` isn't
always the obvious python data type associated with the field. For
example, `DateField.get_db_prep_save` and
`DateField.get_db_prep_lookup` uses duck typing to support
datetime.date and string as the type of `value`. My first reaction was
to use self.to_python(value) to ensure that I have a date object
before calling the proposed `DatabaseOperation.value_to_db_date()`.
But later remembered that `Field.to_python` was meant for DB -> Python
conversion and not the other way around.
- If I keep following the same path, I'll have to add a
DatabaseOperation.value_to_db_ipaddr() too. On JDBC, PostgreSQL is not
happy with plain string values (I think I'll need to add a explicit
cast there)
- The management of the `lookup_type` in `Field.get_db_prep_lookup`
looks weird to me. I don't get why the `year` lookup need special
treatment and the `month` lookup doesn't. Also, in DateField,
DateTimeField and TimeField, the `range` lookup have the special
treatment aimed at returning a list instead of a single value, but the
`in` lookup type doesn't. I'm sure there is a reason for this odd
behavior, but I can't see it.
- From tests cases I'm seeing that, under some circumstances, strings
are passed to the backend as the value of parameters which, in the
database, are of numerical types. Numbers (0, 1) instead of booleans
too. Is this an intended behavior?