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