can sqlite3 go case-insensitive?

Railsers:

Our tests are getting slow, so among the potential speed boosts we are trying the sqlite3 driver.

But someone here (certainly not me!) wrote tests with sloppy case! MySQL doesn't mind.

While I fix these tests, is there some way to make SQLite3 turn case insensitive?

(Possibly some way that's invisible to Google?:wink:

What platform? I have had cases where MySQL will go case sensitive,
seemingly by just by running on a different platform.

I was running an app nicely (not Rails in this case) with Mac OS X
Server, but when the data was moved to a Linux machine using a GUI
tool it decided to rename all the tables in lower case. (Like you I
had nothing to do with the case issue, but was the one who had to fix
the problem).

I have also had case issues with PostgreSQL in the past as well. Again
it was running on Linux. SQL is "generally" case agnostic, but on
Linux I've seen a number of cases where it isn't.

Oh, I forgot to mention: Also check your character encoding and
collation which may have an effect on case sensitivity.

What platform? I have had cases where MySQL will go case sensitive,
seemingly by just by running on a different platform.

I was running an app nicely (not Rails in this case) with Mac OS X
Server, but when the data was moved to a Linux machine using a GUI
tool it decided to rename all the tables in lower case. (Like you I
had nothing to do with the case issue, but was the one who had to fix
the problem).

This is because MySQL stores tables on the filesystem by name (certainly
MyISAM tables, and I think InnoDB tables as well). If your DB is on a
system with a case-sensitive filesystem (most filesystems Linux supports),
MySQL table names are case-sensitive. If your DB is on a case-insensitive
(even case-preserving, like HFS+ on MacOS X, though there is a
case-sensitive version available) filesystem, however, the files containing
the tables will be found even when the case does not match.

I have also had case issues with PostgreSQL in the past as well. Again
it was running on Linux. SQL is "generally" case agnostic, but on
Linux I've seen a number of cases where it isn't.

You should not have had any issue with PostgreSQL unless the table names
were quoted when they were created. See
http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Note that none of this answers the question about SQLite3. Some googling
for it makes me think that it isn't too promising. It is probably worth
your while to fix your case issues anyway, though. Most of the SQL I see
generated by ActiveRecord does a lot of table name and column name quoting,
which (according to the SQL standard) should enforce case. MySQL is
exhibiting non-standard behavior (see above) in this case.

(And in so many others. Don't get me started on how much I loathe MySQL and
everything it stands for, despite having had to use it for work for over
two years. All my personal projects use PostgreSQL or, occasionally, SQLite.)

--Greg

Robert Walker wrote:

Oh, I forgot to mention: Also check your character encoding and
collation which may have an effect on case sensitivity.

I found COLLATE NOCASE; that leads to this:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg29526.html

So the answer is probably to tack a COLLATE NOCASE onto the columns that are indifferent to case. Thanks y'all!