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