Hi all,
I have a design issue on ticket #7210 that requires some feedback from
the community.
For those that haven't been following along, Ticket #7210 is about
adding the ability to reference fields during a query. Two quick
examples of common use cases:
Find all the hotel rooms that have the same number of chairs and beds:
>>> HotelRoom.objects.filter(n_chairs=F('n_beds'))
=> SELECT * from HotelRoom WHERE n_chairs=n_beds;
Update the number of occupants in room 319 the room by 1:
>>> HotelRoom.objects.filter(room=319).update(n_occupants=F('n_occupants') + 1)
=> UPDATE HotelRoom SET n_occupants=n_occupants+1 WHERE id=319;
Nicolas Lara did some work on this ticket in the scope of his
Aggregates work, leading off an earlier patch from Sebastian Noack.
The ticket was accepted for v1.1 as a must-have. My Github repository
contains the work-in-progress [1]; from my testing, it appears
reasonably solid, although there is still one feature addition
pending.
However, the test cases don't currently pass for all backends, which
leads me to a problem of design and scope. The test suite for F()
notation has revealed some interesting inconsistencies with the
handling of various operators on the various database backends.
Consider integer division. 42/15 returns 2 under SQLite and Postgres.
MySQL, however, returns 2.8, which is rounded into 3 if you try to
assign the division result to an integer column. (e.g., UPDATE person
SET age = 42/15 ...).
Postgres doesn't allow modulo arithmetic on floats; MySQL and SQLite do.
Bitwise operators (& and |) on floats are also inconsistently handled.
SQLite and MySQL are in agreement, but Postgres won't allow bitwise
operations on floating point values without explicit casts to integer
types.
This doesn't even get into the issue of handling dates and strings
using overloaded arithmetic operations.
I can see several possible ways forward:
1) Expose the underlying operations, warts and all. Provide a minimal
test suite that checks that it is possible to do each of the
arithmetic operations, but studiously avoid edge cases known to cause
problems. This is the ostrich solution, but it also acknowledges that
there are differences between backends. Documentation would be updated
to note that some operations are not necessarily portable, and users
should check local guides for details.
2) As for (1), but provide a comprehensive test suite that checks the
different expected results for each backends. This doesn't change
anything in the way the feature would work, but it would explicitly
check that the problematic cases return the expected results. This
would improve test coverage, but would make the test suite much more
complicated, and I'm not convinced it would actually improve the
quality of the code. The bits of code that need testing are in the way
queries are rolled into SQL, not in the way that the backends
interpret that SQL.
3) Try to find the common ground and only provide the subset of
operations that actually behave the same under all backends. I suspect
this would mean we would ultimately end up with no operators
available.
4) Put the infrastructure in place to ensure that integer division is
always handled the same way, regardless of backend handling, and do
strong type checking of operators and operations to make sure the
resultant expression is legal and meaningful. I'm not even sure this
option is feasible for all cases (or even possible), but if we want to
maintain complete database independence, we may need to look down this
road.
Personally, I'm leaning towards option 1. We already acknowledge some
inconsistencies between backend (e.g., fixture ordering issues with
InnoDB, case sensitivity options with some collations). I suspect that
by trying to bend the rules to make things consistent, we will end up
making things a lot more brittle.
Opinions? Other options?
[1] http://github.com/freakboy3742/django/tree/query-expressions
Yours,
Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
