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(self, e, on_overflow=True),
352        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/edit_distance.htm#EDIT_DISTANCE
353        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
354            rename_func("EDIT_DISTANCE")
355        ),
356        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/mod.htm
357        exp.Mod: rename_func("MOD"),
358        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/from_posix_time.htm
359        exp.UnixToTime: lambda self, e: self.func("FROM_POSIX_TIME", e.this),
360        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/rank.htm
361        exp.Rank: unsupported_args("expressions")(lambda *_: "RANK()"),
362        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/dense_rank.htm
363        exp.DenseRank: unsupported_args("expressions")(lambda *_: "DENSE_RANK()"),
364        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_substr.htm
365        exp.RegexpExtract: unsupported_args("parameters", "group")(rename_func("REGEXP_SUBSTR")),
366        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_replace.htm
367        exp.RegexpReplace: unsupported_args("modifiers")(rename_func("REGEXP_REPLACE")),
368        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/var_pop.htm
369        exp.VariancePop: rename_func("VAR_POP"),
370        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/approximate_count_distinct.htm
371        exp.ApproxDistinct: unsupported_args("accuracy")(rename_func("APPROXIMATE_COUNT_DISTINCT")),
372        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_char%20(datetime).htm
373        exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
374        exp.ToChar: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
375        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
376        exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
377        exp.TimeStrToTime: timestrtotime_sql,
378        exp.TimestampTrunc: _timestamp_trunc_sql,
379        exp.StrToTime: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
380        exp.CurrentUser: lambda *_: "CURRENT_USER",
381        exp.AtTimeZone: lambda self, e: self.func(
382            "CONVERT_TZ",
383            e.this,
384            "'UTC'",
385            e.args.get("zone"),
386        ),
387        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/instr.htm
388        exp.StrPosition: lambda self, e: strposition_sql(
389            self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
390        ),
391        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha%5B1%5D.htm#HASH_SHA%5B1%5D
392        exp.SHA: rename_func("HASH_SHA"),
393        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha256.htm
394        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha512.htm
395        exp.SHA2: _sha2_sql,
396        exp.MD5: rename_func("HASH_MD5"),
397        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hashtype_md5.htm
398        exp.MD5Digest: rename_func("HASHTYPE_MD5"),
399        # https://docs.exasol.com/db/latest/sql/create_view.htm
400        exp.CommentColumnConstraint: lambda self, e: f"COMMENT IS {self.sql(e, 'this')}",
401        exp.SubstringIndex: _substring_index_sql,
402        exp.WeekOfYear: rename_func("WEEK"),
403        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
404        exp.Date: rename_func("TO_DATE"),
405        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_timestamp.htm
406        exp.Timestamp: rename_func("TO_TIMESTAMP"),
407        exp.Quarter: lambda self, e: f"CEIL(MONTH(TO_DATE({self.sql(e, 'this')}))/3)",
408        exp.LastDay: no_last_day_sql,
409    }
410
411    # https://docs.exasol.com/db/7.1/sql_references/system_tables/metadata/exa_sql_keywords.htm
412    RESERVED_KEYWORDS = {
413        "absolute",
414        "action",
415        "add",
416        "after",
417        "all",
418        "allocate",
419        "alter",
420        "and",
421        "any",
422        "append",
423        "are",
424        "array",
425        "as",
426        "asc",
427        "asensitive",
428        "assertion",
429        "at",
430        "attribute",
431        "authid",
432        "authorization",
433        "before",
434        "begin",
435        "between",
436        "bigint",
437        "binary",
438        "bit",
439        "blob",
440        "blocked",
441        "bool",
442        "boolean",
443        "both",
444        "by",
445        "byte",
446        "call",
447        "called",
448        "cardinality",
449        "cascade",
450        "cascaded",
451        "case",
452        "casespecific",
453        "cast",
454        "catalog",
455        "chain",
456        "char",
457        "character",
458        "character_set_catalog",
459        "character_set_name",
460        "character_set_schema",
461        "characteristics",
462        "check",
463        "checked",
464        "clob",
465        "close",
466        "coalesce",
467        "collate",
468        "collation",
469        "collation_catalog",
470        "collation_name",
471        "collation_schema",
472        "column",
473        "commit",
474        "condition",
475        "connect_by_iscycle",
476        "connect_by_isleaf",
477        "connect_by_root",
478        "connection",
479        "constant",
480        "constraint",
481        "constraint_state_default",
482        "constraints",
483        "constructor",
484        "contains",
485        "continue",
486        "control",
487        "convert",
488        "corresponding",
489        "create",
490        "cs",
491        "csv",
492        "cube",
493        "current",
494        "current_cluster",
495        "current_cluster_uid",
496        "current_date",
497        "current_path",
498        "current_role",
499        "current_schema",
500        "current_session",
501        "current_statement",
502        "current_time",
503        "current_timestamp",
504        "current_user",
505        "cursor",
506        "cycle",
507        "data",
508        "datalink",
509        "datetime_interval_code",
510        "datetime_interval_precision",
511        "day",
512        "dbtimezone",
513        "deallocate",
514        "dec",
515        "decimal",
516        "declare",
517        "default",
518        "default_like_escape_character",
519        "deferrable",
520        "deferred",
521        "defined",
522        "definer",
523        "delete",
524        "deref",
525        "derived",
526        "desc",
527        "describe",
528        "descriptor",
529        "deterministic",
530        "disable",
531        "disabled",
532        "disconnect",
533        "dispatch",
534        "distinct",
535        "dlurlcomplete",
536        "dlurlpath",
537        "dlurlpathonly",
538        "dlurlscheme",
539        "dlurlserver",
540        "dlvalue",
541        "do",
542        "domain",
543        "double",
544        "drop",
545        "dynamic",
546        "dynamic_function",
547        "dynamic_function_code",
548        "each",
549        "else",
550        "elseif",
551        "elsif",
552        "emits",
553        "enable",
554        "enabled",
555        "end",
556        "end-exec",
557        "endif",
558        "enforce",
559        "equals",
560        "errors",
561        "escape",
562        "except",
563        "exception",
564        "exec",
565        "execute",
566        "exists",
567        "exit",
568        "export",
569        "external",
570        "extract",
571        "false",
572        "fbv",
573        "fetch",
574        "file",
575        "final",
576        "first",
577        "float",
578        "following",
579        "for",
580        "forall",
581        "force",
582        "format",
583        "found",
584        "free",
585        "from",
586        "fs",
587        "full",
588        "function",
589        "general",
590        "generated",
591        "geometry",
592        "get",
593        "global",
594        "go",
595        "goto",
596        "grant",
597        "granted",
598        "group",
599        "group_concat",
600        "grouping",
601        "groups",
602        "hashtype",
603        "hashtype_format",
604        "having",
605        "high",
606        "hold",
607        "hour",
608        "identity",
609        "if",
610        "ifnull",
611        "immediate",
612        "impersonate",
613        "implementation",
614        "import",
615        "in",
616        "index",
617        "indicator",
618        "inner",
619        "inout",
620        "input",
621        "insensitive",
622        "insert",
623        "instance",
624        "instantiable",
625        "int",
626        "integer",
627        "integrity",
628        "intersect",
629        "interval",
630        "into",
631        "inverse",
632        "invoker",
633        "is",
634        "iterate",
635        "join",
636        "key_member",
637        "key_type",
638        "large",
639        "last",
640        "lateral",
641        "ldap",
642        "leading",
643        "leave",
644        "left",
645        "level",
646        "like",
647        "limit",
648        "listagg",
649        "localtime",
650        "localtimestamp",
651        "locator",
652        "log",
653        "longvarchar",
654        "loop",
655        "low",
656        "map",
657        "match",
658        "matched",
659        "merge",
660        "method",
661        "minus",
662        "minute",
663        "mod",
664        "modifies",
665        "modify",
666        "module",
667        "month",
668        "names",
669        "national",
670        "natural",
671        "nchar",
672        "nclob",
673        "new",
674        "next",
675        "nls_date_format",
676        "nls_date_language",
677        "nls_first_day_of_week",
678        "nls_numeric_characters",
679        "nls_timestamp_format",
680        "no",
681        "nocycle",
682        "nologging",
683        "none",
684        "not",
685        "null",
686        "nullif",
687        "number",
688        "numeric",
689        "nvarchar",
690        "nvarchar2",
691        "object",
692        "of",
693        "off",
694        "old",
695        "on",
696        "only",
697        "open",
698        "option",
699        "options",
700        "or",
701        "order",
702        "ordering",
703        "ordinality",
704        "others",
705        "out",
706        "outer",
707        "output",
708        "over",
709        "overlaps",
710        "overlay",
711        "overriding",
712        "pad",
713        "parallel_enable",
714        "parameter",
715        "parameter_specific_catalog",
716        "parameter_specific_name",
717        "parameter_specific_schema",
718        "parquet",
719        "partial",
720        "path",
721        "permission",
722        "placing",
723        "plus",
724        "preceding",
725        "preferring",
726        "prepare",
727        "preserve",
728        "prior",
729        "privileges",
730        "procedure",
731        "profile",
732        "qualify",
733        "random",
734        "range",
735        "read",
736        "reads",
737        "real",
738        "recovery",
739        "recursive",
740        "ref",
741        "references",
742        "referencing",
743        "refresh",
744        "regexp_like",
745        "relative",
746        "release",
747        "rename",
748        "repeat",
749        "replace",
750        "restore",
751        "restrict",
752        "result",
753        "return",
754        "returned_length",
755        "returned_octet_length",
756        "returns",
757        "revoke",
758        "right",
759        "rollback",
760        "rollup",
761        "routine",
762        "row",
763        "rows",
764        "rowtype",
765        "savepoint",
766        "schema",
767        "scope",
768        "scope_user",
769        "script",
770        "scroll",
771        "search",
772        "second",
773        "section",
774        "security",
775        "select",
776        "selective",
777        "self",
778        "sensitive",
779        "separator",
780        "sequence",
781        "session",
782        "session_user",
783        "sessiontimezone",
784        "set",
785        "sets",
786        "shortint",
787        "similar",
788        "smallint",
789        "some",
790        "source",
791        "space",
792        "specific",
793        "specifictype",
794        "sql",
795        "sql_bigint",
796        "sql_bit",
797        "sql_char",
798        "sql_date",
799        "sql_decimal",
800        "sql_double",
801        "sql_float",
802        "sql_integer",
803        "sql_longvarchar",
804        "sql_numeric",
805        "sql_preprocessor_script",
806        "sql_real",
807        "sql_smallint",
808        "sql_timestamp",
809        "sql_tinyint",
810        "sql_type_date",
811        "sql_type_timestamp",
812        "sql_varchar",
813        "sqlexception",
814        "sqlstate",
815        "sqlwarning",
816        "start",
817        "state",
818        "statement",
819        "static",
820        "structure",
821        "style",
822        "substring",
823        "subtype",
824        "sysdate",
825        "system",
826        "system_user",
827        "systimestamp",
828        "table",
829        "temporary",
830        "text",
831        "then",
832        "time",
833        "timestamp",
834        "timezone_hour",
835        "timezone_minute",
836        "tinyint",
837        "to",
838        "trailing",
839        "transaction",
840        "transform",
841        "transforms",
842        "translation",
843        "treat",
844        "trigger",
845        "trim",
846        "true",
847        "truncate",
848        "under",
849        "union",
850        "unique",
851        "unknown",
852        "unlink",
853        "unnest",
854        "until",
855        "update",
856        "usage",
857        "user",
858        "using",
859        "value",
860        "values",
861        "varchar",
862        "varchar2",
863        "varray",
864        "verify",
865        "view",
866        "when",
867        "whenever",
868        "where",
869        "while",
870        "window",
871        "with",
872        "within",
873        "without",
874        "work",
875        "year",
876        "yes",
877        "zone",
878    }
879
880    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
881        from_tz = expression.args.get("source_tz")
882        to_tz = expression.args.get("target_tz")
883        datetime = expression.args.get("timestamp")
884        options = expression.args.get("options")
885
886        return self.func("CONVERT_TZ", datetime, from_tz, to_tz, options)
887
888    def if_sql(self, expression: exp.If) -> str:
889        this = self.sql(expression, "this")
890        true = self.sql(expression, "true")
891        false = self.sql(expression, "false")
892        return f"IF {this} THEN {true} ELSE {false} ENDIF"
893
894    # https://docs.exasol.com/db/latest/sql/open_schema.htm
895    def use_sql(self, expression: exp.Use) -> str:
896        kind = expression.args.get("kind")
897        if kind and self.sql(kind).upper() != "SCHEMA":
898            self.unsupported(f"'USE {self.sql(kind)}' is not supported in Exasol")
899            return super().use_sql(expression)
900        this = self.sql(expression, "this")
901        return f"OPEN SCHEMA {this}"
902
903    # https://docs.exasol.com/db/latest/sql_references/metadata/metadata_system_tables.htm
904    def show_sql(self, expression: exp.Show) -> str:
905        if expression.name == "TABLES":
906            db_name = expression.text("db")
907            schema_filter: exp.Expression = (
908                exp.Literal.string(db_name.upper()) if db_name else exp.CurrentSchema()
909            )
910            select = (
911                exp.select(exp.column("TABLE_NAME"))
912                .from_(exp.table_("EXA_ALL_TABLES", db="SYS"))
913                .where(exp.column("TABLE_SCHEMA").eq(schema_filter))
914            )
915            return self.sql(select)
916
917        if expression.name == "DATABASES":
918            select = exp.select(exp.column("SCHEMA_NAME")).from_(
919                exp.table_("EXA_SCHEMAS", db="SYS")
920            )
921            return self.sql(select)
922
923        return super().show_sql(expression)
924
925    def collate_sql(self, expression: exp.Collate) -> str:
926        return self.sql(expression.this)
927
928    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
929        sql = self.func(
930            "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
931        )
932
933        emits = self.sql(expression, "emits")
934        if emits:
935            sql = f"{sql} EMITS {emits}"
936
937        return sql
938
939    @unsupported_args("flag")
940    def regexplike_sql(self, expression: exp.RegexpLike) -> str:
941        if not expression.args.get("full_match"):
942            pattern = expression.expression
943            if pattern.is_string:
944                expression.set("expression", exp.Literal.string(f".*{pattern.name}.*"))
945            else:
946                expression.set(
947                    "expression",
948                    exp.Paren(
949                        this=exp.Concat(
950                            expressions=[
951                                exp.Literal.string(".*"),
952                                pattern,
953                                exp.Literal.string(".*"),
954                            ]
955                        )
956                    ),
957                )
958        return self.binary(expression, "REGEXP_LIKE")