[sqlalchemy] vertical partitioning

[Adrian von Bidder]

Heyho!

Is there a tutorial on vertical partitioning?

I have a table Entry and a table EntryFlags (1:1 relation from 
EntryFlags to Entry).  The idea is that while there is a large number of 
Entry rows only a small number has flags set (and thus needs an entry in 
EntryFlags; note that they don't need to be booleans, despite the name).

So having a separate table for the EntryFlags keeps the rows of Entry 
smaller and should also speed up if I select by certain flags.

How do I represent this in sqlalchemy?

Obviously I can trivially do the relation stuff to get entry.flags.myflag, 
but nicer would be having the columns from the EntryFlags table appear in 
Entry as if they were inline, returning a default value where an EntryFlags 
column doesn't exist.  (Bonus for removing the EntryFlags row if all values 
are back to default ;-)

Thanks in advance.

-- vbi


-- 
The use of COBOL cripples the mind; its teaching should, therefore, be
regarded as a criminal offence.
-- E. Dijkstra, 1975


signature.asc
Description: This is a digitally signed message part.

[sqlalchemy] Vertical partitioning, mappers and foreign keys

[Nathan Harmston]

HI,

I m currently trying to vertical partition my database and I have
split it into two groups of tables (say { documents, table1, table2 }
and { table3 }.
So I have

s_engine = create_engine(t_database_string)#, echo=True)
#echo=debug)#, echo=True)
t_engine = create_engine(t_database_string)

Base = declarative_base()

metadata = Base.metadata

documents = Table(documents', metadata,
  Column(document_id, Integer, primary_key = True),
}

table1 = Table(table1, metadata,
   Column(interval_id, Integer, primary_key=True),
  Column(document_id, Integer,
ForeignKey(documents.document_id)),
)

table3 = Table(table3, metadata,
 Column(foo_id, Integer, primary_key = True),
)

table2 = Table(table2, metadata,
 Column(id, Integer, primary_key = True),
 Column(interval_id, Integer, ForeignKey(table1.interval__id))
 Column(foo_id, Integer, ForeignKey(table3.foo_id))
)

secondary = table1.join(table2,
onclause=table1.c.interval_id==table2.c.interval_id).alias(spe_join)

table3.create(bind=t_engine)
 etc



mapper(Foo, table3)
mapper(Document, documents_table, properties={
  foos:relation(Foo,
primaryjoin=documents_table.c.document_id ==
secondary.c.intervals_document_id,

secondaryjoin=secondary.c.table2_foo_id == table3.c.foo_id,
   viewonly = True,

secondary=secondary)

 })
session = sessionmaker(binds={Document:s_engine, Foo:t_engine})

however what happens is that it complains about the foos relation in
that it says that
sqlalchemy.exc.ArgumentError: Could not determine relation direction
for secondaryjoin condition 'spe_join.table2_foo_id = foo.foo_id', on
relation Document.foos. Specify the foreign_keys argument to indicate
which columns on the relation are foreign.

When I had all the tables in the same database I had no problems
involving having to specify foreign keys. What does SQLalchemy do it
the background here? Is my problem that it doesnt know that table3 is
accessed via the t_engine. How can I make it explicitly know this
here? I have tried using different metadata bindings but to no good.
Any pointers/help would be greatly appreciated?

Nathan

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