Order of INSERTs for children of an object

[Tomasz Nazar]

Hi. Let me explain problem I have with an order of insert statements
created by SQLA which looks as random one. Inserts are generated for
the root object, and its children obviously.
Conference - ConferenceLang (one-to-many)
Conference - Participant (one-to-many)

<code>
self.confs_t = Table('confs', self.metadata, autoload=True,
autoload_with=config['pylons.g'].sa_engine)
mapper(Conference, self.confs_t,
properties = {
'conf_langs' : relation(ConferenceLang, cascade='all,
delete-orphan', lazy=False),
'participants' : relation(ConferenceParticipant,

primaryjoin=self.confs_t.c.id == self.conf_parts_t.c.cf_id,

foreign_keys=[self.conf_parts_t.c.cf_id],
cascade='all, delete-orphan'),
}
)
conference = Conference(....)
conference.conf_langs.append(ConferenceLang(...))
conference.participants.append(ConferenceParticipant(...))
.....
dbsession.flush(conference)
</code>

Without going into details, here's each db session's sequence of 3 inserts:
1st: INSERT INTO conferences (id, .....
2nd or 3rd: INSERT INTO conf_langs (cf_id, ...) <- sometimes
as the 2nd statement, and sometimes as the 3rd
2nd or 3rd: INSERT INTO participants (cf_id, ...) <- same as above

Problem is, that in my db - with some constraints not seen above - I
need to put data first into /conf_langs/ table, and only after then
into /participants/ table.
But instead, SQLAlchemy picks these inserts randomly. Sometimes it
issues an insert into /participants/ first, and sometimes as a
second/third statement.

Do I have control over this behaviour? Or am I doing sth wrong?
Thanks for any help..Tomasz

SQLAlchemy-0.4.8-py2.5.egg, transactional, autoload, Pylons 0.9.6.1,
MySql innodb.

--
_i______'simplicity_is_the_key'__________tomasz_nazar
_ii____'i_am_concern_oriented'________________JKM-UPR
_iii__'patsystem.sf.net'___________________linux_user
_'aspectized.com'___________________________prevayler

[Michael Bayer]

On Jan 16, 2009, at 6:13 PM, Tomasz Nazar wrote:

>
> Do I have control over this behaviour? Or am I doing sth wrong?
> Thanks for any help..Tomasz

you can ! its an exposed internal API but its pretty stable for
now. Build a SessionExtension like this:

from sqlalchemy.orm.interfaces import SessionExtension

class SetupDependencyExt(SessionExtension):
def before_flush(self, session, flush_context, instances):
flush_context.register_dependency(class_mapper(A),
class_mapper(B))

where "A" is the mapper that should execute first. then configure
that extension with your sessionmaker(extension=SetupDependencyExt()).

[Tomasz Nazar]

Thanks Michael, it works for simple case and solved my issue for now.
In SqlA 0.4 import is wrong. proper one is:
from sqlalchemy.orm.session import SessionExtension


So, for A->B dependency it works. 'Inserts' seem not be random any more.
I wanted to play a bit more with that feature and can I configure many
dependencies in that way? I was thinking if can tell SQLA to have kind
of global order for more than just 2 classes (to have ordered tree of
classes [inserts])


I have a working example:

class SetupDependencyExt(SessionExtension):
def before_flush(self, session, flush_context, instances):

flush_context.register_dependency(A, B)
flush_context.register_dependency(A2, B)

But when I add another line it fails, and inserts are random again
flush_context.register_dependency(B, C)

But adding this instead of above causes no problems.
flush_context.register_dependency(A3, C)


What do you think?
Tomasz



But when I add A2->B and B->C dependency it all fails again.

[Michael Bayer]

On Jan 19, 2009, at 9:10 AM, Tomasz Nazar wrote:

>
> Thanks Michael, it works for simple case and solved my issue for now.
> In SqlA 0.4 import is wrong. proper one is:
> from sqlalchemy.orm.session import SessionExtension
>
>
> So, for A->B dependency it works. 'Inserts' seem not be random any
> more.
> I wanted to play a bit more with that feature and can I configure many
> dependencies in that way? I was thinking if can tell SQLA to have kind
> of global order for more than just 2 classes (to have ordered tree of
> classes [inserts])
>
>
> I have a working example:
> class SetupDependencyExt(SessionExtension):
> def before_flush(self, session, flush_context, instances):
> flush_context.register_dependency(A, B)
> flush_context.register_dependency(A2, B)
>
> But when I add another line it fails, and inserts are random again
> flush_context.register_dependency(B, C)
>
> But adding this instead of above causes no problems.
> flush_context.register_dependency(A3, C)
>

the dependency order is a firm requirement of mappers. however if a
circular dependency occurs among one or more mappers, the unit of work
has no choice but to drop into row-level dependency checking. at
that point the hack I've given you won't work because a different
system is queried for row-level dependencies, and there is not as
public of an API for that (though of course there could be).

you really have that complex of a network of interdependent triggers ?

[Tomasz Nazar]

On Mon, Jan 19, 2009 at 4:33 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> So, for A->B dependency it works. 'Inserts' seem not be random any
>> more.
>> I wanted to play a bit more with that feature and can I configure many
>> dependencies in that way? I was thinking if can tell SQLA to have kind
>> of global order for more than just 2 classes (to have ordered tree of
>> classes [inserts])
>>
>>
>> I have a working example:
>> class SetupDependencyExt(SessionExtension):
>> def before_flush(self, session, flush_context, instances):
>> flush_context.register_dependency(A, B)
>> flush_context.register_dependency(A2, B)
>>
>> But when I add another line it fails, and inserts are random again
>> flush_context.register_dependency(B, C)
>>
>> But adding this instead of above causes no problems.
>> flush_context.register_dependency(A3, C)
>>
>
>
> the dependency order is a firm requirement of mappers. however if a
> circular dependency occurs among one or more mappers, the unit of work
> has no choice but to drop into row-level dependency checking. at
> that point the hack I've given you won't work because a different
> system is queried for row-level dependencies, and there is not as
> public of an API for that (though of course there could be).

Hmm, the circular dependency: I do have some mappers configured that
some cycle exists in fact. Is this what you're talking about?

Conference-> (has many) ConferenceInterpreter ->(related to one) User
-> (created many) Conference -> .....
I did so, cause it's worth for me to access:
/conference.participant.user/ and on the other side:
/user.conferences/

You know, I wanted to have a kind of ORM here, so I configured most of
mappers this way, they do have dependant sons or just one son.
Maybe the issue is I wanted to have too much of OO, or I did relations
in a wrong way.
If you wish, here's more detailed real example from my app:

Db tables:

Conference:
id int

ConferenceLang:
lg_id varchar(2) -> Lang (natural key)
cf_id int -> Conference

Lang:
id varchar(2)

ConferenceParticipant:
cf_id int -> Conference
lg_id varchar(2) -> Lang
composite foreign key above to ConferenceLang

ConferenceInterpreter:
cf_id int -> Conference
us_id int -> User
lg_a varchar(2) -> Lang
composite foreign key above with cf_id to ConferenceLang
lg_b varchar(2) -> Lang
composite foreign key above with cf_id to ConferenceLang

User:
id int

And some mappers:

mapper(Conference, self.confs_t,
properties = {
'conf_langs' : relation(ConferenceLang, cascade='all,
delete-orphan', lazy=False),
'participants' : relation(ConferenceParticipant,

primaryjoin=self.confs_t.c.id == self.conf_parts_t.c.cf_id,

foreign_keys=[self.conf_parts_t.c.cf_id],
cascade='all, delete-orphan'),

'interpreters' : relation(ConferenceInterpreter,

cascade='all, delete-orphan'),
}
)

mapper(ConferenceParticipant, self.conf_parts_t,
properties = {
'lang' : relation(Lang,

primaryjoin=self.conf_parts_t.c.lg_id==self.langs_t.c.id,
foreign_keys=self.conf_parts_t.c.lg_id,
cascade='save-update')
})
mapper(User, self.users_t,
properties = {
'user_langs' : relation(UserLang, cascade='all, delete-orphan'),
'lang_pairs': relation(ConferenceLangPair,
cascade='save-update'),
'conferences': relation(Conference,

primaryjoin=self.confs_t.c.creator_us_id==self.users_t.c.id,

foreign_keys=self.confs_t.c.creator_us_id,
cascade='save-update'),
})

> you really have that complex of a network of interdependent triggers ?

So you see, it's bit overcomplicated, but I do have nice way of
accessing and viewing db tables/objects.
So, looks there is no easy way but maybe to simplify mappings, or to
manually flush partial changes to the root object - so I can control
order of putting children into DB.

T.

[Michael Bayer]

On Jan 19, 2009, at 11:17 AM, Tomasz Nazar wrote:

>
> Hmm, the circular dependency: I do have some mappers configured that
> some cycle exists in fact. Is this what you're talking about?
>
> Conference-> (has many) ConferenceInterpreter ->(related to one) User
> -> (created many) Conference -> .....
> I did so, cause it's worth for me to access:
> /conference.participant.user/ and on the other side:
> /user.conferences/

if you actually have foreign keys expressing multiple paths to the
same things then there would be a cycle. It's hard to tell from
your mappings, your tables don't seem to have cycles from a foreign
key perspective. Even if there are cycles due to relation(), SQLA can
handle all that as long as the cycles arent from a row to itself (and
even that it can handle via the post_update option). I see a lot
going on there, maybe inheritance and such ? (UserLang/Lang)

[Tomasz Nazar]

On Mon, Jan 19, 2009 at 5:37 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> if you actually have foreign keys expressing multiple paths to the
> same things then there would be a cycle. It's hard to tell from
> your mappings, your tables don't seem to have cycles from a foreign
> key perspective. Even if there are cycles due to relation(), SQLA can
> handle all that as long as the cycles arent from a row to itself (and
> even that it can handle via the post_update option). I see a lot

That's good to hear.

> going on there, maybe inheritance and such ? (UserLang/Lang)

I do not have inheritance at all, but I do have too many objects
mapped (UserLang representing many-to-many table).
Thanks for help and pointing me better direction. For now I solved my
issues by doing partial flushes, and having some 'register_dependency'
entries.
You spent much time for me anyway - Thanks :)

[Tomasz Nazar]

Hi Mike,
I'm migrating from 0.4 to 0.6 and this API has been removed - at least
looks like this. Is there any replacement?
I'd still like SQLA to generate some INSERT statements before others,
as it looks SQLA0.4 could'nt handle mappers/relations/dependencies I
have...
This code worked well for me till now...

Code I have is:

sessionmaker(...extension=SetupDependencyExt())

class SetupDependencyExt(SessionExtension):
"""http://groups.google.com/group/sqlalchemy/browse_thread/thread/2420cbf64ff5a74f
For controlling order of inserts generation for dependent children
by relation

"""
def before_flush(self, session, flush_context, instances):

flush_context.register_dependency(mapper_conference_lang,
mapper_conference_participant)
flush_context.register_dependency(mapper_conference_lang,
mapper_conference_interpreter)

AttributeError: 'UOWTransaction' object has no attribute 'register_dependency'

I'd really appreciate any help, as I can't move forward without it.
Thanks - Tomasz

--
_i______'simplicity_is_the_key'__________tomasz_nazar
_ii____'i_am_concern_oriented'________________JKM-UPR
_iii__'patsystem.sf.net'___________________linux_user
_'aspectized.com'___________________________prevayler

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

ah, you should be able to get the same effect using uowtrans.dependencies.add((parent_mapper, child_mapper)).

did we already have a big discussion about why you can't get your relation()s to handle this for you ?

[Tomasz Nazar]

Thanks for quick response.
Yes we had a discussion on this list
(http://www.mail-archive.com/sqlal...@googlegroups.com/msg12897.html),
but I didn't went deeper into why it happens and just used
"dependencies" what worked great for me.

I will try to use the code you gave.
What I can do later is I can prepare a test case for you with my db
schema + relations, everything else stripped, so you can see (or I
can) why and where the issue occurs.

Thanks - Tomasz

PS. Can I upload files to the list somehow? As attachments maybe?