sqlglot.dialects.tsql
1from __future__ import annotations 2 3import re 4import typing as t 5 6from sqlglot import exp, generator, parser, tokens 7from sqlglot.dialects.dialect import ( 8 Dialect, 9 max_or_greatest, 10 min_or_least, 11 parse_date_delta, 12 rename_func, 13) 14from sqlglot.expressions import DataType 15from sqlglot.helper import seq_get 16from sqlglot.time import format_time 17from sqlglot.tokens import TokenType 18 19FULL_FORMAT_TIME_MAPPING = { 20 "weekday": "%A", 21 "dw": "%A", 22 "w": "%A", 23 "month": "%B", 24 "mm": "%B", 25 "m": "%B", 26} 27 28DATE_DELTA_INTERVAL = { 29 "year": "year", 30 "yyyy": "year", 31 "yy": "year", 32 "quarter": "quarter", 33 "qq": "quarter", 34 "q": "quarter", 35 "month": "month", 36 "mm": "month", 37 "m": "month", 38 "week": "week", 39 "ww": "week", 40 "wk": "week", 41 "day": "day", 42 "dd": "day", 43 "d": "day", 44} 45 46 47DATE_FMT_RE = re.compile("([dD]{1,2})|([mM]{1,2})|([yY]{1,4})|([hH]{1,2})|([sS]{1,2})") 48 49# N = Numeric, C=Currency 50TRANSPILE_SAFE_NUMBER_FMT = {"N", "C"} 51 52 53def _format_time_lambda(exp_class, full_format_mapping=None, default=None): 54 def _format_time(args): 55 return exp_class( 56 this=seq_get(args, 1), 57 format=exp.Literal.string( 58 format_time( 59 seq_get(args, 0).name or (TSQL.time_format if default is True else default), 60 {**TSQL.time_mapping, **FULL_FORMAT_TIME_MAPPING} 61 if full_format_mapping 62 else TSQL.time_mapping, 63 ) 64 ), 65 ) 66 67 return _format_time 68 69 70def _parse_format(args): 71 fmt = seq_get(args, 1) 72 number_fmt = fmt.name in TRANSPILE_SAFE_NUMBER_FMT or not DATE_FMT_RE.search(fmt.this) 73 if number_fmt: 74 return exp.NumberToStr(this=seq_get(args, 0), format=fmt) 75 return exp.TimeToStr( 76 this=seq_get(args, 0), 77 format=exp.Literal.string( 78 format_time(fmt.name, TSQL.format_time_mapping) 79 if len(fmt.name) == 1 80 else format_time(fmt.name, TSQL.time_mapping) 81 ), 82 ) 83 84 85def _parse_eomonth(args): 86 date = seq_get(args, 0) 87 month_lag = seq_get(args, 1) 88 unit = DATE_DELTA_INTERVAL.get("month") 89 90 if month_lag is None: 91 return exp.LastDateOfMonth(this=date) 92 93 # Remove month lag argument in parser as its compared with the number of arguments of the resulting class 94 args.remove(month_lag) 95 96 return exp.LastDateOfMonth(this=exp.DateAdd(this=date, expression=month_lag, unit=unit)) 97 98 99def _parse_hashbytes(args): 100 kind, data = args 101 kind = kind.name.upper() if kind.is_string else "" 102 103 if kind == "MD5": 104 args.pop(0) 105 return exp.MD5(this=data) 106 if kind in ("SHA", "SHA1"): 107 args.pop(0) 108 return exp.SHA(this=data) 109 if kind == "SHA2_256": 110 return exp.SHA2(this=data, length=exp.Literal.number(256)) 111 if kind == "SHA2_512": 112 return exp.SHA2(this=data, length=exp.Literal.number(512)) 113 return exp.func("HASHBYTES", *args) 114 115 116def generate_date_delta_with_unit_sql(self, e): 117 func = "DATEADD" if isinstance(e, exp.DateAdd) else "DATEDIFF" 118 return self.func(func, e.text("unit"), e.expression, e.this) 119 120 121def _format_sql(self, e): 122 fmt = ( 123 e.args["format"] 124 if isinstance(e, exp.NumberToStr) 125 else exp.Literal.string(format_time(e.text("format"), TSQL.inverse_time_mapping)) 126 ) 127 return self.func("FORMAT", e.this, fmt) 128 129 130def _string_agg_sql(self, e): 131 e = e.copy() 132 133 this = e.this 134 distinct = e.find(exp.Distinct) 135 if distinct: 136 # exp.Distinct can appear below an exp.Order or an exp.GroupConcat expression 137 self.unsupported("T-SQL STRING_AGG doesn't support DISTINCT.") 138 this = distinct.pop().expressions[0] 139 140 order = "" 141 if isinstance(e.this, exp.Order): 142 if e.this.this: 143 this = e.this.this.pop() 144 order = f" WITHIN GROUP ({self.sql(e.this)[1:]})" # Order has a leading space 145 146 separator = e.args.get("separator") or exp.Literal.string(",") 147 return f"STRING_AGG({self.format_args(this, separator)}){order}" 148 149 150class TSQL(Dialect): 151 null_ordering = "nulls_are_small" 152 time_format = "'yyyy-mm-dd hh:mm:ss'" 153 154 time_mapping = { 155 "year": "%Y", 156 "qq": "%q", 157 "q": "%q", 158 "quarter": "%q", 159 "dayofyear": "%j", 160 "day": "%d", 161 "dy": "%d", 162 "y": "%Y", 163 "week": "%W", 164 "ww": "%W", 165 "wk": "%W", 166 "hour": "%h", 167 "hh": "%I", 168 "minute": "%M", 169 "mi": "%M", 170 "n": "%M", 171 "second": "%S", 172 "ss": "%S", 173 "s": "%-S", 174 "millisecond": "%f", 175 "ms": "%f", 176 "weekday": "%W", 177 "dw": "%W", 178 "month": "%m", 179 "mm": "%M", 180 "m": "%-M", 181 "Y": "%Y", 182 "YYYY": "%Y", 183 "YY": "%y", 184 "MMMM": "%B", 185 "MMM": "%b", 186 "MM": "%m", 187 "M": "%-m", 188 "dd": "%d", 189 "d": "%-d", 190 "HH": "%H", 191 "H": "%-H", 192 "h": "%-I", 193 "S": "%f", 194 "yyyy": "%Y", 195 "yy": "%y", 196 } 197 198 convert_format_mapping = { 199 "0": "%b %d %Y %-I:%M%p", 200 "1": "%m/%d/%y", 201 "2": "%y.%m.%d", 202 "3": "%d/%m/%y", 203 "4": "%d.%m.%y", 204 "5": "%d-%m-%y", 205 "6": "%d %b %y", 206 "7": "%b %d, %y", 207 "8": "%H:%M:%S", 208 "9": "%b %d %Y %-I:%M:%S:%f%p", 209 "10": "mm-dd-yy", 210 "11": "yy/mm/dd", 211 "12": "yymmdd", 212 "13": "%d %b %Y %H:%M:ss:%f", 213 "14": "%H:%M:%S:%f", 214 "20": "%Y-%m-%d %H:%M:%S", 215 "21": "%Y-%m-%d %H:%M:%S.%f", 216 "22": "%m/%d/%y %-I:%M:%S %p", 217 "23": "%Y-%m-%d", 218 "24": "%H:%M:%S", 219 "25": "%Y-%m-%d %H:%M:%S.%f", 220 "100": "%b %d %Y %-I:%M%p", 221 "101": "%m/%d/%Y", 222 "102": "%Y.%m.%d", 223 "103": "%d/%m/%Y", 224 "104": "%d.%m.%Y", 225 "105": "%d-%m-%Y", 226 "106": "%d %b %Y", 227 "107": "%b %d, %Y", 228 "108": "%H:%M:%S", 229 "109": "%b %d %Y %-I:%M:%S:%f%p", 230 "110": "%m-%d-%Y", 231 "111": "%Y/%m/%d", 232 "112": "%Y%m%d", 233 "113": "%d %b %Y %H:%M:%S:%f", 234 "114": "%H:%M:%S:%f", 235 "120": "%Y-%m-%d %H:%M:%S", 236 "121": "%Y-%m-%d %H:%M:%S.%f", 237 } 238 # not sure if complete 239 format_time_mapping = { 240 "y": "%B %Y", 241 "d": "%m/%d/%Y", 242 "H": "%-H", 243 "h": "%-I", 244 "s": "%Y-%m-%d %H:%M:%S", 245 "D": "%A,%B,%Y", 246 "f": "%A,%B,%Y %-I:%M %p", 247 "F": "%A,%B,%Y %-I:%M:%S %p", 248 "g": "%m/%d/%Y %-I:%M %p", 249 "G": "%m/%d/%Y %-I:%M:%S %p", 250 "M": "%B %-d", 251 "m": "%B %-d", 252 "O": "%Y-%m-%dT%H:%M:%S", 253 "u": "%Y-%M-%D %H:%M:%S%z", 254 "U": "%A, %B %D, %Y %H:%M:%S%z", 255 "T": "%-I:%M:%S %p", 256 "t": "%-I:%M", 257 "Y": "%a %Y", 258 } 259 260 class Tokenizer(tokens.Tokenizer): 261 IDENTIFIERS = ['"', ("[", "]")] 262 263 QUOTES = ["'", '"'] 264 265 KEYWORDS = { 266 **tokens.Tokenizer.KEYWORDS, 267 "DATETIME2": TokenType.DATETIME, 268 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 269 "DECLARE": TokenType.COMMAND, 270 "IMAGE": TokenType.IMAGE, 271 "MONEY": TokenType.MONEY, 272 "NTEXT": TokenType.TEXT, 273 "NVARCHAR(MAX)": TokenType.TEXT, 274 "PRINT": TokenType.COMMAND, 275 "PROC": TokenType.PROCEDURE, 276 "REAL": TokenType.FLOAT, 277 "ROWVERSION": TokenType.ROWVERSION, 278 "SMALLDATETIME": TokenType.DATETIME, 279 "SMALLMONEY": TokenType.SMALLMONEY, 280 "SQL_VARIANT": TokenType.VARIANT, 281 "TIME": TokenType.TIMESTAMP, 282 "TOP": TokenType.TOP, 283 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 284 "VARCHAR(MAX)": TokenType.TEXT, 285 "XML": TokenType.XML, 286 "SYSTEM_USER": TokenType.CURRENT_USER, 287 } 288 289 # TSQL allows @, # to appear as a variable/identifier prefix 290 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 291 SINGLE_TOKENS.pop("#") 292 293 class Parser(parser.Parser): 294 FUNCTIONS = { 295 **parser.Parser.FUNCTIONS, # type: ignore 296 "CHARINDEX": lambda args: exp.StrPosition( 297 this=seq_get(args, 1), 298 substr=seq_get(args, 0), 299 position=seq_get(args, 2), 300 ), 301 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 302 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 303 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 304 "DATEPART": _format_time_lambda(exp.TimeToStr), 305 "EOMONTH": _parse_eomonth, 306 "FORMAT": _parse_format, 307 "GETDATE": exp.CurrentTimestamp.from_arg_list, 308 "HASHBYTES": _parse_hashbytes, 309 "IIF": exp.If.from_arg_list, 310 "ISNULL": exp.Coalesce.from_arg_list, 311 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 312 "LEN": exp.Length.from_arg_list, 313 "REPLICATE": exp.Repeat.from_arg_list, 314 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 315 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 316 "SUSER_NAME": exp.CurrentUser.from_arg_list, 317 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 318 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 319 } 320 321 JOIN_HINTS = { 322 "LOOP", 323 "HASH", 324 "MERGE", 325 "REMOTE", 326 } 327 328 VAR_LENGTH_DATATYPES = { 329 DataType.Type.NVARCHAR, 330 DataType.Type.VARCHAR, 331 DataType.Type.CHAR, 332 DataType.Type.NCHAR, 333 } 334 335 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { # type: ignore 336 TokenType.TABLE, 337 *parser.Parser.TYPE_TOKENS, # type: ignore 338 } 339 340 STATEMENT_PARSERS = { 341 **parser.Parser.STATEMENT_PARSERS, # type: ignore 342 TokenType.END: lambda self: self._parse_command(), 343 } 344 345 LOG_BASE_FIRST = False 346 LOG_DEFAULTS_TO_LN = True 347 348 def _parse_system_time(self) -> t.Optional[exp.Expression]: 349 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 350 return None 351 352 if self._match_text_seq("AS", "OF"): 353 system_time = self.expression( 354 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 355 ) 356 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 357 kind = self._prev.text 358 this = self._parse_bitwise() 359 self._match_texts(("TO", "AND")) 360 expression = self._parse_bitwise() 361 system_time = self.expression( 362 exp.SystemTime, this=this, expression=expression, kind=kind 363 ) 364 elif self._match_text_seq("CONTAINED", "IN"): 365 args = self._parse_wrapped_csv(self._parse_bitwise) 366 system_time = self.expression( 367 exp.SystemTime, 368 this=seq_get(args, 0), 369 expression=seq_get(args, 1), 370 kind="CONTAINED IN", 371 ) 372 elif self._match(TokenType.ALL): 373 system_time = self.expression(exp.SystemTime, kind="ALL") 374 else: 375 system_time = None 376 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 377 378 return system_time 379 380 def _parse_table_parts(self, schema: bool = False) -> exp.Expression: 381 table = super()._parse_table_parts(schema=schema) 382 table.set("system_time", self._parse_system_time()) 383 return table 384 385 def _parse_returns(self) -> exp.Expression: 386 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 387 returns = super()._parse_returns() 388 returns.set("table", table) 389 return returns 390 391 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 392 to = self._parse_types() 393 self._match(TokenType.COMMA) 394 this = self._parse_conjunction() 395 396 if not to or not this: 397 return None 398 399 # Retrieve length of datatype and override to default if not specified 400 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 401 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 402 403 # Check whether a conversion with format is applicable 404 if self._match(TokenType.COMMA): 405 format_val = self._parse_number() 406 format_val_name = format_val.name if format_val else "" 407 408 if format_val_name not in TSQL.convert_format_mapping: 409 raise ValueError( 410 f"CONVERT function at T-SQL does not support format style {format_val_name}" 411 ) 412 413 format_norm = exp.Literal.string(TSQL.convert_format_mapping[format_val_name]) 414 415 # Check whether the convert entails a string to date format 416 if to.this == DataType.Type.DATE: 417 return self.expression(exp.StrToDate, this=this, format=format_norm) 418 # Check whether the convert entails a string to datetime format 419 elif to.this == DataType.Type.DATETIME: 420 return self.expression(exp.StrToTime, this=this, format=format_norm) 421 # Check whether the convert entails a date to string format 422 elif to.this in self.VAR_LENGTH_DATATYPES: 423 return self.expression( 424 exp.Cast if strict else exp.TryCast, 425 to=to, 426 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 427 ) 428 elif to.this == DataType.Type.TEXT: 429 return self.expression(exp.TimeToStr, this=this, format=format_norm) 430 431 # Entails a simple cast without any format requirement 432 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 433 434 def _parse_user_defined_function( 435 self, kind: t.Optional[TokenType] = None 436 ) -> t.Optional[exp.Expression]: 437 this = super()._parse_user_defined_function(kind=kind) 438 439 if ( 440 kind == TokenType.FUNCTION 441 or isinstance(this, exp.UserDefinedFunction) 442 or self._match(TokenType.ALIAS, advance=False) 443 ): 444 return this 445 446 expressions = self._parse_csv(self._parse_function_parameter) 447 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 448 449 class Generator(generator.Generator): 450 LOCKING_READS_SUPPORTED = True 451 452 TYPE_MAPPING = { 453 **generator.Generator.TYPE_MAPPING, # type: ignore 454 exp.DataType.Type.INT: "INTEGER", 455 exp.DataType.Type.DECIMAL: "NUMERIC", 456 exp.DataType.Type.DATETIME: "DATETIME2", 457 exp.DataType.Type.VARIANT: "SQL_VARIANT", 458 } 459 460 TRANSFORMS = { 461 **generator.Generator.TRANSFORMS, # type: ignore 462 exp.DateAdd: generate_date_delta_with_unit_sql, 463 exp.DateDiff: generate_date_delta_with_unit_sql, 464 exp.CurrentDate: rename_func("GETDATE"), 465 exp.CurrentTimestamp: rename_func("GETDATE"), 466 exp.If: rename_func("IIF"), 467 exp.NumberToStr: _format_sql, 468 exp.TimeToStr: _format_sql, 469 exp.GroupConcat: _string_agg_sql, 470 exp.Max: max_or_greatest, 471 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 472 exp.Min: min_or_least, 473 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 474 exp.SHA2: lambda self, e: self.func( 475 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 476 ), 477 } 478 479 TRANSFORMS.pop(exp.ReturnsProperty) 480 481 PROPERTIES_LOCATION = { 482 **generator.Generator.PROPERTIES_LOCATION, # type: ignore 483 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 484 } 485 486 LIMIT_FETCH = "FETCH" 487 488 def offset_sql(self, expression: exp.Offset) -> str: 489 return f"{super().offset_sql(expression)} ROWS" 490 491 def systemtime_sql(self, expression: exp.SystemTime) -> str: 492 kind = expression.args["kind"] 493 if kind == "ALL": 494 return "FOR SYSTEM_TIME ALL" 495 496 start = self.sql(expression, "this") 497 if kind == "AS OF": 498 return f"FOR SYSTEM_TIME AS OF {start}" 499 500 end = self.sql(expression, "expression") 501 if kind == "FROM": 502 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 503 if kind == "BETWEEN": 504 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 505 506 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 507 508 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 509 table = expression.args.get("table") 510 table = f"{table} " if table else "" 511 return f"RETURNS {table}{self.sql(expression, 'this')}"
def
generate_date_delta_with_unit_sql(self, e):
151class TSQL(Dialect): 152 null_ordering = "nulls_are_small" 153 time_format = "'yyyy-mm-dd hh:mm:ss'" 154 155 time_mapping = { 156 "year": "%Y", 157 "qq": "%q", 158 "q": "%q", 159 "quarter": "%q", 160 "dayofyear": "%j", 161 "day": "%d", 162 "dy": "%d", 163 "y": "%Y", 164 "week": "%W", 165 "ww": "%W", 166 "wk": "%W", 167 "hour": "%h", 168 "hh": "%I", 169 "minute": "%M", 170 "mi": "%M", 171 "n": "%M", 172 "second": "%S", 173 "ss": "%S", 174 "s": "%-S", 175 "millisecond": "%f", 176 "ms": "%f", 177 "weekday": "%W", 178 "dw": "%W", 179 "month": "%m", 180 "mm": "%M", 181 "m": "%-M", 182 "Y": "%Y", 183 "YYYY": "%Y", 184 "YY": "%y", 185 "MMMM": "%B", 186 "MMM": "%b", 187 "MM": "%m", 188 "M": "%-m", 189 "dd": "%d", 190 "d": "%-d", 191 "HH": "%H", 192 "H": "%-H", 193 "h": "%-I", 194 "S": "%f", 195 "yyyy": "%Y", 196 "yy": "%y", 197 } 198 199 convert_format_mapping = { 200 "0": "%b %d %Y %-I:%M%p", 201 "1": "%m/%d/%y", 202 "2": "%y.%m.%d", 203 "3": "%d/%m/%y", 204 "4": "%d.%m.%y", 205 "5": "%d-%m-%y", 206 "6": "%d %b %y", 207 "7": "%b %d, %y", 208 "8": "%H:%M:%S", 209 "9": "%b %d %Y %-I:%M:%S:%f%p", 210 "10": "mm-dd-yy", 211 "11": "yy/mm/dd", 212 "12": "yymmdd", 213 "13": "%d %b %Y %H:%M:ss:%f", 214 "14": "%H:%M:%S:%f", 215 "20": "%Y-%m-%d %H:%M:%S", 216 "21": "%Y-%m-%d %H:%M:%S.%f", 217 "22": "%m/%d/%y %-I:%M:%S %p", 218 "23": "%Y-%m-%d", 219 "24": "%H:%M:%S", 220 "25": "%Y-%m-%d %H:%M:%S.%f", 221 "100": "%b %d %Y %-I:%M%p", 222 "101": "%m/%d/%Y", 223 "102": "%Y.%m.%d", 224 "103": "%d/%m/%Y", 225 "104": "%d.%m.%Y", 226 "105": "%d-%m-%Y", 227 "106": "%d %b %Y", 228 "107": "%b %d, %Y", 229 "108": "%H:%M:%S", 230 "109": "%b %d %Y %-I:%M:%S:%f%p", 231 "110": "%m-%d-%Y", 232 "111": "%Y/%m/%d", 233 "112": "%Y%m%d", 234 "113": "%d %b %Y %H:%M:%S:%f", 235 "114": "%H:%M:%S:%f", 236 "120": "%Y-%m-%d %H:%M:%S", 237 "121": "%Y-%m-%d %H:%M:%S.%f", 238 } 239 # not sure if complete 240 format_time_mapping = { 241 "y": "%B %Y", 242 "d": "%m/%d/%Y", 243 "H": "%-H", 244 "h": "%-I", 245 "s": "%Y-%m-%d %H:%M:%S", 246 "D": "%A,%B,%Y", 247 "f": "%A,%B,%Y %-I:%M %p", 248 "F": "%A,%B,%Y %-I:%M:%S %p", 249 "g": "%m/%d/%Y %-I:%M %p", 250 "G": "%m/%d/%Y %-I:%M:%S %p", 251 "M": "%B %-d", 252 "m": "%B %-d", 253 "O": "%Y-%m-%dT%H:%M:%S", 254 "u": "%Y-%M-%D %H:%M:%S%z", 255 "U": "%A, %B %D, %Y %H:%M:%S%z", 256 "T": "%-I:%M:%S %p", 257 "t": "%-I:%M", 258 "Y": "%a %Y", 259 } 260 261 class Tokenizer(tokens.Tokenizer): 262 IDENTIFIERS = ['"', ("[", "]")] 263 264 QUOTES = ["'", '"'] 265 266 KEYWORDS = { 267 **tokens.Tokenizer.KEYWORDS, 268 "DATETIME2": TokenType.DATETIME, 269 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 270 "DECLARE": TokenType.COMMAND, 271 "IMAGE": TokenType.IMAGE, 272 "MONEY": TokenType.MONEY, 273 "NTEXT": TokenType.TEXT, 274 "NVARCHAR(MAX)": TokenType.TEXT, 275 "PRINT": TokenType.COMMAND, 276 "PROC": TokenType.PROCEDURE, 277 "REAL": TokenType.FLOAT, 278 "ROWVERSION": TokenType.ROWVERSION, 279 "SMALLDATETIME": TokenType.DATETIME, 280 "SMALLMONEY": TokenType.SMALLMONEY, 281 "SQL_VARIANT": TokenType.VARIANT, 282 "TIME": TokenType.TIMESTAMP, 283 "TOP": TokenType.TOP, 284 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 285 "VARCHAR(MAX)": TokenType.TEXT, 286 "XML": TokenType.XML, 287 "SYSTEM_USER": TokenType.CURRENT_USER, 288 } 289 290 # TSQL allows @, # to appear as a variable/identifier prefix 291 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 292 SINGLE_TOKENS.pop("#") 293 294 class Parser(parser.Parser): 295 FUNCTIONS = { 296 **parser.Parser.FUNCTIONS, # type: ignore 297 "CHARINDEX": lambda args: exp.StrPosition( 298 this=seq_get(args, 1), 299 substr=seq_get(args, 0), 300 position=seq_get(args, 2), 301 ), 302 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 303 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 304 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 305 "DATEPART": _format_time_lambda(exp.TimeToStr), 306 "EOMONTH": _parse_eomonth, 307 "FORMAT": _parse_format, 308 "GETDATE": exp.CurrentTimestamp.from_arg_list, 309 "HASHBYTES": _parse_hashbytes, 310 "IIF": exp.If.from_arg_list, 311 "ISNULL": exp.Coalesce.from_arg_list, 312 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 313 "LEN": exp.Length.from_arg_list, 314 "REPLICATE": exp.Repeat.from_arg_list, 315 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 316 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 317 "SUSER_NAME": exp.CurrentUser.from_arg_list, 318 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 319 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 320 } 321 322 JOIN_HINTS = { 323 "LOOP", 324 "HASH", 325 "MERGE", 326 "REMOTE", 327 } 328 329 VAR_LENGTH_DATATYPES = { 330 DataType.Type.NVARCHAR, 331 DataType.Type.VARCHAR, 332 DataType.Type.CHAR, 333 DataType.Type.NCHAR, 334 } 335 336 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { # type: ignore 337 TokenType.TABLE, 338 *parser.Parser.TYPE_TOKENS, # type: ignore 339 } 340 341 STATEMENT_PARSERS = { 342 **parser.Parser.STATEMENT_PARSERS, # type: ignore 343 TokenType.END: lambda self: self._parse_command(), 344 } 345 346 LOG_BASE_FIRST = False 347 LOG_DEFAULTS_TO_LN = True 348 349 def _parse_system_time(self) -> t.Optional[exp.Expression]: 350 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 351 return None 352 353 if self._match_text_seq("AS", "OF"): 354 system_time = self.expression( 355 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 356 ) 357 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 358 kind = self._prev.text 359 this = self._parse_bitwise() 360 self._match_texts(("TO", "AND")) 361 expression = self._parse_bitwise() 362 system_time = self.expression( 363 exp.SystemTime, this=this, expression=expression, kind=kind 364 ) 365 elif self._match_text_seq("CONTAINED", "IN"): 366 args = self._parse_wrapped_csv(self._parse_bitwise) 367 system_time = self.expression( 368 exp.SystemTime, 369 this=seq_get(args, 0), 370 expression=seq_get(args, 1), 371 kind="CONTAINED IN", 372 ) 373 elif self._match(TokenType.ALL): 374 system_time = self.expression(exp.SystemTime, kind="ALL") 375 else: 376 system_time = None 377 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 378 379 return system_time 380 381 def _parse_table_parts(self, schema: bool = False) -> exp.Expression: 382 table = super()._parse_table_parts(schema=schema) 383 table.set("system_time", self._parse_system_time()) 384 return table 385 386 def _parse_returns(self) -> exp.Expression: 387 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 388 returns = super()._parse_returns() 389 returns.set("table", table) 390 return returns 391 392 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 393 to = self._parse_types() 394 self._match(TokenType.COMMA) 395 this = self._parse_conjunction() 396 397 if not to or not this: 398 return None 399 400 # Retrieve length of datatype and override to default if not specified 401 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 402 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 403 404 # Check whether a conversion with format is applicable 405 if self._match(TokenType.COMMA): 406 format_val = self._parse_number() 407 format_val_name = format_val.name if format_val else "" 408 409 if format_val_name not in TSQL.convert_format_mapping: 410 raise ValueError( 411 f"CONVERT function at T-SQL does not support format style {format_val_name}" 412 ) 413 414 format_norm = exp.Literal.string(TSQL.convert_format_mapping[format_val_name]) 415 416 # Check whether the convert entails a string to date format 417 if to.this == DataType.Type.DATE: 418 return self.expression(exp.StrToDate, this=this, format=format_norm) 419 # Check whether the convert entails a string to datetime format 420 elif to.this == DataType.Type.DATETIME: 421 return self.expression(exp.StrToTime, this=this, format=format_norm) 422 # Check whether the convert entails a date to string format 423 elif to.this in self.VAR_LENGTH_DATATYPES: 424 return self.expression( 425 exp.Cast if strict else exp.TryCast, 426 to=to, 427 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 428 ) 429 elif to.this == DataType.Type.TEXT: 430 return self.expression(exp.TimeToStr, this=this, format=format_norm) 431 432 # Entails a simple cast without any format requirement 433 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 434 435 def _parse_user_defined_function( 436 self, kind: t.Optional[TokenType] = None 437 ) -> t.Optional[exp.Expression]: 438 this = super()._parse_user_defined_function(kind=kind) 439 440 if ( 441 kind == TokenType.FUNCTION 442 or isinstance(this, exp.UserDefinedFunction) 443 or self._match(TokenType.ALIAS, advance=False) 444 ): 445 return this 446 447 expressions = self._parse_csv(self._parse_function_parameter) 448 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 449 450 class Generator(generator.Generator): 451 LOCKING_READS_SUPPORTED = True 452 453 TYPE_MAPPING = { 454 **generator.Generator.TYPE_MAPPING, # type: ignore 455 exp.DataType.Type.INT: "INTEGER", 456 exp.DataType.Type.DECIMAL: "NUMERIC", 457 exp.DataType.Type.DATETIME: "DATETIME2", 458 exp.DataType.Type.VARIANT: "SQL_VARIANT", 459 } 460 461 TRANSFORMS = { 462 **generator.Generator.TRANSFORMS, # type: ignore 463 exp.DateAdd: generate_date_delta_with_unit_sql, 464 exp.DateDiff: generate_date_delta_with_unit_sql, 465 exp.CurrentDate: rename_func("GETDATE"), 466 exp.CurrentTimestamp: rename_func("GETDATE"), 467 exp.If: rename_func("IIF"), 468 exp.NumberToStr: _format_sql, 469 exp.TimeToStr: _format_sql, 470 exp.GroupConcat: _string_agg_sql, 471 exp.Max: max_or_greatest, 472 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 473 exp.Min: min_or_least, 474 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 475 exp.SHA2: lambda self, e: self.func( 476 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 477 ), 478 } 479 480 TRANSFORMS.pop(exp.ReturnsProperty) 481 482 PROPERTIES_LOCATION = { 483 **generator.Generator.PROPERTIES_LOCATION, # type: ignore 484 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 485 } 486 487 LIMIT_FETCH = "FETCH" 488 489 def offset_sql(self, expression: exp.Offset) -> str: 490 return f"{super().offset_sql(expression)} ROWS" 491 492 def systemtime_sql(self, expression: exp.SystemTime) -> str: 493 kind = expression.args["kind"] 494 if kind == "ALL": 495 return "FOR SYSTEM_TIME ALL" 496 497 start = self.sql(expression, "this") 498 if kind == "AS OF": 499 return f"FOR SYSTEM_TIME AS OF {start}" 500 501 end = self.sql(expression, "expression") 502 if kind == "FROM": 503 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 504 if kind == "BETWEEN": 505 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 506 507 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 508 509 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 510 table = expression.args.get("table") 511 table = f"{table} " if table else "" 512 return f"RETURNS {table}{self.sql(expression, 'this')}"
261 class Tokenizer(tokens.Tokenizer): 262 IDENTIFIERS = ['"', ("[", "]")] 263 264 QUOTES = ["'", '"'] 265 266 KEYWORDS = { 267 **tokens.Tokenizer.KEYWORDS, 268 "DATETIME2": TokenType.DATETIME, 269 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 270 "DECLARE": TokenType.COMMAND, 271 "IMAGE": TokenType.IMAGE, 272 "MONEY": TokenType.MONEY, 273 "NTEXT": TokenType.TEXT, 274 "NVARCHAR(MAX)": TokenType.TEXT, 275 "PRINT": TokenType.COMMAND, 276 "PROC": TokenType.PROCEDURE, 277 "REAL": TokenType.FLOAT, 278 "ROWVERSION": TokenType.ROWVERSION, 279 "SMALLDATETIME": TokenType.DATETIME, 280 "SMALLMONEY": TokenType.SMALLMONEY, 281 "SQL_VARIANT": TokenType.VARIANT, 282 "TIME": TokenType.TIMESTAMP, 283 "TOP": TokenType.TOP, 284 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 285 "VARCHAR(MAX)": TokenType.TEXT, 286 "XML": TokenType.XML, 287 "SYSTEM_USER": TokenType.CURRENT_USER, 288 } 289 290 # TSQL allows @, # to appear as a variable/identifier prefix 291 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 292 SINGLE_TOKENS.pop("#")
Inherited Members
294 class Parser(parser.Parser): 295 FUNCTIONS = { 296 **parser.Parser.FUNCTIONS, # type: ignore 297 "CHARINDEX": lambda args: exp.StrPosition( 298 this=seq_get(args, 1), 299 substr=seq_get(args, 0), 300 position=seq_get(args, 2), 301 ), 302 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 303 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 304 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 305 "DATEPART": _format_time_lambda(exp.TimeToStr), 306 "EOMONTH": _parse_eomonth, 307 "FORMAT": _parse_format, 308 "GETDATE": exp.CurrentTimestamp.from_arg_list, 309 "HASHBYTES": _parse_hashbytes, 310 "IIF": exp.If.from_arg_list, 311 "ISNULL": exp.Coalesce.from_arg_list, 312 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 313 "LEN": exp.Length.from_arg_list, 314 "REPLICATE": exp.Repeat.from_arg_list, 315 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 316 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 317 "SUSER_NAME": exp.CurrentUser.from_arg_list, 318 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 319 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 320 } 321 322 JOIN_HINTS = { 323 "LOOP", 324 "HASH", 325 "MERGE", 326 "REMOTE", 327 } 328 329 VAR_LENGTH_DATATYPES = { 330 DataType.Type.NVARCHAR, 331 DataType.Type.VARCHAR, 332 DataType.Type.CHAR, 333 DataType.Type.NCHAR, 334 } 335 336 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { # type: ignore 337 TokenType.TABLE, 338 *parser.Parser.TYPE_TOKENS, # type: ignore 339 } 340 341 STATEMENT_PARSERS = { 342 **parser.Parser.STATEMENT_PARSERS, # type: ignore 343 TokenType.END: lambda self: self._parse_command(), 344 } 345 346 LOG_BASE_FIRST = False 347 LOG_DEFAULTS_TO_LN = True 348 349 def _parse_system_time(self) -> t.Optional[exp.Expression]: 350 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 351 return None 352 353 if self._match_text_seq("AS", "OF"): 354 system_time = self.expression( 355 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 356 ) 357 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 358 kind = self._prev.text 359 this = self._parse_bitwise() 360 self._match_texts(("TO", "AND")) 361 expression = self._parse_bitwise() 362 system_time = self.expression( 363 exp.SystemTime, this=this, expression=expression, kind=kind 364 ) 365 elif self._match_text_seq("CONTAINED", "IN"): 366 args = self._parse_wrapped_csv(self._parse_bitwise) 367 system_time = self.expression( 368 exp.SystemTime, 369 this=seq_get(args, 0), 370 expression=seq_get(args, 1), 371 kind="CONTAINED IN", 372 ) 373 elif self._match(TokenType.ALL): 374 system_time = self.expression(exp.SystemTime, kind="ALL") 375 else: 376 system_time = None 377 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 378 379 return system_time 380 381 def _parse_table_parts(self, schema: bool = False) -> exp.Expression: 382 table = super()._parse_table_parts(schema=schema) 383 table.set("system_time", self._parse_system_time()) 384 return table 385 386 def _parse_returns(self) -> exp.Expression: 387 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 388 returns = super()._parse_returns() 389 returns.set("table", table) 390 return returns 391 392 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 393 to = self._parse_types() 394 self._match(TokenType.COMMA) 395 this = self._parse_conjunction() 396 397 if not to or not this: 398 return None 399 400 # Retrieve length of datatype and override to default if not specified 401 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 402 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 403 404 # Check whether a conversion with format is applicable 405 if self._match(TokenType.COMMA): 406 format_val = self._parse_number() 407 format_val_name = format_val.name if format_val else "" 408 409 if format_val_name not in TSQL.convert_format_mapping: 410 raise ValueError( 411 f"CONVERT function at T-SQL does not support format style {format_val_name}" 412 ) 413 414 format_norm = exp.Literal.string(TSQL.convert_format_mapping[format_val_name]) 415 416 # Check whether the convert entails a string to date format 417 if to.this == DataType.Type.DATE: 418 return self.expression(exp.StrToDate, this=this, format=format_norm) 419 # Check whether the convert entails a string to datetime format 420 elif to.this == DataType.Type.DATETIME: 421 return self.expression(exp.StrToTime, this=this, format=format_norm) 422 # Check whether the convert entails a date to string format 423 elif to.this in self.VAR_LENGTH_DATATYPES: 424 return self.expression( 425 exp.Cast if strict else exp.TryCast, 426 to=to, 427 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 428 ) 429 elif to.this == DataType.Type.TEXT: 430 return self.expression(exp.TimeToStr, this=this, format=format_norm) 431 432 # Entails a simple cast without any format requirement 433 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 434 435 def _parse_user_defined_function( 436 self, kind: t.Optional[TokenType] = None 437 ) -> t.Optional[exp.Expression]: 438 this = super()._parse_user_defined_function(kind=kind) 439 440 if ( 441 kind == TokenType.FUNCTION 442 or isinstance(this, exp.UserDefinedFunction) 443 or self._match(TokenType.ALIAS, advance=False) 444 ): 445 return this 446 447 expressions = self._parse_csv(self._parse_function_parameter) 448 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions)
Parser consumes a list of tokens produced by the sqlglot.tokens.Tokenizer
and produces
a parsed syntax tree.
Arguments:
- error_level: the desired error level. Default: ErrorLevel.RAISE
- error_message_context: determines the amount of context to capture from a query string when displaying the error message (in number of characters). Default: 50.
- index_offset: Index offset for arrays eg ARRAY[0] vs ARRAY[1] as the head of a list. Default: 0
- alias_post_tablesample: If the table alias comes after tablesample. Default: False
- 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
- null_ordering: Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
Inherited Members
450 class Generator(generator.Generator): 451 LOCKING_READS_SUPPORTED = True 452 453 TYPE_MAPPING = { 454 **generator.Generator.TYPE_MAPPING, # type: ignore 455 exp.DataType.Type.INT: "INTEGER", 456 exp.DataType.Type.DECIMAL: "NUMERIC", 457 exp.DataType.Type.DATETIME: "DATETIME2", 458 exp.DataType.Type.VARIANT: "SQL_VARIANT", 459 } 460 461 TRANSFORMS = { 462 **generator.Generator.TRANSFORMS, # type: ignore 463 exp.DateAdd: generate_date_delta_with_unit_sql, 464 exp.DateDiff: generate_date_delta_with_unit_sql, 465 exp.CurrentDate: rename_func("GETDATE"), 466 exp.CurrentTimestamp: rename_func("GETDATE"), 467 exp.If: rename_func("IIF"), 468 exp.NumberToStr: _format_sql, 469 exp.TimeToStr: _format_sql, 470 exp.GroupConcat: _string_agg_sql, 471 exp.Max: max_or_greatest, 472 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 473 exp.Min: min_or_least, 474 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 475 exp.SHA2: lambda self, e: self.func( 476 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 477 ), 478 } 479 480 TRANSFORMS.pop(exp.ReturnsProperty) 481 482 PROPERTIES_LOCATION = { 483 **generator.Generator.PROPERTIES_LOCATION, # type: ignore 484 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 485 } 486 487 LIMIT_FETCH = "FETCH" 488 489 def offset_sql(self, expression: exp.Offset) -> str: 490 return f"{super().offset_sql(expression)} ROWS" 491 492 def systemtime_sql(self, expression: exp.SystemTime) -> str: 493 kind = expression.args["kind"] 494 if kind == "ALL": 495 return "FOR SYSTEM_TIME ALL" 496 497 start = self.sql(expression, "this") 498 if kind == "AS OF": 499 return f"FOR SYSTEM_TIME AS OF {start}" 500 501 end = self.sql(expression, "expression") 502 if kind == "FROM": 503 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 504 if kind == "BETWEEN": 505 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 506 507 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 508 509 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 510 table = expression.args.get("table") 511 table = f"{table} " if table else "" 512 return f"RETURNS {table}{self.sql(expression, 'this')}"
Generator interprets the given syntax tree and produces a SQL string as an output.
Arguments:
- time_mapping (dict): the dictionary of custom time mappings in which the key represents a python time format and the output the target time format
- time_trie (trie): a trie of the time_mapping keys
- pretty (bool): if set to True the returned string will be formatted. Default: False.
- quote_start (str): specifies which starting character to use to delimit quotes. Default: '.
- quote_end (str): specifies which ending character to use to delimit quotes. Default: '.
- identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
- identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
- identify (bool | str): 'always': always quote, 'safe': quote identifiers if they don't contain an upcase, True defaults to always.
- normalize (bool): if set to True all identifiers will lower cased
- string_escape (str): specifies a string escape character. Default: '.
- identifier_escape (str): specifies an identifier escape character. Default: ".
- pad (int): determines padding in a formatted string. Default: 2.
- indent (int): determines the size of indentation in a formatted string. Default: 4.
- unnest_column_only (bool): if true unnest table aliases are considered only as column aliases
- normalize_functions (str): normalize function names, "upper", "lower", or None Default: "upper"
- alias_post_tablesample (bool): if the table alias comes after tablesample Default: False
- unsupported_level (ErrorLevel): determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- null_ordering (str): Indicates the default null ordering method to use if not explicitly set. Options are "nulls_are_small", "nulls_are_large", "nulls_are_last". Default: "nulls_are_small"
- max_unsupported (int): Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
- leading_comma (bool): if the the comma is leading or trailing in select statements Default: False
- max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
- comments: Whether or not to preserve comments in the output SQL code. Default: True
492 def systemtime_sql(self, expression: exp.SystemTime) -> str: 493 kind = expression.args["kind"] 494 if kind == "ALL": 495 return "FOR SYSTEM_TIME ALL" 496 497 start = self.sql(expression, "this") 498 if kind == "AS OF": 499 return f"FOR SYSTEM_TIME AS OF {start}" 500 501 end = self.sql(expression, "expression") 502 if kind == "FROM": 503 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 504 if kind == "BETWEEN": 505 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 506 507 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})"
Inherited Members
- sqlglot.generator.Generator
- Generator
- generate
- unsupported
- sep
- seg
- pad_comment
- maybe_comment
- wrap
- no_identify
- normalize_func
- indent
- sql
- uncache_sql
- cache_sql
- characterset_sql
- column_sql
- columnposition_sql
- columndef_sql
- columnconstraint_sql
- autoincrementcolumnconstraint_sql
- compresscolumnconstraint_sql
- generatedasidentitycolumnconstraint_sql
- notnullcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- create_sql
- describe_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- datatype_sql
- directory_sql
- delete_sql
- drop_sql
- except_sql
- except_op
- fetch_sql
- filter_sql
- hint_sql
- index_sql
- identifier_sql
- inputoutputformat_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- properties
- with_properties
- locate_properties
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- afterjournalproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- lockingproperty_sql
- withdataproperty_sql
- insert_sql
- intersect_sql
- intersect_op
- introducer_sql
- pseudotype_sql
- onconflict_sql
- returning_sql
- rowformatdelimitedproperty_sql
- table_sql
- tablesample_sql
- pivot_sql
- tuple_sql
- update_sql
- values_sql
- var_sql
- into_sql
- from_sql
- group_sql
- having_sql
- join_sql
- lambda_sql
- lateral_sql
- limit_sql
- setitem_sql
- set_sql
- pragma_sql
- lock_sql
- literal_sql
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognize_sql
- query_modifiers
- select_sql
- schema_sql
- star_sql
- structkwarg_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- union_sql
- union_op
- unnest_sql
- where_sql
- window_sql
- partition_by_sql
- window_spec_sql
- withingroup_sql
- between_sql
- bracket_sql
- all_sql
- any_sql
- exists_sql
- case_sql
- constraint_sql
- extract_sql
- trim_sql
- concat_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- unique_sql
- if_sql
- matchagainst_sql
- jsonkeyvalue_sql
- jsonobject_sql
- in_sql
- in_unnest_op
- interval_sql
- return_sql
- reference_sql
- anonymous_sql
- paren_sql
- neg_sql
- not_sql
- alias_sql
- aliases_sql
- attimezone_sql
- add_sql
- and_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
- transaction_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- renametable_sql
- altertable_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- intdiv_sql
- dpipe_sql
- div_sql
- overlaps_sql
- distance_sql
- dot_sql
- eq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- ilikeany_sql
- is_sql
- like_sql
- likeany_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- or_sql
- slice_sql
- sub_sql
- trycast_sql
- use_sql
- binary
- function_fallback_sql
- func
- format_args
- text_width
- format_time
- expressions
- op_expressions
- naked_property
- set_operation
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql
- tochar_sql