postgresql range types v2

[Marco Paolini]

Hi all,

sorry for the noise, forget my previous mail as it was pointing to the wrong commit,
here's the good one:

https://github.com/mpaolini/django/commit/b754abdeab204949510500ccb1b845b7ad143542

copying here the rest of the original mail:

postgresql since version 9.2 added support for range types [1]
they have a nice set of specialized operators like "overlaps", "left of", etc... [2]

So I decided to work on a reference implementation for Django
even if it looks like psycopg2 does not fully support yet these data types [3]

The implementation is only a proof of concept and is not complete and not tested
(but it does contain tests, of course!)

I did:
- datetime range python data type: two bounds plus inclusive/excusive info (very basic!)
- datetime range model field
- range specific lookups for querysets
- non-overlapping constraint: db-level enforced with sql CONSTRAINT and model validation
- some documentation

TODO:
- form, widget, modelform, localization, admin
- more range types (int, bigint, etc...)
- more validation against invalid ranges
- better range type python implementation
- more testing

Do you like it? Any chances for it to land in master once it is completed? Or is it too specialized?

Cheers,

Marco

[1] http://www.postgresql.org/docs/9.2/static/rangetypes.html
[2] http://www.postgresql.org/docs/9.2/static/functions-range.html#RANGE-OPERATORS-TABLE
[3] http://archives.postgresql.org/psycopg/2012-09/msg00051.php

[Simon Litchfield]

Marco, this is great. 

I wonder if it would be possible to add range fields without modifying django? 

[Simon Litchfield]

Also, did you see psycopg2.extras.DateTimeRange?

On Monday, December 31, 2012 8:56:12 PM UTC+11, mpaolini wrote:

[Marco Paolini]

On Thursday, January 17, 2013, Simon Litchfield wrote:

Marco, this is great. 

Thanks, did you give it a try? 

I wonder if it would be possible to add range fields without modifying django? 

Very difficult, there are many small changes scattered around the core django ORM and driver code

 

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-developers/-/EXxINLYk-hgJ.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Marco Paolini]

On Thursday, January 17, 2013, Simon Litchfield wrote:

Also, did you see psycopg2.extras.DateTimeRange?

No, I missed that one !

Thanks I 'll see if it can be used somehow, but since the range types have to be used by the core django code, I doubt they can be imported from a third party library

 

--
You received this message because you are subscribed to the Google Groups "Django developers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/django-developers/-/shC1MhFZg4YJ.

[Russell Keith-Magee]

Hi Marco,

Great work!

As for getting this into master -- we have to dance a fine line between supporting all the great features of a database like PostgreSQL, and guaranteeing cross-database compatibility.  

So - while I'm not sure there's a place for this in core (unless you can demonstrate how to implement range types on other backends), it should be *possible* to use this library as a third party extension. If there is any interface or refactoring that that can be done to the database backend API that would make this possible, I'm definitely open to making those changes.

The other thing I'd suggest is to look at others doing similar work. For example, Zachary Voase has been working on extensions to Django's PostgreSQL backend to support a whole lot of extra PostgreSQL features[1]. From the look of it, he hasn't got to range types yet, so what you've done here could probably be added as part of that project. PostgreSQL has tons of great features, and it would be great to be able to expose them in a clean way to Django developers; better yet would be to expose them all as a single extension library.

[1] https://github.com/zacharyvoase/django-postgres

Yours,

Russ Magee %-)

[Florian Apolloner]

Hi,

On Thursday, January 17, 2013 11:08:01 PM UTC+1, Russell Keith-Magee wrote:

So - while I'm not sure there's a place for this in core (unless you can demonstrate how to implement range types on other backends), it should be *possible* to use this library as a third party extension. If there is any interface or refactoring that that can be done to the database backend API that would make this possible, I'm definitely open to making those changes.

From what I know Anssi is already working in that area to allow fields to specify custom filters etc (with the goal to support stuff like jsonfields/hstore/arrays). Not sure if there is any public code somewhere already though.

Cheers,
Florian

[Tom Evans]

On Thu, Jan 17, 2013 at 10:08 PM, Russell Keith-Magee
<rus...@keith-magee.com> wrote:
> The other thing I'd suggest is to look at others doing similar work. For
> example, Zachary Voase has been working on extensions to Django's PostgreSQL
> backend to support a whole lot of extra PostgreSQL features[1]. From the
> look of it, he hasn't got to range types yet, so what you've done here could
> probably be added as part of that project. PostgreSQL has tons of great
> features, and it would be great to be able to expose them in a clean way to
> Django developers; better yet would be to expose them all as a single
> extension library.
>
> [1] https://github.com/zacharyvoase/django-postgres
>

We use the django-postgres-netfields library [1] extensively at $JOB,
which allows you to store v4/v6 IP addresses and netmasks as native
types in the database, and perform IP-like queries on them, eg:

Foo.objects.filter(mask__net_contains_or_equals=request['REMOTE_ADDR'])

Perhaps worth looking at as well?

Cheers

Tom

[1] https://github.com/adamcik/django-postgresql-netfields

[Anssi Kääriäinen]

Yes, I have done some work already, discussions & ticket at:
https://code.djangoproject.com/ticket/16187. The code is still in a
stage where I am trying to figure out the correct design.

