sqlglot.dialects.mysql
1from __future__ import annotations 2 3from sqlglot import exp, generator, parser, tokens 4from sqlglot.dialects.dialect import ( 5 Dialect, 6 locate_to_strposition, 7 no_ilike_sql, 8 no_paren_current_date_sql, 9 no_tablesample_sql, 10 no_trycast_sql, 11 strposition_to_locate_sql, 12) 13from sqlglot.helper import seq_get 14from sqlglot.tokens import TokenType 15 16 17def _show_parser(*args, **kwargs): 18 def _parse(self): 19 return self._parse_show_mysql(*args, **kwargs) 20 21 return _parse 22 23 24def _date_trunc_sql(self, expression): 25 expr = self.sql(expression, "this") 26 unit = expression.text("unit") 27 28 if unit == "day": 29 return f"DATE({expr})" 30 31 if unit == "week": 32 concat = f"CONCAT(YEAR({expr}), ' ', WEEK({expr}, 1), ' 1')" 33 date_format = "%Y %u %w" 34 elif unit == "month": 35 concat = f"CONCAT(YEAR({expr}), ' ', MONTH({expr}), ' 1')" 36 date_format = "%Y %c %e" 37 elif unit == "quarter": 38 concat = f"CONCAT(YEAR({expr}), ' ', QUARTER({expr}) * 3 - 2, ' 1')" 39 date_format = "%Y %c %e" 40 elif unit == "year": 41 concat = f"CONCAT(YEAR({expr}), ' 1 1')" 42 date_format = "%Y %c %e" 43 else: 44 self.unsupported(f"Unexpected interval unit: {unit}") 45 return f"DATE({expr})" 46 47 return f"STR_TO_DATE({concat}, '{date_format}')" 48 49 50def _str_to_date(args): 51 date_format = MySQL.format_time(seq_get(args, 1)) 52 return exp.StrToDate(this=seq_get(args, 0), format=date_format) 53 54 55def _str_to_date_sql(self, expression): 56 date_format = self.format_time(expression) 57 return f"STR_TO_DATE({self.sql(expression.this)}, {date_format})" 58 59 60def _trim_sql(self, expression): 61 target = self.sql(expression, "this") 62 trim_type = self.sql(expression, "position") 63 remove_chars = self.sql(expression, "expression") 64 65 # Use TRIM/LTRIM/RTRIM syntax if the expression isn't mysql-specific 66 if not remove_chars: 67 return self.trim_sql(expression) 68 69 trim_type = f"{trim_type} " if trim_type else "" 70 remove_chars = f"{remove_chars} " if remove_chars else "" 71 from_part = "FROM " if trim_type or remove_chars else "" 72 return f"TRIM({trim_type}{remove_chars}{from_part}{target})" 73 74 75def _date_add(expression_class): 76 def func(args): 77 interval = seq_get(args, 1) 78 return expression_class( 79 this=seq_get(args, 0), 80 expression=interval.this, 81 unit=exp.Literal.string(interval.text("unit").lower()), 82 ) 83 84 return func 85 86 87def _date_add_sql(kind): 88 def func(self, expression): 89 this = self.sql(expression, "this") 90 unit = expression.text("unit").upper() or "DAY" 91 return ( 92 f"DATE_{kind}({this}, {self.sql(exp.Interval(this=expression.expression, unit=unit))})" 93 ) 94 95 return func 96 97 98class MySQL(Dialect): 99 time_format = "'%Y-%m-%d %T'" 100 101 # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions 102 time_mapping = { 103 "%M": "%B", 104 "%c": "%-m", 105 "%e": "%-d", 106 "%h": "%I", 107 "%i": "%M", 108 "%s": "%S", 109 "%S": "%S", 110 "%u": "%W", 111 "%k": "%-H", 112 "%l": "%-I", 113 "%T": "%H:%M:%S", 114 } 115 116 class Tokenizer(tokens.Tokenizer): 117 QUOTES = ["'", '"'] 118 COMMENTS = ["--", "#", ("/*", "*/")] 119 IDENTIFIERS = ["`"] 120 STRING_ESCAPES = ["'", "\\"] 121 BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")] 122 HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")] 123 124 KEYWORDS = { 125 **tokens.Tokenizer.KEYWORDS, 126 "MEDIUMTEXT": TokenType.MEDIUMTEXT, 127 "LONGTEXT": TokenType.LONGTEXT, 128 "MEDIUMBLOB": TokenType.MEDIUMBLOB, 129 "LONGBLOB": TokenType.LONGBLOB, 130 "START": TokenType.BEGIN, 131 "SEPARATOR": TokenType.SEPARATOR, 132 "_ARMSCII8": TokenType.INTRODUCER, 133 "_ASCII": TokenType.INTRODUCER, 134 "_BIG5": TokenType.INTRODUCER, 135 "_BINARY": TokenType.INTRODUCER, 136 "_CP1250": TokenType.INTRODUCER, 137 "_CP1251": TokenType.INTRODUCER, 138 "_CP1256": TokenType.INTRODUCER, 139 "_CP1257": TokenType.INTRODUCER, 140 "_CP850": TokenType.INTRODUCER, 141 "_CP852": TokenType.INTRODUCER, 142 "_CP866": TokenType.INTRODUCER, 143 "_CP932": TokenType.INTRODUCER, 144 "_DEC8": TokenType.INTRODUCER, 145 "_EUCJPMS": TokenType.INTRODUCER, 146 "_EUCKR": TokenType.INTRODUCER, 147 "_GB18030": TokenType.INTRODUCER, 148 "_GB2312": TokenType.INTRODUCER, 149 "_GBK": TokenType.INTRODUCER, 150 "_GEOSTD8": TokenType.INTRODUCER, 151 "_GREEK": TokenType.INTRODUCER, 152 "_HEBREW": TokenType.INTRODUCER, 153 "_HP8": TokenType.INTRODUCER, 154 "_KEYBCS2": TokenType.INTRODUCER, 155 "_KOI8R": TokenType.INTRODUCER, 156 "_KOI8U": TokenType.INTRODUCER, 157 "_LATIN1": TokenType.INTRODUCER, 158 "_LATIN2": TokenType.INTRODUCER, 159 "_LATIN5": TokenType.INTRODUCER, 160 "_LATIN7": TokenType.INTRODUCER, 161 "_MACCE": TokenType.INTRODUCER, 162 "_MACROMAN": TokenType.INTRODUCER, 163 "_SJIS": TokenType.INTRODUCER, 164 "_SWE7": TokenType.INTRODUCER, 165 "_TIS620": TokenType.INTRODUCER, 166 "_UCS2": TokenType.INTRODUCER, 167 "_UJIS": TokenType.INTRODUCER, 168 # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html 169 "_UTF8": TokenType.INTRODUCER, 170 "_UTF16": TokenType.INTRODUCER, 171 "_UTF16LE": TokenType.INTRODUCER, 172 "_UTF32": TokenType.INTRODUCER, 173 "_UTF8MB3": TokenType.INTRODUCER, 174 "_UTF8MB4": TokenType.INTRODUCER, 175 "@@": TokenType.SESSION_PARAMETER, 176 } 177 178 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SET, TokenType.SHOW} 179 180 class Parser(parser.Parser): 181 FUNC_TOKENS = {*parser.Parser.FUNC_TOKENS, TokenType.SCHEMA} # type: ignore 182 183 FUNCTIONS = { 184 **parser.Parser.FUNCTIONS, # type: ignore 185 "DATE_ADD": _date_add(exp.DateAdd), 186 "DATE_SUB": _date_add(exp.DateSub), 187 "STR_TO_DATE": _str_to_date, 188 "LOCATE": locate_to_strposition, 189 "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)), 190 "LEFT": lambda args: exp.Substring( 191 this=seq_get(args, 0), start=exp.Literal.number(1), length=seq_get(args, 1) 192 ), 193 } 194 195 FUNCTION_PARSERS = { 196 **parser.Parser.FUNCTION_PARSERS, # type: ignore 197 "GROUP_CONCAT": lambda self: self.expression( 198 exp.GroupConcat, 199 this=self._parse_lambda(), 200 separator=self._match(TokenType.SEPARATOR) and self._parse_field(), 201 ), 202 } 203 204 PROPERTY_PARSERS = { 205 **parser.Parser.PROPERTY_PARSERS, # type: ignore 206 "ENGINE": lambda self: self._parse_property_assignment(exp.EngineProperty), 207 } 208 209 STATEMENT_PARSERS = { 210 **parser.Parser.STATEMENT_PARSERS, # type: ignore 211 TokenType.SHOW: lambda self: self._parse_show(), 212 TokenType.SET: lambda self: self._parse_set(), 213 } 214 215 SHOW_PARSERS = { 216 "BINARY LOGS": _show_parser("BINARY LOGS"), 217 "MASTER LOGS": _show_parser("BINARY LOGS"), 218 "BINLOG EVENTS": _show_parser("BINLOG EVENTS"), 219 "CHARACTER SET": _show_parser("CHARACTER SET"), 220 "CHARSET": _show_parser("CHARACTER SET"), 221 "COLLATION": _show_parser("COLLATION"), 222 "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True), 223 "COLUMNS": _show_parser("COLUMNS", target="FROM"), 224 "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True), 225 "CREATE EVENT": _show_parser("CREATE EVENT", target=True), 226 "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True), 227 "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True), 228 "CREATE TABLE": _show_parser("CREATE TABLE", target=True), 229 "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True), 230 "CREATE VIEW": _show_parser("CREATE VIEW", target=True), 231 "DATABASES": _show_parser("DATABASES"), 232 "ENGINE": _show_parser("ENGINE", target=True), 233 "STORAGE ENGINES": _show_parser("ENGINES"), 234 "ENGINES": _show_parser("ENGINES"), 235 "ERRORS": _show_parser("ERRORS"), 236 "EVENTS": _show_parser("EVENTS"), 237 "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True), 238 "FUNCTION STATUS": _show_parser("FUNCTION STATUS"), 239 "GRANTS": _show_parser("GRANTS", target="FOR"), 240 "INDEX": _show_parser("INDEX", target="FROM"), 241 "MASTER STATUS": _show_parser("MASTER STATUS"), 242 "OPEN TABLES": _show_parser("OPEN TABLES"), 243 "PLUGINS": _show_parser("PLUGINS"), 244 "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True), 245 "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"), 246 "PRIVILEGES": _show_parser("PRIVILEGES"), 247 "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True), 248 "PROCESSLIST": _show_parser("PROCESSLIST"), 249 "PROFILE": _show_parser("PROFILE"), 250 "PROFILES": _show_parser("PROFILES"), 251 "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"), 252 "REPLICAS": _show_parser("REPLICAS"), 253 "SLAVE HOSTS": _show_parser("REPLICAS"), 254 "REPLICA STATUS": _show_parser("REPLICA STATUS"), 255 "SLAVE STATUS": _show_parser("REPLICA STATUS"), 256 "GLOBAL STATUS": _show_parser("STATUS", global_=True), 257 "SESSION STATUS": _show_parser("STATUS"), 258 "STATUS": _show_parser("STATUS"), 259 "TABLE STATUS": _show_parser("TABLE STATUS"), 260 "FULL TABLES": _show_parser("TABLES", full=True), 261 "TABLES": _show_parser("TABLES"), 262 "TRIGGERS": _show_parser("TRIGGERS"), 263 "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True), 264 "SESSION VARIABLES": _show_parser("VARIABLES"), 265 "VARIABLES": _show_parser("VARIABLES"), 266 "WARNINGS": _show_parser("WARNINGS"), 267 } 268 269 SET_PARSERS = { 270 "GLOBAL": lambda self: self._parse_set_item_assignment("GLOBAL"), 271 "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"), 272 "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"), 273 "SESSION": lambda self: self._parse_set_item_assignment("SESSION"), 274 "LOCAL": lambda self: self._parse_set_item_assignment("LOCAL"), 275 "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 276 "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 277 "NAMES": lambda self: self._parse_set_item_names(), 278 "TRANSACTION": lambda self: self._parse_set_transaction(), 279 } 280 281 PROFILE_TYPES = { 282 "ALL", 283 "BLOCK IO", 284 "CONTEXT SWITCHES", 285 "CPU", 286 "IPC", 287 "MEMORY", 288 "PAGE FAULTS", 289 "SOURCE", 290 "SWAPS", 291 } 292 293 TRANSACTION_CHARACTERISTICS = { 294 "ISOLATION LEVEL REPEATABLE READ", 295 "ISOLATION LEVEL READ COMMITTED", 296 "ISOLATION LEVEL READ UNCOMMITTED", 297 "ISOLATION LEVEL SERIALIZABLE", 298 "READ WRITE", 299 "READ ONLY", 300 } 301 302 def _parse_show_mysql(self, this, target=False, full=None, global_=None): 303 if target: 304 if isinstance(target, str): 305 self._match_text_seq(target) 306 target_id = self._parse_id_var() 307 else: 308 target_id = None 309 310 log = self._parse_string() if self._match_text_seq("IN") else None 311 312 if this in {"BINLOG EVENTS", "RELAYLOG EVENTS"}: 313 position = self._parse_number() if self._match_text_seq("FROM") else None 314 db = None 315 else: 316 position = None 317 db = self._parse_id_var() if self._match_text_seq("FROM") else None 318 319 channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None 320 321 like = self._parse_string() if self._match_text_seq("LIKE") else None 322 where = self._parse_where() 323 324 if this == "PROFILE": 325 types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES)) 326 query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None 327 offset = self._parse_number() if self._match_text_seq("OFFSET") else None 328 limit = self._parse_number() if self._match_text_seq("LIMIT") else None 329 else: 330 types, query = None, None 331 offset, limit = self._parse_oldstyle_limit() 332 333 mutex = True if self._match_text_seq("MUTEX") else None 334 mutex = False if self._match_text_seq("STATUS") else mutex 335 336 return self.expression( 337 exp.Show, 338 this=this, 339 target=target_id, 340 full=full, 341 log=log, 342 position=position, 343 db=db, 344 channel=channel, 345 like=like, 346 where=where, 347 types=types, 348 query=query, 349 offset=offset, 350 limit=limit, 351 mutex=mutex, 352 **{"global": global_}, 353 ) 354 355 def _parse_var_from_options(self, options): 356 for option in options: 357 if self._match_text_seq(*option.split(" ")): 358 return exp.Var(this=option) 359 return None 360 361 def _parse_oldstyle_limit(self): 362 limit = None 363 offset = None 364 if self._match_text_seq("LIMIT"): 365 parts = self._parse_csv(self._parse_number) 366 if len(parts) == 1: 367 limit = parts[0] 368 elif len(parts) == 2: 369 limit = parts[1] 370 offset = parts[0] 371 return offset, limit 372 373 def _default_parse_set_item(self): 374 return self._parse_set_item_assignment(kind=None) 375 376 def _parse_set_item_assignment(self, kind): 377 if kind in {"GLOBAL", "SESSION"} and self._match_text_seq("TRANSACTION"): 378 return self._parse_set_transaction(global_=kind == "GLOBAL") 379 380 left = self._parse_primary() or self._parse_id_var() 381 if not self._match(TokenType.EQ): 382 self.raise_error("Expected =") 383 right = self._parse_statement() or self._parse_id_var() 384 385 this = self.expression( 386 exp.EQ, 387 this=left, 388 expression=right, 389 ) 390 391 return self.expression( 392 exp.SetItem, 393 this=this, 394 kind=kind, 395 ) 396 397 def _parse_set_item_charset(self, kind): 398 this = self._parse_string() or self._parse_id_var() 399 400 return self.expression( 401 exp.SetItem, 402 this=this, 403 kind=kind, 404 ) 405 406 def _parse_set_item_names(self): 407 charset = self._parse_string() or self._parse_id_var() 408 if self._match_text_seq("COLLATE"): 409 collate = self._parse_string() or self._parse_id_var() 410 else: 411 collate = None 412 return self.expression( 413 exp.SetItem, 414 this=charset, 415 collate=collate, 416 kind="NAMES", 417 ) 418 419 def _parse_set_transaction(self, global_=False): 420 self._match_text_seq("TRANSACTION") 421 characteristics = self._parse_csv( 422 lambda: self._parse_var_from_options(self.TRANSACTION_CHARACTERISTICS) 423 ) 424 return self.expression( 425 exp.SetItem, 426 expressions=characteristics, 427 kind="TRANSACTION", 428 **{"global": global_}, 429 ) 430 431 class Generator(generator.Generator): 432 LOCKING_READS_SUPPORTED = True 433 NULL_ORDERING_SUPPORTED = False 434 435 TRANSFORMS = { 436 **generator.Generator.TRANSFORMS, # type: ignore 437 exp.CurrentDate: no_paren_current_date_sql, 438 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 439 exp.ILike: no_ilike_sql, 440 exp.TableSample: no_tablesample_sql, 441 exp.TryCast: no_trycast_sql, 442 exp.DateAdd: _date_add_sql("ADD"), 443 exp.DateSub: _date_add_sql("SUB"), 444 exp.DateTrunc: _date_trunc_sql, 445 exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""", 446 exp.StrToDate: _str_to_date_sql, 447 exp.StrToTime: _str_to_date_sql, 448 exp.Trim: _trim_sql, 449 exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"), 450 exp.NullSafeNEQ: lambda self, e: self.not_sql(self.binary(e, "<=>")), 451 exp.StrPosition: strposition_to_locate_sql, 452 } 453 454 TYPE_MAPPING = generator.Generator.TYPE_MAPPING.copy() 455 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT) 456 TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT) 457 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB) 458 TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB) 459 460 def show_sql(self, expression): 461 this = f" {expression.name}" 462 full = " FULL" if expression.args.get("full") else "" 463 global_ = " GLOBAL" if expression.args.get("global") else "" 464 465 target = self.sql(expression, "target") 466 target = f" {target}" if target else "" 467 if expression.name in {"COLUMNS", "INDEX"}: 468 target = f" FROM{target}" 469 elif expression.name == "GRANTS": 470 target = f" FOR{target}" 471 472 db = self._prefixed_sql("FROM", expression, "db") 473 474 like = self._prefixed_sql("LIKE", expression, "like") 475 where = self.sql(expression, "where") 476 477 types = self.expressions(expression, key="types") 478 types = f" {types}" if types else types 479 query = self._prefixed_sql("FOR QUERY", expression, "query") 480 481 if expression.name == "PROFILE": 482 offset = self._prefixed_sql("OFFSET", expression, "offset") 483 limit = self._prefixed_sql("LIMIT", expression, "limit") 484 else: 485 offset = "" 486 limit = self._oldstyle_limit_sql(expression) 487 488 log = self._prefixed_sql("IN", expression, "log") 489 position = self._prefixed_sql("FROM", expression, "position") 490 491 channel = self._prefixed_sql("FOR CHANNEL", expression, "channel") 492 493 if expression.name == "ENGINE": 494 mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS" 495 else: 496 mutex_or_status = "" 497 498 return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}" 499 500 def _prefixed_sql(self, prefix, expression, arg): 501 sql = self.sql(expression, arg) 502 if not sql: 503 return "" 504 return f" {prefix} {sql}" 505 506 def _oldstyle_limit_sql(self, expression): 507 limit = self.sql(expression, "limit") 508 offset = self.sql(expression, "offset") 509 if limit: 510 limit_offset = f"{offset}, {limit}" if offset else limit 511 return f" LIMIT {limit_offset}" 512 return "" 513 514 def setitem_sql(self, expression): 515 kind = self.sql(expression, "kind") 516 kind = f"{kind} " if kind else "" 517 this = self.sql(expression, "this") 518 expressions = self.expressions(expression) 519 collate = self.sql(expression, "collate") 520 collate = f" COLLATE {collate}" if collate else "" 521 global_ = "GLOBAL " if expression.args.get("global") else "" 522 return f"{global_}{kind}{this}{expressions}{collate}" 523 524 def set_sql(self, expression): 525 return f"SET {self.expressions(expression)}"
99class MySQL(Dialect): 100 time_format = "'%Y-%m-%d %T'" 101 102 # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions 103 time_mapping = { 104 "%M": "%B", 105 "%c": "%-m", 106 "%e": "%-d", 107 "%h": "%I", 108 "%i": "%M", 109 "%s": "%S", 110 "%S": "%S", 111 "%u": "%W", 112 "%k": "%-H", 113 "%l": "%-I", 114 "%T": "%H:%M:%S", 115 } 116 117 class Tokenizer(tokens.Tokenizer): 118 QUOTES = ["'", '"'] 119 COMMENTS = ["--", "#", ("/*", "*/")] 120 IDENTIFIERS = ["`"] 121 STRING_ESCAPES = ["'", "\\"] 122 BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")] 123 HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")] 124 125 KEYWORDS = { 126 **tokens.Tokenizer.KEYWORDS, 127 "MEDIUMTEXT": TokenType.MEDIUMTEXT, 128 "LONGTEXT": TokenType.LONGTEXT, 129 "MEDIUMBLOB": TokenType.MEDIUMBLOB, 130 "LONGBLOB": TokenType.LONGBLOB, 131 "START": TokenType.BEGIN, 132 "SEPARATOR": TokenType.SEPARATOR, 133 "_ARMSCII8": TokenType.INTRODUCER, 134 "_ASCII": TokenType.INTRODUCER, 135 "_BIG5": TokenType.INTRODUCER, 136 "_BINARY": TokenType.INTRODUCER, 137 "_CP1250": TokenType.INTRODUCER, 138 "_CP1251": TokenType.INTRODUCER, 139 "_CP1256": TokenType.INTRODUCER, 140 "_CP1257": TokenType.INTRODUCER, 141 "_CP850": TokenType.INTRODUCER, 142 "_CP852": TokenType.INTRODUCER, 143 "_CP866": TokenType.INTRODUCER, 144 "_CP932": TokenType.INTRODUCER, 145 "_DEC8": TokenType.INTRODUCER, 146 "_EUCJPMS": TokenType.INTRODUCER, 147 "_EUCKR": TokenType.INTRODUCER, 148 "_GB18030": TokenType.INTRODUCER, 149 "_GB2312": TokenType.INTRODUCER, 150 "_GBK": TokenType.INTRODUCER, 151 "_GEOSTD8": TokenType.INTRODUCER, 152 "_GREEK": TokenType.INTRODUCER, 153 "_HEBREW": TokenType.INTRODUCER, 154 "_HP8": TokenType.INTRODUCER, 155 "_KEYBCS2": TokenType.INTRODUCER, 156 "_KOI8R": TokenType.INTRODUCER, 157 "_KOI8U": TokenType.INTRODUCER, 158 "_LATIN1": TokenType.INTRODUCER, 159 "_LATIN2": TokenType.INTRODUCER, 160 "_LATIN5": TokenType.INTRODUCER, 161 "_LATIN7": TokenType.INTRODUCER, 162 "_MACCE": TokenType.INTRODUCER, 163 "_MACROMAN": TokenType.INTRODUCER, 164 "_SJIS": TokenType.INTRODUCER, 165 "_SWE7": TokenType.INTRODUCER, 166 "_TIS620": TokenType.INTRODUCER, 167 "_UCS2": TokenType.INTRODUCER, 168 "_UJIS": TokenType.INTRODUCER, 169 # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html 170 "_UTF8": TokenType.INTRODUCER, 171 "_UTF16": TokenType.INTRODUCER, 172 "_UTF16LE": TokenType.INTRODUCER, 173 "_UTF32": TokenType.INTRODUCER, 174 "_UTF8MB3": TokenType.INTRODUCER, 175 "_UTF8MB4": TokenType.INTRODUCER, 176 "@@": TokenType.SESSION_PARAMETER, 177 } 178 179 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SET, TokenType.SHOW} 180 181 class Parser(parser.Parser): 182 FUNC_TOKENS = {*parser.Parser.FUNC_TOKENS, TokenType.SCHEMA} # type: ignore 183 184 FUNCTIONS = { 185 **parser.Parser.FUNCTIONS, # type: ignore 186 "DATE_ADD": _date_add(exp.DateAdd), 187 "DATE_SUB": _date_add(exp.DateSub), 188 "STR_TO_DATE": _str_to_date, 189 "LOCATE": locate_to_strposition, 190 "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)), 191 "LEFT": lambda args: exp.Substring( 192 this=seq_get(args, 0), start=exp.Literal.number(1), length=seq_get(args, 1) 193 ), 194 } 195 196 FUNCTION_PARSERS = { 197 **parser.Parser.FUNCTION_PARSERS, # type: ignore 198 "GROUP_CONCAT": lambda self: self.expression( 199 exp.GroupConcat, 200 this=self._parse_lambda(), 201 separator=self._match(TokenType.SEPARATOR) and self._parse_field(), 202 ), 203 } 204 205 PROPERTY_PARSERS = { 206 **parser.Parser.PROPERTY_PARSERS, # type: ignore 207 "ENGINE": lambda self: self._parse_property_assignment(exp.EngineProperty), 208 } 209 210 STATEMENT_PARSERS = { 211 **parser.Parser.STATEMENT_PARSERS, # type: ignore 212 TokenType.SHOW: lambda self: self._parse_show(), 213 TokenType.SET: lambda self: self._parse_set(), 214 } 215 216 SHOW_PARSERS = { 217 "BINARY LOGS": _show_parser("BINARY LOGS"), 218 "MASTER LOGS": _show_parser("BINARY LOGS"), 219 "BINLOG EVENTS": _show_parser("BINLOG EVENTS"), 220 "CHARACTER SET": _show_parser("CHARACTER SET"), 221 "CHARSET": _show_parser("CHARACTER SET"), 222 "COLLATION": _show_parser("COLLATION"), 223 "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True), 224 "COLUMNS": _show_parser("COLUMNS", target="FROM"), 225 "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True), 226 "CREATE EVENT": _show_parser("CREATE EVENT", target=True), 227 "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True), 228 "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True), 229 "CREATE TABLE": _show_parser("CREATE TABLE", target=True), 230 "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True), 231 "CREATE VIEW": _show_parser("CREATE VIEW", target=True), 232 "DATABASES": _show_parser("DATABASES"), 233 "ENGINE": _show_parser("ENGINE", target=True), 234 "STORAGE ENGINES": _show_parser("ENGINES"), 235 "ENGINES": _show_parser("ENGINES"), 236 "ERRORS": _show_parser("ERRORS"), 237 "EVENTS": _show_parser("EVENTS"), 238 "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True), 239 "FUNCTION STATUS": _show_parser("FUNCTION STATUS"), 240 "GRANTS": _show_parser("GRANTS", target="FOR"), 241 "INDEX": _show_parser("INDEX", target="FROM"), 242 "MASTER STATUS": _show_parser("MASTER STATUS"), 243 "OPEN TABLES": _show_parser("OPEN TABLES"), 244 "PLUGINS": _show_parser("PLUGINS"), 245 "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True), 246 "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"), 247 "PRIVILEGES": _show_parser("PRIVILEGES"), 248 "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True), 249 "PROCESSLIST": _show_parser("PROCESSLIST"), 250 "PROFILE": _show_parser("PROFILE"), 251 "PROFILES": _show_parser("PROFILES"), 252 "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"), 253 "REPLICAS": _show_parser("REPLICAS"), 254 "SLAVE HOSTS": _show_parser("REPLICAS"), 255 "REPLICA STATUS": _show_parser("REPLICA STATUS"), 256 "SLAVE STATUS": _show_parser("REPLICA STATUS"), 257 "GLOBAL STATUS": _show_parser("STATUS", global_=True), 258 "SESSION STATUS": _show_parser("STATUS"), 259 "STATUS": _show_parser("STATUS"), 260 "TABLE STATUS": _show_parser("TABLE STATUS"), 261 "FULL TABLES": _show_parser("TABLES", full=True), 262 "TABLES": _show_parser("TABLES"), 263 "TRIGGERS": _show_parser("TRIGGERS"), 264 "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True), 265 "SESSION VARIABLES": _show_parser("VARIABLES"), 266 "VARIABLES": _show_parser("VARIABLES"), 267 "WARNINGS": _show_parser("WARNINGS"), 268 } 269 270 SET_PARSERS = { 271 "GLOBAL": lambda self: self._parse_set_item_assignment("GLOBAL"), 272 "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"), 273 "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"), 274 "SESSION": lambda self: self._parse_set_item_assignment("SESSION"), 275 "LOCAL": lambda self: self._parse_set_item_assignment("LOCAL"), 276 "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 277 "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 278 "NAMES": lambda self: self._parse_set_item_names(), 279 "TRANSACTION": lambda self: self._parse_set_transaction(), 280 } 281 282 PROFILE_TYPES = { 283 "ALL", 284 "BLOCK IO", 285 "CONTEXT SWITCHES", 286 "CPU", 287 "IPC", 288 "MEMORY", 289 "PAGE FAULTS", 290 "SOURCE", 291 "SWAPS", 292 } 293 294 TRANSACTION_CHARACTERISTICS = { 295 "ISOLATION LEVEL REPEATABLE READ", 296 "ISOLATION LEVEL READ COMMITTED", 297 "ISOLATION LEVEL READ UNCOMMITTED", 298 "ISOLATION LEVEL SERIALIZABLE", 299 "READ WRITE", 300 "READ ONLY", 301 } 302 303 def _parse_show_mysql(self, this, target=False, full=None, global_=None): 304 if target: 305 if isinstance(target, str): 306 self._match_text_seq(target) 307 target_id = self._parse_id_var() 308 else: 309 target_id = None 310 311 log = self._parse_string() if self._match_text_seq("IN") else None 312 313 if this in {"BINLOG EVENTS", "RELAYLOG EVENTS"}: 314 position = self._parse_number() if self._match_text_seq("FROM") else None 315 db = None 316 else: 317 position = None 318 db = self._parse_id_var() if self._match_text_seq("FROM") else None 319 320 channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None 321 322 like = self._parse_string() if self._match_text_seq("LIKE") else None 323 where = self._parse_where() 324 325 if this == "PROFILE": 326 types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES)) 327 query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None 328 offset = self._parse_number() if self._match_text_seq("OFFSET") else None 329 limit = self._parse_number() if self._match_text_seq("LIMIT") else None 330 else: 331 types, query = None, None 332 offset, limit = self._parse_oldstyle_limit() 333 334 mutex = True if self._match_text_seq("MUTEX") else None 335 mutex = False if self._match_text_seq("STATUS") else mutex 336 337 return self.expression( 338 exp.Show, 339 this=this, 340 target=target_id, 341 full=full, 342 log=log, 343 position=position, 344 db=db, 345 channel=channel, 346 like=like, 347 where=where, 348 types=types, 349 query=query, 350 offset=offset, 351 limit=limit, 352 mutex=mutex, 353 **{"global": global_}, 354 ) 355 356 def _parse_var_from_options(self, options): 357 for option in options: 358 if self._match_text_seq(*option.split(" ")): 359 return exp.Var(this=option) 360 return None 361 362 def _parse_oldstyle_limit(self): 363 limit = None 364 offset = None 365 if self._match_text_seq("LIMIT"): 366 parts = self._parse_csv(self._parse_number) 367 if len(parts) == 1: 368 limit = parts[0] 369 elif len(parts) == 2: 370 limit = parts[1] 371 offset = parts[0] 372 return offset, limit 373 374 def _default_parse_set_item(self): 375 return self._parse_set_item_assignment(kind=None) 376 377 def _parse_set_item_assignment(self, kind): 378 if kind in {"GLOBAL", "SESSION"} and self._match_text_seq("TRANSACTION"): 379 return self._parse_set_transaction(global_=kind == "GLOBAL") 380 381 left = self._parse_primary() or self._parse_id_var() 382 if not self._match(TokenType.EQ): 383 self.raise_error("Expected =") 384 right = self._parse_statement() or self._parse_id_var() 385 386 this = self.expression( 387 exp.EQ, 388 this=left, 389 expression=right, 390 ) 391 392 return self.expression( 393 exp.SetItem, 394 this=this, 395 kind=kind, 396 ) 397 398 def _parse_set_item_charset(self, kind): 399 this = self._parse_string() or self._parse_id_var() 400 401 return self.expression( 402 exp.SetItem, 403 this=this, 404 kind=kind, 405 ) 406 407 def _parse_set_item_names(self): 408 charset = self._parse_string() or self._parse_id_var() 409 if self._match_text_seq("COLLATE"): 410 collate = self._parse_string() or self._parse_id_var() 411 else: 412 collate = None 413 return self.expression( 414 exp.SetItem, 415 this=charset, 416 collate=collate, 417 kind="NAMES", 418 ) 419 420 def _parse_set_transaction(self, global_=False): 421 self._match_text_seq("TRANSACTION") 422 characteristics = self._parse_csv( 423 lambda: self._parse_var_from_options(self.TRANSACTION_CHARACTERISTICS) 424 ) 425 return self.expression( 426 exp.SetItem, 427 expressions=characteristics, 428 kind="TRANSACTION", 429 **{"global": global_}, 430 ) 431 432 class Generator(generator.Generator): 433 LOCKING_READS_SUPPORTED = True 434 NULL_ORDERING_SUPPORTED = False 435 436 TRANSFORMS = { 437 **generator.Generator.TRANSFORMS, # type: ignore 438 exp.CurrentDate: no_paren_current_date_sql, 439 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 440 exp.ILike: no_ilike_sql, 441 exp.TableSample: no_tablesample_sql, 442 exp.TryCast: no_trycast_sql, 443 exp.DateAdd: _date_add_sql("ADD"), 444 exp.DateSub: _date_add_sql("SUB"), 445 exp.DateTrunc: _date_trunc_sql, 446 exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""", 447 exp.StrToDate: _str_to_date_sql, 448 exp.StrToTime: _str_to_date_sql, 449 exp.Trim: _trim_sql, 450 exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"), 451 exp.NullSafeNEQ: lambda self, e: self.not_sql(self.binary(e, "<=>")), 452 exp.StrPosition: strposition_to_locate_sql, 453 } 454 455 TYPE_MAPPING = generator.Generator.TYPE_MAPPING.copy() 456 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT) 457 TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT) 458 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB) 459 TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB) 460 461 def show_sql(self, expression): 462 this = f" {expression.name}" 463 full = " FULL" if expression.args.get("full") else "" 464 global_ = " GLOBAL" if expression.args.get("global") else "" 465 466 target = self.sql(expression, "target") 467 target = f" {target}" if target else "" 468 if expression.name in {"COLUMNS", "INDEX"}: 469 target = f" FROM{target}" 470 elif expression.name == "GRANTS": 471 target = f" FOR{target}" 472 473 db = self._prefixed_sql("FROM", expression, "db") 474 475 like = self._prefixed_sql("LIKE", expression, "like") 476 where = self.sql(expression, "where") 477 478 types = self.expressions(expression, key="types") 479 types = f" {types}" if types else types 480 query = self._prefixed_sql("FOR QUERY", expression, "query") 481 482 if expression.name == "PROFILE": 483 offset = self._prefixed_sql("OFFSET", expression, "offset") 484 limit = self._prefixed_sql("LIMIT", expression, "limit") 485 else: 486 offset = "" 487 limit = self._oldstyle_limit_sql(expression) 488 489 log = self._prefixed_sql("IN", expression, "log") 490 position = self._prefixed_sql("FROM", expression, "position") 491 492 channel = self._prefixed_sql("FOR CHANNEL", expression, "channel") 493 494 if expression.name == "ENGINE": 495 mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS" 496 else: 497 mutex_or_status = "" 498 499 return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}" 500 501 def _prefixed_sql(self, prefix, expression, arg): 502 sql = self.sql(expression, arg) 503 if not sql: 504 return "" 505 return f" {prefix} {sql}" 506 507 def _oldstyle_limit_sql(self, expression): 508 limit = self.sql(expression, "limit") 509 offset = self.sql(expression, "offset") 510 if limit: 511 limit_offset = f"{offset}, {limit}" if offset else limit 512 return f" LIMIT {limit_offset}" 513 return "" 514 515 def setitem_sql(self, expression): 516 kind = self.sql(expression, "kind") 517 kind = f"{kind} " if kind else "" 518 this = self.sql(expression, "this") 519 expressions = self.expressions(expression) 520 collate = self.sql(expression, "collate") 521 collate = f" COLLATE {collate}" if collate else "" 522 global_ = "GLOBAL " if expression.args.get("global") else "" 523 return f"{global_}{kind}{this}{expressions}{collate}" 524 525 def set_sql(self, expression): 526 return f"SET {self.expressions(expression)}"
Inherited Members
117 class Tokenizer(tokens.Tokenizer): 118 QUOTES = ["'", '"'] 119 COMMENTS = ["--", "#", ("/*", "*/")] 120 IDENTIFIERS = ["`"] 121 STRING_ESCAPES = ["'", "\\"] 122 BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")] 123 HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")] 124 125 KEYWORDS = { 126 **tokens.Tokenizer.KEYWORDS, 127 "MEDIUMTEXT": TokenType.MEDIUMTEXT, 128 "LONGTEXT": TokenType.LONGTEXT, 129 "MEDIUMBLOB": TokenType.MEDIUMBLOB, 130 "LONGBLOB": TokenType.LONGBLOB, 131 "START": TokenType.BEGIN, 132 "SEPARATOR": TokenType.SEPARATOR, 133 "_ARMSCII8": TokenType.INTRODUCER, 134 "_ASCII": TokenType.INTRODUCER, 135 "_BIG5": TokenType.INTRODUCER, 136 "_BINARY": TokenType.INTRODUCER, 137 "_CP1250": TokenType.INTRODUCER, 138 "_CP1251": TokenType.INTRODUCER, 139 "_CP1256": TokenType.INTRODUCER, 140 "_CP1257": TokenType.INTRODUCER, 141 "_CP850": TokenType.INTRODUCER, 142 "_CP852": TokenType.INTRODUCER, 143 "_CP866": TokenType.INTRODUCER, 144 "_CP932": TokenType.INTRODUCER, 145 "_DEC8": TokenType.INTRODUCER, 146 "_EUCJPMS": TokenType.INTRODUCER, 147 "_EUCKR": TokenType.INTRODUCER, 148 "_GB18030": TokenType.INTRODUCER, 149 "_GB2312": TokenType.INTRODUCER, 150 "_GBK": TokenType.INTRODUCER, 151 "_GEOSTD8": TokenType.INTRODUCER, 152 "_GREEK": TokenType.INTRODUCER, 153 "_HEBREW": TokenType.INTRODUCER, 154 "_HP8": TokenType.INTRODUCER, 155 "_KEYBCS2": TokenType.INTRODUCER, 156 "_KOI8R": TokenType.INTRODUCER, 157 "_KOI8U": TokenType.INTRODUCER, 158 "_LATIN1": TokenType.INTRODUCER, 159 "_LATIN2": TokenType.INTRODUCER, 160 "_LATIN5": TokenType.INTRODUCER, 161 "_LATIN7": TokenType.INTRODUCER, 162 "_MACCE": TokenType.INTRODUCER, 163 "_MACROMAN": TokenType.INTRODUCER, 164 "_SJIS": TokenType.INTRODUCER, 165 "_SWE7": TokenType.INTRODUCER, 166 "_TIS620": TokenType.INTRODUCER, 167 "_UCS2": TokenType.INTRODUCER, 168 "_UJIS": TokenType.INTRODUCER, 169 # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html 170 "_UTF8": TokenType.INTRODUCER, 171 "_UTF16": TokenType.INTRODUCER, 172 "_UTF16LE": TokenType.INTRODUCER, 173 "_UTF32": TokenType.INTRODUCER, 174 "_UTF8MB3": TokenType.INTRODUCER, 175 "_UTF8MB4": TokenType.INTRODUCER, 176 "@@": TokenType.SESSION_PARAMETER, 177 } 178 179 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SET, TokenType.SHOW}
Inherited Members
181 class Parser(parser.Parser): 182 FUNC_TOKENS = {*parser.Parser.FUNC_TOKENS, TokenType.SCHEMA} # type: ignore 183 184 FUNCTIONS = { 185 **parser.Parser.FUNCTIONS, # type: ignore 186 "DATE_ADD": _date_add(exp.DateAdd), 187 "DATE_SUB": _date_add(exp.DateSub), 188 "STR_TO_DATE": _str_to_date, 189 "LOCATE": locate_to_strposition, 190 "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)), 191 "LEFT": lambda args: exp.Substring( 192 this=seq_get(args, 0), start=exp.Literal.number(1), length=seq_get(args, 1) 193 ), 194 } 195 196 FUNCTION_PARSERS = { 197 **parser.Parser.FUNCTION_PARSERS, # type: ignore 198 "GROUP_CONCAT": lambda self: self.expression( 199 exp.GroupConcat, 200 this=self._parse_lambda(), 201 separator=self._match(TokenType.SEPARATOR) and self._parse_field(), 202 ), 203 } 204 205 PROPERTY_PARSERS = { 206 **parser.Parser.PROPERTY_PARSERS, # type: ignore 207 "ENGINE": lambda self: self._parse_property_assignment(exp.EngineProperty), 208 } 209 210 STATEMENT_PARSERS = { 211 **parser.Parser.STATEMENT_PARSERS, # type: ignore 212 TokenType.SHOW: lambda self: self._parse_show(), 213 TokenType.SET: lambda self: self._parse_set(), 214 } 215 216 SHOW_PARSERS = { 217 "BINARY LOGS": _show_parser("BINARY LOGS"), 218 "MASTER LOGS": _show_parser("BINARY LOGS"), 219 "BINLOG EVENTS": _show_parser("BINLOG EVENTS"), 220 "CHARACTER SET": _show_parser("CHARACTER SET"), 221 "CHARSET": _show_parser("CHARACTER SET"), 222 "COLLATION": _show_parser("COLLATION"), 223 "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True), 224 "COLUMNS": _show_parser("COLUMNS", target="FROM"), 225 "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True), 226 "CREATE EVENT": _show_parser("CREATE EVENT", target=True), 227 "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True), 228 "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True), 229 "CREATE TABLE": _show_parser("CREATE TABLE", target=True), 230 "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True), 231 "CREATE VIEW": _show_parser("CREATE VIEW", target=True), 232 "DATABASES": _show_parser("DATABASES"), 233 "ENGINE": _show_parser("ENGINE", target=True), 234 "STORAGE ENGINES": _show_parser("ENGINES"), 235 "ENGINES": _show_parser("ENGINES"), 236 "ERRORS": _show_parser("ERRORS"), 237 "EVENTS": _show_parser("EVENTS"), 238 "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True), 239 "FUNCTION STATUS": _show_parser("FUNCTION STATUS"), 240 "GRANTS": _show_parser("GRANTS", target="FOR"), 241 "INDEX": _show_parser("INDEX", target="FROM"), 242 "MASTER STATUS": _show_parser("MASTER STATUS"), 243 "OPEN TABLES": _show_parser("OPEN TABLES"), 244 "PLUGINS": _show_parser("PLUGINS"), 245 "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True), 246 "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"), 247 "PRIVILEGES": _show_parser("PRIVILEGES"), 248 "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True), 249 "PROCESSLIST": _show_parser("PROCESSLIST"), 250 "PROFILE": _show_parser("PROFILE"), 251 "PROFILES": _show_parser("PROFILES"), 252 "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"), 253 "REPLICAS": _show_parser("REPLICAS"), 254 "SLAVE HOSTS": _show_parser("REPLICAS"), 255 "REPLICA STATUS": _show_parser("REPLICA STATUS"), 256 "SLAVE STATUS": _show_parser("REPLICA STATUS"), 257 "GLOBAL STATUS": _show_parser("STATUS", global_=True), 258 "SESSION STATUS": _show_parser("STATUS"), 259 "STATUS": _show_parser("STATUS"), 260 "TABLE STATUS": _show_parser("TABLE STATUS"), 261 "FULL TABLES": _show_parser("TABLES", full=True), 262 "TABLES": _show_parser("TABLES"), 263 "TRIGGERS": _show_parser("TRIGGERS"), 264 "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True), 265 "SESSION VARIABLES": _show_parser("VARIABLES"), 266 "VARIABLES": _show_parser("VARIABLES"), 267 "WARNINGS": _show_parser("WARNINGS"), 268 } 269 270 SET_PARSERS = { 271 "GLOBAL": lambda self: self._parse_set_item_assignment("GLOBAL"), 272 "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"), 273 "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"), 274 "SESSION": lambda self: self._parse_set_item_assignment("SESSION"), 275 "LOCAL": lambda self: self._parse_set_item_assignment("LOCAL"), 276 "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 277 "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"), 278 "NAMES": lambda self: self._parse_set_item_names(), 279 "TRANSACTION": lambda self: self._parse_set_transaction(), 280 } 281 282 PROFILE_TYPES = { 283 "ALL", 284 "BLOCK IO", 285 "CONTEXT SWITCHES", 286 "CPU", 287 "IPC", 288 "MEMORY", 289 "PAGE FAULTS", 290 "SOURCE", 291 "SWAPS", 292 } 293 294 TRANSACTION_CHARACTERISTICS = { 295 "ISOLATION LEVEL REPEATABLE READ", 296 "ISOLATION LEVEL READ COMMITTED", 297 "ISOLATION LEVEL READ UNCOMMITTED", 298 "ISOLATION LEVEL SERIALIZABLE", 299 "READ WRITE", 300 "READ ONLY", 301 } 302 303 def _parse_show_mysql(self, this, target=False, full=None, global_=None): 304 if target: 305 if isinstance(target, str): 306 self._match_text_seq(target) 307 target_id = self._parse_id_var() 308 else: 309 target_id = None 310 311 log = self._parse_string() if self._match_text_seq("IN") else None 312 313 if this in {"BINLOG EVENTS", "RELAYLOG EVENTS"}: 314 position = self._parse_number() if self._match_text_seq("FROM") else None 315 db = None 316 else: 317 position = None 318 db = self._parse_id_var() if self._match_text_seq("FROM") else None 319 320 channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None 321 322 like = self._parse_string() if self._match_text_seq("LIKE") else None 323 where = self._parse_where() 324 325 if this == "PROFILE": 326 types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES)) 327 query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None 328 offset = self._parse_number() if self._match_text_seq("OFFSET") else None 329 limit = self._parse_number() if self._match_text_seq("LIMIT") else None 330 else: 331 types, query = None, None 332 offset, limit = self._parse_oldstyle_limit() 333 334 mutex = True if self._match_text_seq("MUTEX") else None 335 mutex = False if self._match_text_seq("STATUS") else mutex 336 337 return self.expression( 338 exp.Show, 339 this=this, 340 target=target_id, 341 full=full, 342 log=log, 343 position=position, 344 db=db, 345 channel=channel, 346 like=like, 347 where=where, 348 types=types, 349 query=query, 350 offset=offset, 351 limit=limit, 352 mutex=mutex, 353 **{"global": global_}, 354 ) 355 356 def _parse_var_from_options(self, options): 357 for option in options: 358 if self._match_text_seq(*option.split(" ")): 359 return exp.Var(this=option) 360 return None 361 362 def _parse_oldstyle_limit(self): 363 limit = None 364 offset = None 365 if self._match_text_seq("LIMIT"): 366 parts = self._parse_csv(self._parse_number) 367 if len(parts) == 1: 368 limit = parts[0] 369 elif len(parts) == 2: 370 limit = parts[1] 371 offset = parts[0] 372 return offset, limit 373 374 def _default_parse_set_item(self): 375 return self._parse_set_item_assignment(kind=None) 376 377 def _parse_set_item_assignment(self, kind): 378 if kind in {"GLOBAL", "SESSION"} and self._match_text_seq("TRANSACTION"): 379 return self._parse_set_transaction(global_=kind == "GLOBAL") 380 381 left = self._parse_primary() or self._parse_id_var() 382 if not self._match(TokenType.EQ): 383 self.raise_error("Expected =") 384 right = self._parse_statement() or self._parse_id_var() 385 386 this = self.expression( 387 exp.EQ, 388 this=left, 389 expression=right, 390 ) 391 392 return self.expression( 393 exp.SetItem, 394 this=this, 395 kind=kind, 396 ) 397 398 def _parse_set_item_charset(self, kind): 399 this = self._parse_string() or self._parse_id_var() 400 401 return self.expression( 402 exp.SetItem, 403 this=this, 404 kind=kind, 405 ) 406 407 def _parse_set_item_names(self): 408 charset = self._parse_string() or self._parse_id_var() 409 if self._match_text_seq("COLLATE"): 410 collate = self._parse_string() or self._parse_id_var() 411 else: 412 collate = None 413 return self.expression( 414 exp.SetItem, 415 this=charset, 416 collate=collate, 417 kind="NAMES", 418 ) 419 420 def _parse_set_transaction(self, global_=False): 421 self._match_text_seq("TRANSACTION") 422 characteristics = self._parse_csv( 423 lambda: self._parse_var_from_options(self.TRANSACTION_CHARACTERISTICS) 424 ) 425 return self.expression( 426 exp.SetItem, 427 expressions=characteristics, 428 kind="TRANSACTION", 429 **{"global": global_}, 430 )
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
432 class Generator(generator.Generator): 433 LOCKING_READS_SUPPORTED = True 434 NULL_ORDERING_SUPPORTED = False 435 436 TRANSFORMS = { 437 **generator.Generator.TRANSFORMS, # type: ignore 438 exp.CurrentDate: no_paren_current_date_sql, 439 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 440 exp.ILike: no_ilike_sql, 441 exp.TableSample: no_tablesample_sql, 442 exp.TryCast: no_trycast_sql, 443 exp.DateAdd: _date_add_sql("ADD"), 444 exp.DateSub: _date_add_sql("SUB"), 445 exp.DateTrunc: _date_trunc_sql, 446 exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""", 447 exp.StrToDate: _str_to_date_sql, 448 exp.StrToTime: _str_to_date_sql, 449 exp.Trim: _trim_sql, 450 exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"), 451 exp.NullSafeNEQ: lambda self, e: self.not_sql(self.binary(e, "<=>")), 452 exp.StrPosition: strposition_to_locate_sql, 453 } 454 455 TYPE_MAPPING = generator.Generator.TYPE_MAPPING.copy() 456 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT) 457 TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT) 458 TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB) 459 TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB) 460 461 def show_sql(self, expression): 462 this = f" {expression.name}" 463 full = " FULL" if expression.args.get("full") else "" 464 global_ = " GLOBAL" if expression.args.get("global") else "" 465 466 target = self.sql(expression, "target") 467 target = f" {target}" if target else "" 468 if expression.name in {"COLUMNS", "INDEX"}: 469 target = f" FROM{target}" 470 elif expression.name == "GRANTS": 471 target = f" FOR{target}" 472 473 db = self._prefixed_sql("FROM", expression, "db") 474 475 like = self._prefixed_sql("LIKE", expression, "like") 476 where = self.sql(expression, "where") 477 478 types = self.expressions(expression, key="types") 479 types = f" {types}" if types else types 480 query = self._prefixed_sql("FOR QUERY", expression, "query") 481 482 if expression.name == "PROFILE": 483 offset = self._prefixed_sql("OFFSET", expression, "offset") 484 limit = self._prefixed_sql("LIMIT", expression, "limit") 485 else: 486 offset = "" 487 limit = self._oldstyle_limit_sql(expression) 488 489 log = self._prefixed_sql("IN", expression, "log") 490 position = self._prefixed_sql("FROM", expression, "position") 491 492 channel = self._prefixed_sql("FOR CHANNEL", expression, "channel") 493 494 if expression.name == "ENGINE": 495 mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS" 496 else: 497 mutex_or_status = "" 498 499 return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}" 500 501 def _prefixed_sql(self, prefix, expression, arg): 502 sql = self.sql(expression, arg) 503 if not sql: 504 return "" 505 return f" {prefix} {sql}" 506 507 def _oldstyle_limit_sql(self, expression): 508 limit = self.sql(expression, "limit") 509 offset = self.sql(expression, "offset") 510 if limit: 511 limit_offset = f"{offset}, {limit}" if offset else limit 512 return f" LIMIT {limit_offset}" 513 return "" 514 515 def setitem_sql(self, expression): 516 kind = self.sql(expression, "kind") 517 kind = f"{kind} " if kind else "" 518 this = self.sql(expression, "this") 519 expressions = self.expressions(expression) 520 collate = self.sql(expression, "collate") 521 collate = f" COLLATE {collate}" if collate else "" 522 global_ = "GLOBAL " if expression.args.get("global") else "" 523 return f"{global_}{kind}{this}{expressions}{collate}" 524 525 def set_sql(self, expression): 526 return f"SET {self.expressions(expression)}"
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): if set to True all identifiers will be delimited by the corresponding character.
- 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
def
show_sql(self, expression):
461 def show_sql(self, expression): 462 this = f" {expression.name}" 463 full = " FULL" if expression.args.get("full") else "" 464 global_ = " GLOBAL" if expression.args.get("global") else "" 465 466 target = self.sql(expression, "target") 467 target = f" {target}" if target else "" 468 if expression.name in {"COLUMNS", "INDEX"}: 469 target = f" FROM{target}" 470 elif expression.name == "GRANTS": 471 target = f" FOR{target}" 472 473 db = self._prefixed_sql("FROM", expression, "db") 474 475 like = self._prefixed_sql("LIKE", expression, "like") 476 where = self.sql(expression, "where") 477 478 types = self.expressions(expression, key="types") 479 types = f" {types}" if types else types 480 query = self._prefixed_sql("FOR QUERY", expression, "query") 481 482 if expression.name == "PROFILE": 483 offset = self._prefixed_sql("OFFSET", expression, "offset") 484 limit = self._prefixed_sql("LIMIT", expression, "limit") 485 else: 486 offset = "" 487 limit = self._oldstyle_limit_sql(expression) 488 489 log = self._prefixed_sql("IN", expression, "log") 490 position = self._prefixed_sql("FROM", expression, "position") 491 492 channel = self._prefixed_sql("FOR CHANNEL", expression, "channel") 493 494 if expression.name == "ENGINE": 495 mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS" 496 else: 497 mutex_or_status = "" 498 499 return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}"
def
setitem_sql(self, expression):
515 def setitem_sql(self, expression): 516 kind = self.sql(expression, "kind") 517 kind = f"{kind} " if kind else "" 518 this = self.sql(expression, "this") 519 expressions = self.expressions(expression) 520 collate = self.sql(expression, "collate") 521 collate = f" COLLATE {collate}" if collate else "" 522 global_ = "GLOBAL " if expression.args.get("global") else "" 523 return f"{global_}{kind}{this}{expressions}{collate}"
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
- columndef_sql
- columnconstraint_sql
- autoincrementcolumnconstraint_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
- datatype_sql
- directory_sql
- delete_sql
- drop_sql
- except_sql
- except_op
- fetch_sql
- filter_sql
- hint_sql
- index_sql
- identifier_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
- insert_sql
- intersect_sql
- intersect_op
- introducer_sql
- pseudotype_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
- offset_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
- 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
- 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
- distance_sql
- dot_sql
- eq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- is_sql
- like_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