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