Currently the basic design is that we have Lookup objects which have
some methods needed for preparing the data and producing SQL. And,
then Field gets a new get_lookup() method where one can return
different Lookup objects for different lookup names.

The goal is to support things like nested json lookups. Another goal
is to have complete support for old-style custom lookups for the
deprecation period. I think I have achieved this - the GIS backends
passes all tests without any changes to it. To achieve this the patch
essentially contains both the new and old lookup code in parallel
(yes, it is ugly, but it seems to work).

For examples of how to define lookups, see:
https://github.com/akaariai/django/compare/ticket_16187_new#L13L72 and
the Lookup object itself at https://github.com/akaariai/django/compare/ticket_16187_new#L6R50.
Warning about the code - it will likely contain totally misleading
comments.

- Anssi

[Marco Paolini]

On 17/01/2013 22:08, Russell Keith-Magee wrote:

>
> On Mon, Dec 31, 2012 at 5:56 PM, Marco Paolini <markop...@gmail.com <mailto:markop...@gmail.com>> wrote:
>
> Hi all,
>
> sorry for the noise, forget my previous mail as it was pointing to the wrong commit,
> here's the good one:
>

> https://github.com/mpaolini/__django/commit/__b754abdeab204949510500ccb1b845__b7ad143542 <https://github.com/mpaolini/django/commit/b754abdeab204949510500ccb1b845b7ad143542>

Yep, looks like it can definitely land in django-postgres [1] leveraging Anssi work on custom lookup [2]

will give it a go during the weekend and tell you if it worked

[1] https://github.com/zacharyvoase/django-postgres
[2] https://code.djangoproject.com/ticket/16187

Marco

[Marco Paolini]

Looks like it can definitely be used, I'll give it a try

[Anssi Kääriäinen]

On 18 tammi, 17:35, Marco Paolini <markopaol...@gmail.com> wrote:
> > For examples of how to define lookups, see:
> >https://github.com/akaariai/django/compare/ticket_16187_new#L13L72and

> > the Lookup object itself athttps://github.com/akaariai/django/compare/ticket_16187_new#L6R50.

> > Warning about the code - it will likely contain totally misleading
>
> Looks like it can definitely be used, I'll give it a try

Trying it would be really, really valuable. The current situation is
such that the main ORM lookups are converted, and there is one trivial
test lookup in examples. This doesn't tell at all how the lookup
system will work in real world usage.

If somebody is brave enough to try writing JSON lookups, that is the
use cases I am most interested in.

Also, testing that existing code isn't broken by the changes is
interesting information.

Of course, it is more than likely that the lookup API will still need
to change...

- Anssi

[Anssi Kääriäinen]

> Trying it would be really, really valuable. The current situation is
> such that the main ORM lookups are converted, and there is one trivial
> test lookup in examples. This doesn't tell at all how the lookup
> system will work in real world usage.

I went ahead and converted django-hstore [https://github.com/jordanm/
django-hstore] to use the current version of custom lookups code
(against commit https://github.com/akaariai/django/commit/842727b324717f5b36d647e6b6433c4664b2143f).

I was able to convert the hstore code so that it passes all tests, and
in addition some new features were added:
query one key:
hstorefield__somekey__lt='someval' (where lt can be replaced by
any lookup supported by CharField).
SQL generated:
hstorefield -> somekey < 'someval'.

query one key, converted as date:
hstorefield__somekey__asdate__lt=date.now() (again, any lookup
supported by DateField will do).
SQL generated:
(hstorefield -> somekey)::DATE < '2013-01-24'

query one key, convert to date, extract year from the date:
hstorefield__somekey__asdate__year__lt=2013 (again, any lookup
supported by DateField will do).
SQL:
(EXTRACT YEAR FROM (hstorefield -> somekey)::DATE) < '2013-01-24'

query if one key is in another queryset:

hstorefield__somekey__in=SomeModel.objects.values_list('somecharfield')
SQL:
hstorefield -> somekey IN (SELECT ... FROM somemodel ...)

It seems that aiming for 100% backwards compatibility for all current
custom lookups isn't a sane goal. django-hstore plays with WhereNode
internals and there is zero chance to make that work. So, aiming for
support of simple existing lookups is a good goal, and aiming for
somewhat easy upgrade path where it is possible to create code that
works both on 1.5 and 1.6 is a good idea (the changed django-hstore
does work on 1.5, too).

The django-hstore changes can be found from
https://github.com/akaariai/django-hstore/compare/custom_lookups.

Other notes:
- Better inbuilt support for nested lookups is needed. The code is
currently non-DRY.
- I want to do queries like:
qs.order_by('hstorefield__somekey__asdate') (or aggregate, annotate,
maybe values_list and values, F())
- Better support for extension registration in database backends. I
think the following hooks/signals would be useful for db backends:
on_module_init(), on_db_create(), on_connect(). Signals seem to be a
good way to do this, though on_module_init() might be sent too
early... maybe pre_first_connect()?.

I think the experiment shows that the custom lookup code is powerful
enough. Nested XML/JSON queries should be possible. For me it is
really hard to say if the API is intuitive enough...

I really wish for testing of the current code. Now is an excellent
time to check that the code will work for your use case! I am very
happy to help in testing (#django-dev is a good channel to contact me,
or just email my akaariai gmail address). Unfortunately I haven't yet
written any docs, but will try to do so soon.

- Anssi