Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Reverse engineering FKs to a foreign table fails on MySQL #191

Open
iBeb opened this Issue Nov 17, 2011 · 5 comments

Comments

Projects
None yet
5 participants

iBeb commented Nov 17, 2011

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

Member

fzaninotto commented Nov 18, 2011

I've renamed your issue for clarity.

Owner

willdurand commented Nov 18, 2011

And I add syntax HL on the code :)

iBeb commented Jun 4, 2012

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...

willdurand added a commit that referenced this issue Sep 9, 2013

Merge pull request #191 from batruji/patch-1
Update documentation/whats-new.markdown

kix commented Apr 10, 2014

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

Member

jaugustin commented Apr 10, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment