Why should case sensitive data comparisons be maintained ? It seems to me that it's only in the most obtuse examples where it is of any use e.g. algebra, formulae, equations etc. Even then, how many people will search for a formula ? However, I would agree, that there is an absolute need to maintain the case of the actual stored data. But we shouldn't confuse the storage of data with the querying of data.
By forcing people to use UPPER/LOWER functions to remove the case sensitivity, you are condemning them to considerably poorer performance. As I understand it, UPPER/LOWER and LIKE operators will cause SQLite NOT to use indexes if there are any defined for the column. As you say, Unix has a case sensitive file system (unlike Windows, DOS or VMS for that matter) which is perhaps why the commercial RDBMS that were developed on Unix (Informix, Oracle?, Ingress....) are all case sensitive, and the one that wasn't, SQLServer isn't. Can't we solve this in the index ? Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 01 December 2003 22:05 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Is case sensitivity a problem for me only? For ease of use and less prone to programming errors, Table/Field names should be case INsensitive. Of course data comparisons should be case sensitive, and use UPPER/LOWER methods where case is not a factor. This is one thing Unix has taught the world with case sensitivity in its file system - its a big mistake. In the Windows world we *never* have bugs due to case mistakes in programs where files are involved. IMHO it we will be *much* better off if Sqlite works like this. > >> Pavel said: >> >Hi, all. >> >Traditionally, SQL databases are case insensitive, or at least have an >> >option to behave this way. Sqlite is case sensitive and this fact is >> >introduced in a lot of places in sources. Having case-insensitive >> >sorting and matching seem to be extremely useful thing to me (in >> >business domain where one have to deal with names, addresses etc). Any >> >opinions? >> >Yours, Pavel >> >> Personally, I prefer that databases *are* case-sensitive by default, as > this matches how most programming languages work. >> >> If I do a normal comparison between two strings in a programming >> language, > they match only if the case is the same. It gives me plenty of headaches > if > the database behaves differently, and I can't be certain if matches > returned > by a database actually are matches. >> >> Also, case-sensitive comparisons (basis for both matching and sorting) >> are > considerably faster and use less memory than non-sensitive ones, since > with > the latter one has to convert the string on each side to either an upper > or > lower case representation, so that the normal numerical comparison of the > strings match, and with the former, no conversion is needed. >> >> Also, in these days of increasing internationalization, it is simpler to > do case-sensitive by default since that's what all the world's other > character sets and our own non-letter symbols do, to my knowledge. >> >> Now I do recognize the value of case-insensitive comparisions, but I >> think > that this should be done as a type of extension functionality rather than > in > core functionality. >> >> What I mean is, have a different syntax to specify case-insensitive > matching, much in the way that "like '%abc%'" is used when we want to > match > just part of a string. >> >> Also or alternately, treat case-insensitive operations as a >> locale-related > thing, much as parts of a database schema can be configured to do things > in > a certain way, such as sorting, based on the user's locale. >> >> So case-sensitive default actions all around are the simplest, fastest, > take the least code, are the most reliable, and technically most standard > way of doing things. So I see SQLite doing sensitive by default as a good > thing. >> >> -- Darren Duncan >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: > [EMAIL PROTECTED] >> For additional commands, e-mail: > [EMAIL PROTECTED] >> >> > > > > I'm in the opposite camp I'm afraid - I find case sensitivity irritating > for > both database and programming language and also, a little unneccersary. > Most real world database applications have to provide some level of > textual > searching to their users, be it names, addresses etc. Case sensitivity is > a > major pain in these circumstances. > > I've been working with text management systems for the last 12 years where > this issue is normally managed on a per column basis through the index > i.e. > you define an index for a column to be case insensitive and the search > terms > are adjusted accordingly when a query is run. > Therefore, the performance of case insensitive searching is NOT slower > because the search terms in the index are stored in upper case - when the > query is run, the user terms are converted to the same case as the index > terms prior to looking them up in the index. > > I'm not sure where the drop off in reliability is that you refer to in > this > scenario........ > > To be honest, this sounds like a pretty simple change (but then, don't > they > always) to SQLite to be able to provide a pragma that switches this > feature > on/off for all indexes. > > Steve > > > > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
