[development] Referential integrity -- finally?

[Morbus Iff]

> In other words: I'd happily accept patches that add foreign keys to 
> Drupal tables.  It woud not be perfect as we can't use cascading 
> deletes, for example.  Nevertheless, it would be a great step in the 
> right direction -- assuming we can make it all work in a SQL-compliant 
> way without leaving MyISAM users in the cold.

I'm personally not a fan of it, primarily because it caused me headaches 
in the past. I had to deal with a rather large import to CiviCRM, which 
is all InnoDB and foreign keys and, for speed reasons (2 million 
records), could not use their API. This forced me to add the records to 
the tables manually. And, since the import data was always changing, I 
had to code the importer in such a way that it could start over - delete 
all the import data and prepare the database for a fresh import. Foreign 
keys added tons and tons of time to this - both for the import (though, 
I have no true facts to back this up) and for the actual deletion, since 
the rows had to be deleted in quite a specific way, otherwise the 
foreign keys would complain about splintered data.

Likewise, all the foreign keys in CiviCRM actively caused lookups to 
slow down tremendously - for a 2 million person database (with roughly 
three tables having 2 million rows, and one having nearly 10 million), I 
eventually had to remove all the foreign key constraints (along with a 
number of other optimizations that were contributed back to dlobo and 
CiviCRM 1.5).

To some degree, "referential integrity" sounds all awesome and "yay, 
save me from myself" but, really, Drupal's been working just fine 
without it, and I see no huge reason to just "add it on".

And, for the sake of those not on infra, my comments from there:

For what it's worth, I'm not a fan of InnoDB myself. A default 
configuration of InnoDB uses a single database file that can grow quite 
large (2GB+ for one of my CiviCRM installations), and then there's all 
the index-y and cache tables (whose purpose I never really grasped). 
Unless you have binary logging turned on (which, in turn, create more 
files), you have a single point of failure. Single points of failures 
are never a good thing. Yes, there's a configuration option in newer 
MySQLs to force InnoDB back to one file per table (as per MyISAM). 
Still, the likelihood of any of our common users being able to tweak 
that IS NULL.

-- 
Morbus Iff ( i still fail to see what this has to do with morocco )
Technical: http://www.oreillynet.com/pub/au/779
Culture: http://www.disobey.com/ and http://www.gamegrene.com/
aim: akaMorbus / skype: morbusiff / icq: 2927491 / jabber.org: morbus