On Mon, Mar 29, 2021 at 7:33 PM J. Lewis Muir <[email protected]> wrote:
> Is there a way to use all-lowercase SQL in Database#fetch (or similar) > using the Mysql2 adapter and have Sequel automatically convert it to the > correct case? > > I'm trying to track down a DB issue in a PHP app that is not mine, and one > of the SQL queries that it executes is very long and written in > all-lowercase even though the MariaDB DB that executes the query uses camel > case table names and column names and is not case insensitive. I'd like to > be able to experiment with the SQL query on my own outside of the PHP app. > I can't just copy-and-paste the SQL into the mysql command line DB client > because it considers the all-lowercase names to be invalid. For example, > if a table name is "Customers" and the SQL refers to it as "customers": > > ERROR 1146 (42S02): Table 'northwind.customers' doesn't exist > > I thought of Sequel and wondered whether maybe it had support for > automatically figuring out the correct case for table names and column > names, but it seems not because I get the following exception: > > client.rb:131:in `_query': Mysql2::Error: Table 'northwind.customers' > doesn't exist (Sequel::DatabaseError) > > Is there a Sequel extension or plug-in to do this? > > Or, sorry, perhaps not related to Sequel at all, but does anyone have > suggestions for how to deal with something like this? Obviously, I could > just edit the SQL by hand and fix all the case errors, but that would take > me a long time. Or, I could create a set of transformations to convert the > all-lowercase SQL into the correct SQL. That would still take a long time, > but it would be reusable. Both of these are far from automatic, though. > In terms of SQL, Customers, customers, and CUSTOMERS are all different tables, if quoted. In the SQL standard, unquoted identifiers are folded to uppercase. SQLite, PostgreSQL, and MySQL fold unquoted identifiers to lowercase, but other databases generally follow the SQL standard in this area. I'm assuming in your case, the table name is actually Customers and needs to be quoted, because any unquoted identifier would be treated as customers. However, copying the SQL used by the PHP app and executing it in the mysql shell should work. If it doesn't, I don't have much in the way of ideas. Maybe there is some magic they are doing to make MySQL treat the identifiers as case insensitive. Some searching brings up https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html, but I'm not sure how much of that is related to your problem. In any case, there isn't anything in Sequel that will automatically fix incorrect table names and make them correct table names. Long term, fixing all the case errors is the best approach, IMO. 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/CADGZSSeYX99zD8vWYCzOY%2BdPXMJxGc6X56ss3CCZxZaeQ%2B0Abw%40mail.gmail.com.
