search

damil / SQL-Abstract-More

extension to SQL-Abstract with named parameters and support for several additional SQL clauses
https://metacpan.org/pod/SQL::Abstract::More
6 stars 10 forks source link

Add support for ignore #17

Closed rouzier closed 3 years ago

rouzier commented 4 years ago

Bump

rouzier commented 3 years ago

Bump.

damil commented 3 years ago

Hi. Sorry for having ... ignored (sic!) your request for so long.

I had no idea about this IGNORE keyword in SQL, and found no information in your pull request. Now I did some research; apparently this is specific to MySQL / MariaDB. Oracle has no such syntax. Sqlite has something quite similar, but with more options : you can write INSERT OR IGNORE but also INSERT OR ABORT, INSERT OR REPLACE, etc. Postgresql has a postfix clause ON CONFLICT DO NOTHING.

Since this stuff is quite specific, I am reluctant to add a hardcoded "ignore" parameter. Probably better would be to provide a hook to insert any string supplied by the client -- something like ->insert(-into => $table, -values => ..., -prefix_sql => 'IGNORE') # MySQL ->insert(-into => $table, -values => ..., -prefix_sql => 'OR ABORT') # SQlite ->insert(-into => $table, -values => ..., -postfix_sql => 'ON CONFLICT DO NOTHING') # Postgresql

but I'm not totally happy with these parameters -prefix_sql and -postfix_sql. Another idea would be to add a callback as a coderef that receives the SQL and can do any transformation on it before returning the result.

Any thoughts on this ? Thanks for your proposal anyway.

rouzier commented 3 years ago

No problem. The callback seems to be the best approach overall since it allows the most flexibility.

damil commented 3 years ago

Actually, there is already a way to include the IGNORE keyword (or any other keyword) just after the insert : see https://metacpan.org/pod/SQL::Abstract::More#-columns-=%3E-\@columns

$sqla->select(-from => 'Foo', -columns => [-IGNORE => @column_list])

damil commented 3 years ago

my last comment was stupid : the initial keyword in -columns only works for insert(), which is not relevant for IGNORE.

So in the end the solution is a new parameter "-add_sql" for insert(), delete() and update(). Released in v1.34.

rouzier commented 3 years ago

No problem, I have been quick on the reply before.

Thanks for the update on this.

James