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