diff options
Diffstat (limited to 'examples/polymorph/polymorph.py')
| -rw-r--r-- | examples/polymorph/polymorph.py | 177 |
1 files changed, 71 insertions, 106 deletions
diff --git a/examples/polymorph/polymorph.py b/examples/polymorph/polymorph.py index d105a64ea..76a03b99d 100644 --- a/examples/polymorph/polymorph.py +++ b/examples/polymorph/polymorph.py @@ -1,145 +1,110 @@ from sqlalchemy import * -import sys +import sys, sets -# this example illustrates how to create a relationship to a list of objects, -# where each object in the list has a different type. The typed objects will -# extend from a common base class, although this same approach can be used -# with +# this example illustrates a polymorphic load of two classes, where each class has a very +# different set of properties -db = create_engine('sqlite://', echo=True, echo_uow=False) -#db = create_engine('postgres://user=scott&password=tiger&host=127.0.0.1&database=test', echo=True, echo_uow=False) +metadata = BoundMetaData('sqlite://', echo='debug') # a table to store companies -companies = Table('companies', db, +companies = Table('companies', metadata, Column('company_id', Integer, primary_key=True), - Column('name', String(50))).create() + Column('name', String(50))) # we will define an inheritance relationship between the table "people" and "engineers", # and a second inheritance relationship between the table "people" and "managers" -people = Table('people', db, +people = Table('people', metadata, Column('person_id', Integer, primary_key=True), Column('company_id', Integer, ForeignKey('companies.company_id')), - Column('name', String(50))).create() + Column('name', String(50)), + Column('type', String(30))) -engineers = Table('engineers', db, +engineers = Table('engineers', metadata, Column('person_id', Integer, ForeignKey('people.person_id'), primary_key=True), - Column('special_description', String(50))).create() + Column('status', String(30)), + Column('engineer_name', String(50)), + Column('primary_language', String(50)), + ) -managers = Table('managers', db, +managers = Table('managers', metadata, Column('person_id', Integer, ForeignKey('people.person_id'), primary_key=True), - Column('description', String(50))).create() + Column('status', String(30)), + Column('manager_name', String(50)) + ) + +metadata.create_all() - # create our classes. The Engineer and Manager classes extend from Person. class Person(object): + def __init__(self, **kwargs): + for key, value in kwargs.iteritems(): + setattr(self, key, value) def __repr__(self): return "Ordinary person %s" % self.name class Engineer(Person): def __repr__(self): - return "Engineer %s, description %s" % (self.name, self.special_description) + return "Engineer %s, status %s, engineer_name %s, primary_language %s" % (self.name, self.status, self.engineer_name, self.primary_language) class Manager(Person): def __repr__(self): - return "Manager %s, description %s" % (self.name, self.description) + return "Manager %s, status %s, manager_name %s" % (self.name, self.status, self.manager_name) class Company(object): + def __init__(self, **kwargs): + for key, value in kwargs.iteritems(): + setattr(self, key, value) def __repr__(self): return "Company %s" % self.name -# next we assign Person mappers. Since these are the first mappers we are -# creating for these classes, they automatically become the "primary mappers", which -# define the dependency relationships between the classes, so we do a straight -# inheritance setup, i.e. no modifications to how objects are loaded or anything like that. -assign_mapper(Person, people) -assign_mapper(Engineer, engineers, inherits=Person.mapper) -assign_mapper(Manager, managers, inherits=Person.mapper) - -# next, we define a query that is going to load Managers and Engineers in one shot. -# we will use a UNION ALL with an extra hardcoded column to indicate the type of object. -# this can also be done via several LEFT OUTER JOINS but a UNION is more appropriate -# since they are distinct result sets. -# The select() statement is also given an alias 'pjoin', since the mapper requires -# that all Selectables have a name. -# -# TECHNIQUE - when you want to load a certain set of objects from a in one query, all the -# columns in the Selectable must have unique names. Dont worry about mappers at this point, -# just worry about making a query where if you were to view the results, you could tell everything -# you need to know from each row how to construct an object instance from it. this is the -# essence of "resultset-based-mapping", which is the core ideology of SQLAlchemy. -# -person_join = select( - [people, managers.c.description,column("'manager'").label('type')], - people.c.person_id==managers.c.person_id).union_all( - select( - [people, engineers.c.special_description.label('description'), column("'engineer'").label('type')], - people.c.person_id==engineers.c.person_id)).alias('pjoin') - - -# lets print out what this Selectable looks like. The mapper is going to take the selectable and -# Select off of it, with the flag "use_labels" which indicates to prefix column names with the table -# name. So here is what our mapper will see: -print "Person selectable:", str(person_join.select(use_labels=True)), "\n" - - -# MapperExtension object. -class PersonLoader(MapperExtension): - def create_instance(self, mapper, row, imap, class_): - if row['pjoin_type'] =='engineer': - e = Engineer() - e.special_description = row['pjoin_description'] - return e - elif row['pjoin_type'] =='manager': - return Manager() - else: - return Person() -ext = PersonLoader() - -# set up the polymorphic mapper, which maps the person_join we set up to -# the Person class, using an instance of PersonLoader. -people_mapper = mapper(Person, person_join, extension=ext) - -# create a mapper for Company. the 'employees' relationship points to -# our new people_mapper. -# -# the dependency relationships which take effect on commit (i.e. the order of -# inserts/deletes) will be established against the Person class's primary -# mapper, and when the Engineer and -# Manager objects are found in the 'employees' list, the primary mappers -# for those subclasses will register -# themselves as dependent on the Person mapper's save operations. -# (translation: it'll work) -# TODO: get the eager loading to work (the compound select alias doesnt like being aliased itself) -assign_mapper(Company, companies, properties={ - 'employees': relation(people_mapper, lazy=False, private=True) -}) -c = Company(name='company1') -c.employees.append(Manager(name='pointy haired boss', description='manager1')) -c.employees.append(Engineer(name='dilbert', special_description='engineer1')) -c.employees.append(Engineer(name='wally', special_description='engineer2')) -c.employees.append(Manager(name='jsmith', description='manager2')) -objectstore.commit() +# create a union that represents both types of joins. +person_join = polymorphic_union( + { + 'engineer':people.join(engineers), + 'manager':people.join(managers), + 'person':people.select(people.c.type=='person'), + }, None, 'pjoin') -objectstore.clear() +#person_mapper = mapper(Person, people, select_table=person_join, polymorphic_on=person_join.c.type, polymorphic_identity='person') +person_mapper = mapper(Person, people, select_table=person_join,polymorphic_on=person_join.c.type, polymorphic_identity='person') +mapper(Engineer, engineers, inherits=person_mapper, polymorphic_identity='engineer') +mapper(Manager, managers, inherits=person_mapper, polymorphic_identity='manager') -c = Company.get(1) -for e in c.employees: - print e, e._instance_key +mapper(Company, companies, properties={ + 'employees': relation(Person, lazy=False, private=True, backref='company') +}) +session = create_session(echo_uow=False) +c = Company(name='company1') +c.employees.append(Manager(name='pointy haired boss', status='AAB', manager_name='manager1')) +c.employees.append(Engineer(name='dilbert', status='BBA', engineer_name='engineer1', primary_language='java')) +c.employees.append(Person(name='joesmith', status='HHH')) +c.employees.append(Engineer(name='wally', status='CGG', engineer_name='engineer2', primary_language='python')) +c.employees.append(Manager(name='jsmith', status='ABA', manager_name='manager2')) +session.save(c) +print session.new +session.flush() +#sys.exit() +session.clear() + +c = session.query(Company).get(1) +for e in c.employees: + print e, e._instance_key, e.company +assert sets.Set([e.name for e in c.employees]) == sets.Set(['pointy haired boss', 'dilbert', 'joesmith', 'wally', 'jsmith']) print "\n" -dilbert = Engineer.mapper.get_by(name='dilbert') -dilbert.special_description = 'hes dibert!' -objectstore.commit() +dilbert = session.query(Person).get_by(name='dilbert') +dilbert2 = session.query(Engineer).get_by(name='dilbert') +assert dilbert is dilbert2 + +dilbert.engineer_name = 'hes dibert!' -objectstore.clear() -c = Company.get(1) +session.flush() +session.clear() + +c = session.query(Company).get(1) for e in c.employees: print e, e._instance_key -objectstore.delete(c) -objectstore.commit() - +session.delete(c) +session.flush() -managers.drop() -engineers.drop() -people.drop() -companies.drop() +metadata.drop_all() |