[BioSQL-l] Re: [Open-bio-l] FK cascades

[Hilmar Lapp]

On Wednesday, August 21, 2002, at 03:23  PM, Matthew Pocock wrote:

> Hi Hilmar, just thinking out loud.
>
> Presumably MySQL < 3.23.50 will ignore FK constraints if presented 
> with them. So, the same schema deffinition can be used both for 
> both FK-aware (sane) and FK-unaware (not so sane) databases. The 
> differences would be in the adapter layer only. Is this right, or 
> have I missed something? So - one option would be to freeze/branch 
> the current perl biosql adaptor code as-is (doing FK constraints in 
> adaptor code) and develop the trunk assuming a sane RDBMS.
>

You're right -- almost. As for branch-tagging the status quo, I was 
planning to do that anyway as the changes to the adaptor layer I'm 
going to introduce are substantial, and will be largely untested in 
the beginning. As for table definitions being neutral, that's 50% 
true. While the FK statements are indeed neutral, TYPE=INNODB causes 
an error I believe if you don't have the InnoDB table handler 
activated. (The default mysql executable doesn't have the InnoDB 
handler included; you need to run (and install) mysqld-max.)

> Matthew
>
> ps is it just me, or is MySQL the odd one out with all these 
> issues - FK, unique ID generation, transactions, nested joins and 
> so on?
>

No, it's not just you. Have you read in their documentation how they 
defend their decisions, and the absence of various features that 
even made it into SQL92? It's pathetic. BTW views have been 
postponed to 5.0, after promising them for more than a year for 4.0. 
Pathetic, too. Imagine migrating an enterprise-scale db-application 
with hard-coded joins all over the place to a data warehouse. 
Fortunately, some dot-coms did afford a sane RDBMS, otherwise the 
Nasdaq would be even lower.

Sorry about the rant.

	-hilmar

> Hilmar Lapp wrote:
>> Maintaining is FK integrity in the SW layer is not only inevitably 
>> unsafe and prone to slip, it also makes for _ugly_ code in the 
>> adaptors.
>> I don't know exactly what Postgres is capable of, but I'd assume 
>> it enforces FK constraints and can do cascading deletes. Oracle 
>> does. It seems InnoDB from one of the most recent versions on can 
>> do, too.
>> How much outcry would it cause to require MySQL-Max > 3.23.50 to 
>> run BioSQL, and then migrate to using InnoDB as the table handler? 
>> (There are couple problems with the InnoDB implementation of FK 
>> constraint enforcement, but if no-one messes with the table 
>> definitions and the order they are created, these can be handled I 
>> believe.)
>>     -hilmar
>> (Still cross-posting, but soon I'll post to the biosql-list only. 
>> Hopefully I'm not going to be alone there ...)
>> -- -------------------------------------------------------------
>> Hilmar Lapp                            email: lapp at gnf.org
>> GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
>> -------------------------------------------------------------
>> _______________________________________________
>> Open-Bio-l mailing list
>> Open-Bio-l@open-bio.org
>> http://open-bio.org/mailman/listinfo/open-bio-l
>
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
>
>
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------