search

elixir-ecto / myxql

MySQL 5.5+ driver for Elixir
Apache License 2.0
273 stars 67 forks source link

Support encoding explain format=json #159

Closed vorce closed 2 years ago

vorce commented 2 years ago

Hi there, thanks for a great ecto adapter!

I would like to add support for encoding the result from explain format=json (like what postgrex can do). If this makes sense, I plan on submitting a PR to ecto_sql to expose it there as well.

I don't know if the approach here is good, feels a bit "informal" somehow. I read up on the mysql internals column definition but didn't find anything that could make it a bit more strict/solid.

I basically looked at what was coming in to MyXQL.Protocol.decode_column_def and tried to distinguish between a text/traditional explain and a format=json one. 🤔

greg-rychlewski commented 2 years ago

This will cause an issue with queries like this:

MyXQL.query!(c.conn, "SELECT 'abc' AS `EXPLAIN`")

Sorry I don't have a better idea right now :(.

vorce commented 2 years ago

This will cause an issue with queries like this:

MyXQL.query!(c.conn, "SELECT 'abc' AS `EXPLAIN`")

Sorry I don't have a better idea right now :(.

Oh no, crap. Of course. I'll see if I can fix that 😬

greg-rychlewski commented 2 years ago

I'm not sure there is anything we can do in this driver because MyXQL already understands mysql_type_json. If MySQL is returning this as mysql_type_var_string it might be gg.

vorce commented 2 years ago

Damn. Yeah I can only come up with breakable workarounds.

I'll check if it's still interesting to expose the format option in ecto_sql (Ecto.Adapters.MyXQL.Connection.build_explain_query) so that I can at least parse the string that comes back myself.

vorce commented 2 years ago

Closing this. Was fun to learn more about the internals, thanks @greg-rychlewski !