mysql "show create table" uses quotes in foreign key clauses; sqlalchemy expects backticks

[Former user]

Here's an actual create table string from a mysql 5.0.24 database (mysql-standard-5.0.24a-osx10.4-powerpc), all tables using innodb tables:

CREATE TABLE "l_document_type" (
  "id" int(11) NOT NULL,
  "source_cd" varchar(8) NOT NULL,
  "short_desc" varchar(64) NOT NULL,
  "long_desc" text,
  "type_cd" varchar(16) NOT NULL,
  PRIMARY KEY  ("id"),
  UNIQUE KEY "type_cd" ("type_cd"),
  KEY "fk_document_source" ("source_cd"),
  CONSTRAINT "l_document_type_ibfk_1" FOREIGN KEY ("source_cd") REFERENCES "document_source" ("cd")

In sqlalchemy.databases.mysql, line 425 (sqlalchemy v0.3.3, easy_install updated today), this regular expression is being used to find foreign keys within that create table statement output:

fkpat = r'CONSTRAINT `(?P<name>.+?)` FOREIGN KEY \((?P<columns>.+?)\) REFERENCES `(?P<reftable>.+?)` \((?P<refcols>.+?)\)'

Note that the regex is looking for backticks around the name and reftable elements of the match. This results in all sqlalchemy tables missing foreign key relationships (i.e. an empty set at dbsoup.some_table_name.table.foreign_keys).

This is an easy fix. Change the regex to match on any quoting character (["']("')), and all's well:

fkpat = r**CONSTRAINT [`"'](`"')(?P<name>.+?)[`"'](`"') FOREIGN KEY \((?P<columns>.+?)\) REFERENCES [`"'](`"')(?P<reftable>.+?)[`"'](`"') \((?P<refcols>.+?)\)**

I don't know what configuration controls how mysql outputs create table statements, but I do know that I haven't changed any such configuration -- it would seem that this is default mysql behaviour (at least for this build).

[Mike Bayer]

very weird. im running Ver 5.0.16-standard for apple-darwin8.2.0 on powerpc and I get:

CREATE TABLE `engine_email_addresses` (
  `address_id` int(11) NOT NULL auto_increment,
  `remote_user_id` int(11) default NULL,
  `email_address` varchar(20) default NULL,
  PRIMARY KEY  (`address_id`),
  KEY `remote_user_id` (`remote_user_id`),
  CONSTRAINT `engine_email_addresses_ibfk_1` FOREIGN KEY (`remote_user_id`) REFERENCES `engine_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

with the backticks. also this code is a year old and has been used by many, so this is the first issue we've had like this. so its not very clear what the "default" behavior is.

also you need to update the quoting style in two more regexps below the one you mention; all this code is checked in to changeset:2196, and continues to pass my tests, however you should verify that it works with your build.

[Former user]

Thanks very much -- I've confirmed proper functionality based on a build from ca92a81191a8ac4627e16838c8f6c1a8300291dc.