sqlglot.dialects.tsql
1from __future__ import annotations 2 3import datetime 4import re 5import typing as t 6from functools import partial, reduce 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.parser import build_coalesce 25from sqlglot.time import format_time 26from sqlglot.tokens import TokenType 27 28if t.TYPE_CHECKING: 29 from sqlglot._typing import E 30 31FULL_FORMAT_TIME_MAPPING = { 32 "weekday": "%A", 33 "dw": "%A", 34 "w": "%A", 35 "month": "%B", 36 "mm": "%B", 37 "m": "%B", 38} 39 40DATE_DELTA_INTERVAL = { 41 "year": "year", 42 "yyyy": "year", 43 "yy": "year", 44 "quarter": "quarter", 45 "qq": "quarter", 46 "q": "quarter", 47 "month": "month", 48 "mm": "month", 49 "m": "month", 50 "week": "week", 51 "ww": "week", 52 "wk": "week", 53 "day": "day", 54 "dd": "day", 55 "d": "day", 56} 57 58 59DATE_FMT_RE = re.compile("([dD]{1,2})|([mM]{1,2})|([yY]{1,4})|([hH]{1,2})|([sS]{1,2})") 60 61# N = Numeric, C=Currency 62TRANSPILE_SAFE_NUMBER_FMT = {"N", "C"} 63 64DEFAULT_START_DATE = datetime.date(1900, 1, 1) 65 66BIT_TYPES = {exp.EQ, exp.NEQ, exp.Is, exp.In, exp.Select, exp.Alias} 67 68# Unsupported options: 69# - OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] ) 70# - TABLE HINT 71OPTIONS: parser.OPTIONS_TYPE = { 72 **dict.fromkeys( 73 ( 74 "DISABLE_OPTIMIZED_PLAN_FORCING", 75 "FAST", 76 "IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX", 77 "LABEL", 78 "MAXDOP", 79 "MAXRECURSION", 80 "MAX_GRANT_PERCENT", 81 "MIN_GRANT_PERCENT", 82 "NO_PERFORMANCE_SPOOL", 83 "QUERYTRACEON", 84 "RECOMPILE", 85 ), 86 tuple(), 87 ), 88 "CONCAT": ("UNION",), 89 "DISABLE": ("EXTERNALPUSHDOWN", "SCALEOUTEXECUTION"), 90 "EXPAND": ("VIEWS",), 91 "FORCE": ("EXTERNALPUSHDOWN", "ORDER", "SCALEOUTEXECUTION"), 92 "HASH": ("GROUP", "JOIN", "UNION"), 93 "KEEP": ("PLAN",), 94 "KEEPFIXED": ("PLAN",), 95 "LOOP": ("JOIN",), 96 "MERGE": ("JOIN", "UNION"), 97 "OPTIMIZE": (("FOR", "UNKNOWN"),), 98 "ORDER": ("GROUP",), 99 "PARAMETERIZATION": ("FORCED", "SIMPLE"), 100 "ROBUST": ("PLAN",), 101 "USE": ("PLAN",), 102} 103 104OPTIONS_THAT_REQUIRE_EQUAL = ("MAX_GRANT_PERCENT", "MIN_GRANT_PERCENT", "LABEL") 105 106 107def _build_formatted_time( 108 exp_class: t.Type[E], full_format_mapping: t.Optional[bool] = None 109) -> t.Callable[[t.List], E]: 110 def _builder(args: t.List) -> E: 111 assert len(args) == 2 112 113 return exp_class( 114 this=exp.cast(args[1], exp.DataType.Type.DATETIME), 115 format=exp.Literal.string( 116 format_time( 117 args[0].name.lower(), 118 ( 119 {**TSQL.TIME_MAPPING, **FULL_FORMAT_TIME_MAPPING} 120 if full_format_mapping 121 else TSQL.TIME_MAPPING 122 ), 123 ) 124 ), 125 ) 126 127 return _builder 128 129 130def _build_format(args: t.List) -> exp.NumberToStr | exp.TimeToStr: 131 this = seq_get(args, 0) 132 fmt = seq_get(args, 1) 133 culture = seq_get(args, 2) 134 135 number_fmt = fmt and (fmt.name in TRANSPILE_SAFE_NUMBER_FMT or not DATE_FMT_RE.search(fmt.name)) 136 137 if number_fmt: 138 return exp.NumberToStr(this=this, format=fmt, culture=culture) 139 140 if fmt: 141 fmt = exp.Literal.string( 142 format_time(fmt.name, TSQL.FORMAT_TIME_MAPPING) 143 if len(fmt.name) == 1 144 else format_time(fmt.name, TSQL.TIME_MAPPING) 145 ) 146 147 return exp.TimeToStr(this=this, format=fmt, culture=culture) 148 149 150def _build_eomonth(args: t.List) -> exp.LastDay: 151 date = exp.TsOrDsToDate(this=seq_get(args, 0)) 152 month_lag = seq_get(args, 1) 153 154 if month_lag is None: 155 this: exp.Expression = date 156 else: 157 unit = DATE_DELTA_INTERVAL.get("month") 158 this = exp.DateAdd(this=date, expression=month_lag, unit=unit and exp.var(unit)) 159 160 return exp.LastDay(this=this) 161 162 163def _build_hashbytes(args: t.List) -> exp.Expression: 164 kind, data = args 165 kind = kind.name.upper() if kind.is_string else "" 166 167 if kind == "MD5": 168 args.pop(0) 169 return exp.MD5(this=data) 170 if kind in ("SHA", "SHA1"): 171 args.pop(0) 172 return exp.SHA(this=data) 173 if kind == "SHA2_256": 174 return exp.SHA2(this=data, length=exp.Literal.number(256)) 175 if kind == "SHA2_512": 176 return exp.SHA2(this=data, length=exp.Literal.number(512)) 177 178 return exp.func("HASHBYTES", *args) 179 180 181DATEPART_ONLY_FORMATS = {"DW", "HOUR", "QUARTER"} 182 183 184def _format_sql(self: TSQL.Generator, expression: exp.NumberToStr | exp.TimeToStr) -> str: 185 fmt = expression.args["format"] 186 187 if not isinstance(expression, exp.NumberToStr): 188 if fmt.is_string: 189 mapped_fmt = format_time(fmt.name, TSQL.INVERSE_TIME_MAPPING) 190 191 name = (mapped_fmt or "").upper() 192 if name in DATEPART_ONLY_FORMATS: 193 return self.func("DATEPART", name, expression.this) 194 195 fmt_sql = self.sql(exp.Literal.string(mapped_fmt)) 196 else: 197 fmt_sql = self.format_time(expression) or self.sql(fmt) 198 else: 199 fmt_sql = self.sql(fmt) 200 201 return self.func("FORMAT", expression.this, fmt_sql, expression.args.get("culture")) 202 203 204def _string_agg_sql(self: TSQL.Generator, expression: exp.GroupConcat) -> str: 205 this = expression.this 206 distinct = expression.find(exp.Distinct) 207 if distinct: 208 # exp.Distinct can appear below an exp.Order or an exp.GroupConcat expression 209 self.unsupported("T-SQL STRING_AGG doesn't support DISTINCT.") 210 this = distinct.pop().expressions[0] 211 212 order = "" 213 if isinstance(expression.this, exp.Order): 214 if expression.this.this: 215 this = expression.this.this.pop() 216 # Order has a leading space 217 order = f" WITHIN GROUP ({self.sql(expression.this)[1:]})" 218 219 separator = expression.args.get("separator") or exp.Literal.string(",") 220 return f"STRING_AGG({self.format_args(this, separator)}){order}" 221 222 223def _build_date_delta( 224 exp_class: t.Type[E], unit_mapping: t.Optional[t.Dict[str, str]] = None 225) -> t.Callable[[t.List], E]: 226 def _builder(args: t.List) -> E: 227 unit = seq_get(args, 0) 228 if unit and unit_mapping: 229 unit = exp.var(unit_mapping.get(unit.name.lower(), unit.name)) 230 231 start_date = seq_get(args, 1) 232 if start_date and start_date.is_number: 233 # Numeric types are valid DATETIME values 234 if start_date.is_int: 235 adds = DEFAULT_START_DATE + datetime.timedelta(days=int(start_date.this)) 236 start_date = exp.Literal.string(adds.strftime("%F")) 237 else: 238 # We currently don't handle float values, i.e. they're not converted to equivalent DATETIMEs. 239 # This is not a problem when generating T-SQL code, it is when transpiling to other dialects. 240 return exp_class(this=seq_get(args, 2), expression=start_date, unit=unit) 241 242 return exp_class( 243 this=exp.TimeStrToTime(this=seq_get(args, 2)), 244 expression=exp.TimeStrToTime(this=start_date), 245 unit=unit, 246 ) 247 248 return _builder 249 250 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 281 282 283# https://learn.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver16#syntax 284def _build_datetimefromparts(args: t.List) -> exp.TimestampFromParts: 285 return exp.TimestampFromParts( 286 year=seq_get(args, 0), 287 month=seq_get(args, 1), 288 day=seq_get(args, 2), 289 hour=seq_get(args, 3), 290 min=seq_get(args, 4), 291 sec=seq_get(args, 5), 292 milli=seq_get(args, 6), 293 ) 294 295 296# https://learn.microsoft.com/en-us/sql/t-sql/functions/timefromparts-transact-sql?view=sql-server-ver16#syntax 297def _build_timefromparts(args: t.List) -> exp.TimeFromParts: 298 return exp.TimeFromParts( 299 hour=seq_get(args, 0), 300 min=seq_get(args, 1), 301 sec=seq_get(args, 2), 302 fractions=seq_get(args, 3), 303 precision=seq_get(args, 4), 304 ) 305 306 307def _build_with_arg_as_text( 308 klass: t.Type[exp.Expression], 309) -> t.Callable[[t.List[exp.Expression]], exp.Expression]: 310 def _parse(args: t.List[exp.Expression]) -> exp.Expression: 311 this = seq_get(args, 0) 312 313 if this and not this.is_string: 314 this = exp.cast(this, exp.DataType.Type.TEXT) 315 316 expression = seq_get(args, 1) 317 kwargs = {"this": this} 318 319 if expression: 320 kwargs["expression"] = expression 321 322 return klass(**kwargs) 323 324 return _parse 325 326 327def _build_json_query(args: t.List, dialect: Dialect) -> exp.JSONExtract: 328 if len(args) == 1: 329 # The default value for path is '$'. As a result, if you don't provide a 330 # value for path, JSON_QUERY returns the input expression. 331 args.append(exp.Literal.string("$")) 332 333 return parser.build_extract_json_with_path(exp.JSONExtract)(args, dialect) 334 335 336def _json_extract_sql( 337 self: TSQL.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar 338) -> str: 339 json_query = self.func("JSON_QUERY", expression.this, expression.expression) 340 json_value = self.func("JSON_VALUE", expression.this, expression.expression) 341 return self.func("ISNULL", json_query, json_value) 342 343 344def _timestrtotime_sql(self: TSQL.Generator, expression: exp.TimeStrToTime): 345 sql = timestrtotime_sql(self, expression) 346 if expression.args.get("zone"): 347 # If there is a timezone, produce an expression like: 348 # CAST('2020-01-01 12:13:14-08:00' AS DATETIMEOFFSET) AT TIME ZONE 'UTC' 349 # If you dont have AT TIME ZONE 'UTC', wrapping that expression in another cast back to DATETIME2 just drops the timezone information 350 return self.sql(exp.AtTimeZone(this=sql, zone=exp.Literal.string("UTC"))) 351 return sql 352 353 354class TSQL(Dialect): 355 SUPPORTS_SEMI_ANTI_JOIN = False 356 LOG_BASE_FIRST = False 357 TYPED_DIVISION = True 358 CONCAT_COALESCE = True 359 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 360 361 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 362 363 TIME_MAPPING = { 364 "year": "%Y", 365 "dayofyear": "%j", 366 "day": "%d", 367 "dy": "%d", 368 "y": "%Y", 369 "week": "%W", 370 "ww": "%W", 371 "wk": "%W", 372 "hour": "%h", 373 "hh": "%I", 374 "minute": "%M", 375 "mi": "%M", 376 "n": "%M", 377 "second": "%S", 378 "ss": "%S", 379 "s": "%-S", 380 "millisecond": "%f", 381 "ms": "%f", 382 "weekday": "%W", 383 "dw": "%W", 384 "month": "%m", 385 "mm": "%M", 386 "m": "%-M", 387 "Y": "%Y", 388 "YYYY": "%Y", 389 "YY": "%y", 390 "MMMM": "%B", 391 "MMM": "%b", 392 "MM": "%m", 393 "M": "%-m", 394 "dddd": "%A", 395 "dd": "%d", 396 "d": "%-d", 397 "HH": "%H", 398 "H": "%-H", 399 "h": "%-I", 400 "ffffff": "%f", 401 "yyyy": "%Y", 402 "yy": "%y", 403 } 404 405 CONVERT_FORMAT_MAPPING = { 406 "0": "%b %d %Y %-I:%M%p", 407 "1": "%m/%d/%y", 408 "2": "%y.%m.%d", 409 "3": "%d/%m/%y", 410 "4": "%d.%m.%y", 411 "5": "%d-%m-%y", 412 "6": "%d %b %y", 413 "7": "%b %d, %y", 414 "8": "%H:%M:%S", 415 "9": "%b %d %Y %-I:%M:%S:%f%p", 416 "10": "mm-dd-yy", 417 "11": "yy/mm/dd", 418 "12": "yymmdd", 419 "13": "%d %b %Y %H:%M:ss:%f", 420 "14": "%H:%M:%S:%f", 421 "20": "%Y-%m-%d %H:%M:%S", 422 "21": "%Y-%m-%d %H:%M:%S.%f", 423 "22": "%m/%d/%y %-I:%M:%S %p", 424 "23": "%Y-%m-%d", 425 "24": "%H:%M:%S", 426 "25": "%Y-%m-%d %H:%M:%S.%f", 427 "100": "%b %d %Y %-I:%M%p", 428 "101": "%m/%d/%Y", 429 "102": "%Y.%m.%d", 430 "103": "%d/%m/%Y", 431 "104": "%d.%m.%Y", 432 "105": "%d-%m-%Y", 433 "106": "%d %b %Y", 434 "107": "%b %d, %Y", 435 "108": "%H:%M:%S", 436 "109": "%b %d %Y %-I:%M:%S:%f%p", 437 "110": "%m-%d-%Y", 438 "111": "%Y/%m/%d", 439 "112": "%Y%m%d", 440 "113": "%d %b %Y %H:%M:%S:%f", 441 "114": "%H:%M:%S:%f", 442 "120": "%Y-%m-%d %H:%M:%S", 443 "121": "%Y-%m-%d %H:%M:%S.%f", 444 } 445 446 FORMAT_TIME_MAPPING = { 447 "y": "%B %Y", 448 "d": "%m/%d/%Y", 449 "H": "%-H", 450 "h": "%-I", 451 "s": "%Y-%m-%d %H:%M:%S", 452 "D": "%A,%B,%Y", 453 "f": "%A,%B,%Y %-I:%M %p", 454 "F": "%A,%B,%Y %-I:%M:%S %p", 455 "g": "%m/%d/%Y %-I:%M %p", 456 "G": "%m/%d/%Y %-I:%M:%S %p", 457 "M": "%B %-d", 458 "m": "%B %-d", 459 "O": "%Y-%m-%dT%H:%M:%S", 460 "u": "%Y-%M-%D %H:%M:%S%z", 461 "U": "%A, %B %D, %Y %H:%M:%S%z", 462 "T": "%-I:%M:%S %p", 463 "t": "%-I:%M", 464 "Y": "%a %Y", 465 } 466 467 class Tokenizer(tokens.Tokenizer): 468 IDENTIFIERS = [("[", "]"), '"'] 469 QUOTES = ["'", '"'] 470 HEX_STRINGS = [("0x", ""), ("0X", "")] 471 VAR_SINGLE_TOKENS = {"@", "$", "#"} 472 473 KEYWORDS = { 474 **tokens.Tokenizer.KEYWORDS, 475 "CLUSTERED INDEX": TokenType.INDEX, 476 "DATETIME2": TokenType.DATETIME, 477 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 478 "DECLARE": TokenType.DECLARE, 479 "EXEC": TokenType.COMMAND, 480 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 481 "IMAGE": TokenType.IMAGE, 482 "MONEY": TokenType.MONEY, 483 "NONCLUSTERED INDEX": TokenType.INDEX, 484 "NTEXT": TokenType.TEXT, 485 "OPTION": TokenType.OPTION, 486 "OUTPUT": TokenType.RETURNING, 487 "PRINT": TokenType.COMMAND, 488 "PROC": TokenType.PROCEDURE, 489 "REAL": TokenType.FLOAT, 490 "ROWVERSION": TokenType.ROWVERSION, 491 "SMALLDATETIME": TokenType.DATETIME, 492 "SMALLMONEY": TokenType.SMALLMONEY, 493 "SQL_VARIANT": TokenType.VARIANT, 494 "SYSTEM_USER": TokenType.CURRENT_USER, 495 "TOP": TokenType.TOP, 496 "TIMESTAMP": TokenType.ROWVERSION, 497 "TINYINT": TokenType.UTINYINT, 498 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 499 "UPDATE STATISTICS": TokenType.COMMAND, 500 "XML": TokenType.XML, 501 } 502 KEYWORDS.pop("/*+") 503 504 COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END} 505 506 class Parser(parser.Parser): 507 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 508 LOG_DEFAULTS_TO_LN = True 509 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 510 STRING_ALIASES = True 511 NO_PAREN_IF_COMMANDS = False 512 513 QUERY_MODIFIER_PARSERS = { 514 **parser.Parser.QUERY_MODIFIER_PARSERS, 515 TokenType.OPTION: lambda self: ("options", self._parse_options()), 516 } 517 518 FUNCTIONS = { 519 **parser.Parser.FUNCTIONS, 520 "CHARINDEX": lambda args: exp.StrPosition( 521 this=seq_get(args, 1), 522 substr=seq_get(args, 0), 523 position=seq_get(args, 2), 524 ), 525 "COUNT": lambda args: exp.Count( 526 this=seq_get(args, 0), expressions=args[1:], big_int=False 527 ), 528 "COUNT_BIG": lambda args: exp.Count( 529 this=seq_get(args, 0), expressions=args[1:], big_int=True 530 ), 531 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 532 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 533 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 534 "DATEPART": _build_formatted_time(exp.TimeToStr), 535 "DATETIMEFROMPARTS": _build_datetimefromparts, 536 "EOMONTH": _build_eomonth, 537 "FORMAT": _build_format, 538 "GETDATE": exp.CurrentTimestamp.from_arg_list, 539 "HASHBYTES": _build_hashbytes, 540 "ISNULL": build_coalesce, 541 "JSON_QUERY": _build_json_query, 542 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 543 "LEN": _build_with_arg_as_text(exp.Length), 544 "LEFT": _build_with_arg_as_text(exp.Left), 545 "RIGHT": _build_with_arg_as_text(exp.Right), 546 "REPLICATE": exp.Repeat.from_arg_list, 547 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 548 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 549 "SUSER_NAME": exp.CurrentUser.from_arg_list, 550 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 551 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 552 "TIMEFROMPARTS": _build_timefromparts, 553 } 554 555 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 556 557 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 558 TokenType.TABLE, 559 *parser.Parser.TYPE_TOKENS, 560 } 561 562 STATEMENT_PARSERS = { 563 **parser.Parser.STATEMENT_PARSERS, 564 TokenType.DECLARE: lambda self: self._parse_declare(), 565 } 566 567 RANGE_PARSERS = { 568 **parser.Parser.RANGE_PARSERS, 569 TokenType.DCOLON: lambda self, this: self.expression( 570 exp.ScopeResolution, 571 this=this, 572 expression=self._parse_function() or self._parse_var(any_token=True), 573 ), 574 } 575 576 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 577 COLUMN_OPERATORS = { 578 **parser.Parser.COLUMN_OPERATORS, 579 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 580 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 581 else self.expression(exp.ScopeResolution, this=this, expression=to), 582 } 583 584 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 585 # We want to use _parse_types() if the first token after :: is a known type, 586 # otherwise we could parse something like x::varchar(max) into a function 587 if self._match_set(self.TYPE_TOKENS, advance=False): 588 return self._parse_types() 589 590 return self._parse_function() or self._parse_types() 591 592 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 593 if not self._match(TokenType.OPTION): 594 return None 595 596 def _parse_option() -> t.Optional[exp.Expression]: 597 option = self._parse_var_from_options(OPTIONS) 598 if not option: 599 return None 600 601 self._match(TokenType.EQ) 602 return self.expression( 603 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 604 ) 605 606 return self._parse_wrapped_csv(_parse_option) 607 608 def _parse_projections(self) -> t.List[exp.Expression]: 609 """ 610 T-SQL supports the syntax alias = expression in the SELECT's projection list, 611 so we transform all parsed Selects to convert their EQ projections into Aliases. 612 613 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 614 """ 615 return [ 616 ( 617 exp.alias_(projection.expression, projection.this.this, copy=False) 618 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 619 else projection 620 ) 621 for projection in super()._parse_projections() 622 ] 623 624 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 625 """Applies to SQL Server and Azure SQL Database 626 COMMIT [ { TRAN | TRANSACTION } 627 [ transaction_name | @tran_name_variable ] ] 628 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 629 630 ROLLBACK { TRAN | TRANSACTION } 631 [ transaction_name | @tran_name_variable 632 | savepoint_name | @savepoint_variable ] 633 """ 634 rollback = self._prev.token_type == TokenType.ROLLBACK 635 636 self._match_texts(("TRAN", "TRANSACTION")) 637 this = self._parse_id_var() 638 639 if rollback: 640 return self.expression(exp.Rollback, this=this) 641 642 durability = None 643 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 644 self._match_text_seq("DELAYED_DURABILITY") 645 self._match(TokenType.EQ) 646 647 if self._match_text_seq("OFF"): 648 durability = False 649 else: 650 self._match(TokenType.ON) 651 durability = True 652 653 self._match_r_paren() 654 655 return self.expression(exp.Commit, this=this, durability=durability) 656 657 def _parse_transaction(self) -> exp.Transaction | exp.Command: 658 """Applies to SQL Server and Azure SQL Database 659 BEGIN { TRAN | TRANSACTION } 660 [ { transaction_name | @tran_name_variable } 661 [ WITH MARK [ 'description' ] ] 662 ] 663 """ 664 if self._match_texts(("TRAN", "TRANSACTION")): 665 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 666 if self._match_text_seq("WITH", "MARK"): 667 transaction.set("mark", self._parse_string()) 668 669 return transaction 670 671 return self._parse_as_command(self._prev) 672 673 def _parse_returns(self) -> exp.ReturnsProperty: 674 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 675 returns = super()._parse_returns() 676 returns.set("table", table) 677 return returns 678 679 def _parse_convert( 680 self, strict: bool, safe: t.Optional[bool] = None 681 ) -> t.Optional[exp.Expression]: 682 this = self._parse_types() 683 self._match(TokenType.COMMA) 684 args = [this, *self._parse_csv(self._parse_assignment)] 685 convert = exp.Convert.from_arg_list(args) 686 convert.set("safe", safe) 687 convert.set("strict", strict) 688 return convert 689 690 def _parse_user_defined_function( 691 self, kind: t.Optional[TokenType] = None 692 ) -> t.Optional[exp.Expression]: 693 this = super()._parse_user_defined_function(kind=kind) 694 695 if ( 696 kind == TokenType.FUNCTION 697 or isinstance(this, exp.UserDefinedFunction) 698 or self._match(TokenType.ALIAS, advance=False) 699 ): 700 return this 701 702 expressions = self._parse_csv(self._parse_function_parameter) 703 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 704 705 def _parse_id_var( 706 self, 707 any_token: bool = True, 708 tokens: t.Optional[t.Collection[TokenType]] = None, 709 ) -> t.Optional[exp.Expression]: 710 is_temporary = self._match(TokenType.HASH) 711 is_global = is_temporary and self._match(TokenType.HASH) 712 713 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 714 if this: 715 if is_global: 716 this.set("global", True) 717 elif is_temporary: 718 this.set("temporary", True) 719 720 return this 721 722 def _parse_create(self) -> exp.Create | exp.Command: 723 create = super()._parse_create() 724 725 if isinstance(create, exp.Create): 726 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 727 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 728 if not create.args.get("properties"): 729 create.set("properties", exp.Properties(expressions=[])) 730 731 create.args["properties"].append("expressions", exp.TemporaryProperty()) 732 733 return create 734 735 def _parse_if(self) -> t.Optional[exp.Expression]: 736 index = self._index 737 738 if self._match_text_seq("OBJECT_ID"): 739 self._parse_wrapped_csv(self._parse_string) 740 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 741 return self._parse_drop(exists=True) 742 self._retreat(index) 743 744 return super()._parse_if() 745 746 def _parse_unique(self) -> exp.UniqueColumnConstraint: 747 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 748 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 749 else: 750 this = self._parse_schema(self._parse_id_var(any_token=False)) 751 752 return self.expression(exp.UniqueColumnConstraint, this=this) 753 754 def _parse_partition(self) -> t.Optional[exp.Partition]: 755 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 756 return None 757 758 def parse_range(): 759 low = self._parse_bitwise() 760 high = self._parse_bitwise() if self._match_text_seq("TO") else None 761 762 return ( 763 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 764 ) 765 766 partition = self.expression( 767 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 768 ) 769 770 self._match_r_paren() 771 772 return partition 773 774 def _parse_declare(self) -> exp.Declare | exp.Command: 775 index = self._index 776 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 777 778 if not expressions or self._curr: 779 self._retreat(index) 780 return self._parse_as_command(self._prev) 781 782 return self.expression(exp.Declare, expressions=expressions) 783 784 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 785 var = self._parse_id_var() 786 if not var: 787 return None 788 789 value = None 790 self._match(TokenType.ALIAS) 791 if self._match(TokenType.TABLE): 792 data_type = self._parse_schema() 793 else: 794 data_type = self._parse_types() 795 if self._match(TokenType.EQ): 796 value = self._parse_bitwise() 797 798 return self.expression(exp.DeclareItem, this=var, kind=data_type, default=value) 799 800 class Generator(generator.Generator): 801 LIMIT_IS_TOP = True 802 QUERY_HINTS = False 803 RETURNING_END = False 804 NVL2_SUPPORTED = False 805 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 806 LIMIT_FETCH = "FETCH" 807 COMPUTED_COLUMN_WITH_TYPE = False 808 CTE_RECURSIVE_KEYWORD_REQUIRED = False 809 ENSURE_BOOLS = True 810 NULL_ORDERING_SUPPORTED = None 811 SUPPORTS_SINGLE_ARG_CONCAT = False 812 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 813 SUPPORTS_SELECT_INTO = True 814 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 815 SUPPORTS_TO_NUMBER = False 816 SET_OP_MODIFIERS = False 817 COPY_PARAMS_EQ_REQUIRED = True 818 PARSE_JSON_NAME = None 819 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 820 821 EXPRESSIONS_WITHOUT_NESTED_CTES = { 822 exp.Create, 823 exp.Delete, 824 exp.Insert, 825 exp.Intersect, 826 exp.Except, 827 exp.Merge, 828 exp.Select, 829 exp.Subquery, 830 exp.Union, 831 exp.Update, 832 } 833 834 SUPPORTED_JSON_PATH_PARTS = { 835 exp.JSONPathKey, 836 exp.JSONPathRoot, 837 exp.JSONPathSubscript, 838 } 839 840 TYPE_MAPPING = { 841 **generator.Generator.TYPE_MAPPING, 842 exp.DataType.Type.BOOLEAN: "BIT", 843 exp.DataType.Type.DECIMAL: "NUMERIC", 844 exp.DataType.Type.DATETIME: "DATETIME2", 845 exp.DataType.Type.DOUBLE: "FLOAT", 846 exp.DataType.Type.INT: "INTEGER", 847 exp.DataType.Type.ROWVERSION: "ROWVERSION", 848 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 849 exp.DataType.Type.TIMESTAMP: "DATETIME2", 850 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 851 exp.DataType.Type.UTINYINT: "TINYINT", 852 exp.DataType.Type.VARIANT: "SQL_VARIANT", 853 } 854 855 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 856 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 857 858 TRANSFORMS = { 859 **generator.Generator.TRANSFORMS, 860 exp.AnyValue: any_value_to_max_sql, 861 exp.ArrayToString: rename_func("STRING_AGG"), 862 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 863 exp.DateAdd: date_delta_sql("DATEADD"), 864 exp.DateDiff: date_delta_sql("DATEDIFF"), 865 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 866 exp.CurrentDate: rename_func("GETDATE"), 867 exp.CurrentTimestamp: rename_func("GETDATE"), 868 exp.DateStrToDate: datestrtodate_sql, 869 exp.Extract: rename_func("DATEPART"), 870 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 871 exp.GroupConcat: _string_agg_sql, 872 exp.If: rename_func("IIF"), 873 exp.JSONExtract: _json_extract_sql, 874 exp.JSONExtractScalar: _json_extract_sql, 875 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 876 exp.Max: max_or_greatest, 877 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 878 exp.Min: min_or_least, 879 exp.NumberToStr: _format_sql, 880 exp.Repeat: rename_func("REPLICATE"), 881 exp.Select: transforms.preprocess( 882 [ 883 transforms.eliminate_distinct_on, 884 transforms.eliminate_semi_and_anti_joins, 885 transforms.eliminate_qualify, 886 transforms.unnest_generate_date_array_using_recursive_cte, 887 ] 888 ), 889 exp.Stddev: rename_func("STDEV"), 890 exp.StrPosition: lambda self, e: self.func( 891 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 892 ), 893 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 894 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 895 exp.SHA2: lambda self, e: self.func( 896 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 897 ), 898 exp.TemporaryProperty: lambda self, e: "", 899 exp.TimeStrToTime: _timestrtotime_sql, 900 exp.TimeToStr: _format_sql, 901 exp.Trim: trim_sql, 902 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 903 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 904 } 905 906 TRANSFORMS.pop(exp.ReturnsProperty) 907 908 PROPERTIES_LOCATION = { 909 **generator.Generator.PROPERTIES_LOCATION, 910 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 911 } 912 913 def scope_resolution(self, rhs: str, scope_name: str) -> str: 914 return f"{scope_name}::{rhs}" 915 916 def select_sql(self, expression: exp.Select) -> str: 917 if expression.args.get("offset"): 918 if not expression.args.get("order"): 919 # ORDER BY is required in order to use OFFSET in a query, so we use 920 # a noop order by, since we don't really care about the order. 921 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 922 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 923 924 limit = expression.args.get("limit") 925 if isinstance(limit, exp.Limit): 926 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 927 # we replace here because otherwise TOP would be generated in select_sql 928 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 929 930 return super().select_sql(expression) 931 932 def convert_sql(self, expression: exp.Convert) -> str: 933 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 934 return self.func( 935 name, expression.this, expression.expression, expression.args.get("style") 936 ) 937 938 def queryoption_sql(self, expression: exp.QueryOption) -> str: 939 option = self.sql(expression, "this") 940 value = self.sql(expression, "expression") 941 if value: 942 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 943 return f"{option} {optional_equal_sign}{value}" 944 return option 945 946 def lateral_op(self, expression: exp.Lateral) -> str: 947 cross_apply = expression.args.get("cross_apply") 948 if cross_apply is True: 949 return "CROSS APPLY" 950 if cross_apply is False: 951 return "OUTER APPLY" 952 953 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 954 self.unsupported("LATERAL clause is not supported.") 955 return "LATERAL" 956 957 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 958 nano = expression.args.get("nano") 959 if nano is not None: 960 nano.pop() 961 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 962 963 if expression.args.get("fractions") is None: 964 expression.set("fractions", exp.Literal.number(0)) 965 if expression.args.get("precision") is None: 966 expression.set("precision", exp.Literal.number(0)) 967 968 return rename_func("TIMEFROMPARTS")(self, expression) 969 970 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 971 zone = expression.args.get("zone") 972 if zone is not None: 973 zone.pop() 974 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 975 976 nano = expression.args.get("nano") 977 if nano is not None: 978 nano.pop() 979 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 980 981 if expression.args.get("milli") is None: 982 expression.set("milli", exp.Literal.number(0)) 983 984 return rename_func("DATETIMEFROMPARTS")(self, expression) 985 986 def setitem_sql(self, expression: exp.SetItem) -> str: 987 this = expression.this 988 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 989 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 990 return f"{self.sql(this.left)} {self.sql(this.right)}" 991 992 return super().setitem_sql(expression) 993 994 def boolean_sql(self, expression: exp.Boolean) -> str: 995 if type(expression.parent) in BIT_TYPES or isinstance( 996 expression.find_ancestor(exp.Values, exp.Select), exp.Values 997 ): 998 return "1" if expression.this else "0" 999 1000 return "(1 = 1)" if expression.this else "(1 = 0)" 1001 1002 def is_sql(self, expression: exp.Is) -> str: 1003 if isinstance(expression.expression, exp.Boolean): 1004 return self.binary(expression, "=") 1005 return self.binary(expression, "IS") 1006 1007 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 1008 sql = self.sql(expression, "this") 1009 properties = expression.args.get("properties") 1010 1011 if sql[:1] != "#" and any( 1012 isinstance(prop, exp.TemporaryProperty) 1013 for prop in (properties.expressions if properties else []) 1014 ): 1015 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1016 1017 return sql 1018 1019 def create_sql(self, expression: exp.Create) -> str: 1020 kind = expression.kind 1021 exists = expression.args.pop("exists", None) 1022 1023 like_property = expression.find(exp.LikeProperty) 1024 if like_property: 1025 ctas_expression = like_property.this 1026 else: 1027 ctas_expression = expression.expression 1028 1029 if kind == "VIEW": 1030 expression.this.set("catalog", None) 1031 with_ = expression.args.get("with") 1032 if ctas_expression and with_: 1033 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1034 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1035 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1036 ctas_expression.set("with", with_.pop()) 1037 1038 sql = super().create_sql(expression) 1039 1040 table = expression.find(exp.Table) 1041 1042 # Convert CTAS statement to SELECT .. INTO .. 1043 if kind == "TABLE" and ctas_expression: 1044 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1045 ctas_expression = ctas_expression.subquery() 1046 1047 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1048 select_into.set("into", exp.Into(this=table)) 1049 1050 if like_property: 1051 select_into.limit(0, copy=False) 1052 1053 sql = self.sql(select_into) 1054 1055 if exists: 1056 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1057 sql = self.sql(exp.Literal.string(sql)) 1058 if kind == "SCHEMA": 1059 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1060 elif kind == "TABLE": 1061 assert table 1062 where = exp.and_( 1063 exp.column("table_name").eq(table.name), 1064 exp.column("table_schema").eq(table.db) if table.db else None, 1065 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1066 ) 1067 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1068 elif kind == "INDEX": 1069 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1070 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1071 elif expression.args.get("replace"): 1072 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1073 1074 return self.prepend_ctes(expression, sql) 1075 1076 def count_sql(self, expression: exp.Count) -> str: 1077 func_name = "COUNT_BIG" if expression.args.get("big_int") else "COUNT" 1078 return rename_func(func_name)(self, expression) 1079 1080 def offset_sql(self, expression: exp.Offset) -> str: 1081 return f"{super().offset_sql(expression)} ROWS" 1082 1083 def version_sql(self, expression: exp.Version) -> str: 1084 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1085 this = f"FOR {name}" 1086 expr = expression.expression 1087 kind = expression.text("kind") 1088 if kind in ("FROM", "BETWEEN"): 1089 args = expr.expressions 1090 sep = "TO" if kind == "FROM" else "AND" 1091 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1092 else: 1093 expr_sql = self.sql(expr) 1094 1095 expr_sql = f" {expr_sql}" if expr_sql else "" 1096 return f"{this} {kind}{expr_sql}" 1097 1098 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1099 table = expression.args.get("table") 1100 table = f"{table} " if table else "" 1101 return f"RETURNS {table}{self.sql(expression, 'this')}" 1102 1103 def returning_sql(self, expression: exp.Returning) -> str: 1104 into = self.sql(expression, "into") 1105 into = self.seg(f"INTO {into}") if into else "" 1106 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1107 1108 def transaction_sql(self, expression: exp.Transaction) -> str: 1109 this = self.sql(expression, "this") 1110 this = f" {this}" if this else "" 1111 mark = self.sql(expression, "mark") 1112 mark = f" WITH MARK {mark}" if mark else "" 1113 return f"BEGIN TRANSACTION{this}{mark}" 1114 1115 def commit_sql(self, expression: exp.Commit) -> str: 1116 this = self.sql(expression, "this") 1117 this = f" {this}" if this else "" 1118 durability = expression.args.get("durability") 1119 durability = ( 1120 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1121 if durability is not None 1122 else "" 1123 ) 1124 return f"COMMIT TRANSACTION{this}{durability}" 1125 1126 def rollback_sql(self, expression: exp.Rollback) -> str: 1127 this = self.sql(expression, "this") 1128 this = f" {this}" if this else "" 1129 return f"ROLLBACK TRANSACTION{this}" 1130 1131 def identifier_sql(self, expression: exp.Identifier) -> str: 1132 identifier = super().identifier_sql(expression) 1133 1134 if expression.args.get("global"): 1135 identifier = f"##{identifier}" 1136 elif expression.args.get("temporary"): 1137 identifier = f"#{identifier}" 1138 1139 return identifier 1140 1141 def constraint_sql(self, expression: exp.Constraint) -> str: 1142 this = self.sql(expression, "this") 1143 expressions = self.expressions(expression, flat=True, sep=" ") 1144 return f"CONSTRAINT {this} {expressions}" 1145 1146 def length_sql(self, expression: exp.Length) -> str: 1147 return self._uncast_text(expression, "LEN") 1148 1149 def right_sql(self, expression: exp.Right) -> str: 1150 return self._uncast_text(expression, "RIGHT") 1151 1152 def left_sql(self, expression: exp.Left) -> str: 1153 return self._uncast_text(expression, "LEFT") 1154 1155 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1156 this = expression.this 1157 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1158 this_sql = self.sql(this, "this") 1159 else: 1160 this_sql = self.sql(this) 1161 expression_sql = self.sql(expression, "expression") 1162 return self.func(name, this_sql, expression_sql if expression_sql else None) 1163 1164 def partition_sql(self, expression: exp.Partition) -> str: 1165 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1166 1167 def alter_sql(self, expression: exp.Alter) -> str: 1168 action = seq_get(expression.args.get("actions") or [], 0) 1169 if isinstance(action, exp.RenameTable): 1170 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1171 return super().alter_sql(expression) 1172 1173 def drop_sql(self, expression: exp.Drop) -> str: 1174 if expression.args["kind"] == "VIEW": 1175 expression.this.set("catalog", None) 1176 return super().drop_sql(expression) 1177 1178 def declare_sql(self, expression: exp.Declare) -> str: 1179 return f"DECLARE {self.expressions(expression, flat=True)}" 1180 1181 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1182 variable = self.sql(expression, "this") 1183 default = self.sql(expression, "default") 1184 default = f" = {default}" if default else "" 1185 1186 kind = self.sql(expression, "kind") 1187 if isinstance(expression.args.get("kind"), exp.Schema): 1188 kind = f"TABLE {kind}" 1189 1190 return f"{variable} AS {kind}{default}" 1191 1192 def options_modifier(self, expression: exp.Expression) -> str: 1193 options = self.expressions(expression, key="options") 1194 return f" OPTION{self.wrap(options)}" if options else "" 1195 1196 def dpipe_sql(self, expression: exp.DPipe) -> str: 1197 return self.sql( 1198 reduce(lambda x, y: exp.Add(this=x, expression=y), expression.flatten()) 1199 )
252def qualify_derived_table_outputs(expression: exp.Expression) -> exp.Expression: 253 """Ensures all (unnamed) output columns are aliased for CTEs and Subqueries.""" 254 alias = expression.args.get("alias") 255 256 if ( 257 isinstance(expression, (exp.CTE, exp.Subquery)) 258 and isinstance(alias, exp.TableAlias) 259 and not alias.columns 260 ): 261 from sqlglot.optimizer.qualify_columns import qualify_outputs 262 263 # We keep track of the unaliased column projection indexes instead of the expressions 264 # themselves, because the latter are going to be replaced by new nodes when the aliases 265 # are added and hence we won't be able to reach these newly added Alias parents 266 query = expression.this 267 unaliased_column_indexes = ( 268 i for i, c in enumerate(query.selects) if isinstance(c, exp.Column) and not c.alias 269 ) 270 271 qualify_outputs(query) 272 273 # Preserve the quoting information of columns for newly added Alias nodes 274 query_selects = query.selects 275 for select_index in unaliased_column_indexes: 276 alias = query_selects[select_index] 277 column = alias.this 278 if isinstance(column.this, exp.Identifier): 279 alias.args["alias"].set("quoted", column.this.quoted) 280 281 return expression
Ensures all (unnamed) output columns are aliased for CTEs and Subqueries.
355class TSQL(Dialect): 356 SUPPORTS_SEMI_ANTI_JOIN = False 357 LOG_BASE_FIRST = False 358 TYPED_DIVISION = True 359 CONCAT_COALESCE = True 360 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 361 362 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 363 364 TIME_MAPPING = { 365 "year": "%Y", 366 "dayofyear": "%j", 367 "day": "%d", 368 "dy": "%d", 369 "y": "%Y", 370 "week": "%W", 371 "ww": "%W", 372 "wk": "%W", 373 "hour": "%h", 374 "hh": "%I", 375 "minute": "%M", 376 "mi": "%M", 377 "n": "%M", 378 "second": "%S", 379 "ss": "%S", 380 "s": "%-S", 381 "millisecond": "%f", 382 "ms": "%f", 383 "weekday": "%W", 384 "dw": "%W", 385 "month": "%m", 386 "mm": "%M", 387 "m": "%-M", 388 "Y": "%Y", 389 "YYYY": "%Y", 390 "YY": "%y", 391 "MMMM": "%B", 392 "MMM": "%b", 393 "MM": "%m", 394 "M": "%-m", 395 "dddd": "%A", 396 "dd": "%d", 397 "d": "%-d", 398 "HH": "%H", 399 "H": "%-H", 400 "h": "%-I", 401 "ffffff": "%f", 402 "yyyy": "%Y", 403 "yy": "%y", 404 } 405 406 CONVERT_FORMAT_MAPPING = { 407 "0": "%b %d %Y %-I:%M%p", 408 "1": "%m/%d/%y", 409 "2": "%y.%m.%d", 410 "3": "%d/%m/%y", 411 "4": "%d.%m.%y", 412 "5": "%d-%m-%y", 413 "6": "%d %b %y", 414 "7": "%b %d, %y", 415 "8": "%H:%M:%S", 416 "9": "%b %d %Y %-I:%M:%S:%f%p", 417 "10": "mm-dd-yy", 418 "11": "yy/mm/dd", 419 "12": "yymmdd", 420 "13": "%d %b %Y %H:%M:ss:%f", 421 "14": "%H:%M:%S:%f", 422 "20": "%Y-%m-%d %H:%M:%S", 423 "21": "%Y-%m-%d %H:%M:%S.%f", 424 "22": "%m/%d/%y %-I:%M:%S %p", 425 "23": "%Y-%m-%d", 426 "24": "%H:%M:%S", 427 "25": "%Y-%m-%d %H:%M:%S.%f", 428 "100": "%b %d %Y %-I:%M%p", 429 "101": "%m/%d/%Y", 430 "102": "%Y.%m.%d", 431 "103": "%d/%m/%Y", 432 "104": "%d.%m.%Y", 433 "105": "%d-%m-%Y", 434 "106": "%d %b %Y", 435 "107": "%b %d, %Y", 436 "108": "%H:%M:%S", 437 "109": "%b %d %Y %-I:%M:%S:%f%p", 438 "110": "%m-%d-%Y", 439 "111": "%Y/%m/%d", 440 "112": "%Y%m%d", 441 "113": "%d %b %Y %H:%M:%S:%f", 442 "114": "%H:%M:%S:%f", 443 "120": "%Y-%m-%d %H:%M:%S", 444 "121": "%Y-%m-%d %H:%M:%S.%f", 445 } 446 447 FORMAT_TIME_MAPPING = { 448 "y": "%B %Y", 449 "d": "%m/%d/%Y", 450 "H": "%-H", 451 "h": "%-I", 452 "s": "%Y-%m-%d %H:%M:%S", 453 "D": "%A,%B,%Y", 454 "f": "%A,%B,%Y %-I:%M %p", 455 "F": "%A,%B,%Y %-I:%M:%S %p", 456 "g": "%m/%d/%Y %-I:%M %p", 457 "G": "%m/%d/%Y %-I:%M:%S %p", 458 "M": "%B %-d", 459 "m": "%B %-d", 460 "O": "%Y-%m-%dT%H:%M:%S", 461 "u": "%Y-%M-%D %H:%M:%S%z", 462 "U": "%A, %B %D, %Y %H:%M:%S%z", 463 "T": "%-I:%M:%S %p", 464 "t": "%-I:%M", 465 "Y": "%a %Y", 466 } 467 468 class Tokenizer(tokens.Tokenizer): 469 IDENTIFIERS = [("[", "]"), '"'] 470 QUOTES = ["'", '"'] 471 HEX_STRINGS = [("0x", ""), ("0X", "")] 472 VAR_SINGLE_TOKENS = {"@", "$", "#"} 473 474 KEYWORDS = { 475 **tokens.Tokenizer.KEYWORDS, 476 "CLUSTERED INDEX": TokenType.INDEX, 477 "DATETIME2": TokenType.DATETIME, 478 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 479 "DECLARE": TokenType.DECLARE, 480 "EXEC": TokenType.COMMAND, 481 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 482 "IMAGE": TokenType.IMAGE, 483 "MONEY": TokenType.MONEY, 484 "NONCLUSTERED INDEX": TokenType.INDEX, 485 "NTEXT": TokenType.TEXT, 486 "OPTION": TokenType.OPTION, 487 "OUTPUT": TokenType.RETURNING, 488 "PRINT": TokenType.COMMAND, 489 "PROC": TokenType.PROCEDURE, 490 "REAL": TokenType.FLOAT, 491 "ROWVERSION": TokenType.ROWVERSION, 492 "SMALLDATETIME": TokenType.DATETIME, 493 "SMALLMONEY": TokenType.SMALLMONEY, 494 "SQL_VARIANT": TokenType.VARIANT, 495 "SYSTEM_USER": TokenType.CURRENT_USER, 496 "TOP": TokenType.TOP, 497 "TIMESTAMP": TokenType.ROWVERSION, 498 "TINYINT": TokenType.UTINYINT, 499 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 500 "UPDATE STATISTICS": TokenType.COMMAND, 501 "XML": TokenType.XML, 502 } 503 KEYWORDS.pop("/*+") 504 505 COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END} 506 507 class Parser(parser.Parser): 508 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 509 LOG_DEFAULTS_TO_LN = True 510 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 511 STRING_ALIASES = True 512 NO_PAREN_IF_COMMANDS = False 513 514 QUERY_MODIFIER_PARSERS = { 515 **parser.Parser.QUERY_MODIFIER_PARSERS, 516 TokenType.OPTION: lambda self: ("options", self._parse_options()), 517 } 518 519 FUNCTIONS = { 520 **parser.Parser.FUNCTIONS, 521 "CHARINDEX": lambda args: exp.StrPosition( 522 this=seq_get(args, 1), 523 substr=seq_get(args, 0), 524 position=seq_get(args, 2), 525 ), 526 "COUNT": lambda args: exp.Count( 527 this=seq_get(args, 0), expressions=args[1:], big_int=False 528 ), 529 "COUNT_BIG": lambda args: exp.Count( 530 this=seq_get(args, 0), expressions=args[1:], big_int=True 531 ), 532 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 533 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 534 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 535 "DATEPART": _build_formatted_time(exp.TimeToStr), 536 "DATETIMEFROMPARTS": _build_datetimefromparts, 537 "EOMONTH": _build_eomonth, 538 "FORMAT": _build_format, 539 "GETDATE": exp.CurrentTimestamp.from_arg_list, 540 "HASHBYTES": _build_hashbytes, 541 "ISNULL": build_coalesce, 542 "JSON_QUERY": _build_json_query, 543 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 544 "LEN": _build_with_arg_as_text(exp.Length), 545 "LEFT": _build_with_arg_as_text(exp.Left), 546 "RIGHT": _build_with_arg_as_text(exp.Right), 547 "REPLICATE": exp.Repeat.from_arg_list, 548 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 549 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 550 "SUSER_NAME": exp.CurrentUser.from_arg_list, 551 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 552 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 553 "TIMEFROMPARTS": _build_timefromparts, 554 } 555 556 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 557 558 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 559 TokenType.TABLE, 560 *parser.Parser.TYPE_TOKENS, 561 } 562 563 STATEMENT_PARSERS = { 564 **parser.Parser.STATEMENT_PARSERS, 565 TokenType.DECLARE: lambda self: self._parse_declare(), 566 } 567 568 RANGE_PARSERS = { 569 **parser.Parser.RANGE_PARSERS, 570 TokenType.DCOLON: lambda self, this: self.expression( 571 exp.ScopeResolution, 572 this=this, 573 expression=self._parse_function() or self._parse_var(any_token=True), 574 ), 575 } 576 577 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 578 COLUMN_OPERATORS = { 579 **parser.Parser.COLUMN_OPERATORS, 580 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 581 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 582 else self.expression(exp.ScopeResolution, this=this, expression=to), 583 } 584 585 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 586 # We want to use _parse_types() if the first token after :: is a known type, 587 # otherwise we could parse something like x::varchar(max) into a function 588 if self._match_set(self.TYPE_TOKENS, advance=False): 589 return self._parse_types() 590 591 return self._parse_function() or self._parse_types() 592 593 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 594 if not self._match(TokenType.OPTION): 595 return None 596 597 def _parse_option() -> t.Optional[exp.Expression]: 598 option = self._parse_var_from_options(OPTIONS) 599 if not option: 600 return None 601 602 self._match(TokenType.EQ) 603 return self.expression( 604 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 605 ) 606 607 return self._parse_wrapped_csv(_parse_option) 608 609 def _parse_projections(self) -> t.List[exp.Expression]: 610 """ 611 T-SQL supports the syntax alias = expression in the SELECT's projection list, 612 so we transform all parsed Selects to convert their EQ projections into Aliases. 613 614 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 615 """ 616 return [ 617 ( 618 exp.alias_(projection.expression, projection.this.this, copy=False) 619 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 620 else projection 621 ) 622 for projection in super()._parse_projections() 623 ] 624 625 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 626 """Applies to SQL Server and Azure SQL Database 627 COMMIT [ { TRAN | TRANSACTION } 628 [ transaction_name | @tran_name_variable ] ] 629 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 630 631 ROLLBACK { TRAN | TRANSACTION } 632 [ transaction_name | @tran_name_variable 633 | savepoint_name | @savepoint_variable ] 634 """ 635 rollback = self._prev.token_type == TokenType.ROLLBACK 636 637 self._match_texts(("TRAN", "TRANSACTION")) 638 this = self._parse_id_var() 639 640 if rollback: 641 return self.expression(exp.Rollback, this=this) 642 643 durability = None 644 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 645 self._match_text_seq("DELAYED_DURABILITY") 646 self._match(TokenType.EQ) 647 648 if self._match_text_seq("OFF"): 649 durability = False 650 else: 651 self._match(TokenType.ON) 652 durability = True 653 654 self._match_r_paren() 655 656 return self.expression(exp.Commit, this=this, durability=durability) 657 658 def _parse_transaction(self) -> exp.Transaction | exp.Command: 659 """Applies to SQL Server and Azure SQL Database 660 BEGIN { TRAN | TRANSACTION } 661 [ { transaction_name | @tran_name_variable } 662 [ WITH MARK [ 'description' ] ] 663 ] 664 """ 665 if self._match_texts(("TRAN", "TRANSACTION")): 666 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 667 if self._match_text_seq("WITH", "MARK"): 668 transaction.set("mark", self._parse_string()) 669 670 return transaction 671 672 return self._parse_as_command(self._prev) 673 674 def _parse_returns(self) -> exp.ReturnsProperty: 675 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 676 returns = super()._parse_returns() 677 returns.set("table", table) 678 return returns 679 680 def _parse_convert( 681 self, strict: bool, safe: t.Optional[bool] = None 682 ) -> t.Optional[exp.Expression]: 683 this = self._parse_types() 684 self._match(TokenType.COMMA) 685 args = [this, *self._parse_csv(self._parse_assignment)] 686 convert = exp.Convert.from_arg_list(args) 687 convert.set("safe", safe) 688 convert.set("strict", strict) 689 return convert 690 691 def _parse_user_defined_function( 692 self, kind: t.Optional[TokenType] = None 693 ) -> t.Optional[exp.Expression]: 694 this = super()._parse_user_defined_function(kind=kind) 695 696 if ( 697 kind == TokenType.FUNCTION 698 or isinstance(this, exp.UserDefinedFunction) 699 or self._match(TokenType.ALIAS, advance=False) 700 ): 701 return this 702 703 expressions = self._parse_csv(self._parse_function_parameter) 704 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 705 706 def _parse_id_var( 707 self, 708 any_token: bool = True, 709 tokens: t.Optional[t.Collection[TokenType]] = None, 710 ) -> t.Optional[exp.Expression]: 711 is_temporary = self._match(TokenType.HASH) 712 is_global = is_temporary and self._match(TokenType.HASH) 713 714 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 715 if this: 716 if is_global: 717 this.set("global", True) 718 elif is_temporary: 719 this.set("temporary", True) 720 721 return this 722 723 def _parse_create(self) -> exp.Create | exp.Command: 724 create = super()._parse_create() 725 726 if isinstance(create, exp.Create): 727 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 728 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 729 if not create.args.get("properties"): 730 create.set("properties", exp.Properties(expressions=[])) 731 732 create.args["properties"].append("expressions", exp.TemporaryProperty()) 733 734 return create 735 736 def _parse_if(self) -> t.Optional[exp.Expression]: 737 index = self._index 738 739 if self._match_text_seq("OBJECT_ID"): 740 self._parse_wrapped_csv(self._parse_string) 741 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 742 return self._parse_drop(exists=True) 743 self._retreat(index) 744 745 return super()._parse_if() 746 747 def _parse_unique(self) -> exp.UniqueColumnConstraint: 748 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 749 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 750 else: 751 this = self._parse_schema(self._parse_id_var(any_token=False)) 752 753 return self.expression(exp.UniqueColumnConstraint, this=this) 754 755 def _parse_partition(self) -> t.Optional[exp.Partition]: 756 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 757 return None 758 759 def parse_range(): 760 low = self._parse_bitwise() 761 high = self._parse_bitwise() if self._match_text_seq("TO") else None 762 763 return ( 764 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 765 ) 766 767 partition = self.expression( 768 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 769 ) 770 771 self._match_r_paren() 772 773 return partition 774 775 def _parse_declare(self) -> exp.Declare | exp.Command: 776 index = self._index 777 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 778 779 if not expressions or self._curr: 780 self._retreat(index) 781 return self._parse_as_command(self._prev) 782 783 return self.expression(exp.Declare, expressions=expressions) 784 785 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 786 var = self._parse_id_var() 787 if not var: 788 return None 789 790 value = None 791 self._match(TokenType.ALIAS) 792 if self._match(TokenType.TABLE): 793 data_type = self._parse_schema() 794 else: 795 data_type = self._parse_types() 796 if self._match(TokenType.EQ): 797 value = self._parse_bitwise() 798 799 return self.expression(exp.DeclareItem, this=var, kind=data_type, default=value) 800 801 class Generator(generator.Generator): 802 LIMIT_IS_TOP = True 803 QUERY_HINTS = False 804 RETURNING_END = False 805 NVL2_SUPPORTED = False 806 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 807 LIMIT_FETCH = "FETCH" 808 COMPUTED_COLUMN_WITH_TYPE = False 809 CTE_RECURSIVE_KEYWORD_REQUIRED = False 810 ENSURE_BOOLS = True 811 NULL_ORDERING_SUPPORTED = None 812 SUPPORTS_SINGLE_ARG_CONCAT = False 813 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 814 SUPPORTS_SELECT_INTO = True 815 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 816 SUPPORTS_TO_NUMBER = False 817 SET_OP_MODIFIERS = False 818 COPY_PARAMS_EQ_REQUIRED = True 819 PARSE_JSON_NAME = None 820 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 821 822 EXPRESSIONS_WITHOUT_NESTED_CTES = { 823 exp.Create, 824 exp.Delete, 825 exp.Insert, 826 exp.Intersect, 827 exp.Except, 828 exp.Merge, 829 exp.Select, 830 exp.Subquery, 831 exp.Union, 832 exp.Update, 833 } 834 835 SUPPORTED_JSON_PATH_PARTS = { 836 exp.JSONPathKey, 837 exp.JSONPathRoot, 838 exp.JSONPathSubscript, 839 } 840 841 TYPE_MAPPING = { 842 **generator.Generator.TYPE_MAPPING, 843 exp.DataType.Type.BOOLEAN: "BIT", 844 exp.DataType.Type.DECIMAL: "NUMERIC", 845 exp.DataType.Type.DATETIME: "DATETIME2", 846 exp.DataType.Type.DOUBLE: "FLOAT", 847 exp.DataType.Type.INT: "INTEGER", 848 exp.DataType.Type.ROWVERSION: "ROWVERSION", 849 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 850 exp.DataType.Type.TIMESTAMP: "DATETIME2", 851 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 852 exp.DataType.Type.UTINYINT: "TINYINT", 853 exp.DataType.Type.VARIANT: "SQL_VARIANT", 854 } 855 856 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 857 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 858 859 TRANSFORMS = { 860 **generator.Generator.TRANSFORMS, 861 exp.AnyValue: any_value_to_max_sql, 862 exp.ArrayToString: rename_func("STRING_AGG"), 863 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 864 exp.DateAdd: date_delta_sql("DATEADD"), 865 exp.DateDiff: date_delta_sql("DATEDIFF"), 866 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 867 exp.CurrentDate: rename_func("GETDATE"), 868 exp.CurrentTimestamp: rename_func("GETDATE"), 869 exp.DateStrToDate: datestrtodate_sql, 870 exp.Extract: rename_func("DATEPART"), 871 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 872 exp.GroupConcat: _string_agg_sql, 873 exp.If: rename_func("IIF"), 874 exp.JSONExtract: _json_extract_sql, 875 exp.JSONExtractScalar: _json_extract_sql, 876 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 877 exp.Max: max_or_greatest, 878 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 879 exp.Min: min_or_least, 880 exp.NumberToStr: _format_sql, 881 exp.Repeat: rename_func("REPLICATE"), 882 exp.Select: transforms.preprocess( 883 [ 884 transforms.eliminate_distinct_on, 885 transforms.eliminate_semi_and_anti_joins, 886 transforms.eliminate_qualify, 887 transforms.unnest_generate_date_array_using_recursive_cte, 888 ] 889 ), 890 exp.Stddev: rename_func("STDEV"), 891 exp.StrPosition: lambda self, e: self.func( 892 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 893 ), 894 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 895 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 896 exp.SHA2: lambda self, e: self.func( 897 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 898 ), 899 exp.TemporaryProperty: lambda self, e: "", 900 exp.TimeStrToTime: _timestrtotime_sql, 901 exp.TimeToStr: _format_sql, 902 exp.Trim: trim_sql, 903 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 904 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 905 } 906 907 TRANSFORMS.pop(exp.ReturnsProperty) 908 909 PROPERTIES_LOCATION = { 910 **generator.Generator.PROPERTIES_LOCATION, 911 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 912 } 913 914 def scope_resolution(self, rhs: str, scope_name: str) -> str: 915 return f"{scope_name}::{rhs}" 916 917 def select_sql(self, expression: exp.Select) -> str: 918 if expression.args.get("offset"): 919 if not expression.args.get("order"): 920 # ORDER BY is required in order to use OFFSET in a query, so we use 921 # a noop order by, since we don't really care about the order. 922 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 923 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 924 925 limit = expression.args.get("limit") 926 if isinstance(limit, exp.Limit): 927 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 928 # we replace here because otherwise TOP would be generated in select_sql 929 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 930 931 return super().select_sql(expression) 932 933 def convert_sql(self, expression: exp.Convert) -> str: 934 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 935 return self.func( 936 name, expression.this, expression.expression, expression.args.get("style") 937 ) 938 939 def queryoption_sql(self, expression: exp.QueryOption) -> str: 940 option = self.sql(expression, "this") 941 value = self.sql(expression, "expression") 942 if value: 943 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 944 return f"{option} {optional_equal_sign}{value}" 945 return option 946 947 def lateral_op(self, expression: exp.Lateral) -> str: 948 cross_apply = expression.args.get("cross_apply") 949 if cross_apply is True: 950 return "CROSS APPLY" 951 if cross_apply is False: 952 return "OUTER APPLY" 953 954 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 955 self.unsupported("LATERAL clause is not supported.") 956 return "LATERAL" 957 958 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 959 nano = expression.args.get("nano") 960 if nano is not None: 961 nano.pop() 962 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 963 964 if expression.args.get("fractions") is None: 965 expression.set("fractions", exp.Literal.number(0)) 966 if expression.args.get("precision") is None: 967 expression.set("precision", exp.Literal.number(0)) 968 969 return rename_func("TIMEFROMPARTS")(self, expression) 970 971 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 972 zone = expression.args.get("zone") 973 if zone is not None: 974 zone.pop() 975 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 976 977 nano = expression.args.get("nano") 978 if nano is not None: 979 nano.pop() 980 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 981 982 if expression.args.get("milli") is None: 983 expression.set("milli", exp.Literal.number(0)) 984 985 return rename_func("DATETIMEFROMPARTS")(self, expression) 986 987 def setitem_sql(self, expression: exp.SetItem) -> str: 988 this = expression.this 989 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 990 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 991 return f"{self.sql(this.left)} {self.sql(this.right)}" 992 993 return super().setitem_sql(expression) 994 995 def boolean_sql(self, expression: exp.Boolean) -> str: 996 if type(expression.parent) in BIT_TYPES or isinstance( 997 expression.find_ancestor(exp.Values, exp.Select), exp.Values 998 ): 999 return "1" if expression.this else "0" 1000 1001 return "(1 = 1)" if expression.this else "(1 = 0)" 1002 1003 def is_sql(self, expression: exp.Is) -> str: 1004 if isinstance(expression.expression, exp.Boolean): 1005 return self.binary(expression, "=") 1006 return self.binary(expression, "IS") 1007 1008 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 1009 sql = self.sql(expression, "this") 1010 properties = expression.args.get("properties") 1011 1012 if sql[:1] != "#" and any( 1013 isinstance(prop, exp.TemporaryProperty) 1014 for prop in (properties.expressions if properties else []) 1015 ): 1016 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1017 1018 return sql 1019 1020 def create_sql(self, expression: exp.Create) -> str: 1021 kind = expression.kind 1022 exists = expression.args.pop("exists", None) 1023 1024 like_property = expression.find(exp.LikeProperty) 1025 if like_property: 1026 ctas_expression = like_property.this 1027 else: 1028 ctas_expression = expression.expression 1029 1030 if kind == "VIEW": 1031 expression.this.set("catalog", None) 1032 with_ = expression.args.get("with") 1033 if ctas_expression and with_: 1034 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1035 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1036 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1037 ctas_expression.set("with", with_.pop()) 1038 1039 sql = super().create_sql(expression) 1040 1041 table = expression.find(exp.Table) 1042 1043 # Convert CTAS statement to SELECT .. INTO .. 1044 if kind == "TABLE" and ctas_expression: 1045 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1046 ctas_expression = ctas_expression.subquery() 1047 1048 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1049 select_into.set("into", exp.Into(this=table)) 1050 1051 if like_property: 1052 select_into.limit(0, copy=False) 1053 1054 sql = self.sql(select_into) 1055 1056 if exists: 1057 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1058 sql = self.sql(exp.Literal.string(sql)) 1059 if kind == "SCHEMA": 1060 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1061 elif kind == "TABLE": 1062 assert table 1063 where = exp.and_( 1064 exp.column("table_name").eq(table.name), 1065 exp.column("table_schema").eq(table.db) if table.db else None, 1066 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1067 ) 1068 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1069 elif kind == "INDEX": 1070 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1071 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1072 elif expression.args.get("replace"): 1073 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1074 1075 return self.prepend_ctes(expression, sql) 1076 1077 def count_sql(self, expression: exp.Count) -> str: 1078 func_name = "COUNT_BIG" if expression.args.get("big_int") else "COUNT" 1079 return rename_func(func_name)(self, expression) 1080 1081 def offset_sql(self, expression: exp.Offset) -> str: 1082 return f"{super().offset_sql(expression)} ROWS" 1083 1084 def version_sql(self, expression: exp.Version) -> str: 1085 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1086 this = f"FOR {name}" 1087 expr = expression.expression 1088 kind = expression.text("kind") 1089 if kind in ("FROM", "BETWEEN"): 1090 args = expr.expressions 1091 sep = "TO" if kind == "FROM" else "AND" 1092 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1093 else: 1094 expr_sql = self.sql(expr) 1095 1096 expr_sql = f" {expr_sql}" if expr_sql else "" 1097 return f"{this} {kind}{expr_sql}" 1098 1099 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1100 table = expression.args.get("table") 1101 table = f"{table} " if table else "" 1102 return f"RETURNS {table}{self.sql(expression, 'this')}" 1103 1104 def returning_sql(self, expression: exp.Returning) -> str: 1105 into = self.sql(expression, "into") 1106 into = self.seg(f"INTO {into}") if into else "" 1107 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1108 1109 def transaction_sql(self, expression: exp.Transaction) -> str: 1110 this = self.sql(expression, "this") 1111 this = f" {this}" if this else "" 1112 mark = self.sql(expression, "mark") 1113 mark = f" WITH MARK {mark}" if mark else "" 1114 return f"BEGIN TRANSACTION{this}{mark}" 1115 1116 def commit_sql(self, expression: exp.Commit) -> str: 1117 this = self.sql(expression, "this") 1118 this = f" {this}" if this else "" 1119 durability = expression.args.get("durability") 1120 durability = ( 1121 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1122 if durability is not None 1123 else "" 1124 ) 1125 return f"COMMIT TRANSACTION{this}{durability}" 1126 1127 def rollback_sql(self, expression: exp.Rollback) -> str: 1128 this = self.sql(expression, "this") 1129 this = f" {this}" if this else "" 1130 return f"ROLLBACK TRANSACTION{this}" 1131 1132 def identifier_sql(self, expression: exp.Identifier) -> str: 1133 identifier = super().identifier_sql(expression) 1134 1135 if expression.args.get("global"): 1136 identifier = f"##{identifier}" 1137 elif expression.args.get("temporary"): 1138 identifier = f"#{identifier}" 1139 1140 return identifier 1141 1142 def constraint_sql(self, expression: exp.Constraint) -> str: 1143 this = self.sql(expression, "this") 1144 expressions = self.expressions(expression, flat=True, sep=" ") 1145 return f"CONSTRAINT {this} {expressions}" 1146 1147 def length_sql(self, expression: exp.Length) -> str: 1148 return self._uncast_text(expression, "LEN") 1149 1150 def right_sql(self, expression: exp.Right) -> str: 1151 return self._uncast_text(expression, "RIGHT") 1152 1153 def left_sql(self, expression: exp.Left) -> str: 1154 return self._uncast_text(expression, "LEFT") 1155 1156 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1157 this = expression.this 1158 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1159 this_sql = self.sql(this, "this") 1160 else: 1161 this_sql = self.sql(this) 1162 expression_sql = self.sql(expression, "expression") 1163 return self.func(name, this_sql, expression_sql if expression_sql else None) 1164 1165 def partition_sql(self, expression: exp.Partition) -> str: 1166 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1167 1168 def alter_sql(self, expression: exp.Alter) -> str: 1169 action = seq_get(expression.args.get("actions") or [], 0) 1170 if isinstance(action, exp.RenameTable): 1171 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1172 return super().alter_sql(expression) 1173 1174 def drop_sql(self, expression: exp.Drop) -> str: 1175 if expression.args["kind"] == "VIEW": 1176 expression.this.set("catalog", None) 1177 return super().drop_sql(expression) 1178 1179 def declare_sql(self, expression: exp.Declare) -> str: 1180 return f"DECLARE {self.expressions(expression, flat=True)}" 1181 1182 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1183 variable = self.sql(expression, "this") 1184 default = self.sql(expression, "default") 1185 default = f" = {default}" if default else "" 1186 1187 kind = self.sql(expression, "kind") 1188 if isinstance(expression.args.get("kind"), exp.Schema): 1189 kind = f"TABLE {kind}" 1190 1191 return f"{variable} AS {kind}{default}" 1192 1193 def options_modifier(self, expression: exp.Expression) -> str: 1194 options = self.expressions(expression, key="options") 1195 return f" OPTION{self.wrap(options)}" if options else "" 1196 1197 def dpipe_sql(self, expression: exp.DPipe) -> str: 1198 return self.sql( 1199 reduce(lambda x, y: exp.Add(this=x, expression=y), expression.flatten()) 1200 )
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.
Specifies the strategy according to which identifiers should be normalized.
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
- STRICT_JSON_PATH_SYNTAX
- ON_CONDITION_EMPTY_BEFORE_ERROR
- ARRAY_AGG_INCLUDES_NULLS
- REGEXP_EXTRACT_DEFAULT_GROUP
- SET_OP_DISTINCT_BY_DEFAULT
- 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
468 class Tokenizer(tokens.Tokenizer): 469 IDENTIFIERS = [("[", "]"), '"'] 470 QUOTES = ["'", '"'] 471 HEX_STRINGS = [("0x", ""), ("0X", "")] 472 VAR_SINGLE_TOKENS = {"@", "$", "#"} 473 474 KEYWORDS = { 475 **tokens.Tokenizer.KEYWORDS, 476 "CLUSTERED INDEX": TokenType.INDEX, 477 "DATETIME2": TokenType.DATETIME, 478 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 479 "DECLARE": TokenType.DECLARE, 480 "EXEC": TokenType.COMMAND, 481 "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT, 482 "IMAGE": TokenType.IMAGE, 483 "MONEY": TokenType.MONEY, 484 "NONCLUSTERED INDEX": TokenType.INDEX, 485 "NTEXT": TokenType.TEXT, 486 "OPTION": TokenType.OPTION, 487 "OUTPUT": TokenType.RETURNING, 488 "PRINT": TokenType.COMMAND, 489 "PROC": TokenType.PROCEDURE, 490 "REAL": TokenType.FLOAT, 491 "ROWVERSION": TokenType.ROWVERSION, 492 "SMALLDATETIME": TokenType.DATETIME, 493 "SMALLMONEY": TokenType.SMALLMONEY, 494 "SQL_VARIANT": TokenType.VARIANT, 495 "SYSTEM_USER": TokenType.CURRENT_USER, 496 "TOP": TokenType.TOP, 497 "TIMESTAMP": TokenType.ROWVERSION, 498 "TINYINT": TokenType.UTINYINT, 499 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 500 "UPDATE STATISTICS": TokenType.COMMAND, 501 "XML": TokenType.XML, 502 } 503 KEYWORDS.pop("/*+") 504 505 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
507 class Parser(parser.Parser): 508 SET_REQUIRES_ASSIGNMENT_DELIMITER = False 509 LOG_DEFAULTS_TO_LN = True 510 ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False 511 STRING_ALIASES = True 512 NO_PAREN_IF_COMMANDS = False 513 514 QUERY_MODIFIER_PARSERS = { 515 **parser.Parser.QUERY_MODIFIER_PARSERS, 516 TokenType.OPTION: lambda self: ("options", self._parse_options()), 517 } 518 519 FUNCTIONS = { 520 **parser.Parser.FUNCTIONS, 521 "CHARINDEX": lambda args: exp.StrPosition( 522 this=seq_get(args, 1), 523 substr=seq_get(args, 0), 524 position=seq_get(args, 2), 525 ), 526 "COUNT": lambda args: exp.Count( 527 this=seq_get(args, 0), expressions=args[1:], big_int=False 528 ), 529 "COUNT_BIG": lambda args: exp.Count( 530 this=seq_get(args, 0), expressions=args[1:], big_int=True 531 ), 532 "DATEADD": build_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 533 "DATEDIFF": _build_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 534 "DATENAME": _build_formatted_time(exp.TimeToStr, full_format_mapping=True), 535 "DATEPART": _build_formatted_time(exp.TimeToStr), 536 "DATETIMEFROMPARTS": _build_datetimefromparts, 537 "EOMONTH": _build_eomonth, 538 "FORMAT": _build_format, 539 "GETDATE": exp.CurrentTimestamp.from_arg_list, 540 "HASHBYTES": _build_hashbytes, 541 "ISNULL": build_coalesce, 542 "JSON_QUERY": _build_json_query, 543 "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar), 544 "LEN": _build_with_arg_as_text(exp.Length), 545 "LEFT": _build_with_arg_as_text(exp.Left), 546 "RIGHT": _build_with_arg_as_text(exp.Right), 547 "REPLICATE": exp.Repeat.from_arg_list, 548 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 549 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 550 "SUSER_NAME": exp.CurrentUser.from_arg_list, 551 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 552 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 553 "TIMEFROMPARTS": _build_timefromparts, 554 } 555 556 JOIN_HINTS = {"LOOP", "HASH", "MERGE", "REMOTE"} 557 558 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 559 TokenType.TABLE, 560 *parser.Parser.TYPE_TOKENS, 561 } 562 563 STATEMENT_PARSERS = { 564 **parser.Parser.STATEMENT_PARSERS, 565 TokenType.DECLARE: lambda self: self._parse_declare(), 566 } 567 568 RANGE_PARSERS = { 569 **parser.Parser.RANGE_PARSERS, 570 TokenType.DCOLON: lambda self, this: self.expression( 571 exp.ScopeResolution, 572 this=this, 573 expression=self._parse_function() or self._parse_var(any_token=True), 574 ), 575 } 576 577 # The DCOLON (::) operator serves as a scope resolution (exp.ScopeResolution) operator in T-SQL 578 COLUMN_OPERATORS = { 579 **parser.Parser.COLUMN_OPERATORS, 580 TokenType.DCOLON: lambda self, this, to: self.expression(exp.Cast, this=this, to=to) 581 if isinstance(to, exp.DataType) and to.this != exp.DataType.Type.USERDEFINED 582 else self.expression(exp.ScopeResolution, this=this, expression=to), 583 } 584 585 def _parse_dcolon(self) -> t.Optional[exp.Expression]: 586 # We want to use _parse_types() if the first token after :: is a known type, 587 # otherwise we could parse something like x::varchar(max) into a function 588 if self._match_set(self.TYPE_TOKENS, advance=False): 589 return self._parse_types() 590 591 return self._parse_function() or self._parse_types() 592 593 def _parse_options(self) -> t.Optional[t.List[exp.Expression]]: 594 if not self._match(TokenType.OPTION): 595 return None 596 597 def _parse_option() -> t.Optional[exp.Expression]: 598 option = self._parse_var_from_options(OPTIONS) 599 if not option: 600 return None 601 602 self._match(TokenType.EQ) 603 return self.expression( 604 exp.QueryOption, this=option, expression=self._parse_primary_or_var() 605 ) 606 607 return self._parse_wrapped_csv(_parse_option) 608 609 def _parse_projections(self) -> t.List[exp.Expression]: 610 """ 611 T-SQL supports the syntax alias = expression in the SELECT's projection list, 612 so we transform all parsed Selects to convert their EQ projections into Aliases. 613 614 See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16#syntax 615 """ 616 return [ 617 ( 618 exp.alias_(projection.expression, projection.this.this, copy=False) 619 if isinstance(projection, exp.EQ) and isinstance(projection.this, exp.Column) 620 else projection 621 ) 622 for projection in super()._parse_projections() 623 ] 624 625 def _parse_commit_or_rollback(self) -> exp.Commit | exp.Rollback: 626 """Applies to SQL Server and Azure SQL Database 627 COMMIT [ { TRAN | TRANSACTION } 628 [ transaction_name | @tran_name_variable ] ] 629 [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 630 631 ROLLBACK { TRAN | TRANSACTION } 632 [ transaction_name | @tran_name_variable 633 | savepoint_name | @savepoint_variable ] 634 """ 635 rollback = self._prev.token_type == TokenType.ROLLBACK 636 637 self._match_texts(("TRAN", "TRANSACTION")) 638 this = self._parse_id_var() 639 640 if rollback: 641 return self.expression(exp.Rollback, this=this) 642 643 durability = None 644 if self._match_pair(TokenType.WITH, TokenType.L_PAREN): 645 self._match_text_seq("DELAYED_DURABILITY") 646 self._match(TokenType.EQ) 647 648 if self._match_text_seq("OFF"): 649 durability = False 650 else: 651 self._match(TokenType.ON) 652 durability = True 653 654 self._match_r_paren() 655 656 return self.expression(exp.Commit, this=this, durability=durability) 657 658 def _parse_transaction(self) -> exp.Transaction | exp.Command: 659 """Applies to SQL Server and Azure SQL Database 660 BEGIN { TRAN | TRANSACTION } 661 [ { transaction_name | @tran_name_variable } 662 [ WITH MARK [ 'description' ] ] 663 ] 664 """ 665 if self._match_texts(("TRAN", "TRANSACTION")): 666 transaction = self.expression(exp.Transaction, this=self._parse_id_var()) 667 if self._match_text_seq("WITH", "MARK"): 668 transaction.set("mark", self._parse_string()) 669 670 return transaction 671 672 return self._parse_as_command(self._prev) 673 674 def _parse_returns(self) -> exp.ReturnsProperty: 675 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 676 returns = super()._parse_returns() 677 returns.set("table", table) 678 return returns 679 680 def _parse_convert( 681 self, strict: bool, safe: t.Optional[bool] = None 682 ) -> t.Optional[exp.Expression]: 683 this = self._parse_types() 684 self._match(TokenType.COMMA) 685 args = [this, *self._parse_csv(self._parse_assignment)] 686 convert = exp.Convert.from_arg_list(args) 687 convert.set("safe", safe) 688 convert.set("strict", strict) 689 return convert 690 691 def _parse_user_defined_function( 692 self, kind: t.Optional[TokenType] = None 693 ) -> t.Optional[exp.Expression]: 694 this = super()._parse_user_defined_function(kind=kind) 695 696 if ( 697 kind == TokenType.FUNCTION 698 or isinstance(this, exp.UserDefinedFunction) 699 or self._match(TokenType.ALIAS, advance=False) 700 ): 701 return this 702 703 expressions = self._parse_csv(self._parse_function_parameter) 704 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 705 706 def _parse_id_var( 707 self, 708 any_token: bool = True, 709 tokens: t.Optional[t.Collection[TokenType]] = None, 710 ) -> t.Optional[exp.Expression]: 711 is_temporary = self._match(TokenType.HASH) 712 is_global = is_temporary and self._match(TokenType.HASH) 713 714 this = super()._parse_id_var(any_token=any_token, tokens=tokens) 715 if this: 716 if is_global: 717 this.set("global", True) 718 elif is_temporary: 719 this.set("temporary", True) 720 721 return this 722 723 def _parse_create(self) -> exp.Create | exp.Command: 724 create = super()._parse_create() 725 726 if isinstance(create, exp.Create): 727 table = create.this.this if isinstance(create.this, exp.Schema) else create.this 728 if isinstance(table, exp.Table) and table.this.args.get("temporary"): 729 if not create.args.get("properties"): 730 create.set("properties", exp.Properties(expressions=[])) 731 732 create.args["properties"].append("expressions", exp.TemporaryProperty()) 733 734 return create 735 736 def _parse_if(self) -> t.Optional[exp.Expression]: 737 index = self._index 738 739 if self._match_text_seq("OBJECT_ID"): 740 self._parse_wrapped_csv(self._parse_string) 741 if self._match_text_seq("IS", "NOT", "NULL") and self._match(TokenType.DROP): 742 return self._parse_drop(exists=True) 743 self._retreat(index) 744 745 return super()._parse_if() 746 747 def _parse_unique(self) -> exp.UniqueColumnConstraint: 748 if self._match_texts(("CLUSTERED", "NONCLUSTERED")): 749 this = self.CONSTRAINT_PARSERS[self._prev.text.upper()](self) 750 else: 751 this = self._parse_schema(self._parse_id_var(any_token=False)) 752 753 return self.expression(exp.UniqueColumnConstraint, this=this) 754 755 def _parse_partition(self) -> t.Optional[exp.Partition]: 756 if not self._match_text_seq("WITH", "(", "PARTITIONS"): 757 return None 758 759 def parse_range(): 760 low = self._parse_bitwise() 761 high = self._parse_bitwise() if self._match_text_seq("TO") else None 762 763 return ( 764 self.expression(exp.PartitionRange, this=low, expression=high) if high else low 765 ) 766 767 partition = self.expression( 768 exp.Partition, expressions=self._parse_wrapped_csv(parse_range) 769 ) 770 771 self._match_r_paren() 772 773 return partition 774 775 def _parse_declare(self) -> exp.Declare | exp.Command: 776 index = self._index 777 expressions = self._try_parse(partial(self._parse_csv, self._parse_declareitem)) 778 779 if not expressions or self._curr: 780 self._retreat(index) 781 return self._parse_as_command(self._prev) 782 783 return self.expression(exp.Declare, expressions=expressions) 784 785 def _parse_declareitem(self) -> t.Optional[exp.DeclareItem]: 786 var = self._parse_id_var() 787 if not var: 788 return None 789 790 value = None 791 self._match(TokenType.ALIAS) 792 if self._match(TokenType.TABLE): 793 data_type = self._parse_schema() 794 else: 795 data_type = self._parse_types() 796 if self._match(TokenType.EQ): 797 value = self._parse_bitwise() 798 799 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
- IS_JSON_PREDICATE_KIND
- ODBC_DATETIME_LITERALS
- ON_CONDITION_TOKENS
- PRIVILEGE_FOLLOW_TOKENS
- 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
801 class Generator(generator.Generator): 802 LIMIT_IS_TOP = True 803 QUERY_HINTS = False 804 RETURNING_END = False 805 NVL2_SUPPORTED = False 806 ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False 807 LIMIT_FETCH = "FETCH" 808 COMPUTED_COLUMN_WITH_TYPE = False 809 CTE_RECURSIVE_KEYWORD_REQUIRED = False 810 ENSURE_BOOLS = True 811 NULL_ORDERING_SUPPORTED = None 812 SUPPORTS_SINGLE_ARG_CONCAT = False 813 TABLESAMPLE_SEED_KEYWORD = "REPEATABLE" 814 SUPPORTS_SELECT_INTO = True 815 JSON_PATH_BRACKETED_KEY_SUPPORTED = False 816 SUPPORTS_TO_NUMBER = False 817 SET_OP_MODIFIERS = False 818 COPY_PARAMS_EQ_REQUIRED = True 819 PARSE_JSON_NAME = None 820 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 821 822 EXPRESSIONS_WITHOUT_NESTED_CTES = { 823 exp.Create, 824 exp.Delete, 825 exp.Insert, 826 exp.Intersect, 827 exp.Except, 828 exp.Merge, 829 exp.Select, 830 exp.Subquery, 831 exp.Union, 832 exp.Update, 833 } 834 835 SUPPORTED_JSON_PATH_PARTS = { 836 exp.JSONPathKey, 837 exp.JSONPathRoot, 838 exp.JSONPathSubscript, 839 } 840 841 TYPE_MAPPING = { 842 **generator.Generator.TYPE_MAPPING, 843 exp.DataType.Type.BOOLEAN: "BIT", 844 exp.DataType.Type.DECIMAL: "NUMERIC", 845 exp.DataType.Type.DATETIME: "DATETIME2", 846 exp.DataType.Type.DOUBLE: "FLOAT", 847 exp.DataType.Type.INT: "INTEGER", 848 exp.DataType.Type.ROWVERSION: "ROWVERSION", 849 exp.DataType.Type.TEXT: "VARCHAR(MAX)", 850 exp.DataType.Type.TIMESTAMP: "DATETIME2", 851 exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET", 852 exp.DataType.Type.UTINYINT: "TINYINT", 853 exp.DataType.Type.VARIANT: "SQL_VARIANT", 854 } 855 856 TYPE_MAPPING.pop(exp.DataType.Type.NCHAR) 857 TYPE_MAPPING.pop(exp.DataType.Type.NVARCHAR) 858 859 TRANSFORMS = { 860 **generator.Generator.TRANSFORMS, 861 exp.AnyValue: any_value_to_max_sql, 862 exp.ArrayToString: rename_func("STRING_AGG"), 863 exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY", 864 exp.DateAdd: date_delta_sql("DATEADD"), 865 exp.DateDiff: date_delta_sql("DATEDIFF"), 866 exp.CTE: transforms.preprocess([qualify_derived_table_outputs]), 867 exp.CurrentDate: rename_func("GETDATE"), 868 exp.CurrentTimestamp: rename_func("GETDATE"), 869 exp.DateStrToDate: datestrtodate_sql, 870 exp.Extract: rename_func("DATEPART"), 871 exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql, 872 exp.GroupConcat: _string_agg_sql, 873 exp.If: rename_func("IIF"), 874 exp.JSONExtract: _json_extract_sql, 875 exp.JSONExtractScalar: _json_extract_sql, 876 exp.LastDay: lambda self, e: self.func("EOMONTH", e.this), 877 exp.Max: max_or_greatest, 878 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 879 exp.Min: min_or_least, 880 exp.NumberToStr: _format_sql, 881 exp.Repeat: rename_func("REPLICATE"), 882 exp.Select: transforms.preprocess( 883 [ 884 transforms.eliminate_distinct_on, 885 transforms.eliminate_semi_and_anti_joins, 886 transforms.eliminate_qualify, 887 transforms.unnest_generate_date_array_using_recursive_cte, 888 ] 889 ), 890 exp.Stddev: rename_func("STDEV"), 891 exp.StrPosition: lambda self, e: self.func( 892 "CHARINDEX", e.args.get("substr"), e.this, e.args.get("position") 893 ), 894 exp.Subquery: transforms.preprocess([qualify_derived_table_outputs]), 895 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 896 exp.SHA2: lambda self, e: self.func( 897 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 898 ), 899 exp.TemporaryProperty: lambda self, e: "", 900 exp.TimeStrToTime: _timestrtotime_sql, 901 exp.TimeToStr: _format_sql, 902 exp.Trim: trim_sql, 903 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 904 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 905 } 906 907 TRANSFORMS.pop(exp.ReturnsProperty) 908 909 PROPERTIES_LOCATION = { 910 **generator.Generator.PROPERTIES_LOCATION, 911 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 912 } 913 914 def scope_resolution(self, rhs: str, scope_name: str) -> str: 915 return f"{scope_name}::{rhs}" 916 917 def select_sql(self, expression: exp.Select) -> str: 918 if expression.args.get("offset"): 919 if not expression.args.get("order"): 920 # ORDER BY is required in order to use OFFSET in a query, so we use 921 # a noop order by, since we don't really care about the order. 922 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 923 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 924 925 limit = expression.args.get("limit") 926 if isinstance(limit, exp.Limit): 927 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 928 # we replace here because otherwise TOP would be generated in select_sql 929 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 930 931 return super().select_sql(expression) 932 933 def convert_sql(self, expression: exp.Convert) -> str: 934 name = "TRY_CONVERT" if expression.args.get("safe") else "CONVERT" 935 return self.func( 936 name, expression.this, expression.expression, expression.args.get("style") 937 ) 938 939 def queryoption_sql(self, expression: exp.QueryOption) -> str: 940 option = self.sql(expression, "this") 941 value = self.sql(expression, "expression") 942 if value: 943 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 944 return f"{option} {optional_equal_sign}{value}" 945 return option 946 947 def lateral_op(self, expression: exp.Lateral) -> str: 948 cross_apply = expression.args.get("cross_apply") 949 if cross_apply is True: 950 return "CROSS APPLY" 951 if cross_apply is False: 952 return "OUTER APPLY" 953 954 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 955 self.unsupported("LATERAL clause is not supported.") 956 return "LATERAL" 957 958 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 959 nano = expression.args.get("nano") 960 if nano is not None: 961 nano.pop() 962 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 963 964 if expression.args.get("fractions") is None: 965 expression.set("fractions", exp.Literal.number(0)) 966 if expression.args.get("precision") is None: 967 expression.set("precision", exp.Literal.number(0)) 968 969 return rename_func("TIMEFROMPARTS")(self, expression) 970 971 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 972 zone = expression.args.get("zone") 973 if zone is not None: 974 zone.pop() 975 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 976 977 nano = expression.args.get("nano") 978 if nano is not None: 979 nano.pop() 980 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 981 982 if expression.args.get("milli") is None: 983 expression.set("milli", exp.Literal.number(0)) 984 985 return rename_func("DATETIMEFROMPARTS")(self, expression) 986 987 def setitem_sql(self, expression: exp.SetItem) -> str: 988 this = expression.this 989 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 990 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 991 return f"{self.sql(this.left)} {self.sql(this.right)}" 992 993 return super().setitem_sql(expression) 994 995 def boolean_sql(self, expression: exp.Boolean) -> str: 996 if type(expression.parent) in BIT_TYPES or isinstance( 997 expression.find_ancestor(exp.Values, exp.Select), exp.Values 998 ): 999 return "1" if expression.this else "0" 1000 1001 return "(1 = 1)" if expression.this else "(1 = 0)" 1002 1003 def is_sql(self, expression: exp.Is) -> str: 1004 if isinstance(expression.expression, exp.Boolean): 1005 return self.binary(expression, "=") 1006 return self.binary(expression, "IS") 1007 1008 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 1009 sql = self.sql(expression, "this") 1010 properties = expression.args.get("properties") 1011 1012 if sql[:1] != "#" and any( 1013 isinstance(prop, exp.TemporaryProperty) 1014 for prop in (properties.expressions if properties else []) 1015 ): 1016 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1017 1018 return sql 1019 1020 def create_sql(self, expression: exp.Create) -> str: 1021 kind = expression.kind 1022 exists = expression.args.pop("exists", None) 1023 1024 like_property = expression.find(exp.LikeProperty) 1025 if like_property: 1026 ctas_expression = like_property.this 1027 else: 1028 ctas_expression = expression.expression 1029 1030 if kind == "VIEW": 1031 expression.this.set("catalog", None) 1032 with_ = expression.args.get("with") 1033 if ctas_expression and with_: 1034 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1035 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1036 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1037 ctas_expression.set("with", with_.pop()) 1038 1039 sql = super().create_sql(expression) 1040 1041 table = expression.find(exp.Table) 1042 1043 # Convert CTAS statement to SELECT .. INTO .. 1044 if kind == "TABLE" and ctas_expression: 1045 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1046 ctas_expression = ctas_expression.subquery() 1047 1048 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1049 select_into.set("into", exp.Into(this=table)) 1050 1051 if like_property: 1052 select_into.limit(0, copy=False) 1053 1054 sql = self.sql(select_into) 1055 1056 if exists: 1057 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1058 sql = self.sql(exp.Literal.string(sql)) 1059 if kind == "SCHEMA": 1060 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1061 elif kind == "TABLE": 1062 assert table 1063 where = exp.and_( 1064 exp.column("table_name").eq(table.name), 1065 exp.column("table_schema").eq(table.db) if table.db else None, 1066 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1067 ) 1068 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1069 elif kind == "INDEX": 1070 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1071 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1072 elif expression.args.get("replace"): 1073 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1074 1075 return self.prepend_ctes(expression, sql) 1076 1077 def count_sql(self, expression: exp.Count) -> str: 1078 func_name = "COUNT_BIG" if expression.args.get("big_int") else "COUNT" 1079 return rename_func(func_name)(self, expression) 1080 1081 def offset_sql(self, expression: exp.Offset) -> str: 1082 return f"{super().offset_sql(expression)} ROWS" 1083 1084 def version_sql(self, expression: exp.Version) -> str: 1085 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1086 this = f"FOR {name}" 1087 expr = expression.expression 1088 kind = expression.text("kind") 1089 if kind in ("FROM", "BETWEEN"): 1090 args = expr.expressions 1091 sep = "TO" if kind == "FROM" else "AND" 1092 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1093 else: 1094 expr_sql = self.sql(expr) 1095 1096 expr_sql = f" {expr_sql}" if expr_sql else "" 1097 return f"{this} {kind}{expr_sql}" 1098 1099 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 1100 table = expression.args.get("table") 1101 table = f"{table} " if table else "" 1102 return f"RETURNS {table}{self.sql(expression, 'this')}" 1103 1104 def returning_sql(self, expression: exp.Returning) -> str: 1105 into = self.sql(expression, "into") 1106 into = self.seg(f"INTO {into}") if into else "" 1107 return f"{self.seg('OUTPUT')} {self.expressions(expression, flat=True)}{into}" 1108 1109 def transaction_sql(self, expression: exp.Transaction) -> str: 1110 this = self.sql(expression, "this") 1111 this = f" {this}" if this else "" 1112 mark = self.sql(expression, "mark") 1113 mark = f" WITH MARK {mark}" if mark else "" 1114 return f"BEGIN TRANSACTION{this}{mark}" 1115 1116 def commit_sql(self, expression: exp.Commit) -> str: 1117 this = self.sql(expression, "this") 1118 this = f" {this}" if this else "" 1119 durability = expression.args.get("durability") 1120 durability = ( 1121 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1122 if durability is not None 1123 else "" 1124 ) 1125 return f"COMMIT TRANSACTION{this}{durability}" 1126 1127 def rollback_sql(self, expression: exp.Rollback) -> str: 1128 this = self.sql(expression, "this") 1129 this = f" {this}" if this else "" 1130 return f"ROLLBACK TRANSACTION{this}" 1131 1132 def identifier_sql(self, expression: exp.Identifier) -> str: 1133 identifier = super().identifier_sql(expression) 1134 1135 if expression.args.get("global"): 1136 identifier = f"##{identifier}" 1137 elif expression.args.get("temporary"): 1138 identifier = f"#{identifier}" 1139 1140 return identifier 1141 1142 def constraint_sql(self, expression: exp.Constraint) -> str: 1143 this = self.sql(expression, "this") 1144 expressions = self.expressions(expression, flat=True, sep=" ") 1145 return f"CONSTRAINT {this} {expressions}" 1146 1147 def length_sql(self, expression: exp.Length) -> str: 1148 return self._uncast_text(expression, "LEN") 1149 1150 def right_sql(self, expression: exp.Right) -> str: 1151 return self._uncast_text(expression, "RIGHT") 1152 1153 def left_sql(self, expression: exp.Left) -> str: 1154 return self._uncast_text(expression, "LEFT") 1155 1156 def _uncast_text(self, expression: exp.Expression, name: str) -> str: 1157 this = expression.this 1158 if isinstance(this, exp.Cast) and this.is_type(exp.DataType.Type.TEXT): 1159 this_sql = self.sql(this, "this") 1160 else: 1161 this_sql = self.sql(this) 1162 expression_sql = self.sql(expression, "expression") 1163 return self.func(name, this_sql, expression_sql if expression_sql else None) 1164 1165 def partition_sql(self, expression: exp.Partition) -> str: 1166 return f"WITH (PARTITIONS({self.expressions(expression, flat=True)}))" 1167 1168 def alter_sql(self, expression: exp.Alter) -> str: 1169 action = seq_get(expression.args.get("actions") or [], 0) 1170 if isinstance(action, exp.RenameTable): 1171 return f"EXEC sp_rename '{self.sql(expression.this)}', '{action.this.name}'" 1172 return super().alter_sql(expression) 1173 1174 def drop_sql(self, expression: exp.Drop) -> str: 1175 if expression.args["kind"] == "VIEW": 1176 expression.this.set("catalog", None) 1177 return super().drop_sql(expression) 1178 1179 def declare_sql(self, expression: exp.Declare) -> str: 1180 return f"DECLARE {self.expressions(expression, flat=True)}" 1181 1182 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1183 variable = self.sql(expression, "this") 1184 default = self.sql(expression, "default") 1185 default = f" = {default}" if default else "" 1186 1187 kind = self.sql(expression, "kind") 1188 if isinstance(expression.args.get("kind"), exp.Schema): 1189 kind = f"TABLE {kind}" 1190 1191 return f"{variable} AS {kind}{default}" 1192 1193 def options_modifier(self, expression: exp.Expression) -> str: 1194 options = self.expressions(expression, key="options") 1195 return f" OPTION{self.wrap(options)}" if options else "" 1196 1197 def dpipe_sql(self, expression: exp.DPipe) -> str: 1198 return self.sql( 1199 reduce(lambda x, y: exp.Add(this=x, expression=y), expression.flatten()) 1200 )
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
917 def select_sql(self, expression: exp.Select) -> str: 918 if expression.args.get("offset"): 919 if not expression.args.get("order"): 920 # ORDER BY is required in order to use OFFSET in a query, so we use 921 # a noop order by, since we don't really care about the order. 922 # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819 923 expression.order_by(exp.select(exp.null()).subquery(), copy=False) 924 925 limit = expression.args.get("limit") 926 if isinstance(limit, exp.Limit): 927 # TOP and OFFSET can't be combined, we need use FETCH instead of TOP 928 # we replace here because otherwise TOP would be generated in select_sql 929 limit.replace(exp.Fetch(direction="FIRST", count=limit.expression)) 930 931 return super().select_sql(expression)
939 def queryoption_sql(self, expression: exp.QueryOption) -> str: 940 option = self.sql(expression, "this") 941 value = self.sql(expression, "expression") 942 if value: 943 optional_equal_sign = "= " if option in OPTIONS_THAT_REQUIRE_EQUAL else "" 944 return f"{option} {optional_equal_sign}{value}" 945 return option
947 def lateral_op(self, expression: exp.Lateral) -> str: 948 cross_apply = expression.args.get("cross_apply") 949 if cross_apply is True: 950 return "CROSS APPLY" 951 if cross_apply is False: 952 return "OUTER APPLY" 953 954 # TODO: perhaps we can check if the parent is a Join and transpile it appropriately 955 self.unsupported("LATERAL clause is not supported.") 956 return "LATERAL"
958 def timefromparts_sql(self, expression: exp.TimeFromParts) -> str: 959 nano = expression.args.get("nano") 960 if nano is not None: 961 nano.pop() 962 self.unsupported("Specifying nanoseconds is not supported in TIMEFROMPARTS.") 963 964 if expression.args.get("fractions") is None: 965 expression.set("fractions", exp.Literal.number(0)) 966 if expression.args.get("precision") is None: 967 expression.set("precision", exp.Literal.number(0)) 968 969 return rename_func("TIMEFROMPARTS")(self, expression)
971 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 972 zone = expression.args.get("zone") 973 if zone is not None: 974 zone.pop() 975 self.unsupported("Time zone is not supported in DATETIMEFROMPARTS.") 976 977 nano = expression.args.get("nano") 978 if nano is not None: 979 nano.pop() 980 self.unsupported("Specifying nanoseconds is not supported in DATETIMEFROMPARTS.") 981 982 if expression.args.get("milli") is None: 983 expression.set("milli", exp.Literal.number(0)) 984 985 return rename_func("DATETIMEFROMPARTS")(self, expression)
987 def setitem_sql(self, expression: exp.SetItem) -> str: 988 this = expression.this 989 if isinstance(this, exp.EQ) and not isinstance(this.left, exp.Parameter): 990 # T-SQL does not use '=' in SET command, except when the LHS is a variable. 991 return f"{self.sql(this.left)} {self.sql(this.right)}" 992 993 return super().setitem_sql(expression)
1008 def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str: 1009 sql = self.sql(expression, "this") 1010 properties = expression.args.get("properties") 1011 1012 if sql[:1] != "#" and any( 1013 isinstance(prop, exp.TemporaryProperty) 1014 for prop in (properties.expressions if properties else []) 1015 ): 1016 sql = f"[#{sql[1:]}" if sql.startswith("[") else f"#{sql}" 1017 1018 return sql
1020 def create_sql(self, expression: exp.Create) -> str: 1021 kind = expression.kind 1022 exists = expression.args.pop("exists", None) 1023 1024 like_property = expression.find(exp.LikeProperty) 1025 if like_property: 1026 ctas_expression = like_property.this 1027 else: 1028 ctas_expression = expression.expression 1029 1030 if kind == "VIEW": 1031 expression.this.set("catalog", None) 1032 with_ = expression.args.get("with") 1033 if ctas_expression and with_: 1034 # We've already preprocessed the Create expression to bubble up any nested CTEs, 1035 # but CREATE VIEW actually requires the WITH clause to come after it so we need 1036 # to amend the AST by moving the CTEs to the CREATE VIEW statement's query. 1037 ctas_expression.set("with", with_.pop()) 1038 1039 sql = super().create_sql(expression) 1040 1041 table = expression.find(exp.Table) 1042 1043 # Convert CTAS statement to SELECT .. INTO .. 1044 if kind == "TABLE" and ctas_expression: 1045 if isinstance(ctas_expression, exp.UNWRAPPED_QUERIES): 1046 ctas_expression = ctas_expression.subquery() 1047 1048 select_into = exp.select("*").from_(exp.alias_(ctas_expression, "temp", table=True)) 1049 select_into.set("into", exp.Into(this=table)) 1050 1051 if like_property: 1052 select_into.limit(0, copy=False) 1053 1054 sql = self.sql(select_into) 1055 1056 if exists: 1057 identifier = self.sql(exp.Literal.string(exp.table_name(table) if table else "")) 1058 sql = self.sql(exp.Literal.string(sql)) 1059 if kind == "SCHEMA": 1060 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = {identifier}) EXEC({sql})""" 1061 elif kind == "TABLE": 1062 assert table 1063 where = exp.and_( 1064 exp.column("table_name").eq(table.name), 1065 exp.column("table_schema").eq(table.db) if table.db else None, 1066 exp.column("table_catalog").eq(table.catalog) if table.catalog else None, 1067 ) 1068 sql = f"""IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE {where}) EXEC({sql})""" 1069 elif kind == "INDEX": 1070 index = self.sql(exp.Literal.string(expression.this.text("this"))) 1071 sql = f"""IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id({identifier}) AND name = {index}) EXEC({sql})""" 1072 elif expression.args.get("replace"): 1073 sql = sql.replace("CREATE OR REPLACE ", "CREATE OR ALTER ", 1) 1074 1075 return self.prepend_ctes(expression, sql)
1084 def version_sql(self, expression: exp.Version) -> str: 1085 name = "SYSTEM_TIME" if expression.name == "TIMESTAMP" else expression.name 1086 this = f"FOR {name}" 1087 expr = expression.expression 1088 kind = expression.text("kind") 1089 if kind in ("FROM", "BETWEEN"): 1090 args = expr.expressions 1091 sep = "TO" if kind == "FROM" else "AND" 1092 expr_sql = f"{self.sql(seq_get(args, 0))} {sep} {self.sql(seq_get(args, 1))}" 1093 else: 1094 expr_sql = self.sql(expr) 1095 1096 expr_sql = f" {expr_sql}" if expr_sql else "" 1097 return f"{this} {kind}{expr_sql}"
1116 def commit_sql(self, expression: exp.Commit) -> str: 1117 this = self.sql(expression, "this") 1118 this = f" {this}" if this else "" 1119 durability = expression.args.get("durability") 1120 durability = ( 1121 f" WITH (DELAYED_DURABILITY = {'ON' if durability else 'OFF'})" 1122 if durability is not None 1123 else "" 1124 ) 1125 return f"COMMIT TRANSACTION{this}{durability}"
1132 def identifier_sql(self, expression: exp.Identifier) -> str: 1133 identifier = super().identifier_sql(expression) 1134 1135 if expression.args.get("global"): 1136 identifier = f"##{identifier}" 1137 elif expression.args.get("temporary"): 1138 identifier = f"#{identifier}" 1139 1140 return identifier
1182 def declareitem_sql(self, expression: exp.DeclareItem) -> str: 1183 variable = self.sql(expression, "this") 1184 default = self.sql(expression, "default") 1185 default = f" = {default}" if default else "" 1186 1187 kind = self.sql(expression, "kind") 1188 if isinstance(expression.args.get("kind"), exp.Schema): 1189 kind = f"TABLE {kind}" 1190 1191 return f"{variable} AS {kind}{default}"
Inherited Members
- sqlglot.generator.Generator
- Generator
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- 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
- set_operation
- set_operations
- 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
- 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
- groupingsets_sql
- rollup_sql
- cube_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
- 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
- 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
- duplicatekeyproperty_sql
- distributedbyproperty_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
- json_sql
- jsonvalue_sql
- conditionalinsert_sql
- multitableinserts_sql
- oncondition_sql
- jsonexists_sql
- arrayagg_sql
- apply_sql
- grant_sql
- grantprivilege_sql
- grantprincipal_sql
- columns_sql
- overlay_sql