Reverse engineering FKs to a foreign table fails on MySQL

[iBeb]

I have two databases and some tables have foreign keys across the two databases. From what I've read, I thought I could generate a schema.xml referencing foreign keys from a different database, but I couldn't make it work.

Here's the SQL code for the creation one one table with an external foreign key (I've kept only major columns):

CREATE TABLE `user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(11) unsigned NOT NULL,
  `contact_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `u_group_id` (`group_id`),
  KEY `u_contact_id` (`contact_id`),
  CONSTRAINT `u_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `dream`.`contact` (`contact_id`),
  CONSTRAINT `u_group_id` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Reading the MysqlSchemaParser.php, I've noticed that the regex

<?php

$regEx = '/CONSTRAINT `([^`]+)` FOREIGN KEY \((.+)\) REFERENCES `([^`]*)` \((.+)\)(.*)/';

works perfectly for tables within the same schema but excludes a reference to an external table.

I tried to find discussions about that, unsuccessfully.

I'd be glad to have an explanation about that, because I might have misunderstood something.

_bertrand

[fzaninotto]

I've renamed your issue for clarity.

[willdurand]

And I add syntax HL on the code :)

[iBeb]

I had some time today to think about it. By changing the regex and a few lines, it could work. But...

I've realized the problem is much deeper: First, $foreignTable->getSchema() returns nothing even for tables within the same schema. Then, $database->getTable($ftbl, true); returns nothing for a foreign table from a different schema.

I really can't find any information about reverse engineering across multiple databases/schemas.

That's really an issue for me: I have a big set of 5 databases including between 20 and 60 tables, and some are connected. In MySQL, foreign keys between different schemas are allowed and work perfectly. And I'm losing them with Propel...

[kix]

Are there any updates on this issue? Should we expect this to be implemented?

[jaugustin]

In propel 1 no, but if someone send a pull request on Propel2 maybe,