sqlglot.generator
1from __future__ import annotations 2 3import logging 4import typing as t 5 6from sqlglot import exp 7from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages 8from sqlglot.helper import apply_index_offset, csv, seq_get, should_identify 9from sqlglot.time import format_time 10from sqlglot.tokens import TokenType 11 12logger = logging.getLogger("sqlglot") 13 14 15class Generator: 16 """ 17 Generator interprets the given syntax tree and produces a SQL string as an output. 18 19 Args: 20 time_mapping (dict): the dictionary of custom time mappings in which the key 21 represents a python time format and the output the target time format 22 time_trie (trie): a trie of the time_mapping keys 23 pretty (bool): if set to True the returned string will be formatted. Default: False. 24 quote_start (str): specifies which starting character to use to delimit quotes. Default: '. 25 quote_end (str): specifies which ending character to use to delimit quotes. Default: '. 26 identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ". 27 identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ". 28 identify (bool | str): 'always': always quote, 'safe': quote identifiers if they don't contain an upcase, True defaults to always. 29 normalize (bool): if set to True all identifiers will lower cased 30 string_escape (str): specifies a string escape character. Default: '. 31 identifier_escape (str): specifies an identifier escape character. Default: ". 32 pad (int): determines padding in a formatted string. Default: 2. 33 indent (int): determines the size of indentation in a formatted string. Default: 4. 34 unnest_column_only (bool): if true unnest table aliases are considered only as column aliases 35 normalize_functions (str): normalize function names, "upper", "lower", or None 36 Default: "upper" 37 alias_post_tablesample (bool): if the table alias comes after tablesample 38 Default: False 39 unsupported_level (ErrorLevel): determines the generator's behavior when it encounters 40 unsupported expressions. Default ErrorLevel.WARN. 41 null_ordering (str): Indicates the default null ordering method to use if not explicitly set. 42 Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". 43 Default: "nulls_are_small" 44 max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. 45 This is only relevant if unsupported_level is ErrorLevel.RAISE. 46 Default: 3 47 leading_comma (bool): if the the comma is leading or trailing in select statements 48 Default: False 49 max_text_width: The max number of characters in a segment before creating new lines in pretty mode. 50 The default is on the smaller end because the length only represents a segment and not the true 51 line length. 52 Default: 80 53 comments: Whether or not to preserve comments in the output SQL code. 54 Default: True 55 """ 56 57 TRANSFORMS = { 58 exp.DateAdd: lambda self, e: self.func( 59 "DATE_ADD", e.this, e.expression, exp.Literal.string(e.text("unit")) 60 ), 61 exp.TsOrDsAdd: lambda self, e: self.func( 62 "TS_OR_DS_ADD", e.this, e.expression, exp.Literal.string(e.text("unit")) 63 ), 64 exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]), 65 exp.CharacterSetProperty: lambda self, e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}", 66 exp.ExecuteAsProperty: lambda self, e: self.naked_property(e), 67 exp.ExternalProperty: lambda self, e: "EXTERNAL", 68 exp.LanguageProperty: lambda self, e: self.naked_property(e), 69 exp.LocationProperty: lambda self, e: self.naked_property(e), 70 exp.LogProperty: lambda self, e: f"{'NO ' if e.args.get('no') else ''}LOG", 71 exp.MaterializedProperty: lambda self, e: "MATERIALIZED", 72 exp.NoPrimaryIndexProperty: lambda self, e: "NO PRIMARY INDEX", 73 exp.OnCommitProperty: lambda self, e: "ON COMMIT PRESERVE ROWS", 74 exp.ReturnsProperty: lambda self, e: self.naked_property(e), 75 exp.SetProperty: lambda self, e: f"{'MULTI' if e.args.get('multi') else ''}SET", 76 exp.SqlSecurityProperty: lambda self, e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}", 77 exp.TemporaryProperty: lambda self, e: f"{'GLOBAL ' if e.args.get('global_') else ''}TEMPORARY", 78 exp.TransientProperty: lambda self, e: "TRANSIENT", 79 exp.StabilityProperty: lambda self, e: e.name, 80 exp.VolatileProperty: lambda self, e: "VOLATILE", 81 exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}", 82 exp.CaseSpecificColumnConstraint: lambda self, e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC", 83 exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}", 84 exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}", 85 exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}", 86 exp.UppercaseColumnConstraint: lambda self, e: f"UPPERCASE", 87 exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}", 88 exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}", 89 exp.CheckColumnConstraint: lambda self, e: f"CHECK ({self.sql(e, 'this')})", 90 exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}", 91 exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}", 92 exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}", 93 exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}", 94 exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}", 95 } 96 97 # Whether or not null ordering is supported in order by 98 NULL_ORDERING_SUPPORTED = True 99 100 # Whether or not locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported 101 LOCKING_READS_SUPPORTED = False 102 103 # Always do union distinct or union all 104 EXPLICIT_UNION = False 105 106 # Wrap derived values in parens, usually standard but spark doesn't support it 107 WRAP_DERIVED_VALUES = True 108 109 # Whether or not create function uses an AS before the RETURN 110 CREATE_FUNCTION_RETURN_AS = True 111 112 # Whether or not MERGE ... WHEN MATCHED BY SOURCE is allowed 113 MATCHED_BY_SOURCE = True 114 115 # Whether or not the INTERVAL expression works only with values like '1 day' 116 SINGLE_STRING_INTERVAL = False 117 118 # Whether or not the plural form of date parts like day (i.e. "days") is supported in INTERVALs 119 INTERVAL_ALLOWS_PLURAL_FORM = True 120 121 # Whether or not the TABLESAMPLE clause supports a method name, like BERNOULLI 122 TABLESAMPLE_WITH_METHOD = True 123 124 # Whether or not to treat the number in TABLESAMPLE (50) as a percentage 125 TABLESAMPLE_SIZE_IS_PERCENT = False 126 127 # Whether or not limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH") 128 LIMIT_FETCH = "ALL" 129 130 TYPE_MAPPING = { 131 exp.DataType.Type.NCHAR: "CHAR", 132 exp.DataType.Type.NVARCHAR: "VARCHAR", 133 exp.DataType.Type.MEDIUMTEXT: "TEXT", 134 exp.DataType.Type.LONGTEXT: "TEXT", 135 exp.DataType.Type.MEDIUMBLOB: "BLOB", 136 exp.DataType.Type.LONGBLOB: "BLOB", 137 exp.DataType.Type.INET: "INET", 138 } 139 140 STAR_MAPPING = { 141 "except": "EXCEPT", 142 "replace": "REPLACE", 143 } 144 145 TIME_PART_SINGULARS = { 146 "microseconds": "microsecond", 147 "seconds": "second", 148 "minutes": "minute", 149 "hours": "hour", 150 "days": "day", 151 "weeks": "week", 152 "months": "month", 153 "quarters": "quarter", 154 "years": "year", 155 } 156 157 TOKEN_MAPPING: t.Dict[TokenType, str] = {} 158 159 STRUCT_DELIMITER = ("<", ">") 160 161 PARAMETER_TOKEN = "@" 162 163 PROPERTIES_LOCATION = { 164 exp.AfterJournalProperty: exp.Properties.Location.POST_NAME, 165 exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE, 166 exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA, 167 exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME, 168 exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA, 169 exp.ChecksumProperty: exp.Properties.Location.POST_NAME, 170 exp.CollateProperty: exp.Properties.Location.POST_SCHEMA, 171 exp.Cluster: exp.Properties.Location.POST_SCHEMA, 172 exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME, 173 exp.DefinerProperty: exp.Properties.Location.POST_CREATE, 174 exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA, 175 exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA, 176 exp.EngineProperty: exp.Properties.Location.POST_SCHEMA, 177 exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA, 178 exp.ExternalProperty: exp.Properties.Location.POST_CREATE, 179 exp.FallbackProperty: exp.Properties.Location.POST_NAME, 180 exp.FileFormatProperty: exp.Properties.Location.POST_WITH, 181 exp.FreespaceProperty: exp.Properties.Location.POST_NAME, 182 exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME, 183 exp.JournalProperty: exp.Properties.Location.POST_NAME, 184 exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA, 185 exp.LikeProperty: exp.Properties.Location.POST_SCHEMA, 186 exp.LocationProperty: exp.Properties.Location.POST_SCHEMA, 187 exp.LockingProperty: exp.Properties.Location.POST_ALIAS, 188 exp.LogProperty: exp.Properties.Location.POST_NAME, 189 exp.MaterializedProperty: exp.Properties.Location.POST_CREATE, 190 exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME, 191 exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION, 192 exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION, 193 exp.PartitionedByProperty: exp.Properties.Location.POST_WITH, 194 exp.Property: exp.Properties.Location.POST_WITH, 195 exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA, 196 exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA, 197 exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA, 198 exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA, 199 exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA, 200 exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA, 201 exp.SetProperty: exp.Properties.Location.POST_CREATE, 202 exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA, 203 exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE, 204 exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA, 205 exp.TableFormatProperty: exp.Properties.Location.POST_WITH, 206 exp.TemporaryProperty: exp.Properties.Location.POST_CREATE, 207 exp.TransientProperty: exp.Properties.Location.POST_CREATE, 208 exp.VolatileProperty: exp.Properties.Location.POST_CREATE, 209 exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION, 210 exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME, 211 } 212 213 JOIN_HINTS = True 214 TABLE_HINTS = True 215 216 RESERVED_KEYWORDS: t.Set[str] = set() 217 WITH_SEPARATED_COMMENTS = (exp.Select, exp.From, exp.Where, exp.With) 218 UNWRAPPED_INTERVAL_VALUES = (exp.Literal, exp.Paren, exp.Column) 219 220 SENTINEL_LINE_BREAK = "__SQLGLOT__LB__" 221 222 __slots__ = ( 223 "time_mapping", 224 "time_trie", 225 "pretty", 226 "quote_start", 227 "quote_end", 228 "identifier_start", 229 "identifier_end", 230 "identify", 231 "normalize", 232 "string_escape", 233 "identifier_escape", 234 "pad", 235 "index_offset", 236 "unnest_column_only", 237 "alias_post_tablesample", 238 "normalize_functions", 239 "unsupported_level", 240 "unsupported_messages", 241 "null_ordering", 242 "max_unsupported", 243 "_indent", 244 "_escaped_quote_end", 245 "_escaped_identifier_end", 246 "_leading_comma", 247 "_max_text_width", 248 "_comments", 249 "_cache", 250 ) 251 252 def __init__( 253 self, 254 time_mapping=None, 255 time_trie=None, 256 pretty=None, 257 quote_start=None, 258 quote_end=None, 259 identifier_start=None, 260 identifier_end=None, 261 identify=False, 262 normalize=False, 263 string_escape=None, 264 identifier_escape=None, 265 pad=2, 266 indent=2, 267 index_offset=0, 268 unnest_column_only=False, 269 alias_post_tablesample=False, 270 normalize_functions="upper", 271 unsupported_level=ErrorLevel.WARN, 272 null_ordering=None, 273 max_unsupported=3, 274 leading_comma=False, 275 max_text_width=80, 276 comments=True, 277 ): 278 import sqlglot 279 280 self.time_mapping = time_mapping or {} 281 self.time_trie = time_trie 282 self.pretty = pretty if pretty is not None else sqlglot.pretty 283 self.quote_start = quote_start or "'" 284 self.quote_end = quote_end or "'" 285 self.identifier_start = identifier_start or '"' 286 self.identifier_end = identifier_end or '"' 287 self.identify = identify 288 self.normalize = normalize 289 self.string_escape = string_escape or "'" 290 self.identifier_escape = identifier_escape or '"' 291 self.pad = pad 292 self.index_offset = index_offset 293 self.unnest_column_only = unnest_column_only 294 self.alias_post_tablesample = alias_post_tablesample 295 self.normalize_functions = normalize_functions 296 self.unsupported_level = unsupported_level 297 self.unsupported_messages = [] 298 self.max_unsupported = max_unsupported 299 self.null_ordering = null_ordering 300 self._indent = indent 301 self._escaped_quote_end = self.string_escape + self.quote_end 302 self._escaped_identifier_end = self.identifier_escape + self.identifier_end 303 self._leading_comma = leading_comma 304 self._max_text_width = max_text_width 305 self._comments = comments 306 self._cache = None 307 308 def generate( 309 self, 310 expression: t.Optional[exp.Expression], 311 cache: t.Optional[t.Dict[int, str]] = None, 312 ) -> str: 313 """ 314 Generates a SQL string by interpreting the given syntax tree. 315 316 Args 317 expression: the syntax tree. 318 cache: an optional sql string cache. this leverages the hash of an expression which is slow, so only use this if you set _hash on each node. 319 320 Returns 321 the SQL string. 322 """ 323 if cache is not None: 324 self._cache = cache 325 self.unsupported_messages = [] 326 sql = self.sql(expression).strip() 327 self._cache = None 328 329 if self.unsupported_level == ErrorLevel.IGNORE: 330 return sql 331 332 if self.unsupported_level == ErrorLevel.WARN: 333 for msg in self.unsupported_messages: 334 logger.warning(msg) 335 elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages: 336 raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported)) 337 338 if self.pretty: 339 sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n") 340 return sql 341 342 def unsupported(self, message: str) -> None: 343 if self.unsupported_level == ErrorLevel.IMMEDIATE: 344 raise UnsupportedError(message) 345 self.unsupported_messages.append(message) 346 347 def sep(self, sep: str = " ") -> str: 348 return f"{sep.strip()}\n" if self.pretty else sep 349 350 def seg(self, sql: str, sep: str = " ") -> str: 351 return f"{self.sep(sep)}{sql}" 352 353 def pad_comment(self, comment: str) -> str: 354 comment = " " + comment if comment[0].strip() else comment 355 comment = comment + " " if comment[-1].strip() else comment 356 return comment 357 358 def maybe_comment( 359 self, 360 sql: str, 361 expression: t.Optional[exp.Expression] = None, 362 comments: t.Optional[t.List[str]] = None, 363 ) -> str: 364 comments = (comments or (expression and expression.comments)) if self._comments else None # type: ignore 365 366 if not comments or isinstance(expression, exp.Binary): 367 return sql 368 369 sep = "\n" if self.pretty else " " 370 comments_sql = sep.join( 371 f"/*{self.pad_comment(comment)}*/" for comment in comments if comment 372 ) 373 374 if not comments_sql: 375 return sql 376 377 if isinstance(expression, self.WITH_SEPARATED_COMMENTS): 378 return f"{comments_sql}{self.sep()}{sql}" 379 380 return f"{sql} {comments_sql}" 381 382 def wrap(self, expression: exp.Expression | str) -> str: 383 this_sql = self.indent( 384 self.sql(expression) 385 if isinstance(expression, (exp.Select, exp.Union)) 386 else self.sql(expression, "this"), 387 level=1, 388 pad=0, 389 ) 390 return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}" 391 392 def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str: 393 original = self.identify 394 self.identify = False 395 result = func(*args, **kwargs) 396 self.identify = original 397 return result 398 399 def normalize_func(self, name: str) -> str: 400 if self.normalize_functions == "upper": 401 return name.upper() 402 if self.normalize_functions == "lower": 403 return name.lower() 404 return name 405 406 def indent( 407 self, 408 sql: str, 409 level: int = 0, 410 pad: t.Optional[int] = None, 411 skip_first: bool = False, 412 skip_last: bool = False, 413 ) -> str: 414 if not self.pretty: 415 return sql 416 417 pad = self.pad if pad is None else pad 418 lines = sql.split("\n") 419 420 return "\n".join( 421 line 422 if (skip_first and i == 0) or (skip_last and i == len(lines) - 1) 423 else f"{' ' * (level * self._indent + pad)}{line}" 424 for i, line in enumerate(lines) 425 ) 426 427 def sql( 428 self, 429 expression: t.Optional[str | exp.Expression], 430 key: t.Optional[str] = None, 431 comment: bool = True, 432 ) -> str: 433 if not expression: 434 return "" 435 436 if isinstance(expression, str): 437 return expression 438 439 if key: 440 return self.sql(expression.args.get(key)) 441 442 if self._cache is not None: 443 expression_id = hash(expression) 444 445 if expression_id in self._cache: 446 return self._cache[expression_id] 447 448 transform = self.TRANSFORMS.get(expression.__class__) 449 450 if callable(transform): 451 sql = transform(self, expression) 452 elif transform: 453 sql = transform 454 elif isinstance(expression, exp.Expression): 455 exp_handler_name = f"{expression.key}_sql" 456 457 if hasattr(self, exp_handler_name): 458 sql = getattr(self, exp_handler_name)(expression) 459 elif isinstance(expression, exp.Func): 460 sql = self.function_fallback_sql(expression) 461 elif isinstance(expression, exp.Property): 462 sql = self.property_sql(expression) 463 else: 464 raise ValueError(f"Unsupported expression type {expression.__class__.__name__}") 465 else: 466 raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}") 467 468 sql = self.maybe_comment(sql, expression) if self._comments and comment else sql 469 470 if self._cache is not None: 471 self._cache[expression_id] = sql 472 return sql 473 474 def uncache_sql(self, expression: exp.Uncache) -> str: 475 table = self.sql(expression, "this") 476 exists_sql = " IF EXISTS" if expression.args.get("exists") else "" 477 return f"UNCACHE TABLE{exists_sql} {table}" 478 479 def cache_sql(self, expression: exp.Cache) -> str: 480 lazy = " LAZY" if expression.args.get("lazy") else "" 481 table = self.sql(expression, "this") 482 options = expression.args.get("options") 483 options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else "" 484 sql = self.sql(expression, "expression") 485 sql = f" AS{self.sep()}{sql}" if sql else "" 486 sql = f"CACHE{lazy} TABLE {table}{options}{sql}" 487 return self.prepend_ctes(expression, sql) 488 489 def characterset_sql(self, expression: exp.CharacterSet) -> str: 490 if isinstance(expression.parent, exp.Cast): 491 return f"CHAR CHARACTER SET {self.sql(expression, 'this')}" 492 default = "DEFAULT " if expression.args.get("default") else "" 493 return f"{default}CHARACTER SET={self.sql(expression, 'this')}" 494 495 def column_sql(self, expression: exp.Column) -> str: 496 return ".".join( 497 self.sql(part) 498 for part in ( 499 expression.args.get("catalog"), 500 expression.args.get("db"), 501 expression.args.get("table"), 502 expression.args.get("this"), 503 ) 504 if part 505 ) 506 507 def columnposition_sql(self, expression: exp.ColumnPosition) -> str: 508 this = self.sql(expression, "this") 509 this = f" {this}" if this else "" 510 position = self.sql(expression, "position") 511 return f"{position}{this}" 512 513 def columndef_sql(self, expression: exp.ColumnDef) -> str: 514 column = self.sql(expression, "this") 515 kind = self.sql(expression, "kind") 516 constraints = self.expressions(expression, key="constraints", sep=" ", flat=True) 517 exists = "IF NOT EXISTS " if expression.args.get("exists") else "" 518 kind = f" {kind}" if kind else "" 519 constraints = f" {constraints}" if constraints else "" 520 position = self.sql(expression, "position") 521 position = f" {position}" if position else "" 522 523 return f"{exists}{column}{kind}{constraints}{position}" 524 525 def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str: 526 this = self.sql(expression, "this") 527 kind_sql = self.sql(expression, "kind") 528 return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql 529 530 def autoincrementcolumnconstraint_sql(self, _) -> str: 531 return self.token_sql(TokenType.AUTO_INCREMENT) 532 533 def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str: 534 if isinstance(expression.this, list): 535 this = self.wrap(self.expressions(expression, key="this", flat=True)) 536 else: 537 this = self.sql(expression, "this") 538 539 return f"COMPRESS {this}" 540 541 def generatedasidentitycolumnconstraint_sql( 542 self, expression: exp.GeneratedAsIdentityColumnConstraint 543 ) -> str: 544 this = "" 545 if expression.this is not None: 546 this = " ALWAYS " if expression.this else " BY DEFAULT " 547 start = expression.args.get("start") 548 start = f"START WITH {start}" if start else "" 549 increment = expression.args.get("increment") 550 increment = f" INCREMENT BY {increment}" if increment else "" 551 minvalue = expression.args.get("minvalue") 552 minvalue = f" MINVALUE {minvalue}" if minvalue else "" 553 maxvalue = expression.args.get("maxvalue") 554 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else "" 555 cycle = expression.args.get("cycle") 556 cycle_sql = "" 557 if cycle is not None: 558 cycle_sql = f"{' NO' if not cycle else ''} CYCLE" 559 cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql 560 sequence_opts = "" 561 if start or increment or cycle_sql: 562 sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}" 563 sequence_opts = f" ({sequence_opts.strip()})" 564 return f"GENERATED{this}AS IDENTITY{sequence_opts}" 565 566 def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str: 567 return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL" 568 569 def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str: 570 desc = expression.args.get("desc") 571 if desc is not None: 572 return f"PRIMARY KEY{' DESC' if desc else ' ASC'}" 573 return f"PRIMARY KEY" 574 575 def uniquecolumnconstraint_sql(self, _) -> str: 576 return "UNIQUE" 577 578 def create_sql(self, expression: exp.Create) -> str: 579 kind = self.sql(expression, "kind").upper() 580 properties = expression.args.get("properties") 581 properties_exp = expression.copy() 582 properties_locs = self.locate_properties(properties) if properties else {} 583 if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get( 584 exp.Properties.Location.POST_WITH 585 ): 586 properties_exp.set( 587 "properties", 588 exp.Properties( 589 expressions=[ 590 *properties_locs[exp.Properties.Location.POST_SCHEMA], 591 *properties_locs[exp.Properties.Location.POST_WITH], 592 ] 593 ), 594 ) 595 if kind == "TABLE" and properties_locs.get(exp.Properties.Location.POST_NAME): 596 this_name = self.sql(expression.this, "this") 597 this_properties = self.properties( 598 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_NAME]), 599 wrapped=False, 600 ) 601 this_schema = f"({self.expressions(expression.this)})" 602 this = f"{this_name}, {this_properties} {this_schema}" 603 properties_sql = "" 604 else: 605 this = self.sql(expression, "this") 606 properties_sql = self.sql(properties_exp, "properties") 607 begin = " BEGIN" if expression.args.get("begin") else "" 608 expression_sql = self.sql(expression, "expression") 609 if expression_sql: 610 expression_sql = f"{begin}{self.sep()}{expression_sql}" 611 612 if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return): 613 if properties_locs.get(exp.Properties.Location.POST_ALIAS): 614 postalias_props_sql = self.properties( 615 exp.Properties( 616 expressions=properties_locs[exp.Properties.Location.POST_ALIAS] 617 ), 618 wrapped=False, 619 ) 620 expression_sql = f" AS {postalias_props_sql}{expression_sql}" 621 else: 622 expression_sql = f" AS{expression_sql}" 623 624 postindex_props_sql = "" 625 if properties_locs.get(exp.Properties.Location.POST_INDEX): 626 postindex_props_sql = self.properties( 627 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]), 628 wrapped=False, 629 prefix=" ", 630 ) 631 632 indexes = expression.args.get("indexes") 633 if indexes: 634 indexes_sql: t.List[str] = [] 635 for index in indexes: 636 ind_unique = " UNIQUE" if index.args.get("unique") else "" 637 ind_primary = " PRIMARY" if index.args.get("primary") else "" 638 ind_amp = " AMP" if index.args.get("amp") else "" 639 ind_name = f" {index.name}" if index.name else "" 640 ind_columns = ( 641 f' ({self.expressions(index, key="columns", flat=True)})' 642 if index.args.get("columns") 643 else "" 644 ) 645 ind_sql = f"{ind_unique}{ind_primary}{ind_amp} INDEX{ind_name}{ind_columns}" 646 647 if indexes_sql: 648 indexes_sql.append(ind_sql) 649 else: 650 indexes_sql.append( 651 f"{ind_sql}{postindex_props_sql}" 652 if index.args.get("primary") 653 else f"{postindex_props_sql}{ind_sql}" 654 ) 655 656 index_sql = "".join(indexes_sql) 657 else: 658 index_sql = postindex_props_sql 659 660 replace = " OR REPLACE" if expression.args.get("replace") else "" 661 unique = " UNIQUE" if expression.args.get("unique") else "" 662 663 postcreate_props_sql = "" 664 if properties_locs.get(exp.Properties.Location.POST_CREATE): 665 postcreate_props_sql = self.properties( 666 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]), 667 sep=" ", 668 prefix=" ", 669 wrapped=False, 670 ) 671 672 modifiers = "".join((replace, unique, postcreate_props_sql)) 673 674 postexpression_props_sql = "" 675 if properties_locs.get(exp.Properties.Location.POST_EXPRESSION): 676 postexpression_props_sql = self.properties( 677 exp.Properties( 678 expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION] 679 ), 680 sep=" ", 681 prefix=" ", 682 wrapped=False, 683 ) 684 685 exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else "" 686 no_schema_binding = ( 687 " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else "" 688 ) 689 690 expression_sql = f"CREATE{modifiers} {kind}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}" 691 return self.prepend_ctes(expression, expression_sql) 692 693 def describe_sql(self, expression: exp.Describe) -> str: 694 return f"DESCRIBE {self.sql(expression, 'this')}" 695 696 def prepend_ctes(self, expression: exp.Expression, sql: str) -> str: 697 with_ = self.sql(expression, "with") 698 if with_: 699 sql = f"{with_}{self.sep()}{sql}" 700 return sql 701 702 def with_sql(self, expression: exp.With) -> str: 703 sql = self.expressions(expression, flat=True) 704 recursive = "RECURSIVE " if expression.args.get("recursive") else "" 705 706 return f"WITH {recursive}{sql}" 707 708 def cte_sql(self, expression: exp.CTE) -> str: 709 alias = self.sql(expression, "alias") 710 return f"{alias} AS {self.wrap(expression)}" 711 712 def tablealias_sql(self, expression: exp.TableAlias) -> str: 713 alias = self.sql(expression, "this") 714 columns = self.expressions(expression, key="columns", flat=True) 715 columns = f"({columns})" if columns else "" 716 return f"{alias}{columns}" 717 718 def bitstring_sql(self, expression: exp.BitString) -> str: 719 return self.sql(expression, "this") 720 721 def hexstring_sql(self, expression: exp.HexString) -> str: 722 return self.sql(expression, "this") 723 724 def bytestring_sql(self, expression: exp.ByteString) -> str: 725 return self.sql(expression, "this") 726 727 def datatype_sql(self, expression: exp.DataType) -> str: 728 type_value = expression.this 729 type_sql = self.TYPE_MAPPING.get(type_value, type_value.value) 730 nested = "" 731 interior = self.expressions(expression, flat=True) 732 values = "" 733 if interior: 734 if expression.args.get("nested"): 735 nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}" 736 if expression.args.get("values") is not None: 737 delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")") 738 values = f"{delimiters[0]}{self.expressions(expression, key='values')}{delimiters[1]}" 739 else: 740 nested = f"({interior})" 741 742 return f"{type_sql}{nested}{values}" 743 744 def directory_sql(self, expression: exp.Directory) -> str: 745 local = "LOCAL " if expression.args.get("local") else "" 746 row_format = self.sql(expression, "row_format") 747 row_format = f" {row_format}" if row_format else "" 748 return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}" 749 750 def delete_sql(self, expression: exp.Delete) -> str: 751 this = self.sql(expression, "this") 752 this = f" FROM {this}" if this else "" 753 using_sql = ( 754 f" USING {self.expressions(expression, key='using', sep=', USING ')}" 755 if expression.args.get("using") 756 else "" 757 ) 758 where_sql = self.sql(expression, "where") 759 returning = self.sql(expression, "returning") 760 sql = f"DELETE{this}{using_sql}{where_sql}{returning}" 761 return self.prepend_ctes(expression, sql) 762 763 def drop_sql(self, expression: exp.Drop) -> str: 764 this = self.sql(expression, "this") 765 kind = expression.args["kind"] 766 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 767 temporary = " TEMPORARY" if expression.args.get("temporary") else "" 768 materialized = " MATERIALIZED" if expression.args.get("materialized") else "" 769 cascade = " CASCADE" if expression.args.get("cascade") else "" 770 constraints = " CONSTRAINTS" if expression.args.get("constraints") else "" 771 purge = " PURGE" if expression.args.get("purge") else "" 772 return ( 773 f"DROP{temporary}{materialized} {kind}{exists_sql}{this}{cascade}{constraints}{purge}" 774 ) 775 776 def except_sql(self, expression: exp.Except) -> str: 777 return self.prepend_ctes( 778 expression, 779 self.set_operation(expression, self.except_op(expression)), 780 ) 781 782 def except_op(self, expression: exp.Except) -> str: 783 return f"EXCEPT{'' if expression.args.get('distinct') else ' ALL'}" 784 785 def fetch_sql(self, expression: exp.Fetch) -> str: 786 direction = expression.args.get("direction") 787 direction = f" {direction.upper()}" if direction else "" 788 count = expression.args.get("count") 789 count = f" {count}" if count else "" 790 if expression.args.get("percent"): 791 count = f"{count} PERCENT" 792 with_ties_or_only = "WITH TIES" if expression.args.get("with_ties") else "ONLY" 793 return f"{self.seg('FETCH')}{direction}{count} ROWS {with_ties_or_only}" 794 795 def filter_sql(self, expression: exp.Filter) -> str: 796 this = self.sql(expression, "this") 797 where = self.sql(expression, "expression")[1:] # where has a leading space 798 return f"{this} FILTER({where})" 799 800 def hint_sql(self, expression: exp.Hint) -> str: 801 if self.sql(expression, "this"): 802 self.unsupported("Hints are not supported") 803 return "" 804 805 def index_sql(self, expression: exp.Index) -> str: 806 this = self.sql(expression, "this") 807 table = self.sql(expression, "table") 808 columns = self.sql(expression, "columns") 809 return f"{this} ON {table} {columns}" 810 811 def identifier_sql(self, expression: exp.Identifier) -> str: 812 text = expression.name 813 lower = text.lower() 814 text = lower if self.normalize and not expression.quoted else text 815 text = text.replace(self.identifier_end, self._escaped_identifier_end) 816 if ( 817 expression.quoted 818 or should_identify(text, self.identify) 819 or lower in self.RESERVED_KEYWORDS 820 ): 821 text = f"{self.identifier_start}{text}{self.identifier_end}" 822 return text 823 824 def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str: 825 input_format = self.sql(expression, "input_format") 826 input_format = f"INPUTFORMAT {input_format}" if input_format else "" 827 output_format = self.sql(expression, "output_format") 828 output_format = f"OUTPUTFORMAT {output_format}" if output_format else "" 829 return self.sep().join((input_format, output_format)) 830 831 def national_sql(self, expression: exp.National) -> str: 832 return f"N{self.sql(expression, 'this')}" 833 834 def partition_sql(self, expression: exp.Partition) -> str: 835 return f"PARTITION({self.expressions(expression)})" 836 837 def properties_sql(self, expression: exp.Properties) -> str: 838 root_properties = [] 839 with_properties = [] 840 841 for p in expression.expressions: 842 p_loc = self.PROPERTIES_LOCATION[p.__class__] 843 if p_loc == exp.Properties.Location.POST_WITH: 844 with_properties.append(p) 845 elif p_loc == exp.Properties.Location.POST_SCHEMA: 846 root_properties.append(p) 847 848 return self.root_properties( 849 exp.Properties(expressions=root_properties) 850 ) + self.with_properties(exp.Properties(expressions=with_properties)) 851 852 def root_properties(self, properties: exp.Properties) -> str: 853 if properties.expressions: 854 return self.sep() + self.expressions(properties, indent=False, sep=" ") 855 return "" 856 857 def properties( 858 self, 859 properties: exp.Properties, 860 prefix: str = "", 861 sep: str = ", ", 862 suffix: str = "", 863 wrapped: bool = True, 864 ) -> str: 865 if properties.expressions: 866 expressions = self.expressions(properties, sep=sep, indent=False) 867 expressions = self.wrap(expressions) if wrapped else expressions 868 return f"{prefix}{' ' if prefix and prefix != ' ' else ''}{expressions}{suffix}" 869 return "" 870 871 def with_properties(self, properties: exp.Properties) -> str: 872 return self.properties(properties, prefix=self.seg("WITH")) 873 874 def locate_properties( 875 self, properties: exp.Properties 876 ) -> t.Dict[exp.Properties.Location, list[exp.Property]]: 877 properties_locs: t.Dict[exp.Properties.Location, list[exp.Property]] = { 878 key: [] for key in exp.Properties.Location 879 } 880 881 for p in properties.expressions: 882 p_loc = self.PROPERTIES_LOCATION[p.__class__] 883 if p_loc == exp.Properties.Location.POST_NAME: 884 properties_locs[exp.Properties.Location.POST_NAME].append(p) 885 elif p_loc == exp.Properties.Location.POST_INDEX: 886 properties_locs[exp.Properties.Location.POST_INDEX].append(p) 887 elif p_loc == exp.Properties.Location.POST_SCHEMA: 888 properties_locs[exp.Properties.Location.POST_SCHEMA].append(p) 889 elif p_loc == exp.Properties.Location.POST_WITH: 890 properties_locs[exp.Properties.Location.POST_WITH].append(p) 891 elif p_loc == exp.Properties.Location.POST_CREATE: 892 properties_locs[exp.Properties.Location.POST_CREATE].append(p) 893 elif p_loc == exp.Properties.Location.POST_ALIAS: 894 properties_locs[exp.Properties.Location.POST_ALIAS].append(p) 895 elif p_loc == exp.Properties.Location.POST_EXPRESSION: 896 properties_locs[exp.Properties.Location.POST_EXPRESSION].append(p) 897 elif p_loc == exp.Properties.Location.UNSUPPORTED: 898 self.unsupported(f"Unsupported property {p.key}") 899 900 return properties_locs 901 902 def property_sql(self, expression: exp.Property) -> str: 903 property_cls = expression.__class__ 904 if property_cls == exp.Property: 905 return f"{expression.name}={self.sql(expression, 'value')}" 906 907 property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls) 908 if not property_name: 909 self.unsupported(f"Unsupported property {expression.key}") 910 911 return f"{property_name}={self.sql(expression, 'this')}" 912 913 def likeproperty_sql(self, expression: exp.LikeProperty) -> str: 914 options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions) 915 options = f" {options}" if options else "" 916 return f"LIKE {self.sql(expression, 'this')}{options}" 917 918 def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str: 919 no = "NO " if expression.args.get("no") else "" 920 protection = " PROTECTION" if expression.args.get("protection") else "" 921 return f"{no}FALLBACK{protection}" 922 923 def journalproperty_sql(self, expression: exp.JournalProperty) -> str: 924 no = "NO " if expression.args.get("no") else "" 925 dual = "DUAL " if expression.args.get("dual") else "" 926 before = "BEFORE " if expression.args.get("before") else "" 927 return f"{no}{dual}{before}JOURNAL" 928 929 def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str: 930 freespace = self.sql(expression, "this") 931 percent = " PERCENT" if expression.args.get("percent") else "" 932 return f"FREESPACE={freespace}{percent}" 933 934 def afterjournalproperty_sql(self, expression: exp.AfterJournalProperty) -> str: 935 no = "NO " if expression.args.get("no") else "" 936 dual = "DUAL " if expression.args.get("dual") else "" 937 local = "" 938 if expression.args.get("local") is not None: 939 local = "LOCAL " if expression.args.get("local") else "NOT LOCAL " 940 return f"{no}{dual}{local}AFTER JOURNAL" 941 942 def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str: 943 if expression.args.get("default"): 944 property = "DEFAULT" 945 elif expression.args.get("on"): 946 property = "ON" 947 else: 948 property = "OFF" 949 return f"CHECKSUM={property}" 950 951 def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str: 952 if expression.args.get("no"): 953 return "NO MERGEBLOCKRATIO" 954 if expression.args.get("default"): 955 return "DEFAULT MERGEBLOCKRATIO" 956 957 percent = " PERCENT" if expression.args.get("percent") else "" 958 return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}" 959 960 def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str: 961 default = expression.args.get("default") 962 min = expression.args.get("min") 963 if default is not None or min is not None: 964 if default: 965 property = "DEFAULT" 966 elif min: 967 property = "MINIMUM" 968 else: 969 property = "MAXIMUM" 970 return f"{property} DATABLOCKSIZE" 971 else: 972 units = expression.args.get("units") 973 units = f" {units}" if units else "" 974 return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}" 975 976 def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str: 977 autotemp = expression.args.get("autotemp") 978 always = expression.args.get("always") 979 default = expression.args.get("default") 980 manual = expression.args.get("manual") 981 never = expression.args.get("never") 982 983 if autotemp is not None: 984 property = f"AUTOTEMP({self.expressions(autotemp)})" 985 elif always: 986 property = "ALWAYS" 987 elif default: 988 property = "DEFAULT" 989 elif manual: 990 property = "MANUAL" 991 elif never: 992 property = "NEVER" 993 return f"BLOCKCOMPRESSION={property}" 994 995 def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str: 996 no = expression.args.get("no") 997 no = " NO" if no else "" 998 concurrent = expression.args.get("concurrent") 999 concurrent = " CONCURRENT" if concurrent else "" 1000 1001 for_ = "" 1002 if expression.args.get("for_all"): 1003 for_ = " FOR ALL" 1004 elif expression.args.get("for_insert"): 1005 for_ = " FOR INSERT" 1006 elif expression.args.get("for_none"): 1007 for_ = " FOR NONE" 1008 return f"WITH{no}{concurrent} ISOLATED LOADING{for_}" 1009 1010 def lockingproperty_sql(self, expression: exp.LockingProperty) -> str: 1011 kind = expression.args.get("kind") 1012 this: str = f" {this}" if expression.this else "" 1013 for_or_in = expression.args.get("for_or_in") 1014 lock_type = expression.args.get("lock_type") 1015 override = " OVERRIDE" if expression.args.get("override") else "" 1016 return f"LOCKING {kind}{this} {for_or_in} {lock_type}{override}" 1017 1018 def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str: 1019 data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA" 1020 statistics = expression.args.get("statistics") 1021 statistics_sql = "" 1022 if statistics is not None: 1023 statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS" 1024 return f"{data_sql}{statistics_sql}" 1025 1026 def insert_sql(self, expression: exp.Insert) -> str: 1027 overwrite = expression.args.get("overwrite") 1028 1029 if isinstance(expression.this, exp.Directory): 1030 this = "OVERWRITE " if overwrite else "INTO " 1031 else: 1032 this = "OVERWRITE TABLE " if overwrite else "INTO " 1033 1034 alternative = expression.args.get("alternative") 1035 alternative = f" OR {alternative} " if alternative else " " 1036 this = f"{this}{self.sql(expression, 'this')}" 1037 1038 exists = " IF EXISTS " if expression.args.get("exists") else " " 1039 partition_sql = ( 1040 self.sql(expression, "partition") if expression.args.get("partition") else "" 1041 ) 1042 expression_sql = self.sql(expression, "expression") 1043 conflict = self.sql(expression, "conflict") 1044 returning = self.sql(expression, "returning") 1045 sep = self.sep() if partition_sql else "" 1046 sql = f"INSERT{alternative}{this}{exists}{partition_sql}{sep}{expression_sql}{conflict}{returning}" 1047 return self.prepend_ctes(expression, sql) 1048 1049 def intersect_sql(self, expression: exp.Intersect) -> str: 1050 return self.prepend_ctes( 1051 expression, 1052 self.set_operation(expression, self.intersect_op(expression)), 1053 ) 1054 1055 def intersect_op(self, expression: exp.Intersect) -> str: 1056 return f"INTERSECT{'' if expression.args.get('distinct') else ' ALL'}" 1057 1058 def introducer_sql(self, expression: exp.Introducer) -> str: 1059 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}" 1060 1061 def pseudotype_sql(self, expression: exp.PseudoType) -> str: 1062 return expression.name.upper() 1063 1064 def onconflict_sql(self, expression: exp.OnConflict) -> str: 1065 conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT" 1066 constraint = self.sql(expression, "constraint") 1067 if constraint: 1068 constraint = f"ON CONSTRAINT {constraint}" 1069 key = self.expressions(expression, key="key", flat=True) 1070 do = "" if expression.args.get("duplicate") else " DO " 1071 nothing = "NOTHING" if expression.args.get("nothing") else "" 1072 expressions = self.expressions(expression, flat=True) 1073 if expressions: 1074 expressions = f"UPDATE SET {expressions}" 1075 return f"{self.seg(conflict)} {constraint}{key}{do}{nothing}{expressions}" 1076 1077 def returning_sql(self, expression: exp.Returning) -> str: 1078 return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}" 1079 1080 def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str: 1081 fields = expression.args.get("fields") 1082 fields = f" FIELDS TERMINATED BY {fields}" if fields else "" 1083 escaped = expression.args.get("escaped") 1084 escaped = f" ESCAPED BY {escaped}" if escaped else "" 1085 items = expression.args.get("collection_items") 1086 items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else "" 1087 keys = expression.args.get("map_keys") 1088 keys = f" MAP KEYS TERMINATED BY {keys}" if keys else "" 1089 lines = expression.args.get("lines") 1090 lines = f" LINES TERMINATED BY {lines}" if lines else "" 1091 null = expression.args.get("null") 1092 null = f" NULL DEFINED AS {null}" if null else "" 1093 return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}" 1094 1095 def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str: 1096 table = ".".join( 1097 part 1098 for part in [ 1099 self.sql(expression, "catalog"), 1100 self.sql(expression, "db"), 1101 self.sql(expression, "this"), 1102 ] 1103 if part 1104 ) 1105 1106 alias = self.sql(expression, "alias") 1107 alias = f"{sep}{alias}" if alias else "" 1108 hints = self.expressions(expression, key="hints", sep=", ", flat=True) 1109 hints = f" WITH ({hints})" if hints and self.TABLE_HINTS else "" 1110 laterals = self.expressions(expression, key="laterals", sep="") 1111 joins = self.expressions(expression, key="joins", sep="") 1112 pivots = self.expressions(expression, key="pivots", sep="") 1113 system_time = expression.args.get("system_time") 1114 system_time = f" {self.sql(expression, 'system_time')}" if system_time else "" 1115 1116 return f"{table}{system_time}{alias}{hints}{laterals}{joins}{pivots}" 1117 1118 def tablesample_sql(self, expression: exp.TableSample, seed_prefix: str = "SEED") -> str: 1119 if self.alias_post_tablesample and expression.this.alias: 1120 this = self.sql(expression.this, "this") 1121 alias = f" AS {self.sql(expression.this, 'alias')}" 1122 else: 1123 this = self.sql(expression, "this") 1124 alias = "" 1125 method = self.sql(expression, "method") 1126 method = f"{method.upper()} " if method and self.TABLESAMPLE_WITH_METHOD else "" 1127 numerator = self.sql(expression, "bucket_numerator") 1128 denominator = self.sql(expression, "bucket_denominator") 1129 field = self.sql(expression, "bucket_field") 1130 field = f" ON {field}" if field else "" 1131 bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else "" 1132 percent = self.sql(expression, "percent") 1133 percent = f"{percent} PERCENT" if percent else "" 1134 rows = self.sql(expression, "rows") 1135 rows = f"{rows} ROWS" if rows else "" 1136 size = self.sql(expression, "size") 1137 if size and self.TABLESAMPLE_SIZE_IS_PERCENT: 1138 size = f"{size} PERCENT" 1139 seed = self.sql(expression, "seed") 1140 seed = f" {seed_prefix} ({seed})" if seed else "" 1141 kind = expression.args.get("kind", "TABLESAMPLE") 1142 return f"{this} {kind} {method}({bucket}{percent}{rows}{size}){seed}{alias}" 1143 1144 def pivot_sql(self, expression: exp.Pivot) -> str: 1145 this = self.sql(expression, "this") 1146 alias = self.sql(expression, "alias") 1147 alias = f" AS {alias}" if alias else "" 1148 unpivot = expression.args.get("unpivot") 1149 direction = "UNPIVOT" if unpivot else "PIVOT" 1150 expressions = self.expressions(expression, key="expressions") 1151 field = self.sql(expression, "field") 1152 return f"{this} {direction}({expressions} FOR {field}){alias}" 1153 1154 def tuple_sql(self, expression: exp.Tuple) -> str: 1155 return f"({self.expressions(expression, flat=True)})" 1156 1157 def update_sql(self, expression: exp.Update) -> str: 1158 this = self.sql(expression, "this") 1159 set_sql = self.expressions(expression, flat=True) 1160 from_sql = self.sql(expression, "from") 1161 where_sql = self.sql(expression, "where") 1162 returning = self.sql(expression, "returning") 1163 sql = f"UPDATE {this} SET {set_sql}{from_sql}{where_sql}{returning}" 1164 return self.prepend_ctes(expression, sql) 1165 1166 def values_sql(self, expression: exp.Values) -> str: 1167 args = self.expressions(expression) 1168 alias = self.sql(expression, "alias") 1169 values = f"VALUES{self.seg('')}{args}" 1170 values = ( 1171 f"({values})" 1172 if self.WRAP_DERIVED_VALUES and (alias or isinstance(expression.parent, exp.From)) 1173 else values 1174 ) 1175 return f"{values} AS {alias}" if alias else values 1176 1177 def var_sql(self, expression: exp.Var) -> str: 1178 return self.sql(expression, "this") 1179 1180 def into_sql(self, expression: exp.Into) -> str: 1181 temporary = " TEMPORARY" if expression.args.get("temporary") else "" 1182 unlogged = " UNLOGGED" if expression.args.get("unlogged") else "" 1183 return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}" 1184 1185 def from_sql(self, expression: exp.From) -> str: 1186 expressions = self.expressions(expression, flat=True) 1187 return f"{self.seg('FROM')} {expressions}" 1188 1189 def group_sql(self, expression: exp.Group) -> str: 1190 group_by = self.op_expressions("GROUP BY", expression) 1191 grouping_sets = self.expressions(expression, key="grouping_sets", indent=False) 1192 grouping_sets = ( 1193 f"{self.seg('GROUPING SETS')} {self.wrap(grouping_sets)}" if grouping_sets else "" 1194 ) 1195 1196 cube = expression.args.get("cube", []) 1197 if seq_get(cube, 0) is True: 1198 return f"{group_by}{self.seg('WITH CUBE')}" 1199 else: 1200 cube_sql = self.expressions(expression, key="cube", indent=False) 1201 cube_sql = f"{self.seg('CUBE')} {self.wrap(cube_sql)}" if cube_sql else "" 1202 1203 rollup = expression.args.get("rollup", []) 1204 if seq_get(rollup, 0) is True: 1205 return f"{group_by}{self.seg('WITH ROLLUP')}" 1206 else: 1207 rollup_sql = self.expressions(expression, key="rollup", indent=False) 1208 rollup_sql = f"{self.seg('ROLLUP')} {self.wrap(rollup_sql)}" if rollup_sql else "" 1209 1210 groupings = csv(grouping_sets, cube_sql, rollup_sql, sep=",") 1211 1212 if expression.args.get("expressions") and groupings: 1213 group_by = f"{group_by}," 1214 1215 return f"{group_by}{groupings}" 1216 1217 def having_sql(self, expression: exp.Having) -> str: 1218 this = self.indent(self.sql(expression, "this")) 1219 return f"{self.seg('HAVING')}{self.sep()}{this}" 1220 1221 def join_sql(self, expression: exp.Join) -> str: 1222 op_sql = self.seg( 1223 " ".join( 1224 op 1225 for op in ( 1226 "NATURAL" if expression.args.get("natural") else None, 1227 expression.side, 1228 expression.kind, 1229 expression.hint if self.JOIN_HINTS else None, 1230 "JOIN", 1231 ) 1232 if op 1233 ) 1234 ) 1235 on_sql = self.sql(expression, "on") 1236 using = expression.args.get("using") 1237 1238 if not on_sql and using: 1239 on_sql = csv(*(self.sql(column) for column in using)) 1240 1241 if on_sql: 1242 on_sql = self.indent(on_sql, skip_first=True) 1243 space = self.seg(" " * self.pad) if self.pretty else " " 1244 if using: 1245 on_sql = f"{space}USING ({on_sql})" 1246 else: 1247 on_sql = f"{space}ON {on_sql}" 1248 1249 expression_sql = self.sql(expression, "expression") 1250 this_sql = self.sql(expression, "this") 1251 return f"{expression_sql}{op_sql} {this_sql}{on_sql}" 1252 1253 def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->") -> str: 1254 args = self.expressions(expression, flat=True) 1255 args = f"({args})" if len(args.split(",")) > 1 else args 1256 return f"{args} {arrow_sep} {self.sql(expression, 'this')}" 1257 1258 def lateral_sql(self, expression: exp.Lateral) -> str: 1259 this = self.sql(expression, "this") 1260 1261 if isinstance(expression.this, exp.Subquery): 1262 return f"LATERAL {this}" 1263 1264 if expression.args.get("view"): 1265 alias = expression.args["alias"] 1266 columns = self.expressions(alias, key="columns", flat=True) 1267 table = f" {alias.name}" if alias.name else "" 1268 columns = f" AS {columns}" if columns else "" 1269 op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}") 1270 return f"{op_sql}{self.sep()}{this}{table}{columns}" 1271 1272 alias = self.sql(expression, "alias") 1273 alias = f" AS {alias}" if alias else "" 1274 return f"LATERAL {this}{alias}" 1275 1276 def limit_sql(self, expression: exp.Limit) -> str: 1277 this = self.sql(expression, "this") 1278 return f"{this}{self.seg('LIMIT')} {self.sql(expression, 'expression')}" 1279 1280 def offset_sql(self, expression: exp.Offset) -> str: 1281 this = self.sql(expression, "this") 1282 return f"{this}{self.seg('OFFSET')} {self.sql(expression, 'expression')}" 1283 1284 def setitem_sql(self, expression: exp.SetItem) -> str: 1285 kind = self.sql(expression, "kind") 1286 kind = f"{kind} " if kind else "" 1287 this = self.sql(expression, "this") 1288 expressions = self.expressions(expression) 1289 collate = self.sql(expression, "collate") 1290 collate = f" COLLATE {collate}" if collate else "" 1291 global_ = "GLOBAL " if expression.args.get("global") else "" 1292 return f"{global_}{kind}{this}{expressions}{collate}" 1293 1294 def set_sql(self, expression: exp.Set) -> str: 1295 expressions = ( 1296 f" {self.expressions(expression, flat=True)}" if expression.expressions else "" 1297 ) 1298 return f"SET{expressions}" 1299 1300 def pragma_sql(self, expression: exp.Pragma) -> str: 1301 return f"PRAGMA {self.sql(expression, 'this')}" 1302 1303 def lock_sql(self, expression: exp.Lock) -> str: 1304 if self.LOCKING_READS_SUPPORTED: 1305 lock_type = "UPDATE" if expression.args["update"] else "SHARE" 1306 return self.seg(f"FOR {lock_type}") 1307 1308 self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported") 1309 return "" 1310 1311 def literal_sql(self, expression: exp.Literal) -> str: 1312 text = expression.this or "" 1313 if expression.is_string: 1314 text = text.replace(self.quote_end, self._escaped_quote_end) 1315 if self.pretty: 1316 text = text.replace("\n", self.SENTINEL_LINE_BREAK) 1317 text = f"{self.quote_start}{text}{self.quote_end}" 1318 return text 1319 1320 def loaddata_sql(self, expression: exp.LoadData) -> str: 1321 local = " LOCAL" if expression.args.get("local") else "" 1322 inpath = f" INPATH {self.sql(expression, 'inpath')}" 1323 overwrite = " OVERWRITE" if expression.args.get("overwrite") else "" 1324 this = f" INTO TABLE {self.sql(expression, 'this')}" 1325 partition = self.sql(expression, "partition") 1326 partition = f" {partition}" if partition else "" 1327 input_format = self.sql(expression, "input_format") 1328 input_format = f" INPUTFORMAT {input_format}" if input_format else "" 1329 serde = self.sql(expression, "serde") 1330 serde = f" SERDE {serde}" if serde else "" 1331 return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}" 1332 1333 def null_sql(self, *_) -> str: 1334 return "NULL" 1335 1336 def boolean_sql(self, expression: exp.Boolean) -> str: 1337 return "TRUE" if expression.this else "FALSE" 1338 1339 def order_sql(self, expression: exp.Order, flat: bool = False) -> str: 1340 this = self.sql(expression, "this") 1341 this = f"{this} " if this else this 1342 return self.op_expressions(f"{this}ORDER BY", expression, flat=this or flat) # type: ignore 1343 1344 def cluster_sql(self, expression: exp.Cluster) -> str: 1345 return self.op_expressions("CLUSTER BY", expression) 1346 1347 def distribute_sql(self, expression: exp.Distribute) -> str: 1348 return self.op_expressions("DISTRIBUTE BY", expression) 1349 1350 def sort_sql(self, expression: exp.Sort) -> str: 1351 return self.op_expressions("SORT BY", expression) 1352 1353 def ordered_sql(self, expression: exp.Ordered) -> str: 1354 desc = expression.args.get("desc") 1355 asc = not desc 1356 1357 nulls_first = expression.args.get("nulls_first") 1358 nulls_last = not nulls_first 1359 nulls_are_large = self.null_ordering == "nulls_are_large" 1360 nulls_are_small = self.null_ordering == "nulls_are_small" 1361 nulls_are_last = self.null_ordering == "nulls_are_last" 1362 1363 sort_order = " DESC" if desc else "" 1364 nulls_sort_change = "" 1365 if nulls_first and ( 1366 (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last 1367 ): 1368 nulls_sort_change = " NULLS FIRST" 1369 elif ( 1370 nulls_last 1371 and ((asc and nulls_are_small) or (desc and nulls_are_large)) 1372 and not nulls_are_last 1373 ): 1374 nulls_sort_change = " NULLS LAST" 1375 1376 if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED: 1377 self.unsupported( 1378 "Sorting in an ORDER BY on NULLS FIRST/NULLS LAST is not supported by this dialect" 1379 ) 1380 nulls_sort_change = "" 1381 1382 return f"{self.sql(expression, 'this')}{sort_order}{nulls_sort_change}" 1383 1384 def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str: 1385 partition = self.partition_by_sql(expression) 1386 order = self.sql(expression, "order") 1387 measures = self.expressions(expression, key="measures") 1388 measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else "" 1389 rows = self.sql(expression, "rows") 1390 rows = self.seg(rows) if rows else "" 1391 after = self.sql(expression, "after") 1392 after = self.seg(after) if after else "" 1393 pattern = self.sql(expression, "pattern") 1394 pattern = self.seg(f"PATTERN ({pattern})") if pattern else "" 1395 definition_sqls = [ 1396 f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}" 1397 for definition in expression.args.get("define", []) 1398 ] 1399 definitions = self.expressions(sqls=definition_sqls) 1400 define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else "" 1401 body = "".join( 1402 ( 1403 partition, 1404 order, 1405 measures, 1406 rows, 1407 after, 1408 pattern, 1409 define, 1410 ) 1411 ) 1412 alias = self.sql(expression, "alias") 1413 alias = f" {alias}" if alias else "" 1414 return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}" 1415 1416 def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str: 1417 limit = expression.args.get("limit") 1418 1419 if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch): 1420 limit = exp.Limit(expression=limit.args.get("count")) 1421 elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit): 1422 limit = exp.Fetch(direction="FIRST", count=limit.expression) 1423 1424 fetch = isinstance(limit, exp.Fetch) 1425 1426 return csv( 1427 *sqls, 1428 *[self.sql(sql) for sql in expression.args.get("joins") or []], 1429 self.sql(expression, "match"), 1430 *[self.sql(sql) for sql in expression.args.get("laterals") or []], 1431 self.sql(expression, "where"), 1432 self.sql(expression, "group"), 1433 self.sql(expression, "having"), 1434 self.sql(expression, "qualify"), 1435 self.seg("WINDOW ") + self.expressions(expression, key="windows", flat=True) 1436 if expression.args.get("windows") 1437 else "", 1438 self.sql(expression, "distribute"), 1439 self.sql(expression, "sort"), 1440 self.sql(expression, "cluster"), 1441 self.sql(expression, "order"), 1442 self.sql(expression, "offset") if fetch else self.sql(limit), 1443 self.sql(limit) if fetch else self.sql(expression, "offset"), 1444 self.sql(expression, "lock"), 1445 self.sql(expression, "sample"), 1446 sep="", 1447 ) 1448 1449 def select_sql(self, expression: exp.Select) -> str: 1450 kind = expression.args.get("kind") 1451 kind = f" AS {kind}" if kind else "" 1452 hint = self.sql(expression, "hint") 1453 distinct = self.sql(expression, "distinct") 1454 distinct = f" {distinct}" if distinct else "" 1455 expressions = self.expressions(expression) 1456 expressions = f"{self.sep()}{expressions}" if expressions else expressions 1457 sql = self.query_modifiers( 1458 expression, 1459 f"SELECT{kind}{hint}{distinct}{expressions}", 1460 self.sql(expression, "into", comment=False), 1461 self.sql(expression, "from", comment=False), 1462 ) 1463 return self.prepend_ctes(expression, sql) 1464 1465 def schema_sql(self, expression: exp.Schema) -> str: 1466 this = self.sql(expression, "this") 1467 this = f"{this} " if this else "" 1468 sql = f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}" 1469 return f"{this}{sql}" 1470 1471 def star_sql(self, expression: exp.Star) -> str: 1472 except_ = self.expressions(expression, key="except", flat=True) 1473 except_ = f"{self.seg(self.STAR_MAPPING['except'])} ({except_})" if except_ else "" 1474 replace = self.expressions(expression, key="replace", flat=True) 1475 replace = f"{self.seg(self.STAR_MAPPING['replace'])} ({replace})" if replace else "" 1476 return f"*{except_}{replace}" 1477 1478 def structkwarg_sql(self, expression: exp.StructKwarg) -> str: 1479 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}" 1480 1481 def parameter_sql(self, expression: exp.Parameter) -> str: 1482 this = self.sql(expression, "this") 1483 this = f"{{{this}}}" if expression.args.get("wrapped") else f"{this}" 1484 return f"{self.PARAMETER_TOKEN}{this}" 1485 1486 def sessionparameter_sql(self, expression: exp.SessionParameter) -> str: 1487 this = self.sql(expression, "this") 1488 kind = expression.text("kind") 1489 if kind: 1490 kind = f"{kind}." 1491 return f"@@{kind}{this}" 1492 1493 def placeholder_sql(self, expression: exp.Placeholder) -> str: 1494 return f":{expression.name}" if expression.name else "?" 1495 1496 def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str: 1497 alias = self.sql(expression, "alias") 1498 alias = f"{sep}{alias}" if alias else "" 1499 1500 sql = self.query_modifiers( 1501 expression, 1502 self.wrap(expression), 1503 alias, 1504 self.expressions(expression, key="pivots", sep=" "), 1505 ) 1506 1507 return self.prepend_ctes(expression, sql) 1508 1509 def qualify_sql(self, expression: exp.Qualify) -> str: 1510 this = self.indent(self.sql(expression, "this")) 1511 return f"{self.seg('QUALIFY')}{self.sep()}{this}" 1512 1513 def union_sql(self, expression: exp.Union) -> str: 1514 return self.prepend_ctes( 1515 expression, 1516 self.set_operation(expression, self.union_op(expression)), 1517 ) 1518 1519 def union_op(self, expression: exp.Union) -> str: 1520 kind = " DISTINCT" if self.EXPLICIT_UNION else "" 1521 kind = kind if expression.args.get("distinct") else " ALL" 1522 return f"UNION{kind}" 1523 1524 def unnest_sql(self, expression: exp.Unnest) -> str: 1525 args = self.expressions(expression, flat=True) 1526 alias = expression.args.get("alias") 1527 if alias and self.unnest_column_only: 1528 columns = alias.columns 1529 alias = self.sql(columns[0]) if columns else "" 1530 else: 1531 alias = self.sql(expression, "alias") 1532 alias = f" AS {alias}" if alias else alias 1533 ordinality = " WITH ORDINALITY" if expression.args.get("ordinality") else "" 1534 offset = expression.args.get("offset") 1535 offset = f" WITH OFFSET AS {self.sql(offset)}" if offset else "" 1536 return f"UNNEST({args}){ordinality}{alias}{offset}" 1537 1538 def where_sql(self, expression: exp.Where) -> str: 1539 this = self.indent(self.sql(expression, "this")) 1540 return f"{self.seg('WHERE')}{self.sep()}{this}" 1541 1542 def window_sql(self, expression: exp.Window) -> str: 1543 this = self.sql(expression, "this") 1544 1545 partition = self.partition_by_sql(expression) 1546 1547 order = expression.args.get("order") 1548 order_sql = self.order_sql(order, flat=True) if order else "" 1549 1550 partition_sql = partition + " " if partition and order else partition 1551 1552 spec = expression.args.get("spec") 1553 spec_sql = " " + self.window_spec_sql(spec) if spec else "" 1554 1555 alias = self.sql(expression, "alias") 1556 this = f"{this} {'AS' if expression.arg_key == 'windows' else 'OVER'}" 1557 1558 if not partition and not order and not spec and alias: 1559 return f"{this} {alias}" 1560 1561 window_args = alias + partition_sql + order_sql + spec_sql 1562 1563 return f"{this} ({window_args.strip()})" 1564 1565 def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str: 1566 partition = self.expressions(expression, key="partition_by", flat=True) 1567 return f"PARTITION BY {partition}" if partition else "" 1568 1569 def window_spec_sql(self, expression: exp.WindowSpec) -> str: 1570 kind = self.sql(expression, "kind") 1571 start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ") 1572 end = ( 1573 csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ") 1574 or "CURRENT ROW" 1575 ) 1576 return f"{kind} BETWEEN {start} AND {end}" 1577 1578 def withingroup_sql(self, expression: exp.WithinGroup) -> str: 1579 this = self.sql(expression, "this") 1580 expression_sql = self.sql(expression, "expression")[1:] # order has a leading space 1581 return f"{this} WITHIN GROUP ({expression_sql})" 1582 1583 def between_sql(self, expression: exp.Between) -> str: 1584 this = self.sql(expression, "this") 1585 low = self.sql(expression, "low") 1586 high = self.sql(expression, "high") 1587 return f"{this} BETWEEN {low} AND {high}" 1588 1589 def bracket_sql(self, expression: exp.Bracket) -> str: 1590 expressions = apply_index_offset(expression.this, expression.expressions, self.index_offset) 1591 expressions_sql = ", ".join(self.sql(e) for e in expressions) 1592 1593 return f"{self.sql(expression, 'this')}[{expressions_sql}]" 1594 1595 def all_sql(self, expression: exp.All) -> str: 1596 return f"ALL {self.wrap(expression)}" 1597 1598 def any_sql(self, expression: exp.Any) -> str: 1599 this = self.sql(expression, "this") 1600 if isinstance(expression.this, exp.Subqueryable): 1601 this = self.wrap(this) 1602 return f"ANY {this}" 1603 1604 def exists_sql(self, expression: exp.Exists) -> str: 1605 return f"EXISTS{self.wrap(expression)}" 1606 1607 def case_sql(self, expression: exp.Case) -> str: 1608 this = self.sql(expression, "this") 1609 statements = [f"CASE {this}" if this else "CASE"] 1610 1611 for e in expression.args["ifs"]: 1612 statements.append(f"WHEN {self.sql(e, 'this')}") 1613 statements.append(f"THEN {self.sql(e, 'true')}") 1614 1615 default = self.sql(expression, "default") 1616 1617 if default: 1618 statements.append(f"ELSE {default}") 1619 1620 statements.append("END") 1621 1622 if self.pretty and self.text_width(statements) > self._max_text_width: 1623 return self.indent("\n".join(statements), skip_first=True, skip_last=True) 1624 1625 return " ".join(statements) 1626 1627 def constraint_sql(self, expression: exp.Constraint) -> str: 1628 this = self.sql(expression, "this") 1629 expressions = self.expressions(expression, flat=True) 1630 return f"CONSTRAINT {this} {expressions}" 1631 1632 def extract_sql(self, expression: exp.Extract) -> str: 1633 this = self.sql(expression, "this") 1634 expression_sql = self.sql(expression, "expression") 1635 return f"EXTRACT({this} FROM {expression_sql})" 1636 1637 def trim_sql(self, expression: exp.Trim) -> str: 1638 trim_type = self.sql(expression, "position") 1639 1640 if trim_type == "LEADING": 1641 return self.func("LTRIM", expression.this) 1642 elif trim_type == "TRAILING": 1643 return self.func("RTRIM", expression.this) 1644 else: 1645 return self.func("TRIM", expression.this, expression.expression) 1646 1647 def concat_sql(self, expression: exp.Concat) -> str: 1648 if len(expression.expressions) == 1: 1649 return self.sql(expression.expressions[0]) 1650 return self.function_fallback_sql(expression) 1651 1652 def check_sql(self, expression: exp.Check) -> str: 1653 this = self.sql(expression, key="this") 1654 return f"CHECK ({this})" 1655 1656 def foreignkey_sql(self, expression: exp.ForeignKey) -> str: 1657 expressions = self.expressions(expression, flat=True) 1658 reference = self.sql(expression, "reference") 1659 reference = f" {reference}" if reference else "" 1660 delete = self.sql(expression, "delete") 1661 delete = f" ON DELETE {delete}" if delete else "" 1662 update = self.sql(expression, "update") 1663 update = f" ON UPDATE {update}" if update else "" 1664 return f"FOREIGN KEY ({expressions}){reference}{delete}{update}" 1665 1666 def primarykey_sql(self, expression: exp.ForeignKey) -> str: 1667 expressions = self.expressions(expression, flat=True) 1668 options = self.expressions(expression, key="options", flat=True, sep=" ") 1669 options = f" {options}" if options else "" 1670 return f"PRIMARY KEY ({expressions}){options}" 1671 1672 def unique_sql(self, expression: exp.Unique) -> str: 1673 columns = self.expressions(expression, key="expressions") 1674 return f"UNIQUE ({columns})" 1675 1676 def if_sql(self, expression: exp.If) -> str: 1677 return self.case_sql( 1678 exp.Case(ifs=[expression.copy()], default=expression.args.get("false")) 1679 ) 1680 1681 def matchagainst_sql(self, expression: exp.MatchAgainst) -> str: 1682 modifier = expression.args.get("modifier") 1683 modifier = f" {modifier}" if modifier else "" 1684 return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})" 1685 1686 def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str: 1687 return f"{self.sql(expression, 'this')}: {self.sql(expression, 'expression')}" 1688 1689 def jsonobject_sql(self, expression: exp.JSONObject) -> str: 1690 expressions = self.expressions(expression) 1691 null_handling = expression.args.get("null_handling") 1692 null_handling = f" {null_handling}" if null_handling else "" 1693 unique_keys = expression.args.get("unique_keys") 1694 if unique_keys is not None: 1695 unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS" 1696 else: 1697 unique_keys = "" 1698 return_type = self.sql(expression, "return_type") 1699 return_type = f" RETURNING {return_type}" if return_type else "" 1700 format_json = " FORMAT JSON" if expression.args.get("format_json") else "" 1701 encoding = self.sql(expression, "encoding") 1702 encoding = f" ENCODING {encoding}" if encoding else "" 1703 return f"JSON_OBJECT({expressions}{null_handling}{unique_keys}{return_type}{format_json}{encoding})" 1704 1705 def in_sql(self, expression: exp.In) -> str: 1706 query = expression.args.get("query") 1707 unnest = expression.args.get("unnest") 1708 field = expression.args.get("field") 1709 is_global = " GLOBAL" if expression.args.get("is_global") else "" 1710 1711 if query: 1712 in_sql = self.wrap(query) 1713 elif unnest: 1714 in_sql = self.in_unnest_op(unnest) 1715 elif field: 1716 in_sql = self.sql(field) 1717 else: 1718 in_sql = f"({self.expressions(expression, flat=True)})" 1719 1720 return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}" 1721 1722 def in_unnest_op(self, unnest: exp.Unnest) -> str: 1723 return f"(SELECT {self.sql(unnest)})" 1724 1725 def interval_sql(self, expression: exp.Interval) -> str: 1726 unit = self.sql(expression, "unit") 1727 if not self.INTERVAL_ALLOWS_PLURAL_FORM: 1728 unit = self.TIME_PART_SINGULARS.get(unit.lower(), unit) 1729 unit = f" {unit}" if unit else "" 1730 1731 if self.SINGLE_STRING_INTERVAL: 1732 this = expression.this.name if expression.this else "" 1733 return f"INTERVAL '{this}{unit}'" 1734 1735 this = self.sql(expression, "this") 1736 if this: 1737 unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES) 1738 this = f" {this}" if unwrapped else f" ({this})" 1739 1740 return f"INTERVAL{this}{unit}" 1741 1742 def return_sql(self, expression: exp.Return) -> str: 1743 return f"RETURN {self.sql(expression, 'this')}" 1744 1745 def reference_sql(self, expression: exp.Reference) -> str: 1746 this = self.sql(expression, "this") 1747 expressions = self.expressions(expression, flat=True) 1748 expressions = f"({expressions})" if expressions else "" 1749 options = self.expressions(expression, key="options", flat=True, sep=" ") 1750 options = f" {options}" if options else "" 1751 return f"REFERENCES {this}{expressions}{options}" 1752 1753 def anonymous_sql(self, expression: exp.Anonymous) -> str: 1754 return self.func(expression.name, *expression.expressions) 1755 1756 def paren_sql(self, expression: exp.Paren) -> str: 1757 if isinstance(expression.unnest(), exp.Select): 1758 sql = self.wrap(expression) 1759 else: 1760 sql = self.seg(self.indent(self.sql(expression, "this")), sep="") 1761 sql = f"({sql}{self.seg(')', sep='')}" 1762 1763 return self.prepend_ctes(expression, sql) 1764 1765 def neg_sql(self, expression: exp.Neg) -> str: 1766 # This makes sure we don't convert "- - 5" to "--5", which is a comment 1767 this_sql = self.sql(expression, "this") 1768 sep = " " if this_sql[0] == "-" else "" 1769 return f"-{sep}{this_sql}" 1770 1771 def not_sql(self, expression: exp.Not) -> str: 1772 return f"NOT {self.sql(expression, 'this')}" 1773 1774 def alias_sql(self, expression: exp.Alias) -> str: 1775 alias = self.sql(expression, "alias") 1776 alias = f" AS {alias}" if alias else "" 1777 return f"{self.sql(expression, 'this')}{alias}" 1778 1779 def aliases_sql(self, expression: exp.Aliases) -> str: 1780 return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})" 1781 1782 def attimezone_sql(self, expression: exp.AtTimeZone) -> str: 1783 this = self.sql(expression, "this") 1784 zone = self.sql(expression, "zone") 1785 return f"{this} AT TIME ZONE {zone}" 1786 1787 def add_sql(self, expression: exp.Add) -> str: 1788 return self.binary(expression, "+") 1789 1790 def and_sql(self, expression: exp.And) -> str: 1791 return self.connector_sql(expression, "AND") 1792 1793 def connector_sql(self, expression: exp.Connector, op: str) -> str: 1794 if not self.pretty: 1795 return self.binary(expression, op) 1796 1797 sqls = tuple( 1798 self.maybe_comment(self.sql(e), e, e.parent.comments) if i != 1 else self.sql(e) 1799 for i, e in enumerate(expression.flatten(unnest=False)) 1800 ) 1801 1802 sep = "\n" if self.text_width(sqls) > self._max_text_width else " " 1803 return f"{sep}{op} ".join(sqls) 1804 1805 def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str: 1806 return self.binary(expression, "&") 1807 1808 def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str: 1809 return self.binary(expression, "<<") 1810 1811 def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str: 1812 return f"~{self.sql(expression, 'this')}" 1813 1814 def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str: 1815 return self.binary(expression, "|") 1816 1817 def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str: 1818 return self.binary(expression, ">>") 1819 1820 def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str: 1821 return self.binary(expression, "^") 1822 1823 def cast_sql(self, expression: exp.Cast) -> str: 1824 return f"CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')})" 1825 1826 def currentdate_sql(self, expression: exp.CurrentDate) -> str: 1827 zone = self.sql(expression, "this") 1828 return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE" 1829 1830 def collate_sql(self, expression: exp.Collate) -> str: 1831 return self.binary(expression, "COLLATE") 1832 1833 def command_sql(self, expression: exp.Command) -> str: 1834 return f"{self.sql(expression, 'this').upper()} {expression.text('expression').strip()}" 1835 1836 def comment_sql(self, expression: exp.Comment) -> str: 1837 this = self.sql(expression, "this") 1838 kind = expression.args["kind"] 1839 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 1840 expression_sql = self.sql(expression, "expression") 1841 return f"COMMENT{exists_sql}ON {kind} {this} IS {expression_sql}" 1842 1843 def transaction_sql(self, expression: exp.Transaction) -> str: 1844 return "BEGIN" 1845 1846 def commit_sql(self, expression: exp.Commit) -> str: 1847 chain = expression.args.get("chain") 1848 if chain is not None: 1849 chain = " AND CHAIN" if chain else " AND NO CHAIN" 1850 1851 return f"COMMIT{chain or ''}" 1852 1853 def rollback_sql(self, expression: exp.Rollback) -> str: 1854 savepoint = expression.args.get("savepoint") 1855 savepoint = f" TO {savepoint}" if savepoint else "" 1856 return f"ROLLBACK{savepoint}" 1857 1858 def altercolumn_sql(self, expression: exp.AlterColumn) -> str: 1859 this = self.sql(expression, "this") 1860 1861 dtype = self.sql(expression, "dtype") 1862 if dtype: 1863 collate = self.sql(expression, "collate") 1864 collate = f" COLLATE {collate}" if collate else "" 1865 using = self.sql(expression, "using") 1866 using = f" USING {using}" if using else "" 1867 return f"ALTER COLUMN {this} TYPE {dtype}{collate}{using}" 1868 1869 default = self.sql(expression, "default") 1870 if default: 1871 return f"ALTER COLUMN {this} SET DEFAULT {default}" 1872 1873 if not expression.args.get("drop"): 1874 self.unsupported("Unsupported ALTER COLUMN syntax") 1875 1876 return f"ALTER COLUMN {this} DROP DEFAULT" 1877 1878 def renametable_sql(self, expression: exp.RenameTable) -> str: 1879 this = self.sql(expression, "this") 1880 return f"RENAME TO {this}" 1881 1882 def altertable_sql(self, expression: exp.AlterTable) -> str: 1883 actions = expression.args["actions"] 1884 1885 if isinstance(actions[0], exp.ColumnDef): 1886 actions = self.expressions(expression, key="actions", prefix="ADD COLUMN ") 1887 elif isinstance(actions[0], exp.Schema): 1888 actions = self.expressions(expression, key="actions", prefix="ADD COLUMNS ") 1889 elif isinstance(actions[0], exp.Delete): 1890 actions = self.expressions(expression, key="actions", flat=True) 1891 else: 1892 actions = self.expressions(expression, key="actions") 1893 1894 exists = " IF EXISTS" if expression.args.get("exists") else "" 1895 return f"ALTER TABLE{exists} {self.sql(expression, 'this')} {actions}" 1896 1897 def droppartition_sql(self, expression: exp.DropPartition) -> str: 1898 expressions = self.expressions(expression) 1899 exists = " IF EXISTS " if expression.args.get("exists") else " " 1900 return f"DROP{exists}{expressions}" 1901 1902 def addconstraint_sql(self, expression: exp.AddConstraint) -> str: 1903 this = self.sql(expression, "this") 1904 expression_ = self.sql(expression, "expression") 1905 add_constraint = f"ADD CONSTRAINT {this}" if this else "ADD" 1906 1907 enforced = expression.args.get("enforced") 1908 if enforced is not None: 1909 return f"{add_constraint} CHECK ({expression_}){' ENFORCED' if enforced else ''}" 1910 1911 return f"{add_constraint} {expression_}" 1912 1913 def distinct_sql(self, expression: exp.Distinct) -> str: 1914 this = self.expressions(expression, flat=True) 1915 this = f" {this}" if this else "" 1916 1917 on = self.sql(expression, "on") 1918 on = f" ON {on}" if on else "" 1919 return f"DISTINCT{this}{on}" 1920 1921 def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str: 1922 return f"{self.sql(expression, 'this')} IGNORE NULLS" 1923 1924 def respectnulls_sql(self, expression: exp.RespectNulls) -> str: 1925 return f"{self.sql(expression, 'this')} RESPECT NULLS" 1926 1927 def intdiv_sql(self, expression: exp.IntDiv) -> str: 1928 return self.sql( 1929 exp.Cast( 1930 this=exp.Div(this=expression.this, expression=expression.expression), 1931 to=exp.DataType(this=exp.DataType.Type.INT), 1932 ) 1933 ) 1934 1935 def dpipe_sql(self, expression: exp.DPipe) -> str: 1936 return self.binary(expression, "||") 1937 1938 def div_sql(self, expression: exp.Div) -> str: 1939 return self.binary(expression, "/") 1940 1941 def overlaps_sql(self, expression: exp.Overlaps) -> str: 1942 return self.binary(expression, "OVERLAPS") 1943 1944 def distance_sql(self, expression: exp.Distance) -> str: 1945 return self.binary(expression, "<->") 1946 1947 def dot_sql(self, expression: exp.Dot) -> str: 1948 return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}" 1949 1950 def eq_sql(self, expression: exp.EQ) -> str: 1951 return self.binary(expression, "=") 1952 1953 def escape_sql(self, expression: exp.Escape) -> str: 1954 return self.binary(expression, "ESCAPE") 1955 1956 def glob_sql(self, expression: exp.Glob) -> str: 1957 return self.binary(expression, "GLOB") 1958 1959 def gt_sql(self, expression: exp.GT) -> str: 1960 return self.binary(expression, ">") 1961 1962 def gte_sql(self, expression: exp.GTE) -> str: 1963 return self.binary(expression, ">=") 1964 1965 def ilike_sql(self, expression: exp.ILike) -> str: 1966 return self.binary(expression, "ILIKE") 1967 1968 def ilikeany_sql(self, expression: exp.ILikeAny) -> str: 1969 return self.binary(expression, "ILIKE ANY") 1970 1971 def is_sql(self, expression: exp.Is) -> str: 1972 return self.binary(expression, "IS") 1973 1974 def like_sql(self, expression: exp.Like) -> str: 1975 return self.binary(expression, "LIKE") 1976 1977 def likeany_sql(self, expression: exp.LikeAny) -> str: 1978 return self.binary(expression, "LIKE ANY") 1979 1980 def similarto_sql(self, expression: exp.SimilarTo) -> str: 1981 return self.binary(expression, "SIMILAR TO") 1982 1983 def lt_sql(self, expression: exp.LT) -> str: 1984 return self.binary(expression, "<") 1985 1986 def lte_sql(self, expression: exp.LTE) -> str: 1987 return self.binary(expression, "<=") 1988 1989 def mod_sql(self, expression: exp.Mod) -> str: 1990 return self.binary(expression, "%") 1991 1992 def mul_sql(self, expression: exp.Mul) -> str: 1993 return self.binary(expression, "*") 1994 1995 def neq_sql(self, expression: exp.NEQ) -> str: 1996 return self.binary(expression, "<>") 1997 1998 def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str: 1999 return self.binary(expression, "IS NOT DISTINCT FROM") 2000 2001 def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str: 2002 return self.binary(expression, "IS DISTINCT FROM") 2003 2004 def or_sql(self, expression: exp.Or) -> str: 2005 return self.connector_sql(expression, "OR") 2006 2007 def slice_sql(self, expression: exp.Slice) -> str: 2008 return self.binary(expression, ":") 2009 2010 def sub_sql(self, expression: exp.Sub) -> str: 2011 return self.binary(expression, "-") 2012 2013 def trycast_sql(self, expression: exp.TryCast) -> str: 2014 return f"TRY_CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')})" 2015 2016 def use_sql(self, expression: exp.Use) -> str: 2017 kind = self.sql(expression, "kind") 2018 kind = f" {kind}" if kind else "" 2019 this = self.sql(expression, "this") 2020 this = f" {this}" if this else "" 2021 return f"USE{kind}{this}" 2022 2023 def binary(self, expression: exp.Binary, op: str) -> str: 2024 op = self.maybe_comment(op, comments=expression.comments) 2025 return f"{self.sql(expression, 'this')} {op} {self.sql(expression, 'expression')}" 2026 2027 def function_fallback_sql(self, expression: exp.Func) -> str: 2028 args = [] 2029 for arg_value in expression.args.values(): 2030 if isinstance(arg_value, list): 2031 for value in arg_value: 2032 args.append(value) 2033 else: 2034 args.append(arg_value) 2035 2036 return self.func(expression.sql_name(), *args) 2037 2038 def func(self, name: str, *args: t.Optional[exp.Expression | str]) -> str: 2039 return f"{self.normalize_func(name)}({self.format_args(*args)})" 2040 2041 def format_args(self, *args: t.Optional[str | exp.Expression]) -> str: 2042 arg_sqls = tuple(self.sql(arg) for arg in args if arg is not None) 2043 if self.pretty and self.text_width(arg_sqls) > self._max_text_width: 2044 return self.indent("\n" + f",\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True) 2045 return ", ".join(arg_sqls) 2046 2047 def text_width(self, args: t.Iterable) -> int: 2048 return sum(len(arg) for arg in args) 2049 2050 def format_time(self, expression: exp.Expression) -> t.Optional[str]: 2051 return format_time(self.sql(expression, "format"), self.time_mapping, self.time_trie) 2052 2053 def expressions( 2054 self, 2055 expression: t.Optional[exp.Expression] = None, 2056 key: t.Optional[str] = None, 2057 sqls: t.Optional[t.List[str]] = None, 2058 flat: bool = False, 2059 indent: bool = True, 2060 sep: str = ", ", 2061 prefix: str = "", 2062 ) -> str: 2063 expressions = expression.args.get(key or "expressions") if expression else sqls 2064 2065 if not expressions: 2066 return "" 2067 2068 if flat: 2069 return sep.join(self.sql(e) for e in expressions) 2070 2071 num_sqls = len(expressions) 2072 2073 # These are calculated once in case we have the leading_comma / pretty option set, correspondingly 2074 pad = " " * self.pad 2075 stripped_sep = sep.strip() 2076 2077 result_sqls = [] 2078 for i, e in enumerate(expressions): 2079 sql = self.sql(e, comment=False) 2080 comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else "" 2081 2082 if self.pretty: 2083 if self._leading_comma: 2084 result_sqls.append(f"{sep if i > 0 else pad}{prefix}{sql}{comments}") 2085 else: 2086 result_sqls.append( 2087 f"{prefix}{sql}{stripped_sep if i + 1 < num_sqls else ''}{comments}" 2088 ) 2089 else: 2090 result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}") 2091 2092 result_sql = "\n".join(result_sqls) if self.pretty else "".join(result_sqls) 2093 return self.indent(result_sql, skip_first=False) if indent else result_sql 2094 2095 def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str: 2096 flat = flat or isinstance(expression.parent, exp.Properties) 2097 expressions_sql = self.expressions(expression, flat=flat) 2098 if flat: 2099 return f"{op} {expressions_sql}" 2100 return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}" 2101 2102 def naked_property(self, expression: exp.Property) -> str: 2103 property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__) 2104 if not property_name: 2105 self.unsupported(f"Unsupported property {expression.__class__.__name__}") 2106 return f"{property_name} {self.sql(expression, 'this')}" 2107 2108 def set_operation(self, expression: exp.Expression, op: str) -> str: 2109 this = self.sql(expression, "this") 2110 op = self.seg(op) 2111 return self.query_modifiers( 2112 expression, f"{this}{op}{self.sep()}{self.sql(expression, 'expression')}" 2113 ) 2114 2115 def tag_sql(self, expression: exp.Tag) -> str: 2116 return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}" 2117 2118 def token_sql(self, token_type: TokenType) -> str: 2119 return self.TOKEN_MAPPING.get(token_type, token_type.name) 2120 2121 def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str: 2122 this = self.sql(expression, "this") 2123 expressions = self.no_identify(self.expressions, expression) 2124 expressions = ( 2125 self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}" 2126 ) 2127 return f"{this}{expressions}" 2128 2129 def joinhint_sql(self, expression: exp.JoinHint) -> str: 2130 this = self.sql(expression, "this") 2131 expressions = self.expressions(expression, flat=True) 2132 return f"{this}({expressions})" 2133 2134 def kwarg_sql(self, expression: exp.Kwarg) -> str: 2135 return self.binary(expression, "=>") 2136 2137 def when_sql(self, expression: exp.When) -> str: 2138 matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED" 2139 source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else "" 2140 condition = self.sql(expression, "condition") 2141 condition = f" AND {condition}" if condition else "" 2142 2143 then_expression = expression.args.get("then") 2144 if isinstance(then_expression, exp.Insert): 2145 then = f"INSERT {self.sql(then_expression, 'this')}" 2146 if "expression" in then_expression.args: 2147 then += f" VALUES {self.sql(then_expression, 'expression')}" 2148 elif isinstance(then_expression, exp.Update): 2149 if isinstance(then_expression.args.get("expressions"), exp.Star): 2150 then = f"UPDATE {self.sql(then_expression, 'expressions')}" 2151 else: 2152 then = f"UPDATE SET {self.expressions(then_expression, flat=True)}" 2153 else: 2154 then = self.sql(then_expression) 2155 return f"WHEN {matched}{source}{condition} THEN {then}" 2156 2157 def merge_sql(self, expression: exp.Merge) -> str: 2158 this = self.sql(expression, "this") 2159 using = f"USING {self.sql(expression, 'using')}" 2160 on = f"ON {self.sql(expression, 'on')}" 2161 return f"MERGE INTO {this} {using} {on} {self.expressions(expression, sep=' ')}" 2162 2163 def tochar_sql(self, expression: exp.ToChar) -> str: 2164 if expression.args.get("format"): 2165 self.unsupported("Format argument unsupported for TO_CHAR/TO_VARCHAR function") 2166 2167 return self.sql(exp.cast(expression.this, "text"))
class
Generator:
16class Generator: 17 """ 18 Generator interprets the given syntax tree and produces a SQL string as an output. 19 20 Args: 21 time_mapping (dict): the dictionary of custom time mappings in which the key 22 represents a python time format and the output the target time format 23 time_trie (trie): a trie of the time_mapping keys 24 pretty (bool): if set to True the returned string will be formatted. Default: False. 25 quote_start (str): specifies which starting character to use to delimit quotes. Default: '. 26 quote_end (str): specifies which ending character to use to delimit quotes. Default: '. 27 identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ". 28 identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ". 29 identify (bool | str): 'always': always quote, 'safe': quote identifiers if they don't contain an upcase, True defaults to always. 30 normalize (bool): if set to True all identifiers will lower cased 31 string_escape (str): specifies a string escape character. Default: '. 32 identifier_escape (str): specifies an identifier escape character. Default: ". 33 pad (int): determines padding in a formatted string. Default: 2. 34 indent (int): determines the size of indentation in a formatted string. Default: 4. 35 unnest_column_only (bool): if true unnest table aliases are considered only as column aliases 36 normalize_functions (str): normalize function names, "upper", "lower", or None 37 Default: "upper" 38 alias_post_tablesample (bool): if the table alias comes after tablesample 39 Default: False 40 unsupported_level (ErrorLevel): determines the generator's behavior when it encounters 41 unsupported expressions. Default ErrorLevel.WARN. 42 null_ordering (str): Indicates the default null ordering method to use if not explicitly set. 43 Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". 44 Default: "nulls_are_small" 45 max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. 46 This is only relevant if unsupported_level is ErrorLevel.RAISE. 47 Default: 3 48 leading_comma (bool): if the the comma is leading or trailing in select statements 49 Default: False 50 max_text_width: The max number of characters in a segment before creating new lines in pretty mode. 51 The default is on the smaller end because the length only represents a segment and not the true 52 line length. 53 Default: 80 54 comments: Whether or not to preserve comments in the output SQL code. 55 Default: True 56 """ 57 58 TRANSFORMS = { 59 exp.DateAdd: lambda self, e: self.func( 60 "DATE_ADD", e.this, e.expression, exp.Literal.string(e.text("unit")) 61 ), 62 exp.TsOrDsAdd: lambda self, e: self.func( 63 "TS_OR_DS_ADD", e.this, e.expression, exp.Literal.string(e.text("unit")) 64 ), 65 exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]), 66 exp.CharacterSetProperty: lambda self, e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}", 67 exp.ExecuteAsProperty: lambda self, e: self.naked_property(e), 68 exp.ExternalProperty: lambda self, e: "EXTERNAL", 69 exp.LanguageProperty: lambda self, e: self.naked_property(e), 70 exp.LocationProperty: lambda self, e: self.naked_property(e), 71 exp.LogProperty: lambda self, e: f"{'NO ' if e.args.get('no') else ''}LOG", 72 exp.MaterializedProperty: lambda self, e: "MATERIALIZED", 73 exp.NoPrimaryIndexProperty: lambda self, e: "NO PRIMARY INDEX", 74 exp.OnCommitProperty: lambda self, e: "ON COMMIT PRESERVE ROWS", 75 exp.ReturnsProperty: lambda self, e: self.naked_property(e), 76 exp.SetProperty: lambda self, e: f"{'MULTI' if e.args.get('multi') else ''}SET", 77 exp.SqlSecurityProperty: lambda self, e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}", 78 exp.TemporaryProperty: lambda self, e: f"{'GLOBAL ' if e.args.get('global_') else ''}TEMPORARY", 79 exp.TransientProperty: lambda self, e: "TRANSIENT", 80 exp.StabilityProperty: lambda self, e: e.name, 81 exp.VolatileProperty: lambda self, e: "VOLATILE", 82 exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}", 83 exp.CaseSpecificColumnConstraint: lambda self, e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC", 84 exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}", 85 exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}", 86 exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}", 87 exp.UppercaseColumnConstraint: lambda self, e: f"UPPERCASE", 88 exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}", 89 exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}", 90 exp.CheckColumnConstraint: lambda self, e: f"CHECK ({self.sql(e, 'this')})", 91 exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}", 92 exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}", 93 exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}", 94 exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}", 95 exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}", 96 } 97 98 # Whether or not null ordering is supported in order by 99 NULL_ORDERING_SUPPORTED = True 100 101 # Whether or not locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported 102 LOCKING_READS_SUPPORTED = False 103 104 # Always do union distinct or union all 105 EXPLICIT_UNION = False 106 107 # Wrap derived values in parens, usually standard but spark doesn't support it 108 WRAP_DERIVED_VALUES = True 109 110 # Whether or not create function uses an AS before the RETURN 111 CREATE_FUNCTION_RETURN_AS = True 112 113 # Whether or not MERGE ... WHEN MATCHED BY SOURCE is allowed 114 MATCHED_BY_SOURCE = True 115 116 # Whether or not the INTERVAL expression works only with values like '1 day' 117 SINGLE_STRING_INTERVAL = False 118 119 # Whether or not the plural form of date parts like day (i.e. "days") is supported in INTERVALs 120 INTERVAL_ALLOWS_PLURAL_FORM = True 121 122 # Whether or not the TABLESAMPLE clause supports a method name, like BERNOULLI 123 TABLESAMPLE_WITH_METHOD = True 124 125 # Whether or not to treat the number in TABLESAMPLE (50) as a percentage 126 TABLESAMPLE_SIZE_IS_PERCENT = False 127 128 # Whether or not limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH") 129 LIMIT_FETCH = "ALL" 130 131 TYPE_MAPPING = { 132 exp.DataType.Type.NCHAR: "CHAR", 133 exp.DataType.Type.NVARCHAR: "VARCHAR", 134 exp.DataType.Type.MEDIUMTEXT: "TEXT", 135 exp.DataType.Type.LONGTEXT: "TEXT", 136 exp.DataType.Type.MEDIUMBLOB: "BLOB", 137 exp.DataType.Type.LONGBLOB: "BLOB", 138 exp.DataType.Type.INET: "INET", 139 } 140 141 STAR_MAPPING = { 142 "except": "EXCEPT", 143 "replace": "REPLACE", 144 } 145 146 TIME_PART_SINGULARS = { 147 "microseconds": "microsecond", 148 "seconds": "second", 149 "minutes": "minute", 150 "hours": "hour", 151 "days": "day", 152 "weeks": "week", 153 "months": "month", 154 "quarters": "quarter", 155 "years": "year", 156 } 157 158 TOKEN_MAPPING: t.Dict[TokenType, str] = {} 159 160 STRUCT_DELIMITER = ("<", ">") 161 162 PARAMETER_TOKEN = "@" 163 164 PROPERTIES_LOCATION = { 165 exp.AfterJournalProperty: exp.Properties.Location.POST_NAME, 166 exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE, 167 exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA, 168 exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME, 169 exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA, 170 exp.ChecksumProperty: exp.Properties.Location.POST_NAME, 171 exp.CollateProperty: exp.Properties.Location.POST_SCHEMA, 172 exp.Cluster: exp.Properties.Location.POST_SCHEMA, 173 exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME, 174 exp.DefinerProperty: exp.Properties.Location.POST_CREATE, 175 exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA, 176 exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA, 177 exp.EngineProperty: exp.Properties.Location.POST_SCHEMA, 178 exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA, 179 exp.ExternalProperty: exp.Properties.Location.POST_CREATE, 180 exp.FallbackProperty: exp.Properties.Location.POST_NAME, 181 exp.FileFormatProperty: exp.Properties.Location.POST_WITH, 182 exp.FreespaceProperty: exp.Properties.Location.POST_NAME, 183 exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME, 184 exp.JournalProperty: exp.Properties.Location.POST_NAME, 185 exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA, 186 exp.LikeProperty: exp.Properties.Location.POST_SCHEMA, 187 exp.LocationProperty: exp.Properties.Location.POST_SCHEMA, 188 exp.LockingProperty: exp.Properties.Location.POST_ALIAS, 189 exp.LogProperty: exp.Properties.Location.POST_NAME, 190 exp.MaterializedProperty: exp.Properties.Location.POST_CREATE, 191 exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME, 192 exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION, 193 exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION, 194 exp.PartitionedByProperty: exp.Properties.Location.POST_WITH, 195 exp.Property: exp.Properties.Location.POST_WITH, 196 exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA, 197 exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA, 198 exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA, 199 exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA, 200 exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA, 201 exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA, 202 exp.SetProperty: exp.Properties.Location.POST_CREATE, 203 exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA, 204 exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE, 205 exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA, 206 exp.TableFormatProperty: exp.Properties.Location.POST_WITH, 207 exp.TemporaryProperty: exp.Properties.Location.POST_CREATE, 208 exp.TransientProperty: exp.Properties.Location.POST_CREATE, 209 exp.VolatileProperty: exp.Properties.Location.POST_CREATE, 210 exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION, 211 exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME, 212 } 213 214 JOIN_HINTS = True 215 TABLE_HINTS = True 216 217 RESERVED_KEYWORDS: t.Set[str] = set() 218 WITH_SEPARATED_COMMENTS = (exp.Select, exp.From, exp.Where, exp.With) 219 UNWRAPPED_INTERVAL_VALUES = (exp.Literal, exp.Paren, exp.Column) 220 221 SENTINEL_LINE_BREAK = "__SQLGLOT__LB__" 222 223 __slots__ = ( 224 "time_mapping", 225 "time_trie", 226 "pretty", 227 "quote_start", 228 "quote_end", 229 "identifier_start", 230 "identifier_end", 231 "identify", 232 "normalize", 233 "string_escape", 234 "identifier_escape", 235 "pad", 236 "index_offset", 237 "unnest_column_only", 238 "alias_post_tablesample", 239 "normalize_functions", 240 "unsupported_level", 241 "unsupported_messages", 242 "null_ordering", 243 "max_unsupported", 244 "_indent", 245 "_escaped_quote_end", 246 "_escaped_identifier_end", 247 "_leading_comma", 248 "_max_text_width", 249 "_comments", 250 "_cache", 251 ) 252 253 def __init__( 254 self, 255 time_mapping=None, 256 time_trie=None, 257 pretty=None, 258 quote_start=None, 259 quote_end=None, 260 identifier_start=None, 261 identifier_end=None, 262 identify=False, 263 normalize=False, 264 string_escape=None, 265 identifier_escape=None, 266 pad=2, 267 indent=2, 268 index_offset=0, 269 unnest_column_only=False, 270 alias_post_tablesample=False, 271 normalize_functions="upper", 272 unsupported_level=ErrorLevel.WARN, 273 null_ordering=None, 274 max_unsupported=3, 275 leading_comma=False, 276 max_text_width=80, 277 comments=True, 278 ): 279 import sqlglot 280 281 self.time_mapping = time_mapping or {} 282 self.time_trie = time_trie 283 self.pretty = pretty if pretty is not None else sqlglot.pretty 284 self.quote_start = quote_start or "'" 285 self.quote_end = quote_end or "'" 286 self.identifier_start = identifier_start or '"' 287 self.identifier_end = identifier_end or '"' 288 self.identify = identify 289 self.normalize = normalize 290 self.string_escape = string_escape or "'" 291 self.identifier_escape = identifier_escape or '"' 292 self.pad = pad 293 self.index_offset = index_offset 294 self.unnest_column_only = unnest_column_only 295 self.alias_post_tablesample = alias_post_tablesample 296 self.normalize_functions = normalize_functions 297 self.unsupported_level = unsupported_level 298 self.unsupported_messages = [] 299 self.max_unsupported = max_unsupported 300 self.null_ordering = null_ordering 301 self._indent = indent 302 self._escaped_quote_end = self.string_escape + self.quote_end 303 self._escaped_identifier_end = self.identifier_escape + self.identifier_end 304 self._leading_comma = leading_comma 305 self._max_text_width = max_text_width 306 self._comments = comments 307 self._cache = None 308 309 def generate( 310 self, 311 expression: t.Optional[exp.Expression], 312 cache: t.Optional[t.Dict[int, str]] = None, 313 ) -> str: 314 """ 315 Generates a SQL string by interpreting the given syntax tree. 316 317 Args 318 expression: the syntax tree. 319 cache: an optional sql string cache. this leverages the hash of an expression which is slow, so only use this if you set _hash on each node. 320 321 Returns 322 the SQL string. 323 """ 324 if cache is not None: 325 self._cache = cache 326 self.unsupported_messages = [] 327 sql = self.sql(expression).strip() 328 self._cache = None 329 330 if self.unsupported_level == ErrorLevel.IGNORE: 331 return sql 332 333 if self.unsupported_level == ErrorLevel.WARN: 334 for msg in self.unsupported_messages: 335 logger.warning(msg) 336 elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages: 337 raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported)) 338 339 if self.pretty: 340 sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n") 341 return sql 342 343 def unsupported(self, message: str) -> None: 344 if self.unsupported_level == ErrorLevel.IMMEDIATE: 345 raise UnsupportedError(message) 346 self.unsupported_messages.append(message) 347 348 def sep(self, sep: str = " ") -> str: 349 return f"{sep.strip()}\n" if self.pretty else sep 350 351 def seg(self, sql: str, sep: str = " ") -> str: 352 return f"{self.sep(sep)}{sql}" 353 354 def pad_comment(self, comment: str) -> str: 355 comment = " " + comment if comment[0].strip() else comment 356 comment = comment + " " if comment[-1].strip() else comment 357 return comment 358 359 def maybe_comment( 360 self, 361 sql: str, 362 expression: t.Optional[exp.Expression] = None, 363 comments: t.Optional[t.List[str]] = None, 364 ) -> str: 365 comments = (comments or (expression and expression.comments)) if self._comments else None # type: ignore 366 367 if not comments or isinstance(expression, exp.Binary): 368 return sql 369 370 sep = "\n" if self.pretty else " " 371 comments_sql = sep.join( 372 f"/*{self.pad_comment(comment)}*/" for comment in comments if comment 373 ) 374 375 if not comments_sql: 376 return sql 377 378 if isinstance(expression, self.WITH_SEPARATED_COMMENTS): 379 return f"{comments_sql}{self.sep()}{sql}" 380 381 return f"{sql} {comments_sql}" 382 383 def wrap(self, expression: exp.Expression | str) -> str: 384 this_sql = self.indent( 385 self.sql(expression) 386 if isinstance(expression, (exp.Select, exp.Union)) 387 else self.sql(expression, "this"), 388 level=1, 389 pad=0, 390 ) 391 return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}" 392 393 def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str: 394 original = self.identify 395 self.identify = False 396 result = func(*args, **kwargs) 397 self.identify = original 398 return result 399 400 def normalize_func(self, name: str) -> str: 401 if self.normalize_functions == "upper": 402 return name.upper() 403 if self.normalize_functions == "lower": 404 return name.lower() 405 return name 406 407 def indent( 408 self, 409 sql: str, 410 level: int = 0, 411 pad: t.Optional[int] = None, 412 skip_first: bool = False, 413 skip_last: bool = False, 414 ) -> str: 415 if not self.pretty: 416 return sql 417 418 pad = self.pad if pad is None else pad 419 lines = sql.split("\n") 420 421 return "\n".join( 422 line 423 if (skip_first and i == 0) or (skip_last and i == len(lines) - 1) 424 else f"{' ' * (level * self._indent + pad)}{line}" 425 for i, line in enumerate(lines) 426 ) 427 428 def sql( 429 self, 430 expression: t.Optional[str | exp.Expression], 431 key: t.Optional[str] = None, 432 comment: bool = True, 433 ) -> str: 434 if not expression: 435 return "" 436 437 if isinstance(expression, str): 438 return expression 439 440 if key: 441 return self.sql(expression.args.get(key)) 442 443 if self._cache is not None: 444 expression_id = hash(expression) 445 446 if expression_id in self._cache: 447 return self._cache[expression_id] 448 449 transform = self.TRANSFORMS.get(expression.__class__) 450 451 if callable(transform): 452 sql = transform(self, expression) 453 elif transform: 454 sql = transform 455 elif isinstance(expression, exp.Expression): 456 exp_handler_name = f"{expression.key}_sql" 457 458 if hasattr(self, exp_handler_name): 459 sql = getattr(self, exp_handler_name)(expression) 460 elif isinstance(expression, exp.Func): 461 sql = self.function_fallback_sql(expression) 462 elif isinstance(expression, exp.Property): 463 sql = self.property_sql(expression) 464 else: 465 raise ValueError(f"Unsupported expression type {expression.__class__.__name__}") 466 else: 467 raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}") 468 469 sql = self.maybe_comment(sql, expression) if self._comments and comment else sql 470 471 if self._cache is not None: 472 self._cache[expression_id] = sql 473 return sql 474 475 def uncache_sql(self, expression: exp.Uncache) -> str: 476 table = self.sql(expression, "this") 477 exists_sql = " IF EXISTS" if expression.args.get("exists") else "" 478 return f"UNCACHE TABLE{exists_sql} {table}" 479 480 def cache_sql(self, expression: exp.Cache) -> str: 481 lazy = " LAZY" if expression.args.get("lazy") else "" 482 table = self.sql(expression, "this") 483 options = expression.args.get("options") 484 options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else "" 485 sql = self.sql(expression, "expression") 486 sql = f" AS{self.sep()}{sql}" if sql else "" 487 sql = f"CACHE{lazy} TABLE {table}{options}{sql}" 488 return self.prepend_ctes(expression, sql) 489 490 def characterset_sql(self, expression: exp.CharacterSet) -> str: 491 if isinstance(expression.parent, exp.Cast): 492 return f"CHAR CHARACTER SET {self.sql(expression, 'this')}" 493 default = "DEFAULT " if expression.args.get("default") else "" 494 return f"{default}CHARACTER SET={self.sql(expression, 'this')}" 495 496 def column_sql(self, expression: exp.Column) -> str: 497 return ".".join( 498 self.sql(part) 499 for part in ( 500 expression.args.get("catalog"), 501 expression.args.get("db"), 502 expression.args.get("table"), 503 expression.args.get("this"), 504 ) 505 if part 506 ) 507 508 def columnposition_sql(self, expression: exp.ColumnPosition) -> str: 509 this = self.sql(expression, "this") 510 this = f" {this}" if this else "" 511 position = self.sql(expression, "position") 512 return f"{position}{this}" 513 514 def columndef_sql(self, expression: exp.ColumnDef) -> str: 515 column = self.sql(expression, "this") 516 kind = self.sql(expression, "kind") 517 constraints = self.expressions(expression, key="constraints", sep=" ", flat=True) 518 exists = "IF NOT EXISTS " if expression.args.get("exists") else "" 519 kind = f" {kind}" if kind else "" 520 constraints = f" {constraints}" if constraints else "" 521 position = self.sql(expression, "position") 522 position = f" {position}" if position else "" 523 524 return f"{exists}{column}{kind}{constraints}{position}" 525 526 def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str: 527 this = self.sql(expression, "this") 528 kind_sql = self.sql(expression, "kind") 529 return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql 530 531 def autoincrementcolumnconstraint_sql(self, _) -> str: 532 return self.token_sql(TokenType.AUTO_INCREMENT) 533 534 def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str: 535 if isinstance(expression.this, list): 536 this = self.wrap(self.expressions(expression, key="this", flat=True)) 537 else: 538 this = self.sql(expression, "this") 539 540 return f"COMPRESS {this}" 541 542 def generatedasidentitycolumnconstraint_sql( 543 self, expression: exp.GeneratedAsIdentityColumnConstraint 544 ) -> str: 545 this = "" 546 if expression.this is not None: 547 this = " ALWAYS " if expression.this else " BY DEFAULT " 548 start = expression.args.get("start") 549 start = f"START WITH {start}" if start else "" 550 increment = expression.args.get("increment") 551 increment = f" INCREMENT BY {increment}" if increment else "" 552 minvalue = expression.args.get("minvalue") 553 minvalue = f" MINVALUE {minvalue}" if minvalue else "" 554 maxvalue = expression.args.get("maxvalue") 555 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else "" 556 cycle = expression.args.get("cycle") 557 cycle_sql = "" 558 if cycle is not None: 559 cycle_sql = f"{' NO' if not cycle else ''} CYCLE" 560 cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql 561 sequence_opts = "" 562 if start or increment or cycle_sql: 563 sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}" 564 sequence_opts = f" ({sequence_opts.strip()})" 565 return f"GENERATED{this}AS IDENTITY{sequence_opts}" 566 567 def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str: 568 return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL" 569 570 def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str: 571 desc = expression.args.get("desc") 572 if desc is not None: 573 return f"PRIMARY KEY{' DESC' if desc else ' ASC'}" 574 return f"PRIMARY KEY" 575 576 def uniquecolumnconstraint_sql(self, _) -> str: 577 return "UNIQUE" 578 579 def create_sql(self, expression: exp.Create) -> str: 580 kind = self.sql(expression, "kind").upper() 581 properties = expression.args.get("properties") 582 properties_exp = expression.copy() 583 properties_locs = self.locate_properties(properties) if properties else {} 584 if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get( 585 exp.Properties.Location.POST_WITH 586 ): 587 properties_exp.set( 588 "properties", 589 exp.Properties( 590 expressions=[ 591 *properties_locs[exp.Properties.Location.POST_SCHEMA], 592 *properties_locs[exp.Properties.Location.POST_WITH], 593 ] 594 ), 595 ) 596 if kind == "TABLE" and properties_locs.get(exp.Properties.Location.POST_NAME): 597 this_name = self.sql(expression.this, "this") 598 this_properties = self.properties( 599 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_NAME]), 600 wrapped=False, 601 ) 602 this_schema = f"({self.expressions(expression.this)})" 603 this = f"{this_name}, {this_properties} {this_schema}" 604 properties_sql = "" 605 else: 606 this = self.sql(expression, "this") 607 properties_sql = self.sql(properties_exp, "properties") 608 begin = " BEGIN" if expression.args.get("begin") else "" 609 expression_sql = self.sql(expression, "expression") 610 if expression_sql: 611 expression_sql = f"{begin}{self.sep()}{expression_sql}" 612 613 if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return): 614 if properties_locs.get(exp.Properties.Location.POST_ALIAS): 615 postalias_props_sql = self.properties( 616 exp.Properties( 617 expressions=properties_locs[exp.Properties.Location.POST_ALIAS] 618 ), 619 wrapped=False, 620 ) 621 expression_sql = f" AS {postalias_props_sql}{expression_sql}" 622 else: 623 expression_sql = f" AS{expression_sql}" 624 625 postindex_props_sql = "" 626 if properties_locs.get(exp.Properties.Location.POST_INDEX): 627 postindex_props_sql = self.properties( 628 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]), 629 wrapped=False, 630 prefix=" ", 631 ) 632 633 indexes = expression.args.get("indexes") 634 if indexes: 635 indexes_sql: t.List[str] = [] 636 for index in indexes: 637 ind_unique = " UNIQUE" if index.args.get("unique") else "" 638 ind_primary = " PRIMARY" if index.args.get("primary") else "" 639 ind_amp = " AMP" if index.args.get("amp") else "" 640 ind_name = f" {index.name}" if index.name else "" 641 ind_columns = ( 642 f' ({self.expressions(index, key="columns", flat=True)})' 643 if index.args.get("columns") 644 else "" 645 ) 646 ind_sql = f"{ind_unique}{ind_primary}{ind_amp} INDEX{ind_name}{ind_columns}" 647 648 if indexes_sql: 649 indexes_sql.append(ind_sql) 650 else: 651 indexes_sql.append( 652 f"{ind_sql}{postindex_props_sql}" 653 if index.args.get("primary") 654 else f"{postindex_props_sql}{ind_sql}" 655 ) 656 657 index_sql = "".join(indexes_sql) 658 else: 659 index_sql = postindex_props_sql 660 661 replace = " OR REPLACE" if expression.args.get("replace") else "" 662 unique = " UNIQUE" if expression.args.get("unique") else "" 663 664 postcreate_props_sql = "" 665 if properties_locs.get(exp.Properties.Location.POST_CREATE): 666 postcreate_props_sql = self.properties( 667 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]), 668 sep=" ", 669 prefix=" ", 670 wrapped=False, 671 ) 672 673 modifiers = "".join((replace, unique, postcreate_props_sql)) 674 675 postexpression_props_sql = "" 676 if properties_locs.get(exp.Properties.Location.POST_EXPRESSION): 677 postexpression_props_sql = self.properties( 678 exp.Properties( 679 expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION] 680 ), 681 sep=" ", 682 prefix=" ", 683 wrapped=False, 684 ) 685 686 exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else "" 687 no_schema_binding = ( 688 " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else "" 689 ) 690 691 expression_sql = f"CREATE{modifiers} {kind}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}" 692 return self.prepend_ctes(expression, expression_sql) 693 694 def describe_sql(self, expression: exp.Describe) -> str: 695 return f"DESCRIBE {self.sql(expression, 'this')}" 696 697 def prepend_ctes(self, expression: exp.Expression, sql: str) -> str: 698 with_ = self.sql(expression, "with") 699 if with_: 700 sql = f"{with_}{self.sep()}{sql}" 701 return sql 702 703 def with_sql(self, expression: exp.With) -> str: 704 sql = self.expressions(expression, flat=True) 705 recursive = "RECURSIVE " if expression.args.get("recursive") else "" 706 707 return f"WITH {recursive}{sql}" 708 709 def cte_sql(self, expression: exp.CTE) -> str: 710 alias = self.sql(expression, "alias") 711 return f"{alias} AS {self.wrap(expression)}" 712 713 def tablealias_sql(self, expression: exp.TableAlias) -> str: 714 alias = self.sql(expression, "this") 715 columns = self.expressions(expression, key="columns", flat=True) 716 columns = f"({columns})" if columns else "" 717 return f"{alias}{columns}" 718 719 def bitstring_sql(self, expression: exp.BitString) -> str: 720 return self.sql(expression, "this") 721 722 def hexstring_sql(self, expression: exp.HexString) -> str: 723 return self.sql(expression, "this") 724 725 def bytestring_sql(self, expression: exp.ByteString) -> str: 726 return self.sql(expression, "this") 727 728 def datatype_sql(self, expression: exp.DataType) -> str: 729 type_value = expression.this 730 type_sql = self.TYPE_MAPPING.get(type_value, type_value.value) 731 nested = "" 732 interior = self.expressions(expression, flat=True) 733 values = "" 734 if interior: 735 if expression.args.get("nested"): 736 nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}" 737 if expression.args.get("values") is not None: 738 delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")") 739 values = f"{delimiters[0]}{self.expressions(expression, key='values')}{delimiters[1]}" 740 else: 741 nested = f"({interior})" 742 743 return f"{type_sql}{nested}{values}" 744 745 def directory_sql(self, expression: exp.Directory) -> str: 746 local = "LOCAL " if expression.args.get("local") else "" 747 row_format = self.sql(expression, "row_format") 748 row_format = f" {row_format}" if row_format else "" 749 return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}" 750 751 def delete_sql(self, expression: exp.Delete) -> str: 752 this = self.sql(expression, "this") 753 this = f" FROM {this}" if this else "" 754 using_sql = ( 755 f" USING {self.expressions(expression, key='using', sep=', USING ')}" 756 if expression.args.get("using") 757 else "" 758 ) 759 where_sql = self.sql(expression, "where") 760 returning = self.sql(expression, "returning") 761 sql = f"DELETE{this}{using_sql}{where_sql}{returning}" 762 return self.prepend_ctes(expression, sql) 763 764 def drop_sql(self, expression: exp.Drop) -> str: 765 this = self.sql(expression, "this") 766 kind = expression.args["kind"] 767 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 768 temporary = " TEMPORARY" if expression.args.get("temporary") else "" 769 materialized = " MATERIALIZED" if expression.args.get("materialized") else "" 770 cascade = " CASCADE" if expression.args.get("cascade") else "" 771 constraints = " CONSTRAINTS" if expression.args.get("constraints") else "" 772 purge = " PURGE" if expression.args.get("purge") else "" 773 return ( 774 f"DROP{temporary}{materialized} {kind}{exists_sql}{this}{cascade}{constraints}{purge}" 775 ) 776 777 def except_sql(self, expression: exp.Except) -> str: 778 return self.prepend_ctes( 779 expression, 780 self.set_operation(expression, self.except_op(expression)), 781 ) 782 783 def except_op(self, expression: exp.Except) -> str: 784 return f"EXCEPT{'' if expression.args.get('distinct') else ' ALL'}" 785 786 def fetch_sql(self, expression: exp.Fetch) -> str: 787 direction = expression.args.get("direction") 788 direction = f" {direction.upper()}" if direction else "" 789 count = expression.args.get("count") 790 count = f" {count}" if count else "" 791 if expression.args.get("percent"): 792 count = f"{count} PERCENT" 793 with_ties_or_only = "WITH TIES" if expression.args.get("with_ties") else "ONLY" 794 return f"{self.seg('FETCH')}{direction}{count} ROWS {with_ties_or_only}" 795 796 def filter_sql(self, expression: exp.Filter) -> str: 797 this = self.sql(expression, "this") 798 where = self.sql(expression, "expression")[1:] # where has a leading space 799 return f"{this} FILTER({where})" 800 801 def hint_sql(self, expression: exp.Hint) -> str: 802 if self.sql(expression, "this"): 803 self.unsupported("Hints are not supported") 804 return "" 805 806 def index_sql(self, expression: exp.Index) -> str: 807 this = self.sql(expression, "this") 808 table = self.sql(expression, "table") 809 columns = self.sql(expression, "columns") 810 return f"{this} ON {table} {columns}" 811 812 def identifier_sql(self, expression: exp.Identifier) -> str: 813 text = expression.name 814 lower = text.lower() 815 text = lower if self.normalize and not expression.quoted else text 816 text = text.replace(self.identifier_end, self._escaped_identifier_end) 817 if ( 818 expression.quoted 819 or should_identify(text, self.identify) 820 or lower in self.RESERVED_KEYWORDS 821 ): 822 text = f"{self.identifier_start}{text}{self.identifier_end}" 823 return text 824 825 def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str: 826 input_format = self.sql(expression, "input_format") 827 input_format = f"INPUTFORMAT {input_format}" if input_format else "" 828 output_format = self.sql(expression, "output_format") 829 output_format = f"OUTPUTFORMAT {output_format}" if output_format else "" 830 return self.sep().join((input_format, output_format)) 831 832 def national_sql(self, expression: exp.National) -> str: 833 return f"N{self.sql(expression, 'this')}" 834 835 def partition_sql(self, expression: exp.Partition) -> str: 836 return f"PARTITION({self.expressions(expression)})" 837 838 def properties_sql(self, expression: exp.Properties) -> str: 839 root_properties = [] 840 with_properties = [] 841 842 for p in expression.expressions: 843 p_loc = self.PROPERTIES_LOCATION[p.__class__] 844 if p_loc == exp.Properties.Location.POST_WITH: 845 with_properties.append(p) 846 elif p_loc == exp.Properties.Location.POST_SCHEMA: 847 root_properties.append(p) 848 849 return self.root_properties( 850 exp.Properties(expressions=root_properties) 851 ) + self.with_properties(exp.Properties(expressions=with_properties)) 852 853 def root_properties(self, properties: exp.Properties) -> str: 854 if properties.expressions: 855 return self.sep() + self.expressions(properties, indent=False, sep=" ") 856 return "" 857 858 def properties( 859 self, 860 properties: exp.Properties, 861 prefix: str = "", 862 sep: str = ", ", 863 suffix: str = "", 864 wrapped: bool = True, 865 ) -> str: 866 if properties.expressions: 867 expressions = self.expressions(properties, sep=sep, indent=False) 868 expressions = self.wrap(expressions) if wrapped else expressions 869 return f"{prefix}{' ' if prefix and prefix != ' ' else ''}{expressions}{suffix}" 870 return "" 871 872 def with_properties(self, properties: exp.Properties) -> str: 873 return self.properties(properties, prefix=self.seg("WITH")) 874 875 def locate_properties( 876 self, properties: exp.Properties 877 ) -> t.Dict[exp.Properties.Location, list[exp.Property]]: 878 properties_locs: t.Dict[exp.Properties.Location, list[exp.Property]] = { 879 key: [] for key in exp.Properties.Location 880 } 881 882 for p in properties.expressions: 883 p_loc = self.PROPERTIES_LOCATION[p.__class__] 884 if p_loc == exp.Properties.Location.POST_NAME: 885 properties_locs[exp.Properties.Location.POST_NAME].append(p) 886 elif p_loc == exp.Properties.Location.POST_INDEX: 887 properties_locs[exp.Properties.Location.POST_INDEX].append(p) 888 elif p_loc == exp.Properties.Location.POST_SCHEMA: 889 properties_locs[exp.Properties.Location.POST_SCHEMA].append(p) 890 elif p_loc == exp.Properties.Location.POST_WITH: 891 properties_locs[exp.Properties.Location.POST_WITH].append(p) 892 elif p_loc == exp.Properties.Location.POST_CREATE: 893 properties_locs[exp.Properties.Location.POST_CREATE].append(p) 894 elif p_loc == exp.Properties.Location.POST_ALIAS: 895 properties_locs[exp.Properties.Location.POST_ALIAS].append(p) 896 elif p_loc == exp.Properties.Location.POST_EXPRESSION: 897 properties_locs[exp.Properties.Location.POST_EXPRESSION].append(p) 898 elif p_loc == exp.Properties.Location.UNSUPPORTED: 899 self.unsupported(f"Unsupported property {p.key}") 900 901 return properties_locs 902 903 def property_sql(self, expression: exp.Property) -> str: 904 property_cls = expression.__class__ 905 if property_cls == exp.Property: 906 return f"{expression.name}={self.sql(expression, 'value')}" 907 908 property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls) 909 if not property_name: 910 self.unsupported(f"Unsupported property {expression.key}") 911 912 return f"{property_name}={self.sql(expression, 'this')}" 913 914 def likeproperty_sql(self, expression: exp.LikeProperty) -> str: 915 options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions) 916 options = f" {options}" if options else "" 917 return f"LIKE {self.sql(expression, 'this')}{options}" 918 919 def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str: 920 no = "NO " if expression.args.get("no") else "" 921 protection = " PROTECTION" if expression.args.get("protection") else "" 922 return f"{no}FALLBACK{protection}" 923 924 def journalproperty_sql(self, expression: exp.JournalProperty) -> str: 925 no = "NO " if expression.args.get("no") else "" 926 dual = "DUAL " if expression.args.get("dual") else "" 927 before = "BEFORE " if expression.args.get("before") else "" 928 return f"{no}{dual}{before}JOURNAL" 929 930 def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str: 931 freespace = self.sql(expression, "this") 932 percent = " PERCENT" if expression.args.get("percent") else "" 933 return f"FREESPACE={freespace}{percent}" 934 935 def afterjournalproperty_sql(self, expression: exp.AfterJournalProperty) -> str: 936 no = "NO " if expression.args.get("no") else "" 937 dual = "DUAL " if expression.args.get("dual") else "" 938 local = "" 939 if expression.args.get("local") is not None: 940 local = "LOCAL " if expression.args.get("local") else "NOT LOCAL " 941 return f"{no}{dual}{local}AFTER JOURNAL" 942 943 def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str: 944 if expression.args.get("default"): 945 property = "DEFAULT" 946 elif expression.args.get("on"): 947 property = "ON" 948 else: 949 property = "OFF" 950 return f"CHECKSUM={property}" 951 952 def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str: 953 if expression.args.get("no"): 954 return "NO MERGEBLOCKRATIO" 955 if expression.args.get("default"): 956 return "DEFAULT MERGEBLOCKRATIO" 957 958 percent = " PERCENT" if expression.args.get("percent") else "" 959 return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}" 960 961 def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str: 962 default = expression.args.get("default") 963 min = expression.args.get("min") 964 if default is not None or min is not None: 965 if default: 966 property = "DEFAULT" 967 elif min: 968 property = "MINIMUM" 969 else: 970 property = "MAXIMUM" 971 return f"{property} DATABLOCKSIZE" 972 else: 973 units = expression.args.get("units") 974 units = f" {units}" if units else "" 975 return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}" 976 977 def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str: 978 autotemp = expression.args.get("autotemp") 979 always = expression.args.get("always") 980 default = expression.args.get("default") 981 manual = expression.args.get("manual") 982 never = expression.args.get("never") 983 984 if autotemp is not None: 985 property = f"AUTOTEMP({self.expressions(autotemp)})" 986 elif always: 987 property = "ALWAYS" 988 elif default: 989 property = "DEFAULT" 990 elif manual: 991 property = "MANUAL" 992 elif never: 993 property = "NEVER" 994 return f"BLOCKCOMPRESSION={property}" 995 996 def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str: 997 no = expression.args.get("no") 998 no = " NO" if no else "" 999 concurrent = expression.args.get("concurrent") 1000 concurrent = " CONCURRENT" if concurrent else "" 1001 1002 for_ = "" 1003 if expression.args.get("for_all"): 1004 for_ = " FOR ALL" 1005 elif expression.args.get("for_insert"): 1006 for_ = " FOR INSERT" 1007 elif expression.args.get("for_none"): 1008 for_ = " FOR NONE" 1009 return f"WITH{no}{concurrent} ISOLATED LOADING{for_}" 1010 1011 def lockingproperty_sql(self, expression: exp.LockingProperty) -> str: 1012 kind = expression.args.get("kind") 1013 this: str = f" {this}" if expression.this else "" 1014 for_or_in = expression.args.get("for_or_in") 1015 lock_type = expression.args.get("lock_type") 1016 override = " OVERRIDE" if expression.args.get("override") else "" 1017 return f"LOCKING {kind}{this} {for_or_in} {lock_type}{override}" 1018 1019 def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str: 1020 data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA" 1021 statistics = expression.args.get("statistics") 1022 statistics_sql = "" 1023 if statistics is not None: 1024 statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS" 1025 return f"{data_sql}{statistics_sql}" 1026 1027 def insert_sql(self, expression: exp.Insert) -> str: 1028 overwrite = expression.args.get("overwrite") 1029 1030 if isinstance(expression.this, exp.Directory): 1031 this = "OVERWRITE " if overwrite else "INTO " 1032 else: 1033 this = "OVERWRITE TABLE " if overwrite else "INTO " 1034 1035 alternative = expression.args.get("alternative") 1036 alternative = f" OR {alternative} " if alternative else " " 1037 this = f"{this}{self.sql(expression, 'this')}" 1038 1039 exists = " IF EXISTS " if expression.args.get("exists") else " " 1040 partition_sql = ( 1041 self.sql(expression, "partition") if expression.args.get("partition") else "" 1042 ) 1043 expression_sql = self.sql(expression, "expression") 1044 conflict = self.sql(expression, "conflict") 1045 returning = self.sql(expression, "returning") 1046 sep = self.sep() if partition_sql else "" 1047 sql = f"INSERT{alternative}{this}{exists}{partition_sql}{sep}{expression_sql}{conflict}{returning}" 1048 return self.prepend_ctes(expression, sql) 1049 1050 def intersect_sql(self, expression: exp.Intersect) -> str: 1051 return self.prepend_ctes( 1052 expression, 1053 self.set_operation(expression, self.intersect_op(expression)), 1054 ) 1055 1056 def intersect_op(self, expression: exp.Intersect) -> str: 1057 return f"INTERSECT{'' if expression.args.get('distinct') else ' ALL'}" 1058 1059 def introducer_sql(self, expression: exp.Introducer) -> str: 1060 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}" 1061 1062 def pseudotype_sql(self, expression: exp.PseudoType) -> str: 1063 return expression.name.upper() 1064 1065 def onconflict_sql(self, expression: exp.OnConflict) -> str: 1066 conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT" 1067 constraint = self.sql(expression, "constraint") 1068 if constraint: 1069 constraint = f"ON CONSTRAINT {constraint}" 1070 key = self.expressions(expression, key="key", flat=True) 1071 do = "" if expression.args.get("duplicate") else " DO " 1072 nothing = "NOTHING" if expression.args.get("nothing") else "" 1073 expressions = self.expressions(expression, flat=True) 1074 if expressions: 1075 expressions = f"UPDATE SET {expressions}" 1076 return f"{self.seg(conflict)} {constraint}{key}{do}{nothing}{expressions}" 1077 1078 def returning_sql(self, expression: exp.Returning) -> str: 1079 return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}" 1080 1081 def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str: 1082 fields = expression.args.get("fields") 1083 fields = f" FIELDS TERMINATED BY {fields}" if fields else "" 1084 escaped = expression.args.get("escaped") 1085 escaped = f" ESCAPED BY {escaped}" if escaped else "" 1086 items = expression.args.get("collection_items") 1087 items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else "" 1088 keys = expression.args.get("map_keys") 1089 keys = f" MAP KEYS TERMINATED BY {keys}" if keys else "" 1090 lines = expression.args.get("lines") 1091 lines = f" LINES TERMINATED BY {lines}" if lines else "" 1092 null = expression.args.get("null") 1093 null = f" NULL DEFINED AS {null}" if null else "" 1094 return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}" 1095 1096 def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str: 1097 table = ".".join( 1098 part 1099 for part in [ 1100 self.sql(expression, "catalog"), 1101 self.sql(expression, "db"), 1102 self.sql(expression, "this"), 1103 ] 1104 if part 1105 ) 1106 1107 alias = self.sql(expression, "alias") 1108 alias = f"{sep}{alias}" if alias else "" 1109 hints = self.expressions(expression, key="hints", sep=", ", flat=True) 1110 hints = f" WITH ({hints})" if hints and self.TABLE_HINTS else "" 1111 laterals = self.expressions(expression, key="laterals", sep="") 1112 joins = self.expressions(expression, key="joins", sep="") 1113 pivots = self.expressions(expression, key="pivots", sep="") 1114 system_time = expression.args.get("system_time") 1115 system_time = f" {self.sql(expression, 'system_time')}" if system_time else "" 1116 1117 return f"{table}{system_time}{alias}{hints}{laterals}{joins}{pivots}" 1118 1119 def tablesample_sql(self, expression: exp.TableSample, seed_prefix: str = "SEED") -> str: 1120 if self.alias_post_tablesample and expression.this.alias: 1121 this = self.sql(expression.this, "this") 1122 alias = f" AS {self.sql(expression.this, 'alias')}" 1123 else: 1124 this = self.sql(expression, "this") 1125 alias = "" 1126 method = self.sql(expression, "method") 1127 method = f"{method.upper()} " if method and self.TABLESAMPLE_WITH_METHOD else "" 1128 numerator = self.sql(expression, "bucket_numerator") 1129 denominator = self.sql(expression, "bucket_denominator") 1130 field = self.sql(expression, "bucket_field") 1131 field = f" ON {field}" if field else "" 1132 bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else "" 1133 percent = self.sql(expression, "percent") 1134 percent = f"{percent} PERCENT" if percent else "" 1135 rows = self.sql(expression, "rows") 1136 rows = f"{rows} ROWS" if rows else "" 1137 size = self.sql(expression, "size") 1138 if size and self.TABLESAMPLE_SIZE_IS_PERCENT: 1139 size = f"{size} PERCENT" 1140 seed = self.sql(expression, "seed") 1141 seed = f" {seed_prefix} ({seed})" if seed else "" 1142 kind = expression.args.get("kind", "TABLESAMPLE") 1143 return f"{this} {kind} {method}({bucket}{percent}{rows}{size}){seed}{alias}" 1144 1145 def pivot_sql(self, expression: exp.Pivot) -> str: 1146 this = self.sql(expression, "this") 1147 alias = self.sql(expression, "alias") 1148 alias = f" AS {alias}" if alias else "" 1149 unpivot = expression.args.get("unpivot") 1150 direction = "UNPIVOT" if unpivot else "PIVOT" 1151 expressions = self.expressions(expression, key="expressions") 1152 field = self.sql(expression, "field") 1153 return f"{this} {direction}({expressions} FOR {field}){alias}" 1154 1155 def tuple_sql(self, expression: exp.Tuple) -> str: 1156 return f"({self.expressions(expression, flat=True)})" 1157 1158 def update_sql(self, expression: exp.Update) -> str: 1159 this = self.sql(expression, "this") 1160 set_sql = self.expressions(expression, flat=True) 1161 from_sql = self.sql(expression, "from") 1162 where_sql = self.sql(expression, "where") 1163 returning = self.sql(expression, "returning") 1164 sql = f"UPDATE {this} SET {set_sql}{from_sql}{where_sql}{returning}" 1165 return self.prepend_ctes(expression, sql) 1166 1167 def values_sql(self, expression: exp.Values) -> str: 1168 args = self.expressions(expression) 1169 alias = self.sql(expression, "alias") 1170 values = f"VALUES{self.seg('')}{args}" 1171 values = ( 1172 f"({values})" 1173 if self.WRAP_DERIVED_VALUES and (alias or isinstance(expression.parent, exp.From)) 1174 else values 1175 ) 1176 return f"{values} AS {alias}" if alias else values 1177 1178 def var_sql(self, expression: exp.Var) -> str: 1179 return self.sql(expression, "this") 1180 1181 def into_sql(self, expression: exp.Into) -> str: 1182 temporary = " TEMPORARY" if expression.args.get("temporary") else "" 1183 unlogged = " UNLOGGED" if expression.args.get("unlogged") else "" 1184 return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}" 1185 1186 def from_sql(self, expression: exp.From) -> str: 1187 expressions = self.expressions(expression, flat=True) 1188 return f"{self.seg('FROM')} {expressions}" 1189 1190 def group_sql(self, expression: exp.Group) -> str: 1191 group_by = self.op_expressions("GROUP BY", expression) 1192 grouping_sets = self.expressions(expression, key="grouping_sets", indent=False) 1193 grouping_sets = ( 1194 f"{self.seg('GROUPING SETS')} {self.wrap(grouping_sets)}" if grouping_sets else "" 1195 ) 1196 1197 cube = expression.args.get("cube", []) 1198 if seq_get(cube, 0) is True: 1199 return f"{group_by}{self.seg('WITH CUBE')}" 1200 else: 1201 cube_sql = self.expressions(expression, key="cube", indent=False) 1202 cube_sql = f"{self.seg('CUBE')} {self.wrap(cube_sql)}" if cube_sql else "" 1203 1204 rollup = expression.args.get("rollup", []) 1205 if seq_get(rollup, 0) is True: 1206 return f"{group_by}{self.seg('WITH ROLLUP')}" 1207 else: 1208 rollup_sql = self.expressions(expression, key="rollup", indent=False) 1209 rollup_sql = f"{self.seg('ROLLUP')} {self.wrap(rollup_sql)}" if rollup_sql else "" 1210 1211 groupings = csv(grouping_sets, cube_sql, rollup_sql, sep=",") 1212 1213 if expression.args.get("expressions") and groupings: 1214 group_by = f"{group_by}," 1215 1216 return f"{group_by}{groupings}" 1217 1218 def having_sql(self, expression: exp.Having) -> str: 1219 this = self.indent(self.sql(expression, "this")) 1220 return f"{self.seg('HAVING')}{self.sep()}{this}" 1221 1222 def join_sql(self, expression: exp.Join) -> str: 1223 op_sql = self.seg( 1224 " ".join( 1225 op 1226 for op in ( 1227 "NATURAL" if expression.args.get("natural") else None, 1228 expression.side, 1229 expression.kind, 1230 expression.hint if self.JOIN_HINTS else None, 1231 "JOIN", 1232 ) 1233 if op 1234 ) 1235 ) 1236 on_sql = self.sql(expression, "on") 1237 using = expression.args.get("using") 1238 1239 if not on_sql and using: 1240 on_sql = csv(*(self.sql(column) for column in using)) 1241 1242 if on_sql: 1243 on_sql = self.indent(on_sql, skip_first=True) 1244 space = self.seg(" " * self.pad) if self.pretty else " " 1245 if using: 1246 on_sql = f"{space}USING ({on_sql})" 1247 else: 1248 on_sql = f"{space}ON {on_sql}" 1249 1250 expression_sql = self.sql(expression, "expression") 1251 this_sql = self.sql(expression, "this") 1252 return f"{expression_sql}{op_sql} {this_sql}{on_sql}" 1253 1254 def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->") -> str: 1255 args = self.expressions(expression, flat=True) 1256 args = f"({args})" if len(args.split(",")) > 1 else args 1257 return f"{args} {arrow_sep} {self.sql(expression, 'this')}" 1258 1259 def lateral_sql(self, expression: exp.Lateral) -> str: 1260 this = self.sql(expression, "this") 1261 1262 if isinstance(expression.this, exp.Subquery): 1263 return f"LATERAL {this}" 1264 1265 if expression.args.get("view"): 1266 alias = expression.args["alias"] 1267 columns = self.expressions(alias, key="columns", flat=True) 1268 table = f" {alias.name}" if alias.name else "" 1269 columns = f" AS {columns}" if columns else "" 1270 op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}") 1271 return f"{op_sql}{self.sep()}{this}{table}{columns}" 1272 1273 alias = self.sql(expression, "alias") 1274 alias = f" AS {alias}" if alias else "" 1275 return f"LATERAL {this}{alias}" 1276 1277 def limit_sql(self, expression: exp.Limit) -> str: 1278 this = self.sql(expression, "this") 1279 return f"{this}{self.seg('LIMIT')} {self.sql(expression, 'expression')}" 1280 1281 def offset_sql(self, expression: exp.Offset) -> str: 1282 this = self.sql(expression, "this") 1283 return f"{this}{self.seg('OFFSET')} {self.sql(expression, 'expression')}" 1284 1285 def setitem_sql(self, expression: exp.SetItem) -> str: 1286 kind = self.sql(expression, "kind") 1287 kind = f"{kind} " if kind else "" 1288 this = self.sql(expression, "this") 1289 expressions = self.expressions(expression) 1290 collate = self.sql(expression, "collate") 1291 collate = f" COLLATE {collate}" if collate else "" 1292 global_ = "GLOBAL " if expression.args.get("global") else "" 1293 return f"{global_}{kind}{this}{expressions}{collate}" 1294 1295 def set_sql(self, expression: exp.Set) -> str: 1296 expressions = ( 1297 f" {self.expressions(expression, flat=True)}" if expression.expressions else "" 1298 ) 1299 return f"SET{expressions}" 1300 1301 def pragma_sql(self, expression: exp.Pragma) -> str: 1302 return f"PRAGMA {self.sql(expression, 'this')}" 1303 1304 def lock_sql(self, expression: exp.Lock) -> str: 1305 if self.LOCKING_READS_SUPPORTED: 1306 lock_type = "UPDATE" if expression.args["update"] else "SHARE" 1307 return self.seg(f"FOR {lock_type}") 1308 1309 self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported") 1310 return "" 1311 1312 def literal_sql(self, expression: exp.Literal) -> str: 1313 text = expression.this or "" 1314 if expression.is_string: 1315 text = text.replace(self.quote_end, self._escaped_quote_end) 1316 if self.pretty: 1317 text = text.replace("\n", self.SENTINEL_LINE_BREAK) 1318 text = f"{self.quote_start}{text}{self.quote_end}" 1319 return text 1320 1321 def loaddata_sql(self, expression: exp.LoadData) -> str: 1322 local = " LOCAL" if expression.args.get("local") else "" 1323 inpath = f" INPATH {self.sql(expression, 'inpath')}" 1324 overwrite = " OVERWRITE" if expression.args.get("overwrite") else "" 1325 this = f" INTO TABLE {self.sql(expression, 'this')}" 1326 partition = self.sql(expression, "partition") 1327 partition = f" {partition}" if partition else "" 1328 input_format = self.sql(expression, "input_format") 1329 input_format = f" INPUTFORMAT {input_format}" if input_format else "" 1330 serde = self.sql(expression, "serde") 1331 serde = f" SERDE {serde}" if serde else "" 1332 return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}" 1333 1334 def null_sql(self, *_) -> str: 1335 return "NULL" 1336 1337 def boolean_sql(self, expression: exp.Boolean) -> str: 1338 return "TRUE" if expression.this else "FALSE" 1339 1340 def order_sql(self, expression: exp.Order, flat: bool = False) -> str: 1341 this = self.sql(expression, "this") 1342 this = f"{this} " if this else this 1343 return self.op_expressions(f"{this}ORDER BY", expression, flat=this or flat) # type: ignore 1344 1345 def cluster_sql(self, expression: exp.Cluster) -> str: 1346 return self.op_expressions("CLUSTER BY", expression) 1347 1348 def distribute_sql(self, expression: exp.Distribute) -> str: 1349 return self.op_expressions("DISTRIBUTE BY", expression) 1350 1351 def sort_sql(self, expression: exp.Sort) -> str: 1352 return self.op_expressions("SORT BY", expression) 1353 1354 def ordered_sql(self, expression: exp.Ordered) -> str: 1355 desc = expression.args.get("desc") 1356 asc = not desc 1357 1358 nulls_first = expression.args.get("nulls_first") 1359 nulls_last = not nulls_first 1360 nulls_are_large = self.null_ordering == "nulls_are_large" 1361 nulls_are_small = self.null_ordering == "nulls_are_small" 1362 nulls_are_last = self.null_ordering == "nulls_are_last" 1363 1364 sort_order = " DESC" if desc else "" 1365 nulls_sort_change = "" 1366 if nulls_first and ( 1367 (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last 1368 ): 1369 nulls_sort_change = " NULLS FIRST" 1370 elif ( 1371 nulls_last 1372 and ((asc and nulls_are_small) or (desc and nulls_are_large)) 1373 and not nulls_are_last 1374 ): 1375 nulls_sort_change = " NULLS LAST" 1376 1377 if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED: 1378 self.unsupported( 1379 "Sorting in an ORDER BY on NULLS FIRST/NULLS LAST is not supported by this dialect" 1380 ) 1381 nulls_sort_change = "" 1382 1383 return f"{self.sql(expression, 'this')}{sort_order}{nulls_sort_change}" 1384 1385 def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str: 1386 partition = self.partition_by_sql(expression) 1387 order = self.sql(expression, "order") 1388 measures = self.expressions(expression, key="measures") 1389 measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else "" 1390 rows = self.sql(expression, "rows") 1391 rows = self.seg(rows) if rows else "" 1392 after = self.sql(expression, "after") 1393 after = self.seg(after) if after else "" 1394 pattern = self.sql(expression, "pattern") 1395 pattern = self.seg(f"PATTERN ({pattern})") if pattern else "" 1396 definition_sqls = [ 1397 f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}" 1398 for definition in expression.args.get("define", []) 1399 ] 1400 definitions = self.expressions(sqls=definition_sqls) 1401 define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else "" 1402 body = "".join( 1403 ( 1404 partition, 1405 order, 1406 measures, 1407 rows, 1408 after, 1409 pattern, 1410 define, 1411 ) 1412 ) 1413 alias = self.sql(expression, "alias") 1414 alias = f" {alias}" if alias else "" 1415 return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}" 1416 1417 def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str: 1418 limit = expression.args.get("limit") 1419 1420 if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch): 1421 limit = exp.Limit(expression=limit.args.get("count")) 1422 elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit): 1423 limit = exp.Fetch(direction="FIRST", count=limit.expression) 1424 1425 fetch = isinstance(limit, exp.Fetch) 1426 1427 return csv( 1428 *sqls, 1429 *[self.sql(sql) for sql in expression.args.get("joins") or []], 1430 self.sql(expression, "match"), 1431 *[self.sql(sql) for sql in expression.args.get("laterals") or []], 1432 self.sql(expression, "where"), 1433 self.sql(expression, "group"), 1434 self.sql(expression, "having"), 1435 self.sql(expression, "qualify"), 1436 self.seg("WINDOW ") + self.expressions(expression, key="windows", flat=True) 1437 if expression.args.get("windows") 1438 else "", 1439 self.sql(expression, "distribute"), 1440 self.sql(expression, "sort"), 1441 self.sql(expression, "cluster"), 1442 self.sql(expression, "order"), 1443 self.sql(expression, "offset") if fetch else self.sql(limit), 1444 self.sql(limit) if fetch else self.sql(expression, "offset"), 1445 self.sql(expression, "lock"), 1446 self.sql(expression, "sample"), 1447 sep="", 1448 ) 1449 1450 def select_sql(self, expression: exp.Select) -> str: 1451 kind = expression.args.get("kind") 1452 kind = f" AS {kind}" if kind else "" 1453 hint = self.sql(expression, "hint") 1454 distinct = self.sql(expression, "distinct") 1455 distinct = f" {distinct}" if distinct else "" 1456 expressions = self.expressions(expression) 1457 expressions = f"{self.sep()}{expressions}" if expressions else expressions 1458 sql = self.query_modifiers( 1459 expression, 1460 f"SELECT{kind}{hint}{distinct}{expressions}", 1461 self.sql(expression, "into", comment=False), 1462 self.sql(expression, "from", comment=False), 1463 ) 1464 return self.prepend_ctes(expression, sql) 1465 1466 def schema_sql(self, expression: exp.Schema) -> str: 1467 this = self.sql(expression, "this") 1468 this = f"{this} " if this else "" 1469 sql = f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}" 1470 return f"{this}{sql}" 1471 1472 def star_sql(self, expression: exp.Star) -> str: 1473 except_ = self.expressions(expression, key="except", flat=True) 1474 except_ = f"{self.seg(self.STAR_MAPPING['except'])} ({except_})" if except_ else "" 1475 replace = self.expressions(expression, key="replace", flat=True) 1476 replace = f"{self.seg(self.STAR_MAPPING['replace'])} ({replace})" if replace else "" 1477 return f"*{except_}{replace}" 1478 1479 def structkwarg_sql(self, expression: exp.StructKwarg) -> str: 1480 return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}" 1481 1482 def parameter_sql(self, expression: exp.Parameter) -> str: 1483 this = self.sql(expression, "this") 1484 this = f"{{{this}}}" if expression.args.get("wrapped") else f"{this}" 1485 return f"{self.PARAMETER_TOKEN}{this}" 1486 1487 def sessionparameter_sql(self, expression: exp.SessionParameter) -> str: 1488 this = self.sql(expression, "this") 1489 kind = expression.text("kind") 1490 if kind: 1491 kind = f"{kind}." 1492 return f"@@{kind}{this}" 1493 1494 def placeholder_sql(self, expression: exp.Placeholder) -> str: 1495 return f":{expression.name}" if expression.name else "?" 1496 1497 def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str: 1498 alias = self.sql(expression, "alias") 1499 alias = f"{sep}{alias}" if alias else "" 1500 1501 sql = self.query_modifiers( 1502 expression, 1503 self.wrap(expression), 1504 alias, 1505 self.expressions(expression, key="pivots", sep=" "), 1506 ) 1507 1508 return self.prepend_ctes(expression, sql) 1509 1510 def qualify_sql(self, expression: exp.Qualify) -> str: 1511 this = self.indent(self.sql(expression, "this")) 1512 return f"{self.seg('QUALIFY')}{self.sep()}{this}" 1513 1514 def union_sql(self, expression: exp.Union) -> str: 1515 return self.prepend_ctes( 1516 expression, 1517 self.set_operation(expression, self.union_op(expression)), 1518 ) 1519 1520 def union_op(self, expression: exp.Union) -> str: 1521 kind = " DISTINCT" if self.EXPLICIT_UNION else "" 1522 kind = kind if expression.args.get("distinct") else " ALL" 1523 return f"UNION{kind}" 1524 1525 def unnest_sql(self, expression: exp.Unnest) -> str: 1526 args = self.expressions(expression, flat=True) 1527 alias = expression.args.get("alias") 1528 if alias and self.unnest_column_only: 1529 columns = alias.columns 1530 alias = self.sql(columns[0]) if columns else "" 1531 else: 1532 alias = self.sql(expression, "alias") 1533 alias = f" AS {alias}" if alias else alias 1534 ordinality = " WITH ORDINALITY" if expression.args.get("ordinality") else "" 1535 offset = expression.args.get("offset") 1536 offset = f" WITH OFFSET AS {self.sql(offset)}" if offset else "" 1537 return f"UNNEST({args}){ordinality}{alias}{offset}" 1538 1539 def where_sql(self, expression: exp.Where) -> str: 1540 this = self.indent(self.sql(expression, "this")) 1541 return f"{self.seg('WHERE')}{self.sep()}{this}" 1542 1543 def window_sql(self, expression: exp.Window) -> str: 1544 this = self.sql(expression, "this") 1545 1546 partition = self.partition_by_sql(expression) 1547 1548 order = expression.args.get("order") 1549 order_sql = self.order_sql(order, flat=True) if order else "" 1550 1551 partition_sql = partition + " " if partition and order else partition 1552 1553 spec = expression.args.get("spec") 1554 spec_sql = " " + self.window_spec_sql(spec) if spec else "" 1555 1556 alias = self.sql(expression, "alias") 1557 this = f"{this} {'AS' if expression.arg_key == 'windows' else 'OVER'}" 1558 1559 if not partition and not order and not spec and alias: 1560 return f"{this} {alias}" 1561 1562 window_args = alias + partition_sql + order_sql + spec_sql 1563 1564 return f"{this} ({window_args.strip()})" 1565 1566 def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str: 1567 partition = self.expressions(expression, key="partition_by", flat=True) 1568 return f"PARTITION BY {partition}" if partition else "" 1569 1570 def window_spec_sql(self, expression: exp.WindowSpec) -> str: 1571 kind = self.sql(expression, "kind") 1572 start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ") 1573 end = ( 1574 csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ") 1575 or "CURRENT ROW" 1576 ) 1577 return f"{kind} BETWEEN {start} AND {end}" 1578 1579 def withingroup_sql(self, expression: exp.WithinGroup) -> str: 1580 this = self.sql(expression, "this") 1581 expression_sql = self.sql(expression, "expression")[1:] # order has a leading space 1582 return f"{this} WITHIN GROUP ({expression_sql})" 1583 1584 def between_sql(self, expression: exp.Between) -> str: 1585 this = self.sql(expression, "this") 1586 low = self.sql(expression, "low") 1587 high = self.sql(expression, "high") 1588 return f"{this} BETWEEN {low} AND {high}" 1589 1590 def bracket_sql(self, expression: exp.Bracket) -> str: 1591 expressions = apply_index_offset(expression.this, expression.expressions, self.index_offset) 1592 expressions_sql = ", ".join(self.sql(e) for e in expressions) 1593 1594 return f"{self.sql(expression, 'this')}[{expressions_sql}]" 1595 1596 def all_sql(self, expression: exp.All) -> str: 1597 return f"ALL {self.wrap(expression)}" 1598 1599 def any_sql(self, expression: exp.Any) -> str: 1600 this = self.sql(expression, "this") 1601 if isinstance(expression.this, exp.Subqueryable): 1602 this = self.wrap(this) 1603 return f"ANY {this}" 1604 1605 def exists_sql(self, expression: exp.Exists) -> str: 1606 return f"EXISTS{self.wrap(expression)}" 1607 1608 def case_sql(self, expression: exp.Case) -> str: 1609 this = self.sql(expression, "this") 1610 statements = [f"CASE {this}" if this else "CASE"] 1611 1612 for e in expression.args["ifs"]: 1613 statements.append(f"WHEN {self.sql(e, 'this')}") 1614 statements.append(f"THEN {self.sql(e, 'true')}") 1615 1616 default = self.sql(expression, "default") 1617 1618 if default: 1619 statements.append(f"ELSE {default}") 1620 1621 statements.append("END") 1622 1623 if self.pretty and self.text_width(statements) > self._max_text_width: 1624 return self.indent("\n".join(statements), skip_first=True, skip_last=True) 1625 1626 return " ".join(statements) 1627 1628 def constraint_sql(self, expression: exp.Constraint) -> str: 1629 this = self.sql(expression, "this") 1630 expressions = self.expressions(expression, flat=True) 1631 return f"CONSTRAINT {this} {expressions}" 1632 1633 def extract_sql(self, expression: exp.Extract) -> str: 1634 this = self.sql(expression, "this") 1635 expression_sql = self.sql(expression, "expression") 1636 return f"EXTRACT({this} FROM {expression_sql})" 1637 1638 def trim_sql(self, expression: exp.Trim) -> str: 1639 trim_type = self.sql(expression, "position") 1640 1641 if trim_type == "LEADING": 1642 return self.func("LTRIM", expression.this) 1643 elif trim_type == "TRAILING": 1644 return self.func("RTRIM", expression.this) 1645 else: 1646 return self.func("TRIM", expression.this, expression.expression) 1647 1648 def concat_sql(self, expression: exp.Concat) -> str: 1649 if len(expression.expressions) == 1: 1650 return self.sql(expression.expressions[0]) 1651 return self.function_fallback_sql(expression) 1652 1653 def check_sql(self, expression: exp.Check) -> str: 1654 this = self.sql(expression, key="this") 1655 return f"CHECK ({this})" 1656 1657 def foreignkey_sql(self, expression: exp.ForeignKey) -> str: 1658 expressions = self.expressions(expression, flat=True) 1659 reference = self.sql(expression, "reference") 1660 reference = f" {reference}" if reference else "" 1661 delete = self.sql(expression, "delete") 1662 delete = f" ON DELETE {delete}" if delete else "" 1663 update = self.sql(expression, "update") 1664 update = f" ON UPDATE {update}" if update else "" 1665 return f"FOREIGN KEY ({expressions}){reference}{delete}{update}" 1666 1667 def primarykey_sql(self, expression: exp.ForeignKey) -> str: 1668 expressions = self.expressions(expression, flat=True) 1669 options = self.expressions(expression, key="options", flat=True, sep=" ") 1670 options = f" {options}" if options else "" 1671 return f"PRIMARY KEY ({expressions}){options}" 1672 1673 def unique_sql(self, expression: exp.Unique) -> str: 1674 columns = self.expressions(expression, key="expressions") 1675 return f"UNIQUE ({columns})" 1676 1677 def if_sql(self, expression: exp.If) -> str: 1678 return self.case_sql( 1679 exp.Case(ifs=[expression.copy()], default=expression.args.get("false")) 1680 ) 1681 1682 def matchagainst_sql(self, expression: exp.MatchAgainst) -> str: 1683 modifier = expression.args.get("modifier") 1684 modifier = f" {modifier}" if modifier else "" 1685 return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})" 1686 1687 def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str: 1688 return f"{self.sql(expression, 'this')}: {self.sql(expression, 'expression')}" 1689 1690 def jsonobject_sql(self, expression: exp.JSONObject) -> str: 1691 expressions = self.expressions(expression) 1692 null_handling = expression.args.get("null_handling") 1693 null_handling = f" {null_handling}" if null_handling else "" 1694 unique_keys = expression.args.get("unique_keys") 1695 if unique_keys is not None: 1696 unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS" 1697 else: 1698 unique_keys = "" 1699 return_type = self.sql(expression, "return_type") 1700 return_type = f" RETURNING {return_type}" if return_type else "" 1701 format_json = " FORMAT JSON" if expression.args.get("format_json") else "" 1702 encoding = self.sql(expression, "encoding") 1703 encoding = f" ENCODING {encoding}" if encoding else "" 1704 return f"JSON_OBJECT({expressions}{null_handling}{unique_keys}{return_type}{format_json}{encoding})" 1705 1706 def in_sql(self, expression: exp.In) -> str: 1707 query = expression.args.get("query") 1708 unnest = expression.args.get("unnest") 1709 field = expression.args.get("field") 1710 is_global = " GLOBAL" if expression.args.get("is_global") else "" 1711 1712 if query: 1713 in_sql = self.wrap(query) 1714 elif unnest: 1715 in_sql = self.in_unnest_op(unnest) 1716 elif field: 1717 in_sql = self.sql(field) 1718 else: 1719 in_sql = f"({self.expressions(expression, flat=True)})" 1720 1721 return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}" 1722 1723 def in_unnest_op(self, unnest: exp.Unnest) -> str: 1724 return f"(SELECT {self.sql(unnest)})" 1725 1726 def interval_sql(self, expression: exp.Interval) -> str: 1727 unit = self.sql(expression, "unit") 1728 if not self.INTERVAL_ALLOWS_PLURAL_FORM: 1729 unit = self.TIME_PART_SINGULARS.get(unit.lower(), unit) 1730 unit = f" {unit}" if unit else "" 1731 1732 if self.SINGLE_STRING_INTERVAL: 1733 this = expression.this.name if expression.this else "" 1734 return f"INTERVAL '{this}{unit}'" 1735 1736 this = self.sql(expression, "this") 1737 if this: 1738 unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES) 1739 this = f" {this}" if unwrapped else f" ({this})" 1740 1741 return f"INTERVAL{this}{unit}" 1742 1743 def return_sql(self, expression: exp.Return) -> str: 1744 return f"RETURN {self.sql(expression, 'this')}" 1745 1746 def reference_sql(self, expression: exp.Reference) -> str: 1747 this = self.sql(expression, "this") 1748 expressions = self.expressions(expression, flat=True) 1749 expressions = f"({expressions})" if expressions else "" 1750 options = self.expressions(expression, key="options", flat=True, sep=" ") 1751 options = f" {options}" if options else "" 1752 return f"REFERENCES {this}{expressions}{options}" 1753 1754 def anonymous_sql(self, expression: exp.Anonymous) -> str: 1755 return self.func(expression.name, *expression.expressions) 1756 1757 def paren_sql(self, expression: exp.Paren) -> str: 1758 if isinstance(expression.unnest(), exp.Select): 1759 sql = self.wrap(expression) 1760 else: 1761 sql = self.seg(self.indent(self.sql(expression, "this")), sep="") 1762 sql = f"({sql}{self.seg(')', sep='')}" 1763 1764 return self.prepend_ctes(expression, sql) 1765 1766 def neg_sql(self, expression: exp.Neg) -> str: 1767 # This makes sure we don't convert "- - 5" to "--5", which is a comment 1768 this_sql = self.sql(expression, "this") 1769 sep = " " if this_sql[0] == "-" else "" 1770 return f"-{sep}{this_sql}" 1771 1772 def not_sql(self, expression: exp.Not) -> str: 1773 return f"NOT {self.sql(expression, 'this')}" 1774 1775 def alias_sql(self, expression: exp.Alias) -> str: 1776 alias = self.sql(expression, "alias") 1777 alias = f" AS {alias}" if alias else "" 1778 return f"{self.sql(expression, 'this')}{alias}" 1779 1780 def aliases_sql(self, expression: exp.Aliases) -> str: 1781 return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})" 1782 1783 def attimezone_sql(self, expression: exp.AtTimeZone) -> str: 1784 this = self.sql(expression, "this") 1785 zone = self.sql(expression, "zone") 1786 return f"{this} AT TIME ZONE {zone}" 1787 1788 def add_sql(self, expression: exp.Add) -> str: 1789 return self.binary(expression, "+") 1790 1791 def and_sql(self, expression: exp.And) -> str: 1792 return self.connector_sql(expression, "AND") 1793 1794 def connector_sql(self, expression: exp.Connector, op: str) -> str: 1795 if not self.pretty: 1796 return self.binary(expression, op) 1797 1798 sqls = tuple( 1799 self.maybe_comment(self.sql(e), e, e.parent.comments) if i != 1 else self.sql(e) 1800 for i, e in enumerate(expression.flatten(unnest=False)) 1801 ) 1802 1803 sep = "\n" if self.text_width(sqls) > self._max_text_width else " " 1804 return f"{sep}{op} ".join(sqls) 1805 1806 def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str: 1807 return self.binary(expression, "&") 1808 1809 def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str: 1810 return self.binary(expression, "<<") 1811 1812 def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str: 1813 return f"~{self.sql(expression, 'this')}" 1814 1815 def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str: 1816 return self.binary(expression, "|") 1817 1818 def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str: 1819 return self.binary(expression, ">>") 1820 1821 def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str: 1822 return self.binary(expression, "^") 1823 1824 def cast_sql(self, expression: exp.Cast) -> str: 1825 return f"CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')})" 1826 1827 def currentdate_sql(self, expression: exp.CurrentDate) -> str: 1828 zone = self.sql(expression, "this") 1829 return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE" 1830 1831 def collate_sql(self, expression: exp.Collate) -> str: 1832 return self.binary(expression, "COLLATE") 1833 1834 def command_sql(self, expression: exp.Command) -> str: 1835 return f"{self.sql(expression, 'this').upper()} {expression.text('expression').strip()}" 1836 1837 def comment_sql(self, expression: exp.Comment) -> str: 1838 this = self.sql(expression, "this") 1839 kind = expression.args["kind"] 1840 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 1841 expression_sql = self.sql(expression, "expression") 1842 return f"COMMENT{exists_sql}ON {kind} {this} IS {expression_sql}" 1843 1844 def transaction_sql(self, expression: exp.Transaction) -> str: 1845 return "BEGIN" 1846 1847 def commit_sql(self, expression: exp.Commit) -> str: 1848 chain = expression.args.get("chain") 1849 if chain is not None: 1850 chain = " AND CHAIN" if chain else " AND NO CHAIN" 1851 1852 return f"COMMIT{chain or ''}" 1853 1854 def rollback_sql(self, expression: exp.Rollback) -> str: 1855 savepoint = expression.args.get("savepoint") 1856 savepoint = f" TO {savepoint}" if savepoint else "" 1857 return f"ROLLBACK{savepoint}" 1858 1859 def altercolumn_sql(self, expression: exp.AlterColumn) -> str: 1860 this = self.sql(expression, "this") 1861 1862 dtype = self.sql(expression, "dtype") 1863 if dtype: 1864 collate = self.sql(expression, "collate") 1865 collate = f" COLLATE {collate}" if collate else "" 1866 using = self.sql(expression, "using") 1867 using = f" USING {using}" if using else "" 1868 return f"ALTER COLUMN {this} TYPE {dtype}{collate}{using}" 1869 1870 default = self.sql(expression, "default") 1871 if default: 1872 return f"ALTER COLUMN {this} SET DEFAULT {default}" 1873 1874 if not expression.args.get("drop"): 1875 self.unsupported("Unsupported ALTER COLUMN syntax") 1876 1877 return f"ALTER COLUMN {this} DROP DEFAULT" 1878 1879 def renametable_sql(self, expression: exp.RenameTable) -> str: 1880 this = self.sql(expression, "this") 1881 return f"RENAME TO {this}" 1882 1883 def altertable_sql(self, expression: exp.AlterTable) -> str: 1884 actions = expression.args["actions"] 1885 1886 if isinstance(actions[0], exp.ColumnDef): 1887 actions = self.expressions(expression, key="actions", prefix="ADD COLUMN ") 1888 elif isinstance(actions[0], exp.Schema): 1889 actions = self.expressions(expression, key="actions", prefix="ADD COLUMNS ") 1890 elif isinstance(actions[0], exp.Delete): 1891 actions = self.expressions(expression, key="actions", flat=True) 1892 else: 1893 actions = self.expressions(expression, key="actions") 1894 1895 exists = " IF EXISTS" if expression.args.get("exists") else "" 1896 return f"ALTER TABLE{exists} {self.sql(expression, 'this')} {actions}" 1897 1898 def droppartition_sql(self, expression: exp.DropPartition) -> str: 1899 expressions = self.expressions(expression) 1900 exists = " IF EXISTS " if expression.args.get("exists") else " " 1901 return f"DROP{exists}{expressions}" 1902 1903 def addconstraint_sql(self, expression: exp.AddConstraint) -> str: 1904 this = self.sql(expression, "this") 1905 expression_ = self.sql(expression, "expression") 1906 add_constraint = f"ADD CONSTRAINT {this}" if this else "ADD" 1907 1908 enforced = expression.args.get("enforced") 1909 if enforced is not None: 1910 return f"{add_constraint} CHECK ({expression_}){' ENFORCED' if enforced else ''}" 1911 1912 return f"{add_constraint} {expression_}" 1913 1914 def distinct_sql(self, expression: exp.Distinct) -> str: 1915 this = self.expressions(expression, flat=True) 1916 this = f" {this}" if this else "" 1917 1918 on = self.sql(expression, "on") 1919 on = f" ON {on}" if on else "" 1920 return f"DISTINCT{this}{on}" 1921 1922 def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str: 1923 return f"{self.sql(expression, 'this')} IGNORE NULLS" 1924 1925 def respectnulls_sql(self, expression: exp.RespectNulls) -> str: 1926 return f"{self.sql(expression, 'this')} RESPECT NULLS" 1927 1928 def intdiv_sql(self, expression: exp.IntDiv) -> str: 1929 return self.sql( 1930 exp.Cast( 1931 this=exp.Div(this=expression.this, expression=expression.expression), 1932 to=exp.DataType(this=exp.DataType.Type.INT), 1933 ) 1934 ) 1935 1936 def dpipe_sql(self, expression: exp.DPipe) -> str: 1937 return self.binary(expression, "||") 1938 1939 def div_sql(self, expression: exp.Div) -> str: 1940 return self.binary(expression, "/") 1941 1942 def overlaps_sql(self, expression: exp.Overlaps) -> str: 1943 return self.binary(expression, "OVERLAPS") 1944 1945 def distance_sql(self, expression: exp.Distance) -> str: 1946 return self.binary(expression, "<->") 1947 1948 def dot_sql(self, expression: exp.Dot) -> str: 1949 return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}" 1950 1951 def eq_sql(self, expression: exp.EQ) -> str: 1952 return self.binary(expression, "=") 1953 1954 def escape_sql(self, expression: exp.Escape) -> str: 1955 return self.binary(expression, "ESCAPE") 1956 1957 def glob_sql(self, expression: exp.Glob) -> str: 1958 return self.binary(expression, "GLOB") 1959 1960 def gt_sql(self, expression: exp.GT) -> str: 1961 return self.binary(expression, ">") 1962 1963 def gte_sql(self, expression: exp.GTE) -> str: 1964 return self.binary(expression, ">=") 1965 1966 def ilike_sql(self, expression: exp.ILike) -> str: 1967 return self.binary(expression, "ILIKE") 1968 1969 def ilikeany_sql(self, expression: exp.ILikeAny) -> str: 1970 return self.binary(expression, "ILIKE ANY") 1971 1972 def is_sql(self, expression: exp.Is) -> str: 1973 return self.binary(expression, "IS") 1974 1975 def like_sql(self, expression: exp.Like) -> str: 1976 return self.binary(expression, "LIKE") 1977 1978 def likeany_sql(self, expression: exp.LikeAny) -> str: 1979 return self.binary(expression, "LIKE ANY") 1980 1981 def similarto_sql(self, expression: exp.SimilarTo) -> str: 1982 return self.binary(expression, "SIMILAR TO") 1983 1984 def lt_sql(self, expression: exp.LT) -> str: 1985 return self.binary(expression, "<") 1986 1987 def lte_sql(self, expression: exp.LTE) -> str: 1988 return self.binary(expression, "<=") 1989 1990 def mod_sql(self, expression: exp.Mod) -> str: 1991 return self.binary(expression, "%") 1992 1993 def mul_sql(self, expression: exp.Mul) -> str: 1994 return self.binary(expression, "*") 1995 1996 def neq_sql(self, expression: exp.NEQ) -> str: 1997 return self.binary(expression, "<>") 1998 1999 def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str: 2000 return self.binary(expression, "IS NOT DISTINCT FROM") 2001 2002 def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str: 2003 return self.binary(expression, "IS DISTINCT FROM") 2004 2005 def or_sql(self, expression: exp.Or) -> str: 2006 return self.connector_sql(expression, "OR") 2007 2008 def slice_sql(self, expression: exp.Slice) -> str: 2009 return self.binary(expression, ":") 2010 2011 def sub_sql(self, expression: exp.Sub) -> str: 2012 return self.binary(expression, "-") 2013 2014 def trycast_sql(self, expression: exp.TryCast) -> str: 2015 return f"TRY_CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')})" 2016 2017 def use_sql(self, expression: exp.Use) -> str: 2018 kind = self.sql(expression, "kind") 2019 kind = f" {kind}" if kind else "" 2020 this = self.sql(expression, "this") 2021 this = f" {this}" if this else "" 2022 return f"USE{kind}{this}" 2023 2024 def binary(self, expression: exp.Binary, op: str) -> str: 2025 op = self.maybe_comment(op, comments=expression.comments) 2026 return f"{self.sql(expression, 'this')} {op} {self.sql(expression, 'expression')}" 2027 2028 def function_fallback_sql(self, expression: exp.Func) -> str: 2029 args = [] 2030 for arg_value in expression.args.values(): 2031 if isinstance(arg_value, list): 2032 for value in arg_value: 2033 args.append(value) 2034 else: 2035 args.append(arg_value) 2036 2037 return self.func(expression.sql_name(), *args) 2038 2039 def func(self, name: str, *args: t.Optional[exp.Expression | str]) -> str: 2040 return f"{self.normalize_func(name)}({self.format_args(*args)})" 2041 2042 def format_args(self, *args: t.Optional[str | exp.Expression]) -> str: 2043 arg_sqls = tuple(self.sql(arg) for arg in args if arg is not None) 2044 if self.pretty and self.text_width(arg_sqls) > self._max_text_width: 2045 return self.indent("\n" + f",\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True) 2046 return ", ".join(arg_sqls) 2047 2048 def text_width(self, args: t.Iterable) -> int: 2049 return sum(len(arg) for arg in args) 2050 2051 def format_time(self, expression: exp.Expression) -> t.Optional[str]: 2052 return format_time(self.sql(expression, "format"), self.time_mapping, self.time_trie) 2053 2054 def expressions( 2055 self, 2056 expression: t.Optional[exp.Expression] = None, 2057 key: t.Optional[str] = None, 2058 sqls: t.Optional[t.List[str]] = None, 2059 flat: bool = False, 2060 indent: bool = True, 2061 sep: str = ", ", 2062 prefix: str = "", 2063 ) -> str: 2064 expressions = expression.args.get(key or "expressions") if expression else sqls 2065 2066 if not expressions: 2067 return "" 2068 2069 if flat: 2070 return sep.join(self.sql(e) for e in expressions) 2071 2072 num_sqls = len(expressions) 2073 2074 # These are calculated once in case we have the leading_comma / pretty option set, correspondingly 2075 pad = " " * self.pad 2076 stripped_sep = sep.strip() 2077 2078 result_sqls = [] 2079 for i, e in enumerate(expressions): 2080 sql = self.sql(e, comment=False) 2081 comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else "" 2082 2083 if self.pretty: 2084 if self._leading_comma: 2085 result_sqls.append(f"{sep if i > 0 else pad}{prefix}{sql}{comments}") 2086 else: 2087 result_sqls.append( 2088 f"{prefix}{sql}{stripped_sep if i + 1 < num_sqls else ''}{comments}" 2089 ) 2090 else: 2091 result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}") 2092 2093 result_sql = "\n".join(result_sqls) if self.pretty else "".join(result_sqls) 2094 return self.indent(result_sql, skip_first=False) if indent else result_sql 2095 2096 def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str: 2097 flat = flat or isinstance(expression.parent, exp.Properties) 2098 expressions_sql = self.expressions(expression, flat=flat) 2099 if flat: 2100 return f"{op} {expressions_sql}" 2101 return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}" 2102 2103 def naked_property(self, expression: exp.Property) -> str: 2104 property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__) 2105 if not property_name: 2106 self.unsupported(f"Unsupported property {expression.__class__.__name__}") 2107 return f"{property_name} {self.sql(expression, 'this')}" 2108 2109 def set_operation(self, expression: exp.Expression, op: str) -> str: 2110 this = self.sql(expression, "this") 2111 op = self.seg(op) 2112 return self.query_modifiers( 2113 expression, f"{this}{op}{self.sep()}{self.sql(expression, 'expression')}" 2114 ) 2115 2116 def tag_sql(self, expression: exp.Tag) -> str: 2117 return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}" 2118 2119 def token_sql(self, token_type: TokenType) -> str: 2120 return self.TOKEN_MAPPING.get(token_type, token_type.name) 2121 2122 def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str: 2123 this = self.sql(expression, "this") 2124 expressions = self.no_identify(self.expressions, expression) 2125 expressions = ( 2126 self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}" 2127 ) 2128 return f"{this}{expressions}" 2129 2130 def joinhint_sql(self, expression: exp.JoinHint) -> str: 2131 this = self.sql(expression, "this") 2132 expressions = self.expressions(expression, flat=True) 2133 return f"{this}({expressions})" 2134 2135 def kwarg_sql(self, expression: exp.Kwarg) -> str: 2136 return self.binary(expression, "=>") 2137 2138 def when_sql(self, expression: exp.When) -> str: 2139 matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED" 2140 source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else "" 2141 condition = self.sql(expression, "condition") 2142 condition = f" AND {condition}" if condition else "" 2143 2144 then_expression = expression.args.get("then") 2145 if isinstance(then_expression, exp.Insert): 2146 then = f"INSERT {self.sql(then_expression, 'this')}" 2147 if "expression" in then_expression.args: 2148 then += f" VALUES {self.sql(then_expression, 'expression')}" 2149 elif isinstance(then_expression, exp.Update): 2150 if isinstance(then_expression.args.get("expressions"), exp.Star): 2151 then = f"UPDATE {self.sql(then_expression, 'expressions')}" 2152 else: 2153 then = f"UPDATE SET {self.expressions(then_expression, flat=True)}" 2154 else: 2155 then = self.sql(then_expression) 2156 return f"WHEN {matched}{source}{condition} THEN {then}" 2157 2158 def merge_sql(self, expression: exp.Merge) -> str: 2159 this = self.sql(expression, "this") 2160 using = f"USING {self.sql(expression, 'using')}" 2161 on = f"ON {self.sql(expression, 'on')}" 2162 return f"MERGE INTO {this} {using} {on} {self.expressions(expression, sep=' ')}" 2163 2164 def tochar_sql(self, expression: exp.ToChar) -> str: 2165 if expression.args.get("format"): 2166 self.unsupported("Format argument unsupported for TO_CHAR/TO_VARCHAR function") 2167 2168 return self.sql(exp.cast(expression.this, "text"))
Generator interprets the given syntax tree and produces a SQL string as an output.
Arguments:
- time_mapping (dict): the dictionary of custom time mappings in which the key represents a python time format and the output the target time format
- time_trie (trie): a trie of the time_mapping keys
- pretty (bool): if set to True the returned string will be formatted. Default: False.
- quote_start (str): specifies which starting character to use to delimit quotes. Default: '.
- quote_end (str): specifies which ending character to use to delimit quotes. Default: '.
- identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
- identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
- identify (bool | str): 'always': always quote, 'safe': quote identifiers if they don't contain an upcase, True defaults to always.
- normalize (bool): if set to True all identifiers will lower cased
- string_escape (str): specifies a string escape character. Default: '.
- identifier_escape (str): specifies an identifier escape character. Default: ".
- pad (int): determines padding in a formatted string. Default: 2.
- indent (int): determines the size of indentation in a formatted string. Default: 4.
- unnest_column_only (bool): if true unnest table aliases are considered only as column aliases
- normalize_functions (str): normalize function names, "upper", "lower", or None Default: "upper"
- alias_post_tablesample (bool): if the table alias comes after tablesample Default: False
- unsupported_level (ErrorLevel): determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- null_ordering (str): Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
- max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
- leading_comma (bool): if the the comma is leading or trailing in select statements Default: False
- max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
- comments: Whether or not to preserve comments in the output SQL code. Default: True
Generator( time_mapping=None, time_trie=None, pretty=None, quote_start=None, quote_end=None, identifier_start=None, identifier_end=None, identify=False, normalize=False, string_escape=None, identifier_escape=None, pad=2, indent=2, index_offset=0, unnest_column_only=False, alias_post_tablesample=False, normalize_functions='upper', unsupported_level=<ErrorLevel.WARN: 'WARN'>, null_ordering=None, max_unsupported=3, leading_comma=False, max_text_width=80, comments=True)
253 def __init__( 254 self, 255 time_mapping=None, 256 time_trie=None, 257 pretty=None, 258 quote_start=None, 259 quote_end=None, 260 identifier_start=None, 261 identifier_end=None, 262 identify=False, 263 normalize=False, 264 string_escape=None, 265 identifier_escape=None, 266 pad=2, 267 indent=2, 268 index_offset=0, 269 unnest_column_only=False, 270 alias_post_tablesample=False, 271 normalize_functions="upper", 272 unsupported_level=ErrorLevel.WARN, 273 null_ordering=None, 274 max_unsupported=3, 275 leading_comma=False, 276 max_text_width=80, 277 comments=True, 278 ): 279 import sqlglot 280 281 self.time_mapping = time_mapping or {} 282 self.time_trie = time_trie 283 self.pretty = pretty if pretty is not None else sqlglot.pretty 284 self.quote_start = quote_start or "'" 285 self.quote_end = quote_end or "'" 286 self.identifier_start = identifier_start or '"' 287 self.identifier_end = identifier_end or '"' 288 self.identify = identify 289 self.normalize = normalize 290 self.string_escape = string_escape or "'" 291 self.identifier_escape = identifier_escape or '"' 292 self.pad = pad 293 self.index_offset = index_offset 294 self.unnest_column_only = unnest_column_only 295 self.alias_post_tablesample = alias_post_tablesample 296 self.normalize_functions = normalize_functions 297 self.unsupported_level = unsupported_level 298 self.unsupported_messages = [] 299 self.max_unsupported = max_unsupported 300 self.null_ordering = null_ordering 301 self._indent = indent 302 self._escaped_quote_end = self.string_escape + self.quote_end 303 self._escaped_identifier_end = self.identifier_escape + self.identifier_end 304 self._leading_comma = leading_comma 305 self._max_text_width = max_text_width 306 self._comments = comments 307 self._cache = None
def
generate( self, expression: Optional[sqlglot.expressions.Expression], cache: Optional[Dict[int, str]] = None) -> str:
309 def generate( 310 self, 311 expression: t.Optional[exp.Expression], 312 cache: t.Optional[t.Dict[int, str]] = None, 313 ) -> str: 314 """ 315 Generates a SQL string by interpreting the given syntax tree. 316 317 Args 318 expression: the syntax tree. 319 cache: an optional sql string cache. this leverages the hash of an expression which is slow, so only use this if you set _hash on each node. 320 321 Returns 322 the SQL string. 323 """ 324 if cache is not None: 325 self._cache = cache 326 self.unsupported_messages = [] 327 sql = self.sql(expression).strip() 328 self._cache = None 329 330 if self.unsupported_level == ErrorLevel.IGNORE: 331 return sql 332 333 if self.unsupported_level == ErrorLevel.WARN: 334 for msg in self.unsupported_messages: 335 logger.warning(msg) 336 elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages: 337 raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported)) 338 339 if self.pretty: 340 sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n") 341 return sql
Generates a SQL string by interpreting the given syntax tree.
Args expression: the syntax tree. cache: an optional sql string cache. this leverages the hash of an expression which is slow, so only use this if you set _hash on each node.
Returns the SQL string.
def
maybe_comment( self, sql: str, expression: Optional[sqlglot.expressions.Expression] = None, comments: Optional[List[str]] = None) -> str:
359 def maybe_comment( 360 self, 361 sql: str, 362 expression: t.Optional[exp.Expression] = None, 363 comments: t.Optional[t.List[str]] = None, 364 ) -> str: 365 comments = (comments or (expression and expression.comments)) if self._comments else None # type: ignore 366 367 if not comments or isinstance(expression, exp.Binary): 368 return sql 369 370 sep = "\n" if self.pretty else " " 371 comments_sql = sep.join( 372 f"/*{self.pad_comment(comment)}*/" for comment in comments if comment 373 ) 374 375 if not comments_sql: 376 return sql 377 378 if isinstance(expression, self.WITH_SEPARATED_COMMENTS): 379 return f"{comments_sql}{self.sep()}{sql}" 380 381 return f"{sql} {comments_sql}"
383 def wrap(self, expression: exp.Expression | str) -> str: 384 this_sql = self.indent( 385 self.sql(expression) 386 if isinstance(expression, (exp.Select, exp.Union)) 387 else self.sql(expression, "this"), 388 level=1, 389 pad=0, 390 ) 391 return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
def
indent( self, sql: str, level: int = 0, pad: Optional[int] = None, skip_first: bool = False, skip_last: bool = False) -> str:
407 def indent( 408 self, 409 sql: str, 410 level: int = 0, 411 pad: t.Optional[int] = None, 412 skip_first: bool = False, 413 skip_last: bool = False, 414 ) -> str: 415 if not self.pretty: 416 return sql 417 418 pad = self.pad if pad is None else pad 419 lines = sql.split("\n") 420 421 return "\n".join( 422 line 423 if (skip_first and i == 0) or (skip_last and i == len(lines) - 1) 424 else f"{' ' * (level * self._indent + pad)}{line}" 425 for i, line in enumerate(lines) 426 )
def
sql( self, expression: Union[str, sqlglot.expressions.Expression, NoneType], key: Optional[str] = None, comment: bool = True) -> str:
428 def sql( 429 self, 430 expression: t.Optional[str | exp.Expression], 431 key: t.Optional[str] = None, 432 comment: bool = True, 433 ) -> str: 434 if not expression: 435 return "" 436 437 if isinstance(expression, str): 438 return expression 439 440 if key: 441 return self.sql(expression.args.get(key)) 442 443 if self._cache is not None: 444 expression_id = hash(expression) 445 446 if expression_id in self._cache: 447 return self._cache[expression_id] 448 449 transform = self.TRANSFORMS.get(expression.__class__) 450 451 if callable(transform): 452 sql = transform(self, expression) 453 elif transform: 454 sql = transform 455 elif isinstance(expression, exp.Expression): 456 exp_handler_name = f"{expression.key}_sql" 457 458 if hasattr(self, exp_handler_name): 459 sql = getattr(self, exp_handler_name)(expression) 460 elif isinstance(expression, exp.Func): 461 sql = self.function_fallback_sql(expression) 462 elif isinstance(expression, exp.Property): 463 sql = self.property_sql(expression) 464 else: 465 raise ValueError(f"Unsupported expression type {expression.__class__.__name__}") 466 else: 467 raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}") 468 469 sql = self.maybe_comment(sql, expression) if self._comments and comment else sql 470 471 if self._cache is not None: 472 self._cache[expression_id] = sql 473 return sql
480 def cache_sql(self, expression: exp.Cache) -> str: 481 lazy = " LAZY" if expression.args.get("lazy") else "" 482 table = self.sql(expression, "this") 483 options = expression.args.get("options") 484 options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else "" 485 sql = self.sql(expression, "expression") 486 sql = f" AS{self.sep()}{sql}" if sql else "" 487 sql = f"CACHE{lazy} TABLE {table}{options}{sql}" 488 return self.prepend_ctes(expression, sql)
490 def characterset_sql(self, expression: exp.CharacterSet) -> str: 491 if isinstance(expression.parent, exp.Cast): 492 return f"CHAR CHARACTER SET {self.sql(expression, 'this')}" 493 default = "DEFAULT " if expression.args.get("default") else "" 494 return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
514 def columndef_sql(self, expression: exp.ColumnDef) -> str: 515 column = self.sql(expression, "this") 516 kind = self.sql(expression, "kind") 517 constraints = self.expressions(expression, key="constraints", sep=" ", flat=True) 518 exists = "IF NOT EXISTS " if expression.args.get("exists") else "" 519 kind = f" {kind}" if kind else "" 520 constraints = f" {constraints}" if constraints else "" 521 position = self.sql(expression, "position") 522 position = f" {position}" if position else "" 523 524 return f"{exists}{column}{kind}{constraints}{position}"
def
compresscolumnconstraint_sql(self, expression: sqlglot.expressions.CompressColumnConstraint) -> str:
def
generatedasidentitycolumnconstraint_sql( self, expression: sqlglot.expressions.GeneratedAsIdentityColumnConstraint) -> str:
542 def generatedasidentitycolumnconstraint_sql( 543 self, expression: exp.GeneratedAsIdentityColumnConstraint 544 ) -> str: 545 this = "" 546 if expression.this is not None: 547 this = " ALWAYS " if expression.this else " BY DEFAULT " 548 start = expression.args.get("start") 549 start = f"START WITH {start}" if start else "" 550 increment = expression.args.get("increment") 551 increment = f" INCREMENT BY {increment}" if increment else "" 552 minvalue = expression.args.get("minvalue") 553 minvalue = f" MINVALUE {minvalue}" if minvalue else "" 554 maxvalue = expression.args.get("maxvalue") 555 maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else "" 556 cycle = expression.args.get("cycle") 557 cycle_sql = "" 558 if cycle is not None: 559 cycle_sql = f"{' NO' if not cycle else ''} CYCLE" 560 cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql 561 sequence_opts = "" 562 if start or increment or cycle_sql: 563 sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}" 564 sequence_opts = f" ({sequence_opts.strip()})" 565 return f"GENERATED{this}AS IDENTITY{sequence_opts}"
def
notnullcolumnconstraint_sql(self, expression: sqlglot.expressions.NotNullColumnConstraint) -> str:
def
primarykeycolumnconstraint_sql(self, expression: sqlglot.expressions.PrimaryKeyColumnConstraint) -> str:
579 def create_sql(self, expression: exp.Create) -> str: 580 kind = self.sql(expression, "kind").upper() 581 properties = expression.args.get("properties") 582 properties_exp = expression.copy() 583 properties_locs = self.locate_properties(properties) if properties else {} 584 if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get( 585 exp.Properties.Location.POST_WITH 586 ): 587 properties_exp.set( 588 "properties", 589 exp.Properties( 590 expressions=[ 591 *properties_locs[exp.Properties.Location.POST_SCHEMA], 592 *properties_locs[exp.Properties.Location.POST_WITH], 593 ] 594 ), 595 ) 596 if kind == "TABLE" and properties_locs.get(exp.Properties.Location.POST_NAME): 597 this_name = self.sql(expression.this, "this") 598 this_properties = self.properties( 599 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_NAME]), 600 wrapped=False, 601 ) 602 this_schema = f"({self.expressions(expression.this)})" 603 this = f"{this_name}, {this_properties} {this_schema}" 604 properties_sql = "" 605 else: 606 this = self.sql(expression, "this") 607 properties_sql = self.sql(properties_exp, "properties") 608 begin = " BEGIN" if expression.args.get("begin") else "" 609 expression_sql = self.sql(expression, "expression") 610 if expression_sql: 611 expression_sql = f"{begin}{self.sep()}{expression_sql}" 612 613 if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return): 614 if properties_locs.get(exp.Properties.Location.POST_ALIAS): 615 postalias_props_sql = self.properties( 616 exp.Properties( 617 expressions=properties_locs[exp.Properties.Location.POST_ALIAS] 618 ), 619 wrapped=False, 620 ) 621 expression_sql = f" AS {postalias_props_sql}{expression_sql}" 622 else: 623 expression_sql = f" AS{expression_sql}" 624 625 postindex_props_sql = "" 626 if properties_locs.get(exp.Properties.Location.POST_INDEX): 627 postindex_props_sql = self.properties( 628 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]), 629 wrapped=False, 630 prefix=" ", 631 ) 632 633 indexes = expression.args.get("indexes") 634 if indexes: 635 indexes_sql: t.List[str] = [] 636 for index in indexes: 637 ind_unique = " UNIQUE" if index.args.get("unique") else "" 638 ind_primary = " PRIMARY" if index.args.get("primary") else "" 639 ind_amp = " AMP" if index.args.get("amp") else "" 640 ind_name = f" {index.name}" if index.name else "" 641 ind_columns = ( 642 f' ({self.expressions(index, key="columns", flat=True)})' 643 if index.args.get("columns") 644 else "" 645 ) 646 ind_sql = f"{ind_unique}{ind_primary}{ind_amp} INDEX{ind_name}{ind_columns}" 647 648 if indexes_sql: 649 indexes_sql.append(ind_sql) 650 else: 651 indexes_sql.append( 652 f"{ind_sql}{postindex_props_sql}" 653 if index.args.get("primary") 654 else f"{postindex_props_sql}{ind_sql}" 655 ) 656 657 index_sql = "".join(indexes_sql) 658 else: 659 index_sql = postindex_props_sql 660 661 replace = " OR REPLACE" if expression.args.get("replace") else "" 662 unique = " UNIQUE" if expression.args.get("unique") else "" 663 664 postcreate_props_sql = "" 665 if properties_locs.get(exp.Properties.Location.POST_CREATE): 666 postcreate_props_sql = self.properties( 667 exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]), 668 sep=" ", 669 prefix=" ", 670 wrapped=False, 671 ) 672 673 modifiers = "".join((replace, unique, postcreate_props_sql)) 674 675 postexpression_props_sql = "" 676 if properties_locs.get(exp.Properties.Location.POST_EXPRESSION): 677 postexpression_props_sql = self.properties( 678 exp.Properties( 679 expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION] 680 ), 681 sep=" ", 682 prefix=" ", 683 wrapped=False, 684 ) 685 686 exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else "" 687 no_schema_binding = ( 688 " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else "" 689 ) 690 691 expression_sql = f"CREATE{modifiers} {kind}{exists_sql} {this}{properties_sql}{expression_sql}{postexpression_props_sql}{index_sql}{no_schema_binding}" 692 return self.prepend_ctes(expression, expression_sql)
728 def datatype_sql(self, expression: exp.DataType) -> str: 729 type_value = expression.this 730 type_sql = self.TYPE_MAPPING.get(type_value, type_value.value) 731 nested = "" 732 interior = self.expressions(expression, flat=True) 733 values = "" 734 if interior: 735 if expression.args.get("nested"): 736 nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}" 737 if expression.args.get("values") is not None: 738 delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")") 739 values = f"{delimiters[0]}{self.expressions(expression, key='values')}{delimiters[1]}" 740 else: 741 nested = f"({interior})" 742 743 return f"{type_sql}{nested}{values}"
745 def directory_sql(self, expression: exp.Directory) -> str: 746 local = "LOCAL " if expression.args.get("local") else "" 747 row_format = self.sql(expression, "row_format") 748 row_format = f" {row_format}" if row_format else "" 749 return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
751 def delete_sql(self, expression: exp.Delete) -> str: 752 this = self.sql(expression, "this") 753 this = f" FROM {this}" if this else "" 754 using_sql = ( 755 f" USING {self.expressions(expression, key='using', sep=', USING ')}" 756 if expression.args.get("using") 757 else "" 758 ) 759 where_sql = self.sql(expression, "where") 760 returning = self.sql(expression, "returning") 761 sql = f"DELETE{this}{using_sql}{where_sql}{returning}" 762 return self.prepend_ctes(expression, sql)
764 def drop_sql(self, expression: exp.Drop) -> str: 765 this = self.sql(expression, "this") 766 kind = expression.args["kind"] 767 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 768 temporary = " TEMPORARY" if expression.args.get("temporary") else "" 769 materialized = " MATERIALIZED" if expression.args.get("materialized") else "" 770 cascade = " CASCADE" if expression.args.get("cascade") else "" 771 constraints = " CONSTRAINTS" if expression.args.get("constraints") else "" 772 purge = " PURGE" if expression.args.get("purge") else "" 773 return ( 774 f"DROP{temporary}{materialized} {kind}{exists_sql}{this}{cascade}{constraints}{purge}" 775 )
786 def fetch_sql(self, expression: exp.Fetch) -> str: 787 direction = expression.args.get("direction") 788 direction = f" {direction.upper()}" if direction else "" 789 count = expression.args.get("count") 790 count = f" {count}" if count else "" 791 if expression.args.get("percent"): 792 count = f"{count} PERCENT" 793 with_ties_or_only = "WITH TIES" if expression.args.get("with_ties") else "ONLY" 794 return f"{self.seg('FETCH')}{direction}{count} ROWS {with_ties_or_only}"
812 def identifier_sql(self, expression: exp.Identifier) -> str: 813 text = expression.name 814 lower = text.lower() 815 text = lower if self.normalize and not expression.quoted else text 816 text = text.replace(self.identifier_end, self._escaped_identifier_end) 817 if ( 818 expression.quoted 819 or should_identify(text, self.identify) 820 or lower in self.RESERVED_KEYWORDS 821 ): 822 text = f"{self.identifier_start}{text}{self.identifier_end}" 823 return text
825 def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str: 826 input_format = self.sql(expression, "input_format") 827 input_format = f"INPUTFORMAT {input_format}" if input_format else "" 828 output_format = self.sql(expression, "output_format") 829 output_format = f"OUTPUTFORMAT {output_format}" if output_format else "" 830 return self.sep().join((input_format, output_format))
838 def properties_sql(self, expression: exp.Properties) -> str: 839 root_properties = [] 840 with_properties = [] 841 842 for p in expression.expressions: 843 p_loc = self.PROPERTIES_LOCATION[p.__class__] 844 if p_loc == exp.Properties.Location.POST_WITH: 845 with_properties.append(p) 846 elif p_loc == exp.Properties.Location.POST_SCHEMA: 847 root_properties.append(p) 848 849 return self.root_properties( 850 exp.Properties(expressions=root_properties) 851 ) + self.with_properties(exp.Properties(expressions=with_properties))
def
properties( self, properties: sqlglot.expressions.Properties, prefix: str = '', sep: str = ', ', suffix: str = '', wrapped: bool = True) -> str:
858 def properties( 859 self, 860 properties: exp.Properties, 861 prefix: str = "", 862 sep: str = ", ", 863 suffix: str = "", 864 wrapped: bool = True, 865 ) -> str: 866 if properties.expressions: 867 expressions = self.expressions(properties, sep=sep, indent=False) 868 expressions = self.wrap(expressions) if wrapped else expressions 869 return f"{prefix}{' ' if prefix and prefix != ' ' else ''}{expressions}{suffix}" 870 return ""
def
locate_properties( self, properties: sqlglot.expressions.Properties) -> Dict[sqlglot.expressions.Properties.Location, list[sqlglot.expressions.Property]]:
875 def locate_properties( 876 self, properties: exp.Properties 877 ) -> t.Dict[exp.Properties.Location, list[exp.Property]]: 878 properties_locs: t.Dict[exp.Properties.Location, list[exp.Property]] = { 879 key: [] for key in exp.Properties.Location 880 } 881 882 for p in properties.expressions: 883 p_loc = self.PROPERTIES_LOCATION[p.__class__] 884 if p_loc == exp.Properties.Location.POST_NAME: 885 properties_locs[exp.Properties.Location.POST_NAME].append(p) 886 elif p_loc == exp.Properties.Location.POST_INDEX: 887 properties_locs[exp.Properties.Location.POST_INDEX].append(p) 888 elif p_loc == exp.Properties.Location.POST_SCHEMA: 889 properties_locs[exp.Properties.Location.POST_SCHEMA].append(p) 890 elif p_loc == exp.Properties.Location.POST_WITH: 891 properties_locs[exp.Properties.Location.POST_WITH].append(p) 892 elif p_loc == exp.Properties.Location.POST_CREATE: 893 properties_locs[exp.Properties.Location.POST_CREATE].append(p) 894 elif p_loc == exp.Properties.Location.POST_ALIAS: 895 properties_locs[exp.Properties.Location.POST_ALIAS].append(p) 896 elif p_loc == exp.Properties.Location.POST_EXPRESSION: 897 properties_locs[exp.Properties.Location.POST_EXPRESSION].append(p) 898 elif p_loc == exp.Properties.Location.UNSUPPORTED: 899 self.unsupported(f"Unsupported property {p.key}") 900 901 return properties_locs
903 def property_sql(self, expression: exp.Property) -> str: 904 property_cls = expression.__class__ 905 if property_cls == exp.Property: 906 return f"{expression.name}={self.sql(expression, 'value')}" 907 908 property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls) 909 if not property_name: 910 self.unsupported(f"Unsupported property {expression.key}") 911 912 return f"{property_name}={self.sql(expression, 'this')}"
935 def afterjournalproperty_sql(self, expression: exp.AfterJournalProperty) -> str: 936 no = "NO " if expression.args.get("no") else "" 937 dual = "DUAL " if expression.args.get("dual") else "" 938 local = "" 939 if expression.args.get("local") is not None: 940 local = "LOCAL " if expression.args.get("local") else "NOT LOCAL " 941 return f"{no}{dual}{local}AFTER JOURNAL"
def
mergeblockratioproperty_sql(self, expression: sqlglot.expressions.MergeBlockRatioProperty) -> str:
952 def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str: 953 if expression.args.get("no"): 954 return "NO MERGEBLOCKRATIO" 955 if expression.args.get("default"): 956 return "DEFAULT MERGEBLOCKRATIO" 957 958 percent = " PERCENT" if expression.args.get("percent") else "" 959 return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
961 def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str: 962 default = expression.args.get("default") 963 min = expression.args.get("min") 964 if default is not None or min is not None: 965 if default: 966 property = "DEFAULT" 967 elif min: 968 property = "MINIMUM" 969 else: 970 property = "MAXIMUM" 971 return f"{property} DATABLOCKSIZE" 972 else: 973 units = expression.args.get("units") 974 units = f" {units}" if units else "" 975 return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
def
blockcompressionproperty_sql(self, expression: sqlglot.expressions.BlockCompressionProperty) -> str:
977 def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str: 978 autotemp = expression.args.get("autotemp") 979 always = expression.args.get("always") 980 default = expression.args.get("default") 981 manual = expression.args.get("manual") 982 never = expression.args.get("never") 983 984 if autotemp is not None: 985 property = f"AUTOTEMP({self.expressions(autotemp)})" 986 elif always: 987 property = "ALWAYS" 988 elif default: 989 property = "DEFAULT" 990 elif manual: 991 property = "MANUAL" 992 elif never: 993 property = "NEVER" 994 return f"BLOCKCOMPRESSION={property}"
def
isolatedloadingproperty_sql(self, expression: sqlglot.expressions.IsolatedLoadingProperty) -> str:
996 def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str: 997 no = expression.args.get("no") 998 no = " NO" if no else "" 999 concurrent = expression.args.get("concurrent") 1000 concurrent = " CONCURRENT" if concurrent else "" 1001 1002 for_ = "" 1003 if expression.args.get("for_all"): 1004 for_ = " FOR ALL" 1005 elif expression.args.get("for_insert"): 1006 for_ = " FOR INSERT" 1007 elif expression.args.get("for_none"): 1008 for_ = " FOR NONE" 1009 return f"WITH{no}{concurrent} ISOLATED LOADING{for_}"
1011 def lockingproperty_sql(self, expression: exp.LockingProperty) -> str: 1012 kind = expression.args.get("kind") 1013 this: str = f" {this}" if expression.this else "" 1014 for_or_in = expression.args.get("for_or_in") 1015 lock_type = expression.args.get("lock_type") 1016 override = " OVERRIDE" if expression.args.get("override") else "" 1017 return f"LOCKING {kind}{this} {for_or_in} {lock_type}{override}"
1019 def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str: 1020 data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA" 1021 statistics = expression.args.get("statistics") 1022 statistics_sql = "" 1023 if statistics is not None: 1024 statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS" 1025 return f"{data_sql}{statistics_sql}"
1027 def insert_sql(self, expression: exp.Insert) -> str: 1028 overwrite = expression.args.get("overwrite") 1029 1030 if isinstance(expression.this, exp.Directory): 1031 this = "OVERWRITE " if overwrite else "INTO " 1032 else: 1033 this = "OVERWRITE TABLE " if overwrite else "INTO " 1034 1035 alternative = expression.args.get("alternative") 1036 alternative = f" OR {alternative} " if alternative else " " 1037 this = f"{this}{self.sql(expression, 'this')}" 1038 1039 exists = " IF EXISTS " if expression.args.get("exists") else " " 1040 partition_sql = ( 1041 self.sql(expression, "partition") if expression.args.get("partition") else "" 1042 ) 1043 expression_sql = self.sql(expression, "expression") 1044 conflict = self.sql(expression, "conflict") 1045 returning = self.sql(expression, "returning") 1046 sep = self.sep() if partition_sql else "" 1047 sql = f"INSERT{alternative}{this}{exists}{partition_sql}{sep}{expression_sql}{conflict}{returning}" 1048 return self.prepend_ctes(expression, sql)
1065 def onconflict_sql(self, expression: exp.OnConflict) -> str: 1066 conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT" 1067 constraint = self.sql(expression, "constraint") 1068 if constraint: 1069 constraint = f"ON CONSTRAINT {constraint}" 1070 key = self.expressions(expression, key="key", flat=True) 1071 do = "" if expression.args.get("duplicate") else " DO " 1072 nothing = "NOTHING" if expression.args.get("nothing") else "" 1073 expressions = self.expressions(expression, flat=True) 1074 if expressions: 1075 expressions = f"UPDATE SET {expressions}" 1076 return f"{self.seg(conflict)} {constraint}{key}{do}{nothing}{expressions}"
def
rowformatdelimitedproperty_sql(self, expression: sqlglot.expressions.RowFormatDelimitedProperty) -> str:
1081 def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str: 1082 fields = expression.args.get("fields") 1083 fields = f" FIELDS TERMINATED BY {fields}" if fields else "" 1084 escaped = expression.args.get("escaped") 1085 escaped = f" ESCAPED BY {escaped}" if escaped else "" 1086 items = expression.args.get("collection_items") 1087 items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else "" 1088 keys = expression.args.get("map_keys") 1089 keys = f" MAP KEYS TERMINATED BY {keys}" if keys else "" 1090 lines = expression.args.get("lines") 1091 lines = f" LINES TERMINATED BY {lines}" if lines else "" 1092 null = expression.args.get("null") 1093 null = f" NULL DEFINED AS {null}" if null else "" 1094 return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
1096 def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str: 1097 table = ".".join( 1098 part 1099 for part in [ 1100 self.sql(expression, "catalog"), 1101 self.sql(expression, "db"), 1102 self.sql(expression, "this"), 1103 ] 1104 if part 1105 ) 1106 1107 alias = self.sql(expression, "alias") 1108 alias = f"{sep}{alias}" if alias else "" 1109 hints = self.expressions(expression, key="hints", sep=", ", flat=True) 1110 hints = f" WITH ({hints})" if hints and self.TABLE_HINTS else "" 1111 laterals = self.expressions(expression, key="laterals", sep="") 1112 joins = self.expressions(expression, key="joins", sep="") 1113 pivots = self.expressions(expression, key="pivots", sep="") 1114 system_time = expression.args.get("system_time") 1115 system_time = f" {self.sql(expression, 'system_time')}" if system_time else "" 1116 1117 return f"{table}{system_time}{alias}{hints}{laterals}{joins}{pivots}"
def
tablesample_sql( self, expression: sqlglot.expressions.TableSample, seed_prefix: str = 'SEED') -> str:
1119 def tablesample_sql(self, expression: exp.TableSample, seed_prefix: str = "SEED") -> str: 1120 if self.alias_post_tablesample and expression.this.alias: 1121 this = self.sql(expression.this, "this") 1122 alias = f" AS {self.sql(expression.this, 'alias')}" 1123 else: 1124 this = self.sql(expression, "this") 1125 alias = "" 1126 method = self.sql(expression, "method") 1127 method = f"{method.upper()} " if method and self.TABLESAMPLE_WITH_METHOD else "" 1128 numerator = self.sql(expression, "bucket_numerator") 1129 denominator = self.sql(expression, "bucket_denominator") 1130 field = self.sql(expression, "bucket_field") 1131 field = f" ON {field}" if field else "" 1132 bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else "" 1133 percent = self.sql(expression, "percent") 1134 percent = f"{percent} PERCENT" if percent else "" 1135 rows = self.sql(expression, "rows") 1136 rows = f"{rows} ROWS" if rows else "" 1137 size = self.sql(expression, "size") 1138 if size and self.TABLESAMPLE_SIZE_IS_PERCENT: 1139 size = f"{size} PERCENT" 1140 seed = self.sql(expression, "seed") 1141 seed = f" {seed_prefix} ({seed})" if seed else "" 1142 kind = expression.args.get("kind", "TABLESAMPLE") 1143 return f"{this} {kind} {method}({bucket}{percent}{rows}{size}){seed}{alias}"
1145 def pivot_sql(self, expression: exp.Pivot) -> str: 1146 this = self.sql(expression, "this") 1147 alias = self.sql(expression, "alias") 1148 alias = f" AS {alias}" if alias else "" 1149 unpivot = expression.args.get("unpivot") 1150 direction = "UNPIVOT" if unpivot else "PIVOT" 1151 expressions = self.expressions(expression, key="expressions") 1152 field = self.sql(expression, "field") 1153 return f"{this} {direction}({expressions} FOR {field}){alias}"
1158 def update_sql(self, expression: exp.Update) -> str: 1159 this = self.sql(expression, "this") 1160 set_sql = self.expressions(expression, flat=True) 1161 from_sql = self.sql(expression, "from") 1162 where_sql = self.sql(expression, "where") 1163 returning = self.sql(expression, "returning") 1164 sql = f"UPDATE {this} SET {set_sql}{from_sql}{where_sql}{returning}" 1165 return self.prepend_ctes(expression, sql)
1167 def values_sql(self, expression: exp.Values) -> str: 1168 args = self.expressions(expression) 1169 alias = self.sql(expression, "alias") 1170 values = f"VALUES{self.seg('')}{args}" 1171 values = ( 1172 f"({values})" 1173 if self.WRAP_DERIVED_VALUES and (alias or isinstance(expression.parent, exp.From)) 1174 else values 1175 ) 1176 return f"{values} AS {alias}" if alias else values
1190 def group_sql(self, expression: exp.Group) -> str: 1191 group_by = self.op_expressions("GROUP BY", expression) 1192 grouping_sets = self.expressions(expression, key="grouping_sets", indent=False) 1193 grouping_sets = ( 1194 f"{self.seg('GROUPING SETS')} {self.wrap(grouping_sets)}" if grouping_sets else "" 1195 ) 1196 1197 cube = expression.args.get("cube", []) 1198 if seq_get(cube, 0) is True: 1199 return f"{group_by}{self.seg('WITH CUBE')}" 1200 else: 1201 cube_sql = self.expressions(expression, key="cube", indent=False) 1202 cube_sql = f"{self.seg('CUBE')} {self.wrap(cube_sql)}" if cube_sql else "" 1203 1204 rollup = expression.args.get("rollup", []) 1205 if seq_get(rollup, 0) is True: 1206 return f"{group_by}{self.seg('WITH ROLLUP')}" 1207 else: 1208 rollup_sql = self.expressions(expression, key="rollup", indent=False) 1209 rollup_sql = f"{self.seg('ROLLUP')} {self.wrap(rollup_sql)}" if rollup_sql else "" 1210 1211 groupings = csv(grouping_sets, cube_sql, rollup_sql, sep=",") 1212 1213 if expression.args.get("expressions") and groupings: 1214 group_by = f"{group_by}," 1215 1216 return f"{group_by}{groupings}"
1222 def join_sql(self, expression: exp.Join) -> str: 1223 op_sql = self.seg( 1224 " ".join( 1225 op 1226 for op in ( 1227 "NATURAL" if expression.args.get("natural") else None, 1228 expression.side, 1229 expression.kind, 1230 expression.hint if self.JOIN_HINTS else None, 1231 "JOIN", 1232 ) 1233 if op 1234 ) 1235 ) 1236 on_sql = self.sql(expression, "on") 1237 using = expression.args.get("using") 1238 1239 if not on_sql and using: 1240 on_sql = csv(*(self.sql(column) for column in using)) 1241 1242 if on_sql: 1243 on_sql = self.indent(on_sql, skip_first=True) 1244 space = self.seg(" " * self.pad) if self.pretty else " " 1245 if using: 1246 on_sql = f"{space}USING ({on_sql})" 1247 else: 1248 on_sql = f"{space}ON {on_sql}" 1249 1250 expression_sql = self.sql(expression, "expression") 1251 this_sql = self.sql(expression, "this") 1252 return f"{expression_sql}{op_sql} {this_sql}{on_sql}"
1259 def lateral_sql(self, expression: exp.Lateral) -> str: 1260 this = self.sql(expression, "this") 1261 1262 if isinstance(expression.this, exp.Subquery): 1263 return f"LATERAL {this}" 1264 1265 if expression.args.get("view"): 1266 alias = expression.args["alias"] 1267 columns = self.expressions(alias, key="columns", flat=True) 1268 table = f" {alias.name}" if alias.name else "" 1269 columns = f" AS {columns}" if columns else "" 1270 op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}") 1271 return f"{op_sql}{self.sep()}{this}{table}{columns}" 1272 1273 alias = self.sql(expression, "alias") 1274 alias = f" AS {alias}" if alias else "" 1275 return f"LATERAL {this}{alias}"
1285 def setitem_sql(self, expression: exp.SetItem) -> str: 1286 kind = self.sql(expression, "kind") 1287 kind = f"{kind} " if kind else "" 1288 this = self.sql(expression, "this") 1289 expressions = self.expressions(expression) 1290 collate = self.sql(expression, "collate") 1291 collate = f" COLLATE {collate}" if collate else "" 1292 global_ = "GLOBAL " if expression.args.get("global") else "" 1293 return f"{global_}{kind}{this}{expressions}{collate}"
1304 def lock_sql(self, expression: exp.Lock) -> str: 1305 if self.LOCKING_READS_SUPPORTED: 1306 lock_type = "UPDATE" if expression.args["update"] else "SHARE" 1307 return self.seg(f"FOR {lock_type}") 1308 1309 self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported") 1310 return ""
1312 def literal_sql(self, expression: exp.Literal) -> str: 1313 text = expression.this or "" 1314 if expression.is_string: 1315 text = text.replace(self.quote_end, self._escaped_quote_end) 1316 if self.pretty: 1317 text = text.replace("\n", self.SENTINEL_LINE_BREAK) 1318 text = f"{self.quote_start}{text}{self.quote_end}" 1319 return text
1321 def loaddata_sql(self, expression: exp.LoadData) -> str: 1322 local = " LOCAL" if expression.args.get("local") else "" 1323 inpath = f" INPATH {self.sql(expression, 'inpath')}" 1324 overwrite = " OVERWRITE" if expression.args.get("overwrite") else "" 1325 this = f" INTO TABLE {self.sql(expression, 'this')}" 1326 partition = self.sql(expression, "partition") 1327 partition = f" {partition}" if partition else "" 1328 input_format = self.sql(expression, "input_format") 1329 input_format = f" INPUTFORMAT {input_format}" if input_format else "" 1330 serde = self.sql(expression, "serde") 1331 serde = f" SERDE {serde}" if serde else "" 1332 return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
1354 def ordered_sql(self, expression: exp.Ordered) -> str: 1355 desc = expression.args.get("desc") 1356 asc = not desc 1357 1358 nulls_first = expression.args.get("nulls_first") 1359 nulls_last = not nulls_first 1360 nulls_are_large = self.null_ordering == "nulls_are_large" 1361 nulls_are_small = self.null_ordering == "nulls_are_small" 1362 nulls_are_last = self.null_ordering == "nulls_are_last" 1363 1364 sort_order = " DESC" if desc else "" 1365 nulls_sort_change = "" 1366 if nulls_first and ( 1367 (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last 1368 ): 1369 nulls_sort_change = " NULLS FIRST" 1370 elif ( 1371 nulls_last 1372 and ((asc and nulls_are_small) or (desc and nulls_are_large)) 1373 and not nulls_are_last 1374 ): 1375 nulls_sort_change = " NULLS LAST" 1376 1377 if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED: 1378 self.unsupported( 1379 "Sorting in an ORDER BY on NULLS FIRST/NULLS LAST is not supported by this dialect" 1380 ) 1381 nulls_sort_change = "" 1382 1383 return f"{self.sql(expression, 'this')}{sort_order}{nulls_sort_change}"
1385 def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str: 1386 partition = self.partition_by_sql(expression) 1387 order = self.sql(expression, "order") 1388 measures = self.expressions(expression, key="measures") 1389 measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else "" 1390 rows = self.sql(expression, "rows") 1391 rows = self.seg(rows) if rows else "" 1392 after = self.sql(expression, "after") 1393 after = self.seg(after) if after else "" 1394 pattern = self.sql(expression, "pattern") 1395 pattern = self.seg(f"PATTERN ({pattern})") if pattern else "" 1396 definition_sqls = [ 1397 f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}" 1398 for definition in expression.args.get("define", []) 1399 ] 1400 definitions = self.expressions(sqls=definition_sqls) 1401 define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else "" 1402 body = "".join( 1403 ( 1404 partition, 1405 order, 1406 measures, 1407 rows, 1408 after, 1409 pattern, 1410 define, 1411 ) 1412 ) 1413 alias = self.sql(expression, "alias") 1414 alias = f" {alias}" if alias else "" 1415 return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
1417 def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str: 1418 limit = expression.args.get("limit") 1419 1420 if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch): 1421 limit = exp.Limit(expression=limit.args.get("count")) 1422 elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit): 1423 limit = exp.Fetch(direction="FIRST", count=limit.expression) 1424 1425 fetch = isinstance(limit, exp.Fetch) 1426 1427 return csv( 1428 *sqls, 1429 *[self.sql(sql) for sql in expression.args.get("joins") or []], 1430 self.sql(expression, "match"), 1431 *[self.sql(sql) for sql in expression.args.get("laterals") or []], 1432 self.sql(expression, "where"), 1433 self.sql(expression, "group"), 1434 self.sql(expression, "having"), 1435 self.sql(expression, "qualify"), 1436 self.seg("WINDOW ") + self.expressions(expression, key="windows", flat=True) 1437 if expression.args.get("windows") 1438 else "", 1439 self.sql(expression, "distribute"), 1440 self.sql(expression, "sort"), 1441 self.sql(expression, "cluster"), 1442 self.sql(expression, "order"), 1443 self.sql(expression, "offset") if fetch else self.sql(limit), 1444 self.sql(limit) if fetch else self.sql(expression, "offset"), 1445 self.sql(expression, "lock"), 1446 self.sql(expression, "sample"), 1447 sep="", 1448 )
1450 def select_sql(self, expression: exp.Select) -> str: 1451 kind = expression.args.get("kind") 1452 kind = f" AS {kind}" if kind else "" 1453 hint = self.sql(expression, "hint") 1454 distinct = self.sql(expression, "distinct") 1455 distinct = f" {distinct}" if distinct else "" 1456 expressions = self.expressions(expression) 1457 expressions = f"{self.sep()}{expressions}" if expressions else expressions 1458 sql = self.query_modifiers( 1459 expression, 1460 f"SELECT{kind}{hint}{distinct}{expressions}", 1461 self.sql(expression, "into", comment=False), 1462 self.sql(expression, "from", comment=False), 1463 ) 1464 return self.prepend_ctes(expression, sql)
1472 def star_sql(self, expression: exp.Star) -> str: 1473 except_ = self.expressions(expression, key="except", flat=True) 1474 except_ = f"{self.seg(self.STAR_MAPPING['except'])} ({except_})" if except_ else "" 1475 replace = self.expressions(expression, key="replace", flat=True) 1476 replace = f"{self.seg(self.STAR_MAPPING['replace'])} ({replace})" if replace else "" 1477 return f"*{except_}{replace}"
1497 def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str: 1498 alias = self.sql(expression, "alias") 1499 alias = f"{sep}{alias}" if alias else "" 1500 1501 sql = self.query_modifiers( 1502 expression, 1503 self.wrap(expression), 1504 alias, 1505 self.expressions(expression, key="pivots", sep=" "), 1506 ) 1507 1508 return self.prepend_ctes(expression, sql)
1525 def unnest_sql(self, expression: exp.Unnest) -> str: 1526 args = self.expressions(expression, flat=True) 1527 alias = expression.args.get("alias") 1528 if alias and self.unnest_column_only: 1529 columns = alias.columns 1530 alias = self.sql(columns[0]) if columns else "" 1531 else: 1532 alias = self.sql(expression, "alias") 1533 alias = f" AS {alias}" if alias else alias 1534 ordinality = " WITH ORDINALITY" if expression.args.get("ordinality") else "" 1535 offset = expression.args.get("offset") 1536 offset = f" WITH OFFSET AS {self.sql(offset)}" if offset else "" 1537 return f"UNNEST({args}){ordinality}{alias}{offset}"
1543 def window_sql(self, expression: exp.Window) -> str: 1544 this = self.sql(expression, "this") 1545 1546 partition = self.partition_by_sql(expression) 1547 1548 order = expression.args.get("order") 1549 order_sql = self.order_sql(order, flat=True) if order else "" 1550 1551 partition_sql = partition + " " if partition and order else partition 1552 1553 spec = expression.args.get("spec") 1554 spec_sql = " " + self.window_spec_sql(spec) if spec else "" 1555 1556 alias = self.sql(expression, "alias") 1557 this = f"{this} {'AS' if expression.arg_key == 'windows' else 'OVER'}" 1558 1559 if not partition and not order and not spec and alias: 1560 return f"{this} {alias}" 1561 1562 window_args = alias + partition_sql + order_sql + spec_sql 1563 1564 return f"{this} ({window_args.strip()})"
def
partition_by_sql( self, expression: sqlglot.expressions.Window | sqlglot.expressions.MatchRecognize) -> str:
1570 def window_spec_sql(self, expression: exp.WindowSpec) -> str: 1571 kind = self.sql(expression, "kind") 1572 start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ") 1573 end = ( 1574 csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ") 1575 or "CURRENT ROW" 1576 ) 1577 return f"{kind} BETWEEN {start} AND {end}"
1608 def case_sql(self, expression: exp.Case) -> str: 1609 this = self.sql(expression, "this") 1610 statements = [f"CASE {this}" if this else "CASE"] 1611 1612 for e in expression.args["ifs"]: 1613 statements.append(f"WHEN {self.sql(e, 'this')}") 1614 statements.append(f"THEN {self.sql(e, 'true')}") 1615 1616 default = self.sql(expression, "default") 1617 1618 if default: 1619 statements.append(f"ELSE {default}") 1620 1621 statements.append("END") 1622 1623 if self.pretty and self.text_width(statements) > self._max_text_width: 1624 return self.indent("\n".join(statements), skip_first=True, skip_last=True) 1625 1626 return " ".join(statements)
1638 def trim_sql(self, expression: exp.Trim) -> str: 1639 trim_type = self.sql(expression, "position") 1640 1641 if trim_type == "LEADING": 1642 return self.func("LTRIM", expression.this) 1643 elif trim_type == "TRAILING": 1644 return self.func("RTRIM", expression.this) 1645 else: 1646 return self.func("TRIM", expression.this, expression.expression)
1657 def foreignkey_sql(self, expression: exp.ForeignKey) -> str: 1658 expressions = self.expressions(expression, flat=True) 1659 reference = self.sql(expression, "reference") 1660 reference = f" {reference}" if reference else "" 1661 delete = self.sql(expression, "delete") 1662 delete = f" ON DELETE {delete}" if delete else "" 1663 update = self.sql(expression, "update") 1664 update = f" ON UPDATE {update}" if update else "" 1665 return f"FOREIGN KEY ({expressions}){reference}{delete}{update}"
1667 def primarykey_sql(self, expression: exp.ForeignKey) -> str: 1668 expressions = self.expressions(expression, flat=True) 1669 options = self.expressions(expression, key="options", flat=True, sep=" ") 1670 options = f" {options}" if options else "" 1671 return f"PRIMARY KEY ({expressions}){options}"
1690 def jsonobject_sql(self, expression: exp.JSONObject) -> str: 1691 expressions = self.expressions(expression) 1692 null_handling = expression.args.get("null_handling") 1693 null_handling = f" {null_handling}" if null_handling else "" 1694 unique_keys = expression.args.get("unique_keys") 1695 if unique_keys is not None: 1696 unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS" 1697 else: 1698 unique_keys = "" 1699 return_type = self.sql(expression, "return_type") 1700 return_type = f" RETURNING {return_type}" if return_type else "" 1701 format_json = " FORMAT JSON" if expression.args.get("format_json") else "" 1702 encoding = self.sql(expression, "encoding") 1703 encoding = f" ENCODING {encoding}" if encoding else "" 1704 return f"JSON_OBJECT({expressions}{null_handling}{unique_keys}{return_type}{format_json}{encoding})"
1706 def in_sql(self, expression: exp.In) -> str: 1707 query = expression.args.get("query") 1708 unnest = expression.args.get("unnest") 1709 field = expression.args.get("field") 1710 is_global = " GLOBAL" if expression.args.get("is_global") else "" 1711 1712 if query: 1713 in_sql = self.wrap(query) 1714 elif unnest: 1715 in_sql = self.in_unnest_op(unnest) 1716 elif field: 1717 in_sql = self.sql(field) 1718 else: 1719 in_sql = f"({self.expressions(expression, flat=True)})" 1720 1721 return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
1726 def interval_sql(self, expression: exp.Interval) -> str: 1727 unit = self.sql(expression, "unit") 1728 if not self.INTERVAL_ALLOWS_PLURAL_FORM: 1729 unit = self.TIME_PART_SINGULARS.get(unit.lower(), unit) 1730 unit = f" {unit}" if unit else "" 1731 1732 if self.SINGLE_STRING_INTERVAL: 1733 this = expression.this.name if expression.this else "" 1734 return f"INTERVAL '{this}{unit}'" 1735 1736 this = self.sql(expression, "this") 1737 if this: 1738 unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES) 1739 this = f" {this}" if unwrapped else f" ({this})" 1740 1741 return f"INTERVAL{this}{unit}"
1746 def reference_sql(self, expression: exp.Reference) -> str: 1747 this = self.sql(expression, "this") 1748 expressions = self.expressions(expression, flat=True) 1749 expressions = f"({expressions})" if expressions else "" 1750 options = self.expressions(expression, key="options", flat=True, sep=" ") 1751 options = f" {options}" if options else "" 1752 return f"REFERENCES {this}{expressions}{options}"
1757 def paren_sql(self, expression: exp.Paren) -> str: 1758 if isinstance(expression.unnest(), exp.Select): 1759 sql = self.wrap(expression) 1760 else: 1761 sql = self.seg(self.indent(self.sql(expression, "this")), sep="") 1762 sql = f"({sql}{self.seg(')', sep='')}" 1763 1764 return self.prepend_ctes(expression, sql)
1794 def connector_sql(self, expression: exp.Connector, op: str) -> str: 1795 if not self.pretty: 1796 return self.binary(expression, op) 1797 1798 sqls = tuple( 1799 self.maybe_comment(self.sql(e), e, e.parent.comments) if i != 1 else self.sql(e) 1800 for i, e in enumerate(expression.flatten(unnest=False)) 1801 ) 1802 1803 sep = "\n" if self.text_width(sqls) > self._max_text_width else " " 1804 return f"{sep}{op} ".join(sqls)
1837 def comment_sql(self, expression: exp.Comment) -> str: 1838 this = self.sql(expression, "this") 1839 kind = expression.args["kind"] 1840 exists_sql = " IF EXISTS " if expression.args.get("exists") else " " 1841 expression_sql = self.sql(expression, "expression") 1842 return f"COMMENT{exists_sql}ON {kind} {this} IS {expression_sql}"
1859 def altercolumn_sql(self, expression: exp.AlterColumn) -> str: 1860 this = self.sql(expression, "this") 1861 1862 dtype = self.sql(expression, "dtype") 1863 if dtype: 1864 collate = self.sql(expression, "collate") 1865 collate = f" COLLATE {collate}" if collate else "" 1866 using = self.sql(expression, "using") 1867 using = f" USING {using}" if using else "" 1868 return f"ALTER COLUMN {this} TYPE {dtype}{collate}{using}" 1869 1870 default = self.sql(expression, "default") 1871 if default: 1872 return f"ALTER COLUMN {this} SET DEFAULT {default}" 1873 1874 if not expression.args.get("drop"): 1875 self.unsupported("Unsupported ALTER COLUMN syntax") 1876 1877 return f"ALTER COLUMN {this} DROP DEFAULT"
1883 def altertable_sql(self, expression: exp.AlterTable) -> str: 1884 actions = expression.args["actions"] 1885 1886 if isinstance(actions[0], exp.ColumnDef): 1887 actions = self.expressions(expression, key="actions", prefix="ADD COLUMN ") 1888 elif isinstance(actions[0], exp.Schema): 1889 actions = self.expressions(expression, key="actions", prefix="ADD COLUMNS ") 1890 elif isinstance(actions[0], exp.Delete): 1891 actions = self.expressions(expression, key="actions", flat=True) 1892 else: 1893 actions = self.expressions(expression, key="actions") 1894 1895 exists = " IF EXISTS" if expression.args.get("exists") else "" 1896 return f"ALTER TABLE{exists} {self.sql(expression, 'this')} {actions}"
1903 def addconstraint_sql(self, expression: exp.AddConstraint) -> str: 1904 this = self.sql(expression, "this") 1905 expression_ = self.sql(expression, "expression") 1906 add_constraint = f"ADD CONSTRAINT {this}" if this else "ADD" 1907 1908 enforced = expression.args.get("enforced") 1909 if enforced is not None: 1910 return f"{add_constraint} CHECK ({expression_}){' ENFORCED' if enforced else ''}" 1911 1912 return f"{add_constraint} {expression_}"
2028 def function_fallback_sql(self, expression: exp.Func) -> str: 2029 args = [] 2030 for arg_value in expression.args.values(): 2031 if isinstance(arg_value, list): 2032 for value in arg_value: 2033 args.append(value) 2034 else: 2035 args.append(arg_value) 2036 2037 return self.func(expression.sql_name(), *args)
2042 def format_args(self, *args: t.Optional[str | exp.Expression]) -> str: 2043 arg_sqls = tuple(self.sql(arg) for arg in args if arg is not None) 2044 if self.pretty and self.text_width(arg_sqls) > self._max_text_width: 2045 return self.indent("\n" + f",\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True) 2046 return ", ".join(arg_sqls)
def
expressions( self, expression: Optional[sqlglot.expressions.Expression] = None, key: Optional[str] = None, sqls: Optional[List[str]] = None, flat: bool = False, indent: bool = True, sep: str = ', ', prefix: str = '') -> str:
2054 def expressions( 2055 self, 2056 expression: t.Optional[exp.Expression] = None, 2057 key: t.Optional[str] = None, 2058 sqls: t.Optional[t.List[str]] = None, 2059 flat: bool = False, 2060 indent: bool = True, 2061 sep: str = ", ", 2062 prefix: str = "", 2063 ) -> str: 2064 expressions = expression.args.get(key or "expressions") if expression else sqls 2065 2066 if not expressions: 2067 return "" 2068 2069 if flat: 2070 return sep.join(self.sql(e) for e in expressions) 2071 2072 num_sqls = len(expressions) 2073 2074 # These are calculated once in case we have the leading_comma / pretty option set, correspondingly 2075 pad = " " * self.pad 2076 stripped_sep = sep.strip() 2077 2078 result_sqls = [] 2079 for i, e in enumerate(expressions): 2080 sql = self.sql(e, comment=False) 2081 comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else "" 2082 2083 if self.pretty: 2084 if self._leading_comma: 2085 result_sqls.append(f"{sep if i > 0 else pad}{prefix}{sql}{comments}") 2086 else: 2087 result_sqls.append( 2088 f"{prefix}{sql}{stripped_sep if i + 1 < num_sqls else ''}{comments}" 2089 ) 2090 else: 2091 result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}") 2092 2093 result_sql = "\n".join(result_sqls) if self.pretty else "".join(result_sqls) 2094 return self.indent(result_sql, skip_first=False) if indent else result_sql
def
op_expressions( self, op: str, expression: sqlglot.expressions.Expression, flat: bool = False) -> str:
2096 def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str: 2097 flat = flat or isinstance(expression.parent, exp.Properties) 2098 expressions_sql = self.expressions(expression, flat=flat) 2099 if flat: 2100 return f"{op} {expressions_sql}" 2101 return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}"
2103 def naked_property(self, expression: exp.Property) -> str: 2104 property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__) 2105 if not property_name: 2106 self.unsupported(f"Unsupported property {expression.__class__.__name__}") 2107 return f"{property_name} {self.sql(expression, 'this')}"
2122 def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str: 2123 this = self.sql(expression, "this") 2124 expressions = self.no_identify(self.expressions, expression) 2125 expressions = ( 2126 self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}" 2127 ) 2128 return f"{this}{expressions}"
2138 def when_sql(self, expression: exp.When) -> str: 2139 matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED" 2140 source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else "" 2141 condition = self.sql(expression, "condition") 2142 condition = f" AND {condition}" if condition else "" 2143 2144 then_expression = expression.args.get("then") 2145 if isinstance(then_expression, exp.Insert): 2146 then = f"INSERT {self.sql(then_expression, 'this')}" 2147 if "expression" in then_expression.args: 2148 then += f" VALUES {self.sql(then_expression, 'expression')}" 2149 elif isinstance(then_expression, exp.Update): 2150 if isinstance(then_expression.args.get("expressions"), exp.Star): 2151 then = f"UPDATE {self.sql(then_expression, 'expressions')}" 2152 else: 2153 then = f"UPDATE SET {self.expressions(then_expression, flat=True)}" 2154 else: 2155 then = self.sql(then_expression) 2156 return f"WHEN {matched}{source}{condition} THEN {then}"