Can sqlite3 go case-insensitive?

Rails
1

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:

–
Phlip
O'Reilly Media - Technology and Business Training

2

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.

3

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

4

On Wed, Mar 05, 2008 at 05:34:51PM -0800, Robert W. wrote:

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

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

5

Robert W. 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/[email protected]/msg29526.html

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

–
Phlip
O'Reilly Media - Technology and Business Training