Using InnoDB Engine for ManyToMany Relationships
andrewwatts
And, I would like to use an InnoDB engine for all tables, but,
*without* modifying the default MySQL engine type.
I know this is possible with using_table_options
(mysql_engine="InnoDB") on most tables, however I am struggling to
find a way for tables created from ManyToMany relationships.
According to the Elixir Documentation[1]: "Behind the scene, the
ManyToMany relationship will automatically create an intermediate
table to host its data."
Is there a way to define the table options and an InnoDB engine for
the intermediate table automatically created by a ManyToMany
relationship? If not, any recommendations other than an "alter table"
command in mysql?
Thanks,
- Andrew
[1]: http://elixir.ematia.de/apidocs/elixir.relationships.html#manytomany
Gaetan de Menten
> Hello, I'm new to Elixir and SQLAlchemy and using them with MySQL.
> And, I would like to use an InnoDB engine for all tables, but,
> *without* modifying the default MySQL engine type.
>
> I know this is possible with using_table_options
> (mysql_engine="InnoDB") on most tables, however I am struggling to
> find a way for tables created from ManyToMany relationships.
> According to the Elixir Documentation[1]: "Behind the scene, the
> ManyToMany relationship will automatically create an intermediate
> table to host its data."
>
> Is there a way to define the table options and an InnoDB engine for
> the intermediate table automatically created by a ManyToMany
> relationship? If not, any recommendations other than an "alter table"
> command in mysql?
Ouch. This is the kind of problem Elixir is meant to solve and here it
just gets in the way. There is currently no way to do that. Adding an
argument like "table_kwargs" to ManyToMany would be dead easy and
would at least allow you to do what you want (albeit in an not very
elegant way), patch welcome.
The already nicer (IMO) way to fix this is to do it for all your
tables at once by modifying options_defaults as follow:
elixir.options_defaults['table_options'] = dict(mysql_engine="InnoDB")
Unfortunately that would also needs a small patch to work for the
tables created by the ManyToMany relationships (so that they also use
the default table options). Patch welcome too ;-).
These two patches are really entry-level patches, so if anybody always
wanted to hack on Elixir but didn't know of an easy task to tackle,
here is your chance !
Even if you don't want to write patches, please do create tickets for
these two features in our trac so that I keep them in sight.
The even nicer way to fix this (IMO), is to use a custom base class
with "using_options_defaults(table_options=...)", but this is a
feature which only exist in Elixir 0.7 (current svn).
In the mean time, as a workaround, which you can use right now, you
could simply not use a ManyToMany relationship at all and use the more
verbose Association Object pattern (ie map the intermediate table to
an explicit class), and the AssociationProxy SQLAlchemy plugin,
though that's clearly not an ideal solution. See
http://elixir.ematia.de/trac/wiki/Recipes/UsingEntityForOrderedList
for an example of AssociationProxy usage with Elixir.
Hope this helps,
--
Gaëtan de Menten
http://openhex.org
andrewwatts
On Sep 23, 8:25 am, Gaetan de Menten <gdemen...@gmail.com> wrote:
>
> Hope this helps,
> --
> Gaëtan de Mentenhttp://openhex.org
issue: http://elixir.ematia.de/trac/ticket/94. Unfortunately I don't
have the time to implement a patch right now, and being new to elixir
I don't know how easy is easy, but perhaps I'll get some time some
weekend.
I think for now, for this app, I'm going to note that mysql needs to
be configured for InnoDB by default.