codekingpro/portable-devtools
114k
1# dialects/mysql/reflection.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9
10import re
11
12from .enumerated import ENUM
13from .enumerated import SET
14from .types import DATETIME
15from .types import TIME
16from .types import TIMESTAMP
17from ... import log
18from ... import types as sqltypes
19from ... import util
20
21
22class ReflectedState:
23 """Stores raw information about a SHOW CREATE TABLE statement."""
24
25 def __init__(self):
26 self.columns = []
27 self.table_options = {}
28 self.table_name = None
29 self.keys = []
30 self.fk_constraints = []
31 self.ck_constraints = []
32
33
34@log.class_logger
35class MySQLTableDefinitionParser:
36 """Parses the results of a SHOW CREATE TABLE statement."""
37
38 def __init__(self, dialect, preparer):
39 self.dialect = dialect
40 self.preparer = preparer
41 self._prep_regexes()
42
43 def parse(self, show_create, charset):
44 state = ReflectedState()
45 state.charset = charset
46 for line in re.split(r"\r?\n", show_create):
47 if line.startswith(" " + self.preparer.initial_quote):
48 self._parse_column(line, state)
49 # a regular table options line
50 elif line.startswith(") "):
51 self._parse_table_options(line, state)
52 # an ANSI-mode table options line
53 elif line == ")":
54 pass
55 elif line.startswith("CREATE "):
56 self._parse_table_name(line, state)
57 elif "PARTITION" in line:
58 self._parse_partition_options(line, state)
59 # Not present in real reflection, but may be if
60 # loading from a file.
61 elif not line:
62 pass
63 else:
64 type_, spec = self._parse_constraints(line)
65 if type_ is None:
66 util.warn("Unknown schema content: %r" % line)
67 elif type_ == "key":
68 state.keys.append(spec)
69 elif type_ == "fk_constraint":
70 state.fk_constraints.append(spec)
71 elif type_ == "ck_constraint":
72 state.ck_constraints.append(spec)
73 else:
74 pass
75 return state
76
77 def _check_view(self, sql: str) -> bool:
78 return bool(self._re_is_view.match(sql))
79
80 def _parse_constraints(self, line):
81 """Parse a KEY or CONSTRAINT line.
82
83 :param line: A line of SHOW CREATE TABLE output
84 """
85
86 # KEY
87 m = self._re_key.match(line)
88 if m:
89 spec = m.groupdict()
90 # convert columns into name, length pairs
91 # NOTE: we may want to consider SHOW INDEX as the
92 # format of indexes in MySQL becomes more complex
93 spec["columns"] = self._parse_keyexprs(spec["columns"])
94 if spec["version_sql"]:
95 m2 = self._re_key_version_sql.match(spec["version_sql"])
96 if m2 and m2.groupdict()["parser"]:
97 spec["parser"] = m2.groupdict()["parser"]
98 if spec["parser"]:
99 spec["parser"] = self.preparer.unformat_identifiers(
100 spec["parser"]
101 )[0]
102 return "key", spec
103
104 # FOREIGN KEY CONSTRAINT
105 m = self._re_fk_constraint.match(line)
106 if m:
107 spec = m.groupdict()
108 spec["table"] = self.preparer.unformat_identifiers(spec["table"])
109 spec["local"] = [c[0] for c in self._parse_keyexprs(spec["local"])]
110 spec["foreign"] = [
111 c[0] for c in self._parse_keyexprs(spec["foreign"])
112 ]
113 return "fk_constraint", spec
114
115 # CHECK constraint
116 m = self._re_ck_constraint.match(line)
117 if m:
118 spec = m.groupdict()
119 return "ck_constraint", spec
120
121 # PARTITION and SUBPARTITION
122 m = self._re_partition.match(line)
123 if m:
124 # Punt!
125 return "partition", line
126
127 # No match.
128 return (None, line)
129
130 def _parse_table_name(self, line, state):
131 """Extract the table name.
132
133 :param line: The first line of SHOW CREATE TABLE
134 """
135
136 regex, cleanup = self._pr_name
137 m = regex.match(line)
138 if m:
139 state.table_name = cleanup(m.group("name"))
140
141 def _parse_table_options(self, line, state):
142 """Build a dictionary of all reflected table-level options.
143
144 :param line: The final line of SHOW CREATE TABLE output.
145 """
146
147 options = {}
148
149 if line and line != ")":
150 rest_of_line = line
151 for regex, cleanup in self._pr_options:
152 m = regex.search(rest_of_line)
153 if not m:
154 continue
155 directive, value = m.group("directive"), m.group("val")
156 if cleanup:
157 value = cleanup(value)
158 options[directive.lower()] = value
159 rest_of_line = regex.sub("", rest_of_line)
160
161 for nope in ("auto_increment", "data directory", "index directory"):
162 options.pop(nope, None)
163
164 for opt, val in options.items():
165 state.table_options["%s_%s" % (self.dialect.name, opt)] = val
166
167 def _parse_partition_options(self, line, state):
168 options = {}
169 new_line = line[:]
170
171 while new_line.startswith("(") or new_line.startswith(" "):
172 new_line = new_line[1:]
173
174 for regex, cleanup in self._pr_options:
175 m = regex.search(new_line)
176 if not m or "PARTITION" not in regex.pattern:
177 continue
178
179 directive = m.group("directive")
180 directive = directive.lower()
181 is_subpartition = directive == "subpartition"
182
183 if directive == "partition" or is_subpartition:
184 new_line = new_line.replace(") */", "")
185 new_line = new_line.replace(",", "")
186 if is_subpartition and new_line.endswith(")"):
187 new_line = new_line[:-1]
188 if self.dialect.name == "mariadb" and new_line.endswith(")"):
189 if (
190 "MAXVALUE" in new_line
191 or "MINVALUE" in new_line
192 or "ENGINE" in new_line
193 ):
194 # final line of MariaDB partition endswith ")"
195 new_line = new_line[:-1]
196
197 defs = "%s_%s_definitions" % (self.dialect.name, directive)
198 options[defs] = new_line
199
200 else:
201 directive = directive.replace(" ", "_")
202 value = m.group("val")
203 if cleanup:
204 value = cleanup(value)
205 options[directive] = value
206 break
207
208 for opt, val in options.items():
209 part_def = "%s_partition_definitions" % (self.dialect.name)
210 subpart_def = "%s_subpartition_definitions" % (self.dialect.name)
211 if opt == part_def or opt == subpart_def:
212 # builds a string of definitions
213 if opt not in state.table_options:
214 state.table_options[opt] = val
215 else:
216 state.table_options[opt] = "%s, %s" % (
217 state.table_options[opt],
218 val,
219 )
220 else:
221 state.table_options["%s_%s" % (self.dialect.name, opt)] = val
222
223 def _parse_column(self, line, state):
224 """Extract column details.
225
226 Falls back to a 'minimal support' variant if full parse fails.
227
228 :param line: Any column-bearing line from SHOW CREATE TABLE
229 """
230
231 spec = None
232 m = self._re_column.match(line)
233 if m:
234 spec = m.groupdict()
235 spec["full"] = True
236 else:
237 m = self._re_column_loose.match(line)
238 if m:
239 spec = m.groupdict()
240 spec["full"] = False
241 if not spec:
242 util.warn("Unknown column definition %r" % line)
243 return
244 if not spec["full"]:
245 util.warn("Incomplete reflection of column definition %r" % line)
246
247 name, type_, args = spec["name"], spec["coltype"], spec["arg"]
248
249 try:
250 col_type = self.dialect.ischema_names[type_]
251 except KeyError:
252 util.warn(
253 "Did not recognize type '%s' of column '%s'" % (type_, name)
254 )
255 col_type = sqltypes.NullType
256
257 # Column type positional arguments eg. varchar(32)
258 if args is None or args == "":
259 type_args = []
260 elif args[0] == "'" and args[-1] == "'":
261 type_args = self._re_csv_str.findall(args)
262 else:
263 type_args = [int(v) for v in self._re_csv_int.findall(args)]
264
265 # Column type keyword options
266 type_kw = {}
267
268 if issubclass(col_type, (DATETIME, TIME, TIMESTAMP)):
269 if type_args:
270 type_kw["fsp"] = type_args.pop(0)
271
272 for kw in ("unsigned", "zerofill"):
273 if spec.get(kw, False):
274 type_kw[kw] = True
275 for kw in ("charset", "collate"):
276 if spec.get(kw, False):
277 type_kw[kw] = spec[kw]
278 if issubclass(col_type, (ENUM, SET)):
279 type_args = _strip_values(type_args)
280
281 if issubclass(col_type, SET) and "" in type_args:
282 type_kw["retrieve_as_bitwise"] = True
283
284 type_instance = col_type(*type_args, **type_kw)
285
286 col_kw = {}
287
288 # NOT NULL
289 col_kw["nullable"] = True
290 # this can be "NULL" in the case of TIMESTAMP
291 if spec.get("notnull", False) == "NOT NULL":
292 col_kw["nullable"] = False
293 # For generated columns, the nullability is marked in a different place
294 if spec.get("notnull_generated", False) == "NOT NULL":
295 col_kw["nullable"] = False
296
297 # AUTO_INCREMENT
298 if spec.get("autoincr", False):
299 col_kw["autoincrement"] = True
300 elif issubclass(col_type, sqltypes.Integer):
301 col_kw["autoincrement"] = False
302
303 # DEFAULT
304 default = spec.get("default", None)
305
306 if default == "NULL":
307 # eliminates the need to deal with this later.
308 default = None
309
310 comment = spec.get("comment", None)
311
312 if comment is not None:
313 comment = cleanup_text(comment)
314
315 sqltext = spec.get("generated")
316 if sqltext is not None:
317 computed = dict(sqltext=sqltext)
318 persisted = spec.get("persistence")
319 if persisted is not None:
320 computed["persisted"] = persisted == "STORED"
321 col_kw["computed"] = computed
322
323 col_d = dict(
324 name=name, type=type_instance, default=default, comment=comment
325 )
326 col_d.update(col_kw)
327 state.columns.append(col_d)
328
329 def _describe_to_create(self, table_name, columns):
330 """Re-format DESCRIBE output as a SHOW CREATE TABLE string.
331
332 DESCRIBE is a much simpler reflection and is sufficient for
333 reflecting views for runtime use. This method formats DDL
334 for columns only- keys are omitted.
335
336 :param columns: A sequence of DESCRIBE or SHOW COLUMNS 6-tuples.
337 SHOW FULL COLUMNS FROM rows must be rearranged for use with
338 this function.
339 """
340
341 buffer = []
342 for row in columns:
343 (name, col_type, nullable, default, extra) = (
344 row[i] for i in (0, 1, 2, 4, 5)
345 )
346
347 line = [" "]
348 line.append(self.preparer.quote_identifier(name))
349 line.append(col_type)
350 if not nullable:
351 line.append("NOT NULL")
352 if default:
353 if "auto_increment" in default:
354 pass
355 elif col_type.startswith("timestamp") and default.startswith(
356 "C"
357 ):
358 line.append("DEFAULT")
359 line.append(default)
360 elif default == "NULL":
361 line.append("DEFAULT")
362 line.append(default)
363 else:
364 line.append("DEFAULT")
365 line.append("'%s'" % default.replace("'", "''"))
366 if extra:
367 line.append(extra)
368
369 buffer.append(" ".join(line))
370
371 return "".join(
372 [
373 (
374 "CREATE TABLE %s (\n"
375 % self.preparer.quote_identifier(table_name)
376 ),
377 ",\n".join(buffer),
378 "\n) ",
379 ]
380 )
381
382 def _parse_keyexprs(self, identifiers):
383 """Unpack '"col"(2),"col" ASC'-ish strings into components."""
384
385 return [
386 (colname, int(length) if length else None, modifiers)
387 for colname, length, modifiers in self._re_keyexprs.findall(
388 identifiers
389 )
390 ]
391
392 def _prep_regexes(self):
393 """Pre-compile regular expressions."""
394
395 self._re_columns = []
396 self._pr_options = []
397
398 _final = self.preparer.final_quote
399
400 quotes = dict(
401 zip(
402 ("iq", "fq", "esc_fq"),
403 [
404 re.escape(s)
405 for s in (
406 self.preparer.initial_quote,
407 _final,
408 self.preparer._escape_identifier(_final),
409 )
410 ],
411 )
412 )
413
414 self._pr_name = _pr_compile(
415 r"^CREATE (?:\w+ +)?TABLE +"
416 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +\($" % quotes,
417 self.preparer._unescape_identifier,
418 )
419
420 self._re_is_view = _re_compile(r"^CREATE(?! TABLE)(\s.*)?\sVIEW")
421
422 # `col`,`col2`(32),`col3`(15) DESC
423 #
424 self._re_keyexprs = _re_compile(
425 r"(?:"
426 r"(?:%(iq)s((?:%(esc_fq)s|[^%(fq)s])+)%(fq)s)"
427 r"(?:\((\d+)\))?(?: +(ASC|DESC))?(?=\,|$))+" % quotes
428 )
429
430 # 'foo' or 'foo','bar' or 'fo,o','ba''a''r'
431 self._re_csv_str = _re_compile(r"\x27(?:\x27\x27|[^\x27])*\x27")
432
433 # 123 or 123,456
434 self._re_csv_int = _re_compile(r"\d+")
435
436 # `colname` <type> [type opts]
437 # (NOT NULL | NULL)
438 # DEFAULT ('value' | CURRENT_TIMESTAMP...)
439 # COMMENT 'comment'
440 # COLUMN_FORMAT (FIXED|DYNAMIC|DEFAULT)
441 # STORAGE (DISK|MEMORY)
442 self._re_column = _re_compile(
443 r" "
444 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"
445 r"(?P<coltype>\w+)"
446 r"(?:\((?P<arg>(?:\d+|\d+,\d+|"
447 r"(?:'(?:''|[^'])*',?)+))\))?"
448 r"(?: +(?P<unsigned>UNSIGNED))?"
449 r"(?: +(?P<zerofill>ZEROFILL))?"
450 r"(?: +CHARACTER SET +(?P<charset>[\w_]+))?"
451 r"(?: +COLLATE +(?P<collate>[\w_]+))?"
452 r"(?: +(?P<notnull>(?:NOT )?NULL))?"
453 r"(?: +DEFAULT +(?P<default>"
454 r"(?:NULL|'(?:''|[^'])*'|[\-\w\.\(\)]+"
455 r"(?: +ON UPDATE [\-\w\.\(\)]+)?)"
456 r"))?"
457 r"(?: +(?:GENERATED ALWAYS)? ?AS +(?P<generated>\("
458 r".*\))? ?(?P<persistence>VIRTUAL|STORED)?"
459 r"(?: +(?P<notnull_generated>(?:NOT )?NULL))?"
460 r")?"
461 r"(?: +(?P<autoincr>AUTO_INCREMENT))?"
462 r"(?: +COMMENT +'(?P<comment>(?:''|[^'])*)')?"
463 r"(?: +COLUMN_FORMAT +(?P<colfmt>\w+))?"
464 r"(?: +STORAGE +(?P<storage>\w+))?"
465 r"(?: +(?P<extra>.*))?"
466 r",?$" % quotes
467 )
468
469 # Fallback, try to parse as little as possible
470 self._re_column_loose = _re_compile(
471 r" "
472 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"
473 r"(?P<coltype>\w+)"
474 r"(?:\((?P<arg>(?:\d+|\d+,\d+|\x27(?:\x27\x27|[^\x27])+\x27))\))?"
475 r".*?(?P<notnull>(?:NOT )NULL)?" % quotes
476 )
477
478 # (PRIMARY|UNIQUE|FULLTEXT|SPATIAL) INDEX `name` (USING (BTREE|HASH))?
479 # (`col` (ASC|DESC)?, `col` (ASC|DESC)?)
480 # KEY_BLOCK_SIZE size | WITH PARSER name /*!50100 WITH PARSER name */
481 self._re_key = _re_compile(
482 r" "
483 r"(?:(?P<type>\S+) )?KEY"
484 r"(?: +%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s)?"
485 r"(?: +USING +(?P<using_pre>\S+))?"
486 r" +\((?P<columns>.+?)\)"
487 r"(?: +USING +(?P<using_post>\S+))?"
488 r"(?: +KEY_BLOCK_SIZE *[ =]? *(?P<keyblock>\S+))?"
489 r"(?: +WITH PARSER +(?P<parser>\S+))?"
490 r"(?: +COMMENT +(?P<comment>(\x27\x27|\x27([^\x27])*?\x27)+))?"
491 r"(?: +/\*(?P<version_sql>.+)\*/ *)?"
492 r",?$" % quotes
493 )
494
495 # https://forums.mysql.com/read.php?20,567102,567111#msg-567111
496 # It means if the MySQL version >= \d+, execute what's in the comment
497 self._re_key_version_sql = _re_compile(
498 r"\!\d+ " r"(?: *WITH PARSER +(?P<parser>\S+) *)?"
499 )
500
501 # CONSTRAINT `name` FOREIGN KEY (`local_col`)
502 # REFERENCES `remote` (`remote_col`)
503 # MATCH FULL | MATCH PARTIAL | MATCH SIMPLE
504 # ON DELETE CASCADE ON UPDATE RESTRICT
505 #
506 # unique constraints come back as KEYs
507 kw = quotes.copy()
508 kw["on"] = "RESTRICT|CASCADE|SET NULL|NO ACTION"
509 self._re_fk_constraint = _re_compile(
510 r" "
511 r"CONSTRAINT +"
512 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"
513 r"FOREIGN KEY +"
514 r"\((?P<local>[^\)]+?)\) REFERENCES +"
515 r"(?P<table>%(iq)s[^%(fq)s]+%(fq)s"
516 r"(?:\.%(iq)s[^%(fq)s]+%(fq)s)?) +"
517 r"\((?P<foreign>(?:%(iq)s[^%(fq)s]+%(fq)s(?: *, *)?)+)\)"
518 r"(?: +(?P<match>MATCH \w+))?"
519 r"(?: +ON DELETE (?P<ondelete>%(on)s))?"
520 r"(?: +ON UPDATE (?P<onupdate>%(on)s))?" % kw
521 )
522
523 # CONSTRAINT `CONSTRAINT_1` CHECK (`x` > 5)'
524 # testing on MariaDB 10.2 shows that the CHECK constraint
525 # is returned on a line by itself, so to match without worrying
526 # about parenthesis in the expression we go to the end of the line
527 self._re_ck_constraint = _re_compile(
528 r" "
529 r"CONSTRAINT +"
530 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"
531 r"CHECK +"
532 r"\((?P<sqltext>.+)\),?" % kw
533 )
534
535 # PARTITION
536 #
537 # punt!
538 self._re_partition = _re_compile(r"(?:.*)(?:SUB)?PARTITION(?:.*)")
539
540 # Table-level options (COLLATE, ENGINE, etc.)
541 # Do the string options first, since they have quoted
542 # strings we need to get rid of.
543 for option in _options_of_type_string:
544 self._add_option_string(option)
545
546 for option in (
547 "ENGINE",
548 "TYPE",
549 "AUTO_INCREMENT",
550 "AVG_ROW_LENGTH",
551 "CHARACTER SET",
552 "DEFAULT CHARSET",
553 "CHECKSUM",
554 "COLLATE",
555 "DELAY_KEY_WRITE",
556 "INSERT_METHOD",
557 "MAX_ROWS",
558 "MIN_ROWS",
559 "PACK_KEYS",
560 "ROW_FORMAT",
561 "KEY_BLOCK_SIZE",
562 "STATS_SAMPLE_PAGES",
563 ):
564 self._add_option_word(option)
565
566 for option in (
567 "PARTITION BY",
568 "SUBPARTITION BY",
569 "PARTITIONS",
570 "SUBPARTITIONS",
571 "PARTITION",
572 "SUBPARTITION",
573 ):
574 self._add_partition_option_word(option)
575
576 self._add_option_regex("UNION", r"\([^\)]+\)")
577 self._add_option_regex("TABLESPACE", r".*? STORAGE DISK")
578 self._add_option_regex(
579 "RAID_TYPE",
580 r"\w+\s+RAID_CHUNKS\s*\=\s*\w+RAID_CHUNKSIZE\s*=\s*\w+",
581 )
582
583 _optional_equals = r"(?:\s*(?:=\s*)|\s+)"
584
585 def _add_option_string(self, directive):
586 regex = r"(?P<directive>%s)%s" r"'(?P<val>(?:[^']|'')*?)'(?!')" % (
587 re.escape(directive),
588 self._optional_equals,
589 )
590 self._pr_options.append(_pr_compile(regex, cleanup_text))
591
592 def _add_option_word(self, directive):
593 regex = r"(?P<directive>%s)%s" r"(?P<val>\w+)" % (
594 re.escape(directive),
595 self._optional_equals,
596 )
597 self._pr_options.append(_pr_compile(regex))
598
599 def _add_partition_option_word(self, directive):
600 if directive == "PARTITION BY" or directive == "SUBPARTITION BY":
601 regex = r"(?<!\S)(?P<directive>%s)%s" r"(?P<val>\w+.*)" % (
602 re.escape(directive),
603 self._optional_equals,
604 )
605 elif directive == "SUBPARTITIONS" or directive == "PARTITIONS":
606 regex = r"(?<!\S)(?P<directive>%s)%s" r"(?P<val>\d+)" % (
607 re.escape(directive),
608 self._optional_equals,
609 )
610 else:
611 regex = r"(?<!\S)(?P<directive>%s)(?!\S)" % (re.escape(directive),)
612 self._pr_options.append(_pr_compile(regex))
613
614 def _add_option_regex(self, directive, regex):
615 regex = r"(?P<directive>%s)%s" r"(?P<val>%s)" % (
616 re.escape(directive),
617 self._optional_equals,
618 regex,
619 )
620 self._pr_options.append(_pr_compile(regex))
621
622
623_options_of_type_string = (
624 "COMMENT",
625 "DATA DIRECTORY",
626 "INDEX DIRECTORY",
627 "PASSWORD",
628 "CONNECTION",
629)
630
631
632def _pr_compile(regex, cleanup=None):
633 """Prepare a 2-tuple of compiled regex and callable."""
634
635 return (_re_compile(regex), cleanup)
636
637
638def _re_compile(regex):
639 """Compile a string to regex, I and UNICODE."""
640
641 return re.compile(regex, re.I | re.UNICODE)
642
643
644def _strip_values(values):
645 "Strip reflected values quotes"
646 strip_values = []
647 for a in values:
648 if a[0:1] == '"' or a[0:1] == "'":
649 # strip enclosing quotes and unquote interior
650 a = a[1:-1].replace(a[0] * 2, a[0])
651 strip_values.append(a)
652 return strip_values
653
654
655def cleanup_text(raw_text: str) -> str:
656 if "\\" in raw_text:
657 raw_text = re.sub(
658 _control_char_regexp, lambda s: _control_char_map[s[0]], raw_text
659 )
660 return raw_text.replace("''", "'")
661
662
663_control_char_map = {
664 "\\\\": "\\",
665 "\\0": "\0",
666 "\\a": "\a",
667 "\\b": "\b",
668 "\\t": "\t",
669 "\\n": "\n",
670 "\\v": "\v",
671 "\\f": "\f",
672 "\\r": "\r",
673 # '\\e':'\e',
674}
675_control_char_regexp = re.compile(
676 "|".join(re.escape(k) for k in _control_char_map)
677)
678