On Dec 4, 2009, at 9:19 AM, Kieran Kelleher wrote: > Actually, let me rephrase that. My brain was not working earlier since I had > not yet had breakfast :-)
Mmmmm. Guinness! > The lack of deferred constraints prevents one using foreign key constraints > in MySQL (InnoDB specifically supports FK constraints). The problem is that > MySQL will not let you delete related rows during a transaction, rather than > let you execute the full statement and then check for FK integrity afterwards. Exactly, except that if EOF happens to be in a good mood and gets the right sequence by accident then it will work. > Please correct me if I am mistaken in my thinking here, but in WebObjects, > the "Deny" delete rule of a relationship essentially does the same thing. > Also, in java code, we have the ERXGuardedInterface~ (from now on let's say > "~" means "or a class spelled something like that) which implements > canDelete() and that allows us to prevent deletion based on logic. We also > have validateForDelete to prevent deletions in EOF logic. > > Since I have never used FL constraints in MySQL, Chuck or someone else can > explain their exact role/benefit better I am sure. If other things are touching your DB, it protects your referential integrity. I have written apps that have to deal with FKs being invalid due to no constraints and it is a PITA. Doable, especially with Wonder, but still, I like being able to assume that the FK constraint has my back. > Now on the orphan issue. If you have orphans, it means that the FKs did not > get nullified or cascade deleted or sth like that. It means your database did > not roll back the failing transaction fully. The reason transactions protect > you is because when you delete an object and the related items that have a > Nullify relationship Delete rule, WO updates those foreign keys to null in > the same transaction. Or that some other application is touching your DB. > And if you are dealing with orphans on a MySQL database, it means one of > three things are wrong. > > (1) ... > (2) ... > (3) ... > (4) ... (5) You're DB doesn't live in a hermetically sealed environment and other things can change your data without EOF's blessing. Even if the only thing touching my app's DB today is my EOF app, I don't know that that is going to be the case in the future and I can't rely on EOF keeping maintaining Referential Integrity. Call it future-proofing, CYA, Anal Retentiveness, whatever, but if I can enforce referential integrity in the DB, that's where I will do it, and not being able to is a BIG downside to me. Dave > > > On Dec 4, 2009, at 7:57 AM, David Avendasora wrote: > >> >> On Dec 4, 2009, at 7:17 AM, Kieran Kelleher wrote: >> >>> Fair enough. Finally, we have one specific strike against it. ;-) >>> >>> Since we have Delete rules in the EOModel, is this feature a "safety net" >>> that is needed for external non-WO apps that are accessing the database? >> >> What do you mean? That EOF will clean up orphaned objects that didn't get >> cascade-deleted by EOF when the related object was deleted? No. If EOF loads >> rows that have invalid FKs in them, it will create a fault for that object, >> then when you go to try to follow that relationship and the fault is fired, >> you'd get a missing object exception. I've had to deal with this exact >> situation before. >> >>> I have never implemented constraints and have yet to have an orphan record >>> since transactions/rollback protect against that, right? >> >> How would transactions protect you from having an invalid FK if you don't >> have any FK constraints? >> >> Dave >> >> >>> >>> -Kieran >>> >>> On Dec 4, 2009, at 12:39 AM, Chuck Hill wrote: >>> >>>> >>>> On Dec 3, 2009, at 5:44 PM, Lachlan Deck wrote: >>>> >>>>> On 04/12/2009, at 12:25 PM, Kieran Kelleher wrote: >>>>> >>>>>> I was just wondering why people were saying disaster, toy, etc .... >>>>>> wondering if I am missing something and going to lose all my data next >>>>>> week! >>>>>> >>>>>> Like I said, I have not used FrontBase or PostgreSQL in production and >>>>>> have never touched PostgreSQL, so if it is comparison you are after, I >>>>>> don't have one. However I will say that I started using MySQL at 4.0, >>>>>> then 4.1 and now 5.0. Being the stickler for learning as much as I think >>>>>> I need to do something right, I bought the original Jeremy Zawodny book >>>>>> "Advanced MySQL" and that gave me a clear understanding and confidence >>>>>> of how to set the thing up. I have never used the cluster engine >>>>>> (NDB).... yet. I have always used InnoDB. I used MyISAM once for a >>>>>> readonly database (about 5 tables only) that has geocode lookups on >>>>>> tables of about 100 million rows because at the time it appeared faster >>>>>> (with mysql 4.0 at the time) to do points in radius operations which >>>>>> sometimes selected up to 500,000 rows in a select. My main ongoing >>>>>> project is InnoDB and every user is a user that does edits, with a small >>>>>> percentage of users absolutely hammering the database with production >>>>>> processing during business hours each day. I replicate to 3 slaves on >>>>>> that project purely for backup. It runs 24/7 and almost never have any >>>>>> "Scheduled Maintenance" downtime garbage because of the fact that the >>>>>> replication slaves are where the backups happen. One slave is remote and >>>>>> 2 onsite with the master. The binary logs on the master are written to a >>>>>> separate phyaical drive >>>>>> >>>>>> Why do I like it? >>>>>> - It is free >>>>>> - It has never left me down - no data/table corruption >>>>>> - It is simple to set up and configure >>>>>> - replication is a breeze to set up >>>>>> - It has multiple engine types for different scenarios >>>>>> - and finally the reason that most people like what they use: "I am >>>>>> comfortable with it" ;-) >>>>>> >>>>>> >>>>>> What would I like that I think I might be missing? >>>>>> - transactional structure changes (ie., create table and roll back.) >>>>>> transactions in InnoDB only apply to table/record edits themselves. >>>>> >>>>> + Deferred constraints! >>>> >>>> >>>> That is a pretty big strike against MySQL in my books. >>>> >>>> >>>> Chuck >>>> >>>> -- >>>> Chuck Hill Senior Consultant / VP Development >>>> >>>> Practical WebObjects - for developers who want to increase their overall >>>> knowledge of WebObjects or who are trying to solve specific problems. >>>> http://www.global-village.net/products/practical_webobjects >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> _______________________________________________ >>>> Do not post admin requests to the list. They will be ignored. >>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>> Help/Unsubscribe/Update your Subscription: >>>> http://lists.apple.com/mailman/options/webobjects-dev/kieran_lists%40mac.com >>>> >>>> This email sent to kieran_li...@mac.com >>> >>> _______________________________________________ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>> Help/Unsubscribe/Update your Subscription: >>> http://lists.apple.com/mailman/options/webobjects-dev/webobjects%40avendasora.com >>> >>> This email sent to webobje...@avendasora.com >>> >>> >> >> David Avendasora >> Senior Software Engineer >> K12, Inc. >> >> ***** >> WebObjects Documentation Wiki : >> http://wiki.objectstyle.org/confluence/display/WO/ >> ***** >> WebObjects API: >> http://developer.apple.com/legacy/mac/library/documentation/MacOSXServer/Reference/WO54_Reference/index.html >> ***** >> > > > David Avendasora Senior Software Engineer K12, Inc. ***** WebObjects Documentation Wiki : http://wiki.objectstyle.org/confluence/display/WO/ ***** WebObjects API: http://developer.apple.com/legacy/mac/library/documentation/MacOSXServer/Reference/WO54_Reference/index.html ***** _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
