Hello
I am working with a legacy database and application design. I'm
running into trouble mapping composite objects, as there is a common
class that is persisted in different manners across many composite
tables.
I'm hoping to illustrate this problem with some Python code (below).
I respect anyone's suggestions for better table or class designs,
however ideally I'm hoping there's a way I can implement the composite
mapping so it does not interfere with the application's existing class
design nor table design.
I appreciate any help.
thanks!
Hal
from sqlalchemy import MetaData, create_engine, Table, Column, String,
Integer
from sqlalchemy.orm import sessionmaker, mapper, synonym, relation,
composite
"""
DESCRIPTION:
sqlalchemy test file to test mapping classes to composite tables
By design, this program will fail with:
AttributeError: 'Z' object has no attribute '__composite_values__'
OVERVIEW:
Z is a class that for database design reasons is persisted in
composite tables
objects that contain instances of Z only want to persist a subset of
Z's
attributes into the mapped tables
* A class mapped to table_A must only persist the z1 attribute from Z
* B class mapped to table_B must only persist the z2 attribute from Z
* C class mapped to table_C must persist both the z1 and z2 attributes
from Z
PROBLEM:
using sqlalchemy.orm.composite to map instances of Z to composite
tables
requires that Z define __init__, __composite_values__, and ideally
__set_composite_values__, and __eq__, which are specific to how Z is
mapped
This does not allow for Z to be mapped to multiple composite tables
if
each table persists Z differently
SOLUTION 1:
modify table_A and table_B to include all of Z's attributes and
subsequently modify Z
to define __init__, __composite_values__, __set_composite_values__,
and __eq__
as expected by sqlalchemy.orm.composite
--> Not optimal for database design, especially for legacy databases
or
cases where Z may contain large numbers of attributes
SOLUTION 2:
build separate Z classes depending on how Z will be persisted
--> Not optimal for class implementation
class Z_a(Z):
def __init__(self, z1):
Z.__init__(z1, None)
def __composite_values__(self):
return self.z1
class Z_b(Z):
def __init__(self, z2):
Z.__init__(None, z2)
def __composite_values__(self):
return self.z2
class Z_c(Z):
def __init__(self, z1, z2):
Z.__init__(z1, z2)
def __composite_values__(self):
return self.z1, self.z2
Then the application must be modified to instantiate the correct
Z subclass (bad for integration)
QUESTION:
Is there another solution to define the composite mapping separate
from
the application class design, so neither classes A, B, C, Z, nor
tables
table_A, table_B, table_C must be modified?
"""
### CODE BEGINS
#
# APPLICATION CLASSES
#
class A(object):
def __init__(self,a1,z1):
""" for application design A needs a Z only with z1 attribute
"""
self.a1 = a1
self.a_z = Z(z1,None)
class B(object):
def __init__(self,b1,z2):
""" for application design B needs a Z only with z2 attribute
"""
self.b1 = b1
self.b_z = Z(None,z2)
class C(object):
def __init__(self,c1,z1,z2):
""" for application design C needs a Z with both z1 and z2
attributes """
self.c1 = c1
self.c_z = Z(z1,z2)
class Z(object):
def __init__(self,z1,z2):
self.z1 = z1
self.z2 = z2
#
# METADATA
#
metadata = MetaData()
""" for optimum database design, table_A only needs z1 column """
composite_table_A = Table(
'table_a', metadata,
Column('id', Integer, primary_key=True),
Column('a1', String),
Column('z1', String)
)
""" for optimum database design, table_B only needs z2 column """
composite_table_B = Table(
'table_b', metadata,
Column('id', Integer, primary_key=True),
Column('b1', String),
Column('z2', String)
)
""" for optimum database design, Z's are best kept in composite tables
"""
composite_table_C = Table(
'table_c', metadata,
Column('id', Integer, primary_key=True),
Column('c1',String),
Column('z1',String),
Column('z2',String)
)
#
# MAPPING
#
mapper(A,composite_table_A,properties={
'a_z':composite(Z,composite_table_A.c.z1)})
mapper(B,composite_table_B,properties={
'b_z':composite(Z,composite_table_B.c.z2)})
mapper(C,composite_table_C,properties={
'c_z':composite(Z,composite_table_C.c.z1,composite_table_C.c.z2)})
#
# TEST CODE
#
if __name__ == "__main__":
engine = create_engine('sqlite:///:memory:',echo=True)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
a = A('aaa','a_z1')
b = B('bbb','b_z2')
c = C('ccc','c_z1','c_z2')
session.add_all([a,b,c])
session.commit()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
