I looked into the AddConstraint class, and added some debugging info and
found it wasn't being hit, because the fk was part of a CREATE. So I'd need
to use @compiles to override both of those to ensure both of them render
the references correctly.
The single point where the magic happens is in compiler.py, in a function
called 'define_constraint_remote_table'. Can I use @compiles to override
that function?
Right now I have manually added the following to
dialects.mysql.base.MySQLDDLCompiler:
def define_constraint_remote_table(self, constraint, table, preparer):
"""Format the remote table clause of a CREATE CONSTRAINT clause.
If using InnoDB, tables without manually-provided schemas will
be formatted using the default connection schema when rendered
in foreign key statements. This is because InnoDB (or perhaps
mysql?) will interpret references without schemas as being in the
same schema as the table being created/altered """
engine_key = '%s_engine' % self.dialect.name
is_innodb = engine_key in table.kwargs and \
table.kwargs[engine_key].lower() == 'innodb'
if is_innodb and table.schema is None:
default_schema = table.bind.url.database
constraint_schema = constraint.columns[0].table.schema
if constraint_schema not in (default_schema, None):
""" if the constraint schema is not the default, we need to
add a schema before formatting the table """
table.schema = default_schema
value = preparer.format_table(table)
table.schema = None
return value
return preparer.format_table(table)
This does exactly what I want it to, and ensures that in any cases where
the referenced keys are in a different schema than the active table, the
keys are prefixed by the schema.
Is there a way to 'inject' this into the MySQLDDLCompiler?
Would this version of the formatting function not be more appropriate than
the current, as the current is unable to handle a simple 2 model relation
when they are in different schemas?
On Wednesday, August 21, 2013 2:52:41 PM UTC-7, Michael Bayer wrote:
>
>
> On Aug 21, 2013, at 5:37 PM, Gerald Thibault
> <[email protected]<javascript:>>
> wrote:
>
> To make it sqlalchemy specific, how do i cause generated CREATE statements
> to use absolute schema.table names for foreign key references?
>
>
> you either need to specify "schema" in your referenced Table def, or you'd
> otherwise have to intercept the AddConstraint construct using @compiles to
> inject the schema name that you'd want there.
>
>
>
> I realized the reason MyISAM had no issue with it was because it ignores
> all those lines, so even if they were wrong (which they seem to be), it
> wouldn't care.
>
> Here is the relevant output from SHOW ENGINE STATUS INNODB:
>
> ------------------------
> LATEST FOREIGN KEY ERROR
> ------------------------
> 130821 13:22:18 Error in foreign key constraint of table
> test2/registrations:
> FOREIGN KEY(user_id) REFERENCES user_vars (id)
> )ENGINE=InnoDB:
> Cannot resolve table name close to:
> (id)
> )ENGINE=InnoDB
>
> It seems the schema is mandatory when operating cross-schema.
>
> Is there a way to get this behavior from sqlalchemy?
>
> If MyISAM ignores the FK declarations, and InnoDB requires a schema in
> order to have cross-schema fk references, it seems like using schema.table
> format would fix this.
>
> On Wednesday, August 21, 2013 1:57:07 PM UTC-7, Michael Bayer wrote:
>>
>> you might try asking this as a generic MySQL question on stackoverflow, I
>> don't really know how MySQL does cross-schema work. my rough understanding
>> was "not much".
>>
>>
>>
>>
>> On Aug 21, 2013, at 4:17 PM, Gerald Thibault <[email protected]> wrote:
>>
>> I have a User class, and a Registration class with a FK to User.id.
>>
>> When I try to create these on a db using InnoDB as default, I get this
>> error:
>>
>> sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create
>> table 'test2.registrations' (errno: 150)") '\nCREATE TABLE
>> test2.registrations (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id
>> INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(user_id) REFERENCES
>> user_vars (id)\n)\n\n' ()
>>
>> If i copy this query and try it manually via MySQL Workbench, it still
>> fails.
>>
>> If I prepend the default schema to the table (user_vars ->
>> 'test.user_vars'), the query succeeds.
>>
>> I'm not entirely sure what is happening here. In the absence of an
>> explicit schema, I thought mysql used the active schema to handle table
>> lookups, but it looks like this isn't the case. Is it 'switching' the
>> active schema to the one hosting the new table? Also, this works perfectly
>> with MyISAM tables, so I have even less to go on.
>>
>> Any ideas? Do I need explicit schema declarations for every fk declared?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/groups/opt_out.
>> <innodb.py>
>>
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
