#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: 0 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** =|| > ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**|| > ||{{{NULLIF}}}||Returns {{{NULL}}} if the first argument equals the > second.||✔||✔||✔||✔||[https://github.com/django/django/pull/9543 > 9543]|| > ||||||||||||||= **Math** =|| > ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**|| > ||{{{ABS}}}||Returns the absolute > value.||✔||✔||✔||✔||[https://github.com/django/django/pull/9577 > 9577], [https://github.com/django/django/pull/9622 9622]|| > ||{{{ACOS}}}||Returns the > arccosine.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{ASIN}}}||Returns the > arcsine.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{ATAN}}}||Returns the > arctangent.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{ATAN2}}}||Returns the arctangent of the two variables passed to > it.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{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}}}||Returns the cosine expressed in > radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{COT}}}||Returns the > cotangent.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{DEGREES}}}||Returns a numeric expression converted from radians to > degrees.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{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}}}||Returns the largest integer value that is not greater than > a numeric > expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{LOG}}}||Returns the natural logarithm of a numeric > expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{MOD}}}||Returns the remainder of one expression by diving by another > expression.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{PI}}}||Returns the value of > π||✔||-||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{POWER}}}||Returns the value of one expression raised to the power of > another > expression||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{RADIANS}}}||Returns the value of an expression converted from > degrees to > radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{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}}}||Returns the sine given in > radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{SQRT}}}||Returns the square > root.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||{{{TAN}}}||Returns the tangent expressed in > radians.||✔||✔||✔||✔**^1^**||[https://github.com/django/django/pull/9622 > 9622]|| > ||||||||||||||= **Text** =|| > ||**Name**||**Description**||**PostgreSQL**||**Oracle**||**MySQL**||**SQLite**||**PR**|| > ||{{{ASCII}}}||Returns numeric value of left-most character. (Equivalent > to {{{ord()}}} in > Python.)||✔||✔||✔||{{{UNICODE}}}||[https://github.com/django/django/pull/9583 > 9583]|| > ||{{{CHR}}}||Character with the given > code||✔||✔||{{{CHAR}}}||{{{CHAR}}}||[https://github.com/django/django/pull/9583 > 9583]|| > ||{{{LEFT}}}||Returns the leftmost number of characters as > specified||✔||✔**^2^**||✔||✔**^2^**||[https://github.com/django/django/pull/9583 > 9583]|| > ||{{{LPAD}}}||Returns the string argument, left-padded with the specified > string||✔||✔||✔||-||[https://github.com/django/django/pull/9798 > 9798]|| > ||{{{LTRIM}}}||Removes leading > spaces||✔||✔||✔||✔||[https://github.com/django/django/pull/9220 > 9220]|| > ||{{{MD5}}}||Calculates the MD5 hash of string, returning the result in > hexadecimal||✔||-||-||✔**^1^**||-|| > ||{{{REPEAT}}}||Repeats a string the specified number of > times||✔||-||✔||✔**^1^**||[https://github.com/django/django/pull/9808 > 9808]|| > ||{{{REPLACE}}}||Replaces occurrences of a specified > string||✔||✔||✔||✔||[https://github.com/django/django/pull/9539 9539] > [[span(style=font-size:75%, (Merged))]]|| > ||{{{REVERSE}}}||Reverse the characters in a > string||✔||-||✔||✔**^1^**||-|| > ||{{{RIGHT}}}||Returns the specified rightmost number of > characters||✔||✔**^2^**||✔||✔**^2^**||[https://github.com/django/django/pull/9583 > 9583]|| > ||{{{RPAD}}}||Appends string the specified number of > times||✔||✔||✔||-||[https://github.com/django/django/pull/9798 9798]|| > ||{{{RTRIM}}}||Removes trailing > spaces||✔||✔||✔||✔||[https://github.com/django/django/pull/9220 > 9220]|| > ||{{{TRIM}}}||Removes leading and trailing > spaces||✔||✔||✔||✔||[https://github.com/django/django/pull/9220 > 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}}}. 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.||✔||✔||✔||✔||[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^**||✔||✔**^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^**||[https://github.com/django/django/pull/9808 9808]|| ||{{{Replace}}}||{{{REPLACE}}}||Replaces occurrences of a specified string||✔||✔||✔||✔||~~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||✔||✔||✔||✔**^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)}}}. -- -- Ticket URL: <https://code.djangoproject.com/ticket/28643#comment:38> 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.620822304c1dad0e3df03d223ad46b56%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.