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