not working on mysqldump

[PikMaster]

Hi

I have the following SQL dump produced using mysqldump 5.1.73:
Command used:
mysqldump --add-drop-table -d --skip-lock-tables -h ${db_host} -P ${db_port} -u ${db_user} -p${db_pass} {$db_name}

This is the dump:

CREATE TABLE `active_directory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hostname` varchar(100) COLLATE utf8_bin NOT NULL,
  `path` varchar(200) COLLATE utf8_bin NOT NULL COMMENT 'human friendly path, ie. AA/BB/Computers',
  `domain` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'DNS FQDN, ie. domain.com',
  `dn` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'LDAP distinguished name, ie. CN=HOST,OU=Computers,OU=AA,OU=BB,DC=domain,DC=com',
  `os` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'ie. Windows 7 Enterprise SP1',
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `hostname_fqdn` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'for faster index without CONCAT',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hostname_domain` (`hostname`,`domain`) USING BTREE,
  KEY `hostname_idx` (`hostname`),
  KEY `domain` (`domain`),
  KEY `hostname_fqdn_idx` (`hostname_fqdn`)
  CONSTRAINT `dns_servers_zones_ibfk_1` FOREIGN KEY (`domain`) REFERENCES `dns_zones` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `dns_servers_zones_ibfk_2` FOREIGN KEY (`hostname`) REFERENCES `dns_servers` (`hostname`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4566624 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='computer objects from active directory';

CREATE TABLE `other_table` (
  `id2` int(11) NOT NULL AUTO_INCREMENT,
  `hostname2` varchar(100) COLLATE utf8_bin NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=4566624 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

For that parser produces nothing (just the initial line: digraph g { graph [ rankdir = "LR" ];

So I came with this enhancement of table definition, which at least detects the tables properly (it accomodates for extra commands after the closing ) parenthesis for table definition:
create_table_def = Literal("CREATE") + "TABLE" + tablename_def.setResultsName("tableName") + "(" + field_list_def.setResultsName("fields") + ")" + ZeroOrMore (CharsNotIn(";")) + ";"

But for foreign keys it is still not detecting them.
I don't know pyparsing grammar too much, but from experiments, I was able to get this to detect a single foreign key:

mysql_fkey_def = Literal("CONSTRAINT") + Word(alphanums + "_`").setResultsName("tableName") + "FOREIGN" + "KEY" + "(" + Word(alphanums + "_`").setResultsName("keyName") + ")" + "REFERENCES" + Word(alphanums + "_`").setResultsName("fkTable") + "(" + Word(alphanums + "_`").setResultsName("fkCol") + ")" + Optional(Literal("ON") + "DELETE" + Word(alphanums) ) + Optional(Literal("ON") + "UPDATE" + Word(alphanums + ",") )

But I cannot integrate it into field definition, I thought of this which doesn't work - they are still detected as normal fields

field_def = mysql_fkey_def | OneOrMore(Word(alphanums + "_\"'`:-") | parenthesis)

Some problems with this:

1. sometimes there are more than 1 foreign key, as in my example above you have 2, but you can have zero as well
2. name of the table is not on the row where foreign key is defined. You would have to reach back somehow to the table name that was parsed (I guess it's doable for someone who knows pyparsing.py)
3. action for the printing the foreign key relation to other database need to be deferred until you completed parsing the table, so it is not part of the "label" section for GraphViz.

I don't know how to do it, that's why I'm posting it here, hoping that someone who knows could do it, based on my input and example MySQL dump.

Best Regards