On Wednesday, December 30, 2015 at 5:04:26 PM UTC-8, [email protected] 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 post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
