On 6/3/07, David Strauss <david@fourkitchens.com> wrote:

Assuming the foreign keys patch to the schema API makes it in, there are four ways we can approach foreign keys in Drupal 6.

1. Don't use them at all. Just provide the schema API for contrib modules with higher database requirements than core.

This is the path of least resistance, but Dries said that he would consider referential integrity stuff, so we should try. 2. Configure foreign keys as RESTRICT on delete. This will have no

effect on MyISAM, but it will improve code quality because operations creating inconsistency would fail. Such operations would then be rewritten to manipulate the database in a consistent way.

This seems the most sensible given the state of affairs (MyISAM, ...etc.). 3. Configure foreign keys as CASCADE on delete and have extra PHP run

whenever a query manipulates a database using MyISAM. Basically, whenever a DELETE query gets sent to a MyISAM system, we would convert it to a SELECT query to identify the rows that would be deleted. Then, we would delete the records attached with foreign keys. Finally, we would run the original DELETE query. This would have to be recursive.

This is moving the database layer work into application code, which is not a good thing. 4. Configure foreign keys as CASCADE on delete and have modules

conditionally run queries in their deletion hooks depending on the storage engine. If a module doesn't check the storage engine type, the DELETE it runs would simply be redundant.

Same as my comment on #3. Personally, I think #2 would be a nice first step in Drupal 6. It would

at least force us to clean up the database interaction so #3 or #4 could be seamlessly added later.

Agreed. -- 2bits.com http://2bits.com Drupal development, customization and consulting.

----- Start Original Message ----- Sent: Sun, 03 Jun 2007 22:14:53 -0500 From: David Strauss <david@fourkitchens.com> To: development@drupal.org Subject: [development] Foreign keys in Drupal 6

Assuming the foreign keys patch to the schema API makes it in, there are four ways we can approach foreign keys in Drupal 6.

1. Don't use them at all. Just provide the schema API for contrib modules with higher database requirements than core.

I do not even consider this an option.

2. Configure foreign keys as RESTRICT on delete. This will have no 3. Configure foreign keys as CASCADE on delete and have extra PHP run

OK, someone else will probably need to translate this to layman terms. But here is the thing: if we add PK support then we have a graph (DAG, actually) where the vertices are the tables and the edges are the PK relationships. In order to properly simulate RESTRICT you need to topologically sort this DAG so at any time you only delete tables that have no outgoing edges. At this point you have simulated CASCADE as well... I have the supporting code (top.sort) for this in my sandbox, originally written to replace module.weight hence the name weights.php . So, CASCADE.

On 04 Jun 2007, at 05:14, David Strauss wrote:

Assuming the foreign keys patch to the schema API makes it in, there are four ways we can approach foreign keys in Drupal 6.

1. Don't use them at all. Just provide the schema API for contrib modules with higher database requirements than core.

2. Configure foreign keys as RESTRICT on delete. This will have no effect on MyISAM, but it will improve code quality because operations creating inconsistency would fail. Such operations would then be rewritten to manipulate the database in a consistent way.

3. Configure foreign keys as CASCADE on delete and have extra PHP run whenever a query manipulates a database using MyISAM. Basically, whenever a DELETE query gets sent to a MyISAM system, we would convert it to a SELECT query to identify the rows that would be deleted. Then, we would delete the records attached with foreign keys. Finally, we would run the original DELETE query. This would have to be recursive.

4. Configure foreign keys as CASCADE on delete and have modules conditionally run queries in their deletion hooks depending on the storage engine. If a module doesn't check the storage engine type, the DELETE it runs would simply be redundant.

Personally, I think #2 would be a great first step. We could then consider #3 or #4 for Drupal 7. -- Dries Buytaert :: http://www.buytaert.net/

David previously listed four options for using foreign keys in Drupal on this mailing list. I previously voted in favor of making foreign keys RESTRICT on delete so the database simply refuses to delete a row that is a foreign key of another table row. I now realize that isn't possible. When we disable a module, we do not delete its tables, but we stop calling its hooks. e.g. In RESTRICT mode, if we disable the book module, it becomes impossible to delete any node previously in a book because the book table will have a row with a foreign key into the node table for that node. Not good. (Incidentally, this also reveals that node_delete() will need to call nodeapi('delete') before deleting the row from the node table.) This leaves us to choose between not using foreign keys at all or using CASCADE on delete. If we use CASCADE and rely on it to delete rows, we will have to implement the cascading-delete logic within Drupal itself for engines that do not support it. I don't think we want to deal with this. Also, it will require using an abstract API for all DELETE SQL queries which many here have opposed for anything but DDL statements. Frankly, the more I think about it, the more I realize referential integrity is just not doable for Drupal 6. However, if we still want to try, my suggestion is: 1. We set foreign keys as CASCADE on delete. 2. We specify that modules MAY NOT depend on the CASCADE deletion; they must continue to execute their own DELETE queries, because the db engine may not support CASCADE deletion. The DELETE queries will be redundant but harmless (deletion is infrequent enough not to be a performance issue, right?). 3. On engines that do support CASCADE deletion, the fact that we are using CASCADE means that deleting nodes or other objects referenced by disabled modules will continue to work; the dependent rows will be deleted. On non-supporting engines, the rows created by disabled modules will continue to exist, but that is just status quo. Comments? Barry