I know this is a very old thread, but it helped me find the answers I was looking for, so thanks! Just in case it's useful for anyone else, I thought I'd mention that I ended up writing this plugin <https://github.com/pact-foundation/pact_broker/blob/1830975c19b0f600cdbce0400b9f59f342f49138/lib/sequel/plugins/insert_ignore.rb> for insert ignore, and a similar one for upsert <https://github.com/pact-foundation/pact_broker/blob/1830975c19b0f600cdbce0400b9f59f342f49138/lib/sequel/plugins/upsert.rb>. It supports Sqlite, Postgres and MySQL.
Cheers, Beth On Monday, 4 January 2016 07:06:29 UTC+11, Jeremy Evans wrote: > > On Wednesday, December 30, 2015 at 5:04:26 PM UTC-8, [email protected] > <javascript:> wrote: >> >> >> >> On Wednesday, December 30, 2015 at 12:18:45 PM UTC-8, Jeremy Evans wrote: >>> >>> This will probably work for what you want: >>> >>> def create?( h ) >>> c = dataset.returning.insert_ignore.insert(new(h).values).first >>> c && call( c ) >>> end >>> >> >> Thanks very much for the pointer. With some more testing I ended up with >> this for create?: >> >> # Conditional create using PostgreSQL 9.5+ >>> # INSERT ON CONFLICT DO NOTHING >>> def create?( h ) >>> m = new(h) >>> m.before_save >>> c = dataset. >>> returning( *load_columns ). >>> insert_ignore. >>> insert( m.values ). >>> first >>> c && call( c ) >>> end >>> >> >> Note I'm calling before_save myself here as a workaround to get this hook >> that I'm using to work. It seems supporting the full set of validation and >> hooks would take a good bit more. Similarly I want to improve the upsert >> I was using in some other cases (previously with SELECT FOR UPDATE, >> INSERT/UPDATE) and the following is working: >> >> # Insert or update using PostgreSQL 9.5+ ON CONFLICT DO UPDATE >>> # using the specified key as the target constraint column by >>> # inference. This does not open its own transaction. >>> def upsert( h, key = primary_key ) >>> m = new( h ) >>> m.before_save >>> h = m.values >>> c = dataset. >>> returning( *load_columns ). >>> insert_conflict( target: key, update: mutable_subset( h ) ). >>> insert( h ). >>> first >>> c && call( c ) >>> end >>> >> >> So next I'm wondering if you would consider either some Sequel Model API >> support to make this easier and more complete, and/or some sort of model >> plugin that makes it more generically available? >> > > I don't think this is something that should be added to Sequel::Model > itself, as not all databases support this, but it should be possible to add > support for this in a plugin. The simplest way to implement it would be to > set a flag, call save, and in the internal methods, check for the flag and > use insert_ignore/insert_conflict if the flag is present. MySQL, MSSQL, > and SQLite have ways to do upserts, but with a different APIs. Ideally > such a plugin would work with all of those databases, but I'm not sure how > possible that is. > > Thanks, > Jeremy > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/b6204d52-7d82-40ca-9821-04e82a8907a2o%40googlegroups.com.
