#28643: Complete the ORM Function Library
-------------------------------------+-------------------------------------
Reporter: Matthew Pava | Owner: JunyiJ
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Nick Pope:
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.||✔||✔||✔||✔||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{ACos}}}||{{{ACOS}}}||Returns the
> arccosine.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{ASin}}}||{{{ASIN}}}||Returns the
> arcsine.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{ATan}}}||{{{ATAN}}}||Returns the
> arctangent.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{ATan2}}}||{{{ATAN2}}}||Returns the arctangent of the two variables
> passed to
> it.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Ceil}}}||{{{CEILING}}}||Returns the smallest integer value that is
> not less than a numeric
> expression||✔||{{{CEIL}}}||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Cos}}}||{{{COS}}}||Returns the cosine expressed in
> radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Cot}}}||{{{COT}}}||Returns the
> cotangent.||✔||✔**^5^**||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Degrees}}}||{{{DEGREES}}}||Returns a numeric expression converted
> from radians to
> degrees.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Exp}}}||{{{EXP}}}||Returns the base of the natural logarithm (e)
> raised to the power of a numeric
> expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Floor}}}||{{{FLOOR}}}||Returns the largest integer value that is not
> greater than a numeric
> expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Ln}}}||{{{LN}}}||Returns the natural logarithm of a numeric
> expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Log}}}||{{{LOG(B, X)}}}||Returns the logarithm of a numeric
> expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Mod}}}||{{{MOD}}}||Returns the remainder of one expression by diving
> by another
> expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Pi}}}||{{{PI}}}||Returns the value of
> π||✔||✔**^4^**||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Power}}}||{{{POWER}}}||Returns the value of one expression raised to
> the power of another
> expression||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Radians}}}||{{{RADIANS}}}||Returns the value of an expression
> converted from degrees to
> radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Round}}}||{{{ROUND}}}||Returns a numeric expression rounded to an
> integer.\\Can be used to round an expression to a number of decimal
> points||✔||✔||✔||✔||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Sin}}}||{{{SIN}}}||Returns the sine given in
> radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Sqrt}}}||{{{SQRT}}}||Returns the square
> root.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 9622]||
> ||{{{Tan}}}||{{{TAN}}}||Returns the tangent expressed in
> radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622
> 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||✔||-||✔||✔**^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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:44>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.0efa01bc15806d8e01d545b27b162300%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
