concatenate two arguments together this is used by concat because not all backend databases support more than two arguments use concatws with an empty separator so that nulls are ignored null on either side results in null for expression wrap with coalesce concatenate text fields together backends that result in an entire null expression when any arguments are null will wrap each argument in coalesce functions to ensure a nonnull result wrap pairs of expressions in successive concat functions exp a b c d concatpaira concatpairb concatpairc d expression the name of a field or an expression returning a string length the number of characters to return from the start of the string return the number of characters in the expression function length lookupname length outputfield integerfield def asmysqlself compiler connection extracontext return super assql compiler connection functioncharlength extracontext class lowertransform function lower lookupname lower class lpadfunc function lpad outputfield charfield def initself expression length filltextvalue extra if not hasattrlength resolveexpression and length is not none and length 0 raise valueerror length must be greater or equal to 0 super initexpression length filltext extra class ltrimtransform function ltrim lookupname ltrim class md5oraclehashmixin transform function md5 lookupname md5 class ordtransform function ascii lookupname ord outputfield integerfield def asmysqlself compiler connection extracontext return super assqlcompiler connection functionord extracontext def assqliteself compiler connection extracontext return super assqlcompiler connection functionunicode extracontext class repeatfunc function repeat outputfield charfield def initself expression number extra if not hasattrnumber resolveexpression and number is not none and number 0 raise valueerror number must be greater or equal to 0 super initexpression number extra def asoracleself compiler connection extracontext expression number self sourceexpressions length none if number is none else lengthexpression number rpad rpadexpression length expression return rpad assqlcompiler connection extracontext class replacefunc function replace def initself expression text replacementvalue extra super initexpression text replacement extra class reversetransform function reverse lookupname reverse def asoracleself compiler connection extracontext reverse in oracle is undocumented and doesn t support multibyte strings use a special subquery instead sql params super assql compiler connection template select listaggs within group order by n desc from select level n substrexpressionss level 1 s from dual connect by level lengthexpressionss group by expressionss extracontext return sql params 3 class rightleft function right def getsubstrself return substr self sourceexpressions0 self sourceexpressions1 value1 self sourceexpressions1 class rpadlpad function rpad class rtrimtransform function rtrim lookupname rtrim class sha1oraclehashmixin postgresqlshamixin transform function sha1 lookupname sha1 class sha224mysqlsha2mixin postgresqlshamixin transform function sha224 lookupname sha224 def asoracleself compiler connection extracontext raise notsupportederrorsha224 is not supported on oracle class sha256mysqlsha2mixin oraclehashmixin postgresqlshamixin transform function sha256 lookupname sha256 class sha384mysqlsha2mixin oraclehashmixin postgresqlshamixin transform function sha384 lookupname sha384 class sha512mysqlsha2mixin oraclehashmixin postgresqlshamixin transform function sha512 lookupname sha512 class strindexfunc function instr arity 2 outputfield integerfield def aspostgresqlself compiler connection extracontext return super assqlcompiler connection functionstrpos extracontext class substrfunc function substring outputfield charfield def initself expression pos lengthnone extra if not hasattrpos resolveexpression if pos 1 raise valueerror pos must be greater than 0 expressions expression pos if length is not none expressions appendlength super initexpressions extra def assqliteself compiler connection extracontext return super assqlcompiler connection functionsubstr extracontext def asoracleself compiler connection extracontext return super assqlcompiler connection functionsubstr extracontext class trimtransform function trim lookupname trim class uppertransform function upper lookupname upper concatenate two arguments together this is used by concat because not all backend databases support more than two arguments use concat_ws with an empty separator so that nulls are ignored null on either side results in null for expression wrap with coalesce concatenate text fields together backends that result in an entire null expression when any arguments are null will wrap each argument in coalesce functions to ensure a non null result wrap pairs of expressions in successive concat functions exp a b c d concatpair a concatpair b concatpair c d expression the name of a field or an expression returning a string length the number of characters to return from the start of the string return the number of characters in the expression reverse in oracle is undocumented and doesn t support multi byte strings use a special subquery instead return a positive integer corresponding to the 1 indexed position of the first occurrence of a substring inside another string or 0 if the substring is not found expression the name of a field or an expression returning a string pos an integer 0 or an expression returning an integer length an optional number of characters to return
from django.db import NotSupportedError
from django.db.models.expressions import Func, Value
from django.db.models.fields import CharField, IntegerField, TextField
from django.db.models.functions import Cast, Coalesce
from django.db.models.lookups import Transform


