[sqlalchemy] Re: polymorphic_identity determination
On Nov 8, 2006, at 12:04 AM, Randall Smith wrote: That leads to the part I'm stuck on; mapper inheritance. When finished, session.query(Employee).select() should list all employee as instances of their specific classes and session.query(Engineer).select() should list all engineers ... So how do I set up the mappers to accomplish this? The polymorphic_on/polymorphic_identity method seems to only accommodate 1 level of inheritance. The Engineer mapper will inherit from the Employee mapper, but will have no polymorphic_identity. beacuse the polymorphic union is manually constructed, if you want multi-level loading you have to construct individual polymorphic unions for each class from which you want to load polymorphically, and specify it as the select_table argument for each mapper that requires polymorphic loading. when you say query(Employee).select(), it uses the mapper assigned to Employee and the select_table which you specified. when you say query(Engineer).select(), the Employee mapper is not used; it uses the Engineer mapper and the select_table specified on *that* mapper. it *is* in the cards that the polymorphic_union query will probably become more automated in a future release. the polymorphic_union function itself is the first version of this automation and im sort of road testing just that level of automation to start with. for your other questions: 1. a mapper inheriting from another mapper is mostly significant with regards to the persistence of object instances, i.e. during flush(), since there is an entire set of dependencies between inheriting classes. with regards to selecting and populating, an inherited mapper inherits the properties of the parent mapper, i.e. the column- mapped attributes as well as the relation()'s which you have attached to the parent mapper. when you add the polymorphic_identity into the mix, an additional set of logic occurs during the _instance() method on mapper, which is the method that receives a row and produces an object instance. it just looks at the polymorphic_on column in each incoming row and delegates the instance construction to the appropriate mapper. so the loading of polymorphic entities can be totally understood by just looking at the queries issued, and then looking at the rows that come back. each row needs some kind of identifying type in order to determine the class to be instantiated. 2. the concrete flag refers to an inheriting mapper that stores its entire set of data in its own table, and does not need any columns from the parent mapper's table in order to map an instance. so to load a particular class of entity involves querying only one table, and no join is required. this flag is only meaningful in inheritance relationships. I just noticed that one of my docstrings in the constructor of the Mapper class is incorrect regarding local_table and concrete as well, in case that was confusing you. it should refer to single table inheritance. 3. select_table should be used when you want polymorphic loading from a particular mapper, and the inheritance relationship spans over more tables than just what is defined in that mapper. this means you use it for joined table and concrete inheritance, but not single table. if a set of mappers mixes the approaches, then youd probably want to use select_table. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
I worked on this some more and found that the queries are correct as
they are. The problem is in the mapping. A Chemical Engineer is an
Engineer, who is an Employee. So the employees selectable should return
all employees and the engineer selectable should return all engineers,
not just unspecific engineers.
For the purpose of discussion and example, assume all engineers must be
specified. They may be chemical or mechanical engineers. I think the
polymorphic union should look like this:
p_union = polymorphic_union(
{
'ac': accountants,
'me': mechanical_engineers,
'ce': chemical_engineers,
},
type,
)
Notice that I didn't include the employee table nor the engineer
selectable. That's because I don't want instances of the Employee class
or the Engineer class. They would cause redundant data since a Chemical
Engineer is an Engineer is an Employee, etc.
That leads to the part I'm stuck on; mapper inheritance. When finished,
session.query(Employee).select() should list all employee as instances
of their specific classes and session.query(Engineer).select() should
list all engineers ... So how do I set up the mappers to accomplish
this? The polymorphic_on/polymorphic_identity method seems to only
accommodate 1 level of inheritance. The Engineer mapper will inherit
from the Employee mapper, but will have no polymorphic_identity.
Summary of design:
* Single employees table with ac_info, ce_info, and me_info columns.
* Selectables for each employee type that looks like this:
engineers = select([employees, ],
and_(employees.c.engineer_info != None,
employees.c.cheme_info==None)).alias('engineers')
chemical_engineers = select([employees,
column('ce').label('type')],
and_(employees.c.engineer_info != None,
employees.c.ce_info != None)).alias('chemical_engineers')
* Polymorphic Union that only includes types for instances we want
(No Employee or Engineer) types.
So how could mappers be set up to accommodate these criteria?
Randall
Michael Bayer wrote:
if you change your echo to 'debug', or just select straight from your
p_union selectable, youll see these rows:
(5, u'cengineer1', u'cengineer1', u'cengineer1', None,
u'chemical_engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None,
u'chemical_engineer')
(1, u'manager1', None, None, u'manager1', u'manager')
(2, u'manager2', None, None, u'manager2', u'manager')
(3, u'engineer1', u'engineer1', None, None, u'engineer')
(4, u'engineer2', u'engineer2', None, None, u'engineer')
(5, u'cengineer1', u'cengineer1', u'cengineer1', None, u'engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None, u'engineer')
Where you can see that the chemical engineers are coming out twice with
inconsistent types. the query has to be tuned to be more specific:
managers = select([employees, column('manager').label('type')],
employees.c.manager_data !=
None).alias('managers')
engineers = select([employees, column('engineer').label('type')],
and_(employees.c.engineer_info !=
None,
employees.c.cheme_info==None)).alias('engineers')
chemical_engineers = select([employees,
column('chemical_engineer').label('type')],
and_(employees.c.engineer_info != None,
employees.c.cheme_info !=
None)).alias(
'chemical_engineers')
p_union = polymorphic_union(
{
'engineer': engineers,
'manager': managers,
'chemical_engineer': chemical_engineers
},
None,
)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
hey, no, youre doing great, breaking new ground yourself. i havent spent much time with inheritance scenarios other than what you see in the examples and unittests (and a lot of that other people came up with). just as long as the rowsets contain the information, things are good. this is also why i have the polymorphic union as a separate, manually-configured query - I dont trust that an automated system of generating those is going to be smart enough just yet. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
I've attached a file which is a variant to the employees example with
two objectives.
1. Base polymorphic_identity on select criteria (no type column).
2. Use two levels of inheritance.
The first objective seems to be met, but the second is not working
properly. I put in two Managers, two Generic Engineers and two Chemical
Engineers (which inherit from Engineer). When I select from employees,
I get eight records. The Chemical Engineers are included once as
Chemical Engineers and once and Generic Engineers.
How might this be better written to meet these objectives?
Randall
Michael Bayer wrote:
just FYI, the type column idea is taken from Hibernate, and that's
all Hibernate supports as far as polymorphic loading.
But for polymorphic loading in SA, you are free to make any kind of
polymorphic_union you like that can add in a functionally-generated
type column, and specify it into select_table. im pretty sure this
should work completely right now, such as:
import sqlalchemy.sql as sql
person_join = polymorphic_union(
{
'engineer':sql.select([people.join(engineers),
sql.column('engineer').label('type')]),
'manager':sql.select([people.join(managers),
sql.column('manager').label('type')]),
}
)
etc.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
metadata = DynamicMetaData('testdata')
employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('manager_data', String(50)),
Column('engineer_info', String(50)),
Column('cheme_info', String(50)),
)
managers = select([employees], employees.c.manager_data !=
None).alias('managers')
engineers = select([employees], employees.c.engineer_info !=
None).alias('engineers')
chemical_engineers = select([employees],
and_(employees.c.engineer_info != None,
employees.c.cheme_info != None)).alias(
'chemical_engineers')
class Employee(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return self.__class__.__name__ + + self.name
class Manager(Employee):
def __init__(self, name, manager_data):
self.name = name
self.manager_data = manager_data
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.manager_data
class Engineer(Employee):
def __init__(self, name, engineer_info):
self.name = name
self.engineer_info = engineer_info
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.engineer_info
class ChemicalEngineer(Engineer):
def __init__(self, name, engineer_info, cheme_info):
self.name = name
self.engineer_info = engineer_info
self.cheme_info = cheme_info
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.engineer_info
p_union = polymorphic_union(
{
'engineer': engineers,
'manager': managers,
'chemical_engineer': chemical_engineers
},
'type'
)
employee_mapper = mapper(Employee, p_union, polymorphic_on=p_union.c.type)
manager_mapper = mapper(Manager, managers, inherits=employee_mapper,
concrete=True, polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers, inherits=employee_mapper,
concrete=True, polymorphic_identity='engineer')
mapper(ChemicalEngineer, chemical_engineers, inherits=engineer_mapper,
concrete=True, polymorphic_identity='chemical_engineer')
def populate(session):
m1 = Manager('manager1', 'manager1')
m2 = Manager('manager2', 'manager2')
e1 = Engineer('engineer1', 'engineer1')
e2 = Engineer('engineer2', 'engineer2')
ce1 = ChemicalEngineer('cengineer1', 'cengineer1', 'cengineer1')
ce2 = ChemicalEngineer('cengineer2', 'cengineer2', 'cengineer2')
for o in (m1, m2, e1, e2, ce1, ce2):
session.save(o)
session.flush()
if __name__ == '__main__':
engine = create_engine('sqlite:///test.db')
engine.echo = True
metadata.connect(engine)
metadata.drop_all()
metadata.create_all()
session = create_session(engine)
populate(session)
print session.query(Employee).select()
[sqlalchemy] Re: polymorphic_identity determination
if you change your echo to 'debug', or just select straight from your
p_union selectable, youll see these rows:
(5, u'cengineer1', u'cengineer1', u'cengineer1', None,
u'chemical_engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None,
u'chemical_engineer')
(1, u'manager1', None, None, u'manager1', u'manager')
(2, u'manager2', None, None, u'manager2', u'manager')
(3, u'engineer1', u'engineer1', None, None, u'engineer')
(4, u'engineer2', u'engineer2', None, None, u'engineer')
(5, u'cengineer1', u'cengineer1', u'cengineer1', None, u'engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None, u'engineer')
Where you can see that the chemical engineers are coming out twice with
inconsistent types. the query has to be tuned to be more specific:
managers = select([employees, column('manager').label('type')],
employees.c.manager_data !=
None).alias('managers')
engineers = select([employees, column('engineer').label('type')],
and_(employees.c.engineer_info !=
None,
employees.c.cheme_info==None)).alias('engineers')
chemical_engineers = select([employees,
column('chemical_engineer').label('type')],
and_(employees.c.engineer_info != None,
employees.c.cheme_info !=
None)).alias(
'chemical_engineers')
p_union = polymorphic_union(
{
'engineer': engineers,
'manager': managers,
'chemical_engineer': chemical_engineers
},
None,
)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
Randall Smith wrote: For discussion, consider the Employee, Manager, Engineer example from the docs. If I were designing the tables, I would not normally have a type field. I would use the null status of engineer_info or manager_data to determine the employee type. Or if that data was in separate tables, I would use the existence of records in those tables. And these are simple cases. I can think of some real life cases where the values of multiple fields would determine the class. IMO you always need to keep your people/place/things data separate from your roles data. If you put the 2 together you end up developing a system that is highly inflexible and the code will start to look ugly. After all a user can wear many hats. He can be an Employee, Manager, Engineer, etc. He can even be an Employee of 2 different companies, a manager of multiple groups, etc. So I would keep user tables/classes separate from role tables/classes. For the database design you would have 2 basic choices. First option: User Table userID ... Role Table userID roleType roleData ... Where roleData would likely contain serialized (pickled) data. Second option: User Table id ... Role Table id userID roleType roleDetailsID ... Role1Details Table id ... Role2Details Table id ... other role details tables I would only go with the second option if you actually needed to do ad hoc queries of the Role#Details tables. Otherwise, the first option is far quicker to code and provides more flexibility. John --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
John, Thanks for the feedback. The code I put up is not part of any real system. I'm just playing off of the existing examples in the docs and trying to get comfortable with SA inheritance. Randall John M Camara wrote: Randall Smith wrote: For discussion, consider the Employee, Manager, Engineer example from the docs. If I were designing the tables, I would not normally have a type field. I would use the null status of engineer_info or manager_data to determine the employee type. Or if that data was in separate tables, I would use the existence of records in those tables. And these are simple cases. I can think of some real life cases where the values of multiple fields would determine the class. IMO you always need to keep your people/place/things data separate from your roles data. If you put the 2 together you end up developing a system that is highly inflexible and the code will start to look ugly. After all a user can wear many hats. He can be an Employee, Manager, Engineer, etc. He can even be an Employee of 2 different companies, a manager of multiple groups, etc. So I would keep user tables/classes separate from role tables/classes. For the database design you would have 2 basic choices. First option: User Table userID ... Role Table userID roleType roleData ... Where roleData would likely contain serialized (pickled) data. Second option: User Table id ... Role Table id userID roleType roleDetailsID ... Role1Details Table id ... Role2Details Table id ... other role details tables I would only go with the second option if you actually needed to do ad hoc queries of the Role#Details tables. Otherwise, the first option is far quicker to code and provides more flexibility. John --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
just FYI, the type column idea is taken from Hibernate, and that's
all Hibernate supports as far as polymorphic loading.
But for polymorphic loading in SA, you are free to make any kind of
polymorphic_union you like that can add in a functionally-generated
type column, and specify it into select_table. im pretty sure this
should work completely right now, such as:
import sqlalchemy.sql as sql
person_join = polymorphic_union(
{
'engineer':sql.select([people.join(engineers),
sql.column('engineer').label('type')]),
'manager':sql.select([people.join(managers),
sql.column('manager').label('type')]),
}
)
etc.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_identity determination
Michael Bayer wrote:
just FYI, the type column idea is taken from Hibernate, and that's
all Hibernate supports as far as polymorphic loading.
I think it's good that SA takes good features from and is similar to
Hibernate, but I hope that your aspirations and those of SA's users are
to make SA much better than Hibernate. I think Python vs. Java/XML (I
despise the XML modeling) is already a boost for SA. SA is amazing and
I've got high hopes for its future.
But for polymorphic loading in SA, you are free to make any kind of
polymorphic_union you like that can add in a functionally-generated
type column, and specify it into select_table. im pretty sure this
should work completely right now, such as:
import sqlalchemy.sql as sql
person_join = polymorphic_union(
{
'engineer':sql.select([people.join(engineers),
sql.column('engineer').label('type')]),
'manager':sql.select([people.join(managers),
sql.column('manager').label('type')]),
}
)
etc.
Thanks for code example. I suspect that's the approach I'll be using
most often.
Randall
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
