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