Hello,
I just solved a problem that was most likely due to my lack of
understanding of RDBO. I just wanted to pass it along in case other
newbies run into the same issue.
Breaking the problem down to its simplest form, I have two tables in a
one-to many relationship. The problem was that subsequent inserts were
deleting earlier entries in the "many" table. Here's a quick definition:
package Basket;
__PACKAGE__->meta->setup(
table => 'baskets',
columns => [
id => { type => 'integer', not_null => 1 },
Item => { type => 'integer', default => '0', not_null => 1 },
transaction_id => { type => 'integer', default => '0', not_null
=> 1 },
ProductID => { type => 'varchar', default => '', length =>
50, not_null => 1 },
],
primary_key_columns => [ 'id' ],
unique_key => [ 'transaction_id', 'ProductID', 'Item' ],
relationships => [
persons => {
type => 'one to many',
class => 'Person',
column_map => { Item => 'Item' },
},
]
);
1;
I'd enter one entry with a transaction_id=10 and Item=1 just fine. If I
entered another entry with transaction_id=11 and Item=1, the previous
entry in the "persons" table for transaction_id=10 would be deleted.
This is because when the save() is executed, the SQL created issues a
DELETE before the INSERT into "persons", e.g.
DELETE FROM persons WHERE
Item = ? - bind params: 1
I was very suprised by this DELETE. I discovered this by using the debug
mode for RDBO:
local $Rose::DB::Object::Debug = 1;
print query; # Just before the save()
The fix was to add the transaction_id to the column_map, i.e.
column_map => { Item => 'Item', transaction_id => 'transaction_id' },
so the DELETE becomes:
DELETE FROM persons WHERE
Item = ? AND
transaction_id = ? - bind params: 1, 11
which doesn't match fortunately.
The db rms I'm using is MySQL.
I hope this helps someone.
Take care,
Kurt Hansen
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
