Opened 16 months ago
Last modified 5 days ago
#28643 assigned New feature
Complete the ORM Function Library
Reported by: | Matthew Pava | Owned by: | Nick Pope |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | master |
Severity: | Normal | Keywords: | |
Cc: | josh.smeaton@…, felixxm, Shai Berger, Adam (Chainz) Johnson, Thomas Lagae | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I was surprised to learn that we didn't have a StrIndex function until version 2, and yet we had Substr since at least version 1.8. I wonder how users were using Substr without also finding a use for StrIndex this whole time. Anyway, since we seem to be adding these functions one at a time, why don't we work on trying to get the built-ins implemented in one sweep instead?
We may even want to split the documentation page (https://docs.djangoproject.com/en/dev/ref/models/database-functions/) into further categories with String functions and Numeric functions.
This is just a sample checklist, with corresponding attributes to which backend has them available.
Comparison | |||||||
---|---|---|---|---|---|---|---|
Django | SQL | Description | PostgreSQL | Oracle | MySQL | SQLite | PR |
NullIf | NULLIF | Returns NULL if the first argument equals the second. | ✔ | ✔ | ✔ | ✔ | |
Math | |||||||
Django | SQL | Description | PostgreSQL | Oracle | MySQL | SQLite | PR |
Abs | ABS | Returns the absolute value. | ✔ | ✔ | ✔ | ✔ | |
ACos | ACOS | Returns the arccosine. | ✔ | ✔ | ✔ | ✔^{1} | |
ASin | ASIN | Returns the arcsine. | ✔ | ✔ | ✔ | ✔^{1} | |
ATan | ATAN | Returns the arctangent. | ✔ | ✔ | ✔ | ✔^{1} | |
ATan2 | ATAN2 | Returns the arctangent of the two variables passed to it. | ✔ | ✔ | ✔ | ✔^{1} | |
Ceil | CEILING | Returns the smallest integer value that is not less than a numeric expression | ✔ | CEIL | ✔ | ✔^{1} | |
Cos | COS | Returns the cosine expressed in radians. | ✔ | ✔ | ✔ | ✔^{1} | |
Cot | COT | Returns the cotangent. | ✔ | ✔^{5} | ✔ | ✔^{1} | |
Degrees | DEGREES | Returns a numeric expression converted from radians to degrees. | ✔ | ✔ | ✔ | ✔^{1} | |
Exp | EXP | Returns the base of the natural logarithm (e) raised to the power of a numeric expression. | ✔ | ✔ | ✔ | ✔^{1} | |
Floor | FLOOR | Returns the largest integer value that is not greater than a numeric expression. | ✔ | ✔ | ✔ | ✔^{1} | |
Ln | LN | Returns the natural logarithm of a numeric expression. | ✔ | ✔ | ✔ | ✔^{1} | |
Log | LOG(B, X) | Returns the logarithm of a numeric expression. | ✔ | ✔ | ✔ | ✔^{1} | |
Mod | MOD | Returns the remainder of one expression by diving by another expression. | ✔ | ✔ | ✔ | ✔^{1} | |
Pi | PI | Returns the value of π | ✔ | ✔^{4} | ✔ | ✔^{1} | |
Power | POWER | Returns the value of one expression raised to the power of another expression | ✔ | ✔ | ✔ | ✔^{1} | |
Radians | RADIANS | Returns the value of an expression converted from degrees to radians. | ✔ | ✔ | ✔ | ✔^{1} | |
Round | ROUND | Returns a numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points | ✔ | ✔ | ✔ | ✔ | |
Sin | SIN | Returns the sine given in radians. | ✔ | ✔ | ✔ | ✔^{1} | |
Sqrt | SQRT | Returns the square root. | ✔ | ✔ | ✔ | ✔^{1} | |
Tan | TAN | Returns the tangent expressed in radians. | ✔ | ✔ | ✔ | ✔^{1} | |
Text | |||||||
Django | SQL | Description | PostgreSQL | Oracle | MySQL | SQLite | PR |
Ord | ASCII | Returns numeric value of left-most character. (Equivalent to ord() in Python.) | ✔ | ✔ | ✔ | UNICODE | |
Chr | CHR | Character with the given code | ✔ | ✔ | CHAR | CHAR | |
Left | LEFT | Returns the leftmost number of characters as specified | ✔ | ✔^{2, 6} | ✔ | ✔^{2} | |
LPad | LPAD | Returns the string argument, left-padded with the specified string | ✔ | ✔ | ✔ | ✔^{1} | |
LTrim | LTRIM | Removes leading spaces | ✔ | ✔ | ✔ | ✔ | |
MD5 | MD5 | Calculates the MD5 hash of string, returning the result in hexadecimal | ✔ | - | - | ✔^{1} | - |
Repeat | REPEAT | Repeats a string the specified number of times | ✔ | ✔^{3} | ✔ | ✔^{1} | |
Replace | REPLACE | Replaces occurrences of a specified string | ✔ | ✔^{6} | ✔ | ✔ | |
Reverse | REVERSE | Reverse the characters in a string | ✔ | ✔^{6, 7} | ✔ | ✔^{1} | |
Right | RIGHT | Returns the specified rightmost number of characters | ✔ | ✔^{2} | ✔ | ✔^{2} | |
RPad | RPAD | Appends string the specified number of times | ✔ | ✔^{6} | ✔ | ✔^{1} | |
RTrim | RTRIM | Removes trailing spaces | ✔ | ✔ | ✔ | ✔ | |
Trim | TRIM | Removes leading and trailing spaces | ✔ | ✔ | ✔ | ✔ |
- ^{1} Function can be easily supported on SQLite with a user defined function.
- ^{2} Can be emulated by using
SUBSTR
. - ^{3} Can be emulated by using
LENGTH
andRPAD
. - ^{4} Can be emulated by directly substituting constant
math.pi
. - ^{5} Can be emulated by using
1 / TAN(X)
. - ^{6} Doesn't work properly with multibyte characters sets on Oracle.
- ^{7} The
REVERSE
function is undocumented on Oracle.
Change History (57)
comment:1 Changed 16 months ago by
comment:2 Changed 16 months ago by
Cc: | josh.smeaton@… added |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 Changed 16 months ago by
Cc: | felixxm added |
---|
comment:4 Changed 16 months ago by
Owner: | changed from nobody to JunyiJ |
---|---|
Status: | new → assigned |
comment:5 Changed 16 months ago by
For whoever wants to begin here, I think a good plan of attack will be a PR per function or per a small set of functions. Then the commit message would be something like "Refs #28643 -- Added X, Y, Z functions".
That way we can get through smaller pieces at a time, without such a large burden being placed on any one individual. We could also get some newer contributors to help in this way. Each function on its own should be a relatively easy thing to implement. A higher level task of re-organising the layout into functions/string functions/number may be helpful to get the ticket rolling.
comment:6 follow-up: 7 Changed 16 months ago by
I did find this third-party utility that handles specific PostgreSQL functions. I wonder if there are other backends that they could be ported to.
https://github.com/hypertrack/django-pg-utils
And I also wonder how all of this connects with specific PostgreSQL aggregate functions already builtin to Django:
https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/aggregates/
comment:7 Changed 16 months ago by
Replying to Matthew Pava:
I did find this third-party utility that handles specific PostgreSQL functions. I wonder if there are other backends that they could be ported to.
https://github.com/hypertrack/django-pg-utils
From a quick look, these things are already supported by Django.
comment:8 Changed 16 months ago by
Description: | modified (diff) |
---|
comment:9 Changed 15 months ago by
I prepared patch to reorganize database functions docs and code (PR). I think we should organize code and doc as follows (bolded functions doesn't exist):
docs/ref/models/database-functions.txt
sectionComparison and conversion functions
(django/db/models/functions/comparison.py
):- Cast, Coalesce, NullIf, Greatest, Least;
docs/ref/models/database-functions.txt
sectionDate Functions
(django/db/models/functions/datetime.py
):- Extract, ExtractDay, ExtractHour, ExtractMinute, ExtractMonth, ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay, ExtractYear, Now, Trunc, TruncDate, TruncDay, TruncHour, TruncMinute, TruncMonth, TruncQuarter, TruncSecond, TruncTime, TruncYear";
docs/ref/models/database-functions.txt
sectionMath Functions
(django/db/models/functions/math.py
):- Abs, ACos, ASin, ATan, ATan2, Ceil, Cos, Cot, Exp, Floor, Log, Mod, Power, Round, Sin, Sqrt, Tan;
docs/ref/models/database-functions.txt
sectionText Functions
(django/db/models/functions/text.py
):- Ascii, Chr, Concat, ConcatPair, Length, LPad, Lower, LTrim, Replace, RPad, RTrim, StrIndex, Substr, Trim, Upper;
docs/ref/models/database-functions.txt
sectionWindow Functions
(django/db/models/functions/window.py
):- CumeDist, DenseRank, FirstValue, Lag, LastValue, Lead, NthValue, Ntile, PercentRank, Rank, RowNumber.
Headers and functions in docs and code should be organized alphabetically. Thanks Tim Graham for suggestions.
I'm not convince that following functions should be implemented because there not supported on all databases:
- LEFT(), MD5(), REPEAT(), REVERSE(), RIGHT(), DEGREES(), PI(), RADIANS().
comment:10 Changed 15 months ago by
The "Miscellaneous" group is sort of bothering me.
Cast
, Coalesce
, Greatest
, Least
sound like candidates for a section called something like "Comparison and conversion functions"
Ascii
and Chr
sound like they could be "text" related.
Now
sounds like a candidate for "Date Functions".
comment:12 follow-up: 13 Changed 15 months ago by
In regards to Left() and Right(), they are available in PostgreSQL and MySQL, but not in SQLite or Oracle. However, you can emulate their functionality using the corresponding Substr function in the database backend. I would hate to see us leave those out for that reason alone. At the same time, developers could use the Substr across all databases instead of using Left and Right.
I was also wondering about the use of Trim. Python doesn't use that term; instead, it uses strip. I wonder if that really matters. Some developers may look for a strip functionality in Django ORM not realizing that the databases use the term Trim. Perhaps that could be clarified in the documentation.
comment:13 Changed 15 months ago by
Cc: | Shai Berger added |
---|
Replying to Matthew Pava:
I was also wondering about the use of Trim. Python doesn't use that term; instead, it uses strip. I wonder if that really matters. Some developers may look for a strip functionality in Django ORM not realizing that the databases use the term Trim. Perhaps that could be clarified in the documentation.
Unless there is some other use for the term strip
in the databases, I'd consider "documenting" this by giving an alias:
class Trim(Transform): # ... Strip = Trim # Give Trim a more Pythonic name
comment:17 Changed 15 months ago by
PR - Ltrim
, Rtrim
, and Trim
with aliases Lstrip
, Rstrip
, and Strip
, respectively.
comment:18 follow-up: 19 Changed 15 months ago by
And, of course, there is a Python lstrip and rstrip as well.
Lstrip = Ltrim # Give Ltrim a more Pythonic name Rstrip = Rtrim # Give Rtrim a more Pythonic name
comment:19 Changed 15 months ago by
Replying to Matthew Pava:
And, of course, there is a Python lstrip and rstrip as well.
Lstrip = Ltrim # Give Ltrim a more Pythonic name Rstrip = Rtrim # Give Rtrim a more Pythonic name
I added these aliases.
comment:20 Changed 14 months ago by
Has patch: | set |
---|
comment:21 Changed 14 months ago by
Cc: | Adam (Chainz) Johnson added |
---|
comment:22 Changed 13 months ago by
Description: | modified (diff) |
---|
I formatted the list into a table so we could see better what we can do to address this ticket. It looks like SQLite is missing much functionality that the other backends support, especially in the math department.
I also suggest implementing Left and Right, but for the unsupported backends to use the corresponding Substr function.
comment:23 Changed 13 months ago by
Description: | modified (diff) |
---|
Updated description and comment:9 to include NullIf
.
See the following links:
comment:24 Changed 13 months ago by
Description: | modified (diff) |
---|
comment:25 Changed 13 months ago by
Description: | modified (diff) |
---|
comment:27 Changed 12 months ago by
Cc: | Thomas Lagae added |
---|---|
Description: | modified (diff) |
Add link to Abs
PR.
I am a first time contributor so any feedback is much appreciated.
comment:28 Changed 12 months ago by
Description: | modified (diff) |
---|
comment:29 Changed 12 months ago by
When passing a non-numeric value into the Abs
function, the behaviour differs between databases. I think this will also be the case for other math functions. Should we catch non-numeric values and throw an error or just document that this behaviour is undefined and differs between databases?
comment:30 Changed 12 months ago by
I added a link to the Ord, Chr, Left, and Right PR.
I just started thinking more about the Left and Right aspects, and it occurred to me that a more pythonic solution would be the implementation of slicing instead of having these separate database functions. This would also change the implementation of Substr since that would no longer be necessary either.
comment:32 Changed 12 months ago by
Description: | modified (diff) |
---|
comment:35 Changed 10 months ago by
Description: | modified (diff) |
---|
comment:37 Changed 10 months ago by
Description: | modified (diff) |
---|
comment:38 Changed 10 months ago by
Description: | modified (diff) |
---|
comment:39 Changed 10 months ago by
Description: | modified (diff) |
---|
comment:41 Changed 10 months ago by
Description: | modified (diff) |
---|
comment:42 Changed 7 months ago by
Patch needs improvement: | set |
---|
comment:44 Changed 5 months ago by
Description: | modified (diff) |
---|
comment:46 Changed 11 days ago by
Owner: | changed from JunyiJ to Nick Pope |
---|---|
Patch needs improvement: | unset |
I have added a new PR that cleans up some of the compatibility mixins added as part of the math function work so that they can be reused.
It also makes use of these mixins for aggregates defined in django.db.models.aggregates
.
comment:47 Changed 11 days ago by
Description: | modified (diff) |
---|
Added a PR implementing Reverse()
.
Note that REVERSE
exists for Oracle but is undocumented and works on bytes, not multi-byte strings. (Support table updated in ticket description.)
comment:48 Changed 11 days ago by
Description: | modified (diff) |
---|
comment:50 Changed 7 days ago by
Description: | modified (diff) |
---|
comment:57 Changed 5 days ago by
Has patch: | unset |
---|
I had hoped that the 3rd party community would take care of providing "Function Packs" for each of the backends. But functions are fairly easy to create in your own project if you need them so I guess few people have bothered to group them into a distributable package.
I would prefer that the django ecosystem had a standard group of function expressions that can be used across backends, rather than a bunch of similar but not quite the same implementations. Then 3rd party libraries can depend on the known good versions and everyone is better off. Since the community hasn't seemed to provide such a library (that I'm aware of), I think it's fine for Django to do so.
Splitting the module up into string/numeric/date types sounds fine. Let's take inspiration from postgres and other db vendor docs so navigating them is familiar. Let's begin with functions that have standard support across our 4 backends, then look into what's left over. If we can approximate support by combining other functions we can do that. Otherwise if a particular function only has support for one or two backends we can consider ignoring it or implementing in a contrib module.