← Back to team overview

drizzle-discuss team mailing list archive

Re: PROPOSAL: Change to the Cursor interface

 

Hi Jobin, All,

On Sun, Apr 25, 2010 at 11:03 AM, Jobin Augustine <jobinau@xxxxxxxxx> wrote:

Jobin, I read up a bit more on MERGE, and indeed it seems not trivial
to rewrite REPLACE INTO into a MERGE INTO statement.
Good point - I never gave it that much thought, so thanks for pointing that out.

>>> all these headache may go if we could discontinue support for mysqlism.
>
>>But that would postpone the problem until you would like to add
>>support for the standard MERGE INTO syntax.
>>In other words, it would need to be solved anyway.
>
> No. not requied, MERGE is bit different on its use.
> (just like its name sounds)
>
> let me explain, the overall syntax of merge will look like:
> As we can see merge works like a sub query fashion. and UPDATE and INSERT
> are part of it (3rd and 5th line).
> so in effect MERGE statement splits into a SELECT statement (1st line)
> followed by UPDATE or INSERT.
>
> Here the advantage is, UPDATE and INSERT are explicit. so no ambiguity
> remains.

Ok. So if I understand correctly, your point is that since MERGE
explicitly results in either INSERT or UPDATE, there is never any
doubt what function should be called and what it's result should
indicate.
I think that's a fair point.

That said, I don't know enough of the details of any of the APIs to
judge whether this would completely lift the need for a method that
can either insert or update.
Anyway, thanks for pointing this out to me - it's an eyeopener for me.

kind regards,

Roland

>
> But this is not the case of REPLACE, need a investigation of the return pair
> to understand actually what happened inside.
>
> Am i right?
>
> Thank you,
> Jobin.
>
> On Sun, Apr 25, 2010 at 10:24 PM, Jobin Augustine <jobinau@xxxxxxxxx> wrote:
>>
>> Hi Roland,
>>
>> Yes!. MERGE can be used as a replacement for REPLACE. :)
>> but in practical, it is difficult to write REPLACE using MERGE.
>> i hope nobody will do that,  just give a try. i bet you will give up :)
>>
>> MERGE statement is designed to serve other purposes very efficiently.
>> where we deal with multiple tables.
>> For example.
>>
>>  1. Warehousing / Reporting systems require "merging" data from normalized
>> transaction tables into different models
>>  2. Archiving old data, merge new data into your archive tables very
>> efficiently.
>>
>> I am not against any extra features like REPLACE.
>> but just conscious about the extra complexity it adds to the code bases
>> and propages to other plugins (yet to born ones also)
>> more complexity means more bugs.
>> referring above proposal, insertRecord returning a pair !
>>
>> Thank you,
>> Jobin.
>>
>>
>>
>>
>>
>>
>>
>> On Sun, Apr 25, 2010 at 1:53 PM, Roland Bouman <roland.bouman@xxxxxxxxx>
>> wrote:
>>>
>>> Actually, after some more reading I now get the impression that MERGE
>>> INTO does allow for the same flexibility in handling UPDATEs
>>> differently from the INSERTs, and is in fact even more flexible as it
>>> allows a separate set to identify the target records.
>>>
>>> On Sun, Apr 25, 2010 at 1:14 AM, Roland Bouman <roland.bouman@xxxxxxxxx>
>>> wrote:
>>> > Gah...I replied to Jobin, but forgot to reply to Jay and the list....
>>> >
>>> > here goes:
>>> >
>>> > Hi Jobin,
>>> >
>>> > On Sat, Apr 24, 2010 at 10:20 PM, Jobin Augustine <jobinau@xxxxxxxxx>
>>> > wrote:
>>> >> is this REPLACE is something so useful?.
>>> >> are developers are aware and using it to a good extend?
>>> >
>>> > In my opinion, it is useful. I don't think there is any statistic on
>>> > whether developers are making legitimate use of it. But then, this
>>> > information is also not available for any other SQL feature.
>>> >
>>> >> I don't see it in SQL standards and other planets (Postgres,
>>> >> Oracle,...)
>>> >
>>> > In the SQL standard, the MERGE INTO statement offers similar (although
>>> > not equivalent) functionality
>>> >
>>> > <merge statement> ::=
>>> >   MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
>>> >   USING <table reference>
>>> >   ON <search condition> <merge operation specification>
>>> >
>>> > (ISO/IEC 9075-2:2003 (E) 14.9   <merge statement>, page 837)
>>> >
>>> > It is similar in that matching existing rows are updated, and if there
>>> > are no such rows, they will be inserted.
>>> > Personally I find the standardese particular dense and hard to
>>> > understand, but this wikipedia entry explains it nicely:
>>> >
>>> > http://en.wikipedia.org/wiki/Merge_(SQL)
>>> >
>>> > Personally I like MySQL's REPLACE and ON DUPLICATE KEY constructs
>>> > better because they don't just merge the data, but allow you a IMO
>>> > flexible and natural way to handle the updates differently than the
>>> > inserts. But this is besides your point: the standard offers an
>>> > "Upsert"
>>> > construct just like MySQL, but it is called MERGE INTO, and not
>>> > REPLACE INTO (or INSERT ... ON DUPLICATE KEY UPDATE)
>>> >
>>> >> for me, REPLACE is as bad as silent commits (in many planets it
>>> >> happens
>>> >> before DDL)
>>> >
>>> > Ok. Then don't use it :)
>>> >
>>> >> all these headache may go if we could discontinue support for
>>> >> mysqlism.
>>> >
>>> > That would postpone the problem until drizzle would add
>>> > support for SQL standard MERGE INTO syntax.
>>> > In other words, it would need to be solved anyway.
>>> >
>>> > kind regards,
>>> >
>>> > Roland
>>> >
>>> >>
>>> >> Thank you,
>>> >> Jobin.
>>> >>
>>> >>
>>> >>
>>> >> _______________________________________________
>>> >> Mailing list: https://launchpad.net/~drizzle-discuss
>>> >> Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
>>> >> Unsubscribe : https://launchpad.net/~drizzle-discuss
>>> >> More help   : https://help.launchpad.net/ListHelp
>>> >>
>>> >>
>>> >
>>> >
>>> >
>>> > --
>>> > Roland Bouman
>>> > http://rpbouman.blogspot.com/
>>> >
>>> > Author of "Pentaho Solutions: Business Intelligence and Data
>>> > Warehousing with Pentaho and MySQL",
>>> > http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html
>>> >
>>>
>>>
>>>
>>> --
>>> Roland Bouman
>>> http://rpbouman.blogspot.com/
>>>
>>> Author of "Pentaho Solutions: Business Intelligence and Data
>>> Warehousing with Pentaho and MySQL",
>>> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html
>>
>
>



-- 
Roland Bouman
http://rpbouman.blogspot.com/

Author of "Pentaho Solutions: Business Intelligence and Data
Warehousing with Pentaho and MySQL",
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html



Follow ups

References