Setting lower_case_table_names = 1 on MySQL results in AttributeError about weewx HOT 8 CLOSED

I've done a more thorough look at this, and have come up with 3 options:

1. Do case-insensitive comparisons. This would break skins that depend on case to distinguish between SQL columns. Don't know how many of them are out there.
2. Store the actual (case-sensitive) names as metadata, in a new table.
3. Do nothing. We just don't support cross-platform MySQL (which is the only reason to choose to set lower_case_table_names=1)

Presently, I'm favoring 3. Otherwise, option 2.

from weewx.

which approach adheres to the sql standard? note that the sql standard and what mysql does are not always the same thing. i as because at some point it would be nice to have built-in support for postgresql.

from weewx.

Beats me. Everything I'm seeing on the web says, "It depends."

As I understand it, postgresql is case-insensitive unless you quote the table/column names. Generally, in weewx, nothing is quoted. One exception: column names are back quoted (to get around interval being treated as a key word by MySQL).

Are you aware of anyone depending on case-sensitive names?

from weewx.

i do not know of any case-sensitive dependencies in existing weewx extensions.

here is a pretty good summary of the situation across different sql implementations:

http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

the conclusion: The safest choice if you care about portability and peace of mind is not to quote the identifiers when you create your tables/fields and when you run your queries.

here are additional references:

Some systems (like PostgreSQL) are case sensitive in table and column names, but attempt to hide it through lowercasing or uppercasing all names before looking them up. On these systems you will have to enclose the table name in "double quotes" to make sure the exact name you entered is looked up.

http://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive

MySQL represents databases and tables using directories and files in the underlying filesystem on the server host. As a result, the default case sensitivity of database and table names depends on how the operating system on that host treats filenames. Windows filenames are not case sensitive, so a MySQL server running on Windows does not treat database and table names as case sensitive. Servers running on Unix usually treat database and table names as case sensitive because Unix filenames are case sensitive. An exception is that names in Mac OS X Extended filesystems can be case insensitive.

http://www.informit.com/articles/article.aspx?p=2036581&seqNum=3

from weewx.

the conclusion: The safest choice if you care about portability and peace of mind is not to quote the
identifiers when you create your tables/fields and when you run your queries.

Which is exactly what we do, except for backquoting the column names when creating a table. (Backquoting is necessary because the column name interval is a reserved keyword in MySQL. Backquoting doesn't change the case, it just "escapes" the recognition as a keyword.)

The problem is when it comes time to recognize those columns. If lower_case_table_names=1 has been set, a select statement such as

SELECT mintime FROM ArChiVe_Day_OutTemp;

works just fine, but if you try

SHOW TABLES;

you'll get back archive_day_outtemp. The manager uses this name to figure out what observation types are in the database, so it gets outtemp and thinks it is different from the outTemp being offered in the archive record. An exception occurs.

The problem is not so much on the database end, as in the interaction of a now case-insensitive database with the still very case-sensitive Python language.

Anyway, if I'm reading the article you linked to correctly, we should be assuming that all identifiers are case-insensitive. This means we should be converting outTemp to outtemp, etc., before comparing. As for the keyword interval, it would not be hard to backquote only its use, and no other identifiers.

from weewx.

we might have problems if we lowercase everything. DB2/Oracle does an uppercase of everything, PostgreSQL does a lowercase of everything, and MySQL depends on the platform's file system.

perhaps a small test suite would answer this definitively. could we create the 3/10/whatever examples of sql that are representative of what happens in weewx, then use that in a unit test to see exactly what we must do to make weewx work across databases and database configurations, or at least definitively say which will be compatible and which will not?

from weewx.

Sorry, I didn't make myself clear. Both the observation type and what's coming from the database would be lower-cased. So, it doesn't matter what the database does with it.

I'm pretty sure most of weewx will not be touched by this. Only the logic that determines whether a daily summary is available for a type.

In any case, it's too late for V3.2. Let's wait until 3.3. We've got to stabilize this thing.

from weewx.

This issue has been kicking around for nearly a year and a half and, in that time, no new users have reported having this problem. So, I guess, there are not that many people doing cross-platform MySQL (that is, who have set lower_case_table_names=1 in their MySQL configuration).

So, I say we punt and choose my option number 3: do nothing. The additional complexity is not worth it.

from weewx.