#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
-------------------------------------+-------------------------------------
Description changed by felixxm:
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.||✔||✔||✔||✔||~~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||✔||✔**^8^**||✔||✔**^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^**||~~10827~~||
> ||`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^** Can be emulated by using `SUBSTR`.
> - **^3^** Can be emulated by using `LENGTH` and `RPAD`.
> - **^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.
> - **^8^** Can be supported by using `STANDARD_HASH` 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.||✔||✔||✔||✔||~~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||✔||✔**^8^**||✔||✔**^1^**||11004||
||`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^**||~~10827~~||
||`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^** Can be emulated by using `SUBSTR`.
- **^3^** Can be emulated by using `LENGTH` and `RPAD`.
- **^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.
- **^8^** Can be supported by using `STANDARD_HASH` on Oracle.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:61>
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.e2dc5cdab7702895ced32aa894bfa3de%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
