Bug #3877

extra fields in crosslink table lost on update to parent table

Details

Submitted	2005-03-18 23:35 UTC
From	andrew dot clark at ucsb dot edu
Assigned	justinpatrin
Status	Closed
Package	DB_DataObject_FormBuilder
PHP Version	4.3.10
OS	FreeBSD 4.10
Roadmaps	(Not assigned)

Comments

[2005-03-18 23:35 UTC] andrew dot clark at ucsb dot edu

Description:
------------
Given a table and a cross-linking table (I'll call them Net and Net_contact), where crossLinks are in use and on the form (as selects) update some attribute of a Net which has linked Net_contacts. The update to the Net table causes the Net_contacts to be deleted and reinserted, which causes any attributes of that Net_contact other than ids to be lost.

For clarity, I'll say Net_contact looks like so:

net_contact_id int(16) primary_key, contact_id int(16) foreign key, net_id int(16) foreign key, last_modified timestamp, role enum(primary, secondary, security, ...)

According to similar bugs, $fb_crossLinkExtraFields is supposed to fix this. Defining that in the crossLink table results in the code dying:

[Fri Mar 18 15:27:02 2005] [error] PHP Fatal error: Call to undefined function: processform() in /usr/local/share/pear/DB/DataObject/FormBuilder.php on line 2 384

and deletes the linked Net_contacts from the table (except for those with enum value 1, apparently).

Reproduce code:
---------------
<?php
require_once 'include/config.php';
require_once 'DB/DataObject/FormBuilder.php';

function pe($err) {
die($err->getMessage().' '.$err->getUserInfo());
}
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'pe');

DB_DataObject::debugLevel(0);

$self =& DB_DataObject::factory('net');

if (PEAR::isError($self)) {
die ( $self->getMessage() );
}

$self->get(283);

$fb_options = array( 'elementNamePrefix' => $self->ident() );

$builder =& DB_DataObject_FormBuilder::create($self,$fb_options);

$form =& $builder->getForm();

$form->process(array(&$builder,'processForm'), false);

echo $form->toHtml();

Expected result:
----------------
Changes to a Net shouldn't affect the Net_contact attributes.

Actual result:
--------------
DataObjects debug output:

dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE
net_contact.net_contact_id = 1055
dataobjects_net_contact: query: QUERY DONE IN 0.0011179447174072 seconds
dataobjects_net_contact: 1: Clearing Cache for dataobjects_net_contact
dataobjects_net_contact: FETCH:
a:3:{s:10:"contact_id";s:3:"150";s:6:"net_id";s:3:"283";s:14:"net_contact_id";s:4:"1054";}
dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE
net_contact.net_contact_id = 1054
dataobjects_net_contact: query: QUERY DONE IN 0.0010049343109131 seconds
dataobjects_net_contact: 1: Clearing Cache for dataobjects_net_contact
dataobjects_net_contact: FETCH: N;
dataobjects_net_contact: FETCH: Last Data Fetch'ed after 0.00086498260498047
seconds
dataobjects_net_contact: QUERY: INSERT INTO net_contact (net_id , contact_id )
VALUES ( 283 , 150 )
dataobjects_net_contact: query: QUERY DONE IN 0.00096011161804199 seconds
dataobjects_net_contact: QUERY: INSERT INTO net_contact (net_id , contact_id )
VALUES ( 283 , 111 )
dataobjects_net_contact: query: QUERY DONE IN 0.00095391273498535 seconds

[2005-03-19 00:16 UTC] andrew dot clark at ucsb dot edu

It claims to be 0.12.1.

[2005-03-19 01:04 UTC] andrew dot clark at ucsb dot edu

Actually, I'm using select menus, rather than checkboxes. It seems that crossLinkExtraFields doesn't display in that case, but I'll try checkboxes. I'm pretty darn sure I'm using the current version, as linkNewValue is present.

[2005-03-19 01:22 UTC] andrew dot clark at ucsb dot edu

Checkboxes and my formElementPrefix might be unhappy together, I'll have to do more debug on that one. As far as the test you proposed:

"Now that I've said all of that, it looks to me from your debug info that all is working as expected..."

