+1 Not so sure about the insert default param.
On Tuesday, April 3, 2012 at 7:36:16 PM UTC+5:30, ducktype wrote: > > Hi all, as Luca suggested in: > http://code.google.com/p/orient/issues/detail?id=766 > i'm posting here some suggestions/ideas i think make life easier in some > common cases, > to see if someone likes it or even want to implement some or all of it. > > In my experience the 90% of cases where one use insert and update are > precedeed by a select to know if the record already exists or is new one, > actually they are so called UPSERTs. > > I see in RC9 the SET syntax is good also for INSERT (like mysql and others > do but not SQL Server) and this simplify the "SQL Command String" creation > but still require the SELECT to switch between INSERT/UPDATE. > > Many db have a "limited" version of this functionality (MERGE/REPLACE... > statements), limited because: > > > 1. You can't specify in the statement which fields are to considered > unique, they always use the primary key or unique index defined in the > destination table, but this shoud be an OVERRIDABLE default NOT the only > way. > > 2. You can't specify which fields are only for insert, only for > update, or for both: for example is useful to always specify the field: > inserted_at giving it the current timestamp as value but telling the > statement to INSERT ONLY the field and leave it untouched in the case of > an > UPDATE. > > 3. A way to avoid another very common select after the UPSERT to > "reload" the inserted/updated row to ensure you have fresh values of the > fields you need, think of triggers and other cases that modify the row > value db side etc. > The orrible GET_LAST_ID() way, that many db encourage to do, seems a > weird and un-elegant solution to me. > > A better solution to this is a RETURNING/OUTPUT clause availabe in > INSERT/UPDATE(/and UPSERT), like some db do (only MySQL does not like it) > and make the statement return the result as a normal SELECT statement. > http://msdn.microsoft.com/en-us/library/ms177564.aspx ( OUTPUT ) > http://www.postgresql.org/docs/8.3/static/sql-insert.html ( RETURNING ) > > 4. Another minor one is the LIMIT clause on UPDATES (like SQL server > and others have) to ensure only this amount of records gets updated. > > *This one is already implemented in orientdb but undocumented. see: * > http://code.google.com/p/orient/wiki/SQLUpdate > > 5. "NULL-safe equal": > Another things that i find useful is a "NULL-safe equal" operator as > MySQL call it: > > > http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to > obviusly as the OUTPUT|RETURNING clause this operator make sense in > every conditional expression not only in upserts. > > This operator avoid the otherwise required: > if(value == null){ sqlcommand += " IS NULL " } else { sqlcommand += " > = " + value } > in the generation on sql commands client side, for example in where > conditions. > > And also seems simpler more powerful than having a global/session ANSI > ON/OFF switch? > > > UPSERT SYNTAX PROPOSAL: > > So hoping that this make sense also to you, and you feel frustrated like > me when you need to workaround all this weird things, i'm proposing this > syntax: > > UPSERT <class> SET <field-name> [I,U,D,*]= <field-value>,... > [OUTPUT|RETURNING *|<field-name>,...] [WHERE <conditions, default to the > unique keys>] > > [I,U,D,*] > I: ONLY for INSERT > U: ONLY for UPDATE > D: REINSERT DEFAULT FIELD VALUE (make sense??) > *: BOTH INSERT/UPDATE > > Obviusly feel free to rethink it as you want and refine my points/syntax > but i see this things as a clear requirement of any db that understand how > people/programmers use it 90% of the time. > > > PS: > Sorry for the eventually bad english. :) > > > > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
