Hm. You're defining a relationship; I've always mapped relationships
aas relationships, and let Cayenne deal with the id specifics. Note
that cayenne won't force the creation foreign key (although you should
create one, if your db supports it).
You say you have "too many tables like that"... what order of
magnitude are you talking about?
I think the upfront work of mapping relationships as relationships
will be worth it to you, long term.
In terms of committing it all in the "same transaction", if you're
using myisam in mysql, it doesn't support true transactions, anyway.
Anyway, if you /really/ want to deal with all of the nastiness of
id's, then I would think about using SQLTemplate.
You can script that using velocity directives and variables, so then
you may just wind up executing a query chain of SQLTemplates, where
the first template inserts into t_1 and the second template in the
chain inserts into t_2 per your requirements. If you really want low-
level jdbc-like functionality, SQLTemplate is the key.
Robert
On Apr 20, 2009, at 4/204:59 AM , Joseph Schmidt wrote:
>
>>>> * write the record to database, fetch it back again and
>>>> then you'll have the primary key
>>> Than this is not the same transaction :(.
>> Correct. This is a limitation of how databases work, not Cayenne.
>> You can't have a primary key until you write the record, unless you
>> do something tricky like using a nanosecond timestamp hashed with
>> the MAC address of your machine and the number you first thought of.
> Sorry but this is complete *bullshit*.
> There's no such database limitation. I can write in the same
> transaction (i.e. before I do commit), the values received from the
> previous inserts - even those generated by the DB.
> E.g. for Mysql, for the following tables:
>
> t_1(id integer autoincrement, name varchar40)
> t_2(id integer autoincrement, name varchar40, t_1_id integer)
> (see that t_1_id field is not a foreign key here)
>
> No if one is doing with DB directly or with JDBC, the following will
> work:
> -- start transaction
> INSERT INTO t_1 (name) VALUES ('name1');
> INSERT INTO t_1 (name) VALUES ('name2');
> INSERT INTO t_2 (name,t_1_id) select 'name1' , t_1.id from t_1 where
> t_1.name='name1';
> commit; -- end transaction
>
> so the correct primary key value (generated by the DB for t_1) will
> be insert at the correct position of t_2.t_1_id, and this inside the
> same transaction - so just like using FK would.
>
> Now, my trivial question again: How to do it with Cayenne, how to
> get that ID to write again inside the same transaction?
> (I mentioned in the previous posts that I don't have for t_2.t_1_id
> a relationship, nor FK, because there are too many tables like that).
>
> This is an incredible common scenario in most DBs, so I suppose that
> it should be possible with an ORM like Cayenne too(since with SQL or
> JDBC is dead easy).
> I red the entire documentation several times but couldn't find
> anything how to do it in Cayenne :(.
>
> thanks in advance,
> Joseph.
>
This archive was generated by hypermail 2.0.0 : Tue Apr 21 2009 - 11:15:33 EDT