The transactional approach with the subquery is working just fine but if anyone has thoughts on how I can get the auto-increment to work I would love to hear them.
Thanks, -Bob On Sun, Nov 22, 2009 at 4:10 PM, Robert Stockdale < [email protected]> wrote: > Hi Peter, > > > Several things strike me as wrong: >> >> *) Why is the primary key a composite, if the above subquery guarantees >> that >> the single column 'id' will always be unique? >> > > I should have been a little more clear. The ID field is not always set > using the subquery. In certain cases a value is assigned directly. So a > slightly broader view of the code looks like this: > > if ( $some_condition ) { > $fields->{id} = $id > } > else { > > # code to set other fields here... > $fields->{id} = \'(SELECT FIELD1 FROM (SELECT MAX(FIELD1) + 1 FIELD1 > FROM TABLE1) TEMP_T)'; > } > > $c->model('DB::Table1')->create($fields); > > Based on the code above you can now tell that the ID column is not unique > by itself. > > *) Why not simply use autoincrementing columns, but instead incur the speed >> penalty of a subquery on every insert? >> > > I tried this: The ID column is being incremented as expected by msql, but > the call to create() still blows up with: > > "DBIx::Class::ResultSet::create(): Can't get last insert id at ..." > > I would really prefer to go this route. Any idea why this wouldn't be > working? > > >> *) Bonus - if you have a field 'id' and a multicolumn PK, you need to >> redefine the accessor for this particular column. Otherwise you're >> overloading the internal 'id' method[1]. >> > > Thanks for the tip, I didn't know this. Do you know where I can find more > info on doing this? > > >> If you're hellbent on using the subquery - you need to roughly do this: >> > > I would really prefer to use the auto_increment approach but it wasn't > working so I figured the subquery was the next best approach because I > needed to get away from two completely separate queries to get/set the ID. > So yea, if I can get the auto_increment to work that would be ideal. Could > the id accessor issue be the underlying problem? > > Thanks, > -Bob > > >> The reason it doesn't work the way you did it: >> 1) DBIC executes the SQL you expect when creating the row >> 2) DBIC then runs through the columns of the just created object, >> and makes sure all PK columns are defined non-ref values >> 3) It finds something odd and calls $storage->_dbh_last_insert_id, >> since a full PK condition is necessary to do any further work with >> this object (i.e. when you update/delete you need all the values to >> make sure you're working with the correct row) >> 4) Of course the storage driver does not return anything, as the >> DBD driver did not use an autoincrement, thus has no idea what the >> value of the subquery was >> 5) DBIC dies instead of leaving you with a broken row object >> >> Cheers >> >> _______________________________________________ >> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class >> IRC: irc.perl.org#dbix-class >> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ >> Searchable Archive: >> http://www.grokbase.com/group/[email protected] >> > -- Bob Stockdale [email protected]
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
