Case sensitivity of table names


#1

I just came accross a problem with moving a mysql DB from my develop machine (Windows) to the live server (Linux).
The code that worked on my dev machines has thrown an error on the live machine saying the column_name in my query doesn’t exist.

The code was absolutely the same and the generated query still works fine on my windows machine.

The reason was found very easily: Table- and columnnames on Mac/Linux are case sensitive. On windows the are not.

This mistake was one I coded by my self. I did the following select:

var qTemp = presideObjectService.selectManyToManyData(
	objectName   = "homepage"
, propertyName = "pageElements"
, filter 			 = { "id" = arguments.id }
, selectFields = [...]
, orderby      = "homepage__join__pageelement.sort_order"
);

The generated query did all correct and was generating a perfect code:

select ... 
from `psys_homepage` `homepage` inner join `psys_homepage__join__pageelement` `homepage__join__pageElement` on (`homepage__join__pageElement`.`homepage` = `homepage`.`id`) 
inner join `pobj_pageelement` `pageElements` on (`pageElements`.`id` = `homepage__join__pageElement`.`pageElement`) 
inner join `psys_page` `pageElements$page` on (`pageElements$page`.`id` = `pageElements`.`page`) 
where `homepage`.`id` = 'E6501249-3324-40EF-AD4D2C6B1D940374' order by homepage__join__pageelement.sort_order

What you see is, that Preside generated the table-alias homepage__join__pageElement. But in my order attribute I wrote it in a different case: homepage__join__pageelement.sort_order

This is the reason, why my query failed on Linux.

On possibility would be to disable case sensitive tablename with a global setting (lower_case_table_names). But as said, it’s a global setting for the whole server. And maybe you’re running a Galeracluster with 100 DBs for already five years and don’t want to change this setting.

What I like to discuss is: What if Preside would automatically lowercase all table and columnnames automatically?
Is this possible?
Would this make sense?
Preside is already doing this by generating only lowercase table and columnnames. But in the orderby and the filter it is not.

I don’t know how difficult it is to realize this, but I think if you’re lowercasing the query exept the parameter data this could work.

Thoughts? Meanings?

Michi


#2

Ah, I did not recall this - but that’s a good start. An order by can, of course, contain more than just field names, e.g.:

order by Len( my_field ) DESC

I’m not aware of any problems lowercasing those things, but not sure its right to mess here…?


#3

yes, the order by can contain more than just field names. But all the reseverd words and functions (like LEN or ASC/DESC) are already case-insensitive.


#4

The more complicated problem could be the filter:

filter = { "fieldname":"value" }

or as string

filter = "isActive = true and lastname='abc'"

But (without testing) it should be possible to lowercase everything with a RegEx except values in quotations.


#5

That’s a bunch of complexity / processing to run on every query though. I reckon we just do this:

LCase( sql )

(joking of course)


#6

To be honest, the idea isn’t that bad. In selects (of course not in Insert/Update) the case of values doesn’t matter. It doesn’t matter it your value you’re searching for is smith, SMITH or sMiTh.

But yes, this would be too simple, I think.