class MySQLSHA2Mixin:
    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            template="SHA2(%%(expressions)s, %s)" % self.function[3:],
            **extra_context,
        )


class OracleHashMixin:
    def as_oracle(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            template=(
                "LOWER(RAWTOHEX(STANDARD_HASH(UTL_I18N.STRING_TO_RAW("
                "%(expressions)s, 'AL32UTF8'), '%(function)s')))"
            ),
            **extra_context,
        )


class PostgreSQLSHAMixin:
    def as_postgresql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            template="ENCODE(DIGEST(%(expressions)s, '%(function)s'), 'hex')",
            function=self.function.lower(),
            **extra_context,
        )


class Chr(Transform):
    function = "CHR"
    lookup_name = "chr"
    output_field = CharField()

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            function="CHAR",
            template="%(function)s(%(expressions)s USING utf16)",
            **extra_context,
        )

    def as_oracle(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler,
            connection,
            template="%(function)s(%(expressions)s USING NCHAR_CS)",
            **extra_context,
        )

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="CHAR", **extra_context)


class ConcatPair(Func):
    

    function = "CONCAT"

    def pipes_concat_sql(self, compiler, connection, **extra_context):
        coalesced = self.coalesce()
        return super(ConcatPair, coalesced).as_sql(
            compiler,
            connection,
            template="%(expressions)s",
            arg_joiner=" || ",
            **extra_context,
        )

    as_sqlite = pipes_concat_sql

    def as_postgresql(self, compiler, connection, **extra_context):
        c = self.copy()
        c.set_source_expressions(
            [
                expression
                if isinstance(expression.output_field, (CharField, TextField))
                else Cast(expression, TextField())
                for expression in c.get_source_expressions()
            ]
        )
        return c.pipes_concat_sql(compiler, connection, **extra_context)

    def as_mysql(self, compiler, connection, **extra_context):
        
        return super().as_sql(
            compiler,
            connection,
            function="CONCAT_WS",
            template="%(function)s('', %(expressions)s)",
            **extra_context,
        )

    def coalesce(self):
        
        c = self.copy()
        c.set_source_expressions(
            [
                Coalesce(expression, Value(""))
                for expression in c.get_source_expressions()
            ]
        )
        return c


class Concat(Func):
    

    function = None
    template = "%(expressions)s"

    def __init__(self, *expressions, **extra):
        if len(expressions) < 2:
            raise ValueError("Concat must take at least two expressions")
        paired = self._paired(expressions, output_field=extra.get("output_field"))
        super().__init__(paired, **extra)

    def _paired(self, expressions, output_field):
        
        
        
        if len(expressions) == 2:
            return ConcatPair(*expressions, output_field=output_field)
        return ConcatPair(
            expressions[0],
            self._paired(expressions[1:], output_field=output_field),
            output_field=output_field,
        )


class Left(Func):
    function = "LEFT"
    arity = 2
    output_field = CharField()

    def __init__(self, expression, length, **extra):
        
        if not hasattr(length, "resolve_expression"):
            if length < 1:
                raise ValueError("'length' must be greater than 0.")
        super().__init__(expression, length, **extra)

    def get_substr(self):
        return Substr(self.source_expressions[0], Value(1), self.source_expressions[1])

    def as_oracle(self, compiler, connection, **extra_context):
        return self.get_substr().as_oracle(compiler, connection, **extra_context)

    def as_sqlite(self, compiler, connection, **extra_context):
        return self.get_substr().as_sqlite(compiler, connection, **extra_context)


class Length(Transform):
    

    function = "LENGTH"
    lookup_name = "length"
    output_field = IntegerField()

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection, function="CHAR_LENGTH", **extra_context
        )


class Lower(Transform):
    function = "LOWER"
    lookup_name = "lower"


