Range type adaptation implemented
От | Daniele Varrazzo |
---|---|
Тема | Range type adaptation implemented |
Дата | |
Msg-id | CA+mi_8ahX2cQtv201k91VZYFrOjMR94U16ZhgLJZp7-4RMDtJA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Range type adaptation implemented
(Karsten Hilbert <Karsten.Hilbert@gmx.net>)
|
Список | psycopg |
I have implemented adaptation between Postgres range types and Python. Usage is documented at: <http://initd.org/psycopg/docs/extras.html#range-data-types> There is a basic Range class, completely working (it is not an abstract class) but with not enough information for adaptation. A few subclasses allow mapping to and from the db, for instance NumericRange map to int4range, int8range and numrange. Adaptation of builtin range types is supported out-of-the-box. Adaptation of user-defined ranges is supported via the function register_range() that queries the database, if required creates a new range type and registers all the adapters. The usual chain of fallbacks is provided if the user doesn't want to query the database etc. The Range class doesn't re-implements the database range operator and doesn't support canonicalization because we cannot emulate fully the Postgres model. The funniest surprise I've had during the development was an error creating the object TextRange('#', '$') in the database. It should be possible as >>> ord('#'), ord('$') (35, 36) but PG insisted that "range lower bound must be less than or equal to range upper bound". Well, turns out that: postgres=# select ascii('#') < ascii('$'), '#' < '$'; ?column? | ?column? ----------+---------- t | f (1 row) Morale: it's between [hard and impossible) to canonicalize ranges the way postgres does, hence it's better to leave only the database responsible for it, otherwise we may end up with ranges valid for postgres and not for python and viceversa. On adaptation: numeric ranges are adapted using a syntax '[10,20)': this allows to have a single range type for all numerical types. Other ranges, such as the date range and in general user-defined types, are adapted using a syntax such as daterange('2012-01-01', '2012-12-31', '(]'), with the values adapted recursively. I would have preferred to implement all the ranges in the second way, because it relies on already defined adapters for the representation and returns typed values instead of string literals. I've had to adapt the number ranges as literals because there is no cast defined across postgres range types: postgres=# select '[10,20)'::int4range::numrange; ERROR: cannot cast type int4range to numrange nor AFAIK there is a constructor for an "untyped range" such as range(lower, upper, bounds). So if what we have is Python numbers, e.g. 10, 20, we cannot choose whether we want an int4range or an int8range, and Postgres won't forgive the error with a cast. The shortcoming of using a literal is that in case of ambiguity postgres will refuse to guess, but this can be solved with a ::cast on the placeholder. OTOH the lack of a generic postgres range constructor function forces us to have different Range subclasses for date, timestamp, timestamptz and all the custom ranges, because in the sql representation we have to pass the type name. I've thought about using a single Range with a single adapter looking at the type of the bounds contained by the python objects and making and using the proper PG type name, but this wouldn't work for ranges containing no bound (unbound or empty) and in that case it should resort to a literal. This is bad as a query would end up having sometimes a daterange(to, from), sometimes just a literal '(,)' or 'empty' so it may work all the time and fail unexpectedly on specific values for cast problems in the database. The above docs include the other changes implemented in these days: customizable composite adaptation and json support. My devel has still the features in separate branches to be merged together. Comments, tests and docs proofreading are well accepted as usual. Thank you very much. -- Daniele
В списке psycopg по дате отправления: