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