class LPad(Func):
    function = "LPAD"
    output_field = CharField()

    def __init__(self, expression, length, fill_text=Value(" "), **extra):
        if (
            not hasattr(length, "resolve_expression")
            and length is not None
            and length < 0
        ):
            raise ValueError("'length' must be greater or equal to 0.")
        super().__init__(expression, length, fill_text, **extra)


class LTrim(Transform):
    function = "LTRIM"
    lookup_name = "ltrim"


class MD5(OracleHashMixin, Transform):
    function = "MD5"
    lookup_name = "md5"


class Ord(Transform):
    function = "ASCII"
    lookup_name = "ord"
    output_field = IntegerField()

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="ORD", **extra_context)

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="UNICODE", **extra_context)


class Repeat(Func):
    function = "REPEAT"
    output_field = CharField()

    def __init__(self, expression, number, **extra):
        if (
            not hasattr(number, "resolve_expression")
            and number is not None
            and number < 0
        ):
            raise ValueError("'number' must be greater or equal to 0.")
        super().__init__(expression, number, **extra)

    def as_oracle(self, compiler, connection, **extra_context):
        expression, number = self.source_expressions
        length = None if number is None else Length(expression) * number
        rpad = RPad(expression, length, expression)
        return rpad.as_sql(compiler, connection, **extra_context)


class Replace(Func):
    function = "REPLACE"

    def __init__(self, expression, text, replacement=Value(""), **extra):
        super().__init__(expression, text, replacement, **extra)


class Reverse(Transform):
    function = "REVERSE"
    lookup_name = "reverse"

    def as_oracle(self, compiler, connection, **extra_context):
        
        
        sql, params = super().as_sql(
            compiler,
            connection,
            template=(
                "(SELECT LISTAGG(s) WITHIN GROUP (ORDER BY n DESC) FROM "
                "(SELECT LEVEL n, SUBSTR(%(expressions)s, LEVEL, 1) s "
                "FROM DUAL CONNECT BY LEVEL <= LENGTH(%(expressions)s)) "
                "GROUP BY %(expressions)s)"
            ),
            **extra_context,
        )
        return sql, params * 3


class Right(Left):
    function = "RIGHT"

    def get_substr(self):
        return Substr(
            self.source_expressions[0],
            self.source_expressions[1] * Value(-1),
            self.source_expressions[1],
        )


class RPad(LPad):
    function = "RPAD"


class RTrim(Transform):
    function = "RTRIM"
    lookup_name = "rtrim"


class SHA1(OracleHashMixin, PostgreSQLSHAMixin, Transform):
    function = "SHA1"
    lookup_name = "sha1"


class SHA224(MySQLSHA2Mixin, PostgreSQLSHAMixin, Transform):
    function = "SHA224"
    lookup_name = "sha224"

    def as_oracle(self, compiler, connection, **extra_context):
        raise NotSupportedError("SHA224 is not supported on Oracle.")


class SHA256(MySQLSHA2Mixin, OracleHashMixin, PostgreSQLSHAMixin, Transform):
    function = "SHA256"
    lookup_name = "sha256"


class SHA384(MySQLSHA2Mixin, OracleHashMixin, PostgreSQLSHAMixin, Transform):
    function = "SHA384"
    lookup_name = "sha384"


class SHA512(MySQLSHA2Mixin, OracleHashMixin, PostgreSQLSHAMixin, Transform):
    function = "SHA512"
    lookup_name = "sha512"


class StrIndex(Func):
    

    function = "INSTR"
    arity = 2
    output_field = IntegerField()

    def as_postgresql(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="STRPOS", **extra_context)


class Substr(Func):
    function = "SUBSTRING"
    output_field = CharField()

    def __init__(self, expression, pos, length=None, **extra):
        
        if not hasattr(pos, "resolve_expression"):
            if pos < 1:
                raise ValueError("'pos' must be greater than 0")
        expressions = [expression, pos]
        if length is not None:
            expressions.append(length)
        super().__init__(*expressions, **extra)

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="SUBSTR", **extra_context)

    def as_oracle(self, compiler, connection, **extra_context):
        return super().as_sql(compiler, connection, function="SUBSTR", **extra_context)


class Trim(Transform):
    function = "TRIM"
    lookup_name = "trim"


class Upper(Transform):
    function = "UPPER"
    lookup_name = "upper"