Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Please provide a way to get matched/found rows for UPDATE queries #71

Closed
jeremyevans opened this issue May 3, 2023 · 3 comments
Closed

Comments

@jeremyevans
Copy link
Contributor

I recently added a Sequel adapter for trilogy: jeremyevans/sequel@0701217. I found trilogy very easy to use and that made the adapter quite easy to create. Thank you for that!

One issue I ran into is that trilogy does not return the number of matched rows in an update statement, it returns the number of rows modified. For example:

c = Trilogy.new(...)
c.query('CREATE TABLE t(a integer)')
c.query('INSERT INTO t VALUES (1)')
c.query('UPDATE t SET a = 1').affected_rows
# => 0 # instead of 1

I think this is MySQL default behavior, so it seems reasonable, but most other databases do not operate this way, instead returning the number of matched rows. It there a way to get the above code to return 1? The mysql2 gem supports this using flags: Mysql2::Client::FOUND_ROWS (which looks like it comes from CLIENT_FOUND_ROWS in the mysql driver). If there is not currently a way, could support be added for it?

This affects Sequel because by default, Sequel checks during model instance updates that one row was updated. If zero rows were updated, that by default indicates to Sequel that the underlying database record was already deleted, in which case Sequel should raise an exception. I disabled that part in Sequel when trilogy is used, but it would be nice to be able to enable it.

There are a few other parts of Sequel that don't work perfectly with trilogy, but those are also issues with mysql2, or deliberately unsupported by trilogy (lack of application_timezone).

@composerinteralia
Copy link
Contributor

composerinteralia commented May 4, 2023

That's fantastic. Thank you!

Based on https://github.com/github/trilogy/blob/7ceee303b7d440f0b26cba65d00032bfb897c0ef/inc/trilogy/protocol.h#L33 and https://github.com/github/trilogy/blob/7ceee303b7d440f0b26cba65d00032bfb897c0ef/contrib/ruby/ext/trilogy-ruby/cext.c#L454-L456 I think we might already have that by passing found_rows on connect: Trilogy.new(found_rows: false. Is that sufficient?

@jeremyevans
Copy link
Contributor Author

That works, thank you very much! Sorry I couldn't find it. I'll update the Sequel trilogy driver to use that.

@jeremyevans
Copy link
Contributor Author

Here's the change to Sequel: jeremyevans/sequel@41e621f

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants