Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/reflection.py: 13%

1# mysql/reflection.py

3# <see AUTHORS file>

5# This module is part of SQLAlchemy and is released under

6# the MIT License: https://www.opensource.org/licenses/mit-license.php

8import re

10from .enumerated import ENUM

11from .enumerated import SET

12from .types import DATETIME

13from .types import TIME

14from .types import TIMESTAMP

15from ... import log

16from ... import types as sqltypes

17from ... import util

20class ReflectedState(object):

21 """Stores raw information about a SHOW CREATE TABLE statement."""

23 def __init__(self):

24 self.columns = []

25 self.table_options = {}

26 self.table_name = None

27 self.keys = []

28 self.fk_constraints = []

29 self.ck_constraints = []

32@log.class_logger

33class MySQLTableDefinitionParser(object):

34 """Parses the results of a SHOW CREATE TABLE statement."""

36 def __init__(self, dialect, preparer):

37 self.dialect = dialect

38 self.preparer = preparer

39 self._prep_regexes()

41 def parse(self, show_create, charset):

42 state = ReflectedState()

43 state.charset = charset

44 for line in re.split(r"\r?\n", show_create):

45 if line.startswith(" " + self.preparer.initial_quote):

46 self._parse_column(line, state)

47 # a regular table options line

48 elif line.startswith(") "):

49 self._parse_table_options(line, state)

50 # an ANSI-mode table options line

51 elif line == ")":

52 pass

53 elif line.startswith("CREATE "):

54 self._parse_table_name(line, state)

55 # Not present in real reflection, but may be if

56 # loading from a file.

57 elif not line:

58 pass

59 else:

60 type_, spec = self._parse_constraints(line)

61 if type_ is None:

62 util.warn("Unknown schema content: %r" % line)

63 elif type_ == "key":

64 state.keys.append(spec)

65 elif type_ == "fk_constraint":

66 state.fk_constraints.append(spec)

67 elif type_ == "ck_constraint":

68 state.ck_constraints.append(spec)

69 else:

70 pass

71 return state

73 def _check_view(self, sql):

74 return bool(self._re_is_view.match(sql))

76 def _parse_constraints(self, line):

77 """Parse a KEY or CONSTRAINT line.

79 :param line: A line of SHOW CREATE TABLE output

80 """

82 # KEY

83 m = self._re_key.match(line)

84 if m:

85 spec = m.groupdict()

86 # convert columns into name, length pairs

87 # NOTE: we may want to consider SHOW INDEX as the

88 # format of indexes in MySQL becomes more complex

89 spec["columns"] = self._parse_keyexprs(spec["columns"])

90 if spec["version_sql"]:

91 m2 = self._re_key_version_sql.match(spec["version_sql"])

92 if m2 and m2.groupdict()["parser"]:

93 spec["parser"] = m2.groupdict()["parser"]

94 if spec["parser"]:

95 spec["parser"] = self.preparer.unformat_identifiers(

96 spec["parser"]

97 )[0]

98 return "key", spec

100 # FOREIGN KEY CONSTRAINT

101 m = self._re_fk_constraint.match(line)

102 if m:

103 spec = m.groupdict()

104 spec["table"] = self.preparer.unformat_identifiers(spec["table"])

105 spec["local"] = [c[0] for c in self._parse_keyexprs(spec["local"])]

106 spec["foreign"] = [

107 c[0] for c in self._parse_keyexprs(spec["foreign"])

108 ]

109 return "fk_constraint", spec

110

111 # CHECK constraint

112 m = self._re_ck_constraint.match(line)

113 if m:

114 spec = m.groupdict()

115 return "ck_constraint", spec

116

117 # PARTITION and SUBPARTITION

118 m = self._re_partition.match(line)

119 if m:

120 # Punt!

121 return "partition", line

122

123 # No match.

124 return (None, line)

125

126 def _parse_table_name(self, line, state):

127 """Extract the table name.

128

129 :param line: The first line of SHOW CREATE TABLE

130 """

131

132 regex, cleanup = self._pr_name

133 m = regex.match(line)

134 if m:

135 state.table_name = cleanup(m.group("name"))

136

137 def _parse_table_options(self, line, state):

138 """Build a dictionary of all reflected table-level options.

139

140 :param line: The final line of SHOW CREATE TABLE output.

141 """

142

143 options = {}

144

145 if not line or line == ")":

146 pass

147

148 else:

149 rest_of_line = line[:]

150 for regex, cleanup in self._pr_options:

151 m = regex.search(rest_of_line)

152 if not m:

153 continue

154 directive, value = m.group("directive"), m.group("val")

155 if cleanup:

156 value = cleanup(value)

157 options[directive.lower()] = value

158 rest_of_line = regex.sub("", rest_of_line)

159

160 for nope in ("auto_increment", "data directory", "index directory"):

161 options.pop(nope, None)

162

163 for opt, val in options.items():

164 state.table_options["%s_%s" % (self.dialect.name, opt)] = val

165

166 def _parse_column(self, line, state):

167 """Extract column details.

168

169 Falls back to a 'minimal support' variant if full parse fails.

170

171 :param line: Any column-bearing line from SHOW CREATE TABLE

172 """

173

174 spec = None

175 m = self._re_column.match(line)

176 if m:

177 spec = m.groupdict()

178 spec["full"] = True

179 else:

180 m = self._re_column_loose.match(line)

181 if m:

182 spec = m.groupdict()

183 spec["full"] = False

184 if not spec:

185 util.warn("Unknown column definition %r" % line)

186 return

187 if not spec["full"]:

188 util.warn("Incomplete reflection of column definition %r" % line)

189

190 name, type_, args = spec["name"], spec["coltype"], spec["arg"]

191

192 try:

193 col_type = self.dialect.ischema_names[type_]

194 except KeyError:

195 util.warn(

196 "Did not recognize type '%s' of column '%s'" % (type_, name)

197 )

198 col_type = sqltypes.NullType

199

200 # Column type positional arguments eg. varchar(32)

201 if args is None or args == "":

202 type_args = []

203 elif args[0] == "'" and args[-1] == "'":

204 type_args = self._re_csv_str.findall(args)

205 else:

206 type_args = [int(v) for v in self._re_csv_int.findall(args)]

207

208 # Column type keyword options

209 type_kw = {}

210

211 if issubclass(col_type, (DATETIME, TIME, TIMESTAMP)):

212 if type_args:

213 type_kw["fsp"] = type_args.pop(0)

214

215 for kw in ("unsigned", "zerofill"):

216 if spec.get(kw, False):

217 type_kw[kw] = True

218 for kw in ("charset", "collate"):

219 if spec.get(kw, False):

220 type_kw[kw] = spec[kw]

221 if issubclass(col_type, (ENUM, SET)):

222 type_args = _strip_values(type_args)

223

224 if issubclass(col_type, SET) and "" in type_args:

225 type_kw["retrieve_as_bitwise"] = True

226

227 type_instance = col_type(*type_args, **type_kw)

228

229 col_kw = {}

230

231 # NOT NULL

232 col_kw["nullable"] = True

233 # this can be "NULL" in the case of TIMESTAMP

234 if spec.get("notnull", False) == "NOT NULL":

235 col_kw["nullable"] = False

236

237 # AUTO_INCREMENT

238 if spec.get("autoincr", False):

239 col_kw["autoincrement"] = True

240 elif issubclass(col_type, sqltypes.Integer):

241 col_kw["autoincrement"] = False

242

243 # DEFAULT

244 default = spec.get("default", None)

245

246 if default == "NULL":

247 # eliminates the need to deal with this later.

248 default = None

249

250 comment = spec.get("comment", None)

251

252 if comment is not None:

253 comment = comment.replace("\\\\", "\\").replace("''", "'")

254

255 sqltext = spec.get("generated")

256 if sqltext is not None:

257 computed = dict(sqltext=sqltext)

258 persisted = spec.get("persistence")

259 if persisted is not None:

260 computed["persisted"] = persisted == "STORED"

261 col_kw["computed"] = computed

262

263 col_d = dict(

264 name=name, type=type_instance, default=default, comment=comment

265 )

266 col_d.update(col_kw)

267 state.columns.append(col_d)

268

269 def _describe_to_create(self, table_name, columns):

270 """Re-format DESCRIBE output as a SHOW CREATE TABLE string.

271

272 DESCRIBE is a much simpler reflection and is sufficient for

273 reflecting views for runtime use. This method formats DDL

274 for columns only- keys are omitted.

275

276 :param columns: A sequence of DESCRIBE or SHOW COLUMNS 6-tuples.

277 SHOW FULL COLUMNS FROM rows must be rearranged for use with

278 this function.

279 """

280

281 buffer = []

282 for row in columns:

283 (name, col_type, nullable, default, extra) = [

284 row[i] for i in (0, 1, 2, 4, 5)

285 ]

286

287 line = [" "]

288 line.append(self.preparer.quote_identifier(name))

289 line.append(col_type)

290 if not nullable:

291 line.append("NOT NULL")

292 if default:

293 if "auto_increment" in default:

294 pass

295 elif col_type.startswith("timestamp") and default.startswith(

296 "C"

297 ):

298 line.append("DEFAULT")

299 line.append(default)

300 elif default == "NULL":

301 line.append("DEFAULT")

302 line.append(default)

303 else:

304 line.append("DEFAULT")

305 line.append("'%s'" % default.replace("'", "''"))

306 if extra:

307 line.append(extra)

308

309 buffer.append(" ".join(line))

310

311 return "".join(

312 [

313 (

314 "CREATE TABLE %s (\n"

315 % self.preparer.quote_identifier(table_name)

316 ),

317 ",\n".join(buffer),

318 "\n) ",

319 ]

320 )

321

322 def _parse_keyexprs(self, identifiers):

323 """Unpack '"col"(2),"col" ASC'-ish strings into components."""

324

325 return self._re_keyexprs.findall(identifiers)

326

327 def _prep_regexes(self):

328 """Pre-compile regular expressions."""

329

330 self._re_columns = []

331 self._pr_options = []

332

333 _final = self.preparer.final_quote

334

335 quotes = dict(

336 zip(

337 ("iq", "fq", "esc_fq"),

338 [

339 re.escape(s)

340 for s in (

341 self.preparer.initial_quote,

342 _final,

343 self.preparer._escape_identifier(_final),

344 )

345 ],

346 )

347 )

348

349 self._pr_name = _pr_compile(

350 r"^CREATE (?:\w+ +)?TABLE +"

351 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +\($" % quotes,

352 self.preparer._unescape_identifier,

353 )

354

355 self._re_is_view = _re_compile(r"^CREATE(?! TABLE)(\s.*)?\sVIEW")

356

357 # `col`,`col2`(32),`col3`(15) DESC

358 #

359 self._re_keyexprs = _re_compile(

360 r"(?:"

361 r"(?:%(iq)s((?:%(esc_fq)s|[^%(fq)s])+)%(fq)s)"

362 r"(?:$(\d+)$)?(?: +(ASC|DESC))?(?=\,|$))+" % quotes

363 )

364

365 # 'foo' or 'foo','bar' or 'fo,o','ba''a''r'

366 self._re_csv_str = _re_compile(r"\x27(?:\x27\x27|[^\x27])*\x27")

367

368 # 123 or 123,456

369 self._re_csv_int = _re_compile(r"\d+")

370

371 # `colname` <type> [type opts]

372 # (NOT NULL | NULL)

373 # DEFAULT ('value' | CURRENT_TIMESTAMP...)

374 # COMMENT 'comment'

375 # COLUMN_FORMAT (FIXED|DYNAMIC|DEFAULT)

376 # STORAGE (DISK|MEMORY)

377 self._re_column = _re_compile(

378 r" "

379 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"

380 r"(?P<coltype>\w+)"

381 r"(?:\((?P<arg>(?:\d+|\d+,\d+|"

382 r"(?:'(?:''|[^'])*',?)+))\))?"

383 r"(?: +(?P<unsigned>UNSIGNED))?"

384 r"(?: +(?P<zerofill>ZEROFILL))?"

385 r"(?: +CHARACTER SET +(?P<charset>[\w_]+))?"

386 r"(?: +COLLATE +(?P<collate>[\w_]+))?"

387 r"(?: +(?P<notnull>(?:NOT )?NULL))?"

388 r"(?: +DEFAULT +(?P<default>"

389 r"(?:NULL|'(?:''|[^'])*'|[\-\w\.]+"

390 r"(?: +ON UPDATE [\-\w\.]+)?)"

391 r"))?"

392 r"(?: +(?:GENERATED ALWAYS)? ?AS +(?P<generated>\("

393 r".*\))? ?(?P<persistence>VIRTUAL|STORED)?)?"

394 r"(?: +(?P<autoincr>AUTO_INCREMENT))?"

395 r"(?: +COMMENT +'(?P<comment>(?:''|[^'])*)')?"

396 r"(?: +COLUMN_FORMAT +(?P<colfmt>\w+))?"

397 r"(?: +STORAGE +(?P<storage>\w+))?"

398 r"(?: +(?P<extra>.*))?"

399 r",?$" % quotes

400 )

401

402 # Fallback, try to parse as little as possible

403 self._re_column_loose = _re_compile(

404 r" "

405 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"

406 r"(?P<coltype>\w+)"

407 r"(?:$(?P<arg>(?:\d+|\d+,\d+|\x27(?:\x27\x27|[^\x27])+\x27))$)?"

408 r".*?(?P<notnull>(?:NOT )NULL)?" % quotes

409 )

410

411 # (PRIMARY|UNIQUE|FULLTEXT|SPATIAL) INDEX `name` (USING (BTREE|HASH))?

412 # (`col` (ASC|DESC)?, `col` (ASC|DESC)?)

413 # KEY_BLOCK_SIZE size | WITH PARSER name /*!50100 WITH PARSER name */

414 self._re_key = _re_compile(

415 r" "

416 r"(?:(?P<type>\S+) )?KEY"

417 r"(?: +%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s)?"

418 r"(?: +USING +(?P<using_pre>\S+))?"

419 r" +$(?P<columns>.+?)$"

420 r"(?: +USING +(?P<using_post>\S+))?"

421 r"(?: +KEY_BLOCK_SIZE *[ =]? *(?P<keyblock>\S+))?"

422 r"(?: +WITH PARSER +(?P<parser>\S+))?"

423 r"(?: +COMMENT +(?P<comment>(\x27\x27|\x27([^\x27])*?\x27)+))?"

424 r"(?: +/\*(?P<version_sql>.+)\*/ *)?"

425 r",?$" % quotes

426 )

427

428 # https://forums.mysql.com/read.php?20,567102,567111#msg-567111

429 # It means if the MySQL version >= \d+, execute what's in the comment

430 self._re_key_version_sql = _re_compile(

431 r"\!\d+ " r"(?: *WITH PARSER +(?P<parser>\S+) *)?"

432 )

433

434 # CONSTRAINT `name` FOREIGN KEY (`local_col`)

435 # REFERENCES `remote` (`remote_col`)

436 # MATCH FULL | MATCH PARTIAL | MATCH SIMPLE

437 # ON DELETE CASCADE ON UPDATE RESTRICT

438 #

439 # unique constraints come back as KEYs

440 kw = quotes.copy()

441 kw["on"] = "RESTRICT|CASCADE|SET NULL|NO ACTION"

442 self._re_fk_constraint = _re_compile(

443 r" "

444 r"CONSTRAINT +"

445 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"

446 r"FOREIGN KEY +"

447 r"$(?P<local>[^$]+?)\) REFERENCES +"

448 r"(?P<table>%(iq)s[^%(fq)s]+%(fq)s"

449 r"(?:\.%(iq)s[^%(fq)s]+%(fq)s)?) +"

450 r"$(?P<foreign>[^$]+?)\)"

451 r"(?: +(?P<match>MATCH \w+))?"

452 r"(?: +ON DELETE (?P<ondelete>%(on)s))?"

453 r"(?: +ON UPDATE (?P<onupdate>%(on)s))?" % kw

454 )

455

456 # CONSTRAINT `CONSTRAINT_1` CHECK (`x` > 5)'

457 # testing on MariaDB 10.2 shows that the CHECK constraint

458 # is returned on a line by itself, so to match without worrying

459 # about parenthesis in the expression we go to the end of the line

460 self._re_ck_constraint = _re_compile(

461 r" "

462 r"CONSTRAINT +"

463 r"%(iq)s(?P<name>(?:%(esc_fq)s|[^%(fq)s])+)%(fq)s +"

464 r"CHECK +"

465 r"$(?P<sqltext>.+)$,?" % kw

466 )

467

468 # PARTITION

469 #

470 # punt!

471 self._re_partition = _re_compile(r"(?:.*)(?:SUB)?PARTITION(?:.*)")

472

473 # Table-level options (COLLATE, ENGINE, etc.)

474 # Do the string options first, since they have quoted

475 # strings we need to get rid of.

476 for option in _options_of_type_string:

477 self._add_option_string(option)

478

479 for option in (

480 "ENGINE",

481 "TYPE",

482 "AUTO_INCREMENT",

483 "AVG_ROW_LENGTH",

484 "CHARACTER SET",

485 "DEFAULT CHARSET",

486 "CHECKSUM",

487 "COLLATE",

488 "DELAY_KEY_WRITE",

489 "INSERT_METHOD",

490 "MAX_ROWS",

491 "MIN_ROWS",

492 "PACK_KEYS",

493 "ROW_FORMAT",

494 "KEY_BLOCK_SIZE",

495 ):

496 self._add_option_word(option)

497

498 self._add_option_regex("UNION", r"$[^$]+\)")

499 self._add_option_regex("TABLESPACE", r".*? STORAGE DISK")

500 self._add_option_regex(

501 "RAID_TYPE",

502 r"\w+\s+RAID_CHUNKS\s*\=\s*\w+RAID_CHUNKSIZE\s*=\s*\w+",

503 )

504

505 _optional_equals = r"(?:\s*(?:=\s*)|\s+)"

506

507 def _add_option_string(self, directive):

508 regex = r"(?P<directive>%s)%s" r"'(?P<val>(?:[^']|'')*?)'(?!')" % (

509 re.escape(directive),

510 self._optional_equals,

511 )

512 self._pr_options.append(

513 _pr_compile(

514 regex, lambda v: v.replace("\\\\", "\\").replace("''", "'")

515 )

516 )

517

518 def _add_option_word(self, directive):

519 regex = r"(?P<directive>%s)%s" r"(?P<val>\w+)" % (

520 re.escape(directive),

521 self._optional_equals,

522 )

523 self._pr_options.append(_pr_compile(regex))

524

525 def _add_option_regex(self, directive, regex):

526 regex = r"(?P<directive>%s)%s" r"(?P<val>%s)" % (

527 re.escape(directive),

528 self._optional_equals,

529 regex,

530 )

531 self._pr_options.append(_pr_compile(regex))

532

533

534_options_of_type_string = (

535 "COMMENT",

536 "DATA DIRECTORY",

537 "INDEX DIRECTORY",

538 "PASSWORD",

539 "CONNECTION",

540)

541

542

543def _pr_compile(regex, cleanup=None):

544 """Prepare a 2-tuple of compiled regex and callable."""

545

546 return (_re_compile(regex), cleanup)

547

548

549def _re_compile(regex):

550 """Compile a string to regex, I and UNICODE."""

551

552 return re.compile(regex, re.I | re.UNICODE)

553

554

555def _strip_values(values):

556 "Strip reflected values quotes"

557 strip_values = []

558 for a in values:

559 if a[0:1] == '"' or a[0:1] == "'":

560 # strip enclosing quotes and unquote interior

561 a = a[1:-1].replace(a[0] * 2, a[0])

562 strip_values.append(a)

563 return strip_values