The delete method is rather inefficient. When called, Class::DBI will issue one DELETE statement for each row in your table. Instead of producing

DELETE FROM page WHERE user_id = 1
DELETE FROM user WHERE user_id = 1
[download]

It does

DELETE FROM   page WHERE  page_id = '1'
DELETE FROM   page WHERE  page_id = '2'
DELETE FROM   page WHERE  page_id = '3'
DELETE FROM   page WHERE  page_id = '4'
DELETE FROM   page WHERE  page_id = '5'
DELETE FROM   user WHERE  user_id = '1'
[download]

which can be quite long for large data sets. The sad thing is that, even if I use a database that enforces referential integrity, Class::DBI will still override the database features and issue the same DELETE statements. In your example, if you replace the table definition to use the InnoDB table type, adding

FOREIGN KEY (user_id) references user (user_id) ON DELETE CASCADE,
KEY user_id (user_id)
# and replace "MyISAM" with "InnoDB" for both tables
[download]

You can then get the same behavior with

%dbh->do("DELETE FROM user WHERE user_id=1")
[download]

and the database engine will take care of cascade deletiing the appropriate records in "page".

I would value your comments on the DELETE case as well!

jdtoronto
PS Do you know of any examples I could look at of multi-table JOIN's in Class::DBI? ..j

I think he's just saying that Class::DBI implements its own "delete on cascade" feature, and it isn't as effeicient as what the database can do. I don't think this will be a problem, since Class::DBI will simply not find anything to delete if you have your database do the cascade. And again, Class::DBI definitely can't violate any integrity constraints.

I have understood that there was no problem to use DBI with InnoDB tables, but I still have a problem :

I used to have MyISAM tables where everything was working fine, and I created a whole new set of InnoDB tables.
When I call Inserts on these new tables, it does NOT insert my values, and DBI::execute is not returning any error, still the 'auto_increment'... increments (on table status).
Table is :
______________________________
CREATE TABLE `log` (
`id` int(11) NOT NULL auto_increment,
`timestamp` datetime NOT NULL default '0000-00-00
00:00:00',
`alert` enum('y','n') NOT NULL default 'y',
`id_daemon` int(11) NOT NULL default '0',
`id_event_type` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `id_daemon` (`id_daemon`),
KEY `id_event_type` (`id_event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
___________________________________
Foreign keys :
ALTER TABLE `log`
ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`id_daemon`)
REFERENCES `daemons` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `log_ibfk_2` FOREIGN KEY (`id_event_type`)
REFERENCES `event_types` (`id`) ON DELETE CASCADE;
___________________________________

Code :
$req = "INSERT INTO log
(`timestamp`,`alert`,`id_daemon`,`id_event_type`)
VALUES
(NOW(),'y','31','3')";
$query = $dbh->prepare($req);
$query->execute or sayAll("Query failed : $req".$query->errstr);

When I paste the request into 'mysql' in command line, or by phpMyAdmin... it works... but not with DBI.

Can anybody bring me help ?