#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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.||✔||✔||✔||✔||[https://github.com/django/django/pull/9543
> 9543]||
> ||||||||||||||||= **Math** =||
> ||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||{{{Abs}}}||{{{ABS}}}||Returns the absolute
> value.||✔||✔||✔||✔||~~9622~~||
> ||{{{ACos}}}||{{{ACOS}}}||Returns the
> arccosine.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{ASin}}}||{{{ASIN}}}||Returns the
> arcsine.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{ATan}}}||{{{ATAN}}}||Returns the
> arctangent.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{ATan2}}}||{{{ATAN2}}}||Returns the arctangent of the two variables
> passed to it.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Ceil}}}||{{{CEILING}}}||Returns the smallest integer value that is
> not less than a numeric
> expression||✔||{{{CEIL}}}||✔||✔**^1^**||~~9622~~||
> ||{{{Cos}}}||{{{COS}}}||Returns the cosine expressed in
> radians.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Cot}}}||{{{COT}}}||Returns the
> cotangent.||✔||✔**^5^**||✔||✔**^1^**||~~9622~~||
> ||{{{Degrees}}}||{{{DEGREES}}}||Returns a numeric expression converted
> from radians to degrees.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Exp}}}||{{{EXP}}}||Returns the base of the natural logarithm (e)
> raised to the power of a numeric
> expression.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Floor}}}||{{{FLOOR}}}||Returns the largest integer value that is not
> greater than a numeric expression.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Ln}}}||{{{LN}}}||Returns the natural logarithm of a numeric
> expression.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Log}}}||{{{LOG(B, X)}}}||Returns the logarithm of a numeric
> expression.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Mod}}}||{{{MOD}}}||Returns the remainder of one expression by diving
> by another expression.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Pi}}}||{{{PI}}}||Returns the value of
> π||✔||✔**^4^**||✔||✔**^1^**||~~9622~~||
> ||{{{Power}}}||{{{POWER}}}||Returns the value of one expression raised to
> the power of another expression||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Radians}}}||{{{RADIANS}}}||Returns the value of an expression
> converted from degrees to radians.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Round}}}||{{{ROUND}}}||Returns a numeric expression rounded to an
> integer.\\Can be used to round an expression to a number of decimal
> points||✔||✔||✔||✔||~~9622~~||
> ||{{{Sin}}}||{{{SIN}}}||Returns the sine given in
> radians.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Sqrt}}}||{{{SQRT}}}||Returns the square
> root.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||{{{Tan}}}||{{{TAN}}}||Returns the tangent expressed in
> radians.||✔||✔||✔||✔**^1^**||~~9622~~||
> ||||||||||||||||= **Text** =||
> ||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
> ||{{{Ord}}}||{{{ASCII}}}||Returns numeric value of left-most character.
> (Equivalent to {{{ord()}}} in
> Python.)||✔||✔||✔||{{{UNICODE}}}||~~9583~~||
> ||{{{Chr}}}||{{{CHR}}}||Character with the given
> code||✔||✔||{{{CHAR}}}||{{{CHAR}}}||~~9583~~||
> ||{{{Left}}}||{{{LEFT}}}||Returns the leftmost number of characters as
> specified||✔||✔**^2, 6^**||✔||✔**^2^**||~~9583~~||
> ||{{{LPad}}}||{{{LPAD}}}||Returns the string argument, left-padded with
> the specified string||✔||✔||✔||✔**^1^**||~~9798~~||
> ||{{{LTrim}}}||{{{LTRIM}}}||Removes leading
> spaces||✔||✔||✔||✔||~~9220~~||
> ||{{{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^**||~~9808~~||
> ||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified
> string||✔||✔**^6^**||✔||✔||~~9539~~||
> ||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
> string||✔||-||✔||✔**^1^**||-||
> ||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
> characters||✔||✔**^2^**||✔||✔**^2^**||~~9583~~||
> ||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
> times||✔||✔**^6^**||✔||✔**^1^**||~~9798~~||
> ||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
> spaces||✔||✔||✔||✔||~~9220~~||
> ||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
> spaces||✔||✔||✔||✔||~~9220~~||
>
> - **^1^** Function can be easily supported on SQLite with a
> [https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
> user defined function].
> - **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
> - **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
> - **^4^** Behaviour can be emulated by directly substituting constant
> {{{math.pi}}}.
> - **^5^** Behaviour can be emulated by using {{{1 / TAN(X)}}}.
> - **^6^** Doesn't work properly with multibyte characters sets on Oracle.
New description:
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.||✔||✔||✔||✔||[https://github.com/django/django/pull/9543
9543]||
||||||||||||||||= **Math** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{Abs}}}||{{{ABS}}}||Returns the absolute
value.||✔||✔||✔||✔||~~9622~~||
||{{{ACos}}}||{{{ACOS}}}||Returns the
arccosine.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{ASin}}}||{{{ASIN}}}||Returns the
arcsine.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{ATan}}}||{{{ATAN}}}||Returns the
arctangent.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{ATan2}}}||{{{ATAN2}}}||Returns the arctangent of the two variables
passed to it.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Ceil}}}||{{{CEILING}}}||Returns the smallest integer value that is
not less than a numeric
expression||✔||{{{CEIL}}}||✔||✔**^1^**||~~9622~~||
||{{{Cos}}}||{{{COS}}}||Returns the cosine expressed in
radians.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Cot}}}||{{{COT}}}||Returns the
cotangent.||✔||✔**^5^**||✔||✔**^1^**||~~9622~~||
||{{{Degrees}}}||{{{DEGREES}}}||Returns a numeric expression converted
from radians to degrees.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Exp}}}||{{{EXP}}}||Returns the base of the natural logarithm (e)
raised to the power of a numeric
expression.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Floor}}}||{{{FLOOR}}}||Returns the largest integer value that is not
greater than a numeric expression.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Ln}}}||{{{LN}}}||Returns the natural logarithm of a numeric
expression.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Log}}}||{{{LOG(B, X)}}}||Returns the logarithm of a numeric
expression.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Mod}}}||{{{MOD}}}||Returns the remainder of one expression by diving
by another expression.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Pi}}}||{{{PI}}}||Returns the value of
π||✔||✔**^4^**||✔||✔**^1^**||~~9622~~||
||{{{Power}}}||{{{POWER}}}||Returns the value of one expression raised to
the power of another expression||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Radians}}}||{{{RADIANS}}}||Returns the value of an expression
converted from degrees to radians.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Round}}}||{{{ROUND}}}||Returns a numeric expression rounded to an
integer.\\Can be used to round an expression to a number of decimal
points||✔||✔||✔||✔||~~9622~~||
||{{{Sin}}}||{{{SIN}}}||Returns the sine given in
radians.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Sqrt}}}||{{{SQRT}}}||Returns the square
root.||✔||✔||✔||✔**^1^**||~~9622~~||
||{{{Tan}}}||{{{TAN}}}||Returns the tangent expressed in
radians.||✔||✔||✔||✔**^1^**||~~9622~~||
||||||||||||||||= **Text** =||
||**Django**||**SQL**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**||
||{{{Ord}}}||{{{ASCII}}}||Returns numeric value of left-most character.
(Equivalent to {{{ord()}}} in
Python.)||✔||✔||✔||{{{UNICODE}}}||~~9583~~||
||{{{Chr}}}||{{{CHR}}}||Character with the given
code||✔||✔||{{{CHAR}}}||{{{CHAR}}}||~~9583~~||
||{{{Left}}}||{{{LEFT}}}||Returns the leftmost number of characters as
specified||✔||✔**^2, 6^**||✔||✔**^2^**||~~9583~~||
||{{{LPad}}}||{{{LPAD}}}||Returns the string argument, left-padded with
the specified string||✔||✔||✔||✔**^1^**||~~9798~~||
||{{{LTrim}}}||{{{LTRIM}}}||Removes leading
spaces||✔||✔||✔||✔||~~9220~~||
||{{{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^**||~~9808~~||
||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified
string||✔||✔**^6^**||✔||✔||~~9539~~||
||{{{Reverse}}}||{{{REVERSE}}}||Reverse the characters in a
string||✔||✔**^6, 7^**||✔||✔**^1^**||-||
||{{{Right}}}||{{{RIGHT}}}||Returns the specified rightmost number of
characters||✔||✔**^2^**||✔||✔**^2^**||~~9583~~||
||{{{RPad}}}||{{{RPAD}}}||Appends string the specified number of
times||✔||✔**^6^**||✔||✔**^1^**||~~9798~~||
||{{{RTrim}}}||{{{RTRIM}}}||Removes trailing
spaces||✔||✔||✔||✔||~~9220~~||
||{{{Trim}}}||{{{TRIM}}}||Removes leading and trailing
spaces||✔||✔||✔||✔||~~9220~~||
- **^1^** Function can be easily supported on SQLite with a
[https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
user defined function].
- **^2^** Behaviour can be emulated by using {{{SUBSTR}}}.
- **^3^** Behaviour can be emulated by using {{{LENGTH}}} and {{{RPAD}}}.
- **^4^** Behaviour can be emulated by directly substituting constant
{{{math.pi}}}.
- **^5^** Behaviour 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.
--
Comment (by Nick Pope):
Added a [https://github.com/django/django/pull/10827 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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:47>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.33920849da6bb00efbbdc6e9b73238ad%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
