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