On Thu, Aug 11, 2011 at 12:20 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> Vertical partitioning implies tables in separate databases or schemas.
> It seems like you have it set up such that a table in partition A can refer
> to a table in partition B using a schema qualifier. So if this is the case
> you'd use the "schema" argument on each Table to establish these names, when
> you do your CREATE, as well as schema-qualify the ForeignKey:
>
> t1 = Table("table_a", metadata, Column('id', Integer, primary_key=True),
> schema="schema_a")
> t2 = Table("table_b", metadata, Column('table_a_id', Integer, ForeignKey("
> schema_a.table_a.id")), schema="schema_b")
>
Your answers raise more questions :) So it looks like I can use schema
prefixing as above and avoid using vertical partitioning altogether.
Another option would be to forego the usage of ForeignKey for table
> relationships that span across two partitions, at least when CREATE TABLE is
> emitted. This is effectively what you were doing when you were on MyISAM,
> since REFERENCES is a no-op on MyISAM - it just gets thrown away hence you
> had no error.
>
> This is total conjecture since I don't know the details here nor do I know
> MySQL's performance characteristics very deeply, but the existence of actual
> cross-schema foreign key constraints in the MySQL database may be a
> performance issue, if writing to table B means a disk operation on an
> entirely separate schema for table A must occur in order to insert or update
> a row.
>
OK, let me try to understand this.
The sample vertical partitioning code (this is my basic test that works,
shown below, based on the sample code in the O'Reilly book) works correctly
with InnoDB. It appears to emit ForeignKey for table relationships when
CREATE TABLE is emitted. Is this code supposed to fail? It's working.
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:foo@localhost/car_fac')
engine2 = create_engine('mysql://car:foo@localhost/car_res')
engine1.echo = engine2.echo = True
metadata = MetaData()
product_table = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric),
mysql_engine='InnoDB')
product_summary_table = Table( 'product_summary', metadata,
Column('sku', String(20), ForeignKey('product.sku'),
primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
product_table.create(bind=engine1,checkfirst=True)
product_summary_table.create(bind=engine2,checkfirst=True)
class Product(object):
pass
class ProductSummary(object):
pass
mapper(ProductSummary, product_summary_table, properties=dict(
product=relation(Product, backref=backref('summary',
uselist=False))))
mapper(Product, product_table)
Session = sessionmaker(twophase=True)
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
As you can see, I'm using the same mysql account, but with two different
engines in a vertical partitioning configuration. MySQL is happy with the
foreign key relationships and creates the tables.
So... I don't understand your suggestion of not emitting ForeignKey at table
creation time. It appears to work in my basic test.
Shouldn't SQLA detect that the tables are vertically partitioned, treat the
ForeignKey relationships as NO-OPs to MySQL (because the tables may not be
able to *see* each other, since you could be using different MySQL accounts
with different permissions), and just use the ForeignKey definitions to help
set up the mappers properly?
I guess I don't understand the limitations/capabilities of vertical
partitioning in SQLA.
I have a more complex application that is basically doing the same thing as
this example code, actually using three engines to connect to three
different MySQL databases on the same server with the same account. This is
probably not the best way to do things, as schema prefixing would be better.
But as the above sample code shows, this *can* work. But my more complex app
is failing with the errno 105, which is what is confusing me. I can't figure
out the difference between my large application and this simple example, and
why the simple example works but my application does not, when they are
essentially doing the same thing. The sample code above emits SQL to MySQL
that defines the ForeignKey relationship and does not need a "database."
prefix. But my big app seems to need that "database." prefix. Maybe I have
the binds messed up?
-Daniel
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
