codekingpro/portable-devtools

1"""2Package for SQL functions wrappers3"""4 5from __future__ import annotations6 7from pypika import Field8from pypika.enums import SqlTypes9from pypika.terms import AggregateFunction, Function, LiteralValue, Star, Term10from pypika.utils import builder11 12__author__ = "Timothy Heys"13__email__ = "theys@kayak.com"14 15 16class DistinctOptionFunction(AggregateFunction):17    def __init__(self, name, *args, **kwargs):18        alias = kwargs.get("alias")19        super().__init__(name, *args, alias=alias)20        self._distinct = False21 22    def get_function_sql(self, **kwargs):23        s = super().get_function_sql(**kwargs)24 25        n = len(self.name) + 126        if self._distinct:27            return s[:n] + "DISTINCT " + s[n:]28        return s29 30    @builder31    def distinct(self):32        self._distinct = True33 34 35class Count(DistinctOptionFunction):36    def __init__(self, param: str | Term, alias: str | None = None) -> None:37        is_star = isinstance(param, str) and "*" == param38        super().__init__("COUNT", Star() if is_star else param, alias=alias)39 40 41# Arithmetic Functions42class Sum(DistinctOptionFunction):43    def __init__(self, term: Term, alias: str | None = None):44        super().__init__("SUM", term, alias=alias)45 46 47class Avg(AggregateFunction):48    def __init__(self, term: Term, alias: str | None = None):49        super().__init__("AVG", term, alias=alias)50 51 52class Min(AggregateFunction):53    def __init__(self, term: Term, alias: str | None = None):54        super().__init__("MIN", term, alias=alias)55 56 57class Max(AggregateFunction):58    def __init__(self, term: Term, alias: str | None = None):59        super().__init__("MAX", term, alias=alias)60 61 62class Std(AggregateFunction):63    def __init__(self, term: Term, alias: str | None = None):64        super().__init__("STD", term, alias=alias)65 66 67class StdDev(AggregateFunction):68    def __init__(self, term: Term, alias: str | None = None):69        super().__init__("STDDEV", term, alias=alias)70 71 72class Abs(AggregateFunction):73    def __init__(self, term: Term, alias: str | None = None):74        super().__init__("ABS", term, alias=alias)75 76 77class First(AggregateFunction):78    def __init__(self, term: Term, alias: str | None = None):79        super().__init__("FIRST", term, alias=alias)80 81 82class Last(AggregateFunction):83    def __init__(self, term: Term, alias: str | None = None):84        super().__init__("LAST", term, alias=alias)85 86 87class Sqrt(Function):88    def __init__(self, term: Term, alias: str | None = None):89        super().__init__("SQRT", term, alias=alias)90 91 92class Floor(Function):93    def __init__(self, term: Term, alias: str | None = None):94        super().__init__("FLOOR", term, alias=alias)95 96 97class ApproximatePercentile(AggregateFunction):98    def __init__(self, term, percentile, alias=None):99        super().__init__("APPROXIMATE_PERCENTILE", term, alias=alias)100        self.percentile = float(percentile)101 102    def get_special_params_sql(self, **kwargs):103        return "USING PARAMETERS percentile={percentile}".format(percentile=self.percentile)104 105 106# Type Functions107class Cast(Function):108    def __init__(self, term, as_type, alias=None):109        super().__init__("CAST", term, alias=alias)110        self.as_type = as_type111 112    def get_special_params_sql(self, **kwargs):113        type_sql = self.as_type.get_sql(**kwargs) if hasattr(self.as_type, "get_sql") else str(self.as_type).upper()114        return "AS {type}".format(type=type_sql)115 116 117class Convert(Function):118    def __init__(self, term, encoding, alias=None):119        super().__init__("CONVERT", term, alias=alias)120        self.encoding = encoding121 122    def get_special_params_sql(self, **kwargs):123        return "USING {type}".format(type=self.encoding.value)124 125 126class ToChar(Function):127    def __init__(self, term, as_type, alias=None):128        super().__init__("TO_CHAR", term, as_type, alias=alias)129 130 131class Signed(Cast):132    def __init__(self, term: Term, alias: str | None = None):133        super().__init__(term, SqlTypes.SIGNED, alias=alias)134 135 136class Unsigned(Cast):137    def __init__(self, term: Term, alias: str | None = None):138        super().__init__(term, SqlTypes.UNSIGNED, alias=alias)139 140 141class Date(Function):142    def __init__(self, term: Term, alias: str | None = None):143        super().__init__("DATE", term, alias=alias)144 145 146class DateDiff(Function):147    def __init__(self, interval, start_date, end_date, alias=None):148        super().__init__("DATEDIFF", interval, start_date, end_date, alias=alias)149 150 151class TimeDiff(Function):152    def __init__(self, start_time, end_time, alias=None):153        super().__init__("TIMEDIFF", start_time, end_time, alias=alias)154 155 156class DateAdd(Function):157    def __init__(self, date_part, interval, term: Term, alias: str | None = None):158        date_part = getattr(date_part, "value", date_part)159        super().__init__("DATE_ADD", LiteralValue(date_part), interval, term, alias=alias)160 161 162class ToDate(Function):163    def __init__(self, value, format_mask, alias=None):164        super().__init__("TO_DATE", value, format_mask, alias=alias)165 166 167class Timestamp(Function):168    def __init__(self, term: Term, alias: str | None = None):169        super().__init__("TIMESTAMP", term, alias=alias)170 171 172class TimestampAdd(Function):173    def __init__(self, date_part, interval, term: Term, alias: str | None = None):174        date_part = getattr(date_part, 'value', date_part)175        super().__init__("TIMESTAMPADD", LiteralValue(date_part), interval, term, alias=alias)176 177 178# String Functions179class Ascii(Function):180    def __init__(self, term: Term, alias: str | None = None):181        super().__init__("ASCII", term, alias=alias)182 183 184class NullIf(Function):185    def __init__(self, term, condition, **kwargs):186        super().__init__("NULLIF", term, condition, **kwargs)187 188 189class Bin(Function):190    def __init__(self, term: Term, alias: str | None = None):191        super().__init__("BIN", term, alias=alias)192 193 194class Concat(Function):195    def __init__(self, *terms, **kwargs):196        super().__init__("CONCAT", *terms, **kwargs)197 198 199class Insert(Function):200    def __init__(self, term, start, stop, subterm, alias=None):201        term, start, stop, subterm = [term for term in [term, start, stop, subterm]]202        super().__init__("INSERT", term, start, stop, subterm, alias=alias)203 204 205class Length(Function):206    def __init__(self, term: Term, alias: str | None = None):207        super().__init__("LENGTH", term, alias=alias)208 209 210class Upper(Function):211    def __init__(self, term: Term, alias: str | None = None):212        super().__init__("UPPER", term, alias=alias)213 214 215class Lower(Function):216    def __init__(self, term: Term, alias: str | None = None):217        super().__init__("LOWER", term, alias=alias)218 219 220class Substring(Function):221    def __init__(self, term, start, stop, alias=None):222        super().__init__("SUBSTRING", term, start, stop, alias=alias)223 224 225class Reverse(Function):226    def __init__(self, term: Term, alias: str | None = None):227        super().__init__("REVERSE", term, alias=alias)228 229 230class Trim(Function):231    def __init__(self, term: Term, alias: str | None = None):232        super().__init__("TRIM", term, alias=alias)233 234 235class SplitPart(Function):236    def __init__(self, term, delimiter, index, alias=None):237        super().__init__("SPLIT_PART", term, delimiter, index, alias=alias)238 239 240class RegexpMatches(Function):241    def __init__(self, term, pattern, modifiers=None, alias=None):242        super().__init__("REGEXP_MATCHES", term, pattern, modifiers, alias=alias)243 244 245class RegexpLike(Function):246    def __init__(self, term, pattern, modifiers=None, alias=None):247        super().__init__("REGEXP_LIKE", term, pattern, modifiers, alias=alias)248 249 250class Replace(Function):251    def __init__(self, term, find_string, replace_with, alias=None):252        super().__init__("REPLACE", term, find_string, replace_with, alias=alias)253 254 255# Date/Time Functions256class Now(Function):257    def __init__(self, alias=None):258        super().__init__("NOW", alias=alias)259 260 261class UtcTimestamp(Function):262    def __init__(self, alias=None):263        super().__init__("UTC_TIMESTAMP", alias=alias)264 265 266class CurTimestamp(Function):267    def __init__(self, alias=None):268        super().__init__("CURRENT_TIMESTAMP", alias=alias)269 270    def get_function_sql(self, **kwargs):271        # CURRENT_TIMESTAMP takes no arguments, so the SQL to generate is quite272        # simple.  Note that empty parentheses have been omitted intentionally.273        return "CURRENT_TIMESTAMP"274 275 276class CurDate(Function):277    def __init__(self, alias=None):278        super().__init__("CURRENT_DATE", alias=alias)279 280 281class CurTime(Function):282    def __init__(self, alias=None):283        super().__init__("CURRENT_TIME", alias=alias)284 285 286class Extract(Function):287    def __init__(self, date_part, field, alias=None):288        date_part = getattr(date_part, "value", date_part)289        super().__init__("EXTRACT", LiteralValue(date_part), alias=alias)290        self.field = field291 292    def get_special_params_sql(self, **kwargs):293        return "FROM {field}".format(field=self.field.get_sql(**kwargs))294 295 296# Null Functions297class IsNull(Function):298    def __init__(self, term: Term, alias: str | None = None):299        super().__init__("ISNULL", term, alias=alias)300 301 302class Coalesce(Function):303    def __init__(self, term, *default_values, **kwargs):304        super().__init__("COALESCE", term, *default_values, **kwargs)305 306 307class IfNull(Function):308    def __init__(self, condition, term, **kwargs):309        super().__init__("IFNULL", condition, term, **kwargs)310 311 312class NVL(Function):313    def __init__(self, condition, term: Term, alias: str | None = None):314        super().__init__("NVL", condition, term, alias=alias)315