Hey Michael,
rev 1554 worked for me - thanks for the quick reply.
Cheers,
Nick
Michael Bayer wrote:
> Hey Nick -
>
> I ran this test (which is well-written, youve understood the docs very
> well) with my favorite constraint checker, Postgres, and it does in fact
> fail on 0.2.1. Fortunately, it does not fail when testing with the
> fixes I have just made today, i think its the same issue someone else
> raised today (a lot has changed with the persistence mechanism with
> regards to inheritance structures recently). Try checking out the
> trunk, rev 1554. also, one slight change to your program:
>
> magazine_page_mapper = mapper(MagazinePage, magazine_page_table,
> inherits=page_mapper, polymorphic_identity='m', properties={
> 'magazine': relation(Magazine, backref=backref('pages'))
> })
>
> and remove the extra property:
>
> #magazine_mapper.add_property('pages', relation(MagazinePage, lazy=True,
> private=True))
>
> this establishes 'pages' as a backreference (it will work either way if
> you are just loading the object from the DB, but the "backref" makes it
> automatically set up the two-way relationship before anything is even
> saved to the DB).
>
> the insert order is correct including on PG which wont let constraint
> violations go by, and the output of your select at the bottom is then:
>
> [<__main__.MagazinePage object at 0x10fea30>]
>
> For test purposes, I threw a session.clear() right after the flush() to
> insure a full load from the DB works and i got the same result.
>
>
> On May 30, 2006, at 3:15 PM, Nick Joyce wrote:
>
>> Hi,
>>
>> I am new to SQLAlchemy and have been testing it using a real-world
>> project of mine. (See attached test.py)
>>
>> I am using 0.2.1
>>
>> magazine_page.magazine_id has a foreign key to magazine.id
>>
>> Excerpt from debug.log:
>>
>> [2006-05-30 17:42:11,724] [engine]: BEGIN
>> [2006-05-30 17:42:11,724] [engine]: INSERT INTO location_name (name)
>> VALUES (?)
>> [2006-05-30 17:42:11,725] [engine]: ['London']
>> [2006-05-30 17:42:11,726] [engine]: INSERT INTO page (page_no, type)
>> VALUES (?, ?)
>> [2006-05-30 17:42:11,726] [engine]: [1, 'm']
>> [2006-05-30 17:42:11,728] [engine]: INSERT INTO magazine_page (page_id,
>> magazine_id, orders) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,728] [engine]: [1, '0', '']
>> [2006-05-30 17:42:11,729] [engine]: INSERT INTO page_size (width,
>> height, name) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,730] [engine]: [210, 297, 'A4']
>> [2006-05-30 17:42:11,731] [engine]: INSERT INTO publication (name)
>> VALUES (?)
>> [2006-05-30 17:42:11,731] [engine]: ['Test']
>> [2006-05-30 17:42:11,732] [engine]: INSERT INTO issue (publication_id,
>> issue) VALUES (?, ?)
>> [2006-05-30 17:42:11,733] [engine]: [1, 46]
>> [2006-05-30 17:42:11,734] [engine]: INSERT INTO location (issue_id, ref,
>> location_name_id) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,734] [engine]: [1, 'ABC', 1]
>> [2006-05-30 17:42:11,736] [engine]: INSERT INTO magazine (location_id,
>> page_size_id) VALUES (?, ?)
>> [2006-05-30 17:42:11,736] [engine]: [1, 1]
>> [2006-05-30 17:42:11,737] [engine]: COMMIT
>>
>> In enforcing the referential integrity shouldn't the magazine insert
>> come before the magazine_page (and update the magazine_page.magazine_id)?
>>
>> Perhaps I have the mapping wrong, if so can someone give me some
>> pointers in the right direction? I have extensively consulted the
>> documentation, but perhaps I missed something ...
>>
>> Cheers,
>>
>> Nick
>>
>>
>> [2006-05-30 17:42:11,694] [engine]: PRAGMA table_info(classified_page)
>> [2006-05-30 17:42:11,694] [engine]: {}
>> [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(magazine_page)
>> [2006-05-30 17:42:11,696] [engine]: {}
>> [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(magazine)
>> [2006-05-30 17:42:11,696] [engine]: {}
>> [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(location)
>> [2006-05-30 17:42:11,696] [engine]: {}
>> [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(location_name)
>> [2006-05-30 17:42:11,697] [engine]: {}
>> [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(issue)
>> [2006-05-30 17:42:11,697] [engine]: {}
>> [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(publication)
>> [2006-05-30 17:42:11,697] [engine]: {}
>> [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(page)
>> [2006-05-30 17:42:11,698] [engine]: {}
>> [2006-05-30 17:42:11,698] [engine]: PRAGMA table_info(page_size)
>> [2006-05-30 17:42:11,698] [engine]: {}
>> [2006-05-30 17:42:11,699] [engine]: PRAGMA table_info(page_size)
>> [2006-05-30 17:42:11,699] [engine]: {}
>> [2006-05-30 17:42:11,700] [engine]:
>> CREATE TABLE page_size(
>> id INTEGER NOT NULL PRIMARY KEY,
>> width INTEGER,
>> height INTEGER,
>> name VARCHAR(45)
>> )
>>
>>
>> [2006-05-30 17:42:11,700] [engine]: None
>> [2006-05-30 17:42:11,700] [engine]: COMMIT
>> [2006-05-30 17:42:11,701] [engine]: PRAGMA table_info(page)
>> [2006-05-30 17:42:11,701] [engine]: {}
>> [2006-05-30 17:42:11,701] [engine]:
>> CREATE TABLE page(
>> id INTEGER NOT NULL PRIMARY KEY,
>> page_no INTEGER,
>> type CHAR(1)
>> )
>>
>>
>> [2006-05-30 17:42:11,702] [engine]: None
>> [2006-05-30 17:42:11,702] [engine]: COMMIT
>> [2006-05-30 17:42:11,702] [engine]: PRAGMA table_info(publication)
>> [2006-05-30 17:42:11,702] [engine]: {}
>> [2006-05-30 17:42:11,703] [engine]:
>> CREATE TABLE publication(
>> id INTEGER NOT NULL PRIMARY KEY,
>> name VARCHAR(45)
>> )
>>
>>
>> [2006-05-30 17:42:11,703] [engine]: None
>> [2006-05-30 17:42:11,704] [engine]: COMMIT
>> [2006-05-30 17:42:11,704] [engine]: PRAGMA table_info(issue)
>> [2006-05-30 17:42:11,704] [engine]: {}
>> [2006-05-30 17:42:11,705] [engine]:
>> CREATE TABLE issue(
>> id INTEGER NOT NULL PRIMARY KEY,
>> publication_id INTEGER REFERENCES publication(id),
>> issue INTEGER
>> )
>>
>>
>> [2006-05-30 17:42:11,705] [engine]: None
>> [2006-05-30 17:42:11,705] [engine]: COMMIT
>> [2006-05-30 17:42:11,706] [engine]: PRAGMA table_info(location_name)
>> [2006-05-30 17:42:11,706] [engine]: {}
>> [2006-05-30 17:42:11,706] [engine]:
>> CREATE TABLE location_name(
>> id INTEGER NOT NULL PRIMARY KEY,
>> name VARCHAR(45)
>> )
>>
>>
>> [2006-05-30 17:42:11,707] [engine]: None
>> [2006-05-30 17:42:11,707] [engine]: COMMIT
>> [2006-05-30 17:42:11,707] [engine]: PRAGMA table_info(location)
>> [2006-05-30 17:42:11,707] [engine]: {}
>> [2006-05-30 17:42:11,708] [engine]:
>> CREATE TABLE location(
>> id INTEGER NOT NULL PRIMARY KEY,
>> issue_id INTEGER REFERENCES issue(id),
>> ref CHAR(3),
>> location_name_id INTEGER REFERENCES location_name(id)
>> )
>>
>>
>> [2006-05-30 17:42:11,708] [engine]: None
>> [2006-05-30 17:42:11,709] [engine]: COMMIT
>> [2006-05-30 17:42:11,709] [engine]: PRAGMA table_info(magazine)
>> [2006-05-30 17:42:11,709] [engine]: {}
>> [2006-05-30 17:42:11,710] [engine]:
>> CREATE TABLE magazine(
>> id INTEGER NOT NULL PRIMARY KEY,
>> location_id INTEGER REFERENCES location(id),
>> page_size_id INTEGER REFERENCES page_size(id)
>> )
>>
>>
>> [2006-05-30 17:42:11,710] [engine]: None
>> [2006-05-30 17:42:11,711] [engine]: COMMIT
>> [2006-05-30 17:42:11,711] [engine]: PRAGMA table_info(magazine_page)
>> [2006-05-30 17:42:11,711] [engine]: {}
>> [2006-05-30 17:42:11,712] [engine]:
>> CREATE TABLE magazine_page(
>> page_id INTEGER NOT NULL PRIMARY KEY REFERENCES page(id),
>> magazine_id INTEGER REFERENCES magazine(id),
>> orders TEXT
>> )
>>
>>
>> [2006-05-30 17:42:11,712] [engine]: None
>> [2006-05-30 17:42:11,713] [engine]: COMMIT
>> [2006-05-30 17:42:11,713] [engine]: PRAGMA table_info(classified_page)
>> [2006-05-30 17:42:11,713] [engine]: {}
>> [2006-05-30 17:42:11,714] [engine]:
>> CREATE TABLE classified_page(
>> magazine_page_id INTEGER NOT NULL PRIMARY KEY REFERENCES
>> magazine_page(page_id),
>> titles VARCHAR(45)
>> )
>>
>>
>> [2006-05-30 17:42:11,714] [engine]: None
>> [2006-05-30 17:42:11,714] [engine]: COMMIT
>> [2006-05-30 17:42:11,718] [engine]: SELECT location_name.id AS
>> location_name_id, location_name.name AS location_name_name
>> FROM location_name
>> WHERE location_name.name = ? ORDER BY location_name.oid
>> LIMIT 1 OFFSET 0
>> [2006-05-30 17:42:11,718] [engine]: ['London']
>> [2006-05-30 17:42:11,724] [engine]: BEGIN
>> [2006-05-30 17:42:11,724] [engine]: INSERT INTO location_name (name)
>> VALUES (?)
>> [2006-05-30 17:42:11,725] [engine]: ['London']
>> [2006-05-30 17:42:11,726] [engine]: INSERT INTO page (page_no, type)
>> VALUES (?, ?)
>> [2006-05-30 17:42:11,726] [engine]: [1, 'm']
>> [2006-05-30 17:42:11,728] [engine]: INSERT INTO magazine_page
>> (page_id, magazine_id, orders) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,728] [engine]: [1, '0', '']
>> [2006-05-30 17:42:11,729] [engine]: INSERT INTO page_size (width,
>> height, name) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,730] [engine]: [210, 297, 'A4']
>> [2006-05-30 17:42:11,731] [engine]: INSERT INTO publication (name)
>> VALUES (?)
>> [2006-05-30 17:42:11,731] [engine]: ['Test']
>> [2006-05-30 17:42:11,732] [engine]: INSERT INTO issue (publication_id,
>> issue) VALUES (?, ?)
>> [2006-05-30 17:42:11,733] [engine]: [1, 46]
>> [2006-05-30 17:42:11,734] [engine]: INSERT INTO location (issue_id,
>> ref, location_name_id) VALUES (?, ?, ?)
>> [2006-05-30 17:42:11,734] [engine]: [1, 'ABC', 1]
>> [2006-05-30 17:42:11,736] [engine]: INSERT INTO magazine (location_id,
>> page_size_id) VALUES (?, ?)
>> [2006-05-30 17:42:11,736] [engine]: [1, 1]
>> [2006-05-30 17:42:11,737] [engine]: COMMIT
>> import sqlalchemy.mods.threadlocal
>> from sqlalchemy import *
>>
>> # set-up tables
>>
>> metadata = BoundMetaData('sqlite://:memory:')
>>
>> publication_table = Table('publication', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('name', String(45), default=''),
>> )
>> issue_table = Table('issue', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('publication_id', Integer, ForeignKey('publication.id'),
>> default='0'),
>> Column('issue', Integer, default='0'),
>> )
>> location_table = Table('location', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('issue_id', Integer, ForeignKey('issue.id'), default='0'),
>> Column('ref', CHAR(3), default=''),
>> Column('location_name_id', Integer,
>> ForeignKey('location_name.id'), default='0'),
>> )
>> location_name_table = Table('location_name', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('name', String(45), default=''),
>> )
>> magazine_table = Table('magazine', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('location_id', Integer, ForeignKey('location.id'),
>> default='0'),
>> Column('page_size_id', Integer, ForeignKey('page_size.id'),
>> default='0'),
>> )
>> page_table = Table('page', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('page_no', Integer, default='0'),
>> Column('type', CHAR(1), default='p'),
>> )
>> magazine_page_table = Table('magazine_page', metadata,
>> Column('page_id', Integer, ForeignKey('page.id'),
>> primary_key=True, default='0'),
>> Column('magazine_id', Integer, ForeignKey('magazine.id'),
>> default='0'),
>> Column('orders', TEXT, default=''),
>> )
>> classified_page_table = Table('classified_page', metadata,
>> Column('magazine_page_id', Integer,
>> ForeignKey('magazine_page.page_id'), primary_key=True, default='0'),
>> Column('titles', String(45), default=''),
>> )
>> page_size_table = Table('page_size', metadata,
>> Column('id', Integer, primary_key=True, default=None),
>> Column('width', Integer, default='0'),
>> Column('height', Integer, default='0'),
>> Column('name', String(45), default=''),
>> )
>>
>> # mapping objects
>>
>> class BaseObject(object):
>> def __init__(self, *args, **kwargs):
>> for key, value in kwargs.iteritems():
>> setattr(self, key, value)
>>
>> class Publication(BaseObject):
>> pass
>>
>> class Issue(BaseObject):
>> pass
>>
>> class Location(BaseObject):
>> def _get_name(self):
>> return self._name
>>
>> def _set_name(self, name):
>> session = objectstore.get_session()
>> s =
>> session.query(LocationName).selectfirst(location_name_table.c.name==name)
>>
>> if s is not None:
>> self._name = s
>>
>> return
>>
>> found = False
>>
>> for i in session.new:
>> if isinstance(i, LocationName) and i.name == name:
>> self._name = i
>> found = True
>>
>> break
>>
>> if found == False:
>> self._name = LocationName(name=name)
>>
>> name = property(_get_name, _set_name)
>>
>> class LocationName(BaseObject):
>> pass
>>
>> class PageSize(BaseObject):
>> pass
>>
>> class Magazine(BaseObject):
>> pass
>>
>> class Page(BaseObject):
>> pass
>>
>> class MagazinePage(Page):
>> def __init__(self, *args, **kwargs):
>> Page.__init__(self, *args, **kwargs)
>>
>> class ClassifiedPage(MagazinePage):
>> pass
>>
>> # define mapping
>>
>> publication_mapper = mapper(Publication, publication_table)
>>
>> issue_mapper = mapper(Issue, issue_table, properties = {
>> 'publication': relation(Publication, backref=backref('issues',
>> cascade="add, delete-orphan")),
>> })
>>
>> location_name_mapper = mapper(LocationName, location_name_table)
>>
>> location_mapper = mapper(Location, location_table, properties = {
>> 'issue': relation(Issue, backref='locations'),
>> '_name': relation(LocationName),
>> })
>>
>> issue_mapper.add_property('locations', relation(Location, lazy=False,
>> private=True, backref='issue'))
>>
>> page_size_mapper = mapper(PageSize, page_size_table)
>>
>> page_join = polymorphic_union(
>> {
>> 'm': page_table.join(magazine_page_table),
>> 'p': page_table.select(page_table.c.type=='p'),
>> }, None, 'page_join')
>>
>> magazine_mapper = mapper(Magazine, magazine_table, properties = {
>> 'location': relation(Location, backref=backref('magazine',
>> uselist=False)),
>> 'size': relation(PageSize),
>> })
>>
>> page_mapper = mapper(Page, page_table, select_table=page_join,
>> polymorphic_on=page_join.c.type, polymorphic_identity='p')
>>
>> magazine_page_mapper = mapper(MagazinePage, magazine_page_table,
>> inherits=page_mapper, polymorphic_identity='m', properties={
>> 'magazine': relation(Magazine)
>> })
>>
>> magazine_mapper.add_property('pages', relation(MagazinePage,
>> lazy=True, private=True))
>>
>> #classified_page_mapper = mapper(ClassifiedPage,
>> classified_page_table, inherits=MagazinePage.mapper,
>> polymorphic_identity='c')
>>
>> session = objectstore.get_session()
>>
>> # do some operations
>>
>> metadata.engine.echo = True
>>
>> # metadata.drop_all()
>> metadata.create_all()
>>
>> pub = Publication(name='Test')
>> issue = Issue(issue=46,publication=pub)
>> location = Location(ref='ABC',name='London',issue=issue)
>>
>> page_size = PageSize(name='A4',width=210,height=297)
>>
>> magazine = Magazine(location=location,size=page_size)
>>
>> page = MagazinePage(magazine=magazine,page_no=1)
>>
>> session.flush()
>>
>> '''
>> p = session.query(Publication).selectone_by(name='Test')
>>
>> print p.issues[0].locations[0].magazine.pages
>> '''
>
>
>
> -------------------------------------------------------
> All the advantages of Linux Managed Hosting--Without the Cost and Risk!
> Fully trained technicians. The highest number of Red Hat certifications in
> the hosting industry. Fanatical Support. Click to learn more
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
