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