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