1from __future__ import annotations
2
3import logging
4import re
5import typing as t
6from collections import defaultdict
7from functools import reduce, wraps
8
9from sqlglot import exp
10from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
11from sqlglot.helper import apply_index_offset, csv, name_sequence, seq_get
12from sqlglot.jsonpath import ALL_JSON_PATH_PARTS, JSON_PATH_PART_TRANSFORMS
13from sqlglot.time import format_time
14from sqlglot.tokens import TokenType
15
16if t.TYPE_CHECKING:
17 from sqlglot._typing import E
18 from sqlglot.dialects.dialect import DialectType
19
20 G = t.TypeVar("G", bound="Generator")
21 GeneratorMethod = t.Callable[[G, E], str]
22
23logger = logging.getLogger("sqlglot")
24
25ESCAPED_UNICODE_RE = re.compile(r"\\(\d+)")
26UNSUPPORTED_TEMPLATE = "Argument '{}' is not supported for expression '{}' when targeting {}."
27
28
29def unsupported_args(
30 *args: t.Union[str, t.Tuple[str, str]],
31) -> t.Callable[[GeneratorMethod], GeneratorMethod]:
32 """
33 Decorator that can be used to mark certain args of an `Expression` subclass as unsupported.
34 It expects a sequence of argument names or pairs of the form (argument_name, diagnostic_msg).
35 """
36 diagnostic_by_arg: t.Dict[str, t.Optional[str]] = {}
37 for arg in args:
38 if isinstance(arg, str):
39 diagnostic_by_arg[arg] = None
40 else:
41 diagnostic_by_arg[arg[0]] = arg[1]
42
43 def decorator(func: GeneratorMethod) -> GeneratorMethod:
44 @wraps(func)
45 def _func(generator: G, expression: E) -> str:
46 expression_name = expression.__class__.__name__
47 dialect_name = generator.dialect.__class__.__name__
48
49 for arg_name, diagnostic in diagnostic_by_arg.items():
50 if expression.args.get(arg_name):
51 diagnostic = diagnostic or UNSUPPORTED_TEMPLATE.format(
52 arg_name, expression_name, dialect_name
53 )
54 generator.unsupported(diagnostic)
55
56 return func(generator, expression)
57
58 return _func
59
60 return decorator
61
62
63class _Generator(type):
64 def __new__(cls, clsname, bases, attrs):
65 klass = super().__new__(cls, clsname, bases, attrs)
66
67 # Remove transforms that correspond to unsupported JSONPathPart expressions
68 for part in ALL_JSON_PATH_PARTS - klass.SUPPORTED_JSON_PATH_PARTS:
69 klass.TRANSFORMS.pop(part, None)
70
71 return klass
72
73
74class Generator(metaclass=_Generator):
75 """
76 Generator converts a given syntax tree to the corresponding SQL string.
77
78 Args:
79 pretty: Whether to format the produced SQL string.
80 Default: False.
81 identify: Determines when an identifier should be quoted. Possible values are:
82 False (default): Never quote, except in cases where it's mandatory by the dialect.
83 True or 'always': Always quote.
84 'safe': Only quote identifiers that are case insensitive.
85 normalize: Whether to normalize identifiers to lowercase.
86 Default: False.
87 pad: The pad size in a formatted string. For example, this affects the indentation of
88 a projection in a query, relative to its nesting level.
89 Default: 2.
90 indent: The indentation size in a formatted string. For example, this affects the
91 indentation of subqueries and filters under a `WHERE` clause.
92 Default: 2.
93 normalize_functions: How to normalize function names. Possible values are:
94 "upper" or True (default): Convert names to uppercase.
95 "lower": Convert names to lowercase.
96 False: Disables function name normalization.
97 unsupported_level: Determines the generator's behavior when it encounters unsupported expressions.
98 Default ErrorLevel.WARN.
99 max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError.
100 This is only relevant if unsupported_level is ErrorLevel.RAISE.
101 Default: 3
102 leading_comma: Whether the comma is leading or trailing in select expressions.
103 This is only relevant when generating in pretty mode.
104 Default: False
105 max_text_width: The max number of characters in a segment before creating new lines in pretty mode.
106 The default is on the smaller end because the length only represents a segment and not the true
107 line length.
108 Default: 80
109 comments: Whether to preserve comments in the output SQL code.
110 Default: True
111 """
112
113 TRANSFORMS: t.Dict[t.Type[exp.Expression], t.Callable[..., str]] = {
114 **JSON_PATH_PART_TRANSFORMS,
115 exp.AllowedValuesProperty: lambda self,
116 e: f"ALLOWED_VALUES {self.expressions(e, flat=True)}",
117 exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
118 exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
119 exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
120 exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
121 exp.CaseSpecificColumnConstraint: lambda _,
122 e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
123 exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
124 exp.CharacterSetProperty: lambda self,
125 e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}",
126 exp.ClusteredColumnConstraint: lambda self,
127 e: f"CLUSTERED ({self.expressions(e, 'this', indent=False)})",
128 exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
129 exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
130 exp.ConnectByRoot: lambda self, e: f"CONNECT_BY_ROOT {self.sql(e, 'this')}",
131 exp.CopyGrantsProperty: lambda *_: "COPY GRANTS",
132 exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
133 exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
134 exp.DynamicProperty: lambda *_: "DYNAMIC",
135 exp.EmptyProperty: lambda *_: "EMPTY",
136 exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
137 exp.EphemeralColumnConstraint: lambda self,
138 e: f"EPHEMERAL{(' ' + self.sql(e, 'this')) if e.this else ''}",
139 exp.ExcludeColumnConstraint: lambda self, e: f"EXCLUDE {self.sql(e, 'this').lstrip()}",
140 exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
141 exp.Except: lambda self, e: self.set_operations(e),
142 exp.ExternalProperty: lambda *_: "EXTERNAL",
143 exp.GlobalProperty: lambda *_: "GLOBAL",
144 exp.HeapProperty: lambda *_: "HEAP",
145 exp.IcebergProperty: lambda *_: "ICEBERG",
146 exp.InheritsProperty: lambda self, e: f"INHERITS ({self.expressions(e, flat=True)})",
147 exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
148 exp.InputModelProperty: lambda self, e: f"INPUT{self.sql(e, 'this')}",
149 exp.Intersect: lambda self, e: self.set_operations(e),
150 exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
151 exp.LanguageProperty: lambda self, e: self.naked_property(e),
152 exp.LocationProperty: lambda self, e: self.naked_property(e),
153 exp.LogProperty: lambda _, e: f"{'NO ' if e.args.get('no') else ''}LOG",
154 exp.MaterializedProperty: lambda *_: "MATERIALIZED",
155 exp.NonClusteredColumnConstraint: lambda self,
156 e: f"NONCLUSTERED ({self.expressions(e, 'this', indent=False)})",
157 exp.NoPrimaryIndexProperty: lambda *_: "NO PRIMARY INDEX",
158 exp.NotForReplicationColumnConstraint: lambda *_: "NOT FOR REPLICATION",
159 exp.OnCommitProperty: lambda _,
160 e: f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS",
161 exp.OnProperty: lambda self, e: f"ON {self.sql(e, 'this')}",
162 exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
163 exp.Operator: lambda self, e: self.binary(e, ""), # The operator is produced in `binary`
164 exp.OutputModelProperty: lambda self, e: f"OUTPUT{self.sql(e, 'this')}",
165 exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
166 exp.PivotAny: lambda self, e: f"ANY{self.sql(e, 'this')}",
167 exp.ProjectionPolicyColumnConstraint: lambda self,
168 e: f"PROJECTION POLICY {self.sql(e, 'this')}",
169 exp.RemoteWithConnectionModelProperty: lambda self,
170 e: f"REMOTE WITH CONNECTION {self.sql(e, 'this')}",
171 exp.ReturnsProperty: lambda self, e: (
172 "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
173 ),
174 exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
175 exp.SecureProperty: lambda *_: "SECURE",
176 exp.SecurityProperty: lambda self, e: f"SECURITY {self.sql(e, 'this')}",
177 exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
178 exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
179 exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
180 exp.SharingProperty: lambda self, e: f"SHARING={self.sql(e, 'this')}",
181 exp.SqlReadWriteProperty: lambda _, e: e.name,
182 exp.SqlSecurityProperty: lambda _,
183 e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
184 exp.StabilityProperty: lambda _, e: e.name,
185 exp.Stream: lambda self, e: f"STREAM {self.sql(e, 'this')}",
186 exp.StreamingTableProperty: lambda *_: "STREAMING",
187 exp.StrictProperty: lambda *_: "STRICT",
188 exp.SwapTable: lambda self, e: f"SWAP WITH {self.sql(e, 'this')}",
189 exp.TemporaryProperty: lambda *_: "TEMPORARY",
190 exp.TagColumnConstraint: lambda self, e: f"TAG ({self.expressions(e, flat=True)})",
191 exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
192 exp.ToMap: lambda self, e: f"MAP {self.sql(e, 'this')}",
193 exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
194 exp.TransformModelProperty: lambda self, e: self.func("TRANSFORM", *e.expressions),
195 exp.TransientProperty: lambda *_: "TRANSIENT",
196 exp.Union: lambda self, e: self.set_operations(e),
197 exp.UnloggedProperty: lambda *_: "UNLOGGED",
198 exp.Uuid: lambda *_: "UUID()",
199 exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
200 exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
201 exp.ViewAttributeProperty: lambda self, e: f"WITH {self.sql(e, 'this')}",
202 exp.VolatileProperty: lambda *_: "VOLATILE",
203 exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
204 exp.WithProcedureOptions: lambda self, e: f"WITH {self.expressions(e, flat=True)}",
205 exp.WithSchemaBindingProperty: lambda self, e: f"WITH SCHEMA {self.sql(e, 'this')}",
206 exp.WithOperator: lambda self, e: f"{self.sql(e, 'this')} WITH {self.sql(e, 'op')}",
207 }
208
209 # Whether null ordering is supported in order by
210 # True: Full Support, None: No support, False: No support for certain cases
211 # such as window specifications, aggregate functions etc
212 NULL_ORDERING_SUPPORTED: t.Optional[bool] = True
213
214 # Whether ignore nulls is inside the agg or outside.
215 # FIRST(x IGNORE NULLS) OVER vs FIRST (x) IGNORE NULLS OVER
216 IGNORE_NULLS_IN_FUNC = False
217
218 # Whether locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported
219 LOCKING_READS_SUPPORTED = False
220
221 # Whether the EXCEPT and INTERSECT operations can return duplicates
222 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = True
223
224 # Wrap derived values in parens, usually standard but spark doesn't support it
225 WRAP_DERIVED_VALUES = True
226
227 # Whether create function uses an AS before the RETURN
228 CREATE_FUNCTION_RETURN_AS = True
229
230 # Whether MERGE ... WHEN MATCHED BY SOURCE is allowed
231 MATCHED_BY_SOURCE = True
232
233 # Whether the INTERVAL expression works only with values like '1 day'
234 SINGLE_STRING_INTERVAL = False
235
236 # Whether the plural form of date parts like day (i.e. "days") is supported in INTERVALs
237 INTERVAL_ALLOWS_PLURAL_FORM = True
238
239 # Whether limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH")
240 LIMIT_FETCH = "ALL"
241
242 # Whether limit and fetch allows expresions or just limits
243 LIMIT_ONLY_LITERALS = False
244
245 # Whether a table is allowed to be renamed with a db
246 RENAME_TABLE_WITH_DB = True
247
248 # The separator for grouping sets and rollups
249 GROUPINGS_SEP = ","
250
251 # The string used for creating an index on a table
252 INDEX_ON = "ON"
253
254 # Whether join hints should be generated
255 JOIN_HINTS = True
256
257 # Whether table hints should be generated
258 TABLE_HINTS = True
259
260 # Whether query hints should be generated
261 QUERY_HINTS = True
262
263 # What kind of separator to use for query hints
264 QUERY_HINT_SEP = ", "
265
266 # Whether comparing against booleans (e.g. x IS TRUE) is supported
267 IS_BOOL_ALLOWED = True
268
269 # Whether to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
270 DUPLICATE_KEY_UPDATE_WITH_SET = True
271
272 # Whether to generate the limit as TOP <value> instead of LIMIT <value>
273 LIMIT_IS_TOP = False
274
275 # Whether to generate INSERT INTO ... RETURNING or INSERT INTO RETURNING ...
276 RETURNING_END = True
277
278 # Whether to generate an unquoted value for EXTRACT's date part argument
279 EXTRACT_ALLOWS_QUOTES = True
280
281 # Whether TIMETZ / TIMESTAMPTZ will be generated using the "WITH TIME ZONE" syntax
282 TZ_TO_WITH_TIME_ZONE = False
283
284 # Whether the NVL2 function is supported
285 NVL2_SUPPORTED = True
286
287 # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
288 SELECT_KINDS: t.Tuple[str, ...] = ("STRUCT", "VALUE")
289
290 # Whether VALUES statements can be used as derived tables.
291 # MySQL 5 and Redshift do not allow this, so when False, it will convert
292 # SELECT * VALUES into SELECT UNION
293 VALUES_AS_TABLE = True
294
295 # Whether the word COLUMN is included when adding a column with ALTER TABLE
296 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = True
297
298 # UNNEST WITH ORDINALITY (presto) instead of UNNEST WITH OFFSET (bigquery)
299 UNNEST_WITH_ORDINALITY = True
300
301 # Whether FILTER (WHERE cond) can be used for conditional aggregation
302 AGGREGATE_FILTER_SUPPORTED = True
303
304 # Whether JOIN sides (LEFT, RIGHT) are supported in conjunction with SEMI/ANTI join kinds
305 SEMI_ANTI_JOIN_WITH_SIDE = True
306
307 # Whether to include the type of a computed column in the CREATE DDL
308 COMPUTED_COLUMN_WITH_TYPE = True
309
310 # Whether CREATE TABLE .. COPY .. is supported. False means we'll generate CLONE instead of COPY
311 SUPPORTS_TABLE_COPY = True
312
313 # Whether parentheses are required around the table sample's expression
314 TABLESAMPLE_REQUIRES_PARENS = True
315
316 # Whether a table sample clause's size needs to be followed by the ROWS keyword
317 TABLESAMPLE_SIZE_IS_ROWS = True
318
319 # The keyword(s) to use when generating a sample clause
320 TABLESAMPLE_KEYWORDS = "TABLESAMPLE"
321
322 # Whether the TABLESAMPLE clause supports a method name, like BERNOULLI
323 TABLESAMPLE_WITH_METHOD = True
324
325 # The keyword to use when specifying the seed of a sample clause
326 TABLESAMPLE_SEED_KEYWORD = "SEED"
327
328 # Whether COLLATE is a function instead of a binary operator
329 COLLATE_IS_FUNC = False
330
331 # Whether data types support additional specifiers like e.g. CHAR or BYTE (oracle)
332 DATA_TYPE_SPECIFIERS_ALLOWED = False
333
334 # Whether conditions require booleans WHERE x = 0 vs WHERE x
335 ENSURE_BOOLS = False
336
337 # Whether the "RECURSIVE" keyword is required when defining recursive CTEs
338 CTE_RECURSIVE_KEYWORD_REQUIRED = True
339
340 # Whether CONCAT requires >1 arguments
341 SUPPORTS_SINGLE_ARG_CONCAT = True
342
343 # Whether LAST_DAY function supports a date part argument
344 LAST_DAY_SUPPORTS_DATE_PART = True
345
346 # Whether named columns are allowed in table aliases
347 SUPPORTS_TABLE_ALIAS_COLUMNS = True
348
349 # Whether UNPIVOT aliases are Identifiers (False means they're Literals)
350 UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
351
352 # What delimiter to use for separating JSON key/value pairs
353 JSON_KEY_VALUE_PAIR_SEP = ":"
354
355 # INSERT OVERWRITE TABLE x override
356 INSERT_OVERWRITE = " OVERWRITE TABLE"
357
358 # Whether the SELECT .. INTO syntax is used instead of CTAS
359 SUPPORTS_SELECT_INTO = False
360
361 # Whether UNLOGGED tables can be created
362 SUPPORTS_UNLOGGED_TABLES = False
363
364 # Whether the CREATE TABLE LIKE statement is supported
365 SUPPORTS_CREATE_TABLE_LIKE = True
366
367 # Whether the LikeProperty needs to be specified inside of the schema clause
368 LIKE_PROPERTY_INSIDE_SCHEMA = False
369
370 # Whether DISTINCT can be followed by multiple args in an AggFunc. If not, it will be
371 # transpiled into a series of CASE-WHEN-ELSE, ultimately using a tuple conseisting of the args
372 MULTI_ARG_DISTINCT = True
373
374 # Whether the JSON extraction operators expect a value of type JSON
375 JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
376
377 # Whether bracketed keys like ["foo"] are supported in JSON paths
378 JSON_PATH_BRACKETED_KEY_SUPPORTED = True
379
380 # Whether to escape keys using single quotes in JSON paths
381 JSON_PATH_SINGLE_QUOTE_ESCAPE = False
382
383 # The JSONPathPart expressions supported by this dialect
384 SUPPORTED_JSON_PATH_PARTS = ALL_JSON_PATH_PARTS.copy()
385
386 # Whether any(f(x) for x in array) can be implemented by this dialect
387 CAN_IMPLEMENT_ARRAY_ANY = False
388
389 # Whether the function TO_NUMBER is supported
390 SUPPORTS_TO_NUMBER = True
391
392 # Whether or not set op modifiers apply to the outer set op or select.
393 # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
394 # True means limit 1 happens after the set op, False means it it happens on y.
395 SET_OP_MODIFIERS = True
396
397 # Whether parameters from COPY statement are wrapped in parentheses
398 COPY_PARAMS_ARE_WRAPPED = True
399
400 # Whether values of params are set with "=" token or empty space
401 COPY_PARAMS_EQ_REQUIRED = False
402
403 # Whether COPY statement has INTO keyword
404 COPY_HAS_INTO_KEYWORD = True
405
406 # Whether the conditional TRY(expression) function is supported
407 TRY_SUPPORTED = True
408
409 # Whether the UESCAPE syntax in unicode strings is supported
410 SUPPORTS_UESCAPE = True
411
412 # The keyword to use when generating a star projection with excluded columns
413 STAR_EXCEPT = "EXCEPT"
414
415 # The HEX function name
416 HEX_FUNC = "HEX"
417
418 # The keywords to use when prefixing & separating WITH based properties
419 WITH_PROPERTIES_PREFIX = "WITH"
420
421 # Whether to quote the generated expression of exp.JsonPath
422 QUOTE_JSON_PATH = True
423
424 # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
425 PAD_FILL_PATTERN_IS_REQUIRED = False
426
427 # Whether a projection can explode into multiple rows, e.g. by unnesting an array.
428 SUPPORTS_EXPLODING_PROJECTIONS = True
429
430 # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
431 ARRAY_CONCAT_IS_VAR_LEN = True
432
433 # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
434 SUPPORTS_CONVERT_TIMEZONE = False
435
436 # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
437 SUPPORTS_MEDIAN = True
438
439 # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
440 PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
441
442 TYPE_MAPPING = {
443 exp.DataType.Type.NCHAR: "CHAR",
444 exp.DataType.Type.NVARCHAR: "VARCHAR",
445 exp.DataType.Type.MEDIUMTEXT: "TEXT",
446 exp.DataType.Type.LONGTEXT: "TEXT",
447 exp.DataType.Type.TINYTEXT: "TEXT",
448 exp.DataType.Type.MEDIUMBLOB: "BLOB",
449 exp.DataType.Type.LONGBLOB: "BLOB",
450 exp.DataType.Type.TINYBLOB: "BLOB",
451 exp.DataType.Type.INET: "INET",
452 exp.DataType.Type.ROWVERSION: "VARBINARY",
453 }
454
455 TIME_PART_SINGULARS = {
456 "MICROSECONDS": "MICROSECOND",
457 "SECONDS": "SECOND",
458 "MINUTES": "MINUTE",
459 "HOURS": "HOUR",
460 "DAYS": "DAY",
461 "WEEKS": "WEEK",
462 "MONTHS": "MONTH",
463 "QUARTERS": "QUARTER",
464 "YEARS": "YEAR",
465 }
466
467 AFTER_HAVING_MODIFIER_TRANSFORMS = {
468 "cluster": lambda self, e: self.sql(e, "cluster"),
469 "distribute": lambda self, e: self.sql(e, "distribute"),
470 "sort": lambda self, e: self.sql(e, "sort"),
471 "windows": lambda self, e: (
472 self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
473 if e.args.get("windows")
474 else ""
475 ),
476 "qualify": lambda self, e: self.sql(e, "qualify"),
477 }
478
479 TOKEN_MAPPING: t.Dict[TokenType, str] = {}
480
481 STRUCT_DELIMITER = ("<", ">")
482
483 PARAMETER_TOKEN = "@"
484 NAMED_PLACEHOLDER_TOKEN = ":"
485
486 PROPERTIES_LOCATION = {
487 exp.AllowedValuesProperty: exp.Properties.Location.POST_SCHEMA,
488 exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE,
489 exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA,
490 exp.AutoRefreshProperty: exp.Properties.Location.POST_SCHEMA,
491 exp.BackupProperty: exp.Properties.Location.POST_SCHEMA,
492 exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME,
493 exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
494 exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
495 exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
496 exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
497 exp.Cluster: exp.Properties.Location.POST_SCHEMA,
498 exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
499 exp.DistributedByProperty: exp.Properties.Location.POST_SCHEMA,
500 exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
501 exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
502 exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
503 exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
504 exp.DictRange: exp.Properties.Location.POST_SCHEMA,
505 exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
506 exp.DynamicProperty: exp.Properties.Location.POST_CREATE,
507 exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
508 exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
509 exp.EmptyProperty: exp.Properties.Location.POST_SCHEMA,
510 exp.EngineProperty: exp.Properties.Location.POST_SCHEMA,
511 exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA,
512 exp.ExternalProperty: exp.Properties.Location.POST_CREATE,
513 exp.FallbackProperty: exp.Properties.Location.POST_NAME,
514 exp.FileFormatProperty: exp.Properties.Location.POST_WITH,
515 exp.FreespaceProperty: exp.Properties.Location.POST_NAME,
516 exp.GlobalProperty: exp.Properties.Location.POST_CREATE,
517 exp.HeapProperty: exp.Properties.Location.POST_WITH,
518 exp.InheritsProperty: exp.Properties.Location.POST_SCHEMA,
519 exp.IcebergProperty: exp.Properties.Location.POST_CREATE,
520 exp.InputModelProperty: exp.Properties.Location.POST_SCHEMA,
521 exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME,
522 exp.JournalProperty: exp.Properties.Location.POST_NAME,
523 exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
524 exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
525 exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
526 exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
527 exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
528 exp.LogProperty: exp.Properties.Location.POST_NAME,
529 exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
530 exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME,
531 exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION,
532 exp.OnProperty: exp.Properties.Location.POST_SCHEMA,
533 exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION,
534 exp.Order: exp.Properties.Location.POST_SCHEMA,
535 exp.OutputModelProperty: exp.Properties.Location.POST_SCHEMA,
536 exp.PartitionedByProperty: exp.Properties.Location.POST_WITH,
537 exp.PartitionedOfProperty: exp.Properties.Location.POST_SCHEMA,
538 exp.PrimaryKey: exp.Properties.Location.POST_SCHEMA,
539 exp.Property: exp.Properties.Location.POST_WITH,
540 exp.RemoteWithConnectionModelProperty: exp.Properties.Location.POST_SCHEMA,
541 exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA,
542 exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA,
543 exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA,
544 exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA,
545 exp.SampleProperty: exp.Properties.Location.POST_SCHEMA,
546 exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA,
547 exp.SecureProperty: exp.Properties.Location.POST_CREATE,
548 exp.SecurityProperty: exp.Properties.Location.POST_SCHEMA,
549 exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA,
550 exp.Set: exp.Properties.Location.POST_SCHEMA,
551 exp.SettingsProperty: exp.Properties.Location.POST_SCHEMA,
552 exp.SetProperty: exp.Properties.Location.POST_CREATE,
553 exp.SetConfigProperty: exp.Properties.Location.POST_SCHEMA,
554 exp.SharingProperty: exp.Properties.Location.POST_EXPRESSION,
555 exp.SequenceProperties: exp.Properties.Location.POST_EXPRESSION,
556 exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
557 exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
558 exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE,
559 exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
560 exp.StreamingTableProperty: exp.Properties.Location.POST_CREATE,
561 exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
562 exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
563 exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
564 exp.TransientProperty: exp.Properties.Location.POST_CREATE,
565 exp.TransformModelProperty: exp.Properties.Location.POST_SCHEMA,
566 exp.MergeTreeTTL: exp.Properties.Location.POST_SCHEMA,
567 exp.UnloggedProperty: exp.Properties.Location.POST_CREATE,
568 exp.ViewAttributeProperty: exp.Properties.Location.POST_SCHEMA,
569 exp.VolatileProperty: exp.Properties.Location.POST_CREATE,
570 exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION,
571 exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME,
572 exp.WithProcedureOptions: exp.Properties.Location.POST_SCHEMA,
573 exp.WithSchemaBindingProperty: exp.Properties.Location.POST_SCHEMA,
574 exp.WithSystemVersioningProperty: exp.Properties.Location.POST_SCHEMA,
575 }
576
577 # Keywords that can't be used as unquoted identifier names
578 RESERVED_KEYWORDS: t.Set[str] = set()
579
580 # Expressions whose comments are separated from them for better formatting
581 WITH_SEPARATED_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
582 exp.Command,
583 exp.Create,
584 exp.Delete,
585 exp.Drop,
586 exp.From,
587 exp.Insert,
588 exp.Join,
589 exp.MultitableInserts,
590 exp.Select,
591 exp.SetOperation,
592 exp.Update,
593 exp.Where,
594 exp.With,
595 )
596
597 # Expressions that should not have their comments generated in maybe_comment
598 EXCLUDE_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
599 exp.Binary,
600 exp.SetOperation,
601 )
602
603 # Expressions that can remain unwrapped when appearing in the context of an INTERVAL
604 UNWRAPPED_INTERVAL_VALUES: t.Tuple[t.Type[exp.Expression], ...] = (
605 exp.Column,
606 exp.Literal,
607 exp.Neg,
608 exp.Paren,
609 )
610
611 PARAMETERIZABLE_TEXT_TYPES = {
612 exp.DataType.Type.NVARCHAR,
613 exp.DataType.Type.VARCHAR,
614 exp.DataType.Type.CHAR,
615 exp.DataType.Type.NCHAR,
616 }
617
618 # Expressions that need to have all CTEs under them bubbled up to them
619 EXPRESSIONS_WITHOUT_NESTED_CTES: t.Set[t.Type[exp.Expression]] = set()
620
621 SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
622
623 __slots__ = (
624 "pretty",
625 "identify",
626 "normalize",
627 "pad",
628 "_indent",
629 "normalize_functions",
630 "unsupported_level",
631 "max_unsupported",
632 "leading_comma",
633 "max_text_width",
634 "comments",
635 "dialect",
636 "unsupported_messages",
637 "_escaped_quote_end",
638 "_escaped_identifier_end",
639 "_next_name",
640 "_identifier_start",
641 "_identifier_end",
642 )
643
644 def __init__(
645 self,
646 pretty: t.Optional[bool] = None,
647 identify: str | bool = False,
648 normalize: bool = False,
649 pad: int = 2,
650 indent: int = 2,
651 normalize_functions: t.Optional[str | bool] = None,
652 unsupported_level: ErrorLevel = ErrorLevel.WARN,
653 max_unsupported: int = 3,
654 leading_comma: bool = False,
655 max_text_width: int = 80,
656 comments: bool = True,
657 dialect: DialectType = None,
658 ):
659 import sqlglot
660 from sqlglot.dialects import Dialect
661
662 self.pretty = pretty if pretty is not None else sqlglot.pretty
663 self.identify = identify
664 self.normalize = normalize
665 self.pad = pad
666 self._indent = indent
667 self.unsupported_level = unsupported_level
668 self.max_unsupported = max_unsupported
669 self.leading_comma = leading_comma
670 self.max_text_width = max_text_width
671 self.comments = comments
672 self.dialect = Dialect.get_or_raise(dialect)
673
674 # This is both a Dialect property and a Generator argument, so we prioritize the latter
675 self.normalize_functions = (
676 self.dialect.NORMALIZE_FUNCTIONS if normalize_functions is None else normalize_functions
677 )
678
679 self.unsupported_messages: t.List[str] = []
680 self._escaped_quote_end: str = (
681 self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.QUOTE_END
682 )
683 self._escaped_identifier_end = self.dialect.IDENTIFIER_END * 2
684
685 self._next_name = name_sequence("_t")
686
687 self._identifier_start = self.dialect.IDENTIFIER_START
688 self._identifier_end = self.dialect.IDENTIFIER_END
689
690 def generate(self, expression: exp.Expression, copy: bool = True) -> str:
691 """
692 Generates the SQL string corresponding to the given syntax tree.
693
694 Args:
695 expression: The syntax tree.
696 copy: Whether to copy the expression. The generator performs mutations so
697 it is safer to copy.
698
699 Returns:
700 The SQL string corresponding to `expression`.
701 """
702 if copy:
703 expression = expression.copy()
704
705 expression = self.preprocess(expression)
706
707 self.unsupported_messages = []
708 sql = self.sql(expression).strip()
709
710 if self.pretty:
711 sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n")
712
713 if self.unsupported_level == ErrorLevel.IGNORE:
714 return sql
715
716 if self.unsupported_level == ErrorLevel.WARN:
717 for msg in self.unsupported_messages:
718 logger.warning(msg)
719 elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages:
720 raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported))
721
722 return sql
723
724 def preprocess(self, expression: exp.Expression) -> exp.Expression:
725 """Apply generic preprocessing transformations to a given expression."""
726 expression = self._move_ctes_to_top_level(expression)
727
728 if self.ENSURE_BOOLS:
729 from sqlglot.transforms import ensure_bools
730
731 expression = ensure_bools(expression)
732
733 return expression
734
735 def _move_ctes_to_top_level(self, expression: E) -> E:
736 if (
737 not expression.parent
738 and type(expression) in self.EXPRESSIONS_WITHOUT_NESTED_CTES
739 and any(node.parent is not expression for node in expression.find_all(exp.With))
740 ):
741 from sqlglot.transforms import move_ctes_to_top_level
742
743 expression = move_ctes_to_top_level(expression)
744 return expression
745
746 def unsupported(self, message: str) -> None:
747 if self.unsupported_level == ErrorLevel.IMMEDIATE:
748 raise UnsupportedError(message)
749 self.unsupported_messages.append(message)
750
751 def sep(self, sep: str = " ") -> str:
752 return f"{sep.strip()}\n" if self.pretty else sep
753
754 def seg(self, sql: str, sep: str = " ") -> str:
755 return f"{self.sep(sep)}{sql}"
756
757 def pad_comment(self, comment: str) -> str:
758 comment = " " + comment if comment[0].strip() else comment
759 comment = comment + " " if comment[-1].strip() else comment
760 return comment
761
762 def maybe_comment(
763 self,
764 sql: str,
765 expression: t.Optional[exp.Expression] = None,
766 comments: t.Optional[t.List[str]] = None,
767 separated: bool = False,
768 ) -> str:
769 comments = (
770 ((expression and expression.comments) if comments is None else comments) # type: ignore
771 if self.comments
772 else None
773 )
774
775 if not comments or isinstance(expression, self.EXCLUDE_COMMENTS):
776 return sql
777
778 comments_sql = " ".join(
779 f"/*{self.pad_comment(comment)}*/" for comment in comments if comment
780 )
781
782 if not comments_sql:
783 return sql
784
785 comments_sql = self._replace_line_breaks(comments_sql)
786
787 if separated or isinstance(expression, self.WITH_SEPARATED_COMMENTS):
788 return (
789 f"{self.sep()}{comments_sql}{sql}"
790 if not sql or sql[0].isspace()
791 else f"{comments_sql}{self.sep()}{sql}"
792 )
793
794 return f"{sql} {comments_sql}"
795
796 def wrap(self, expression: exp.Expression | str) -> str:
797 this_sql = (
798 self.sql(expression)
799 if isinstance(expression, exp.UNWRAPPED_QUERIES)
800 else self.sql(expression, "this")
801 )
802 if not this_sql:
803 return "()"
804
805 this_sql = self.indent(this_sql, level=1, pad=0)
806 return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
807
808 def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str:
809 original = self.identify
810 self.identify = False
811 result = func(*args, **kwargs)
812 self.identify = original
813 return result
814
815 def normalize_func(self, name: str) -> str:
816 if self.normalize_functions == "upper" or self.normalize_functions is True:
817 return name.upper()
818 if self.normalize_functions == "lower":
819 return name.lower()
820 return name
821
822 def indent(
823 self,
824 sql: str,
825 level: int = 0,
826 pad: t.Optional[int] = None,
827 skip_first: bool = False,
828 skip_last: bool = False,
829 ) -> str:
830 if not self.pretty or not sql:
831 return sql
832
833 pad = self.pad if pad is None else pad
834 lines = sql.split("\n")
835
836 return "\n".join(
837 (
838 line
839 if (skip_first and i == 0) or (skip_last and i == len(lines) - 1)
840 else f"{' ' * (level * self._indent + pad)}{line}"
841 )
842 for i, line in enumerate(lines)
843 )
844
845 def sql(
846 self,
847 expression: t.Optional[str | exp.Expression],
848 key: t.Optional[str] = None,
849 comment: bool = True,
850 ) -> str:
851 if not expression:
852 return ""
853
854 if isinstance(expression, str):
855 return expression
856
857 if key:
858 value = expression.args.get(key)
859 if value:
860 return self.sql(value)
861 return ""
862
863 transform = self.TRANSFORMS.get(expression.__class__)
864
865 if callable(transform):
866 sql = transform(self, expression)
867 elif isinstance(expression, exp.Expression):
868 exp_handler_name = f"{expression.key}_sql"
869
870 if hasattr(self, exp_handler_name):
871 sql = getattr(self, exp_handler_name)(expression)
872 elif isinstance(expression, exp.Func):
873 sql = self.function_fallback_sql(expression)
874 elif isinstance(expression, exp.Property):
875 sql = self.property_sql(expression)
876 else:
877 raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
878 else:
879 raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}")
880
881 return self.maybe_comment(sql, expression) if self.comments and comment else sql
882
883 def uncache_sql(self, expression: exp.Uncache) -> str:
884 table = self.sql(expression, "this")
885 exists_sql = " IF EXISTS" if expression.args.get("exists") else ""
886 return f"UNCACHE TABLE{exists_sql} {table}"
887
888 def cache_sql(self, expression: exp.Cache) -> str:
889 lazy = " LAZY" if expression.args.get("lazy") else ""
890 table = self.sql(expression, "this")
891 options = expression.args.get("options")
892 options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else ""
893 sql = self.sql(expression, "expression")
894 sql = f" AS{self.sep()}{sql}" if sql else ""
895 sql = f"CACHE{lazy} TABLE {table}{options}{sql}"
896 return self.prepend_ctes(expression, sql)
897
898 def characterset_sql(self, expression: exp.CharacterSet) -> str:
899 if isinstance(expression.parent, exp.Cast):
900 return f"CHAR CHARACTER SET {self.sql(expression, 'this')}"
901 default = "DEFAULT " if expression.args.get("default") else ""
902 return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
903
904 def column_parts(self, expression: exp.Column) -> str:
905 return ".".join(
906 self.sql(part)
907 for part in (
908 expression.args.get("catalog"),
909 expression.args.get("db"),
910 expression.args.get("table"),
911 expression.args.get("this"),
912 )
913 if part
914 )
915
916 def column_sql(self, expression: exp.Column) -> str:
917 join_mark = " (+)" if expression.args.get("join_mark") else ""
918
919 if join_mark and not self.dialect.SUPPORTS_COLUMN_JOIN_MARKS:
920 join_mark = ""
921 self.unsupported("Outer join syntax using the (+) operator is not supported.")
922
923 return f"{self.column_parts(expression)}{join_mark}"
924
925 def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
926 this = self.sql(expression, "this")
927 this = f" {this}" if this else ""
928 position = self.sql(expression, "position")
929 return f"{position}{this}"
930
931 def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
932 column = self.sql(expression, "this")
933 kind = self.sql(expression, "kind")
934 constraints = self.expressions(expression, key="constraints", sep=" ", flat=True)
935 exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
936 kind = f"{sep}{kind}" if kind else ""
937 constraints = f" {constraints}" if constraints else ""
938 position = self.sql(expression, "position")
939 position = f" {position}" if position else ""
940
941 if expression.find(exp.ComputedColumnConstraint) and not self.COMPUTED_COLUMN_WITH_TYPE:
942 kind = ""
943
944 return f"{exists}{column}{kind}{constraints}{position}"
945
946 def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str:
947 this = self.sql(expression, "this")
948 kind_sql = self.sql(expression, "kind").strip()
949 return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql
950
951 def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
952 this = self.sql(expression, "this")
953 if expression.args.get("not_null"):
954 persisted = " PERSISTED NOT NULL"
955 elif expression.args.get("persisted"):
956 persisted = " PERSISTED"
957 else:
958 persisted = ""
959 return f"AS {this}{persisted}"
960
961 def autoincrementcolumnconstraint_sql(self, _) -> str:
962 return self.token_sql(TokenType.AUTO_INCREMENT)
963
964 def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str:
965 if isinstance(expression.this, list):
966 this = self.wrap(self.expressions(expression, key="this", flat=True))
967 else:
968 this = self.sql(expression, "this")
969
970 return f"COMPRESS {this}"
971
972 def generatedasidentitycolumnconstraint_sql(
973 self, expression: exp.GeneratedAsIdentityColumnConstraint
974 ) -> str:
975 this = ""
976 if expression.this is not None:
977 on_null = " ON NULL" if expression.args.get("on_null") else ""
978 this = " ALWAYS" if expression.this else f" BY DEFAULT{on_null}"
979
980 start = expression.args.get("start")
981 start = f"START WITH {start}" if start else ""
982 increment = expression.args.get("increment")
983 increment = f" INCREMENT BY {increment}" if increment else ""
984 minvalue = expression.args.get("minvalue")
985 minvalue = f" MINVALUE {minvalue}" if minvalue else ""
986 maxvalue = expression.args.get("maxvalue")
987 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
988 cycle = expression.args.get("cycle")
989 cycle_sql = ""
990
991 if cycle is not None:
992 cycle_sql = f"{' NO' if not cycle else ''} CYCLE"
993 cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql
994
995 sequence_opts = ""
996 if start or increment or cycle_sql:
997 sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}"
998 sequence_opts = f" ({sequence_opts.strip()})"
999
1000 expr = self.sql(expression, "expression")
1001 expr = f"({expr})" if expr else "IDENTITY"
1002
1003 return f"GENERATED{this} AS {expr}{sequence_opts}"
1004
1005 def generatedasrowcolumnconstraint_sql(
1006 self, expression: exp.GeneratedAsRowColumnConstraint
1007 ) -> str:
1008 start = "START" if expression.args.get("start") else "END"
1009 hidden = " HIDDEN" if expression.args.get("hidden") else ""
1010 return f"GENERATED ALWAYS AS ROW {start}{hidden}"
1011
1012 def periodforsystemtimeconstraint_sql(
1013 self, expression: exp.PeriodForSystemTimeConstraint
1014 ) -> str:
1015 return f"PERIOD FOR SYSTEM_TIME ({self.sql(expression, 'this')}, {self.sql(expression, 'expression')})"
1016
1017 def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str:
1018 return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL"
1019
1020 def transformcolumnconstraint_sql(self, expression: exp.TransformColumnConstraint) -> str:
1021 return f"AS {self.sql(expression, 'this')}"
1022
1023 def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str:
1024 desc = expression.args.get("desc")
1025 if desc is not None:
1026 return f"PRIMARY KEY{' DESC' if desc else ' ASC'}"
1027 return "PRIMARY KEY"
1028
1029 def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
1030 this = self.sql(expression, "this")
1031 this = f" {this}" if this else ""
1032 index_type = expression.args.get("index_type")
1033 index_type = f" USING {index_type}" if index_type else ""
1034 on_conflict = self.sql(expression, "on_conflict")
1035 on_conflict = f" {on_conflict}" if on_conflict else ""
1036 nulls_sql = " NULLS NOT DISTINCT" if expression.args.get("nulls") else ""
1037 return f"UNIQUE{nulls_sql}{this}{index_type}{on_conflict}"
1038
1039 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1040 return self.sql(expression, "this")
1041
1042 def create_sql(self, expression: exp.Create) -> str:
1043 kind = self.sql(expression, "kind")
1044 kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1045 properties = expression.args.get("properties")
1046 properties_locs = self.locate_properties(properties) if properties else defaultdict()
1047
1048 this = self.createable_sql(expression, properties_locs)
1049
1050 properties_sql = ""
1051 if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get(
1052 exp.Properties.Location.POST_WITH
1053 ):
1054 properties_sql = self.sql(
1055 exp.Properties(
1056 expressions=[
1057 *properties_locs[exp.Properties.Location.POST_SCHEMA],
1058 *properties_locs[exp.Properties.Location.POST_WITH],
1059 ]
1060 )
1061 )
1062
1063 if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
1064 properties_sql = self.sep() + properties_sql
1065 elif not self.pretty:
1066 # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
1067 properties_sql = f" {properties_sql}"
1068
1069 begin = " BEGIN" if expression.args.get("begin") else ""
1070 end = " END" if expression.args.get("end") else ""
1071
1072 expression_sql = self.sql(expression, "expression")
1073 if expression_sql:
1074 expression_sql = f"{begin}{self.sep()}{expression_sql}{end}"
1075
1076 if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return):
1077 postalias_props_sql = ""
1078 if properties_locs.get(exp.Properties.Location.POST_ALIAS):
1079 postalias_props_sql = self.properties(
1080 exp.Properties(
1081 expressions=properties_locs[exp.Properties.Location.POST_ALIAS]
1082 ),
1083 wrapped=False,
1084 )
1085 postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
1086 expression_sql = f" AS{postalias_props_sql}{expression_sql}"
1087
1088 postindex_props_sql = ""
1089 if properties_locs.get(exp.Properties.Location.POST_INDEX):
1090 postindex_props_sql = self.properties(
1091 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]),
1092 wrapped=False,
1093 prefix=" ",
1094 )
1095
1096 indexes = self.expressions(expression, key="indexes", indent=False, sep=" ")
1097 indexes = f" {indexes}" if indexes else ""
1098 index_sql = indexes + postindex_props_sql
1099
1100 replace = " OR REPLACE" if expression.args.get("replace") else ""
1101 refresh = " OR REFRESH" if expression.args.get("refresh") else ""
1102 unique = " UNIQUE" if expression.args.get("unique") else ""
1103
1104 clustered = expression.args.get("clustered")
1105 if clustered is None:
1106 clustered_sql = ""
1107 elif clustered:
1108 clustered_sql = " CLUSTERED COLUMNSTORE"
1109 else:
1110 clustered_sql = " NONCLUSTERED COLUMNSTORE"
1111
1112 postcreate_props_sql = ""
1113 if properties_locs.get(exp.Properties.Location.POST_CREATE):
1114 postcreate_props_sql = self.properties(
1115 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]),
1116 sep=" ",
1117 prefix=" ",
1118 wrapped=False,
1119 )
1120
1121 modifiers = "".join((clustered_sql, replace, refresh, unique, postcreate_props_sql))
1122
1123 postexpression_props_sql = ""
1124 if properties_locs.get(exp.Properties.Location.POST_EXPRESSION):
1125 postexpression_props_sql = self.properties(
1126 exp.Properties(
1127 expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION]
1128 ),
1129 sep=" ",
1130 prefix=" ",
1131 wrapped=False,
1132 )
1133
1134 concurrently = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1135 exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
1136 no_schema_binding = (
1137 " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
1138 )
1139
1140 clone = self.sql(expression, "clone")
1141 clone = f" {clone}" if clone else ""
1142
1143 expression_sql = f"CREATE{modifiers} {kind}{concurrently}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
1144 return self.prepend_ctes(expression, expression_sql)
1145
1146 def sequenceproperties_sql(self, expression: exp.SequenceProperties) -> str:
1147 start = self.sql(expression, "start")
1148 start = f"START WITH {start}" if start else ""
1149 increment = self.sql(expression, "increment")
1150 increment = f" INCREMENT BY {increment}" if increment else ""
1151 minvalue = self.sql(expression, "minvalue")
1152 minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1153 maxvalue = self.sql(expression, "maxvalue")
1154 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1155 owned = self.sql(expression, "owned")
1156 owned = f" OWNED BY {owned}" if owned else ""
1157
1158 cache = expression.args.get("cache")
1159 if cache is None:
1160 cache_str = ""
1161 elif cache is True:
1162 cache_str = " CACHE"
1163 else:
1164 cache_str = f" CACHE {cache}"
1165
1166 options = self.expressions(expression, key="options", flat=True, sep=" ")
1167 options = f" {options}" if options else ""
1168
1169 return f"{start}{increment}{minvalue}{maxvalue}{cache_str}{options}{owned}".lstrip()
1170
1171 def clone_sql(self, expression: exp.Clone) -> str:
1172 this = self.sql(expression, "this")
1173 shallow = "SHALLOW " if expression.args.get("shallow") else ""
1174 keyword = "COPY" if expression.args.get("copy") and self.SUPPORTS_TABLE_COPY else "CLONE"
1175 return f"{shallow}{keyword} {this}"
1176
1177 def describe_sql(self, expression: exp.Describe) -> str:
1178 style = expression.args.get("style")
1179 style = f" {style}" if style else ""
1180 partition = self.sql(expression, "partition")
1181 partition = f" {partition}" if partition else ""
1182 return f"DESCRIBE{style} {self.sql(expression, 'this')}{partition}"
1183
1184 def heredoc_sql(self, expression: exp.Heredoc) -> str:
1185 tag = self.sql(expression, "tag")
1186 return f"${tag}${self.sql(expression, 'this')}${tag}$"
1187
1188 def prepend_ctes(self, expression: exp.Expression, sql: str) -> str:
1189 with_ = self.sql(expression, "with")
1190 if with_:
1191 sql = f"{with_}{self.sep()}{sql}"
1192 return sql
1193
1194 def with_sql(self, expression: exp.With) -> str:
1195 sql = self.expressions(expression, flat=True)
1196 recursive = (
1197 "RECURSIVE "
1198 if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
1199 else ""
1200 )
1201
1202 return f"WITH {recursive}{sql}"
1203
1204 def cte_sql(self, expression: exp.CTE) -> str:
1205 alias = expression.args.get("alias")
1206 if alias:
1207 alias.add_comments(expression.pop_comments())
1208
1209 alias_sql = self.sql(expression, "alias")
1210
1211 materialized = expression.args.get("materialized")
1212 if materialized is False:
1213 materialized = "NOT MATERIALIZED "
1214 elif materialized:
1215 materialized = "MATERIALIZED "
1216
1217 return f"{alias_sql} AS {materialized or ''}{self.wrap(expression)}"
1218
1219 def tablealias_sql(self, expression: exp.TableAlias) -> str:
1220 alias = self.sql(expression, "this")
1221 columns = self.expressions(expression, key="columns", flat=True)
1222 columns = f"({columns})" if columns else ""
1223
1224 if columns and not self.SUPPORTS_TABLE_ALIAS_COLUMNS:
1225 columns = ""
1226 self.unsupported("Named columns are not supported in table alias.")
1227
1228 if not alias and not self.dialect.UNNEST_COLUMN_ONLY:
1229 alias = self._next_name()
1230
1231 return f"{alias}{columns}"
1232
1233 def bitstring_sql(self, expression: exp.BitString) -> str:
1234 this = self.sql(expression, "this")
1235 if self.dialect.BIT_START:
1236 return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
1237 return f"{int(this, 2)}"
1238
1239 def hexstring_sql(self, expression: exp.HexString) -> str:
1240 this = self.sql(expression, "this")
1241 if self.dialect.HEX_START:
1242 return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
1243 return f"{int(this, 16)}"
1244
1245 def bytestring_sql(self, expression: exp.ByteString) -> str:
1246 this = self.sql(expression, "this")
1247 if self.dialect.BYTE_START:
1248 return f"{self.dialect.BYTE_START}{this}{self.dialect.BYTE_END}"
1249 return this
1250
1251 def unicodestring_sql(self, expression: exp.UnicodeString) -> str:
1252 this = self.sql(expression, "this")
1253 escape = expression.args.get("escape")
1254
1255 if self.dialect.UNICODE_START:
1256 escape_substitute = r"\\\1"
1257 left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
1258 else:
1259 escape_substitute = r"\\u\1"
1260 left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
1261
1262 if escape:
1263 escape_pattern = re.compile(rf"{escape.name}(\d+)")
1264 escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
1265 else:
1266 escape_pattern = ESCAPED_UNICODE_RE
1267 escape_sql = ""
1268
1269 if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
1270 this = escape_pattern.sub(escape_substitute, this)
1271
1272 return f"{left_quote}{this}{right_quote}{escape_sql}"
1273
1274 def rawstring_sql(self, expression: exp.RawString) -> str:
1275 string = self.escape_str(expression.this.replace("\\", "\\\\"), escape_backslash=False)
1276 return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
1277
1278 def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
1279 this = self.sql(expression, "this")
1280 specifier = self.sql(expression, "expression")
1281 specifier = f" {specifier}" if specifier and self.DATA_TYPE_SPECIFIERS_ALLOWED else ""
1282 return f"{this}{specifier}"
1283
1284 def datatype_sql(self, expression: exp.DataType) -> str:
1285 nested = ""
1286 values = ""
1287 interior = self.expressions(expression, flat=True)
1288
1289 type_value = expression.this
1290 if type_value == exp.DataType.Type.USERDEFINED and expression.args.get("kind"):
1291 type_sql = self.sql(expression, "kind")
1292 else:
1293 type_sql = (
1294 self.TYPE_MAPPING.get(type_value, type_value.value)
1295 if isinstance(type_value, exp.DataType.Type)
1296 else type_value
1297 )
1298
1299 if interior:
1300 if expression.args.get("nested"):
1301 nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}"
1302 if expression.args.get("values") is not None:
1303 delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")")
1304 values = self.expressions(expression, key="values", flat=True)
1305 values = f"{delimiters[0]}{values}{delimiters[1]}"
1306 elif type_value == exp.DataType.Type.INTERVAL:
1307 nested = f" {interior}"
1308 else:
1309 nested = f"({interior})"
1310
1311 type_sql = f"{type_sql}{nested}{values}"
1312 if self.TZ_TO_WITH_TIME_ZONE and type_value in (
1313 exp.DataType.Type.TIMETZ,
1314 exp.DataType.Type.TIMESTAMPTZ,
1315 ):
1316 type_sql = f"{type_sql} WITH TIME ZONE"
1317
1318 return type_sql
1319
1320 def directory_sql(self, expression: exp.Directory) -> str:
1321 local = "LOCAL " if expression.args.get("local") else ""
1322 row_format = self.sql(expression, "row_format")
1323 row_format = f" {row_format}" if row_format else ""
1324 return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
1325
1326 def delete_sql(self, expression: exp.Delete) -> str:
1327 this = self.sql(expression, "this")
1328 this = f" FROM {this}" if this else ""
1329 using = self.sql(expression, "using")
1330 using = f" USING {using}" if using else ""
1331 cluster = self.sql(expression, "cluster")
1332 cluster = f" {cluster}" if cluster else ""
1333 where = self.sql(expression, "where")
1334 returning = self.sql(expression, "returning")
1335 limit = self.sql(expression, "limit")
1336 tables = self.expressions(expression, key="tables")
1337 tables = f" {tables}" if tables else ""
1338 if self.RETURNING_END:
1339 expression_sql = f"{this}{using}{cluster}{where}{returning}{limit}"
1340 else:
1341 expression_sql = f"{returning}{this}{using}{cluster}{where}{limit}"
1342 return self.prepend_ctes(expression, f"DELETE{tables}{expression_sql}")
1343
1344 def drop_sql(self, expression: exp.Drop) -> str:
1345 this = self.sql(expression, "this")
1346 expressions = self.expressions(expression, flat=True)
1347 expressions = f" ({expressions})" if expressions else ""
1348 kind = expression.args["kind"]
1349 kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1350 exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
1351 concurrently_sql = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1352 on_cluster = self.sql(expression, "cluster")
1353 on_cluster = f" {on_cluster}" if on_cluster else ""
1354 temporary = " TEMPORARY" if expression.args.get("temporary") else ""
1355 materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
1356 cascade = " CASCADE" if expression.args.get("cascade") else ""
1357 constraints = " CONSTRAINTS" if expression.args.get("constraints") else ""
1358 purge = " PURGE" if expression.args.get("purge") else ""
1359 return f"DROP{temporary}{materialized} {kind}{concurrently_sql}{exists_sql}{this}{on_cluster}{expressions}{cascade}{constraints}{purge}"
1360
1361 def set_operation(self, expression: exp.SetOperation) -> str:
1362 op_type = type(expression)
1363 op_name = op_type.key.upper()
1364
1365 distinct = expression.args.get("distinct")
1366 if (
1367 distinct is False
1368 and op_type in (exp.Except, exp.Intersect)
1369 and not self.EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE
1370 ):
1371 self.unsupported(f"{op_name} ALL is not supported")
1372
1373 default_distinct = self.dialect.SET_OP_DISTINCT_BY_DEFAULT[op_type]
1374
1375 if distinct is None:
1376 distinct = default_distinct
1377 if distinct is None:
1378 self.unsupported(f"{op_name} requires DISTINCT or ALL to be specified")
1379
1380 if distinct is default_distinct:
1381 kind = ""
1382 else:
1383 kind = " DISTINCT" if distinct else " ALL"
1384
1385 by_name = " BY NAME" if expression.args.get("by_name") else ""
1386 return f"{op_name}{kind}{by_name}"
1387
1388 def set_operations(self, expression: exp.SetOperation) -> str:
1389 if not self.SET_OP_MODIFIERS:
1390 limit = expression.args.get("limit")
1391 order = expression.args.get("order")
1392
1393 if limit or order:
1394 select = self._move_ctes_to_top_level(
1395 exp.subquery(expression, "_l_0", copy=False).select("*", copy=False)
1396 )
1397
1398 if limit:
1399 select = select.limit(limit.pop(), copy=False)
1400 if order:
1401 select = select.order_by(order.pop(), copy=False)
1402 return self.sql(select)
1403
1404 sqls: t.List[str] = []
1405 stack: t.List[t.Union[str, exp.Expression]] = [expression]
1406
1407 while stack:
1408 node = stack.pop()
1409
1410 if isinstance(node, exp.SetOperation):
1411 stack.append(node.expression)
1412 stack.append(
1413 self.maybe_comment(
1414 self.set_operation(node), comments=node.comments, separated=True
1415 )
1416 )
1417 stack.append(node.this)
1418 else:
1419 sqls.append(self.sql(node))
1420
1421 this = self.sep().join(sqls)
1422 this = self.query_modifiers(expression, this)
1423 return self.prepend_ctes(expression, this)
1424
1425 def fetch_sql(self, expression: exp.Fetch) -> str:
1426 direction = expression.args.get("direction")
1427 direction = f" {direction}" if direction else ""
1428 count = self.sql(expression, "count")
1429 count = f" {count}" if count else ""
1430 if expression.args.get("percent"):
1431 count = f"{count} PERCENT"
1432 with_ties_or_only = "WITH TIES" if expression.args.get("with_ties") else "ONLY"
1433 return f"{self.seg('FETCH')}{direction}{count} ROWS {with_ties_or_only}"
1434
1435 def filter_sql(self, expression: exp.Filter) -> str:
1436 if self.AGGREGATE_FILTER_SUPPORTED:
1437 this = self.sql(expression, "this")
1438 where = self.sql(expression, "expression").strip()
1439 return f"{this} FILTER({where})"
1440
1441 agg = expression.this
1442 agg_arg = agg.this
1443 cond = expression.expression.this
1444 agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy()))
1445 return self.sql(agg)
1446
1447 def hint_sql(self, expression: exp.Hint) -> str:
1448 if not self.QUERY_HINTS:
1449 self.unsupported("Hints are not supported")
1450 return ""
1451
1452 return f" /*+ {self.expressions(expression, sep=self.QUERY_HINT_SEP).strip()} */"
1453
1454 def indexparameters_sql(self, expression: exp.IndexParameters) -> str:
1455 using = self.sql(expression, "using")
1456 using = f" USING {using}" if using else ""
1457 columns = self.expressions(expression, key="columns", flat=True)
1458 columns = f"({columns})" if columns else ""
1459 partition_by = self.expressions(expression, key="partition_by", flat=True)
1460 partition_by = f" PARTITION BY {partition_by}" if partition_by else ""
1461 where = self.sql(expression, "where")
1462 include = self.expressions(expression, key="include", flat=True)
1463 if include:
1464 include = f" INCLUDE ({include})"
1465 with_storage = self.expressions(expression, key="with_storage", flat=True)
1466 with_storage = f" WITH ({with_storage})" if with_storage else ""
1467 tablespace = self.sql(expression, "tablespace")
1468 tablespace = f" USING INDEX TABLESPACE {tablespace}" if tablespace else ""
1469 on = self.sql(expression, "on")
1470 on = f" ON {on}" if on else ""
1471
1472 return f"{using}{columns}{include}{with_storage}{tablespace}{partition_by}{where}{on}"
1473
1474 def index_sql(self, expression: exp.Index) -> str:
1475 unique = "UNIQUE " if expression.args.get("unique") else ""
1476 primary = "PRIMARY " if expression.args.get("primary") else ""
1477 amp = "AMP " if expression.args.get("amp") else ""
1478 name = self.sql(expression, "this")
1479 name = f"{name} " if name else ""
1480 table = self.sql(expression, "table")
1481 table = f"{self.INDEX_ON} {table}" if table else ""
1482
1483 index = "INDEX " if not table else ""
1484
1485 params = self.sql(expression, "params")
1486 return f"{unique}{primary}{amp}{index}{name}{table}{params}"
1487
1488 def identifier_sql(self, expression: exp.Identifier) -> str:
1489 text = expression.name
1490 lower = text.lower()
1491 text = lower if self.normalize and not expression.quoted else text
1492 text = text.replace(self._identifier_end, self._escaped_identifier_end)
1493 if (
1494 expression.quoted
1495 or self.dialect.can_identify(text, self.identify)
1496 or lower in self.RESERVED_KEYWORDS
1497 or (not self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT and text[:1].isdigit())
1498 ):
1499 text = f"{self._identifier_start}{text}{self._identifier_end}"
1500 return text
1501
1502 def hex_sql(self, expression: exp.Hex) -> str:
1503 text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1504 if self.dialect.HEX_LOWERCASE:
1505 text = self.func("LOWER", text)
1506
1507 return text
1508
1509 def lowerhex_sql(self, expression: exp.LowerHex) -> str:
1510 text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1511 if not self.dialect.HEX_LOWERCASE:
1512 text = self.func("LOWER", text)
1513 return text
1514
1515 def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str:
1516 input_format = self.sql(expression, "input_format")
1517 input_format = f"INPUTFORMAT {input_format}" if input_format else ""
1518 output_format = self.sql(expression, "output_format")
1519 output_format = f"OUTPUTFORMAT {output_format}" if output_format else ""
1520 return self.sep().join((input_format, output_format))
1521
1522 def national_sql(self, expression: exp.National, prefix: str = "N") -> str:
1523 string = self.sql(exp.Literal.string(expression.name))
1524 return f"{prefix}{string}"
1525
1526 def partition_sql(self, expression: exp.Partition) -> str:
1527 return f"PARTITION({self.expressions(expression, flat=True)})"
1528
1529 def properties_sql(self, expression: exp.Properties) -> str:
1530 root_properties = []
1531 with_properties = []
1532
1533 for p in expression.expressions:
1534 p_loc = self.PROPERTIES_LOCATION[p.__class__]
1535 if p_loc == exp.Properties.Location.POST_WITH:
1536 with_properties.append(p)
1537 elif p_loc == exp.Properties.Location.POST_SCHEMA:
1538 root_properties.append(p)
1539
1540 root_props = self.root_properties(exp.Properties(expressions=root_properties))
1541 with_props = self.with_properties(exp.Properties(expressions=with_properties))
1542
1543 if root_props and with_props and not self.pretty:
1544 with_props = " " + with_props
1545
1546 return root_props + with_props
1547
1548 def root_properties(self, properties: exp.Properties) -> str:
1549 if properties.expressions:
1550 return self.expressions(properties, indent=False, sep=" ")
1551 return ""
1552
1553 def properties(
1554 self,
1555 properties: exp.Properties,
1556 prefix: str = "",
1557 sep: str = ", ",
1558 suffix: str = "",
1559 wrapped: bool = True,
1560 ) -> str:
1561 if properties.expressions:
1562 expressions = self.expressions(properties, sep=sep, indent=False)
1563 if expressions:
1564 expressions = self.wrap(expressions) if wrapped else expressions
1565 return f"{prefix}{' ' if prefix.strip() else ''}{expressions}{suffix}"
1566 return ""
1567
1568 def with_properties(self, properties: exp.Properties) -> str:
1569 return self.properties(properties, prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""))
1570
1571 def locate_properties(self, properties: exp.Properties) -> t.DefaultDict:
1572 properties_locs = defaultdict(list)
1573 for p in properties.expressions:
1574 p_loc = self.PROPERTIES_LOCATION[p.__class__]
1575 if p_loc != exp.Properties.Location.UNSUPPORTED:
1576 properties_locs[p_loc].append(p)
1577 else:
1578 self.unsupported(f"Unsupported property {p.key}")
1579
1580 return properties_locs
1581
1582 def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
1583 if isinstance(expression.this, exp.Dot):
1584 return self.sql(expression, "this")
1585 return f"'{expression.name}'" if string_key else expression.name
1586
1587 def property_sql(self, expression: exp.Property) -> str:
1588 property_cls = expression.__class__
1589 if property_cls == exp.Property:
1590 return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
1591
1592 property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
1593 if not property_name:
1594 self.unsupported(f"Unsupported property {expression.key}")
1595
1596 return f"{property_name}={self.sql(expression, 'this')}"
1597
1598 def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1599 if self.SUPPORTS_CREATE_TABLE_LIKE:
1600 options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions)
1601 options = f" {options}" if options else ""
1602
1603 like = f"LIKE {self.sql(expression, 'this')}{options}"
1604 if self.LIKE_PROPERTY_INSIDE_SCHEMA and not isinstance(expression.parent, exp.Schema):
1605 like = f"({like})"
1606
1607 return like
1608
1609 if expression.expressions:
1610 self.unsupported("Transpilation of LIKE property options is unsupported")
1611
1612 select = exp.select("*").from_(expression.this).limit(0)
1613 return f"AS {self.sql(select)}"
1614
1615 def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str:
1616 no = "NO " if expression.args.get("no") else ""
1617 protection = " PROTECTION" if expression.args.get("protection") else ""
1618 return f"{no}FALLBACK{protection}"
1619
1620 def journalproperty_sql(self, expression: exp.JournalProperty) -> str:
1621 no = "NO " if expression.args.get("no") else ""
1622 local = expression.args.get("local")
1623 local = f"{local} " if local else ""
1624 dual = "DUAL " if expression.args.get("dual") else ""
1625 before = "BEFORE " if expression.args.get("before") else ""
1626 after = "AFTER " if expression.args.get("after") else ""
1627 return f"{no}{local}{dual}{before}{after}JOURNAL"
1628
1629 def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str:
1630 freespace = self.sql(expression, "this")
1631 percent = " PERCENT" if expression.args.get("percent") else ""
1632 return f"FREESPACE={freespace}{percent}"
1633
1634 def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str:
1635 if expression.args.get("default"):
1636 property = "DEFAULT"
1637 elif expression.args.get("on"):
1638 property = "ON"
1639 else:
1640 property = "OFF"
1641 return f"CHECKSUM={property}"
1642
1643 def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str:
1644 if expression.args.get("no"):
1645 return "NO MERGEBLOCKRATIO"
1646 if expression.args.get("default"):
1647 return "DEFAULT MERGEBLOCKRATIO"
1648
1649 percent = " PERCENT" if expression.args.get("percent") else ""
1650 return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
1651
1652 def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str:
1653 default = expression.args.get("default")
1654 minimum = expression.args.get("minimum")
1655 maximum = expression.args.get("maximum")
1656 if default or minimum or maximum:
1657 if default:
1658 prop = "DEFAULT"
1659 elif minimum:
1660 prop = "MINIMUM"
1661 else:
1662 prop = "MAXIMUM"
1663 return f"{prop} DATABLOCKSIZE"
1664 units = expression.args.get("units")
1665 units = f" {units}" if units else ""
1666 return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
1667
1668 def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str:
1669 autotemp = expression.args.get("autotemp")
1670 always = expression.args.get("always")
1671 default = expression.args.get("default")
1672 manual = expression.args.get("manual")
1673 never = expression.args.get("never")
1674
1675 if autotemp is not None:
1676 prop = f"AUTOTEMP({self.expressions(autotemp)})"
1677 elif always:
1678 prop = "ALWAYS"
1679 elif default:
1680 prop = "DEFAULT"
1681 elif manual:
1682 prop = "MANUAL"
1683 elif never:
1684 prop = "NEVER"
1685 return f"BLOCKCOMPRESSION={prop}"
1686
1687 def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str:
1688 no = expression.args.get("no")
1689 no = " NO" if no else ""
1690 concurrent = expression.args.get("concurrent")
1691 concurrent = " CONCURRENT" if concurrent else ""
1692 target = self.sql(expression, "target")
1693 target = f" {target}" if target else ""
1694 return f"WITH{no}{concurrent} ISOLATED LOADING{target}"
1695
1696 def partitionboundspec_sql(self, expression: exp.PartitionBoundSpec) -> str:
1697 if isinstance(expression.this, list):
1698 return f"IN ({self.expressions(expression, key='this', flat=True)})"
1699 if expression.this:
1700 modulus = self.sql(expression, "this")
1701 remainder = self.sql(expression, "expression")
1702 return f"WITH (MODULUS {modulus}, REMAINDER {remainder})"
1703
1704 from_expressions = self.expressions(expression, key="from_expressions", flat=True)
1705 to_expressions = self.expressions(expression, key="to_expressions", flat=True)
1706 return f"FROM ({from_expressions}) TO ({to_expressions})"
1707
1708 def partitionedofproperty_sql(self, expression: exp.PartitionedOfProperty) -> str:
1709 this = self.sql(expression, "this")
1710
1711 for_values_or_default = expression.expression
1712 if isinstance(for_values_or_default, exp.PartitionBoundSpec):
1713 for_values_or_default = f" FOR VALUES {self.sql(for_values_or_default)}"
1714 else:
1715 for_values_or_default = " DEFAULT"
1716
1717 return f"PARTITION OF {this}{for_values_or_default}"
1718
1719 def lockingproperty_sql(self, expression: exp.LockingProperty) -> str:
1720 kind = expression.args.get("kind")
1721 this = f" {self.sql(expression, 'this')}" if expression.this else ""
1722 for_or_in = expression.args.get("for_or_in")
1723 for_or_in = f" {for_or_in}" if for_or_in else ""
1724 lock_type = expression.args.get("lock_type")
1725 override = " OVERRIDE" if expression.args.get("override") else ""
1726 return f"LOCKING {kind}{this}{for_or_in} {lock_type}{override}"
1727
1728 def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str:
1729 data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA"
1730 statistics = expression.args.get("statistics")
1731 statistics_sql = ""
1732 if statistics is not None:
1733 statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS"
1734 return f"{data_sql}{statistics_sql}"
1735
1736 def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
1737 this = self.sql(expression, "this")
1738 this = f"HISTORY_TABLE={this}" if this else ""
1739 data_consistency: t.Optional[str] = self.sql(expression, "data_consistency")
1740 data_consistency = (
1741 f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
1742 )
1743 retention_period: t.Optional[str] = self.sql(expression, "retention_period")
1744 retention_period = (
1745 f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
1746 )
1747
1748 if this:
1749 on_sql = self.func("ON", this, data_consistency, retention_period)
1750 else:
1751 on_sql = "ON" if expression.args.get("on") else "OFF"
1752
1753 sql = f"SYSTEM_VERSIONING={on_sql}"
1754
1755 return f"WITH({sql})" if expression.args.get("with") else sql
1756
1757 def insert_sql(self, expression: exp.Insert) -> str:
1758 hint = self.sql(expression, "hint")
1759 overwrite = expression.args.get("overwrite")
1760
1761 if isinstance(expression.this, exp.Directory):
1762 this = " OVERWRITE" if overwrite else " INTO"
1763 else:
1764 this = self.INSERT_OVERWRITE if overwrite else " INTO"
1765
1766 stored = self.sql(expression, "stored")
1767 stored = f" {stored}" if stored else ""
1768 alternative = expression.args.get("alternative")
1769 alternative = f" OR {alternative}" if alternative else ""
1770 ignore = " IGNORE" if expression.args.get("ignore") else ""
1771 is_function = expression.args.get("is_function")
1772 if is_function:
1773 this = f"{this} FUNCTION"
1774 this = f"{this} {self.sql(expression, 'this')}"
1775
1776 exists = " IF EXISTS" if expression.args.get("exists") else ""
1777 where = self.sql(expression, "where")
1778 where = f"{self.sep()}REPLACE WHERE {where}" if where else ""
1779 expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
1780 on_conflict = self.sql(expression, "conflict")
1781 on_conflict = f" {on_conflict}" if on_conflict else ""
1782 by_name = " BY NAME" if expression.args.get("by_name") else ""
1783 returning = self.sql(expression, "returning")
1784
1785 if self.RETURNING_END:
1786 expression_sql = f"{expression_sql}{on_conflict}{returning}"
1787 else:
1788 expression_sql = f"{returning}{expression_sql}{on_conflict}"
1789
1790 partition_by = self.sql(expression, "partition")
1791 partition_by = f" {partition_by}" if partition_by else ""
1792 settings = self.sql(expression, "settings")
1793 settings = f" {settings}" if settings else ""
1794
1795 source = self.sql(expression, "source")
1796 source = f"TABLE {source}" if source else ""
1797
1798 sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}{source}"
1799 return self.prepend_ctes(expression, sql)
1800
1801 def introducer_sql(self, expression: exp.Introducer) -> str:
1802 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
1803
1804 def kill_sql(self, expression: exp.Kill) -> str:
1805 kind = self.sql(expression, "kind")
1806 kind = f" {kind}" if kind else ""
1807 this = self.sql(expression, "this")
1808 this = f" {this}" if this else ""
1809 return f"KILL{kind}{this}"
1810
1811 def pseudotype_sql(self, expression: exp.PseudoType) -> str:
1812 return expression.name
1813
1814 def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
1815 return expression.name
1816
1817 def onconflict_sql(self, expression: exp.OnConflict) -> str:
1818 conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
1819
1820 constraint = self.sql(expression, "constraint")
1821 constraint = f" ON CONSTRAINT {constraint}" if constraint else ""
1822
1823 conflict_keys = self.expressions(expression, key="conflict_keys", flat=True)
1824 conflict_keys = f"({conflict_keys}) " if conflict_keys else " "
1825 action = self.sql(expression, "action")
1826
1827 expressions = self.expressions(expression, flat=True)
1828 if expressions:
1829 set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
1830 expressions = f" {set_keyword}{expressions}"
1831
1832 return f"{conflict}{constraint}{conflict_keys}{action}{expressions}"
1833
1834 def returning_sql(self, expression: exp.Returning) -> str:
1835 return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
1836
1837 def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
1838 fields = self.sql(expression, "fields")
1839 fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
1840 escaped = self.sql(expression, "escaped")
1841 escaped = f" ESCAPED BY {escaped}" if escaped else ""
1842 items = self.sql(expression, "collection_items")
1843 items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
1844 keys = self.sql(expression, "map_keys")
1845 keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
1846 lines = self.sql(expression, "lines")
1847 lines = f" LINES TERMINATED BY {lines}" if lines else ""
1848 null = self.sql(expression, "null")
1849 null = f" NULL DEFINED AS {null}" if null else ""
1850 return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
1851
1852 def withtablehint_sql(self, expression: exp.WithTableHint) -> str:
1853 return f"WITH ({self.expressions(expression, flat=True)})"
1854
1855 def indextablehint_sql(self, expression: exp.IndexTableHint) -> str:
1856 this = f"{self.sql(expression, 'this')} INDEX"
1857 target = self.sql(expression, "target")
1858 target = f" FOR {target}" if target else ""
1859 return f"{this}{target} ({self.expressions(expression, flat=True)})"
1860
1861 def historicaldata_sql(self, expression: exp.HistoricalData) -> str:
1862 this = self.sql(expression, "this")
1863 kind = self.sql(expression, "kind")
1864 expr = self.sql(expression, "expression")
1865 return f"{this} ({kind} => {expr})"
1866
1867 def table_parts(self, expression: exp.Table) -> str:
1868 return ".".join(
1869 self.sql(part)
1870 for part in (
1871 expression.args.get("catalog"),
1872 expression.args.get("db"),
1873 expression.args.get("this"),
1874 )
1875 if part is not None
1876 )
1877
1878 def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
1879 table = self.table_parts(expression)
1880 only = "ONLY " if expression.args.get("only") else ""
1881 partition = self.sql(expression, "partition")
1882 partition = f" {partition}" if partition else ""
1883 version = self.sql(expression, "version")
1884 version = f" {version}" if version else ""
1885 alias = self.sql(expression, "alias")
1886 alias = f"{sep}{alias}" if alias else ""
1887
1888 sample = self.sql(expression, "sample")
1889 if self.dialect.ALIAS_POST_TABLESAMPLE:
1890 sample_pre_alias = sample
1891 sample_post_alias = ""
1892 else:
1893 sample_pre_alias = ""
1894 sample_post_alias = sample
1895
1896 hints = self.expressions(expression, key="hints", sep=" ")
1897 hints = f" {hints}" if hints and self.TABLE_HINTS else ""
1898 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
1899 joins = self.indent(
1900 self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
1901 )
1902 laterals = self.expressions(expression, key="laterals", sep="")
1903
1904 file_format = self.sql(expression, "format")
1905 if file_format:
1906 pattern = self.sql(expression, "pattern")
1907 pattern = f", PATTERN => {pattern}" if pattern else ""
1908 file_format = f" (FILE_FORMAT => {file_format}{pattern})"
1909
1910 ordinality = expression.args.get("ordinality") or ""
1911 if ordinality:
1912 ordinality = f" WITH ORDINALITY{alias}"
1913 alias = ""
1914
1915 when = self.sql(expression, "when")
1916 if when:
1917 table = f"{table} {when}"
1918
1919 changes = self.sql(expression, "changes")
1920 changes = f" {changes}" if changes else ""
1921
1922 rows_from = self.expressions(expression, key="rows_from")
1923 if rows_from:
1924 table = f"ROWS FROM {self.wrap(rows_from)}"
1925
1926 return f"{only}{table}{changes}{partition}{version}{file_format}{sample_pre_alias}{alias}{hints}{pivots}{sample_post_alias}{joins}{laterals}{ordinality}"
1927
1928 def tablesample_sql(
1929 self,
1930 expression: exp.TableSample,
1931 tablesample_keyword: t.Optional[str] = None,
1932 ) -> str:
1933 method = self.sql(expression, "method")
1934 method = f"{method} " if method and self.TABLESAMPLE_WITH_METHOD else ""
1935 numerator = self.sql(expression, "bucket_numerator")
1936 denominator = self.sql(expression, "bucket_denominator")
1937 field = self.sql(expression, "bucket_field")
1938 field = f" ON {field}" if field else ""
1939 bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else ""
1940 seed = self.sql(expression, "seed")
1941 seed = f" {self.TABLESAMPLE_SEED_KEYWORD} ({seed})" if seed else ""
1942
1943 size = self.sql(expression, "size")
1944 if size and self.TABLESAMPLE_SIZE_IS_ROWS:
1945 size = f"{size} ROWS"
1946
1947 percent = self.sql(expression, "percent")
1948 if percent and not self.dialect.TABLESAMPLE_SIZE_IS_PERCENT:
1949 percent = f"{percent} PERCENT"
1950
1951 expr = f"{bucket}{percent}{size}"
1952 if self.TABLESAMPLE_REQUIRES_PARENS:
1953 expr = f"({expr})"
1954
1955 return f" {tablesample_keyword or self.TABLESAMPLE_KEYWORDS} {method}{expr}{seed}"
1956
1957 def pivot_sql(self, expression: exp.Pivot) -> str:
1958 expressions = self.expressions(expression, flat=True)
1959
1960 if expression.this:
1961 this = self.sql(expression, "this")
1962 if not expressions:
1963 return f"UNPIVOT {this}"
1964
1965 on = f"{self.seg('ON')} {expressions}"
1966 using = self.expressions(expression, key="using", flat=True)
1967 using = f"{self.seg('USING')} {using}" if using else ""
1968 group = self.sql(expression, "group")
1969 return f"PIVOT {this}{on}{using}{group}"
1970
1971 alias = self.sql(expression, "alias")
1972 alias = f" AS {alias}" if alias else ""
1973 direction = self.seg("UNPIVOT" if expression.unpivot else "PIVOT")
1974
1975 field = self.sql(expression, "field")
1976
1977 include_nulls = expression.args.get("include_nulls")
1978 if include_nulls is not None:
1979 nulls = " INCLUDE NULLS " if include_nulls else " EXCLUDE NULLS "
1980 else:
1981 nulls = ""
1982
1983 default_on_null = self.sql(expression, "default_on_null")
1984 default_on_null = f" DEFAULT ON NULL ({default_on_null})" if default_on_null else ""
1985 return f"{direction}{nulls}({expressions} FOR {field}{default_on_null}){alias}"
1986
1987 def version_sql(self, expression: exp.Version) -> str:
1988 this = f"FOR {expression.name}"
1989 kind = expression.text("kind")
1990 expr = self.sql(expression, "expression")
1991 return f"{this} {kind} {expr}"
1992
1993 def tuple_sql(self, expression: exp.Tuple) -> str:
1994 return f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
1995
1996 def update_sql(self, expression: exp.Update) -> str:
1997 this = self.sql(expression, "this")
1998 set_sql = self.expressions(expression, flat=True)
1999 from_sql = self.sql(expression, "from")
2000 where_sql = self.sql(expression, "where")
2001 returning = self.sql(expression, "returning")
2002 order = self.sql(expression, "order")
2003 limit = self.sql(expression, "limit")
2004 if self.RETURNING_END:
2005 expression_sql = f"{from_sql}{where_sql}{returning}"
2006 else:
2007 expression_sql = f"{returning}{from_sql}{where_sql}"
2008 sql = f"UPDATE {this} SET {set_sql}{expression_sql}{order}{limit}"
2009 return self.prepend_ctes(expression, sql)
2010
2011 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
2012 values_as_table = values_as_table and self.VALUES_AS_TABLE
2013
2014 # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
2015 if values_as_table or not expression.find_ancestor(exp.From, exp.Join):
2016 args = self.expressions(expression)
2017 alias = self.sql(expression, "alias")
2018 values = f"VALUES{self.seg('')}{args}"
2019 values = (
2020 f"({values})"
2021 if self.WRAP_DERIVED_VALUES
2022 and (alias or isinstance(expression.parent, (exp.From, exp.Table)))
2023 else values
2024 )
2025 return f"{values} AS {alias}" if alias else values
2026
2027 # Converts `VALUES...` expression into a series of select unions.
2028 alias_node = expression.args.get("alias")
2029 column_names = alias_node and alias_node.columns
2030
2031 selects: t.List[exp.Query] = []
2032
2033 for i, tup in enumerate(expression.expressions):
2034 row = tup.expressions
2035
2036 if i == 0 and column_names:
2037 row = [
2038 exp.alias_(value, column_name) for value, column_name in zip(row, column_names)
2039 ]
2040
2041 selects.append(exp.Select(expressions=row))
2042
2043 if self.pretty:
2044 # This may result in poor performance for large-cardinality `VALUES` tables, due to
2045 # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
2046 # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
2047 query = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
2048 return self.subquery_sql(query.subquery(alias_node and alias_node.this, copy=False))
2049
2050 alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
2051 unions = " UNION ALL ".join(self.sql(select) for select in selects)
2052 return f"({unions}){alias}"
2053
2054 def var_sql(self, expression: exp.Var) -> str:
2055 return self.sql(expression, "this")
2056
2057 @unsupported_args("expressions")
2058 def into_sql(self, expression: exp.Into) -> str:
2059 temporary = " TEMPORARY" if expression.args.get("temporary") else ""
2060 unlogged = " UNLOGGED" if expression.args.get("unlogged") else ""
2061 return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}"
2062
2063 def from_sql(self, expression: exp.From) -> str:
2064 return f"{self.seg('FROM')} {self.sql(expression, 'this')}"
2065
2066 def groupingsets_sql(self, expression: exp.GroupingSets) -> str:
2067 grouping_sets = self.expressions(expression, indent=False)
2068 return f"GROUPING SETS {self.wrap(grouping_sets)}"
2069
2070 def rollup_sql(self, expression: exp.Rollup) -> str:
2071 expressions = self.expressions(expression, indent=False)
2072 return f"ROLLUP {self.wrap(expressions)}" if expressions else "WITH ROLLUP"
2073
2074 def cube_sql(self, expression: exp.Cube) -> str:
2075 expressions = self.expressions(expression, indent=False)
2076 return f"CUBE {self.wrap(expressions)}" if expressions else "WITH CUBE"
2077
2078 def group_sql(self, expression: exp.Group) -> str:
2079 group_by_all = expression.args.get("all")
2080 if group_by_all is True:
2081 modifier = " ALL"
2082 elif group_by_all is False:
2083 modifier = " DISTINCT"
2084 else:
2085 modifier = ""
2086
2087 group_by = self.op_expressions(f"GROUP BY{modifier}", expression)
2088
2089 grouping_sets = self.expressions(expression, key="grouping_sets")
2090 cube = self.expressions(expression, key="cube")
2091 rollup = self.expressions(expression, key="rollup")
2092
2093 groupings = csv(
2094 self.seg(grouping_sets) if grouping_sets else "",
2095 self.seg(cube) if cube else "",
2096 self.seg(rollup) if rollup else "",
2097 self.seg("WITH TOTALS") if expression.args.get("totals") else "",
2098 sep=self.GROUPINGS_SEP,
2099 )
2100
2101 if (
2102 expression.expressions
2103 and groupings
2104 and groupings.strip() not in ("WITH CUBE", "WITH ROLLUP")
2105 ):
2106 group_by = f"{group_by}{self.GROUPINGS_SEP}"
2107
2108 return f"{group_by}{groupings}"
2109
2110 def having_sql(self, expression: exp.Having) -> str:
2111 this = self.indent(self.sql(expression, "this"))
2112 return f"{self.seg('HAVING')}{self.sep()}{this}"
2113
2114 def connect_sql(self, expression: exp.Connect) -> str:
2115 start = self.sql(expression, "start")
2116 start = self.seg(f"START WITH {start}") if start else ""
2117 nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
2118 connect = self.sql(expression, "connect")
2119 connect = self.seg(f"CONNECT BY{nocycle} {connect}")
2120 return start + connect
2121
2122 def prior_sql(self, expression: exp.Prior) -> str:
2123 return f"PRIOR {self.sql(expression, 'this')}"
2124
2125 def join_sql(self, expression: exp.Join) -> str:
2126 if not self.SEMI_ANTI_JOIN_WITH_SIDE and expression.kind in ("SEMI", "ANTI"):
2127 side = None
2128 else:
2129 side = expression.side
2130
2131 op_sql = " ".join(
2132 op
2133 for op in (
2134 expression.method,
2135 "GLOBAL" if expression.args.get("global") else None,
2136 side,
2137 expression.kind,
2138 expression.hint if self.JOIN_HINTS else None,
2139 )
2140 if op
2141 )
2142 match_cond = self.sql(expression, "match_condition")
2143 match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
2144 on_sql = self.sql(expression, "on")
2145 using = expression.args.get("using")
2146
2147 if not on_sql and using:
2148 on_sql = csv(*(self.sql(column) for column in using))
2149
2150 this = expression.this
2151 this_sql = self.sql(this)
2152
2153 exprs = self.expressions(expression)
2154 if exprs:
2155 this_sql = f"{this_sql},{self.seg(exprs)}"
2156
2157 if on_sql:
2158 on_sql = self.indent(on_sql, skip_first=True)
2159 space = self.seg(" " * self.pad) if self.pretty else " "
2160 if using:
2161 on_sql = f"{space}USING ({on_sql})"
2162 else:
2163 on_sql = f"{space}ON {on_sql}"
2164 elif not op_sql:
2165 if isinstance(this, exp.Lateral) and this.args.get("cross_apply") is not None:
2166 return f" {this_sql}"
2167
2168 return f", {this_sql}"
2169
2170 if op_sql != "STRAIGHT_JOIN":
2171 op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
2172
2173 return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}"
2174
2175 def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->") -> str:
2176 args = self.expressions(expression, flat=True)
2177 args = f"({args})" if len(args.split(",")) > 1 else args
2178 return f"{args} {arrow_sep} {self.sql(expression, 'this')}"
2179
2180 def lateral_op(self, expression: exp.Lateral) -> str:
2181 cross_apply = expression.args.get("cross_apply")
2182
2183 # https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
2184 if cross_apply is True:
2185 op = "INNER JOIN "
2186 elif cross_apply is False:
2187 op = "LEFT JOIN "
2188 else:
2189 op = ""
2190
2191 return f"{op}LATERAL"
2192
2193 def lateral_sql(self, expression: exp.Lateral) -> str:
2194 this = self.sql(expression, "this")
2195
2196 if expression.args.get("view"):
2197 alias = expression.args["alias"]
2198 columns = self.expressions(alias, key="columns", flat=True)
2199 table = f" {alias.name}" if alias.name else ""
2200 columns = f" AS {columns}" if columns else ""
2201 op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}")
2202 return f"{op_sql}{self.sep()}{this}{table}{columns}"
2203
2204 alias = self.sql(expression, "alias")
2205 alias = f" AS {alias}" if alias else ""
2206 return f"{self.lateral_op(expression)} {this}{alias}"
2207
2208 def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
2209 this = self.sql(expression, "this")
2210
2211 args = [
2212 self._simplify_unless_literal(e) if self.LIMIT_ONLY_LITERALS else e
2213 for e in (expression.args.get(k) for k in ("offset", "expression"))
2214 if e
2215 ]
2216
2217 args_sql = ", ".join(self.sql(e) for e in args)
2218 args_sql = f"({args_sql})" if top and any(not e.is_number for e in args) else args_sql
2219 expressions = self.expressions(expression, flat=True)
2220 expressions = f" BY {expressions}" if expressions else ""
2221
2222 return f"{this}{self.seg('TOP' if top else 'LIMIT')} {args_sql}{expressions}"
2223
2224 def offset_sql(self, expression: exp.Offset) -> str:
2225 this = self.sql(expression, "this")
2226 value = expression.expression
2227 value = self._simplify_unless_literal(value) if self.LIMIT_ONLY_LITERALS else value
2228 expressions = self.expressions(expression, flat=True)
2229 expressions = f" BY {expressions}" if expressions else ""
2230 return f"{this}{self.seg('OFFSET')} {self.sql(value)}{expressions}"
2231
2232 def setitem_sql(self, expression: exp.SetItem) -> str:
2233 kind = self.sql(expression, "kind")
2234 kind = f"{kind} " if kind else ""
2235 this = self.sql(expression, "this")
2236 expressions = self.expressions(expression)
2237 collate = self.sql(expression, "collate")
2238 collate = f" COLLATE {collate}" if collate else ""
2239 global_ = "GLOBAL " if expression.args.get("global") else ""
2240 return f"{global_}{kind}{this}{expressions}{collate}"
2241
2242 def set_sql(self, expression: exp.Set) -> str:
2243 expressions = (
2244 f" {self.expressions(expression, flat=True)}" if expression.expressions else ""
2245 )
2246 tag = " TAG" if expression.args.get("tag") else ""
2247 return f"{'UNSET' if expression.args.get('unset') else 'SET'}{tag}{expressions}"
2248
2249 def pragma_sql(self, expression: exp.Pragma) -> str:
2250 return f"PRAGMA {self.sql(expression, 'this')}"
2251
2252 def lock_sql(self, expression: exp.Lock) -> str:
2253 if not self.LOCKING_READS_SUPPORTED:
2254 self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported")
2255 return ""
2256
2257 lock_type = "FOR UPDATE" if expression.args["update"] else "FOR SHARE"
2258 expressions = self.expressions(expression, flat=True)
2259 expressions = f" OF {expressions}" if expressions else ""
2260 wait = expression.args.get("wait")
2261
2262 if wait is not None:
2263 if isinstance(wait, exp.Literal):
2264 wait = f" WAIT {self.sql(wait)}"
2265 else:
2266 wait = " NOWAIT" if wait else " SKIP LOCKED"
2267
2268 return f"{lock_type}{expressions}{wait or ''}"
2269
2270 def literal_sql(self, expression: exp.Literal) -> str:
2271 text = expression.this or ""
2272 if expression.is_string:
2273 text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
2274 return text
2275
2276 def escape_str(self, text: str, escape_backslash: bool = True) -> str:
2277 if self.dialect.ESCAPED_SEQUENCES:
2278 to_escaped = self.dialect.ESCAPED_SEQUENCES
2279 text = "".join(
2280 to_escaped.get(ch, ch) if escape_backslash or ch != "\\" else ch for ch in text
2281 )
2282
2283 return self._replace_line_breaks(text).replace(
2284 self.dialect.QUOTE_END, self._escaped_quote_end
2285 )
2286
2287 def loaddata_sql(self, expression: exp.LoadData) -> str:
2288 local = " LOCAL" if expression.args.get("local") else ""
2289 inpath = f" INPATH {self.sql(expression, 'inpath')}"
2290 overwrite = " OVERWRITE" if expression.args.get("overwrite") else ""
2291 this = f" INTO TABLE {self.sql(expression, 'this')}"
2292 partition = self.sql(expression, "partition")
2293 partition = f" {partition}" if partition else ""
2294 input_format = self.sql(expression, "input_format")
2295 input_format = f" INPUTFORMAT {input_format}" if input_format else ""
2296 serde = self.sql(expression, "serde")
2297 serde = f" SERDE {serde}" if serde else ""
2298 return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
2299
2300 def null_sql(self, *_) -> str:
2301 return "NULL"
2302
2303 def boolean_sql(self, expression: exp.Boolean) -> str:
2304 return "TRUE" if expression.this else "FALSE"
2305
2306 def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
2307 this = self.sql(expression, "this")
2308 this = f"{this} " if this else this
2309 siblings = "SIBLINGS " if expression.args.get("siblings") else ""
2310 return self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=this or flat) # type: ignore
2311
2312 def withfill_sql(self, expression: exp.WithFill) -> str:
2313 from_sql = self.sql(expression, "from")
2314 from_sql = f" FROM {from_sql}" if from_sql else ""
2315 to_sql = self.sql(expression, "to")
2316 to_sql = f" TO {to_sql}" if to_sql else ""
2317 step_sql = self.sql(expression, "step")
2318 step_sql = f" STEP {step_sql}" if step_sql else ""
2319 interpolated_values = [
2320 f"{self.sql(e, 'alias')} AS {self.sql(e, 'this')}"
2321 if isinstance(e, exp.Alias)
2322 else self.sql(e, "this")
2323 for e in expression.args.get("interpolate") or []
2324 ]
2325 interpolate = (
2326 f" INTERPOLATE ({', '.join(interpolated_values)})" if interpolated_values else ""
2327 )
2328 return f"WITH FILL{from_sql}{to_sql}{step_sql}{interpolate}"
2329
2330 def cluster_sql(self, expression: exp.Cluster) -> str:
2331 return self.op_expressions("CLUSTER BY", expression)
2332
2333 def distribute_sql(self, expression: exp.Distribute) -> str:
2334 return self.op_expressions("DISTRIBUTE BY", expression)
2335
2336 def sort_sql(self, expression: exp.Sort) -> str:
2337 return self.op_expressions("SORT BY", expression)
2338
2339 def ordered_sql(self, expression: exp.Ordered) -> str:
2340 desc = expression.args.get("desc")
2341 asc = not desc
2342
2343 nulls_first = expression.args.get("nulls_first")
2344 nulls_last = not nulls_first
2345 nulls_are_large = self.dialect.NULL_ORDERING == "nulls_are_large"
2346 nulls_are_small = self.dialect.NULL_ORDERING == "nulls_are_small"
2347 nulls_are_last = self.dialect.NULL_ORDERING == "nulls_are_last"
2348
2349 this = self.sql(expression, "this")
2350
2351 sort_order = " DESC" if desc else (" ASC" if desc is False else "")
2352 nulls_sort_change = ""
2353 if nulls_first and (
2354 (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last
2355 ):
2356 nulls_sort_change = " NULLS FIRST"
2357 elif (
2358 nulls_last
2359 and ((asc and nulls_are_small) or (desc and nulls_are_large))
2360 and not nulls_are_last
2361 ):
2362 nulls_sort_change = " NULLS LAST"
2363
2364 # If the NULLS FIRST/LAST clause is unsupported, we add another sort key to simulate it
2365 if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED:
2366 window = expression.find_ancestor(exp.Window, exp.Select)
2367 if isinstance(window, exp.Window) and window.args.get("spec"):
2368 self.unsupported(
2369 f"'{nulls_sort_change.strip()}' translation not supported in window functions"
2370 )
2371 nulls_sort_change = ""
2372 elif (
2373 self.NULL_ORDERING_SUPPORTED is False
2374 and (isinstance(expression.find_ancestor(exp.AggFunc, exp.Select), exp.AggFunc))
2375 and (
2376 (asc and nulls_sort_change == " NULLS LAST")
2377 or (desc and nulls_sort_change == " NULLS FIRST")
2378 )
2379 ):
2380 self.unsupported(
2381 f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
2382 )
2383 nulls_sort_change = ""
2384 elif self.NULL_ORDERING_SUPPORTED is None:
2385 if expression.this.is_int:
2386 self.unsupported(
2387 f"'{nulls_sort_change.strip()}' translation not supported with positional ordering"
2388 )
2389 elif not isinstance(expression.this, exp.Rand):
2390 null_sort_order = " DESC" if nulls_sort_change == " NULLS FIRST" else ""
2391 this = f"CASE WHEN {this} IS NULL THEN 1 ELSE 0 END{null_sort_order}, {this}"
2392 nulls_sort_change = ""
2393
2394 with_fill = self.sql(expression, "with_fill")
2395 with_fill = f" {with_fill}" if with_fill else ""
2396
2397 return f"{this}{sort_order}{nulls_sort_change}{with_fill}"
2398
2399 def matchrecognizemeasure_sql(self, expression: exp.MatchRecognizeMeasure) -> str:
2400 window_frame = self.sql(expression, "window_frame")
2401 window_frame = f"{window_frame} " if window_frame else ""
2402
2403 this = self.sql(expression, "this")
2404
2405 return f"{window_frame}{this}"
2406
2407 def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str:
2408 partition = self.partition_by_sql(expression)
2409 order = self.sql(expression, "order")
2410 measures = self.expressions(expression, key="measures")
2411 measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else ""
2412 rows = self.sql(expression, "rows")
2413 rows = self.seg(rows) if rows else ""
2414 after = self.sql(expression, "after")
2415 after = self.seg(after) if after else ""
2416 pattern = self.sql(expression, "pattern")
2417 pattern = self.seg(f"PATTERN ({pattern})") if pattern else ""
2418 definition_sqls = [
2419 f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}"
2420 for definition in expression.args.get("define", [])
2421 ]
2422 definitions = self.expressions(sqls=definition_sqls)
2423 define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else ""
2424 body = "".join(
2425 (
2426 partition,
2427 order,
2428 measures,
2429 rows,
2430 after,
2431 pattern,
2432 define,
2433 )
2434 )
2435 alias = self.sql(expression, "alias")
2436 alias = f" {alias}" if alias else ""
2437 return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
2438
2439 def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str:
2440 limit = expression.args.get("limit")
2441
2442 if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch):
2443 limit = exp.Limit(expression=exp.maybe_copy(limit.args.get("count")))
2444 elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
2445 limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
2446
2447 return csv(
2448 *sqls,
2449 *[self.sql(join) for join in expression.args.get("joins") or []],
2450 self.sql(expression, "connect"),
2451 self.sql(expression, "match"),
2452 *[self.sql(lateral) for lateral in expression.args.get("laterals") or []],
2453 self.sql(expression, "prewhere"),
2454 self.sql(expression, "where"),
2455 self.sql(expression, "group"),
2456 self.sql(expression, "having"),
2457 *[gen(self, expression) for gen in self.AFTER_HAVING_MODIFIER_TRANSFORMS.values()],
2458 self.sql(expression, "order"),
2459 *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
2460 *self.after_limit_modifiers(expression),
2461 self.options_modifier(expression),
2462 sep="",
2463 )
2464
2465 def options_modifier(self, expression: exp.Expression) -> str:
2466 options = self.expressions(expression, key="options")
2467 return f" {options}" if options else ""
2468
2469 def queryoption_sql(self, expression: exp.QueryOption) -> str:
2470 return ""
2471
2472 def offset_limit_modifiers(
2473 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
2474 ) -> t.List[str]:
2475 return [
2476 self.sql(expression, "offset") if fetch else self.sql(limit),
2477 self.sql(limit) if fetch else self.sql(expression, "offset"),
2478 ]
2479
2480 def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
2481 locks = self.expressions(expression, key="locks", sep=" ")
2482 locks = f" {locks}" if locks else ""
2483 return [locks, self.sql(expression, "sample")]
2484
2485 def select_sql(self, expression: exp.Select) -> str:
2486 into = expression.args.get("into")
2487 if not self.SUPPORTS_SELECT_INTO and into:
2488 into.pop()
2489
2490 hint = self.sql(expression, "hint")
2491 distinct = self.sql(expression, "distinct")
2492 distinct = f" {distinct}" if distinct else ""
2493 kind = self.sql(expression, "kind")
2494
2495 limit = expression.args.get("limit")
2496 if isinstance(limit, exp.Limit) and self.LIMIT_IS_TOP:
2497 top = self.limit_sql(limit, top=True)
2498 limit.pop()
2499 else:
2500 top = ""
2501
2502 expressions = self.expressions(expression)
2503
2504 if kind:
2505 if kind in self.SELECT_KINDS:
2506 kind = f" AS {kind}"
2507 else:
2508 if kind == "STRUCT":
2509 expressions = self.expressions(
2510 sqls=[
2511 self.sql(
2512 exp.Struct(
2513 expressions=[
2514 exp.PropertyEQ(this=e.args.get("alias"), expression=e.this)
2515 if isinstance(e, exp.Alias)
2516 else e
2517 for e in expression.expressions
2518 ]
2519 )
2520 )
2521 ]
2522 )
2523 kind = ""
2524
2525 operation_modifiers = self.expressions(expression, key="operation_modifiers", sep=" ")
2526 operation_modifiers = f"{self.sep()}{operation_modifiers}" if operation_modifiers else ""
2527
2528 # We use LIMIT_IS_TOP as a proxy for whether DISTINCT should go first because tsql and Teradata
2529 # are the only dialects that use LIMIT_IS_TOP and both place DISTINCT first.
2530 top_distinct = f"{distinct}{hint}{top}" if self.LIMIT_IS_TOP else f"{top}{hint}{distinct}"
2531 expressions = f"{self.sep()}{expressions}" if expressions else expressions
2532 sql = self.query_modifiers(
2533 expression,
2534 f"SELECT{top_distinct}{operation_modifiers}{kind}{expressions}",
2535 self.sql(expression, "into", comment=False),
2536 self.sql(expression, "from", comment=False),
2537 )
2538
2539 # If both the CTE and SELECT clauses have comments, generate the latter earlier
2540 if expression.args.get("with"):
2541 sql = self.maybe_comment(sql, expression)
2542 expression.pop_comments()
2543
2544 sql = self.prepend_ctes(expression, sql)
2545
2546 if not self.SUPPORTS_SELECT_INTO and into:
2547 if into.args.get("temporary"):
2548 table_kind = " TEMPORARY"
2549 elif self.SUPPORTS_UNLOGGED_TABLES and into.args.get("unlogged"):
2550 table_kind = " UNLOGGED"
2551 else:
2552 table_kind = ""
2553 sql = f"CREATE{table_kind} TABLE {self.sql(into.this)} AS {sql}"
2554
2555 return sql
2556
2557 def schema_sql(self, expression: exp.Schema) -> str:
2558 this = self.sql(expression, "this")
2559 sql = self.schema_columns_sql(expression)
2560 return f"{this} {sql}" if this and sql else this or sql
2561
2562 def schema_columns_sql(self, expression: exp.Schema) -> str:
2563 if expression.expressions:
2564 return f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}"
2565 return ""
2566
2567 def star_sql(self, expression: exp.Star) -> str:
2568 except_ = self.expressions(expression, key="except", flat=True)
2569 except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
2570 replace = self.expressions(expression, key="replace", flat=True)
2571 replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
2572 rename = self.expressions(expression, key="rename", flat=True)
2573 rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
2574 return f"*{except_}{replace}{rename}"
2575
2576 def parameter_sql(self, expression: exp.Parameter) -> str:
2577 this = self.sql(expression, "this")
2578 return f"{self.PARAMETER_TOKEN}{this}"
2579
2580 def sessionparameter_sql(self, expression: exp.SessionParameter) -> str:
2581 this = self.sql(expression, "this")
2582 kind = expression.text("kind")
2583 if kind:
2584 kind = f"{kind}."
2585 return f"@@{kind}{this}"
2586
2587 def placeholder_sql(self, expression: exp.Placeholder) -> str:
2588 return f"{self.NAMED_PLACEHOLDER_TOKEN}{expression.name}" if expression.this else "?"
2589
2590 def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
2591 alias = self.sql(expression, "alias")
2592 alias = f"{sep}{alias}" if alias else ""
2593 sample = self.sql(expression, "sample")
2594 if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
2595 alias = f"{sample}{alias}"
2596
2597 # Set to None so it's not generated again by self.query_modifiers()
2598 expression.set("sample", None)
2599
2600 pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2601 sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
2602 return self.prepend_ctes(expression, sql)
2603
2604 def qualify_sql(self, expression: exp.Qualify) -> str:
2605 this = self.indent(self.sql(expression, "this"))
2606 return f"{self.seg('QUALIFY')}{self.sep()}{this}"
2607
2608 def unnest_sql(self, expression: exp.Unnest) -> str:
2609 args = self.expressions(expression, flat=True)
2610
2611 alias = expression.args.get("alias")
2612 offset = expression.args.get("offset")
2613
2614 if self.UNNEST_WITH_ORDINALITY:
2615 if alias and isinstance(offset, exp.Expression):
2616 alias.append("columns", offset)
2617
2618 if alias and self.dialect.UNNEST_COLUMN_ONLY:
2619 columns = alias.columns
2620 alias = self.sql(columns[0]) if columns else ""
2621 else:
2622 alias = self.sql(alias)
2623
2624 alias = f" AS {alias}" if alias else alias
2625 if self.UNNEST_WITH_ORDINALITY:
2626 suffix = f" WITH ORDINALITY{alias}" if offset else alias
2627 else:
2628 if isinstance(offset, exp.Expression):
2629 suffix = f"{alias} WITH OFFSET AS {self.sql(offset)}"
2630 elif offset:
2631 suffix = f"{alias} WITH OFFSET"
2632 else:
2633 suffix = alias
2634
2635 return f"UNNEST({args}){suffix}"
2636
2637 def prewhere_sql(self, expression: exp.PreWhere) -> str:
2638 return ""
2639
2640 def where_sql(self, expression: exp.Where) -> str:
2641 this = self.indent(self.sql(expression, "this"))
2642 return f"{self.seg('WHERE')}{self.sep()}{this}"
2643
2644 def window_sql(self, expression: exp.Window) -> str:
2645 this = self.sql(expression, "this")
2646 partition = self.partition_by_sql(expression)
2647 order = expression.args.get("order")
2648 order = self.order_sql(order, flat=True) if order else ""
2649 spec = self.sql(expression, "spec")
2650 alias = self.sql(expression, "alias")
2651 over = self.sql(expression, "over") or "OVER"
2652
2653 this = f"{this} {'AS' if expression.arg_key == 'windows' else over}"
2654
2655 first = expression.args.get("first")
2656 if first is None:
2657 first = ""
2658 else:
2659 first = "FIRST" if first else "LAST"
2660
2661 if not partition and not order and not spec and alias:
2662 return f"{this} {alias}"
2663
2664 args = " ".join(arg for arg in (alias, first, partition, order, spec) if arg)
2665 return f"{this} ({args})"
2666
2667 def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str:
2668 partition = self.expressions(expression, key="partition_by", flat=True)
2669 return f"PARTITION BY {partition}" if partition else ""
2670
2671 def windowspec_sql(self, expression: exp.WindowSpec) -> str:
2672 kind = self.sql(expression, "kind")
2673 start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ")
2674 end = (
2675 csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
2676 or "CURRENT ROW"
2677 )
2678 return f"{kind} BETWEEN {start} AND {end}"
2679
2680 def withingroup_sql(self, expression: exp.WithinGroup) -> str:
2681 this = self.sql(expression, "this")
2682 expression_sql = self.sql(expression, "expression")[1:] # order has a leading space
2683 return f"{this} WITHIN GROUP ({expression_sql})"
2684
2685 def between_sql(self, expression: exp.Between) -> str:
2686 this = self.sql(expression, "this")
2687 low = self.sql(expression, "low")
2688 high = self.sql(expression, "high")
2689 return f"{this} BETWEEN {low} AND {high}"
2690
2691 def bracket_offset_expressions(
2692 self, expression: exp.Bracket, index_offset: t.Optional[int] = None
2693 ) -> t.List[exp.Expression]:
2694 return apply_index_offset(
2695 expression.this,
2696 expression.expressions,
2697 (index_offset or self.dialect.INDEX_OFFSET) - expression.args.get("offset", 0),
2698 )
2699
2700 def bracket_sql(self, expression: exp.Bracket) -> str:
2701 expressions = self.bracket_offset_expressions(expression)
2702 expressions_sql = ", ".join(self.sql(e) for e in expressions)
2703 return f"{self.sql(expression, 'this')}[{expressions_sql}]"
2704
2705 def all_sql(self, expression: exp.All) -> str:
2706 return f"ALL {self.wrap(expression)}"
2707
2708 def any_sql(self, expression: exp.Any) -> str:
2709 this = self.sql(expression, "this")
2710 if isinstance(expression.this, (*exp.UNWRAPPED_QUERIES, exp.Paren)):
2711 if isinstance(expression.this, exp.UNWRAPPED_QUERIES):
2712 this = self.wrap(this)
2713 return f"ANY{this}"
2714 return f"ANY {this}"
2715
2716 def exists_sql(self, expression: exp.Exists) -> str:
2717 return f"EXISTS{self.wrap(expression)}"
2718
2719 def case_sql(self, expression: exp.Case) -> str:
2720 this = self.sql(expression, "this")
2721 statements = [f"CASE {this}" if this else "CASE"]
2722
2723 for e in expression.args["ifs"]:
2724 statements.append(f"WHEN {self.sql(e, 'this')}")
2725 statements.append(f"THEN {self.sql(e, 'true')}")
2726
2727 default = self.sql(expression, "default")
2728
2729 if default:
2730 statements.append(f"ELSE {default}")
2731
2732 statements.append("END")
2733
2734 if self.pretty and self.too_wide(statements):
2735 return self.indent("\n".join(statements), skip_first=True, skip_last=True)
2736
2737 return " ".join(statements)
2738
2739 def constraint_sql(self, expression: exp.Constraint) -> str:
2740 this = self.sql(expression, "this")
2741 expressions = self.expressions(expression, flat=True)
2742 return f"CONSTRAINT {this} {expressions}"
2743
2744 def nextvaluefor_sql(self, expression: exp.NextValueFor) -> str:
2745 order = expression.args.get("order")
2746 order = f" OVER ({self.order_sql(order, flat=True)})" if order else ""
2747 return f"NEXT VALUE FOR {self.sql(expression, 'this')}{order}"
2748
2749 def extract_sql(self, expression: exp.Extract) -> str:
2750 this = self.sql(expression, "this") if self.EXTRACT_ALLOWS_QUOTES else expression.this.name
2751 expression_sql = self.sql(expression, "expression")
2752 return f"EXTRACT({this} FROM {expression_sql})"
2753
2754 def trim_sql(self, expression: exp.Trim) -> str:
2755 trim_type = self.sql(expression, "position")
2756
2757 if trim_type == "LEADING":
2758 func_name = "LTRIM"
2759 elif trim_type == "TRAILING":
2760 func_name = "RTRIM"
2761 else:
2762 func_name = "TRIM"
2763
2764 return self.func(func_name, expression.this, expression.expression)
2765
2766 def convert_concat_args(self, expression: exp.Concat | exp.ConcatWs) -> t.List[exp.Expression]:
2767 args = expression.expressions
2768 if isinstance(expression, exp.ConcatWs):
2769 args = args[1:] # Skip the delimiter
2770
2771 if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
2772 args = [exp.cast(e, exp.DataType.Type.TEXT) for e in args]
2773
2774 if not self.dialect.CONCAT_COALESCE and expression.args.get("coalesce"):
2775 args = [exp.func("coalesce", e, exp.Literal.string("")) for e in args]
2776
2777 return args
2778
2779 def concat_sql(self, expression: exp.Concat) -> str:
2780 expressions = self.convert_concat_args(expression)
2781
2782 # Some dialects don't allow a single-argument CONCAT call
2783 if not self.SUPPORTS_SINGLE_ARG_CONCAT and len(expressions) == 1:
2784 return self.sql(expressions[0])
2785
2786 return self.func("CONCAT", *expressions)
2787
2788 def concatws_sql(self, expression: exp.ConcatWs) -> str:
2789 return self.func(
2790 "CONCAT_WS", seq_get(expression.expressions, 0), *self.convert_concat_args(expression)
2791 )
2792
2793 def check_sql(self, expression: exp.Check) -> str:
2794 this = self.sql(expression, key="this")
2795 return f"CHECK ({this})"
2796
2797 def foreignkey_sql(self, expression: exp.ForeignKey) -> str:
2798 expressions = self.expressions(expression, flat=True)
2799 reference = self.sql(expression, "reference")
2800 reference = f" {reference}" if reference else ""
2801 delete = self.sql(expression, "delete")
2802 delete = f" ON DELETE {delete}" if delete else ""
2803 update = self.sql(expression, "update")
2804 update = f" ON UPDATE {update}" if update else ""
2805 return f"FOREIGN KEY ({expressions}){reference}{delete}{update}"
2806
2807 def primarykey_sql(self, expression: exp.ForeignKey) -> str:
2808 expressions = self.expressions(expression, flat=True)
2809 options = self.expressions(expression, key="options", flat=True, sep=" ")
2810 options = f" {options}" if options else ""
2811 return f"PRIMARY KEY ({expressions}){options}"
2812
2813 def if_sql(self, expression: exp.If) -> str:
2814 return self.case_sql(exp.Case(ifs=[expression], default=expression.args.get("false")))
2815
2816 def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
2817 modifier = expression.args.get("modifier")
2818 modifier = f" {modifier}" if modifier else ""
2819 return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
2820
2821 def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
2822 return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
2823
2824 def jsonpath_sql(self, expression: exp.JSONPath) -> str:
2825 path = self.expressions(expression, sep="", flat=True).lstrip(".")
2826
2827 if expression.args.get("escape"):
2828 path = self.escape_str(path)
2829
2830 if self.QUOTE_JSON_PATH:
2831 path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
2832
2833 return path
2834
2835 def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
2836 if isinstance(expression, exp.JSONPathPart):
2837 transform = self.TRANSFORMS.get(expression.__class__)
2838 if not callable(transform):
2839 self.unsupported(f"Unsupported JSONPathPart type {expression.__class__.__name__}")
2840 return ""
2841
2842 return transform(self, expression)
2843
2844 if isinstance(expression, int):
2845 return str(expression)
2846
2847 if self.JSON_PATH_SINGLE_QUOTE_ESCAPE:
2848 escaped = expression.replace("'", "\\'")
2849 escaped = f"\\'{expression}\\'"
2850 else:
2851 escaped = expression.replace('"', '\\"')
2852 escaped = f'"{escaped}"'
2853
2854 return escaped
2855
2856 def formatjson_sql(self, expression: exp.FormatJson) -> str:
2857 return f"{self.sql(expression, 'this')} FORMAT JSON"
2858
2859 def jsonobject_sql(self, expression: exp.JSONObject | exp.JSONObjectAgg) -> str:
2860 null_handling = expression.args.get("null_handling")
2861 null_handling = f" {null_handling}" if null_handling else ""
2862
2863 unique_keys = expression.args.get("unique_keys")
2864 if unique_keys is not None:
2865 unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
2866 else:
2867 unique_keys = ""
2868
2869 return_type = self.sql(expression, "return_type")
2870 return_type = f" RETURNING {return_type}" if return_type else ""
2871 encoding = self.sql(expression, "encoding")
2872 encoding = f" ENCODING {encoding}" if encoding else ""
2873
2874 return self.func(
2875 "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG",
2876 *expression.expressions,
2877 suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
2878 )
2879
2880 def jsonobjectagg_sql(self, expression: exp.JSONObjectAgg) -> str:
2881 return self.jsonobject_sql(expression)
2882
2883 def jsonarray_sql(self, expression: exp.JSONArray) -> str:
2884 null_handling = expression.args.get("null_handling")
2885 null_handling = f" {null_handling}" if null_handling else ""
2886 return_type = self.sql(expression, "return_type")
2887 return_type = f" RETURNING {return_type}" if return_type else ""
2888 strict = " STRICT" if expression.args.get("strict") else ""
2889 return self.func(
2890 "JSON_ARRAY", *expression.expressions, suffix=f"{null_handling}{return_type}{strict})"
2891 )
2892
2893 def jsonarrayagg_sql(self, expression: exp.JSONArrayAgg) -> str:
2894 this = self.sql(expression, "this")
2895 order = self.sql(expression, "order")
2896 null_handling = expression.args.get("null_handling")
2897 null_handling = f" {null_handling}" if null_handling else ""
2898 return_type = self.sql(expression, "return_type")
2899 return_type = f" RETURNING {return_type}" if return_type else ""
2900 strict = " STRICT" if expression.args.get("strict") else ""
2901 return self.func(
2902 "JSON_ARRAYAGG",
2903 this,
2904 suffix=f"{order}{null_handling}{return_type}{strict})",
2905 )
2906
2907 def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
2908 path = self.sql(expression, "path")
2909 path = f" PATH {path}" if path else ""
2910 nested_schema = self.sql(expression, "nested_schema")
2911
2912 if nested_schema:
2913 return f"NESTED{path} {nested_schema}"
2914
2915 this = self.sql(expression, "this")
2916 kind = self.sql(expression, "kind")
2917 kind = f" {kind}" if kind else ""
2918 return f"{this}{kind}{path}"
2919
2920 def jsonschema_sql(self, expression: exp.JSONSchema) -> str:
2921 return self.func("COLUMNS", *expression.expressions)
2922
2923 def jsontable_sql(self, expression: exp.JSONTable) -> str:
2924 this = self.sql(expression, "this")
2925 path = self.sql(expression, "path")
2926 path = f", {path}" if path else ""
2927 error_handling = expression.args.get("error_handling")
2928 error_handling = f" {error_handling}" if error_handling else ""
2929 empty_handling = expression.args.get("empty_handling")
2930 empty_handling = f" {empty_handling}" if empty_handling else ""
2931 schema = self.sql(expression, "schema")
2932 return self.func(
2933 "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling} {schema})"
2934 )
2935
2936 def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
2937 this = self.sql(expression, "this")
2938 kind = self.sql(expression, "kind")
2939 path = self.sql(expression, "path")
2940 path = f" {path}" if path else ""
2941 as_json = " AS JSON" if expression.args.get("as_json") else ""
2942 return f"{this} {kind}{path}{as_json}"
2943
2944 def openjson_sql(self, expression: exp.OpenJSON) -> str:
2945 this = self.sql(expression, "this")
2946 path = self.sql(expression, "path")
2947 path = f", {path}" if path else ""
2948 expressions = self.expressions(expression)
2949 with_ = (
2950 f" WITH ({self.seg(self.indent(expressions), sep='')}{self.seg(')', sep='')}"
2951 if expressions
2952 else ""
2953 )
2954 return f"OPENJSON({this}{path}){with_}"
2955
2956 def in_sql(self, expression: exp.In) -> str:
2957 query = expression.args.get("query")
2958 unnest = expression.args.get("unnest")
2959 field = expression.args.get("field")
2960 is_global = " GLOBAL" if expression.args.get("is_global") else ""
2961
2962 if query:
2963 in_sql = self.sql(query)
2964 elif unnest:
2965 in_sql = self.in_unnest_op(unnest)
2966 elif field:
2967 in_sql = self.sql(field)
2968 else:
2969 in_sql = f"({self.expressions(expression, flat=True)})"
2970
2971 return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
2972
2973 def in_unnest_op(self, unnest: exp.Unnest) -> str:
2974 return f"(SELECT {self.sql(unnest)})"
2975
2976 def interval_sql(self, expression: exp.Interval) -> str:
2977 unit = self.sql(expression, "unit")
2978 if not self.INTERVAL_ALLOWS_PLURAL_FORM:
2979 unit = self.TIME_PART_SINGULARS.get(unit, unit)
2980 unit = f" {unit}" if unit else ""
2981
2982 if self.SINGLE_STRING_INTERVAL:
2983 this = expression.this.name if expression.this else ""
2984 return f"INTERVAL '{this}{unit}'" if this else f"INTERVAL{unit}"
2985
2986 this = self.sql(expression, "this")
2987 if this:
2988 unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES)
2989 this = f" {this}" if unwrapped else f" ({this})"
2990
2991 return f"INTERVAL{this}{unit}"
2992
2993 def return_sql(self, expression: exp.Return) -> str:
2994 return f"RETURN {self.sql(expression, 'this')}"
2995
2996 def reference_sql(self, expression: exp.Reference) -> str:
2997 this = self.sql(expression, "this")
2998 expressions = self.expressions(expression, flat=True)
2999 expressions = f"({expressions})" if expressions else ""
3000 options = self.expressions(expression, key="options", flat=True, sep=" ")
3001 options = f" {options}" if options else ""
3002 return f"REFERENCES {this}{expressions}{options}"
3003
3004 def anonymous_sql(self, expression: exp.Anonymous) -> str:
3005 # We don't normalize qualified functions such as a.b.foo(), because they can be case-sensitive
3006 parent = expression.parent
3007 is_qualified = isinstance(parent, exp.Dot) and expression is parent.expression
3008 return self.func(
3009 self.sql(expression, "this"), *expression.expressions, normalize=not is_qualified
3010 )
3011
3012 def paren_sql(self, expression: exp.Paren) -> str:
3013 sql = self.seg(self.indent(self.sql(expression, "this")), sep="")
3014 return f"({sql}{self.seg(')', sep='')}"
3015
3016 def neg_sql(self, expression: exp.Neg) -> str:
3017 # This makes sure we don't convert "- - 5" to "--5", which is a comment
3018 this_sql = self.sql(expression, "this")
3019 sep = " " if this_sql[0] == "-" else ""
3020 return f"-{sep}{this_sql}"
3021
3022 def not_sql(self, expression: exp.Not) -> str:
3023 return f"NOT {self.sql(expression, 'this')}"
3024
3025 def alias_sql(self, expression: exp.Alias) -> str:
3026 alias = self.sql(expression, "alias")
3027 alias = f" AS {alias}" if alias else ""
3028 return f"{self.sql(expression, 'this')}{alias}"
3029
3030 def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
3031 alias = expression.args["alias"]
3032
3033 identifier_alias = isinstance(alias, exp.Identifier)
3034 literal_alias = isinstance(alias, exp.Literal)
3035
3036 if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3037 alias.replace(exp.Literal.string(alias.output_name))
3038 elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3039 alias.replace(exp.to_identifier(alias.output_name))
3040
3041 return self.alias_sql(expression)
3042
3043 def aliases_sql(self, expression: exp.Aliases) -> str:
3044 return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})"
3045
3046 def atindex_sql(self, expression: exp.AtTimeZone) -> str:
3047 this = self.sql(expression, "this")
3048 index = self.sql(expression, "expression")
3049 return f"{this} AT {index}"
3050
3051 def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
3052 this = self.sql(expression, "this")
3053 zone = self.sql(expression, "zone")
3054 return f"{this} AT TIME ZONE {zone}"
3055
3056 def fromtimezone_sql(self, expression: exp.FromTimeZone) -> str:
3057 this = self.sql(expression, "this")
3058 zone = self.sql(expression, "zone")
3059 return f"{this} AT TIME ZONE {zone} AT TIME ZONE 'UTC'"
3060
3061 def add_sql(self