Constraint can't reference different database

[mindspin311]

Seems to be an issue referencing a table in a different db.

cconnors@dev1:~/Modyllic$ scripts/modyllic diff --debug --verbose mysql:host=dev1.nanigans.com:dbname=library:username=aa:password=bb mysql:host=dev1.nanigans.com:dbname=library:username=aa:password=bb
Expected one of '(', got Modyllic_Token_Symbol:'.' while parsing SQL in library.table_view_configurations on line 16 at col 105:

MP,
  PRIMARY KEY (`id`),
  KEY `portal_user_id` (`portal_user_id`),
  KEY `app_id` (`app_id`),
  CONSTRAINT `table_view_configurations_ibfk_1` FOREIGN KEY (`portal_user_id`) REFERENCES `nan_central`.<---HERE--->`portal_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `table_view_configurations_ibfk_2` FOREIGN KEY (`app_id`) REFERENCES `nan_central`.`app_settings` (`app_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFA
#0 /home/cconnors/Modyllic/Modyllic/Parser.php(1389): Modyllic_Parser->error('Expected one of...')
#1 /home/cconnors/Modyllic/Modyllic/Parser.php(1370): Modyllic_Parser->assert_symbol('(')
#2 /home/cconnors/Modyllic/Modyllic/Parser.php(1190): Modyllic_Parser->get_symbol('(')
#3 /home/cconnors/Modyllic/Modyllic/Parser.php(1103): Modyllic_Parser->load_foreign_key('CONSTRAINT')
#4 /home/cconnors/Modyllic/Modyllic/Parser.php(870): Modyllic_Parser->load_key()
#5 /home/cconnors/Modyllic/Modyllic/Parser.php(187): Modyllic_Parser->cmd_CREATE_TABLE(Array)
#6 /home/cconnors/Modyllic/Modyllic/Parser.php(137): Modyllic_Parser->cmd_CREATE()
#7 /home/cconnors/Modyllic/Modyllic/Parser.php(56): Modyllic_Parser->parse_command()
#8 /home/cconnors/Modyllic/Modyllic/Loader/DB/MySQL.php(111): Modyllic_Parser->partial(Object(Modyllic_Schema), 'CREATE TABLE `t...', 'library.table_v...')
#9 [internal function]: Modyllic_Loader_DB_MySQL::load(Object(PDO), 'library', Object(Modyllic_Schema))
#10 /home/cconnors/Modyllic/Modyllic/Loader/DB.php(88): call_user_func(Array, Object(PDO), 'library', Object(Modyllic_Schema))
#11 [internal function]: Modyllic_Loader_DB::load('mysql:host=dev1...', Object(Modyllic_Schema))
#12 /home/cconnors/Modyllic/Modyllic/Loader.php(51): call_user_func(Array, 'mysql:host=dev1...', Object(Modyllic_Schema))
#13 /home/cconnors/Modyllic/Modyllic/CommandLine.php(103): Modyllic_Loader::load(Array)
#14 /home/cconnors/Modyllic/scripts/modyllic-diff(86): Modyllic_CommandLine::schema(Array)
#15 {main}

[mindspin311]

Think this fixes it (Note the lines might be off due to alot of local mods in this file). Wasn't sure if you had a better way to handle this (haven't commit anything), but it seems to solve the optional database.tablename format.

@@ -1185,7 +1213,12 @@ class Modyllic_Parser {
             $this->get_reserved();
             $key->weak = true;
         }
+
+        // Handle the '{database}.{table}' format
         $key->references['table'] = $this->get_ident();
+        if ($this->maybe('.')) {
+          $key->references['table'] .= '.' . $this->get_ident();
+        }

[mindspin311]

Fixed in my branch, but couldn't commit to yours. Says I don't have permission. I can just commit to mine and you can merge in changes from that one if you want my fixes.

[iarna]

Make a branch in your fork with the change, then a pull request for it. Pull requests are located in the icon just below the issues icon. Alternatively, when you're viewing your fork it should show you the recently created branch with a pull request button.

We never (almost) never commit to OnlineBuddies/Modyllic– with the exception of releases, it's a merge-only branch. Merges are never done by the person who made the change, they're always done via pull request and someone else reviews the changes and accepts them or asks for changes.

[iarna]

My suspicion is that this patch will still produce errors, just not parse errors. I would expect it to produce an error of this kind:

$constraintname references the table $tablename which does not exist.

These show up at the top of dumps and diffs and will halt migrations unless you --force them.

Modyllic is distinctly lacking in cross-schema support. Ideally you'd be able to have it load multiple schema and thus it'd be able to validate this sort of cross schema link. That being said though, it'd require not insubstantial refactoring.

One interim measure might be to add a database key to Modyllic_Schema_Index_Foreign and populate that (rather then cramming both into the table name) and then skip validation if that's set (or, perhaps, if it differs from $schema->name).