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