Wade Leftwich wrote:
> Michael Bayer wrote:
>
>>ok, great. if you want to look at the code, its pretty easy, its lib/
>>sqlalchemy/databases/mysql.py line 163, "reflecttable". Right now its
>>just "describe", loop through rows, a little parsing, then
>>table.append_item(Column). thanks !
>>
>>- mike
>>
>
>
> See if this gets us at least partway down the road. Seems to work:
>
I had an immediate attack of staircase wit. Here's a simpler version
that also works.
-- Wade
Index: mysql.py
===================================================================
--- mysql.py (revision 915)
+++ mysql.py (working copy)
@@ -164,6 +164,9 @@
# to use information_schema:
#ischema.reflecttable(self, table, ischema_names, use_mysql=True)
+ tabletype, foreignkeyD = self.moretableinfo(table=table)
+ # XXX tabletype == 'InnoDB' or 'MyISAM', what do we do with it?
+
c = self.execute("describe " + table.name, {})
while True:
row = c.fetchone()
@@ -182,7 +185,38 @@
args = re.findall(r'(\d+)', args)
#print "args! " +repr(args)
coltype = coltype(*[int(a) for a in args])
- table.append_item(schema.Column(name, coltype, primary_key=primary_key, nullable=nullable, default=default))
+
+ arglist = []
+ fkey = foreignkeyD.get(name)
+ if fkey is not None:
+ arglist.append(schema.ForeignKey(fkey))
+
+ table.append_item(schema.Column(name, coltype, *arglist,
+ **dict(primary_key=primary_key,
+ nullable=nullable,
+ default=default
+ )))
+
+ def moretableinfo(self, table):
+ """Return (tabletype, {colname:foreignkey...}
+ execute(SHOW CREATE TABLE child) =>
+ 'CREATE TABLE `child` (\n `id` int(11) default NULL,\n `parent_id` int(11) default NULL,\n KEY `par_ind` (`parent_id`),\n CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE\n) TYPE=InnoDB'
+ """
+ c = self.execute("SHOW CREATE TABLE " + table.name, {})
+ desc = c.fetchone()[1].strip()
+ match = re.search(r'\)\s*TYPE=(?P<ttype>.+)$', desc, re.I)
+ if match:
+ tabletype = match.group('ttype')
+ else:
+ tabletype = ''
+ foreignkeyD = {}
+ fkpat = (r'FOREIGN KEY\s*\(`?(?P<name>.+?)`?\)'
+ r'\s*REFERENCES\s*`?(?P<reftable>.+?)`?'
+ r'\s*\(`?(?P<refcol>.+?)`?\)'
+ )
+ for match in re.finditer(fkpat, desc):
+ foreignkeyD[match.group('name')] = match.group('reftable') + '.' + match.group('refcol')
+ return (tabletype, foreignkeyD)
class MySQLTableImpl(sql.TableImpl):
