sqlglot.dialects.tsql
1from __future__ import annotations 2 3import datetime 4import re 5import typing as t 6from functools import partial 7 8from sqlglot import exp, generator, parser, tokens, transforms 9from sqlglot.dialects.dialect import ( 10 Dialect, 11 NormalizationStrategy, 12 any_value_to_max_sql, 13 date_delta_sql, 14 datestrtodate_sql, 15 generatedasidentitycolumnconstraint_sql, 16 max_or_greatest, 17 min_or_least, 18 build_date_delta, 19 rename_func, 20 trim_sql, 21 timestrtotime_sql, 22) 23from sqlglot.helper import seq_get 24from sqlglot.time import format_time 25from sqlglot.tokens import TokenType 26 27if t.TYPE_CHECKING: 28 from sqlglot._typing import E 29 30FULL_FORMAT_TIME_MAPPING = { 31 "weekday": "%A", 32 "dw": "%A", 33 "w": "%A", 34 "month": "%B", 35 "mm": "%B", 36 "m": "%B", 37} 38 39DATE_DELTA_INTERVAL = { 40 "year": "year", 41 "yyyy": "year", 42 "yy": "year", 43 "quarter": "quarter", 44 "qq": "quarter", 45 "q": "quarter", 46 "month": "month", 47 "mm": "month", 48 "m": "month", 49 "week": "week", 50 "ww": "week", 51 "wk": "week", 52 "day": "day", 53 "dd": "day", 54 "d": "day", 55} 56 57 58DATE_FMT_RE = re.compile("([dD]{1,2})|([mM]{1,2})|([yY]{1,4})|([hH]{1,2})|([sS]{1,2})") 59 60# N = Numeric, C=Currency 61TRANSPILE_SAFE_NUMBER_FMT = {"N", "C"} 62 63DEFAULT_START_DATE = datetime.date(1900, 1, 1) 64 65BIT_TYPES = {exp.EQ, exp.NEQ, exp.Is, exp.In, exp.Select, exp.Alias} 66 67# Unsupported options: 68# - OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] ) 69# - TABLE HINT 70OPTIONS: parser.OPTIONS_TYPE = { 71 **dict.fromkeys( 72 ( 73 "DISABLE_OPTIMIZED_PLAN_FORCING", 74 "FAST", 75 "IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX", 76 "LABEL", 77 "MAXDOP", 78 "MAXRECURSION", 79 "MAX_GRANT_PERCENT", 80 "MIN_GRANT_PERCENT", 81 "NO_PERFORMANCE_SPOOL", 82 "QUERYTRACEON", 83 "RECOMPILE", 84 ), 85 tuple(), 86 ), 87 "CONCAT": ("UNION",), 88 "DISABLE": ("EXTERNALPUSHDOWN", "SCALEOUTEXECUTION"), 89 "EXPAND": ("VIEWS",), 90 "FORCE": ("EXTERNALPUSHDOWN", "ORDER", "SCALEOUTEXECUTION"), 91 "HASH": ("GROUP", "JOIN", "UNION"), 92 "KEEP": ("PLAN",), 93 "KEEPFIXED": ("PLAN",), 94 "LOOP": ("JOIN",), 95 "MERGE": ("JOIN", "UNION"), 96 "OPTIMIZE": (("FOR", "UNKNOWN"),), 97 "ORDER": ("GROUP",), 98 "PARAMETERIZATION": ("FORCED", "SIMPLE"), 99 "ROBUST": ("PLAN",), 100 "USE": ("PLAN",), 101} 102 103OPTIONS_THAT_REQUIRE_EQUAL = ("MAX_GRANT_PERCENT", "MIN_GRANT_PERCENT", "LABEL") 104 105 106def _build_formatted_time( 107 exp_class: t.Type[E], full_format_mapping: t.Optional[bool] = None 108) -> t.Callable[[t.List], E]: 109 def _builder(args: t.List) -> E: 110 assert len(args) == 2 111 112 return exp_class( 113 this=exp.cast(args[1], exp.DataType.Type.DATETIME), 114 format=exp.Literal.string( 115 format_time( 116 args[0].name.lower(), 117 ( 118 {**TSQL.TIME_MAPPING, **FULL_FORMAT_TIME_MAPPING} 119 if full_format_mapping 120 else TSQL.TIME_MAPPING 121 ), 122 ) 123 ), 124 ) 125 126 return _builder 127 128 129def _build_format(args: t.List) -> exp.NumberToStr | exp.TimeToStr: 130 this = seq_get(args, 0) 131 fmt = seq_get(args, 1) 132 culture = seq_get(args, 2) 133 134 number_fmt = fmt and (fmt.name in TRANSPILE_SAFE_NUMBER_FMT or not DATE_FMT_RE.search(fmt.name)) 135 136 if number_fmt: 137 return exp.NumberToStr(this=this, format=fmt, culture=culture) 138 139 if fmt: 140 fmt = exp.Literal.string( 141 format_time(fmt.name, TSQL.FORMAT_TIME_MAPPING) 142 if len(fmt.name) == 1 143 else format_time(fmt.name, TSQL.TIME_MAPPING) 144 ) 145 146 return exp.TimeToStr(this=this, format=fmt, culture=culture) 147 148 149def _build_eomonth(args: t.List) -> exp.LastDay: 150 date = exp.TsOrDsToDate(this=seq_get(args, 0)) 151 month_lag = seq_get(args, 1) 152 153 if month_lag is None: 154 this: exp.Expression = date 155 else: 156 unit = DATE_DELTA_INTERVAL.get("month") 157 this = exp.DateAdd(this=date, expression=month_lag, unit=unit and exp.var(unit)) 158 159 return exp.LastDay(this=this) 160 161 162def _build_hashbytes(args: t.List) -> exp.Expression: 163 kind, data = args 164 kind = kind.name.upper() if kind.is_string else "" 165 166 if kind == "MD5": 167 args.pop(0) 168 return exp.MD5(this=data) 169 if kind in ("SHA", "SHA1"): 170 args.pop(0) 171 return exp.SHA(this=data) 172 if kind == "SHA2_256": 173 return exp.SHA2(this=data, length=exp.Literal.number(256)) 174 if kind == "SHA2_512": 175 return exp.SHA2(this=data, length=exp.Literal.number(512)) 176 177 return exp.func("HASHBYTES", *args) 178 179 180DATEPART_ONLY_FORMATS = {"DW", "HOUR", "QUARTER"} 181 182 183def _format_sql(self: TSQL.Generator, expression: exp.NumberToStr | exp.TimeToStr) -> str: 184 fmt = expression.args["format"] 185 186 if not isinstance(expression, exp.NumberToStr): 187 if fmt.is_string: 188 mapped_fmt = format_time(fmt.name, TSQL.INVERSE_TIME_MAPPING) 189 190 name = (mapped_fmt or "").upper() 191 if name in DATEPART_ONLY_FORMATS: 192 return self.func("DATEPART", name, expression.this) 193 194 fmt_sql = self.sql(exp.Literal.string(mapped_fmt)) 195 else: 196 fmt_sql = self.format_time(expression) or self.sql(fmt) 197 else: 198 fmt_sql = self.sql(fmt) 199 200 return self.func("FORMAT", expression.this, fmt_sql, expression.args.get("culture")) 201 202 203def _string_agg_sql(self: TSQL.Generator, expression: exp.GroupConcat) -> str: 204 this = expression.this 205 distinct = expression.find(exp.Distinct) 206 if distinct: 207 # exp.Distinct can appear below an exp.Order or an exp.GroupConcat expression 208 self.unsupported("T-SQL STRING_AGG doesn't support DISTINCT.") 209 this = distinct.pop().expressions[0] 210 211 order = "" 212 if isinstance(expression.this, exp.Order): 213 if expression.this.this: 214 this = expression.this.this.pop() 215 # Order has a leading space 216 order = f" WITHIN GROUP ({self.sql(expression.this)[1:]})" 217 218 separator = expression.args.get("separator") or exp.Literal.string(",") 219 return f"STRING_AGG({self.format_args(this, separator)}){order}" 220 221 222def _build_date_delta( 223 exp_class: t.Type[E], unit_mapping: t.Optional[t.Dict[str, str]] = None 224) -> t.Callable[[t.List], E]: 225 def _builder(args: t.List) -> E: 226 unit = seq_get(args, 0) 227 if unit and unit_mapping: 228 unit = exp.var(unit_mapping.get(unit.name.lower(), unit.name)) 229 230 start_date = seq_get(args, 1) 231 if start_date and start_date.is_number: 232 # Numeric types are valid DATETIME values 233 if start_date.is_int: 234 adds = DEFAULT_START_DATE + datetime.timedelta(days=int(start_date.this)) 235 start_date = exp.Literal.string(adds.strftime("%F")) 236 else: 237 # We currently don't handle float values, i.e. they're not converted to equivalent DATETIMEs. 238 # This is not a problem when generating T-SQL code, it is when transpiling to other dialects. 239 return exp_class(this=seq_get(args, 2), expression=start_date, unit=unit) 240 241 return exp_class( 242 this=exp.TimeStrToTime(this=seq_get(args, 2)), 243 expression=exp.TimeStrToTime(this=start_date), 244 unit=unit, 245 ) 246 247 return _builder 248 249 250def qualify_derived_table_outputs(expression: exp.Expression) -> exp.Expression: 251 """Ensures all (unnamed) output columns are aliased for CTEs and Subqueries.""" 252 alias = expression.args.get("alias") 253 254 if ( 255 isinstance(expression, (exp.CTE, exp.Subquery)) 256 and isinstance(alias, exp.TableAlias) 257 and not alias.columns 258 ): 259 from sqlglot.optimizer.qualify_columns import qualify_outputs 260 261 # We keep track of the unaliased column projection indexes instead of the expressions 262 # themselves, because the latter are going to be replaced by new nodes when the aliases 263 # are added and hence we won't be able to reach these newly added Alias parents 264 query = expression.this 265 unaliased_column_indexes = ( 266 i for i, c in enumerate(query.selects) if isinstance(c, exp.Column) and not c.alias 267 ) 268 269 qualify_outputs(query) 270 271 # Preserve the quoting information of columns for newly added Alias nodes 272 query_selects = query.selects 273 for select_index in unaliased_column_indexes: 274 alias = query_selects[select_index] 275 column = alias.this 276 if isinstance(column.this, exp.Identifier): 277 alias.args["alias"].set("quoted", column.this.quoted) 278 279 return expression 280 281 282# https://learn.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver16#syntax 283def _build_datetimefromparts(args: t.List) -> exp.TimestampFromParts: 284 return exp.TimestampFromParts( 285 year=seq_get(args, 0), 286 month=seq_get(args, 1), 287 day=seq_get(args, 2), 288 hour=seq_get(args, 3), 289 min=seq_get(args, 4), 290 sec=seq_get(args, 5), 291 milli=seq_get(args, 6), 292 ) 293 294 295# https://learn.microsoft.com/en-us/sql/t-sql/functions/timefromparts-transact-sql?view=sql-server-ver16#syntax 296def _build_timefromparts(args: t.List) -> exp.TimeFromParts: 297 return exp.TimeFromParts( 298 hour=seq_get(args, 0), 299 min=seq_get(args, 1), 300 sec=seq_get(args, 2), 301 fractions=seq_get(args, 3), 302 precision=seq_get(args, 4), 303 ) 304 305 306def _build_with_arg_as_text( 307 klass: t.Type[exp.Expression], 308) -> t.Callable[[t.List[exp.Expression]], exp.Expression]: 309 def _parse(args: t.List[exp.Expression]) -> exp.Expression: 310 this = seq_get(args, 0) 311 312 if this and not this.is_string: 313 this = exp.cast(this, exp.DataType.Type.TEXT) 314 315 expression = seq_get(args, 1) 316 kwargs = {"this": this} 317 318 if expression: 319 kwargs["expression"] = expression 320 321 return klass(**kwargs) 322 323 return _parse 324 325 326def _build_json_query(args: t.List, dialect: Dialect) -> exp.JSONExtract: 327 if len(args) == 1: 328 # The default value for path is '$'. As a result, if you don't provide a 329 # value for path, JSON_QUERY returns the input expression. 330 args.append(exp.Literal.string("$")) 331 332 return parser.build_extract_json_with_path(exp.JSONExtract)(args, dialect) 333 334 335def _json_extract_sql( 336 self: TSQL.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar 337) -> str: 338 json_query = self.func("JSON_QUERY", expression.this, expression.expression) 339 json_value = self.func("JSON_VALUE", expression.this, expression.expression) 340 return self.func("ISNULL", json_query, json_value) 341 342 343def _timestrtotime_sql(self: TSQL.Generator, expression: exp.TimeStrToTime): 344 sql = timestrtotime_sql(self, expression) 345 if expression.args.get("zone"): 346 # If there is a timezone, produce an expression like: 347 # CAST('2020-01-01 12:13:14-08:00' AS DATETIMEOFFSET) AT TIME ZONE 'UTC' 348 # If you dont have AT TIME ZONE 'UTC', wrapping that expression in another cast back to DATETIME2 just drops the timezone information 349 return self.sql(exp.AtTimeZone(this=sql, zone=exp.Literal.string("UTC"))) 350 return sql 351 352 353class TSQL(Dialect): 354 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 355 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 356 SUPPORTS_SEMI_ANTI_JOIN = False 357 LOG_BASE_FIRST = False 358 TYPED_DIVISION = True 359 CONCAT_COALESCE = True 360 361 TIME_MAPPING = { 362 "year": "%Y", 363 "dayofyear": "%j", 364 "day": "%d", 365 "dy": "%d", 366 "y": "%Y", 367 "week": "%W", 368 "ww": "%W", 369 "wk": "%W", 370 "hour": "%h", 371 "hh": "%I", 372 "minute": "%M", 373 "mi": "%M", 374 "n": "%M", 375 "second": "%S", 376 "ss": "%S", 377 "s": "%-S", 378 "millisecond": "%f", 379 "ms": "%f", 380 "weekday": "%W", 381 "dw": "%W", 382 "month": "%m", 383 "mm": "%M", 384 "m": "%-M", 385 "Y": "%Y", 386 "YYYY": "%Y", 387 "YY": "%y", 388 "MMMM": "%B", 389 "MMM": "%b", 390 "MM": "%m", 391 "M": "%-m", 392 "dddd": "%A", 393 "dd": "%d", 394 "d": "%-d", 395 "HH": "%H", 396 "H": "%-H", 397 "h": "%-I", 398 "S": "%f", 399 "yyyy": "%Y", 400 "yy": "%y", 401 } 402 403 CONVERT_FORMAT_MAPPING = { 404 "0": "%b %d %Y %-I:%M%p", 405 "1": "%m/%d/%y", 406 "2": "%y.%m.%d", 407 "3": "%d/%m/%y", 408 "4": "%d.%m.%y", 409 "5": "%d-%m-%y", 410 "6": "%d %b %y", 411 "7": "%b %d, %y", 412 "8": "%H:%M:%S", 413 "9": "%b %d %Y %-I:%M:%S:%f%p", 414 "10": "mm-dd-yy", 415 "11": "yy/mm/dd", 416 "12": "yymmdd", 417 "13": "%d %b %Y %H:%M:ss:%f", 418 "14": "%H:%M:%S:%f", 419 "20": "%Y-%m-%d %H:%M:%S", 420 "21": "%Y-%m-%d %H:%M:%S.%f", 421 "22": "%m/%d/%y %-I:%M:%S %p", 422 "23": "%Y-%m-%d", 423 "24": "%H:%M:%S", 424 "25": "%Y-%m-%d %H:%M:%S.%f", 425 "100": "%b %d %Y %-I:%M%p", 426 "101": "%m/%d/%Y", 427 "102": "%Y.%m.%d", 428 "103": "%d/%m/%Y", 429 "104": "%d.%m.%Y", 430 "105": "%d-%m-%Y", 431 "106": "%d %b %Y", 432 "107": "%b %d, %Y", 433 "108": "%H:%M:%S", 434 "109": "%b %d %Y %-I:%M:%S:%f%p", 435 "110": "%m-%d-%Y", 436 "111": "%Y/%m/%d", 437 "112": "%Y%m%d", 438 "113": "%d %b %Y %H:%M:%S:%f", 439 "114": "%H:%M:%S:%f", 440 "120": "%Y-%m-%d %H:%M:%S", 441 "121": "%Y-%m-%d %H:%M:%S.%f", 442 } 443 444 FORMAT_TIME_MAPPING = { 445 "y": "%B %Y", 446 "d": "%m/%d/%Y", 447 "H": "%-H", 448 "h": "%-I", 449 "s": "%Y-%m-%d %H:%M:%S", 450 "D": "%A,%B,%Y", 451 "f": "%A,%B,%Y %-I:%M %p", 452 "F": "%A,%B,%Y %-I:%M:%S %p", 453 "g": "%m/%d/%Y %-I:%M %p", 454 "G": "%m/%d/%Y %-I:%M:%S %p", 455 "M": "%B %-d", 456 "m": "%B %-d", 457 "O": "%Y-%m-%dT%H:%M:%S", 458 "u": "%Y-%M-%D %H:%M:%S%z", 459 "U": "%A, %B %D, %Y %H:%M:%S%z", 460 "T": "%-I:%M:%S %p", 461 "t": "%-I:%M", 462 "Y": "%a %Y", 463 } 464 465 class Tokenizer(tokens.Tokenizer): 466 IDENTIFIERS = [("[", "]"), '"'] 467 QUOTES = ["'", '"'] 468 HEX_STRINGS = [("0x", ""), ("0X", "")] 469 VAR_SINGLE_TOKENS = {"@", "$", "#"} 470 471 KEYWORDS = { 472 **tokens.Tokenizer.KEYWORDS, 473 "CLUSTERED INDEX": TokenType.INDEX, 474 "DATETIME2": TokenType.DATETIME, 475 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 476 "DECLARE": TokenType.DECLARE, 477 "EXEC": TokenType.COMMAND, 478 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 479 "IMAGE": TokenType.IMAGE, 480 "MONEY": TokenType.MONEY, 481 "NONCLUSTERED INDEX": TokenType.INDEX, 482 "NTEXT": TokenType.TEXT, 483 "OPTION": TokenType.OPTION, 484 "OUTPUT": TokenType.RETURNING, 485 "PRINT": TokenType.COMMAND, 486 "PROC": TokenType.PROCEDURE, 487 "REAL": TokenType.FLOAT, 488 "ROWVERSION": TokenType.ROWVERSION, 489 "SMALLDATETIME": TokenType.DATETIME, 490 "SMALLMONEY": TokenType.SMALLMONEY, 491 "SQL_VARIANT": TokenType.VARIANT, 492 "SYSTEM_USER": TokenType.CURRENT_USER, 493 "TOP": TokenType.TOP, 494 "TIMESTAMP": TokenType.ROWVERSION, 495 "TINYINT": TokenType.UTINYINT, 496 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 497 "UPDATE STATISTICS": TokenType.COMMAND, 498 "XML": TokenType.XML, 499 } 500 KEYWORDS.pop("/*+") 501 502 COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END} 503 504 class Parser(parser.Parser): 505 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 506 LOG_DEFAULTS_TO_LN = True 507 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 508 STRING_ALIASES = True 509 NO_PAREN_IF_COMMANDS = False 510 511 QUERY_MODIFIER_PARSERS = { 512 **parser.Parser.QUERY_MODIFIER_PARSERS, 513 TokenType.OPTION: lambda self: ("options", self._parse_options()), 514 } 515 516 FUNCTIONS = { 517 **parser.Parser.FUNCTIONS, 518 "CHARINDEX": lambda args: exp.StrPosition( 519 this=seq_get(args, 1), 520 substr=seq_get(args, 0), 521 position=seq_get(args, 2), 522 ), 523 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 524 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 525 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 526 "DATEPART": _build_formatted_time(exp.TimeToStr), 527 "DATETIMEFROMPARTS": _build_datetimefromparts, 528 "EOMONTH": _build_eomonth, 529 "FORMAT": _build_format, 530 "GETDATE": exp.CurrentTimestamp.from_arg_list, 531 "HASHBYTES": _build_hashbytes, 532 "ISNULL": exp.Coalesce.from_arg_list, 533 "JSON_QUERY": _build_json_query, 534 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 535 "LEN": _build_with_arg_as_text(exp.Length), 536 "LEFT": _build_with_arg_as_text(exp.Left), 537 "RIGHT": _build_with_arg_as_text(exp.Right), 538 "REPLICATE": exp.Repeat.from_arg_list, 539 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 540 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 541 "SUSER_NAME": exp.CurrentUser.from_arg_list, 542 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 543 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 544 "TIMEFROMPARTS": _build_timefromparts, 545 } 546 547 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 548 549 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 550 TokenType.TABLE, 551 *parser.Parser.TYPE_TOKENS, 552 } 553 554 STATEMENT_PARSERS = { 555 **parser.Parser.STATEMENT_PARSERS, 556 TokenType.DECLARE: lambda self: self._parse_declare(), 557 } 558 559 RANGE_PARSERS = { 560 **parser.Parser.RANGE_PARSERS, 561 TokenType.DCOLON: lambda self, this: self.expression( 562 exp.ScopeResolution, 563 this=this, 564 expression=self._parse_function() or self._parse_var(any_token=True), 565 ), 566 } 567 568 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 569 COLUMN_OPERATORS = { 570 **parser.Parser.COLUMN_OPERATORS, 571 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 572 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 573 else self.expression(exp.ScopeResolution, this=this, expression=to), 574 } 575 576 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 577 # We want to use _parse_types() if the first token after :: is a known type, 578 # otherwise we could parse something like x::varchar(max) into a function 579 if self._match_set(self.TYPE_TOKENS, advance=False): 580 return self._parse_types() 581 582 return self._parse_function() or self._parse_types() 583 584 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 585 if not self._match(TokenType.OPTION): 586 return None 587 588 def _parse_option() -> t.Optional[exp.Expression]: 589 option = self._parse_var_from_options(OPTIONS) 590 if not option: 591 return None 592 593 self._match(TokenType.EQ) 594 return self.expression( 595 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 596 ) 597 598 return self._parse_wrapped_csv(_parse_option) 599 600 def _parse_projections(self) -> t.List[exp.Expression]: 601 """ 602 T-SQL supports the syntax alias = expression in the SELECT's projection list, 603 so we transform all parsed Selects to convert their EQ projections into Aliases. 604 605 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 606 """ 607 return [ 608 ( 609 exp.alias_(projection.expression, projection.this.this, copy=False) 610 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 611 else projection 612 ) 613 for projection in super()._parse_projections() 614 ] 615 616 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 617 """Applies to SQL Server and Azure SQL Database 618 COMMIT [ { TRAN | TRANSACTION } 619 [ transaction_name | @tran_name_variable ] ] 620 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 621 622 ROLLBACK { TRAN | TRANSACTION } 623 [ transaction_name | @tran_name_variable 624 | savepoint_name | @savepoint_variable ] 625 """ 626 rollback = self._prev.token_type == TokenType.ROLLBACK 627 628 self._match_texts(("TRAN", "TRANSACTION")) 629 this = self._parse_id_var() 630 631 if rollback: 632 return self.expression(exp.Rollback, this=this) 633 634 durability = None 635 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 636 self._match_text_seq("DELAYED_DURABILITY") 637 self._match(TokenType.EQ) 638 639 if self._match_text_seq("OFF"): 640 durability = False 641 else: 642 self._match(TokenType.ON) 643 durability = True 644 645 self._match_r_paren() 646 647 return self.expression(exp.Commit, this=this, durability=durability) 648 649 def _parse_transaction(self) -> exp.Transaction | exp.Command: 650 """Applies to SQL Server and Azure SQL Database 651 BEGIN { TRAN | TRANSACTION } 652 [ { transaction_name | @tran_name_variable } 653 [ WITH MARK [ 'description' ] ] 654 ] 655 """ 656 if self._match_texts(("TRAN", "TRANSACTION")): 657 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 658 if self._match_text_seq("WITH", "MARK"): 659 transaction.set("mark", self._parse_string()) 660 661 return transaction 662 663 return self._parse_as_command(self._prev) 664 665 def _parse_returns(self) -> exp.ReturnsProperty: 666 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 667 returns = super()._parse_returns() 668 returns.set("table", table) 669 return returns 670 671 def _parse_convert( 672 self, strict: bool, safe: t.Optional[bool] = None 673 ) -> t.Optional[exp.Expression]: 674 this = self._parse_types() 675 self._match(TokenType.COMMA) 676 args = [this, *self._parse_csv(self._parse_assignment)] 677 convert = exp.Convert.from_arg_list(args) 678 convert.set("safe", safe) 679 convert.set("strict", strict) 680 return convert 681 682 def _parse_user_defined_function( 683 self, kind: t.Optional[TokenType] = None 684 ) -> t.Optional[exp.Expression]: 685 this = super()._parse_user_defined_function(kind=kind) 686 687 if ( 688 kind == TokenType.FUNCTION 689 or isinstance(this, exp.UserDefinedFunction) 690 or self._match(TokenType.ALIAS, advance=False) 691 ): 692 return this 693 694 expressions = self._parse_csv(self._parse_function_parameter) 695 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 696 697 def _parse_id_var( 698 self, 699 any_token: bool = True, 700 tokens: t.Optional[t.Collection[TokenType]] = None, 701 ) -> t.Optional[exp.Expression]: 702 is_temporary = self._match(TokenType.HASH) 703 is_global = is_temporary and self._match(TokenType.HASH) 704 705 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 706 if this: 707 if is_global: 708 this.set("global", True) 709 elif is_temporary: 710 this.set("temporary", True) 711 712 return this 713 714 def _parse_create(self) -> exp.Create | exp.Command: 715 create = super()._parse_create() 716 717 if isinstance(create, exp.Create): 718 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 719 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 720 if not create.args.get("properties"): 721 create.set("properties", exp.Properties(expressions=[])) 722 723 create.args["properties"].append("expressions", exp.TemporaryProperty()) 724 725 return create 726 727 def _parse_if(self) -> t.Optional[exp.Expression]: 728 index = self._index 729 730 if self._match_text_seq("OBJECT_ID"): 731 self._parse_wrapped_csv(self._parse_string) 732 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 733 return self._parse_drop(exists=True) 734 self._retreat(index) 735 736 return super()._parse_if() 737 738 def _parse_unique(self) -> exp.UniqueColumnConstraint: 739 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 740 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 741 else: 742 this = self._parse_schema(self._parse_id_var(any_token=False)) 743 744 return self.expression(exp.UniqueColumnConstraint, this=this) 745 746 def _parse_partition(self) -> t.Optional[exp.Partition]: 747 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 748 return None 749 750 def parse_range(): 751 low = self._parse_bitwise() 752 high = self._parse_bitwise() if self._match_text_seq("TO") else None 753 754 return ( 755 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 756 ) 757 758 partition = self.expression( 759 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 760 ) 761 762 self._match_r_paren() 763 764 return partition 765 766 def _parse_declare(self) -> exp.Declare | exp.Command: 767 index = self._index 768 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 769 770 if not expressions or self._curr: 771 self._retreat(index) 772 return self._parse_as_command(self._prev) 773 774 return self.expression(exp.Declare, expressions=expressions) 775 776 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 777 var = self._parse_id_var() 778 if not var: 779 return None 780 781 value = None 782 self._match(TokenType.ALIAS) 783 if self._match(TokenType.TABLE): 784 data_type = self._parse_schema() 785 else: 786 data_type = self._parse_types() 787 if self._match(TokenType.EQ): 788 value = self._parse_bitwise() 789 790 return self.expression(exp.DeclareItem, this=var, kind=data_type, default=value) 791 792 class Generator(generator.Generator): 793 LIMIT_IS_TOP = True 794 QUERY_HINTS = False 795 RETURNING_END = False 796 NVL2_SUPPORTED = False 797 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 798 LIMIT_FETCH = "FETCH" 799 COMPUTED_COLUMN_WITH_TYPE = False 800 CTE_RECURSIVE_KEYWORD_REQUIRED = False 801 ENSURE_BOOLS = True 802 NULL_ORDERING_SUPPORTED = None 803 SUPPORTS_SINGLE_ARG_CONCAT = False 804 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 805 SUPPORTS_SELECT_INTO = True 806 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 807 SUPPORTS_TO_NUMBER = False 808 SET_OP_MODIFIERS = False 809 COPY_PARAMS_EQ_REQUIRED = True 810 PARSE_JSON_NAME = None 811 812 EXPRESSIONS_WITHOUT_NESTED_CTES = { 813 exp.Create, 814 exp.Delete, 815 exp.Insert, 816 exp.Intersect, 817 exp.Except, 818 exp.Merge, 819 exp.Select, 820 exp.Subquery, 821 exp.Union, 822 exp.Update, 823 } 824 825 SUPPORTED_JSON_PATH_PARTS = { 826 exp.JSONPathKey, 827 exp.JSONPathRoot, 828 exp.JSONPathSubscript, 829 } 830 831 TYPE_MAPPING = { 832 **generator.Generator.TYPE_MAPPING, 833 exp.DataType.Type.BOOLEAN: "BIT", 834 exp.DataType.Type.DECIMAL: "NUMERIC", 835 exp.DataType.Type.DATETIME: "DATETIME2", 836 exp.DataType.Type.DOUBLE: "FLOAT", 837 exp.DataType.Type.INT: "INTEGER", 838 exp.DataType.Type.ROWVERSION: "ROWVERSION", 839 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 840 exp.DataType.Type.TIMESTAMP: "DATETIME2", 841 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 842 exp.DataType.Type.UTINYINT: "TINYINT", 843 exp.DataType.Type.VARIANT: "SQL_VARIANT", 844 } 845 846 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 847 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 848 849 TRANSFORMS = { 850 **generator.Generator.TRANSFORMS, 851 exp.AnyValue: any_value_to_max_sql, 852 exp.ArrayToString: rename_func("STRING_AGG"), 853 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 854 exp.DateAdd: date_delta_sql("DATEADD"), 855 exp.DateDiff: date_delta_sql("DATEDIFF"), 856 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 857 exp.CurrentDate: rename_func("GETDATE"), 858 exp.CurrentTimestamp: rename_func("GETDATE"), 859 exp.DateStrToDate: datestrtodate_sql, 860 exp.Extract: rename_func("DATEPART"), 861 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 862 exp.GroupConcat: _string_agg_sql, 863 exp.If: rename_func("IIF"), 864 exp.JSONExtract: _json_extract_sql, 865 exp.JSONExtractScalar: _json_extract_sql, 866 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 867 exp.Max: max_or_greatest, 868 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 869 exp.Min: min_or_least, 870 exp.NumberToStr: _format_sql, 871 exp.Repeat: rename_func("REPLICATE"), 872 exp.Select: transforms.preprocess( 873 [ 874 transforms.eliminate_distinct_on, 875 transforms.eliminate_semi_and_anti_joins, 876 transforms.eliminate_qualify, 877 transforms.unnest_generate_date_array_using_recursive_cte, 878 ] 879 ), 880 exp.Stddev: rename_func("STDEV"), 881 exp.StrPosition: lambda self, e: self.func( 882 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 883 ), 884 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 885 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 886 exp.SHA2: lambda self, e: self.func( 887 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 888 ), 889 exp.TemporaryProperty: lambda self, e: "", 890 exp.TimeStrToTime: _timestrtotime_sql, 891 exp.TimeToStr: _format_sql, 892 exp.Trim: trim_sql, 893 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 894 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 895 } 896 897 TRANSFORMS.pop(exp.ReturnsProperty) 898 899 PROPERTIES_LOCATION = { 900 **generator.Generator.PROPERTIES_LOCATION, 901 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 902 } 903 904 def scope_resolution(self, rhs: str, scope_name: str) -> str: 905 return f"{scope_name}::{rhs}" 906 907 def select_sql(self, expression: exp.Select) -> str: 908 if expression.args.get("offset"): 909 if not expression.args.get("order"): 910 # ORDER BY is required in order to use OFFSET in a query, so we use 911 # a noop order by, since we don't really care about the order. 912 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 913 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 914 915 limit = expression.args.get("limit") 916 if isinstance(limit, exp.Limit): 917 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 918 # we replace here because otherwise TOP would be generated in select_sql 919 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 920 921 return super().select_sql(expression) 922 923 def convert_sql(self, expression: exp.Convert) -> str: 924 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 925 return self.func( 926 name, expression.this, expression.expression, expression.args.get("style") 927 ) 928 929 def queryoption_sql(self, expression: exp.QueryOption) -> str: 930 option = self.sql(expression, "this") 931 value = self.sql(expression, "expression") 932 if value: 933 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 934 return f"{option} {optional_equal_sign}{value}" 935 return option 936 937 def lateral_op(self, expression: exp.Lateral) -> str: 938 cross_apply = expression.args.get("cross_apply") 939 if cross_apply is True: 940 return "CROSS APPLY" 941 if cross_apply is False: 942 return "OUTER APPLY" 943 944 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 945 self.unsupported("LATERAL clause is not supported.") 946 return "LATERAL" 947 948 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 949 nano = expression.args.get("nano") 950 if nano is not None: 951 nano.pop() 952 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 953 954 if expression.args.get("fractions") is None: 955 expression.set("fractions", exp.Literal.number(0)) 956 if expression.args.get("precision") is None: 957 expression.set("precision", exp.Literal.number(0)) 958 959 return rename_func("TIMEFROMPARTS")(self, expression) 960 961 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 962 zone = expression.args.get("zone") 963 if zone is not None: 964 zone.pop() 965 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 966 967 nano = expression.args.get("nano") 968 if nano is not None: 969 nano.pop() 970 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 971 972 if expression.args.get("milli") is None: 973 expression.set("milli", exp.Literal.number(0)) 974 975 return rename_func("DATETIMEFROMPARTS")(self, expression) 976 977 def setitem_sql(self, expression: exp.SetItem) -> str: 978 this = expression.this 979 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 980 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 981 return f"{self.sql(this.left)} {self.sql(this.right)}" 982 983 return super().setitem_sql(expression) 984 985 def boolean_sql(self, expression: exp.Boolean) -> str: 986 if type(expression.parent) in BIT_TYPES: 987 return "1" if expression.this else "0" 988 989 return "(1 = 1)" if expression.this else "(1 = 0)" 990 991 def is_sql(self, expression: exp.Is) -> str: 992 if isinstance(expression.expression, exp.Boolean): 993 return self.binary(expression, "=") 994 return self.binary(expression, "IS") 995 996 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 997 sql = self.sql(expression, "this") 998 properties = expression.args.get("properties") 999 1000 if sql[:1] != "#" and any( 1001 isinstance(prop, exp.TemporaryProperty) 1002 for prop in (properties.expressions if properties else []) 1003 ): 1004 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1005 1006 return sql 1007 1008 def create_sql(self, expression: exp.Create) -> str: 1009 kind = expression.kind 1010 exists = expression.args.pop("exists", None) 1011 1012 like_property = expression.find(exp.LikeProperty) 1013 if like_property: 1014 ctas_expression = like_property.this 1015 else: 1016 ctas_expression = expression.expression 1017 1018 if kind == "VIEW": 1019 expression.this.set("catalog", None) 1020 with_ = expression.args.get("with") 1021 if ctas_expression and with_: 1022 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1023 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1024 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1025 ctas_expression.set("with", with_.pop()) 1026 1027 sql = super().create_sql(expression) 1028 1029 table = expression.find(exp.Table) 1030 1031 # Convert CTAS statement to SELECT .. INTO .. 1032 if kind == "TABLE" and ctas_expression: 1033 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1034 ctas_expression = ctas_expression.subquery() 1035 1036 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1037 select_into.set("into", exp.Into(this=table)) 1038 1039 if like_property: 1040 select_into.limit(0, copy=False) 1041 1042 sql = self.sql(select_into) 1043 1044 if exists: 1045 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1046 sql = self.sql(exp.Literal.string(sql)) 1047 if kind == "SCHEMA": 1048 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1049 elif kind == "TABLE": 1050 assert table 1051 where = exp.and_( 1052 exp.column("table_name").eq(table.name), 1053 exp.column("table_schema").eq(table.db) if table.db else None, 1054 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1055 ) 1056 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1057 elif kind == "INDEX": 1058 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1059 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1060 elif expression.args.get("replace"): 1061 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1062 1063 return self.prepend_ctes(expression, sql) 1064 1065 def offset_sql(self, expression: exp.Offset) -> str: 1066 return f"{super().offset_sql(expression)} ROWS" 1067 1068 def version_sql(self, expression: exp.Version) -> str: 1069 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1070 this = f"FOR {name}" 1071 expr = expression.expression 1072 kind = expression.text("kind") 1073 if kind in ("FROM", "BETWEEN"): 1074 args = expr.expressions 1075 sep = "TO" if kind == "FROM" else "AND" 1076 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1077 else: 1078 expr_sql = self.sql(expr) 1079 1080 expr_sql = f" {expr_sql}" if expr_sql else "" 1081 return f"{this} {kind}{expr_sql}" 1082 1083 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1084 table = expression.args.get("table") 1085 table = f"{table} " if table else "" 1086 return f"RETURNS {table}{self.sql(expression, 'this')}" 1087 1088 def returning_sql(self, expression: exp.Returning) -> str: 1089 into = self.sql(expression, "into") 1090 into = self.seg(f"INTO {into}") if into else "" 1091 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1092 1093 def transaction_sql(self, expression: exp.Transaction) -> str: 1094 this = self.sql(expression, "this") 1095 this = f" {this}" if this else "" 1096 mark = self.sql(expression, "mark") 1097 mark = f" WITH MARK {mark}" if mark else "" 1098 return f"BEGIN TRANSACTION{this}{mark}" 1099 1100 def commit_sql(self, expression: exp.Commit) -> str: 1101 this = self.sql(expression, "this") 1102 this = f" {this}" if this else "" 1103 durability = expression.args.get("durability") 1104 durability = ( 1105 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1106 if durability is not None 1107 else "" 1108 ) 1109 return f"COMMIT TRANSACTION{this}{durability}" 1110 1111 def rollback_sql(self, expression: exp.Rollback) -> str: 1112 this = self.sql(expression, "this") 1113 this = f" {this}" if this else "" 1114 return f"ROLLBACK TRANSACTION{this}" 1115 1116 def identifier_sql(self, expression: exp.Identifier) -> str: 1117 identifier = super().identifier_sql(expression) 1118 1119 if expression.args.get("global"): 1120 identifier = f"##{identifier}" 1121 elif expression.args.get("temporary"): 1122 identifier = f"#{identifier}" 1123 1124 return identifier 1125 1126 def constraint_sql(self, expression: exp.Constraint) -> str: 1127 this = self.sql(expression, "this") 1128 expressions = self.expressions(expression, flat=True, sep=" ") 1129 return f"CONSTRAINT {this} {expressions}" 1130 1131 def length_sql(self, expression: exp.Length) -> str: 1132 return self._uncast_text(expression, "LEN") 1133 1134 def right_sql(self, expression: exp.Right) -> str: 1135 return self._uncast_text(expression, "RIGHT") 1136 1137 def left_sql(self, expression: exp.Left) -> str: 1138 return self._uncast_text(expression, "LEFT") 1139 1140 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1141 this = expression.this 1142 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1143 this_sql = self.sql(this, "this") 1144 else: 1145 this_sql = self.sql(this) 1146 expression_sql = self.sql(expression, "expression") 1147 return self.func(name, this_sql, expression_sql if expression_sql else None) 1148 1149 def partition_sql(self, expression: exp.Partition) -> str: 1150 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1151 1152 def alter_sql(self, expression: exp.Alter) -> str: 1153 action = seq_get(expression.args.get("actions") or [], 0) 1154 if isinstance(action, exp.RenameTable): 1155 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1156 return super().alter_sql(expression) 1157 1158 def drop_sql(self, expression: exp.Drop) -> str: 1159 if expression.args["kind"] == "VIEW": 1160 expression.this.set("catalog", None) 1161 return super().drop_sql(expression) 1162 1163 def declare_sql(self, expression: exp.Declare) -> str: 1164 return f"DECLARE {self.expressions(expression, flat=True)}" 1165 1166 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1167 variable = self.sql(expression, "this") 1168 default = self.sql(expression, "default") 1169 default = f" = {default}" if default else "" 1170 1171 kind = self.sql(expression, "kind") 1172 if isinstance(expression.args.get("kind"), exp.Schema): 1173 kind = f"TABLE {kind}" 1174 1175 return f"{variable} AS {kind}{default}" 1176 1177 def options_modifier(self, expression: exp.Expression) -> str: 1178 options = self.expressions(expression, key="options") 1179 return f" OPTION{self.wrap(options)}" if options else ""
251def qualify_derived_table_outputs(expression: exp.Expression) -> exp.Expression: 252 """Ensures all (unnamed) output columns are aliased for CTEs and Subqueries.""" 253 alias = expression.args.get("alias") 254 255 if ( 256 isinstance(expression, (exp.CTE, exp.Subquery)) 257 and isinstance(alias, exp.TableAlias) 258 and not alias.columns 259 ): 260 from sqlglot.optimizer.qualify_columns import qualify_outputs 261 262 # We keep track of the unaliased column projection indexes instead of the expressions 263 # themselves, because the latter are going to be replaced by new nodes when the aliases 264 # are added and hence we won't be able to reach these newly added Alias parents 265 query = expression.this 266 unaliased_column_indexes = ( 267 i for i, c in enumerate(query.selects) if isinstance(c, exp.Column) and not c.alias 268 ) 269 270 qualify_outputs(query) 271 272 # Preserve the quoting information of columns for newly added Alias nodes 273 query_selects = query.selects 274 for select_index in unaliased_column_indexes: 275 alias = query_selects[select_index] 276 column = alias.this 277 if isinstance(column.this, exp.Identifier): 278 alias.args["alias"].set("quoted", column.this.quoted) 279 280 return expression
Ensures all (unnamed) output columns are aliased for CTEs and Subqueries.
354class TSQL(Dialect): 355 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 356 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 357 SUPPORTS_SEMI_ANTI_JOIN = False 358 LOG_BASE_FIRST = False 359 TYPED_DIVISION = True 360 CONCAT_COALESCE = True 361 362 TIME_MAPPING = { 363 "year": "%Y", 364 "dayofyear": "%j", 365 "day": "%d", 366 "dy": "%d", 367 "y": "%Y", 368 "week": "%W", 369 "ww": "%W", 370 "wk": "%W", 371 "hour": "%h", 372 "hh": "%I", 373 "minute": "%M", 374 "mi": "%M", 375 "n": "%M", 376 "second": "%S", 377 "ss": "%S", 378 "s": "%-S", 379 "millisecond": "%f", 380 "ms": "%f", 381 "weekday": "%W", 382 "dw": "%W", 383 "month": "%m", 384 "mm": "%M", 385 "m": "%-M", 386 "Y": "%Y", 387 "YYYY": "%Y", 388 "YY": "%y", 389 "MMMM": "%B", 390 "MMM": "%b", 391 "MM": "%m", 392 "M": "%-m", 393 "dddd": "%A", 394 "dd": "%d", 395 "d": "%-d", 396 "HH": "%H", 397 "H": "%-H", 398 "h": "%-I", 399 "S": "%f", 400 "yyyy": "%Y", 401 "yy": "%y", 402 } 403 404 CONVERT_FORMAT_MAPPING = { 405 "0": "%b %d %Y %-I:%M%p", 406 "1": "%m/%d/%y", 407 "2": "%y.%m.%d", 408 "3": "%d/%m/%y", 409 "4": "%d.%m.%y", 410 "5": "%d-%m-%y", 411 "6": "%d %b %y", 412 "7": "%b %d, %y", 413 "8": "%H:%M:%S", 414 "9": "%b %d %Y %-I:%M:%S:%f%p", 415 "10": "mm-dd-yy", 416 "11": "yy/mm/dd", 417 "12": "yymmdd", 418 "13": "%d %b %Y %H:%M:ss:%f", 419 "14": "%H:%M:%S:%f", 420 "20": "%Y-%m-%d %H:%M:%S", 421 "21": "%Y-%m-%d %H:%M:%S.%f", 422 "22": "%m/%d/%y %-I:%M:%S %p", 423 "23": "%Y-%m-%d", 424 "24": "%H:%M:%S", 425 "25": "%Y-%m-%d %H:%M:%S.%f", 426 "100": "%b %d %Y %-I:%M%p", 427 "101": "%m/%d/%Y", 428 "102": "%Y.%m.%d", 429 "103": "%d/%m/%Y", 430 "104": "%d.%m.%Y", 431 "105": "%d-%m-%Y", 432 "106": "%d %b %Y", 433 "107": "%b %d, %Y", 434 "108": "%H:%M:%S", 435 "109": "%b %d %Y %-I:%M:%S:%f%p", 436 "110": "%m-%d-%Y", 437 "111": "%Y/%m/%d", 438 "112": "%Y%m%d", 439 "113": "%d %b %Y %H:%M:%S:%f", 440 "114": "%H:%M:%S:%f", 441 "120": "%Y-%m-%d %H:%M:%S", 442 "121": "%Y-%m-%d %H:%M:%S.%f", 443 } 444 445 FORMAT_TIME_MAPPING = { 446 "y": "%B %Y", 447 "d": "%m/%d/%Y", 448 "H": "%-H", 449 "h": "%-I", 450 "s": "%Y-%m-%d %H:%M:%S", 451 "D": "%A,%B,%Y", 452 "f": "%A,%B,%Y %-I:%M %p", 453 "F": "%A,%B,%Y %-I:%M:%S %p", 454 "g": "%m/%d/%Y %-I:%M %p", 455 "G": "%m/%d/%Y %-I:%M:%S %p", 456 "M": "%B %-d", 457 "m": "%B %-d", 458 "O": "%Y-%m-%dT%H:%M:%S", 459 "u": "%Y-%M-%D %H:%M:%S%z", 460 "U": "%A, %B %D, %Y %H:%M:%S%z", 461 "T": "%-I:%M:%S %p", 462 "t": "%-I:%M", 463 "Y": "%a %Y", 464 } 465 466 class Tokenizer(tokens.Tokenizer): 467 IDENTIFIERS = [("[", "]"), '"'] 468 QUOTES = ["'", '"'] 469 HEX_STRINGS = [("0x", ""), ("0X", "")] 470 VAR_SINGLE_TOKENS = {"@", "$", "#"} 471 472 KEYWORDS = { 473 **tokens.Tokenizer.KEYWORDS, 474 "CLUSTERED INDEX": TokenType.INDEX, 475 "DATETIME2": TokenType.DATETIME, 476 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 477 "DECLARE": TokenType.DECLARE, 478 "EXEC": TokenType.COMMAND, 479 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 480 "IMAGE": TokenType.IMAGE, 481 "MONEY": TokenType.MONEY, 482 "NONCLUSTERED INDEX": TokenType.INDEX, 483 "NTEXT": TokenType.TEXT, 484 "OPTION": TokenType.OPTION, 485 "OUTPUT": TokenType.RETURNING, 486 "PRINT": TokenType.COMMAND, 487 "PROC": TokenType.PROCEDURE, 488 "REAL": TokenType.FLOAT, 489 "ROWVERSION": TokenType.ROWVERSION, 490 "SMALLDATETIME": TokenType.DATETIME, 491 "SMALLMONEY": TokenType.SMALLMONEY, 492 "SQL_VARIANT": TokenType.VARIANT, 493 "SYSTEM_USER": TokenType.CURRENT_USER, 494 "TOP": TokenType.TOP, 495 "TIMESTAMP": TokenType.ROWVERSION, 496 "TINYINT": TokenType.UTINYINT, 497 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 498 "UPDATE STATISTICS": TokenType.COMMAND, 499 "XML": TokenType.XML, 500 } 501 KEYWORDS.pop("/*+") 502 503 COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END} 504 505 class Parser(parser.Parser): 506 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 507 LOG_DEFAULTS_TO_LN = True 508 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 509 STRING_ALIASES = True 510 NO_PAREN_IF_COMMANDS = False 511 512 QUERY_MODIFIER_PARSERS = { 513 **parser.Parser.QUERY_MODIFIER_PARSERS, 514 TokenType.OPTION: lambda self: ("options", self._parse_options()), 515 } 516 517 FUNCTIONS = { 518 **parser.Parser.FUNCTIONS, 519 "CHARINDEX": lambda args: exp.StrPosition( 520 this=seq_get(args, 1), 521 substr=seq_get(args, 0), 522 position=seq_get(args, 2), 523 ), 524 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 525 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 526 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 527 "DATEPART": _build_formatted_time(exp.TimeToStr), 528 "DATETIMEFROMPARTS": _build_datetimefromparts, 529 "EOMONTH": _build_eomonth, 530 "FORMAT": _build_format, 531 "GETDATE": exp.CurrentTimestamp.from_arg_list, 532 "HASHBYTES": _build_hashbytes, 533 "ISNULL": exp.Coalesce.from_arg_list, 534 "JSON_QUERY": _build_json_query, 535 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 536 "LEN": _build_with_arg_as_text(exp.Length), 537 "LEFT": _build_with_arg_as_text(exp.Left), 538 "RIGHT": _build_with_arg_as_text(exp.Right), 539 "REPLICATE": exp.Repeat.from_arg_list, 540 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 541 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 542 "SUSER_NAME": exp.CurrentUser.from_arg_list, 543 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 544 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 545 "TIMEFROMPARTS": _build_timefromparts, 546 } 547 548 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 549 550 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 551 TokenType.TABLE, 552 *parser.Parser.TYPE_TOKENS, 553 } 554 555 STATEMENT_PARSERS = { 556 **parser.Parser.STATEMENT_PARSERS, 557 TokenType.DECLARE: lambda self: self._parse_declare(), 558 } 559 560 RANGE_PARSERS = { 561 **parser.Parser.RANGE_PARSERS, 562 TokenType.DCOLON: lambda self, this: self.expression( 563 exp.ScopeResolution, 564 this=this, 565 expression=self._parse_function() or self._parse_var(any_token=True), 566 ), 567 } 568 569 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 570 COLUMN_OPERATORS = { 571 **parser.Parser.COLUMN_OPERATORS, 572 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 573 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 574 else self.expression(exp.ScopeResolution, this=this, expression=to), 575 } 576 577 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 578 # We want to use _parse_types() if the first token after :: is a known type, 579 # otherwise we could parse something like x::varchar(max) into a function 580 if self._match_set(self.TYPE_TOKENS, advance=False): 581 return self._parse_types() 582 583 return self._parse_function() or self._parse_types() 584 585 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 586 if not self._match(TokenType.OPTION): 587 return None 588 589 def _parse_option() -> t.Optional[exp.Expression]: 590 option = self._parse_var_from_options(OPTIONS) 591 if not option: 592 return None 593 594 self._match(TokenType.EQ) 595 return self.expression( 596 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 597 ) 598 599 return self._parse_wrapped_csv(_parse_option) 600 601 def _parse_projections(self) -> t.List[exp.Expression]: 602 """ 603 T-SQL supports the syntax alias = expression in the SELECT's projection list, 604 so we transform all parsed Selects to convert their EQ projections into Aliases. 605 606 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 607 """ 608 return [ 609 ( 610 exp.alias_(projection.expression, projection.this.this, copy=False) 611 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 612 else projection 613 ) 614 for projection in super()._parse_projections() 615 ] 616 617 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 618 """Applies to SQL Server and Azure SQL Database 619 COMMIT [ { TRAN | TRANSACTION } 620 [ transaction_name | @tran_name_variable ] ] 621 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 622 623 ROLLBACK { TRAN | TRANSACTION } 624 [ transaction_name | @tran_name_variable 625 | savepoint_name | @savepoint_variable ] 626 """ 627 rollback = self._prev.token_type == TokenType.ROLLBACK 628 629 self._match_texts(("TRAN", "TRANSACTION")) 630 this = self._parse_id_var() 631 632 if rollback: 633 return self.expression(exp.Rollback, this=this) 634 635 durability = None 636 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 637 self._match_text_seq("DELAYED_DURABILITY") 638 self._match(TokenType.EQ) 639 640 if self._match_text_seq("OFF"): 641 durability = False 642 else: 643 self._match(TokenType.ON) 644 durability = True 645 646 self._match_r_paren() 647 648 return self.expression(exp.Commit, this=this, durability=durability) 649 650 def _parse_transaction(self) -> exp.Transaction | exp.Command: 651 """Applies to SQL Server and Azure SQL Database 652 BEGIN { TRAN | TRANSACTION } 653 [ { transaction_name | @tran_name_variable } 654 [ WITH MARK [ 'description' ] ] 655 ] 656 """ 657 if self._match_texts(("TRAN", "TRANSACTION")): 658 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 659 if self._match_text_seq("WITH", "MARK"): 660 transaction.set("mark", self._parse_string()) 661 662 return transaction 663 664 return self._parse_as_command(self._prev) 665 666 def _parse_returns(self) -> exp.ReturnsProperty: 667 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 668 returns = super()._parse_returns() 669 returns.set("table", table) 670 return returns 671 672 def _parse_convert( 673 self, strict: bool, safe: t.Optional[bool] = None 674 ) -> t.Optional[exp.Expression]: 675 this = self._parse_types() 676 self._match(TokenType.COMMA) 677 args = [this, *self._parse_csv(self._parse_assignment)] 678 convert = exp.Convert.from_arg_list(args) 679 convert.set("safe", safe) 680 convert.set("strict", strict) 681 return convert 682 683 def _parse_user_defined_function( 684 self, kind: t.Optional[TokenType] = None 685 ) -> t.Optional[exp.Expression]: 686 this = super()._parse_user_defined_function(kind=kind) 687 688 if ( 689 kind == TokenType.FUNCTION 690 or isinstance(this, exp.UserDefinedFunction) 691 or self._match(TokenType.ALIAS, advance=False) 692 ): 693 return this 694 695 expressions = self._parse_csv(self._parse_function_parameter) 696 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 697 698 def _parse_id_var( 699 self, 700 any_token: bool = True, 701 tokens: t.Optional[t.Collection[TokenType]] = None, 702 ) -> t.Optional[exp.Expression]: 703 is_temporary = self._match(TokenType.HASH) 704 is_global = is_temporary and self._match(TokenType.HASH) 705 706 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 707 if this: 708 if is_global: 709 this.set("global", True) 710 elif is_temporary: 711 this.set("temporary", True) 712 713 return this 714 715 def _parse_create(self) -> exp.Create | exp.Command: 716 create = super()._parse_create() 717 718 if isinstance(create, exp.Create): 719 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 720 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 721 if not create.args.get("properties"): 722 create.set("properties", exp.Properties(expressions=[])) 723 724 create.args["properties"].append("expressions", exp.TemporaryProperty()) 725 726 return create 727 728 def _parse_if(self) -> t.Optional[exp.Expression]: 729 index = self._index 730 731 if self._match_text_seq("OBJECT_ID"): 732 self._parse_wrapped_csv(self._parse_string) 733 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 734 return self._parse_drop(exists=True) 735 self._retreat(index) 736 737 return super()._parse_if() 738 739 def _parse_unique(self) -> exp.UniqueColumnConstraint: 740 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 741 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 742 else: 743 this = self._parse_schema(self._parse_id_var(any_token=False)) 744 745 return self.expression(exp.UniqueColumnConstraint, this=this) 746 747 def _parse_partition(self) -> t.Optional[exp.Partition]: 748 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 749 return None 750 751 def parse_range(): 752 low = self._parse_bitwise() 753 high = self._parse_bitwise() if self._match_text_seq("TO") else None 754 755 return ( 756 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 757 ) 758 759 partition = self.expression( 760 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 761 ) 762 763 self._match_r_paren() 764 765 return partition 766 767 def _parse_declare(self) -> exp.Declare | exp.Command: 768 index = self._index 769 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 770 771 if not expressions or self._curr: 772 self._retreat(index) 773 return self._parse_as_command(self._prev) 774 775 return self.expression(exp.Declare, expressions=expressions) 776 777 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 778 var = self._parse_id_var() 779 if not var: 780 return None 781 782 value = None 783 self._match(TokenType.ALIAS) 784 if self._match(TokenType.TABLE): 785 data_type = self._parse_schema() 786 else: 787 data_type = self._parse_types() 788 if self._match(TokenType.EQ): 789 value = self._parse_bitwise() 790 791 return self.expression(exp.DeclareItem, this=var, kind=data_type, default=value) 792 793 class Generator(generator.Generator): 794 LIMIT_IS_TOP = True 795 QUERY_HINTS = False 796 RETURNING_END = False 797 NVL2_SUPPORTED = False 798 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 799 LIMIT_FETCH = "FETCH" 800 COMPUTED_COLUMN_WITH_TYPE = False 801 CTE_RECURSIVE_KEYWORD_REQUIRED = False 802 ENSURE_BOOLS = True 803 NULL_ORDERING_SUPPORTED = None 804 SUPPORTS_SINGLE_ARG_CONCAT = False 805 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 806 SUPPORTS_SELECT_INTO = True 807 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 808 SUPPORTS_TO_NUMBER = False 809 SET_OP_MODIFIERS = False 810 COPY_PARAMS_EQ_REQUIRED = True 811 PARSE_JSON_NAME = None 812 813 EXPRESSIONS_WITHOUT_NESTED_CTES = { 814 exp.Create, 815 exp.Delete, 816 exp.Insert, 817 exp.Intersect, 818 exp.Except, 819 exp.Merge, 820 exp.Select, 821 exp.Subquery, 822 exp.Union, 823 exp.Update, 824 } 825 826 SUPPORTED_JSON_PATH_PARTS = { 827 exp.JSONPathKey, 828 exp.JSONPathRoot, 829 exp.JSONPathSubscript, 830 } 831 832 TYPE_MAPPING = { 833 **generator.Generator.TYPE_MAPPING, 834 exp.DataType.Type.BOOLEAN: "BIT", 835 exp.DataType.Type.DECIMAL: "NUMERIC", 836 exp.DataType.Type.DATETIME: "DATETIME2", 837 exp.DataType.Type.DOUBLE: "FLOAT", 838 exp.DataType.Type.INT: "INTEGER", 839 exp.DataType.Type.ROWVERSION: "ROWVERSION", 840 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 841 exp.DataType.Type.TIMESTAMP: "DATETIME2", 842 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 843 exp.DataType.Type.UTINYINT: "TINYINT", 844 exp.DataType.Type.VARIANT: "SQL_VARIANT", 845 } 846 847 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 848 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 849 850 TRANSFORMS = { 851 **generator.Generator.TRANSFORMS, 852 exp.AnyValue: any_value_to_max_sql, 853 exp.ArrayToString: rename_func("STRING_AGG"), 854 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 855 exp.DateAdd: date_delta_sql("DATEADD"), 856 exp.DateDiff: date_delta_sql("DATEDIFF"), 857 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 858 exp.CurrentDate: rename_func("GETDATE"), 859 exp.CurrentTimestamp: rename_func("GETDATE"), 860 exp.DateStrToDate: datestrtodate_sql, 861 exp.Extract: rename_func("DATEPART"), 862 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 863 exp.GroupConcat: _string_agg_sql, 864 exp.If: rename_func("IIF"), 865 exp.JSONExtract: _json_extract_sql, 866 exp.JSONExtractScalar: _json_extract_sql, 867 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 868 exp.Max: max_or_greatest, 869 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 870 exp.Min: min_or_least, 871 exp.NumberToStr: _format_sql, 872 exp.Repeat: rename_func("REPLICATE"), 873 exp.Select: transforms.preprocess( 874 [ 875 transforms.eliminate_distinct_on, 876 transforms.eliminate_semi_and_anti_joins, 877 transforms.eliminate_qualify, 878 transforms.unnest_generate_date_array_using_recursive_cte, 879 ] 880 ), 881 exp.Stddev: rename_func("STDEV"), 882 exp.StrPosition: lambda self, e: self.func( 883 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 884 ), 885 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 886 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 887 exp.SHA2: lambda self, e: self.func( 888 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 889 ), 890 exp.TemporaryProperty: lambda self, e: "", 891 exp.TimeStrToTime: _timestrtotime_sql, 892 exp.TimeToStr: _format_sql, 893 exp.Trim: trim_sql, 894 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 895 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 896 } 897 898 TRANSFORMS.pop(exp.ReturnsProperty) 899 900 PROPERTIES_LOCATION = { 901 **generator.Generator.PROPERTIES_LOCATION, 902 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 903 } 904 905 def scope_resolution(self, rhs: str, scope_name: str) -> str: 906 return f"{scope_name}::{rhs}" 907 908 def select_sql(self, expression: exp.Select) -> str: 909 if expression.args.get("offset"): 910 if not expression.args.get("order"): 911 # ORDER BY is required in order to use OFFSET in a query, so we use 912 # a noop order by, since we don't really care about the order. 913 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 914 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 915 916 limit = expression.args.get("limit") 917 if isinstance(limit, exp.Limit): 918 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 919 # we replace here because otherwise TOP would be generated in select_sql 920 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 921 922 return super().select_sql(expression) 923 924 def convert_sql(self, expression: exp.Convert) -> str: 925 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 926 return self.func( 927 name, expression.this, expression.expression, expression.args.get("style") 928 ) 929 930 def queryoption_sql(self, expression: exp.QueryOption) -> str: 931 option = self.sql(expression, "this") 932 value = self.sql(expression, "expression") 933 if value: 934 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 935 return f"{option} {optional_equal_sign}{value}" 936 return option 937 938 def lateral_op(self, expression: exp.Lateral) -> str: 939 cross_apply = expression.args.get("cross_apply") 940 if cross_apply is True: 941 return "CROSS APPLY" 942 if cross_apply is False: 943 return "OUTER APPLY" 944 945 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 946 self.unsupported("LATERAL clause is not supported.") 947 return "LATERAL" 948 949 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 950 nano = expression.args.get("nano") 951 if nano is not None: 952 nano.pop() 953 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 954 955 if expression.args.get("fractions") is None: 956 expression.set("fractions", exp.Literal.number(0)) 957 if expression.args.get("precision") is None: 958 expression.set("precision", exp.Literal.number(0)) 959 960 return rename_func("TIMEFROMPARTS")(self, expression) 961 962 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 963 zone = expression.args.get("zone") 964 if zone is not None: 965 zone.pop() 966 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 967 968 nano = expression.args.get("nano") 969 if nano is not None: 970 nano.pop() 971 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 972 973 if expression.args.get("milli") is None: 974 expression.set("milli", exp.Literal.number(0)) 975 976 return rename_func("DATETIMEFROMPARTS")(self, expression) 977 978 def setitem_sql(self, expression: exp.SetItem) -> str: 979 this = expression.this 980 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 981 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 982 return f"{self.sql(this.left)} {self.sql(this.right)}" 983 984 return super().setitem_sql(expression) 985 986 def boolean_sql(self, expression: exp.Boolean) -> str: 987 if type(expression.parent) in BIT_TYPES: 988 return "1" if expression.this else "0" 989 990 return "(1 = 1)" if expression.this else "(1 = 0)" 991 992 def is_sql(self, expression: exp.Is) -> str: 993 if isinstance(expression.expression, exp.Boolean): 994 return self.binary(expression, "=") 995 return self.binary(expression, "IS") 996 997 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 998 sql = self.sql(expression, "this") 999 properties = expression.args.get("properties") 1000 1001 if sql[:1] != "#" and any( 1002 isinstance(prop, exp.TemporaryProperty) 1003 for prop in (properties.expressions if properties else []) 1004 ): 1005 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1006 1007 return sql 1008 1009 def create_sql(self, expression: exp.Create) -> str: 1010 kind = expression.kind 1011 exists = expression.args.pop("exists", None) 1012 1013 like_property = expression.find(exp.LikeProperty) 1014 if like_property: 1015 ctas_expression = like_property.this 1016 else: 1017 ctas_expression = expression.expression 1018 1019 if kind == "VIEW": 1020 expression.this.set("catalog", None) 1021 with_ = expression.args.get("with") 1022 if ctas_expression and with_: 1023 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1024 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1025 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1026 ctas_expression.set("with", with_.pop()) 1027 1028 sql = super().create_sql(expression) 1029 1030 table = expression.find(exp.Table) 1031 1032 # Convert CTAS statement to SELECT .. INTO .. 1033 if kind == "TABLE" and ctas_expression: 1034 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1035 ctas_expression = ctas_expression.subquery() 1036 1037 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1038 select_into.set("into", exp.Into(this=table)) 1039 1040 if like_property: 1041 select_into.limit(0, copy=False) 1042 1043 sql = self.sql(select_into) 1044 1045 if exists: 1046 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1047 sql = self.sql(exp.Literal.string(sql)) 1048 if kind == "SCHEMA": 1049 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1050 elif kind == "TABLE": 1051 assert table 1052 where = exp.and_( 1053 exp.column("table_name").eq(table.name), 1054 exp.column("table_schema").eq(table.db) if table.db else None, 1055 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1056 ) 1057 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1058 elif kind == "INDEX": 1059 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1060 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1061 elif expression.args.get("replace"): 1062 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1063 1064 return self.prepend_ctes(expression, sql) 1065 1066 def offset_sql(self, expression: exp.Offset) -> str: 1067 return f"{super().offset_sql(expression)} ROWS" 1068 1069 def version_sql(self, expression: exp.Version) -> str: 1070 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1071 this = f"FOR {name}" 1072 expr = expression.expression 1073 kind = expression.text("kind") 1074 if kind in ("FROM", "BETWEEN"): 1075 args = expr.expressions 1076 sep = "TO" if kind == "FROM" else "AND" 1077 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1078 else: 1079 expr_sql = self.sql(expr) 1080 1081 expr_sql = f" {expr_sql}" if expr_sql else "" 1082 return f"{this} {kind}{expr_sql}" 1083 1084 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1085 table = expression.args.get("table") 1086 table = f"{table} " if table else "" 1087 return f"RETURNS {table}{self.sql(expression, 'this')}" 1088 1089 def returning_sql(self, expression: exp.Returning) -> str: 1090 into = self.sql(expression, "into") 1091 into = self.seg(f"INTO {into}") if into else "" 1092 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1093 1094 def transaction_sql(self, expression: exp.Transaction) -> str: 1095 this = self.sql(expression, "this") 1096 this = f" {this}" if this else "" 1097 mark = self.sql(expression, "mark") 1098 mark = f" WITH MARK {mark}" if mark else "" 1099 return f"BEGIN TRANSACTION{this}{mark}" 1100 1101 def commit_sql(self, expression: exp.Commit) -> str: 1102 this = self.sql(expression, "this") 1103 this = f" {this}" if this else "" 1104 durability = expression.args.get("durability") 1105 durability = ( 1106 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1107 if durability is not None 1108 else "" 1109 ) 1110 return f"COMMIT TRANSACTION{this}{durability}" 1111 1112 def rollback_sql(self, expression: exp.Rollback) -> str: 1113 this = self.sql(expression, "this") 1114 this = f" {this}" if this else "" 1115 return f"ROLLBACK TRANSACTION{this}" 1116 1117 def identifier_sql(self, expression: exp.Identifier) -> str: 1118 identifier = super().identifier_sql(expression) 1119 1120 if expression.args.get("global"): 1121 identifier = f"##{identifier}" 1122 elif expression.args.get("temporary"): 1123 identifier = f"#{identifier}" 1124 1125 return identifier 1126 1127 def constraint_sql(self, expression: exp.Constraint) -> str: 1128 this = self.sql(expression, "this") 1129 expressions = self.expressions(expression, flat=True, sep=" ") 1130 return f"CONSTRAINT {this} {expressions}" 1131 1132 def length_sql(self, expression: exp.Length) -> str: 1133 return self._uncast_text(expression, "LEN") 1134 1135 def right_sql(self, expression: exp.Right) -> str: 1136 return self._uncast_text(expression, "RIGHT") 1137 1138 def left_sql(self, expression: exp.Left) -> str: 1139 return self._uncast_text(expression, "LEFT") 1140 1141 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1142 this = expression.this 1143 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1144 this_sql = self.sql(this, "this") 1145 else: 1146 this_sql = self.sql(this) 1147 expression_sql = self.sql(expression, "expression") 1148 return self.func(name, this_sql, expression_sql if expression_sql else None) 1149 1150 def partition_sql(self, expression: exp.Partition) -> str: 1151 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1152 1153 def alter_sql(self, expression: exp.Alter) -> str: 1154 action = seq_get(expression.args.get("actions") or [], 0) 1155 if isinstance(action, exp.RenameTable): 1156 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1157 return super().alter_sql(expression) 1158 1159 def drop_sql(self, expression: exp.Drop) -> str: 1160 if expression.args["kind"] == "VIEW": 1161 expression.this.set("catalog", None) 1162 return super().drop_sql(expression) 1163 1164 def declare_sql(self, expression: exp.Declare) -> str: 1165 return f"DECLARE {self.expressions(expression, flat=True)}" 1166 1167 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1168 variable = self.sql(expression, "this") 1169 default = self.sql(expression, "default") 1170 default = f" = {default}" if default else "" 1171 1172 kind = self.sql(expression, "kind") 1173 if isinstance(expression.args.get("kind"), exp.Schema): 1174 kind = f"TABLE {kind}" 1175 1176 return f"{variable} AS {kind}{default}" 1177 1178 def options_modifier(self, expression: exp.Expression) -> str: 1179 options = self.expressions(expression, key="options") 1180 return f" OPTION{self.wrap(options)}" if options else ""
Specifies the strategy according to which identifiers should be normalized.
Whether the base comes first in the LOG
function.
Possible values: True
, False
, None
(two arguments are not supported by LOG
)
Whether the behavior of a / b
depends on the types of a
and b
.
False means a / b
is always float division.
True means a / b
is integer division if both a
and b
are integers.
A NULL
arg in CONCAT
yields NULL
by default, but in some dialects it yields an empty string.
Associates this dialect's time formats with their equivalent Python strftime
formats.
Inherited Members
- sqlglot.dialects.dialect.Dialect
- Dialect
- INDEX_OFFSET
- WEEK_OFFSET
- UNNEST_COLUMN_ONLY
- ALIAS_POST_TABLESAMPLE
- TABLESAMPLE_SIZE_IS_PERCENT
- IDENTIFIERS_CAN_START_WITH_DIGIT
- DPIPE_IS_STRING_CONCAT
- STRICT_STRING_CONCAT
- SUPPORTS_USER_DEFINED_TYPES
- COPY_PARAMS_ARE_CSV
- NORMALIZE_FUNCTIONS
- NULL_ORDERING
- SAFE_DIVISION
- HEX_LOWERCASE
- DATE_FORMAT
- DATEINT_FORMAT
- FORMAT_MAPPING
- UNESCAPED_SEQUENCES
- PSEUDOCOLUMNS
- PREFER_CTE_ALIAS_COLUMN
- FORCE_EARLY_ALIAS_REF_EXPANSION
- EXPAND_ALIAS_REFS_EARLY_ONLY_IN_GROUP_BY
- SUPPORTS_ORDER_BY_ALL
- HAS_DISTINCT_ARRAY_CONSTRUCTORS
- SUPPORTS_FIXED_SIZE_ARRAYS
- CREATABLE_KIND_MAPPING
- DATE_PART_MAPPING
- TYPE_TO_EXPRESSIONS
- ANNOTATORS
- get_or_raise
- format_time
- settings
- normalize_identifier
- case_sensitive
- can_identify
- quote_identifier
- to_json_path
- parse
- parse_into
- generate
- transpile
- tokenize
- tokenizer
- jsonpath_tokenizer
- parser
- generator
466 class Tokenizer(tokens.Tokenizer): 467 IDENTIFIERS = [("[", "]"), '"'] 468 QUOTES = ["'", '"'] 469 HEX_STRINGS = [("0x", ""), ("0X", "")] 470 VAR_SINGLE_TOKENS = {"@", "$", "#"} 471 472 KEYWORDS = { 473 **tokens.Tokenizer.KEYWORDS, 474 "CLUSTERED INDEX": TokenType.INDEX, 475 "DATETIME2": TokenType.DATETIME, 476 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 477 "DECLARE": TokenType.DECLARE, 478 "EXEC": TokenType.COMMAND, 479 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 480 "IMAGE": TokenType.IMAGE, 481 "MONEY": TokenType.MONEY, 482 "NONCLUSTERED INDEX": TokenType.INDEX, 483 "NTEXT": TokenType.TEXT, 484 "OPTION": TokenType.OPTION, 485 "OUTPUT": TokenType.RETURNING, 486 "PRINT": TokenType.COMMAND, 487 "PROC": TokenType.PROCEDURE, 488 "REAL": TokenType.FLOAT, 489 "ROWVERSION": TokenType.ROWVERSION, 490 "SMALLDATETIME": TokenType.DATETIME, 491 "SMALLMONEY": TokenType.SMALLMONEY, 492 "SQL_VARIANT": TokenType.VARIANT, 493 "SYSTEM_USER": TokenType.CURRENT_USER, 494 "TOP": TokenType.TOP, 495 "TIMESTAMP": TokenType.ROWVERSION, 496 "TINYINT": TokenType.UTINYINT, 497 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 498 "UPDATE STATISTICS": TokenType.COMMAND, 499 "XML": TokenType.XML, 500 } 501 KEYWORDS.pop("/*+") 502 503 COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END}
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- SINGLE_TOKENS
- BIT_STRINGS
- BYTE_STRINGS
- RAW_STRINGS
- HEREDOC_STRINGS
- UNICODE_STRINGS
- IDENTIFIER_ESCAPES
- STRING_ESCAPES
- HEREDOC_TAG_IS_IDENTIFIER
- HEREDOC_STRING_ALTERNATIVE
- STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS
- NESTED_COMMENTS
- WHITE_SPACE
- COMMAND_PREFIX_TOKENS
- NUMERIC_LITERALS
- COMMENTS
- dialect
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
505 class Parser(parser.Parser): 506 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 507 LOG_DEFAULTS_TO_LN = True 508 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 509 STRING_ALIASES = True 510 NO_PAREN_IF_COMMANDS = False 511 512 QUERY_MODIFIER_PARSERS = { 513 **parser.Parser.QUERY_MODIFIER_PARSERS, 514 TokenType.OPTION: lambda self: ("options", self._parse_options()), 515 } 516 517 FUNCTIONS = { 518 **parser.Parser.FUNCTIONS, 519 "CHARINDEX": lambda args: exp.StrPosition( 520 this=seq_get(args, 1), 521 substr=seq_get(args, 0), 522 position=seq_get(args, 2), 523 ), 524 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 525 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 526 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 527 "DATEPART": _build_formatted_time(exp.TimeToStr), 528 "DATETIMEFROMPARTS": _build_datetimefromparts, 529 "EOMONTH": _build_eomonth, 530 "FORMAT": _build_format, 531 "GETDATE": exp.CurrentTimestamp.from_arg_list, 532 "HASHBYTES": _build_hashbytes, 533 "ISNULL": exp.Coalesce.from_arg_list, 534 "JSON_QUERY": _build_json_query, 535 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 536 "LEN": _build_with_arg_as_text(exp.Length), 537 "LEFT": _build_with_arg_as_text(exp.Left), 538 "RIGHT": _build_with_arg_as_text(exp.Right), 539 "REPLICATE": exp.Repeat.from_arg_list, 540 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 541 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 542 "SUSER_NAME": exp.CurrentUser.from_arg_list, 543 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 544 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 545 "TIMEFROMPARTS": _build_timefromparts, 546 } 547 548 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 549 550 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 551 TokenType.TABLE, 552 *parser.Parser.TYPE_TOKENS, 553 } 554 555 STATEMENT_PARSERS = { 556 **parser.Parser.STATEMENT_PARSERS, 557 TokenType.DECLARE: lambda self: self._parse_declare(), 558 } 559 560 RANGE_PARSERS = { 561 **parser.Parser.RANGE_PARSERS, 562 TokenType.DCOLON: lambda self, this: self.expression( 563 exp.ScopeResolution, 564 this=this, 565 expression=self._parse_function() or self._parse_var(any_token=True), 566 ), 567 } 568 569 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 570 COLUMN_OPERATORS = { 571 **parser.Parser.COLUMN_OPERATORS, 572 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 573 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 574 else self.expression(exp.ScopeResolution, this=this, expression=to), 575 } 576 577 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 578 # We want to use _parse_types() if the first token after :: is a known type, 579 # otherwise we could parse something like x::varchar(max) into a function 580 if self._match_set(self.TYPE_TOKENS, advance=False): 581 return self._parse_types() 582 583 return self._parse_function() or self._parse_types() 584 585 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 586 if not self._match(TokenType.OPTION): 587 return None 588 589 def _parse_option() -> t.Optional[exp.Expression]: 590 option = self._parse_var_from_options(OPTIONS) 591 if not option: 592 return None 593 594 self._match(TokenType.EQ) 595 return self.expression( 596 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 597 ) 598 599 return self._parse_wrapped_csv(_parse_option) 600 601 def _parse_projections(self) -> t.List[exp.Expression]: 602 """ 603 T-SQL supports the syntax alias = expression in the SELECT's projection list, 604 so we transform all parsed Selects to convert their EQ projections into Aliases. 605 606 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 607 """ 608 return [ 609 ( 610 exp.alias_(projection.expression, projection.this.this, copy=False) 611 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 612 else projection 613 ) 614 for projection in super()._parse_projections() 615 ] 616 617 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 618 """Applies to SQL Server and Azure SQL Database 619 COMMIT [ { TRAN | TRANSACTION } 620 [ transaction_name | @tran_name_variable ] ] 621 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 622 623 ROLLBACK { TRAN | TRANSACTION } 624 [ transaction_name | @tran_name_variable 625 | savepoint_name | @savepoint_variable ] 626 """ 627 rollback = self._prev.token_type == TokenType.ROLLBACK 628 629 self._match_texts(("TRAN", "TRANSACTION")) 630 this = self._parse_id_var() 631 632 if rollback: 633 return self.expression(exp.Rollback, this=this) 634 635 durability = None 636 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 637 self._match_text_seq("DELAYED_DURABILITY") 638 self._match(TokenType.EQ) 639 640 if self._match_text_seq("OFF"): 641 durability = False 642 else: 643 self._match(TokenType.ON) 644 durability = True 645 646 self._match_r_paren() 647 648 return self.expression(exp.Commit, this=this, durability=durability) 649 650 def _parse_transaction(self) -> exp.Transaction | exp.Command: 651 """Applies to SQL Server and Azure SQL Database 652 BEGIN { TRAN | TRANSACTION } 653 [ { transaction_name | @tran_name_variable } 654 [ WITH MARK [ 'description' ] ] 655 ] 656 """ 657 if self._match_texts(("TRAN", "TRANSACTION")): 658 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 659 if self._match_text_seq("WITH", "MARK"): 660 transaction.set("mark", self._parse_string()) 661 662 return transaction 663 664 return self._parse_as_command(self._prev) 665 666 def _parse_returns(self) -> exp.ReturnsProperty: 667 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 668 returns = super()._parse_returns() 669 returns.set("table", table) 670 return returns 671 672 def _parse_convert( 673 self, strict: bool, safe: t.Optional[bool] = None 674 ) -> t.Optional[exp.Expression]: 675 this = self._parse_types() 676 self._match(TokenType.COMMA) 677 args = [this, *self._parse_csv(self._parse_assignment)] 678 convert = exp.Convert.from_arg_list(args) 679 convert.set("safe", safe) 680 convert.set("strict", strict) 681 return convert 682 683 def _parse_user_defined_function( 684 self, kind: t.Optional[TokenType] = None 685 ) -> t.Optional[exp.Expression]: 686 this = super()._parse_user_defined_function(kind=kind) 687 688 if ( 689 kind == TokenType.FUNCTION 690 or isinstance(this, exp.UserDefinedFunction) 691 or self._match(TokenType.ALIAS, advance=False) 692 ): 693 return this 694 695 expressions = self._parse_csv(self._parse_function_parameter) 696 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 697 698 def _parse_id_var( 699 self, 700 any_token: bool = True, 701 tokens: t.Optional[t.Collection[TokenType]] = None, 702 ) -> t.Optional[exp.Expression]: 703 is_temporary = self._match(TokenType.HASH) 704 is_global = is_temporary and self._match(TokenType.HASH) 705 706 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 707 if this: 708 if is_global: 709 this.set("global", True) 710 elif is_temporary: 711 this.set("temporary", True) 712 713 return this 714 715 def _parse_create(self) -> exp.Create | exp.Command: 716 create = super()._parse_create() 717 718 if isinstance(create, exp.Create): 719 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 720 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 721 if not create.args.get("properties"): 722 create.set("properties", exp.Properties(expressions=[])) 723 724 create.args["properties"].append("expressions", exp.TemporaryProperty()) 725 726 return create 727 728 def _parse_if(self) -> t.Optional[exp.Expression]: 729 index = self._index 730 731 if self._match_text_seq("OBJECT_ID"): 732 self._parse_wrapped_csv(self._parse_string) 733 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 734 return self._parse_drop(exists=True) 735 self._retreat(index) 736 737 return super()._parse_if() 738 739 def _parse_unique(self) -> exp.UniqueColumnConstraint: 740 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 741 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 742 else: 743 this = self._parse_schema(self._parse_id_var(any_token=False)) 744 745 return self.expression(exp.UniqueColumnConstraint, this=this) 746 747 def _parse_partition(self) -> t.Optional[exp.Partition]: 748 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 749 return None 750 751 def parse_range(): 752 low = self._parse_bitwise() 753 high = self._parse_bitwise() if self._match_text_seq("TO") else None 754 755 return ( 756 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 757 ) 758 759 partition = self.expression( 760 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 761 ) 762 763 self._match_r_paren() 764 765 return partition 766 767 def _parse_declare(self) -> exp.Declare | exp.Command: 768 index = self._index 769 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 770 771 if not expressions or self._curr: 772 self._retreat(index) 773 return self._parse_as_command(self._prev) 774 775 return self.expression(exp.Declare, expressions=expressions) 776 777 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 778 var = self._parse_id_var() 779 if not var: 780 return None 781 782 value = None 783 self._match(TokenType.ALIAS) 784 if self._match(TokenType.TABLE): 785 data_type = self._parse_schema() 786 else: 787 data_type = self._parse_types() 788 if self._match(TokenType.EQ): 789 value = self._parse_bitwise() 790 791 return self.expression(exp.DeclareItem, this=var, kind=data_type, default=value)
Parser consumes a list of tokens produced by the Tokenizer and produces a parsed syntax tree.
Arguments:
- error_level: The desired error level. Default: ErrorLevel.IMMEDIATE
- error_message_context: The amount of context to capture from a query string when displaying the error message (in number of characters). Default: 100
- max_errors: Maximum number of error messages to include in a raised ParseError. This is only relevant if error_level is ErrorLevel.RAISE. Default: 3
Inherited Members
- sqlglot.parser.Parser
- Parser
- NO_PAREN_FUNCTIONS
- STRUCT_TYPE_TOKENS
- NESTED_TYPE_TOKENS
- ENUM_TYPE_TOKENS
- AGGREGATE_TYPE_TOKENS
- TYPE_TOKENS
- SIGNED_TO_UNSIGNED_TYPE_TOKEN
- SUBQUERY_PREDICATES
- RESERVED_TOKENS
- DB_CREATABLES
- CREATABLES
- ALTERABLES
- INTERVAL_VARS
- ALIAS_TOKENS
- ARRAY_CONSTRUCTORS
- COMMENT_TABLE_ALIAS_TOKENS
- UPDATE_ALIAS_TOKENS
- TRIM_TYPES
- FUNC_TOKENS
- CONJUNCTION
- ASSIGNMENT
- DISJUNCTION
- EQUALITY
- COMPARISON
- BITWISE
- TERM
- FACTOR
- EXPONENT
- TIMES
- TIMESTAMPS
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- LAMBDAS
- EXPRESSION_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- PROPERTY_PARSERS
- CONSTRAINT_PARSERS
- ALTER_PARSERS
- ALTER_ALTER_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- NO_PAREN_FUNCTION_PARSERS
- INVALID_FUNC_NAME_TOKENS
- FUNCTIONS_WITH_ALIASED_ARGS
- KEY_VALUE_DEFINITIONS
- FUNCTION_PARSERS
- SET_PARSERS
- SHOW_PARSERS
- TYPE_LITERAL_PARSERS
- TYPE_CONVERTERS
- DDL_SELECT_TOKENS
- PRE_VOLATILE_TOKENS
- TRANSACTION_KIND
- TRANSACTION_CHARACTERISTICS
- CONFLICT_ACTIONS
- CREATE_SEQUENCE
- ISOLATED_LOADING_OPTIONS
- USABLES
- CAST_ACTIONS
- SCHEMA_BINDING_OPTIONS
- KEY_CONSTRAINT_OPTIONS
- INSERT_ALTERNATIVES
- CLONE_KEYWORDS
- HISTORICAL_DATA_PREFIX
- HISTORICAL_DATA_KIND
- OPCLASS_FOLLOW_KEYWORDS
- OPTYPE_FOLLOW_TOKENS
- TABLE_INDEX_HINT_TOKENS
- VIEW_ATTRIBUTES
- WINDOW_ALIAS_TOKENS
- WINDOW_BEFORE_PAREN_TOKENS
- WINDOW_SIDES
- JSON_KEY_VALUE_SEPARATOR_TOKENS
- FETCH_TOKENS
- ADD_CONSTRAINT_TOKENS
- DISTINCT_TOKENS
- NULL_TOKENS
- UNNEST_OFFSET_ALIAS_TOKENS
- SELECT_START_TOKENS
- COPY_INTO_VARLEN_OPTIONS
- STRICT_CAST
- PREFIXED_PIVOT_COLUMNS
- IDENTIFY_PIVOT_STRINGS
- TABLESAMPLE_CSV
- DEFAULT_SAMPLING_METHOD
- TRIM_PATTERN_FIRST
- MODIFIERS_ATTACHED_TO_SET_OP
- SET_OP_MODIFIERS
- JSON_ARROWS_REQUIRE_JSON_TYPE
- COLON_IS_VARIANT_EXTRACT
- VALUES_FOLLOWED_BY_PAREN
- SUPPORTS_IMPLICIT_UNNEST
- INTERVAL_SPANS
- SUPPORTS_PARTITION_SELECTION
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
793 class Generator(generator.Generator): 794 LIMIT_IS_TOP = True 795 QUERY_HINTS = False 796 RETURNING_END = False 797 NVL2_SUPPORTED = False 798 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 799 LIMIT_FETCH = "FETCH" 800 COMPUTED_COLUMN_WITH_TYPE = False 801 CTE_RECURSIVE_KEYWORD_REQUIRED = False 802 ENSURE_BOOLS = True 803 NULL_ORDERING_SUPPORTED = None 804 SUPPORTS_SINGLE_ARG_CONCAT = False 805 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 806 SUPPORTS_SELECT_INTO = True 807 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 808 SUPPORTS_TO_NUMBER = False 809 SET_OP_MODIFIERS = False 810 COPY_PARAMS_EQ_REQUIRED = True 811 PARSE_JSON_NAME = None 812 813 EXPRESSIONS_WITHOUT_NESTED_CTES = { 814 exp.Create, 815 exp.Delete, 816 exp.Insert, 817 exp.Intersect, 818 exp.Except, 819 exp.Merge, 820 exp.Select, 821 exp.Subquery, 822 exp.Union, 823 exp.Update, 824 } 825 826 SUPPORTED_JSON_PATH_PARTS = { 827 exp.JSONPathKey, 828 exp.JSONPathRoot, 829 exp.JSONPathSubscript, 830 } 831 832 TYPE_MAPPING = { 833 **generator.Generator.TYPE_MAPPING, 834 exp.DataType.Type.BOOLEAN: "BIT", 835 exp.DataType.Type.DECIMAL: "NUMERIC", 836 exp.DataType.Type.DATETIME: "DATETIME2", 837 exp.DataType.Type.DOUBLE: "FLOAT", 838 exp.DataType.Type.INT: "INTEGER", 839 exp.DataType.Type.ROWVERSION: "ROWVERSION", 840 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 841 exp.DataType.Type.TIMESTAMP: "DATETIME2", 842 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 843 exp.DataType.Type.UTINYINT: "TINYINT", 844 exp.DataType.Type.VARIANT: "SQL_VARIANT", 845 } 846 847 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 848 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 849 850 TRANSFORMS = { 851 **generator.Generator.TRANSFORMS, 852 exp.AnyValue: any_value_to_max_sql, 853 exp.ArrayToString: rename_func("STRING_AGG"), 854 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 855 exp.DateAdd: date_delta_sql("DATEADD"), 856 exp.DateDiff: date_delta_sql("DATEDIFF"), 857 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 858 exp.CurrentDate: rename_func("GETDATE"), 859 exp.CurrentTimestamp: rename_func("GETDATE"), 860 exp.DateStrToDate: datestrtodate_sql, 861 exp.Extract: rename_func("DATEPART"), 862 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 863 exp.GroupConcat: _string_agg_sql, 864 exp.If: rename_func("IIF"), 865 exp.JSONExtract: _json_extract_sql, 866 exp.JSONExtractScalar: _json_extract_sql, 867 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 868 exp.Max: max_or_greatest, 869 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 870 exp.Min: min_or_least, 871 exp.NumberToStr: _format_sql, 872 exp.Repeat: rename_func("REPLICATE"), 873 exp.Select: transforms.preprocess( 874 [ 875 transforms.eliminate_distinct_on, 876 transforms.eliminate_semi_and_anti_joins, 877 transforms.eliminate_qualify, 878 transforms.unnest_generate_date_array_using_recursive_cte, 879 ] 880 ), 881 exp.Stddev: rename_func("STDEV"), 882 exp.StrPosition: lambda self, e: self.func( 883 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 884 ), 885 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 886 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 887 exp.SHA2: lambda self, e: self.func( 888 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 889 ), 890 exp.TemporaryProperty: lambda self, e: "", 891 exp.TimeStrToTime: _timestrtotime_sql, 892 exp.TimeToStr: _format_sql, 893 exp.Trim: trim_sql, 894 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 895 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 896 } 897 898 TRANSFORMS.pop(exp.ReturnsProperty) 899 900 PROPERTIES_LOCATION = { 901 **generator.Generator.PROPERTIES_LOCATION, 902 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 903 } 904 905 def scope_resolution(self, rhs: str, scope_name: str) -> str: 906 return f"{scope_name}::{rhs}" 907 908 def select_sql(self, expression: exp.Select) -> str: 909 if expression.args.get("offset"): 910 if not expression.args.get("order"): 911 # ORDER BY is required in order to use OFFSET in a query, so we use 912 # a noop order by, since we don't really care about the order. 913 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 914 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 915 916 limit = expression.args.get("limit") 917 if isinstance(limit, exp.Limit): 918 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 919 # we replace here because otherwise TOP would be generated in select_sql 920 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 921 922 return super().select_sql(expression) 923 924 def convert_sql(self, expression: exp.Convert) -> str: 925 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 926 return self.func( 927 name, expression.this, expression.expression, expression.args.get("style") 928 ) 929 930 def queryoption_sql(self, expression: exp.QueryOption) -> str: 931 option = self.sql(expression, "this") 932 value = self.sql(expression, "expression") 933 if value: 934 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 935 return f"{option} {optional_equal_sign}{value}" 936 return option 937 938 def lateral_op(self, expression: exp.Lateral) -> str: 939 cross_apply = expression.args.get("cross_apply") 940 if cross_apply is True: 941 return "CROSS APPLY" 942 if cross_apply is False: 943 return "OUTER APPLY" 944 945 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 946 self.unsupported("LATERAL clause is not supported.") 947 return "LATERAL" 948 949 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 950 nano = expression.args.get("nano") 951 if nano is not None: 952 nano.pop() 953 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 954 955 if expression.args.get("fractions") is None: 956 expression.set("fractions", exp.Literal.number(0)) 957 if expression.args.get("precision") is None: 958 expression.set("precision", exp.Literal.number(0)) 959 960 return rename_func("TIMEFROMPARTS")(self, expression) 961 962 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 963 zone = expression.args.get("zone") 964 if zone is not None: 965 zone.pop() 966 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 967 968 nano = expression.args.get("nano") 969 if nano is not None: 970 nano.pop() 971 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 972 973 if expression.args.get("milli") is None: 974 expression.set("milli", exp.Literal.number(0)) 975 976 return rename_func("DATETIMEFROMPARTS")(self, expression) 977 978 def setitem_sql(self, expression: exp.SetItem) -> str: 979 this = expression.this 980 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 981 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 982 return f"{self.sql(this.left)} {self.sql(this.right)}" 983 984 return super().setitem_sql(expression) 985 986 def boolean_sql(self, expression: exp.Boolean) -> str: 987 if type(expression.parent) in BIT_TYPES: 988 return "1" if expression.this else "0" 989 990 return "(1 = 1)" if expression.this else "(1 = 0)" 991 992 def is_sql(self, expression: exp.Is) -> str: 993 if isinstance(expression.expression, exp.Boolean): 994 return self.binary(expression, "=") 995 return self.binary(expression, "IS") 996 997 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 998 sql = self.sql(expression, "this") 999 properties = expression.args.get("properties") 1000 1001 if sql[:1] != "#" and any( 1002 isinstance(prop, exp.TemporaryProperty) 1003 for prop in (properties.expressions if properties else []) 1004 ): 1005 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1006 1007 return sql 1008 1009 def create_sql(self, expression: exp.Create) -> str: 1010 kind = expression.kind 1011 exists = expression.args.pop("exists", None) 1012 1013 like_property = expression.find(exp.LikeProperty) 1014 if like_property: 1015 ctas_expression = like_property.this 1016 else: 1017 ctas_expression = expression.expression 1018 1019 if kind == "VIEW": 1020 expression.this.set("catalog", None) 1021 with_ = expression.args.get("with") 1022 if ctas_expression and with_: 1023 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1024 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1025 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1026 ctas_expression.set("with", with_.pop()) 1027 1028 sql = super().create_sql(expression) 1029 1030 table = expression.find(exp.Table) 1031 1032 # Convert CTAS statement to SELECT .. INTO .. 1033 if kind == "TABLE" and ctas_expression: 1034 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1035 ctas_expression = ctas_expression.subquery() 1036 1037 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1038 select_into.set("into", exp.Into(this=table)) 1039 1040 if like_property: 1041 select_into.limit(0, copy=False) 1042 1043 sql = self.sql(select_into) 1044 1045 if exists: 1046 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1047 sql = self.sql(exp.Literal.string(sql)) 1048 if kind == "SCHEMA": 1049 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1050 elif kind == "TABLE": 1051 assert table 1052 where = exp.and_( 1053 exp.column("table_name").eq(table.name), 1054 exp.column("table_schema").eq(table.db) if table.db else None, 1055 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1056 ) 1057 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1058 elif kind == "INDEX": 1059 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1060 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1061 elif expression.args.get("replace"): 1062 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1063 1064 return self.prepend_ctes(expression, sql) 1065 1066 def offset_sql(self, expression: exp.Offset) -> str: 1067 return f"{super().offset_sql(expression)} ROWS" 1068 1069 def version_sql(self, expression: exp.Version) -> str: 1070 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1071 this = f"FOR {name}" 1072 expr = expression.expression 1073 kind = expression.text("kind") 1074 if kind in ("FROM", "BETWEEN"): 1075 args = expr.expressions 1076 sep = "TO" if kind == "FROM" else "AND" 1077 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1078 else: 1079 expr_sql = self.sql(expr) 1080 1081 expr_sql = f" {expr_sql}" if expr_sql else "" 1082 return f"{this} {kind}{expr_sql}" 1083 1084 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1085 table = expression.args.get("table") 1086 table = f"{table} " if table else "" 1087 return f"RETURNS {table}{self.sql(expression, 'this')}" 1088 1089 def returning_sql(self, expression: exp.Returning) -> str: 1090 into = self.sql(expression, "into") 1091 into = self.seg(f"INTO {into}") if into else "" 1092 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1093 1094 def transaction_sql(self, expression: exp.Transaction) -> str: 1095 this = self.sql(expression, "this") 1096 this = f" {this}" if this else "" 1097 mark = self.sql(expression, "mark") 1098 mark = f" WITH MARK {mark}" if mark else "" 1099 return f"BEGIN TRANSACTION{this}{mark}" 1100 1101 def commit_sql(self, expression: exp.Commit) -> str: 1102 this = self.sql(expression, "this") 1103 this = f" {this}" if this else "" 1104 durability = expression.args.get("durability") 1105 durability = ( 1106 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1107 if durability is not None 1108 else "" 1109 ) 1110 return f"COMMIT TRANSACTION{this}{durability}" 1111 1112 def rollback_sql(self, expression: exp.Rollback) -> str: 1113 this = self.sql(expression, "this") 1114 this = f" {this}" if this else "" 1115 return f"ROLLBACK TRANSACTION{this}" 1116 1117 def identifier_sql(self, expression: exp.Identifier) -> str: 1118 identifier = super().identifier_sql(expression) 1119 1120 if expression.args.get("global"): 1121 identifier = f"##{identifier}" 1122 elif expression.args.get("temporary"): 1123 identifier = f"#{identifier}" 1124 1125 return identifier 1126 1127 def constraint_sql(self, expression: exp.Constraint) -> str: 1128 this = self.sql(expression, "this") 1129 expressions = self.expressions(expression, flat=True, sep=" ") 1130 return f"CONSTRAINT {this} {expressions}" 1131 1132 def length_sql(self, expression: exp.Length) -> str: 1133 return self._uncast_text(expression, "LEN") 1134 1135 def right_sql(self, expression: exp.Right) -> str: 1136 return self._uncast_text(expression, "RIGHT") 1137 1138 def left_sql(self, expression: exp.Left) -> str: 1139 return self._uncast_text(expression, "LEFT") 1140 1141 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1142 this = expression.this 1143 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1144 this_sql = self.sql(this, "this") 1145 else: 1146 this_sql = self.sql(this) 1147 expression_sql = self.sql(expression, "expression") 1148 return self.func(name, this_sql, expression_sql if expression_sql else None) 1149 1150 def partition_sql(self, expression: exp.Partition) -> str: 1151 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1152 1153 def alter_sql(self, expression: exp.Alter) -> str: 1154 action = seq_get(expression.args.get("actions") or [], 0) 1155 if isinstance(action, exp.RenameTable): 1156 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1157 return super().alter_sql(expression) 1158 1159 def drop_sql(self, expression: exp.Drop) -> str: 1160 if expression.args["kind"] == "VIEW": 1161 expression.this.set("catalog", None) 1162 return super().drop_sql(expression) 1163 1164 def declare_sql(self, expression: exp.Declare) -> str: 1165 return f"DECLARE {self.expressions(expression, flat=True)}" 1166 1167 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1168 variable = self.sql(expression, "this") 1169 default = self.sql(expression, "default") 1170 default = f" = {default}" if default else "" 1171 1172 kind = self.sql(expression, "kind") 1173 if isinstance(expression.args.get("kind"), exp.Schema): 1174 kind = f"TABLE {kind}" 1175 1176 return f"{variable} AS {kind}{default}" 1177 1178 def options_modifier(self, expression: exp.Expression) -> str: 1179 options = self.expressions(expression, key="options") 1180 return f" OPTION{self.wrap(options)}" if options else ""
Generator converts a given syntax tree to the corresponding SQL string.
Arguments:
- pretty: Whether to format the produced SQL string. Default: False.
- identify: Determines when an identifier should be quoted. Possible values are: False (default): Never quote, except in cases where it's mandatory by the dialect. True or 'always': Always quote. 'safe': Only quote identifiers that are case insensitive.
- normalize: Whether to normalize identifiers to lowercase. Default: False.
- pad: The pad size in a formatted string. For example, this affects the indentation of a projection in a query, relative to its nesting level. Default: 2.
- indent: The indentation size in a formatted string. For example, this affects the
indentation of subqueries and filters under a
WHERE
clause. Default: 2. - normalize_functions: How to normalize function names. Possible values are: "upper" or True (default): Convert names to uppercase. "lower": Convert names to lowercase. False: Disables function name normalization.
- unsupported_level: Determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- max_unsupported: 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: Whether the comma is leading or trailing in select expressions. This is only relevant when generating in pretty mode. 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 to preserve comments in the output SQL code. Default: True
908 def select_sql(self, expression: exp.Select) -> str: 909 if expression.args.get("offset"): 910 if not expression.args.get("order"): 911 # ORDER BY is required in order to use OFFSET in a query, so we use 912 # a noop order by, since we don't really care about the order. 913 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 914 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 915 916 limit = expression.args.get("limit") 917 if isinstance(limit, exp.Limit): 918 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 919 # we replace here because otherwise TOP would be generated in select_sql 920 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 921 922 return super().select_sql(expression)
930 def queryoption_sql(self, expression: exp.QueryOption) -> str: 931 option = self.sql(expression, "this") 932 value = self.sql(expression, "expression") 933 if value: 934 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 935 return f"{option} {optional_equal_sign}{value}" 936 return option
938 def lateral_op(self, expression: exp.Lateral) -> str: 939 cross_apply = expression.args.get("cross_apply") 940 if cross_apply is True: 941 return "CROSS APPLY" 942 if cross_apply is False: 943 return "OUTER APPLY" 944 945 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 946 self.unsupported("LATERAL clause is not supported.") 947 return "LATERAL"
949 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 950 nano = expression.args.get("nano") 951 if nano is not None: 952 nano.pop() 953 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 954 955 if expression.args.get("fractions") is None: 956 expression.set("fractions", exp.Literal.number(0)) 957 if expression.args.get("precision") is None: 958 expression.set("precision", exp.Literal.number(0)) 959 960 return rename_func("TIMEFROMPARTS")(self, expression)
962 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 963 zone = expression.args.get("zone") 964 if zone is not None: 965 zone.pop() 966 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 967 968 nano = expression.args.get("nano") 969 if nano is not None: 970 nano.pop() 971 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 972 973 if expression.args.get("milli") is None: 974 expression.set("milli", exp.Literal.number(0)) 975 976 return rename_func("DATETIMEFROMPARTS")(self, expression)
978 def setitem_sql(self, expression: exp.SetItem) -> str: 979 this = expression.this 980 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 981 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 982 return f"{self.sql(this.left)} {self.sql(this.right)}" 983 984 return super().setitem_sql(expression)
997 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 998 sql = self.sql(expression, "this") 999 properties = expression.args.get("properties") 1000 1001 if sql[:1] != "#" and any( 1002 isinstance(prop, exp.TemporaryProperty) 1003 for prop in (properties.expressions if properties else []) 1004 ): 1005 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1006 1007 return sql
1009 def create_sql(self, expression: exp.Create) -> str: 1010 kind = expression.kind 1011 exists = expression.args.pop("exists", None) 1012 1013 like_property = expression.find(exp.LikeProperty) 1014 if like_property: 1015 ctas_expression = like_property.this 1016 else: 1017 ctas_expression = expression.expression 1018 1019 if kind == "VIEW": 1020 expression.this.set("catalog", None) 1021 with_ = expression.args.get("with") 1022 if ctas_expression and with_: 1023 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1024 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1025 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1026 ctas_expression.set("with", with_.pop()) 1027 1028 sql = super().create_sql(expression) 1029 1030 table = expression.find(exp.Table) 1031 1032 # Convert CTAS statement to SELECT .. INTO .. 1033 if kind == "TABLE" and ctas_expression: 1034 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1035 ctas_expression = ctas_expression.subquery() 1036 1037 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1038 select_into.set("into", exp.Into(this=table)) 1039 1040 if like_property: 1041 select_into.limit(0, copy=False) 1042 1043 sql = self.sql(select_into) 1044 1045 if exists: 1046 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1047 sql = self.sql(exp.Literal.string(sql)) 1048 if kind == "SCHEMA": 1049 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1050 elif kind == "TABLE": 1051 assert table 1052 where = exp.and_( 1053 exp.column("table_name").eq(table.name), 1054 exp.column("table_schema").eq(table.db) if table.db else None, 1055 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1056 ) 1057 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1058 elif kind == "INDEX": 1059 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1060 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1061 elif expression.args.get("replace"): 1062 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1063 1064 return self.prepend_ctes(expression, sql)
1069 def version_sql(self, expression: exp.Version) -> str: 1070 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1071 this = f"FOR {name}" 1072 expr = expression.expression 1073 kind = expression.text("kind") 1074 if kind in ("FROM", "BETWEEN"): 1075 args = expr.expressions 1076 sep = "TO" if kind == "FROM" else "AND" 1077 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1078 else: 1079 expr_sql = self.sql(expr) 1080 1081 expr_sql = f" {expr_sql}" if expr_sql else "" 1082 return f"{this} {kind}{expr_sql}"
1101 def commit_sql(self, expression: exp.Commit) -> str: 1102 this = self.sql(expression, "this") 1103 this = f" {this}" if this else "" 1104 durability = expression.args.get("durability") 1105 durability = ( 1106 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1107 if durability is not None 1108 else "" 1109 ) 1110 return f"COMMIT TRANSACTION{this}{durability}"
1117 def identifier_sql(self, expression: exp.Identifier) -> str: 1118 identifier = super().identifier_sql(expression) 1119 1120 if expression.args.get("global"): 1121 identifier = f"##{identifier}" 1122 elif expression.args.get("temporary"): 1123 identifier = f"#{identifier}" 1124 1125 return identifier
1167 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1168 variable = self.sql(expression, "this") 1169 default = self.sql(expression, "default") 1170 default = f" = {default}" if default else "" 1171 1172 kind = self.sql(expression, "kind") 1173 if isinstance(expression.args.get("kind"), exp.Schema): 1174 kind = f"TABLE {kind}" 1175 1176 return f"{variable} AS {kind}{default}"
Inherited Members
- sqlglot.generator.Generator
- Generator
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- EXPLICIT_SET_OP
- WRAP_DERIVED_VALUES
- CREATE_FUNCTION_RETURN_AS
- MATCHED_BY_SOURCE
- SINGLE_STRING_INTERVAL
- INTERVAL_ALLOWS_PLURAL_FORM
- LIMIT_ONLY_LITERALS
- RENAME_TABLE_WITH_DB
- GROUPINGS_SEP
- INDEX_ON
- JOIN_HINTS
- TABLE_HINTS
- QUERY_HINT_SEP
- IS_BOOL_ALLOWED
- DUPLICATE_KEY_UPDATE_WITH_SET
- EXTRACT_ALLOWS_QUOTES
- TZ_TO_WITH_TIME_ZONE
- VALUES_AS_TABLE
- UNNEST_WITH_ORDINALITY
- AGGREGATE_FILTER_SUPPORTED
- SEMI_ANTI_JOIN_WITH_SIDE
- SUPPORTS_TABLE_COPY
- TABLESAMPLE_REQUIRES_PARENS
- TABLESAMPLE_SIZE_IS_ROWS
- TABLESAMPLE_KEYWORDS
- TABLESAMPLE_WITH_METHOD
- COLLATE_IS_FUNC
- DATA_TYPE_SPECIFIERS_ALLOWED
- LAST_DAY_SUPPORTS_DATE_PART
- SUPPORTS_TABLE_ALIAS_COLUMNS
- UNPIVOT_ALIASES_ARE_IDENTIFIERS
- JSON_KEY_VALUE_PAIR_SEP
- INSERT_OVERWRITE
- SUPPORTS_UNLOGGED_TABLES
- SUPPORTS_CREATE_TABLE_LIKE
- LIKE_PROPERTY_INSIDE_SCHEMA
- MULTI_ARG_DISTINCT
- JSON_TYPE_REQUIRED_FOR_EXTRACTION
- JSON_PATH_SINGLE_QUOTE_ESCAPE
- CAN_IMPLEMENT_ARRAY_ANY
- COPY_PARAMS_ARE_WRAPPED
- COPY_HAS_INTO_KEYWORD
- STAR_EXCEPT
- HEX_FUNC
- WITH_PROPERTIES_PREFIX
- QUOTE_JSON_PATH
- PAD_FILL_PATTERN_IS_REQUIRED
- SUPPORTS_EXPLODING_PROJECTIONS
- ARRAY_CONCAT_IS_VAR_LEN
- SUPPORTS_CONVERT_TIMEZONE
- TIME_PART_SINGULARS
- TOKEN_MAPPING
- STRUCT_DELIMITER
- PARAMETER_TOKEN
- NAMED_PLACEHOLDER_TOKEN
- RESERVED_KEYWORDS
- WITH_SEPARATED_COMMENTS
- EXCLUDE_COMMENTS
- UNWRAPPED_INTERVAL_VALUES
- PARAMETERIZABLE_TEXT_TYPES
- SENTINEL_LINE_BREAK
- pretty
- identify
- normalize
- pad
- unsupported_level
- max_unsupported
- leading_comma
- max_text_width
- comments
- dialect
- normalize_functions
- unsupported_messages
- generate
- preprocess
- unsupported
- sep
- seg
- pad_comment
- maybe_comment
- wrap
- no_identify
- normalize_func
- indent
- sql
- uncache_sql
- cache_sql
- characterset_sql
- column_parts
- column_sql
- columnposition_sql
- columndef_sql
- columnconstraint_sql
- computedcolumnconstraint_sql
- autoincrementcolumnconstraint_sql
- compresscolumnconstraint_sql
- generatedasidentitycolumnconstraint_sql
- generatedasrowcolumnconstraint_sql
- periodforsystemtimeconstraint_sql
- notnullcolumnconstraint_sql
- transformcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- sequenceproperties_sql
- clone_sql
- describe_sql
- heredoc_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- unicodestring_sql
- rawstring_sql
- datatypeparam_sql
- datatype_sql
- directory_sql
- delete_sql
- except_sql
- except_op
- fetch_sql
- filter_sql
- hint_sql
- indexparameters_sql
- index_sql
- hex_sql
- lowerhex_sql
- inputoutputformat_sql
- national_sql
- properties_sql
- root_properties
- properties
- with_properties
- locate_properties
- property_name
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- partitionboundspec_sql
- partitionedofproperty_sql
- lockingproperty_sql
- withdataproperty_sql
- withsystemversioningproperty_sql
- insert_sql
- intersect_sql
- intersect_op
- introducer_sql
- kill_sql
- pseudotype_sql
- objectidentifier_sql
- onconflict_sql
- rowformatdelimitedproperty_sql
- withtablehint_sql
- indextablehint_sql
- historicaldata_sql
- table_parts
- table_sql
- tablesample_sql
- pivot_sql
- tuple_sql
- update_sql
- values_sql
- var_sql
- into_sql
- from_sql
- group_sql
- having_sql
- connect_sql
- prior_sql
- join_sql
- lambda_sql
- lateral_sql
- limit_sql
- set_sql
- pragma_sql
- lock_sql
- literal_sql
- escape_str
- loaddata_sql
- null_sql
- order_sql
- withfill_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognizemeasure_sql
- matchrecognize_sql
- query_modifiers
- offset_limit_modifiers
- after_limit_modifiers
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- set_operations
- union_sql
- union_op
- unnest_sql
- prewhere_sql
- where_sql
- window_sql
- partition_by_sql
- windowspec_sql
- withingroup_sql
- between_sql
- bracket_offset_expressions
- bracket_sql
- all_sql
- any_sql
- exists_sql
- case_sql
- nextvaluefor_sql
- extract_sql
- trim_sql
- convert_concat_args
- concat_sql
- concatws_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- if_sql
- matchagainst_sql
- jsonkeyvalue_sql
- jsonpath_sql
- json_path_part
- formatjson_sql
- jsonobject_sql
- jsonobjectagg_sql
- jsonarray_sql
- jsonarrayagg_sql
- jsoncolumndef_sql
- jsonschema_sql
- jsontable_sql
- openjsoncolumndef_sql
- openjson_sql
- in_sql
- in_unnest_op
- interval_sql
- return_sql
- reference_sql
- anonymous_sql
- paren_sql
- neg_sql
- not_sql
- alias_sql
- pivotalias_sql
- aliases_sql
- atindex_sql
- attimezone_sql
- fromtimezone_sql
- add_sql
- and_sql
- or_sql
- xor_sql
- connector_sql
- bitwiseand_sql
- bitwiseleftshift_sql
- bitwisenot_sql
- bitwiseor_sql
- bitwiserightshift_sql
- bitwisexor_sql
- cast_sql
- currentdate_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- altercolumn_sql
- alterdiststyle_sql
- altersortkey_sql
- renametable_sql
- renamecolumn_sql
- alterset_sql
- add_column_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- havingmax_sql
- intdiv_sql
- dpipe_sql
- div_sql
- overlaps_sql
- distance_sql
- dot_sql
- eq_sql
- propertyeq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- ilikeany_sql
- like_sql
- likeany_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- slice_sql
- sub_sql
- trycast_sql
- try_sql
- log_sql
- use_sql
- binary
- function_fallback_sql
- func
- format_args
- too_wide
- format_time
- expressions
- op_expressions
- naked_property
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql
- tochar_sql
- tonumber_sql
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- oncluster_sql
- clusteredbyproperty_sql
- anyvalue_sql
- querytransform_sql
- indexconstraintoption_sql
- checkcolumnconstraint_sql
- indexcolumnconstraint_sql
- nvl2_sql
- comprehension_sql
- columnprefix_sql
- opclass_sql
- predict_sql
- forin_sql
- refresh_sql
- toarray_sql
- tsordstotime_sql
- tsordstotimestamp_sql
- tsordstodate_sql
- unixdate_sql
- lastday_sql
- dateadd_sql
- arrayany_sql
- struct_sql
- partitionrange_sql
- truncatetable_sql
- copyparameter_sql
- credentials_sql
- copy_sql
- semicolon_sql
- datadeletionproperty_sql
- maskingpolicycolumnconstraint_sql
- gapfill_sql
- scoperesolution_sql
- parsejson_sql
- rand_sql
- changes_sql
- pad_sql
- summarize_sql
- explodinggenerateseries_sql
- arrayconcat_sql
- converttimezone_sql