sqlglot.dialects.tsql
1from __future__ import annotations 2 3import re 4import typing as t 5 6from sqlglot import exp, generator, parser, tokens, transforms 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 19if t.TYPE_CHECKING: 20 from sqlglot._typing import E 21 22FULL_FORMAT_TIME_MAPPING = { 23 "weekday": "%A", 24 "dw": "%A", 25 "w": "%A", 26 "month": "%B", 27 "mm": "%B", 28 "m": "%B", 29} 30 31DATE_DELTA_INTERVAL = { 32 "year": "year", 33 "yyyy": "year", 34 "yy": "year", 35 "quarter": "quarter", 36 "qq": "quarter", 37 "q": "quarter", 38 "month": "month", 39 "mm": "month", 40 "m": "month", 41 "week": "week", 42 "ww": "week", 43 "wk": "week", 44 "day": "day", 45 "dd": "day", 46 "d": "day", 47} 48 49 50DATE_FMT_RE = re.compile("([dD]{1,2})|([mM]{1,2})|([yY]{1,4})|([hH]{1,2})|([sS]{1,2})") 51 52# N = Numeric, C=Currency 53TRANSPILE_SAFE_NUMBER_FMT = {"N", "C"} 54 55 56def _format_time_lambda( 57 exp_class: t.Type[E], full_format_mapping: t.Optional[bool] = None 58) -> t.Callable[[t.List], E]: 59 def _format_time(args: t.List) -> E: 60 assert len(args) == 2 61 62 return exp_class( 63 this=args[1], 64 format=exp.Literal.string( 65 format_time( 66 args[0].name, 67 {**TSQL.TIME_MAPPING, **FULL_FORMAT_TIME_MAPPING} 68 if full_format_mapping 69 else TSQL.TIME_MAPPING, 70 ) 71 ), 72 ) 73 74 return _format_time 75 76 77def _parse_format(args: t.List) -> exp.Expression: 78 assert len(args) == 2 79 80 fmt = args[1] 81 number_fmt = fmt.name in TRANSPILE_SAFE_NUMBER_FMT or not DATE_FMT_RE.search(fmt.name) 82 83 if number_fmt: 84 return exp.NumberToStr(this=args[0], format=fmt) 85 86 return exp.TimeToStr( 87 this=args[0], 88 format=exp.Literal.string( 89 format_time(fmt.name, TSQL.FORMAT_TIME_MAPPING) 90 if len(fmt.name) == 1 91 else format_time(fmt.name, TSQL.TIME_MAPPING) 92 ), 93 ) 94 95 96def _parse_eomonth(args: t.List) -> exp.Expression: 97 date = seq_get(args, 0) 98 month_lag = seq_get(args, 1) 99 unit = DATE_DELTA_INTERVAL.get("month") 100 101 if month_lag is None: 102 return exp.LastDateOfMonth(this=date) 103 104 # Remove month lag argument in parser as its compared with the number of arguments of the resulting class 105 args.remove(month_lag) 106 107 return exp.LastDateOfMonth(this=exp.DateAdd(this=date, expression=month_lag, unit=unit)) 108 109 110def _parse_hashbytes(args: t.List) -> exp.Expression: 111 kind, data = args 112 kind = kind.name.upper() if kind.is_string else "" 113 114 if kind == "MD5": 115 args.pop(0) 116 return exp.MD5(this=data) 117 if kind in ("SHA", "SHA1"): 118 args.pop(0) 119 return exp.SHA(this=data) 120 if kind == "SHA2_256": 121 return exp.SHA2(this=data, length=exp.Literal.number(256)) 122 if kind == "SHA2_512": 123 return exp.SHA2(this=data, length=exp.Literal.number(512)) 124 125 return exp.func("HASHBYTES", *args) 126 127 128def generate_date_delta_with_unit_sql( 129 self: generator.Generator, expression: exp.DateAdd | exp.DateDiff 130) -> str: 131 func = "DATEADD" if isinstance(expression, exp.DateAdd) else "DATEDIFF" 132 return self.func(func, expression.text("unit"), expression.expression, expression.this) 133 134 135def _format_sql(self: generator.Generator, expression: exp.NumberToStr | exp.TimeToStr) -> str: 136 fmt = ( 137 expression.args["format"] 138 if isinstance(expression, exp.NumberToStr) 139 else exp.Literal.string( 140 format_time( 141 expression.text("format"), t.cast(t.Dict[str, str], TSQL.INVERSE_TIME_MAPPING) 142 ) 143 ) 144 ) 145 return self.func("FORMAT", expression.this, fmt) 146 147 148def _string_agg_sql(self: generator.Generator, expression: exp.GroupConcat) -> str: 149 expression = expression.copy() 150 151 this = expression.this 152 distinct = expression.find(exp.Distinct) 153 if distinct: 154 # exp.Distinct can appear below an exp.Order or an exp.GroupConcat expression 155 self.unsupported("T-SQL STRING_AGG doesn't support DISTINCT.") 156 this = distinct.pop().expressions[0] 157 158 order = "" 159 if isinstance(expression.this, exp.Order): 160 if expression.this.this: 161 this = expression.this.this.pop() 162 order = f" WITHIN GROUP ({self.sql(expression.this)[1:]})" # Order has a leading space 163 164 separator = expression.args.get("separator") or exp.Literal.string(",") 165 return f"STRING_AGG({self.format_args(this, separator)}){order}" 166 167 168class TSQL(Dialect): 169 NULL_ORDERING = "nulls_are_small" 170 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 171 172 TIME_MAPPING = { 173 "year": "%Y", 174 "qq": "%q", 175 "q": "%q", 176 "quarter": "%q", 177 "dayofyear": "%j", 178 "day": "%d", 179 "dy": "%d", 180 "y": "%Y", 181 "week": "%W", 182 "ww": "%W", 183 "wk": "%W", 184 "hour": "%h", 185 "hh": "%I", 186 "minute": "%M", 187 "mi": "%M", 188 "n": "%M", 189 "second": "%S", 190 "ss": "%S", 191 "s": "%-S", 192 "millisecond": "%f", 193 "ms": "%f", 194 "weekday": "%W", 195 "dw": "%W", 196 "month": "%m", 197 "mm": "%M", 198 "m": "%-M", 199 "Y": "%Y", 200 "YYYY": "%Y", 201 "YY": "%y", 202 "MMMM": "%B", 203 "MMM": "%b", 204 "MM": "%m", 205 "M": "%-m", 206 "dd": "%d", 207 "d": "%-d", 208 "HH": "%H", 209 "H": "%-H", 210 "h": "%-I", 211 "S": "%f", 212 "yyyy": "%Y", 213 "yy": "%y", 214 } 215 216 CONVERT_FORMAT_MAPPING = { 217 "0": "%b %d %Y %-I:%M%p", 218 "1": "%m/%d/%y", 219 "2": "%y.%m.%d", 220 "3": "%d/%m/%y", 221 "4": "%d.%m.%y", 222 "5": "%d-%m-%y", 223 "6": "%d %b %y", 224 "7": "%b %d, %y", 225 "8": "%H:%M:%S", 226 "9": "%b %d %Y %-I:%M:%S:%f%p", 227 "10": "mm-dd-yy", 228 "11": "yy/mm/dd", 229 "12": "yymmdd", 230 "13": "%d %b %Y %H:%M:ss:%f", 231 "14": "%H:%M:%S:%f", 232 "20": "%Y-%m-%d %H:%M:%S", 233 "21": "%Y-%m-%d %H:%M:%S.%f", 234 "22": "%m/%d/%y %-I:%M:%S %p", 235 "23": "%Y-%m-%d", 236 "24": "%H:%M:%S", 237 "25": "%Y-%m-%d %H:%M:%S.%f", 238 "100": "%b %d %Y %-I:%M%p", 239 "101": "%m/%d/%Y", 240 "102": "%Y.%m.%d", 241 "103": "%d/%m/%Y", 242 "104": "%d.%m.%Y", 243 "105": "%d-%m-%Y", 244 "106": "%d %b %Y", 245 "107": "%b %d, %Y", 246 "108": "%H:%M:%S", 247 "109": "%b %d %Y %-I:%M:%S:%f%p", 248 "110": "%m-%d-%Y", 249 "111": "%Y/%m/%d", 250 "112": "%Y%m%d", 251 "113": "%d %b %Y %H:%M:%S:%f", 252 "114": "%H:%M:%S:%f", 253 "120": "%Y-%m-%d %H:%M:%S", 254 "121": "%Y-%m-%d %H:%M:%S.%f", 255 } 256 257 FORMAT_TIME_MAPPING = { 258 "y": "%B %Y", 259 "d": "%m/%d/%Y", 260 "H": "%-H", 261 "h": "%-I", 262 "s": "%Y-%m-%d %H:%M:%S", 263 "D": "%A,%B,%Y", 264 "f": "%A,%B,%Y %-I:%M %p", 265 "F": "%A,%B,%Y %-I:%M:%S %p", 266 "g": "%m/%d/%Y %-I:%M %p", 267 "G": "%m/%d/%Y %-I:%M:%S %p", 268 "M": "%B %-d", 269 "m": "%B %-d", 270 "O": "%Y-%m-%dT%H:%M:%S", 271 "u": "%Y-%M-%D %H:%M:%S%z", 272 "U": "%A, %B %D, %Y %H:%M:%S%z", 273 "T": "%-I:%M:%S %p", 274 "t": "%-I:%M", 275 "Y": "%a %Y", 276 } 277 278 class Tokenizer(tokens.Tokenizer): 279 IDENTIFIERS = ['"', ("[", "]")] 280 QUOTES = ["'", '"'] 281 HEX_STRINGS = [("0x", ""), ("0X", "")] 282 283 KEYWORDS = { 284 **tokens.Tokenizer.KEYWORDS, 285 "DATETIME2": TokenType.DATETIME, 286 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 287 "DECLARE": TokenType.COMMAND, 288 "IMAGE": TokenType.IMAGE, 289 "MONEY": TokenType.MONEY, 290 "NTEXT": TokenType.TEXT, 291 "NVARCHAR(MAX)": TokenType.TEXT, 292 "PRINT": TokenType.COMMAND, 293 "PROC": TokenType.PROCEDURE, 294 "REAL": TokenType.FLOAT, 295 "ROWVERSION": TokenType.ROWVERSION, 296 "SMALLDATETIME": TokenType.DATETIME, 297 "SMALLMONEY": TokenType.SMALLMONEY, 298 "SQL_VARIANT": TokenType.VARIANT, 299 "TIME": TokenType.TIMESTAMP, 300 "TOP": TokenType.TOP, 301 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 302 "VARCHAR(MAX)": TokenType.TEXT, 303 "XML": TokenType.XML, 304 "SYSTEM_USER": TokenType.CURRENT_USER, 305 } 306 307 # TSQL allows @, # to appear as a variable/identifier prefix 308 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 309 SINGLE_TOKENS.pop("#") 310 311 class Parser(parser.Parser): 312 FUNCTIONS = { 313 **parser.Parser.FUNCTIONS, 314 "CHARINDEX": lambda args: exp.StrPosition( 315 this=seq_get(args, 1), substr=seq_get(args, 0), position=seq_get(args, 2) 316 ), 317 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 318 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 319 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 320 "DATEPART": _format_time_lambda(exp.TimeToStr), 321 "EOMONTH": _parse_eomonth, 322 "FORMAT": _parse_format, 323 "GETDATE": exp.CurrentTimestamp.from_arg_list, 324 "HASHBYTES": _parse_hashbytes, 325 "IIF": exp.If.from_arg_list, 326 "ISNULL": exp.Coalesce.from_arg_list, 327 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 328 "LEN": exp.Length.from_arg_list, 329 "REPLICATE": exp.Repeat.from_arg_list, 330 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 331 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 332 "SUSER_NAME": exp.CurrentUser.from_arg_list, 333 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 334 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 335 } 336 337 JOIN_HINTS = { 338 "LOOP", 339 "HASH", 340 "MERGE", 341 "REMOTE", 342 } 343 344 VAR_LENGTH_DATATYPES = { 345 DataType.Type.NVARCHAR, 346 DataType.Type.VARCHAR, 347 DataType.Type.CHAR, 348 DataType.Type.NCHAR, 349 } 350 351 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 352 TokenType.TABLE, 353 *parser.Parser.TYPE_TOKENS, 354 } 355 356 STATEMENT_PARSERS = { 357 **parser.Parser.STATEMENT_PARSERS, 358 TokenType.END: lambda self: self._parse_command(), 359 } 360 361 LOG_BASE_FIRST = False 362 LOG_DEFAULTS_TO_LN = True 363 364 CONCAT_NULL_OUTPUTS_STRING = True 365 366 def _parse_system_time(self) -> t.Optional[exp.Expression]: 367 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 368 return None 369 370 if self._match_text_seq("AS", "OF"): 371 system_time = self.expression( 372 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 373 ) 374 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 375 kind = self._prev.text 376 this = self._parse_bitwise() 377 self._match_texts(("TO", "AND")) 378 expression = self._parse_bitwise() 379 system_time = self.expression( 380 exp.SystemTime, this=this, expression=expression, kind=kind 381 ) 382 elif self._match_text_seq("CONTAINED", "IN"): 383 args = self._parse_wrapped_csv(self._parse_bitwise) 384 system_time = self.expression( 385 exp.SystemTime, 386 this=seq_get(args, 0), 387 expression=seq_get(args, 1), 388 kind="CONTAINED IN", 389 ) 390 elif self._match(TokenType.ALL): 391 system_time = self.expression(exp.SystemTime, kind="ALL") 392 else: 393 system_time = None 394 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 395 396 return system_time 397 398 def _parse_table_parts(self, schema: bool = False) -> exp.Table: 399 table = super()._parse_table_parts(schema=schema) 400 table.set("system_time", self._parse_system_time()) 401 return table 402 403 def _parse_returns(self) -> exp.ReturnsProperty: 404 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 405 returns = super()._parse_returns() 406 returns.set("table", table) 407 return returns 408 409 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 410 to = self._parse_types() 411 self._match(TokenType.COMMA) 412 this = self._parse_conjunction() 413 414 if not to or not this: 415 return None 416 417 # Retrieve length of datatype and override to default if not specified 418 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 419 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 420 421 # Check whether a conversion with format is applicable 422 if self._match(TokenType.COMMA): 423 format_val = self._parse_number() 424 format_val_name = format_val.name if format_val else "" 425 426 if format_val_name not in TSQL.CONVERT_FORMAT_MAPPING: 427 raise ValueError( 428 f"CONVERT function at T-SQL does not support format style {format_val_name}" 429 ) 430 431 format_norm = exp.Literal.string(TSQL.CONVERT_FORMAT_MAPPING[format_val_name]) 432 433 # Check whether the convert entails a string to date format 434 if to.this == DataType.Type.DATE: 435 return self.expression(exp.StrToDate, this=this, format=format_norm) 436 # Check whether the convert entails a string to datetime format 437 elif to.this == DataType.Type.DATETIME: 438 return self.expression(exp.StrToTime, this=this, format=format_norm) 439 # Check whether the convert entails a date to string format 440 elif to.this in self.VAR_LENGTH_DATATYPES: 441 return self.expression( 442 exp.Cast if strict else exp.TryCast, 443 to=to, 444 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 445 ) 446 elif to.this == DataType.Type.TEXT: 447 return self.expression(exp.TimeToStr, this=this, format=format_norm) 448 449 # Entails a simple cast without any format requirement 450 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 451 452 def _parse_user_defined_function( 453 self, kind: t.Optional[TokenType] = None 454 ) -> t.Optional[exp.Expression]: 455 this = super()._parse_user_defined_function(kind=kind) 456 457 if ( 458 kind == TokenType.FUNCTION 459 or isinstance(this, exp.UserDefinedFunction) 460 or self._match(TokenType.ALIAS, advance=False) 461 ): 462 return this 463 464 expressions = self._parse_csv(self._parse_function_parameter) 465 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 466 467 class Generator(generator.Generator): 468 LOCKING_READS_SUPPORTED = True 469 470 TYPE_MAPPING = { 471 **generator.Generator.TYPE_MAPPING, 472 exp.DataType.Type.INT: "INTEGER", 473 exp.DataType.Type.DECIMAL: "NUMERIC", 474 exp.DataType.Type.DATETIME: "DATETIME2", 475 exp.DataType.Type.VARIANT: "SQL_VARIANT", 476 } 477 478 TRANSFORMS = { 479 **generator.Generator.TRANSFORMS, 480 exp.DateAdd: generate_date_delta_with_unit_sql, 481 exp.DateDiff: generate_date_delta_with_unit_sql, 482 exp.CurrentDate: rename_func("GETDATE"), 483 exp.CurrentTimestamp: rename_func("GETDATE"), 484 exp.GroupConcat: _string_agg_sql, 485 exp.If: rename_func("IIF"), 486 exp.Max: max_or_greatest, 487 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 488 exp.Min: min_or_least, 489 exp.NumberToStr: _format_sql, 490 exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]), 491 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 492 exp.SHA2: lambda self, e: self.func( 493 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 494 ), 495 exp.TimeToStr: _format_sql, 496 } 497 498 TRANSFORMS.pop(exp.ReturnsProperty) 499 500 PROPERTIES_LOCATION = { 501 **generator.Generator.PROPERTIES_LOCATION, 502 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 503 } 504 505 LIMIT_FETCH = "FETCH" 506 507 def offset_sql(self, expression: exp.Offset) -> str: 508 return f"{super().offset_sql(expression)} ROWS" 509 510 def systemtime_sql(self, expression: exp.SystemTime) -> str: 511 kind = expression.args["kind"] 512 if kind == "ALL": 513 return "FOR SYSTEM_TIME ALL" 514 515 start = self.sql(expression, "this") 516 if kind == "AS OF": 517 return f"FOR SYSTEM_TIME AS OF {start}" 518 519 end = self.sql(expression, "expression") 520 if kind == "FROM": 521 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 522 if kind == "BETWEEN": 523 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 524 525 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 526 527 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 528 table = expression.args.get("table") 529 table = f"{table} " if table else "" 530 return f"RETURNS {table}{self.sql(expression, 'this')}"
def
generate_date_delta_with_unit_sql( self: sqlglot.generator.Generator, expression: sqlglot.expressions.DateAdd | sqlglot.expressions.DateDiff) -> str:
169class TSQL(Dialect): 170 NULL_ORDERING = "nulls_are_small" 171 TIME_FORMAT = "'yyyy-mm-dd hh:mm:ss'" 172 173 TIME_MAPPING = { 174 "year": "%Y", 175 "qq": "%q", 176 "q": "%q", 177 "quarter": "%q", 178 "dayofyear": "%j", 179 "day": "%d", 180 "dy": "%d", 181 "y": "%Y", 182 "week": "%W", 183 "ww": "%W", 184 "wk": "%W", 185 "hour": "%h", 186 "hh": "%I", 187 "minute": "%M", 188 "mi": "%M", 189 "n": "%M", 190 "second": "%S", 191 "ss": "%S", 192 "s": "%-S", 193 "millisecond": "%f", 194 "ms": "%f", 195 "weekday": "%W", 196 "dw": "%W", 197 "month": "%m", 198 "mm": "%M", 199 "m": "%-M", 200 "Y": "%Y", 201 "YYYY": "%Y", 202 "YY": "%y", 203 "MMMM": "%B", 204 "MMM": "%b", 205 "MM": "%m", 206 "M": "%-m", 207 "dd": "%d", 208 "d": "%-d", 209 "HH": "%H", 210 "H": "%-H", 211 "h": "%-I", 212 "S": "%f", 213 "yyyy": "%Y", 214 "yy": "%y", 215 } 216 217 CONVERT_FORMAT_MAPPING = { 218 "0": "%b %d %Y %-I:%M%p", 219 "1": "%m/%d/%y", 220 "2": "%y.%m.%d", 221 "3": "%d/%m/%y", 222 "4": "%d.%m.%y", 223 "5": "%d-%m-%y", 224 "6": "%d %b %y", 225 "7": "%b %d, %y", 226 "8": "%H:%M:%S", 227 "9": "%b %d %Y %-I:%M:%S:%f%p", 228 "10": "mm-dd-yy", 229 "11": "yy/mm/dd", 230 "12": "yymmdd", 231 "13": "%d %b %Y %H:%M:ss:%f", 232 "14": "%H:%M:%S:%f", 233 "20": "%Y-%m-%d %H:%M:%S", 234 "21": "%Y-%m-%d %H:%M:%S.%f", 235 "22": "%m/%d/%y %-I:%M:%S %p", 236 "23": "%Y-%m-%d", 237 "24": "%H:%M:%S", 238 "25": "%Y-%m-%d %H:%M:%S.%f", 239 "100": "%b %d %Y %-I:%M%p", 240 "101": "%m/%d/%Y", 241 "102": "%Y.%m.%d", 242 "103": "%d/%m/%Y", 243 "104": "%d.%m.%Y", 244 "105": "%d-%m-%Y", 245 "106": "%d %b %Y", 246 "107": "%b %d, %Y", 247 "108": "%H:%M:%S", 248 "109": "%b %d %Y %-I:%M:%S:%f%p", 249 "110": "%m-%d-%Y", 250 "111": "%Y/%m/%d", 251 "112": "%Y%m%d", 252 "113": "%d %b %Y %H:%M:%S:%f", 253 "114": "%H:%M:%S:%f", 254 "120": "%Y-%m-%d %H:%M:%S", 255 "121": "%Y-%m-%d %H:%M:%S.%f", 256 } 257 258 FORMAT_TIME_MAPPING = { 259 "y": "%B %Y", 260 "d": "%m/%d/%Y", 261 "H": "%-H", 262 "h": "%-I", 263 "s": "%Y-%m-%d %H:%M:%S", 264 "D": "%A,%B,%Y", 265 "f": "%A,%B,%Y %-I:%M %p", 266 "F": "%A,%B,%Y %-I:%M:%S %p", 267 "g": "%m/%d/%Y %-I:%M %p", 268 "G": "%m/%d/%Y %-I:%M:%S %p", 269 "M": "%B %-d", 270 "m": "%B %-d", 271 "O": "%Y-%m-%dT%H:%M:%S", 272 "u": "%Y-%M-%D %H:%M:%S%z", 273 "U": "%A, %B %D, %Y %H:%M:%S%z", 274 "T": "%-I:%M:%S %p", 275 "t": "%-I:%M", 276 "Y": "%a %Y", 277 } 278 279 class Tokenizer(tokens.Tokenizer): 280 IDENTIFIERS = ['"', ("[", "]")] 281 QUOTES = ["'", '"'] 282 HEX_STRINGS = [("0x", ""), ("0X", "")] 283 284 KEYWORDS = { 285 **tokens.Tokenizer.KEYWORDS, 286 "DATETIME2": TokenType.DATETIME, 287 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 288 "DECLARE": TokenType.COMMAND, 289 "IMAGE": TokenType.IMAGE, 290 "MONEY": TokenType.MONEY, 291 "NTEXT": TokenType.TEXT, 292 "NVARCHAR(MAX)": TokenType.TEXT, 293 "PRINT": TokenType.COMMAND, 294 "PROC": TokenType.PROCEDURE, 295 "REAL": TokenType.FLOAT, 296 "ROWVERSION": TokenType.ROWVERSION, 297 "SMALLDATETIME": TokenType.DATETIME, 298 "SMALLMONEY": TokenType.SMALLMONEY, 299 "SQL_VARIANT": TokenType.VARIANT, 300 "TIME": TokenType.TIMESTAMP, 301 "TOP": TokenType.TOP, 302 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 303 "VARCHAR(MAX)": TokenType.TEXT, 304 "XML": TokenType.XML, 305 "SYSTEM_USER": TokenType.CURRENT_USER, 306 } 307 308 # TSQL allows @, # to appear as a variable/identifier prefix 309 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 310 SINGLE_TOKENS.pop("#") 311 312 class Parser(parser.Parser): 313 FUNCTIONS = { 314 **parser.Parser.FUNCTIONS, 315 "CHARINDEX": lambda args: exp.StrPosition( 316 this=seq_get(args, 1), substr=seq_get(args, 0), position=seq_get(args, 2) 317 ), 318 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 319 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 320 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 321 "DATEPART": _format_time_lambda(exp.TimeToStr), 322 "EOMONTH": _parse_eomonth, 323 "FORMAT": _parse_format, 324 "GETDATE": exp.CurrentTimestamp.from_arg_list, 325 "HASHBYTES": _parse_hashbytes, 326 "IIF": exp.If.from_arg_list, 327 "ISNULL": exp.Coalesce.from_arg_list, 328 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 329 "LEN": exp.Length.from_arg_list, 330 "REPLICATE": exp.Repeat.from_arg_list, 331 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 332 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 333 "SUSER_NAME": exp.CurrentUser.from_arg_list, 334 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 335 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 336 } 337 338 JOIN_HINTS = { 339 "LOOP", 340 "HASH", 341 "MERGE", 342 "REMOTE", 343 } 344 345 VAR_LENGTH_DATATYPES = { 346 DataType.Type.NVARCHAR, 347 DataType.Type.VARCHAR, 348 DataType.Type.CHAR, 349 DataType.Type.NCHAR, 350 } 351 352 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 353 TokenType.TABLE, 354 *parser.Parser.TYPE_TOKENS, 355 } 356 357 STATEMENT_PARSERS = { 358 **parser.Parser.STATEMENT_PARSERS, 359 TokenType.END: lambda self: self._parse_command(), 360 } 361 362 LOG_BASE_FIRST = False 363 LOG_DEFAULTS_TO_LN = True 364 365 CONCAT_NULL_OUTPUTS_STRING = True 366 367 def _parse_system_time(self) -> t.Optional[exp.Expression]: 368 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 369 return None 370 371 if self._match_text_seq("AS", "OF"): 372 system_time = self.expression( 373 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 374 ) 375 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 376 kind = self._prev.text 377 this = self._parse_bitwise() 378 self._match_texts(("TO", "AND")) 379 expression = self._parse_bitwise() 380 system_time = self.expression( 381 exp.SystemTime, this=this, expression=expression, kind=kind 382 ) 383 elif self._match_text_seq("CONTAINED", "IN"): 384 args = self._parse_wrapped_csv(self._parse_bitwise) 385 system_time = self.expression( 386 exp.SystemTime, 387 this=seq_get(args, 0), 388 expression=seq_get(args, 1), 389 kind="CONTAINED IN", 390 ) 391 elif self._match(TokenType.ALL): 392 system_time = self.expression(exp.SystemTime, kind="ALL") 393 else: 394 system_time = None 395 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 396 397 return system_time 398 399 def _parse_table_parts(self, schema: bool = False) -> exp.Table: 400 table = super()._parse_table_parts(schema=schema) 401 table.set("system_time", self._parse_system_time()) 402 return table 403 404 def _parse_returns(self) -> exp.ReturnsProperty: 405 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 406 returns = super()._parse_returns() 407 returns.set("table", table) 408 return returns 409 410 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 411 to = self._parse_types() 412 self._match(TokenType.COMMA) 413 this = self._parse_conjunction() 414 415 if not to or not this: 416 return None 417 418 # Retrieve length of datatype and override to default if not specified 419 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 420 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 421 422 # Check whether a conversion with format is applicable 423 if self._match(TokenType.COMMA): 424 format_val = self._parse_number() 425 format_val_name = format_val.name if format_val else "" 426 427 if format_val_name not in TSQL.CONVERT_FORMAT_MAPPING: 428 raise ValueError( 429 f"CONVERT function at T-SQL does not support format style {format_val_name}" 430 ) 431 432 format_norm = exp.Literal.string(TSQL.CONVERT_FORMAT_MAPPING[format_val_name]) 433 434 # Check whether the convert entails a string to date format 435 if to.this == DataType.Type.DATE: 436 return self.expression(exp.StrToDate, this=this, format=format_norm) 437 # Check whether the convert entails a string to datetime format 438 elif to.this == DataType.Type.DATETIME: 439 return self.expression(exp.StrToTime, this=this, format=format_norm) 440 # Check whether the convert entails a date to string format 441 elif to.this in self.VAR_LENGTH_DATATYPES: 442 return self.expression( 443 exp.Cast if strict else exp.TryCast, 444 to=to, 445 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 446 ) 447 elif to.this == DataType.Type.TEXT: 448 return self.expression(exp.TimeToStr, this=this, format=format_norm) 449 450 # Entails a simple cast without any format requirement 451 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 452 453 def _parse_user_defined_function( 454 self, kind: t.Optional[TokenType] = None 455 ) -> t.Optional[exp.Expression]: 456 this = super()._parse_user_defined_function(kind=kind) 457 458 if ( 459 kind == TokenType.FUNCTION 460 or isinstance(this, exp.UserDefinedFunction) 461 or self._match(TokenType.ALIAS, advance=False) 462 ): 463 return this 464 465 expressions = self._parse_csv(self._parse_function_parameter) 466 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions) 467 468 class Generator(generator.Generator): 469 LOCKING_READS_SUPPORTED = True 470 471 TYPE_MAPPING = { 472 **generator.Generator.TYPE_MAPPING, 473 exp.DataType.Type.INT: "INTEGER", 474 exp.DataType.Type.DECIMAL: "NUMERIC", 475 exp.DataType.Type.DATETIME: "DATETIME2", 476 exp.DataType.Type.VARIANT: "SQL_VARIANT", 477 } 478 479 TRANSFORMS = { 480 **generator.Generator.TRANSFORMS, 481 exp.DateAdd: generate_date_delta_with_unit_sql, 482 exp.DateDiff: generate_date_delta_with_unit_sql, 483 exp.CurrentDate: rename_func("GETDATE"), 484 exp.CurrentTimestamp: rename_func("GETDATE"), 485 exp.GroupConcat: _string_agg_sql, 486 exp.If: rename_func("IIF"), 487 exp.Max: max_or_greatest, 488 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 489 exp.Min: min_or_least, 490 exp.NumberToStr: _format_sql, 491 exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]), 492 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 493 exp.SHA2: lambda self, e: self.func( 494 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 495 ), 496 exp.TimeToStr: _format_sql, 497 } 498 499 TRANSFORMS.pop(exp.ReturnsProperty) 500 501 PROPERTIES_LOCATION = { 502 **generator.Generator.PROPERTIES_LOCATION, 503 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 504 } 505 506 LIMIT_FETCH = "FETCH" 507 508 def offset_sql(self, expression: exp.Offset) -> str: 509 return f"{super().offset_sql(expression)} ROWS" 510 511 def systemtime_sql(self, expression: exp.SystemTime) -> str: 512 kind = expression.args["kind"] 513 if kind == "ALL": 514 return "FOR SYSTEM_TIME ALL" 515 516 start = self.sql(expression, "this") 517 if kind == "AS OF": 518 return f"FOR SYSTEM_TIME AS OF {start}" 519 520 end = self.sql(expression, "expression") 521 if kind == "FROM": 522 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 523 if kind == "BETWEEN": 524 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 525 526 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 527 528 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 529 table = expression.args.get("table") 530 table = f"{table} " if table else "" 531 return f"RETURNS {table}{self.sql(expression, 'this')}"
279 class Tokenizer(tokens.Tokenizer): 280 IDENTIFIERS = ['"', ("[", "]")] 281 QUOTES = ["'", '"'] 282 HEX_STRINGS = [("0x", ""), ("0X", "")] 283 284 KEYWORDS = { 285 **tokens.Tokenizer.KEYWORDS, 286 "DATETIME2": TokenType.DATETIME, 287 "DATETIMEOFFSET": TokenType.TIMESTAMPTZ, 288 "DECLARE": TokenType.COMMAND, 289 "IMAGE": TokenType.IMAGE, 290 "MONEY": TokenType.MONEY, 291 "NTEXT": TokenType.TEXT, 292 "NVARCHAR(MAX)": TokenType.TEXT, 293 "PRINT": TokenType.COMMAND, 294 "PROC": TokenType.PROCEDURE, 295 "REAL": TokenType.FLOAT, 296 "ROWVERSION": TokenType.ROWVERSION, 297 "SMALLDATETIME": TokenType.DATETIME, 298 "SMALLMONEY": TokenType.SMALLMONEY, 299 "SQL_VARIANT": TokenType.VARIANT, 300 "TIME": TokenType.TIMESTAMP, 301 "TOP": TokenType.TOP, 302 "UNIQUEIDENTIFIER": TokenType.UNIQUEIDENTIFIER, 303 "VARCHAR(MAX)": TokenType.TEXT, 304 "XML": TokenType.XML, 305 "SYSTEM_USER": TokenType.CURRENT_USER, 306 } 307 308 # TSQL allows @, # to appear as a variable/identifier prefix 309 SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy() 310 SINGLE_TOKENS.pop("#")
Inherited Members
312 class Parser(parser.Parser): 313 FUNCTIONS = { 314 **parser.Parser.FUNCTIONS, 315 "CHARINDEX": lambda args: exp.StrPosition( 316 this=seq_get(args, 1), substr=seq_get(args, 0), position=seq_get(args, 2) 317 ), 318 "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), 319 "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), 320 "DATENAME": _format_time_lambda(exp.TimeToStr, full_format_mapping=True), 321 "DATEPART": _format_time_lambda(exp.TimeToStr), 322 "EOMONTH": _parse_eomonth, 323 "FORMAT": _parse_format, 324 "GETDATE": exp.CurrentTimestamp.from_arg_list, 325 "HASHBYTES": _parse_hashbytes, 326 "IIF": exp.If.from_arg_list, 327 "ISNULL": exp.Coalesce.from_arg_list, 328 "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, 329 "LEN": exp.Length.from_arg_list, 330 "REPLICATE": exp.Repeat.from_arg_list, 331 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 332 "SYSDATETIME": exp.CurrentTimestamp.from_arg_list, 333 "SUSER_NAME": exp.CurrentUser.from_arg_list, 334 "SUSER_SNAME": exp.CurrentUser.from_arg_list, 335 "SYSTEM_USER": exp.CurrentUser.from_arg_list, 336 } 337 338 JOIN_HINTS = { 339 "LOOP", 340 "HASH", 341 "MERGE", 342 "REMOTE", 343 } 344 345 VAR_LENGTH_DATATYPES = { 346 DataType.Type.NVARCHAR, 347 DataType.Type.VARCHAR, 348 DataType.Type.CHAR, 349 DataType.Type.NCHAR, 350 } 351 352 RETURNS_TABLE_TOKENS = parser.Parser.ID_VAR_TOKENS - { 353 TokenType.TABLE, 354 *parser.Parser.TYPE_TOKENS, 355 } 356 357 STATEMENT_PARSERS = { 358 **parser.Parser.STATEMENT_PARSERS, 359 TokenType.END: lambda self: self._parse_command(), 360 } 361 362 LOG_BASE_FIRST = False 363 LOG_DEFAULTS_TO_LN = True 364 365 CONCAT_NULL_OUTPUTS_STRING = True 366 367 def _parse_system_time(self) -> t.Optional[exp.Expression]: 368 if not self._match_text_seq("FOR", "SYSTEM_TIME"): 369 return None 370 371 if self._match_text_seq("AS", "OF"): 372 system_time = self.expression( 373 exp.SystemTime, this=self._parse_bitwise(), kind="AS OF" 374 ) 375 elif self._match_set((TokenType.FROM, TokenType.BETWEEN)): 376 kind = self._prev.text 377 this = self._parse_bitwise() 378 self._match_texts(("TO", "AND")) 379 expression = self._parse_bitwise() 380 system_time = self.expression( 381 exp.SystemTime, this=this, expression=expression, kind=kind 382 ) 383 elif self._match_text_seq("CONTAINED", "IN"): 384 args = self._parse_wrapped_csv(self._parse_bitwise) 385 system_time = self.expression( 386 exp.SystemTime, 387 this=seq_get(args, 0), 388 expression=seq_get(args, 1), 389 kind="CONTAINED IN", 390 ) 391 elif self._match(TokenType.ALL): 392 system_time = self.expression(exp.SystemTime, kind="ALL") 393 else: 394 system_time = None 395 self.raise_error("Unable to parse FOR SYSTEM_TIME clause") 396 397 return system_time 398 399 def _parse_table_parts(self, schema: bool = False) -> exp.Table: 400 table = super()._parse_table_parts(schema=schema) 401 table.set("system_time", self._parse_system_time()) 402 return table 403 404 def _parse_returns(self) -> exp.ReturnsProperty: 405 table = self._parse_id_var(any_token=False, tokens=self.RETURNS_TABLE_TOKENS) 406 returns = super()._parse_returns() 407 returns.set("table", table) 408 return returns 409 410 def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]: 411 to = self._parse_types() 412 self._match(TokenType.COMMA) 413 this = self._parse_conjunction() 414 415 if not to or not this: 416 return None 417 418 # Retrieve length of datatype and override to default if not specified 419 if seq_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: 420 to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False) 421 422 # Check whether a conversion with format is applicable 423 if self._match(TokenType.COMMA): 424 format_val = self._parse_number() 425 format_val_name = format_val.name if format_val else "" 426 427 if format_val_name not in TSQL.CONVERT_FORMAT_MAPPING: 428 raise ValueError( 429 f"CONVERT function at T-SQL does not support format style {format_val_name}" 430 ) 431 432 format_norm = exp.Literal.string(TSQL.CONVERT_FORMAT_MAPPING[format_val_name]) 433 434 # Check whether the convert entails a string to date format 435 if to.this == DataType.Type.DATE: 436 return self.expression(exp.StrToDate, this=this, format=format_norm) 437 # Check whether the convert entails a string to datetime format 438 elif to.this == DataType.Type.DATETIME: 439 return self.expression(exp.StrToTime, this=this, format=format_norm) 440 # Check whether the convert entails a date to string format 441 elif to.this in self.VAR_LENGTH_DATATYPES: 442 return self.expression( 443 exp.Cast if strict else exp.TryCast, 444 to=to, 445 this=self.expression(exp.TimeToStr, this=this, format=format_norm), 446 ) 447 elif to.this == DataType.Type.TEXT: 448 return self.expression(exp.TimeToStr, this=this, format=format_norm) 449 450 # Entails a simple cast without any format requirement 451 return self.expression(exp.Cast if strict else exp.TryCast, this=this, to=to) 452 453 def _parse_user_defined_function( 454 self, kind: t.Optional[TokenType] = None 455 ) -> t.Optional[exp.Expression]: 456 this = super()._parse_user_defined_function(kind=kind) 457 458 if ( 459 kind == TokenType.FUNCTION 460 or isinstance(this, exp.UserDefinedFunction) 461 or self._match(TokenType.ALIAS, advance=False) 462 ): 463 return this 464 465 expressions = self._parse_csv(self._parse_function_parameter) 466 return self.expression(exp.UserDefinedFunction, this=this, expressions=expressions)
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: Determines 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
468 class Generator(generator.Generator): 469 LOCKING_READS_SUPPORTED = True 470 471 TYPE_MAPPING = { 472 **generator.Generator.TYPE_MAPPING, 473 exp.DataType.Type.INT: "INTEGER", 474 exp.DataType.Type.DECIMAL: "NUMERIC", 475 exp.DataType.Type.DATETIME: "DATETIME2", 476 exp.DataType.Type.VARIANT: "SQL_VARIANT", 477 } 478 479 TRANSFORMS = { 480 **generator.Generator.TRANSFORMS, 481 exp.DateAdd: generate_date_delta_with_unit_sql, 482 exp.DateDiff: generate_date_delta_with_unit_sql, 483 exp.CurrentDate: rename_func("GETDATE"), 484 exp.CurrentTimestamp: rename_func("GETDATE"), 485 exp.GroupConcat: _string_agg_sql, 486 exp.If: rename_func("IIF"), 487 exp.Max: max_or_greatest, 488 exp.MD5: lambda self, e: self.func("HASHBYTES", exp.Literal.string("MD5"), e.this), 489 exp.Min: min_or_least, 490 exp.NumberToStr: _format_sql, 491 exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]), 492 exp.SHA: lambda self, e: self.func("HASHBYTES", exp.Literal.string("SHA1"), e.this), 493 exp.SHA2: lambda self, e: self.func( 494 "HASHBYTES", exp.Literal.string(f"SHA2_{e.args.get('length', 256)}"), e.this 495 ), 496 exp.TimeToStr: _format_sql, 497 } 498 499 TRANSFORMS.pop(exp.ReturnsProperty) 500 501 PROPERTIES_LOCATION = { 502 **generator.Generator.PROPERTIES_LOCATION, 503 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 504 } 505 506 LIMIT_FETCH = "FETCH" 507 508 def offset_sql(self, expression: exp.Offset) -> str: 509 return f"{super().offset_sql(expression)} ROWS" 510 511 def systemtime_sql(self, expression: exp.SystemTime) -> str: 512 kind = expression.args["kind"] 513 if kind == "ALL": 514 return "FOR SYSTEM_TIME ALL" 515 516 start = self.sql(expression, "this") 517 if kind == "AS OF": 518 return f"FOR SYSTEM_TIME AS OF {start}" 519 520 end = self.sql(expression, "expression") 521 if kind == "FROM": 522 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 523 if kind == "BETWEEN": 524 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 525 526 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})" 527 528 def returnsproperty_sql(self, expression: exp.ReturnsProperty) -> str: 529 table = expression.args.get("table") 530 table = f"{table} " if table else "" 531 return f"RETURNS {table}{self.sql(expression, 'this')}"
Generator converts a given syntax tree to the corresponding SQL string.
Arguments:
- pretty: Whether or not 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 or not to normalize identifiers to lowercase. Default: False.
- pad: Determines the pad size in a formatted string. Default: 2.
- indent: Determines the indentation size in a formatted string. Default: 2.
- normalize_functions: Whether or not to normalize all 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: Determines whether or not 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 or not to preserve comments in the output SQL code. Default: True
511 def systemtime_sql(self, expression: exp.SystemTime) -> str: 512 kind = expression.args["kind"] 513 if kind == "ALL": 514 return "FOR SYSTEM_TIME ALL" 515 516 start = self.sql(expression, "this") 517 if kind == "AS OF": 518 return f"FOR SYSTEM_TIME AS OF {start}" 519 520 end = self.sql(expression, "expression") 521 if kind == "FROM": 522 return f"FOR SYSTEM_TIME FROM {start} TO {end}" 523 if kind == "BETWEEN": 524 return f"FOR SYSTEM_TIME BETWEEN {start} AND {end}" 525 526 return f"FOR SYSTEM_TIME CONTAINED IN ({start}, {end})"
@classmethod
def
can_identify(text: str, identify: str | bool = 'safe') -> bool:
247 @classmethod 248 def can_identify(cls, text: str, identify: str | bool = "safe") -> bool: 249 """Checks if text can be identified given an identify option. 250 251 Args: 252 text: The text to check. 253 identify: 254 "always" or `True`: Always returns true. 255 "safe": True if the identifier is case-insensitive. 256 257 Returns: 258 Whether or not the given text can be identified. 259 """ 260 if identify is True or identify == "always": 261 return True 262 263 if identify == "safe": 264 return not cls.case_sensitive(text) 265 266 return False
Checks if text can be identified given an identify option.
Arguments:
- text: The text to check.
- identify: "always" or
True: Always returns true. "safe": True if the identifier is case-insensitive.
Returns:
Whether or not the given text can be identified.
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
- createable_sql
- create_sql
- clone_sql
- describe_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- rawstring_sql
- datatypesize_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
- 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
- escape_str
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognize_sql
- query_modifiers
- offset_limit_modifiers
- after_having_modifiers
- after_limit_modifiers
- select_sql
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- union_sql
- union_op
- unnest_sql
- where_sql
- window_sql
- partition_by_sql
- windowspec_sql
- withingroup_sql
- between_sql
- bracket_sql
- all_sql
- any_sql
- exists_sql
- case_sql
- constraint_sql
- nextvaluefor_sql
- extract_sql
- trim_sql
- safeconcat_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- if_sql
- matchagainst_sql
- jsonkeyvalue_sql
- jsonobject_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
- 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
- mergetreettlaction_sql
- mergetreettl_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
- safedpipe_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
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- oncluster_sql