Re: [Reactor For CF] Duplicate Insert vs. Update?

[Jeff Lester]

I'd have to agree that this isn't a "reactor" issue. Reactor does exactly what it should and perfectly in my opinion. I would be worried about adding any more to it as it may make reactor more than it should be... a database communication API. It is a developers responsibility to ensure they follow good coding practice, even in terms of datatable design. If you are using userName as your primary key then Reactor is doing exactly what any database communication API should and updating the record with the existing userName. If you don't want it to do this then the developer has to look at their coding options. Personally, I use a UUID field as my Primary key and controllers to access Reactor from MG so that I can do any pre-validation that I require before the call. so I can userGateway and getWhere().isLike("User","userName", arguments.userName) and if recordCount GT 0 then I know it exists and to return failed to the application and tell the user to select another username. Of course, nowadays I just use the Asychronous XML Gateway to check this realtime when they onBlur the username field and notify if the username exists and disable submit until they select a username that matches. This is my opinion is really a client side / XML validation technique, not a Reactor issue.

~Jeff

On 4/27/06, Doug Arthur <[EMAIL PROTECTED]> wrote:

I'd like to chime in on this topic.

 

I may be completely off base here, but is it really a good idea to do an update when what was expected is an insert? Being that an insert was invoked? Going back to a username setup and granted, this can be prevented with a developer that knows what they are doing. But also lets face it, we all make mistakes and overlook and/or forget small things sometimes.

 

So, moving on. If I had a system where a user gets to choose their username, and create their profile, and when they click "submit" it would attempt to do an insert, but since that username is in use it will update that users data with the data that's invalid since it's coming from a user that doesn't even exist? Please note that this is not an approach I'd take on designing a system, but I'm being the devil's advocate here.

 

I'd like to see what people have to say about this further...

 

Thanks

- Doug

 

On 4/26/06, Doug Hughes <[EMAIL PROTECTED] > wrote:

In the end this isn't reactor's job.  You need to implement some custom validation routine to do what you want. 

 

Take a look at the Reactor Blog as an example.  The user management does exactly what you're talking about.  The user table has a userId and username fields.  When you validate the user it confirms that the username is not conflicting with any other accounts.

 

Doug

 

---

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Brian Kotek
Sent: Wednesday, April 26, 2006 3:14 PM

To: reactor@doughughes.net
Subject: Re: [Reactor For CF] Duplicate Insert vs. Update?

 

Yes, this is probably getting closer to the core of the problem: how to handle constraint violations using Reactor? Is this something the framework could handle, or it is something we'd need to handle in custom code (as Beth suggests)? Thanks.

On 4/26/06, Bowden, Beth (NIH/NIEHS) [E] < [EMAIL PROTECTED]> wrote:

In theory, your code would look up how constraints are defined in system tables for your database and check to see if any constraints would be violated as part of validation.  The code would obviously be database-specific.

Beth

---

From: Brian Kotek [mailto: [EMAIL PROTECTED]]
Sent: Wednesday, April 26, 2006 2:58 PM

To: reactor@doughughes.net
Subject: Re: [Reactor For CF] Duplicate Insert vs. Update?

 

That is one option, but that results in this:

Table has userID, userName, firstName, lastName columns.
UserID is primary key.

Now I could create as many duplicates with the same userName as I want, even if they have different userIDs. To avoid that, I could apply a unique constraint to userName.

Now what happens if I try to create a new record with a different userID but the same userName as an existing record? The insert will throw an error due to the violation of the unique constraint on userName.

Basically it seems like any way you slice it, it will require some custom code to either: validate in advance any unique constraints the table may have, or to catch these errors at insertion time, and then generate a user friendly error message accordingly.

Maybe some custom code/error handling the only way to solve the problem (I realize Reactor isn't going to do all my work for me). I just wanted to present the issue and see if anyone else had encountered it and thought about a solution.

Thanks,

Brian

On 4/26/06, Doug Sims < [EMAIL PROTECTED]> wrote:

I would switch to an ID field for the primary key, rather than using the username. you can still force that to be unique, just use an ID to locate and identify records.
Doug S.

 

On 4/26/06, Brian Kotek < [EMAIL PROTECTED]> wrote:

Lets say you have a table called user, with a primary key of userName, and then firstName and lastName columns. If you create a new user and populate a UserRecord with these values and call save(), it will insert this record into the database. This is fine. The problem (as I see it) is this: Later on lets say another new UserRecord is created with the same userName but different firstName and lastName values. When you call save(), it will silently update the existing record instead of telling the user that they are trying to insert a record with a duplicate userName. This seems to be a problem (and a common one at that).

I have a few ideas on how this might be handled:

I could use custom validation code that checks for an existing record and adds an error message to the ErrorCollection. But in that case I'd also need to pass some kind of argument to the validate() method to stop it from doing this check when I am truly editing an existing record and not trying to insert a new one.

I could also not use save() on the record, and instead call the DAO insert() method directly. However, this seems like it is "too late" to be catching something that should probably be caught in the actual validation logic instead, and it would also still mean catching the error and adding something to the ErrorCollection to explain what is wrong.

Am I making sense? I was really wondering if anyone else has run into a similar situation and how they handled it? Also please let me know if you think something is wrong with the premise. Thanks.

 

On 4/26/06, Doug Hughes < [EMAIL PROTECTED]> wrote:

From my perspective there is no problem…. Am I missing something?

 

Doug

 

---

From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Brian Kotek
Sent: Wednesday, April 26, 2006 2:11 PM
To: reactor@doughughes.net
Subject: Re: [Reactor For CF] Duplicate Insert vs. Update?

 

Yep, that's the same method that save() uses to determine whether to insert or update. I just wasn't sure if that is the way others are doing this or if anyone else has even run into the same problem.

On 4/26/06, Bowden, Beth (NIH/NIEHS) [E] < [EMAIL PROTECTED]> wrote:

The base dao.cfc has an "exists" function that returns true or false if the record already exists.

 

Beth Bowden

Phone: 919-541-3355 FAX: 919-541-3687
E-Mail: [EMAIL PROTECTED]
Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709

---

From: Brian Kotek [mailto: [EMAIL PROTECTED]]
Sent: Wednesday, April 26, 2006 2:04 PM
To: reactor@doughughes.net
Subject: [Reactor For CF] Duplicate Insert vs. Update?

 

Just thought I'd ask in case I'm missing something within the API. I just realized that if a user attempts to insert a duplicate record using the same primary key values as an existing record, when you call save() reactor will treat it as an UPDATE instead of throwing an error due to a duplicate primary key. I'm sure I could create something that checks to see if the record already exists (which is really what save() does under the hood) and skip the save(), but I wanted to ask the group first if they have any other ideas. Thanks.

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/