I took a net, added myself as a net contact (default role is secondary (enum 2), then changed the role of that net_contact to financial (enum 4), went back to the net, changed it, and I'm not deleted, but I am set back to the default role (secondary). DataObject debug shows that row being deleted and reinserted.

I am losing Net_contact crosslinks (strangely, for some Nets, just those that aren't role 1 (primary) but I need to muck with that some more) if crossLinkExtraFields is set in Net_contact. In that case, the code exits with the error I mentioned. Reloading the page that displays the Net object shows that all Net_contacts (except for the primary contact as in cases above) have been deleted.

[2005-03-28 21:15 UTC] andrew dot clark at ucsb dot edu

Was out on vacation for a week. I'll try this with the new release of FB and update the bug if it still exists.

[2005-03-28 22:04 UTC] andrew dot clark at ucsb dot edu

The problem doesn't exist when using checkboxes, only using selects. I'll use checkboxes for the time being.
I get:

[error] PHP Fatal error: Call to undefined function: processform() in /usr/local/share/pear/DB/DataObject/FormBuilder.php on line 2449

Changing the pear error handling to

PEAR::setErrorHandling(PEAR_ERROR_DIE)

in the script doesn't provide any additional output

[2005-03-28 23:44 UTC] andrew dot clark at ucsb dot edu

Pardon the HTML cruft, but I'm not sure exactly what difference you're looking for.

For a multiple select, the select element name looks like so:

(I put an md5 hash on via elementNamePrefix)

<select multiple="multiple" name="4eb202dba208f0b825fe60124fcc0986__crossLink_net_contact[]">
<option value="98">Blow, Joe</option>
...

For checkbox, like so:
<tr>
<td bgcolor="lightgrey"><label for="qf_f15923">Blow, Joe</label></td>
<td style="text-align: center"><input name="4eb202dba208f0b825fe60124fcc0986__crossLink_net_contact[77]" type="checkbox" value="77" id="qf_f15923" /></td>
<td style="text-align: center"><select name="4eb202dba208f0b825fe60124fcc0986__crossLink_net_contact__77_role_">
<option value=""></option>
<option value="primary">primary</option>
<option value="secondary">secondary</option>

<option value="security">security</option>
<option value="financial">financial</option>
</select></td>

[2005-03-29 00:59 UTC] andrew dot clark at ucsb dot edu

I'll try cvs. The debug output in the first post is DataObject debug level 1.

[2005-03-29 20:03 UTC] andrew dot clark at ucsb dot edu

Here's the debug 3 showing the delete and reinsert without crossLinkExtraFields and using a select form, instead of checkboxes. I changed the description field for the net, which deleted and reinserted the net_contact. Do you want the entire debug output (it's quite large)?

<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE net_contact.net_contact_id = 1097</code> 
<code>dataobjects_net_contact: query: QUERY DONE IN 0.018433809280396 seconds</code>

<code>dataobjects_net_contact: 1: Clearing Cache for dataobjects_net_contact</code> 
<code>dataobjects_net_contact: FETCH: N;</code> 
<code>dataobjects_net_contact: FETCH: Last Data Fetch'ed after 0.00091814994812012 seconds</code> 
<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code>

<code>dataobjects_net_contact: QUERY: INSERT INTO net_contact (net_id , contact_id ) VALUES ( 405 , 111 ) </code> 
<code>dataobjects_net_contact: query: QUERY DONE IN 0.0092880725860596 seconds</code>

[2005-03-29 21:20 UTC] andrew dot clark at ucsb dot edu

Indeed there was a fine. And this is sort of interesting, because although it finds two net_contacts (which is correct), it leaves the first (the one with role and id of 1) alone:

<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: QUERY: SELECT contact_id , net_id , net_contact_id FROM net_contact WHERE net_contact.net_id = 405 </code> 
<code>dataobjects_net_contact: query: QUERY DONE IN 0.00089001655578613 seconds</code> 
<code>dataobjects_net_contact: __find: CHECK autofetchd </code> 
<code>dataobjects_net_contact: __find: DONE</code>

<code>dataobjects_net_contact: FETCH: a:3:{s:10:"contact_id";s:1:"1";s:6:"net_id";s:3:"405";s:14:"net_contact_id";s:4:"1098";}</code> 
<code>dataobjects_net_contact: fetchrow LINE: contact_id = 1</code> 
<code>dataobjects_net_contact: fetchrow LINE: net_id = 405</code> 
<code>dataobjects_net_contact: fetchrow LINE: net_contact_id = 1098</code> 
<code>dataobjects_net_contact: fetchrow: net_contact DONE</code>

<code>dataobjects_net_contact: FETCH: a:3:{s:10:"contact_id";s:3:"111";s:6:"net_id";s:3:"405";s:14:"net_contact_id";s:4:"1097";}</code> 
<code>dataobjects_net_contact: fetchrow LINE: contact_id = 111</code> 
<code>dataobjects_net_contact: fetchrow LINE: net_id = 405</code> 
<code>dataobjects_net_contact: fetchrow LINE: net_contact_id = 1097</code> 
<code>dataobjects_net_contact: fetchrow: net_contact DONE</code>

<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: CONNECT: USING CACHED CONNECTION</code> 
<code>dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE net_contact.net_contact_id = 1097</code> 
<code>dataobjects_net_contact: query: QUERY DONE IN 0.018433809280396 seconds</code>

I'll try CVS.

[2005-03-29 22:10 UTC] andrew dot clark at ucsb dot edu

It's still deleting and reinserting w/o preserving the role. Here's DO debug level 3 starting at the point of the find:

dataobjects_net_contact: __find:
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: QUERY: SELECT contact_id , net_id , net_contact_id FROM net_contact WHERE net_contact.net_id = 405
dataobjects_net_contact: query: QUERY DONE IN 0.0042400360107422 seconds
dataobjects_net_contact: __find: CHECK autofetchd
dataobjects_net_contact: __find: DONE
dataobjects_net_contact: FETCH: a:3:{s:10:"contact_id";s:1:"1";s:6:"net_id";s:3:"405";s:14:"net_contact_id";s:4:"1103";}
dataobjects_net_contact: fetchrow LINE: contact_id = 1
dataobjects_net_contact: fetchrow LINE: net_id = 405
dataobjects_net_contact: fetchrow LINE: net_contact_id = 1103
dataobjects_net_contact: fetchrow: net_contact DONE
dataobjects_net_contact: FETCH: a:3:{s:10:"contact_id";s:2:"13";s:6:"net_id";s:3:"405";s:14:"net_contact_id";s:4:"1101";}
dataobjects_net_contact: fetchrow LINE: contact_id = 13
dataobjects_net_contact: fetchrow LINE: net_id = 405
dataobjects_net_contact: fetchrow LINE: net_contact_id = 1101
dataobjects_net_contact: fetchrow: net_contact DONE
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE net_contact.net_contact_id = 1101
dataobjects_net_contact: query: QUERY DONE IN 0.0011110305786133 seconds
dataobjects_net_contact: 1: Clearing Cache for dataobjects_net_contact
dataobjects_net_contact: FETCH: a:3:{s:10:"contact_id";s:3:"111";s:6:"net_id";s:3:"405";s:14:"net_contact_id";s:4:"1102";}
dataobjects_net_contact: fetchrow LINE: contact_id = 111
dataobjects_net_contact: fetchrow LINE: net_id = 405
dataobjects_net_contact: fetchrow LINE: net_contact_id = 1102
dataobjects_net_contact: fetchrow: net_contact DONE
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: QUERY: DELETE FROM net_contact WHERE net_contact.net_contact_id = 1102
dataobjects_net_contact: query: QUERY DONE IN 0.0027260780334473 seconds
dataobjects_net_contact: 1: Clearing Cache for dataobjects_net_contact
dataobjects_net_contact: FETCH: N;
dataobjects_net_contact: FETCH: Last Data Fetch'ed after 0.00092601776123047 seconds
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: QUERY: INSERT INTO net_contact (net_id , contact_id ) VALUES ( 405 , 13 )
dataobjects_net_contact: query: QUERY DONE IN 0.00089597702026367 seconds
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: CONNECT: USING CACHED CONNECTION
dataobjects_net_contact: QUERY: INSERT INTO net_contact (net_id , contact_id ) VALUES ( 405 , 111 )
dataobjects_net_contact: query: QUERY DONE IN 0.001162052154541 seconds

[2005-03-31 01:20 UTC] andrew dot clark at ucsb dot edu

Great to hear! I'll try CVS again and verify. crossLinkExtraFields with selects would probably require hierselects, eh? Any idea how onerus implementing that would be? I would prefer multi-select to checkboxes, if possible.

[2005-03-31 01:27 UTC] andrew dot clark at ucsb dot edu

Seems to work for me using CVS.