sqlglot.generators.exasol

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator
  6from sqlglot.dialects.dialect import (
  7    DATE_ADD_OR_SUB,
  8    Dialect,
  9    groupconcat_sql,
 10    no_last_day_sql,
 11    rename_func,
 12    strposition_sql,
 13    timestrtotime_sql,
 14    timestamptrunc_sql,
 15)
 16from sqlglot.errors import UnsupportedError
 17from sqlglot.generator import unsupported_args
 18from sqlglot.optimizer.scope import build_scope, find_in_scope
 19from sqlglot.parsers.exasol import DATE_UNITS
 20
 21
 22def _sha2_sql(self: ExasolGenerator, expression: exp.SHA2) -> str:
 23    length = expression.text("length")
 24    func_name = "HASH_SHA256" if length == "256" else "HASH_SHA512"
 25    return self.func(func_name, expression.this)
 26
 27
 28def _date_diff_sql(self: ExasolGenerator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
 29    unit = expression.text("unit").upper() or "DAY"
 30
 31    if unit not in DATE_UNITS:
 32        self.unsupported(f"'{unit}' is not supported in Exasol.")
 33        return self.function_fallback_sql(expression)
 34
 35    return self.func(f"{unit}S_BETWEEN", expression.this, expression.expression)
 36
 37
 38# https://docs.exasol.com/db/latest/sql/select.htm#:~:text=If%20you%20have,local.x%3E10
 39def _add_local_prefix_for_aliases(expression: exp.Expr, dialect: Dialect) -> exp.Expr:
 40    def _key(ident: exp.Identifier) -> str:
 41        return dialect.normalize_identifier(ident.copy()).this
 42
 43    if isinstance(expression, exp.Select):
 44        aliases: dict[str, exp.Identifier] = {
 45            _key(alias): alias
 46            for sel in expression.selects
 47            if isinstance(sel, exp.Alias) and (alias := sel.args.get("alias"))
 48        }
 49
 50        table = expression.find(exp.Table)
 51        table_ident = table.this if table else None
 52
 53        if (
 54            table_ident
 55            and table_ident.name.upper() == "LOCAL"
 56            and not bool(table_ident.args.get("quoted"))
 57        ):
 58            table_ident.replace(exp.to_identifier(table_ident.name.upper(), quoted=True))
 59
 60        def prefix_local(node: exp.Expr, visible_aliases: dict[str, exp.Identifier]) -> exp.Expr:
 61            if isinstance(node, exp.Column) and not node.table:
 62                alias = visible_aliases.get(_key(node.this))
 63                if alias is not None:
 64                    return exp.Column(
 65                        this=alias,
 66                        table=exp.to_identifier("LOCAL", quoted=False),
 67                    )
 68            return node
 69
 70        for key in ("where", "group", "having"):
 71            if arg := expression.args.get(key):
 72                expression.set(key, arg.transform(lambda node: prefix_local(node, aliases)))
 73
 74        seen_aliases: dict[str, exp.Identifier] = {}
 75        new_selects: list[exp.Expr] = []
 76        for sel in expression.selects:
 77            if isinstance(sel, exp.Alias):
 78                inner = sel.this.transform(lambda node: prefix_local(node, seen_aliases))
 79                sel.set("this", inner)
 80
 81                if alias_node := sel.args.get("alias"):
 82                    seen_aliases[_key(alias_node)] = alias_node
 83                new_selects.append(sel)
 84            else:
 85                new_selects.append(sel.transform(lambda node: prefix_local(node, seen_aliases)))
 86        expression.set("expressions", new_selects)
 87
 88    return expression
 89
 90
 91def _trunc_sql(
 92    self: ExasolGenerator, kind: str, expression: exp.DateTrunc | exp.TimestampTrunc
 93) -> str:
 94    unit_expr = expression.args.get("unit")
 95    unit = (
 96        self.weekstart_name(unit_expr)
 97        if isinstance(unit_expr, exp.WeekStart)
 98        else expression.text("unit")
 99    )
100    node = expression.this.this if isinstance(expression.this, exp.Cast) else expression.this
101    expr_sql = self.sql(node)
102    if isinstance(node, exp.Literal) and node.is_string:
103        expr_sql = (
104            f"{kind} '{node.this.replace('T', ' ')}'"
105            if kind == "TIMESTAMP"
106            else f"DATE '{node.this}'"
107        )
108    return f"DATE_TRUNC('{unit}', {expr_sql})"
109
110
111def _date_trunc_sql(self: ExasolGenerator, expression: exp.DateTrunc) -> str:
112    return _trunc_sql(self, "DATE", expression)
113
114
115def _timestamp_trunc_sql(
116    self: ExasolGenerator, expression: exp.DateTrunc | exp.TimestampTrunc
117) -> str:
118    return _trunc_sql(self, "TIMESTAMP", expression)
119
120
121def is_case_insensitive(node: exp.Expr) -> bool:
122    return isinstance(node, exp.Collate) and node.text("expression").upper() == "UTF8_LCASE"
123
124
125def _substring_index_sql(self: ExasolGenerator, expression: exp.SubstringIndex) -> str:
126    this = expression.this
127    delimiter = expression.args["delimiter"]
128    count_node = expression.args["count"]
129    count_sql = self.sql(expression, "count")
130    num = count_node.to_py() if count_node.is_number else 0
131
132    haystack_sql = self.sql(this)
133    if num == 0:
134        return self.func("SUBSTR", haystack_sql, "1", "0")
135
136    from_right = num < 0
137    direction = "-1" if from_right else "1"
138    occur = self.func("ABS", count_sql) if from_right else count_sql
139
140    delimiter_sql = self.sql(delimiter)
141
142    position = self.func(
143        "INSTR",
144        self.func("LOWER", haystack_sql) if is_case_insensitive(this) else haystack_sql,
145        self.func("LOWER", delimiter_sql) if is_case_insensitive(delimiter) else delimiter_sql,
146        direction,
147        occur,
148    )
149    nullable_pos = self.func("NULLIF", position, "0")
150
151    if from_right:
152        start = self.func(
153            "NVL", f"{nullable_pos} + {self.func('LENGTH', delimiter_sql)}", direction
154        )
155        return self.func("SUBSTR", haystack_sql, start)
156
157    length = self.func("NVL", f"{nullable_pos} - 1", self.func("LENGTH", haystack_sql))
158    return self.func("SUBSTR", haystack_sql, direction, length)
159
160
161# https://docs.exasol.com/db/latest/sql/select.htm#:~:text=The%20select_list%20defines%20the%20columns%20of%20the%20result%20table.%20If%20*%20is%20used%2C%20all%20columns%20are%20listed.%20You%20can%20use%20an%20expression%20like%20t.*%20to%20list%20all%20columns%20of%20the%20table%20t%2C%20the%20view%20t%2C%20or%20the%20object%20with%20the%20table%20alias%20t.
162def _qualify_unscoped_star(expression: exp.Expr) -> exp.Expr:
163    """
164    Exasol doesn't support a bare * alongside other select items, so we rewrite it
165    Rewrite: SELECT *, <other> FROM <Table>
166    Into: SELECT T.*, <other> FROM <Table> AS T
167    """
168
169    if not isinstance(expression, exp.Select):
170        return expression
171
172    select_expressions = expression.expressions or []
173
174    def is_bare_star(expr: exp.Expr) -> bool:
175        return isinstance(expr, exp.Star) and expr.this is None
176
177    has_other_expression = False
178    bare_star_expr: exp.Expr | None = None
179    for expr in select_expressions:
180        has_bare_star = is_bare_star(expr)
181        if has_bare_star and bare_star_expr is None:
182            bare_star_expr = expr
183        elif not has_bare_star:
184            has_other_expression = True
185        if bare_star_expr and has_other_expression:
186            break
187
188    if not (bare_star_expr and has_other_expression):
189        return expression
190
191    scope = build_scope(expression)
192
193    if not scope or not scope.selected_sources:
194        return expression
195
196    table_identifiers: list[exp.Identifier] = []
197
198    for source_name, (source_expr, _) in scope.selected_sources.items():
199        ident = (
200            source_expr.this.copy()
201            if isinstance(source_expr, exp.Table) and isinstance(source_expr.this, exp.Identifier)
202            else exp.to_identifier(source_name)
203        )
204        table_identifiers.append(ident)
205
206    qualified_star_columns = [
207        exp.Column(this=bare_star_expr.copy(), table=ident) for ident in table_identifiers
208    ]
209
210    new_select_expressions: list[exp.Expr] = []
211
212    for select_expr in select_expressions:
213        new_select_expressions.extend(qualified_star_columns) if is_bare_star(
214            select_expr
215        ) else new_select_expressions.append(select_expr)
216
217    expression.set("expressions", new_select_expressions)
218    return expression
219
220
221def _add_date_sql(self: ExasolGenerator, expression: DATE_ADD_OR_SUB) -> str:
222    interval = expression.expression if isinstance(expression.expression, exp.Interval) else None
223
224    unit = (
225        (interval.text("unit") or "DAY").upper()
226        if interval is not None
227        else (expression.text("unit") or "DAY").upper()
228    )
229
230    if unit not in DATE_UNITS:
231        self.unsupported(f"'{unit}' is not supported in Exasol.")
232        return self.function_fallback_sql(expression)
233
234    offset_expr: exp.Expr = expression.expression
235    if interval is not None:
236        offset_expr = interval.this
237
238    if isinstance(expression, exp.DateSub):
239        offset_expr = exp.Neg(this=offset_expr)
240
241    return self.func(f"ADD_{unit}S", expression.this, offset_expr)
242
243
244def _group_by_all(expression: exp.Expr) -> exp.Expr:
245    if not isinstance(expression, exp.Select):
246        return expression
247
248    group = expression.args.get("group")
249    if not group or not group.args.get("all"):
250        return expression
251
252    if expression.is_star:
253        if any(find_in_scope(proj, exp.AggFunc) for proj in expression.expressions):
254            raise UnsupportedError(
255                "GROUP BY ALL with star projection and aggregates is not supported by Exasol"
256            )
257        expression.set("distinct", exp.Distinct())
258        expression.set("group", None)
259        return expression
260
261    group_positions = [
262        exp.Literal.number(i)
263        for i, proj in enumerate(expression.expressions, start=1)
264        if not find_in_scope(proj, exp.AggFunc)
265    ]
266
267    if not group_positions:
268        expression.set("group", None)
269        return expression
270
271    group.set("expressions", group_positions)
272    group.set("all", None)
273
274    return expression
275
276
277class ExasolGenerator(generator.Generator):
278    SELECT_KINDS: tuple[str, ...] = ()
279    TRY_SUPPORTED = False
280    SUPPORTS_UESCAPE = False
281    SUPPORTS_DECODE_CASE = False
282
283    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
284
285    # https://docs.exasol.com/db/latest/sql_references/data_types/datatypedetails.htm#StringDataType
286    STRING_TYPE_MAPPING: t.ClassVar = {
287        exp.DType.BLOB: "VARCHAR",
288        exp.DType.LONGBLOB: "VARCHAR",
289        exp.DType.LONGTEXT: "VARCHAR",
290        exp.DType.MEDIUMBLOB: "VARCHAR",
291        exp.DType.MEDIUMTEXT: "VARCHAR",
292        exp.DType.TINYBLOB: "VARCHAR",
293        exp.DType.TINYTEXT: "VARCHAR",
294        # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
295        exp.DType.TEXT: "LONG VARCHAR",
296        exp.DType.VARBINARY: "VARCHAR",
297    }
298
299    # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
300    TYPE_MAPPING = {
301        **generator.Generator.TYPE_MAPPING,
302        **STRING_TYPE_MAPPING,
303        exp.DType.TINYINT: "SMALLINT",
304        exp.DType.MEDIUMINT: "INT",
305        exp.DType.DECIMAL32: "DECIMAL",
306        exp.DType.DECIMAL64: "DECIMAL",
307        exp.DType.DECIMAL128: "DECIMAL",
308        exp.DType.DECIMAL256: "DECIMAL",
309        exp.DType.DATETIME: "TIMESTAMP",
310        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
311        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
312        exp.DType.TIMESTAMPNTZ: "TIMESTAMP",
313    }
314
315    def select_sql(self, expression: exp.Select) -> str:
316        processed = _qualify_unscoped_star(expression)
317        processed = _add_local_prefix_for_aliases(processed, self.dialect)
318        processed = _group_by_all(processed)
319        return super().select_sql(t.cast(exp.Select, processed))
320
321    def datatype_sql(self, expression: exp.DataType) -> str:
322        # Exasol supports a fixed default precision of 3 for TIMESTAMP WITH LOCAL TIME ZONE
323        # and does not allow specifying a different custom precision
324        if expression.is_type(exp.DType.TIMESTAMPLTZ):
325            return "TIMESTAMP WITH LOCAL TIME ZONE"
326
327        return super().datatype_sql(expression)
328
329    TRANSFORMS = {
330        **generator.Generator.TRANSFORMS,
331        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/every.htm
332        exp.All: rename_func("EVERY"),
333        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_and.htm
334        exp.BitwiseAnd: rename_func("BIT_AND"),
335        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_or.htm
336        exp.BitwiseOr: rename_func("BIT_OR"),
337        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_not.htm
338        exp.BitwiseNot: rename_func("BIT_NOT"),
339        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_lshift.htm
340        exp.BitwiseLeftShift: rename_func("BIT_LSHIFT"),
341        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_rshift.htm
342        exp.BitwiseRightShift: rename_func("BIT_RSHIFT"),
343        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_xor.htm
344        exp.BitwiseXor: rename_func("BIT_XOR"),
345        exp.CurrentSchema: lambda *_: "CURRENT_SCHEMA",
346        exp.DateDiff: _date_diff_sql,
347        exp.DateAdd: _add_date_sql,
348        exp.TsOrDsAdd: _add_date_sql,
349        exp.DateSub: _add_date_sql,
350        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/div.htm#DIV
351        exp.IntDiv: rename_func("DIV"),
352        exp.TsOrDsDiff: _date_diff_sql,
353        exp.DateTrunc: _date_trunc_sql,
354        exp.DayOfWeek: lambda self, e: f"CAST(TO_CHAR({self.sql(e, 'this')}, 'D') AS INTEGER)",
355        exp.DatetimeTrunc: timestamptrunc_sql(),
356        exp.GroupConcat: lambda self, e: groupconcat_sql(self, e, on_overflow=True),
357        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/edit_distance.htm#EDIT_DISTANCE
358        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
359            rename_func("EDIT_DISTANCE")
360        ),
361        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/mod.htm
362        exp.Mod: rename_func("MOD"),
363        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/from_posix_time.htm
364        exp.UnixToTime: lambda self, e: self.func("FROM_POSIX_TIME", e.this),
365        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/rank.htm
366        exp.Rank: unsupported_args("expressions")(lambda *_: "RANK()"),
367        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/dense_rank.htm
368        exp.DenseRank: unsupported_args("expressions")(lambda *_: "DENSE_RANK()"),
369        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_substr.htm
370        exp.RegexpExtract: unsupported_args("parameters", "group")(rename_func("REGEXP_SUBSTR")),
371        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_replace.htm
372        exp.RegexpReplace: unsupported_args("modifiers")(rename_func("REGEXP_REPLACE")),
373        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/var_pop.htm
374        exp.VariancePop: rename_func("VAR_POP"),
375        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/approximate_count_distinct.htm
376        exp.ApproxDistinct: unsupported_args("accuracy")(rename_func("APPROXIMATE_COUNT_DISTINCT")),
377        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_char%20(datetime).htm
378        exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
379        exp.ToChar: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
380        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
381        exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
382        exp.TimeStrToTime: timestrtotime_sql,
383        exp.TimestampTrunc: _timestamp_trunc_sql,
384        exp.StrToTime: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
385        exp.CurrentUser: lambda *_: "CURRENT_USER",
386        exp.AtTimeZone: lambda self, e: self.func(
387            "CONVERT_TZ",
388            e.this,
389            "'UTC'",
390            e.args.get("zone"),
391        ),
392        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/instr.htm
393        exp.StrPosition: lambda self, e: strposition_sql(
394            self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
395        ),
396        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha%5B1%5D.htm#HASH_SHA%5B1%5D
397        exp.SHA: rename_func("HASH_SHA"),
398        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha256.htm
399        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha512.htm
400        exp.SHA2: _sha2_sql,
401        exp.MD5: rename_func("HASH_MD5"),
402        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hashtype_md5.htm
403        exp.MD5Digest: rename_func("HASHTYPE_MD5"),
404        # https://docs.exasol.com/db/latest/sql/create_view.htm
405        exp.CommentColumnConstraint: lambda self, e: f"COMMENT IS {self.sql(e, 'this')}",
406        exp.SubstringIndex: _substring_index_sql,
407        exp.WeekOfYear: rename_func("WEEK"),
408        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
409        exp.Date: rename_func("TO_DATE"),
410        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_timestamp.htm
411        exp.Timestamp: rename_func("TO_TIMESTAMP"),
412        exp.Quarter: lambda self, e: f"CEIL(MONTH(TO_DATE({self.sql(e, 'this')}))/3)",
413        exp.LastDay: no_last_day_sql,
414    }
415
416    # https://docs.exasol.com/db/7.1/sql_references/system_tables/metadata/exa_sql_keywords.htm
417    RESERVED_KEYWORDS = {
418        "absolute",
419        "action",
420        "add",
421        "after",
422        "all",
423        "allocate",
424        "alter",
425        "and",
426        "any",
427        "append",
428        "are",
429        "array",
430        "as",
431        "asc",
432        "asensitive",
433        "assertion",
434        "at",
435        "attribute",
436        "authid",
437        "authorization",
438        "before",
439        "begin",
440        "between",
441        "bigint",
442        "binary",
443        "bit",
444        "blob",
445        "blocked",
446        "bool",
447        "boolean",
448        "both",
449        "by",
450        "byte",
451        "call",
452        "called",
453        "cardinality",
454        "cascade",
455        "cascaded",
456        "case",
457        "casespecific",
458        "cast",
459        "catalog",
460        "chain",
461        "char",
462        "character",
463        "character_set_catalog",
464        "character_set_name",
465        "character_set_schema",
466        "characteristics",
467        "check",
468        "checked",
469        "clob",
470        "close",
471        "coalesce",
472        "collate",
473        "collation",
474        "collation_catalog",
475        "collation_name",
476        "collation_schema",
477        "column",
478        "commit",
479        "condition",
480        "connect_by_iscycle",
481        "connect_by_isleaf",
482        "connect_by_root",
483        "connection",
484        "constant",
485        "constraint",
486        "constraint_state_default",
487        "constraints",
488        "constructor",
489        "contains",
490        "continue",
491        "control",
492        "convert",
493        "corresponding",
494        "create",
495        "cs",
496        "csv",
497        "cube",
498        "current",
499        "current_cluster",
500        "current_cluster_uid",
501        "current_date",
502        "current_path",
503        "current_role",
504        "current_schema",
505        "current_session",
506        "current_statement",
507        "current_time",
508        "current_timestamp",
509        "current_user",
510        "cursor",
511        "cycle",
512        "data",
513        "datalink",
514        "datetime_interval_code",
515        "datetime_interval_precision",
516        "day",
517        "dbtimezone",
518        "deallocate",
519        "dec",
520        "decimal",
521        "declare",
522        "default",
523        "default_like_escape_character",
524        "deferrable",
525        "deferred",
526        "defined",
527        "definer",
528        "delete",
529        "deref",
530        "derived",
531        "desc",
532        "describe",
533        "descriptor",
534        "deterministic",
535        "disable",
536        "disabled",
537        "disconnect",
538        "dispatch",
539        "distinct",
540        "dlurlcomplete",
541        "dlurlpath",
542        "dlurlpathonly",
543        "dlurlscheme",
544        "dlurlserver",
545        "dlvalue",
546        "do",
547        "domain",
548        "double",
549        "drop",
550        "dynamic",
551        "dynamic_function",
552        "dynamic_function_code",
553        "each",
554        "else",
555        "elseif",
556        "elsif",
557        "emits",
558        "enable",
559        "enabled",
560        "end",
561        "end-exec",
562        "endif",
563        "enforce",
564        "equals",
565        "errors",
566        "escape",
567        "except",
568        "exception",
569        "exec",
570        "execute",
571        "exists",
572        "exit",
573        "export",
574        "external",
575        "extract",
576        "false",
577        "fbv",
578        "fetch",
579        "file",
580        "final",
581        "first",
582        "float",
583        "following",
584        "for",
585        "forall",
586        "force",
587        "format",
588        "found",
589        "free",
590        "from",
591        "fs",
592        "full",
593        "function",
594        "general",
595        "generated",
596        "geometry",
597        "get",
598        "global",
599        "go",
600        "goto",
601        "grant",
602        "granted",
603        "group",
604        "group_concat",
605        "grouping",
606        "groups",
607        "hashtype",
608        "hashtype_format",
609        "having",
610        "high",
611        "hold",
612        "hour",
613        "identity",
614        "if",
615        "ifnull",
616        "immediate",
617        "impersonate",
618        "implementation",
619        "import",
620        "in",
621        "index",
622        "indicator",
623        "inner",
624        "inout",
625        "input",
626        "insensitive",
627        "insert",
628        "instance",
629        "instantiable",
630        "int",
631        "integer",
632        "integrity",
633        "intersect",
634        "interval",
635        "into",
636        "inverse",
637        "invoker",
638        "is",
639        "iterate",
640        "join",
641        "key_member",
642        "key_type",
643        "large",
644        "last",
645        "lateral",
646        "ldap",
647        "leading",
648        "leave",
649        "left",
650        "level",
651        "like",
652        "limit",
653        "listagg",
654        "localtime",
655        "localtimestamp",
656        "locator",
657        "log",
658        "longvarchar",
659        "loop",
660        "low",
661        "map",
662        "match",
663        "matched",
664        "merge",
665        "method",
666        "minus",
667        "minute",
668        "mod",
669        "modifies",
670        "modify",
671        "module",
672        "month",
673        "names",
674        "national",
675        "natural",
676        "nchar",
677        "nclob",
678        "new",
679        "next",
680        "nls_date_format",
681        "nls_date_language",
682        "nls_first_day_of_week",
683        "nls_numeric_characters",
684        "nls_timestamp_format",
685        "no",
686        "nocycle",
687        "nologging",
688        "none",
689        "not",
690        "null",
691        "nullif",
692        "number",
693        "numeric",
694        "nvarchar",
695        "nvarchar2",
696        "object",
697        "of",
698        "off",
699        "old",
700        "on",
701        "only",
702        "open",
703        "option",
704        "options",
705        "or",
706        "order",
707        "ordering",
708        "ordinality",
709        "others",
710        "out",
711        "outer",
712        "output",
713        "over",
714        "overlaps",
715        "overlay",
716        "overriding",
717        "pad",
718        "parallel_enable",
719        "parameter",
720        "parameter_specific_catalog",
721        "parameter_specific_name",
722        "parameter_specific_schema",
723        "parquet",
724        "partial",
725        "path",
726        "permission",
727        "placing",
728        "plus",
729        "preceding",
730        "preferring",
731        "prepare",
732        "preserve",
733        "prior",
734        "privileges",
735        "procedure",
736        "profile",
737        "qualify",
738        "random",
739        "range",
740        "read",
741        "reads",
742        "real",
743        "recovery",
744        "recursive",
745        "ref",
746        "references",
747        "referencing",
748        "refresh",
749        "regexp_like",
750        "relative",
751        "release",
752        "rename",
753        "repeat",
754        "replace",
755        "restore",
756        "restrict",
757        "result",
758        "return",
759        "returned_length",
760        "returned_octet_length",
761        "returns",
762        "revoke",
763        "right",
764        "rollback",
765        "rollup",
766        "routine",
767        "row",
768        "rows",
769        "rowtype",
770        "savepoint",
771        "schema",
772        "scope",
773        "scope_user",
774        "script",
775        "scroll",
776        "search",
777        "second",
778        "section",
779        "security",
780        "select",
781        "selective",
782        "self",
783        "sensitive",
784        "separator",
785        "sequence",
786        "session",
787        "session_user",
788        "sessiontimezone",
789        "set",
790        "sets",
791        "shortint",
792        "similar",
793        "smallint",
794        "some",
795        "source",
796        "space",
797        "specific",
798        "specifictype",
799        "sql",
800        "sql_bigint",
801        "sql_bit",
802        "sql_char",
803        "sql_date",
804        "sql_decimal",
805        "sql_double",
806        "sql_float",
807        "sql_integer",
808        "sql_longvarchar",
809        "sql_numeric",
810        "sql_preprocessor_script",
811        "sql_real",
812        "sql_smallint",
813        "sql_timestamp",
814        "sql_tinyint",
815        "sql_type_date",
816        "sql_type_timestamp",
817        "sql_varchar",
818        "sqlexception",
819        "sqlstate",
820        "sqlwarning",
821        "start",
822        "state",
823        "statement",
824        "static",
825        "structure",
826        "style",
827        "substring",
828        "subtype",
829        "sysdate",
830        "system",
831        "system_user",
832        "systimestamp",
833        "table",
834        "temporary",
835        "text",
836        "then",
837        "time",
838        "timestamp",
839        "timezone_hour",
840        "timezone_minute",
841        "tinyint",
842        "to",
843        "trailing",
844        "transaction",
845        "transform",
846        "transforms",
847        "translation",
848        "treat",
849        "trigger",
850        "trim",
851        "true",
852        "truncate",
853        "under",
854        "union",
855        "unique",
856        "unknown",
857        "unlink",
858        "unnest",
859        "until",
860        "update",
861        "usage",
862        "user",
863        "using",
864        "value",
865        "values",
866        "varchar",
867        "varchar2",
868        "varray",
869        "verify",
870        "view",
871        "when",
872        "whenever",
873        "where",
874        "while",
875        "window",
876        "with",
877        "within",
878        "without",
879        "work",
880        "year",
881        "yes",
882        "zone",
883    }
884
885    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
886        from_tz = expression.args.get("source_tz")
887        to_tz = expression.args.get("target_tz")
888        datetime = expression.args.get("timestamp")
889        options = expression.args.get("options")
890
891        return self.func("CONVERT_TZ", datetime, from_tz, to_tz, options)
892
893    def if_sql(self, expression: exp.If) -> str:
894        this = self.sql(expression, "this")
895        true = self.sql(expression, "true")
896        false = self.sql(expression, "false")
897        return f"IF {this} THEN {true} ELSE {false} ENDIF"
898
899    # https://docs.exasol.com/db/latest/sql/open_schema.htm
900    def use_sql(self, expression: exp.Use) -> str:
901        kind = expression.args.get("kind")
902        if kind and self.sql(kind).upper() != "SCHEMA":
903            self.unsupported(f"'USE {self.sql(kind)}' is not supported in Exasol")
904            return super().use_sql(expression)
905        this = self.sql(expression, "this")
906        return f"OPEN SCHEMA {this}"
907
908    # https://docs.exasol.com/db/latest/sql_references/metadata/metadata_system_tables.htm
909    def show_sql(self, expression: exp.Show) -> str:
910        if expression.name == "TABLES":
911            db_name = expression.text("db")
912            schema_filter: exp.Expression = (
913                exp.Literal.string(db_name.upper()) if db_name else exp.CurrentSchema()
914            )
915            select = (
916                exp.select(exp.column("TABLE_NAME"))
917                .from_(exp.table_("EXA_ALL_TABLES", db="SYS"))
918                .where(exp.column("TABLE_SCHEMA").eq(schema_filter))
919            )
920            return self.sql(select)
921
922        if expression.name == "DATABASES":
923            select = exp.select(exp.column("SCHEMA_NAME")).from_(
924                exp.table_("EXA_SCHEMAS", db="SYS")
925            )
926            return self.sql(select)
927
928        return super().show_sql(expression)
929
930    def collate_sql(self, expression: exp.Collate) -> str:
931        return self.sql(expression.this)
932
933    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
934        sql = self.func(
935            "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
936        )
937
938        emits = self.sql(expression, "emits")
939        if emits:
940            sql = f"{sql} EMITS {emits}"
941
942        return sql
943
944    @unsupported_args("flag")
945    def regexplike_sql(self, expression: exp.RegexpLike) -> str:
946        if not expression.args.get("full_match"):
947            pattern = expression.expression
948            if pattern.is_string:
949                expression.set("expression", exp.Literal.string(f".*{pattern.name}.*"))
950            else:
951                expression.set(
952                    "expression",
953                    exp.Paren(
954                        this=exp.Concat(
955                            expressions=[
956                                exp.Literal.string(".*"),
957                                pattern,
958                                exp.Literal.string(".*"),
959                            ]
960                        )
961                    ),
962                )
963        return self.binary(expression, "REGEXP_LIKE")