On Sun, Sep 11, 2011 at 15:55, Antonio Maniero <[email protected]> wrote: >> >> I see. Well, SQLite2 is ancient: that ship has sailed and it's not coming >> back. >> >> Did SQLite2 actually implement case-insensitive comparison on accented >> Latin characters? I honestly don't know - by the time I got involved with >> SQLite (in late 2005), SQLite2 was already history, and its original >> documentation doesn't seem to exist anymore.
SQLite2 didn't support ISO-8859-1 (or 15 or whatever). It just ignored 8-bit characters so you could put whatever you wanted and get whatever you put there back. That means you could also put there UTF-8 text, and the "-16" API functions allowed to directly store/retrieve windows UCS2 Unicode strings (UTF-16 was only after XP, iirc), which SQLite automatically encoded/decoded to UTF-8 text. The problem was that there was no way for 3rd party applications to know what was the encoding being used. If I remember correctly, UTF-8 was still prefered, and some SQL functions acting on text only worked well if it was UTF-8 (like LENGTH). > Maybe someone else could say about the reason that SQLite dropped 8859 > encoding. > > Probably SQLite 2 had not case insensitive comparison on 8859 because it has > many encodings and locales, but implement it would be ease and simple. It didn't drop support. It just never had it anyway, although never caring about it. Nothing changed, except now the SQLite 3 API *EXPLICITLY* says the text functions require either UTF-8 or UTF-16, but nothing stops someone doing the same as with SQLite2 and store it's text as BLOBs. >> Version 3 keeps support for 8859? >> >> No, not really. But, again, it won't prevent you from storing 8859-encoded >> strings in the database, and installing a custom collation that understands >> them, if you are so inclined. Personally, I'd seriously consider switching >> to UTF-8. >> >> > I doubt it that it would be ease to storing 8859 without string functions > problems. The proper collation would be simple, of course, but probably I > would need to re-implement all string functions too. Am I wrong? Do you relly know that there is no 8859 encoding? The standards go from ISO-8859-1 to ISO-8859-16 and you would need to have collations for all of them and a way to let the user choose which one is the right one for them (including regional variations). There are so many things wrong with this kind of reasoning I prefer to not say more. > I can use utf8 but for me SQLite won't be lite anymore without a simple > utf8 implementation. Hopefully someone else could have a ready solution for > collation, otherwise I will do my own implementation. It will never be > correct, but it will be enough. My guess is that you are assuming your text is ISO-8859-1 (commonly called Latin-1). There are many problems with this, like for example the fact ISO-8859-15 -- Latin-9 -- being the replacement, with the € (euro) sign added, but Windows decided to invent it's own encoding and it's "Latin" it's not exactly the same (the Euro sign is the symbol which usually get's corrupted). > Am I the only user that need a lite implementation of SQLite with case > insensitive? Don't assume a case insensitive match it's easy if done right. Read about the Unicode collations and you will understand why -- there doesn't exist a single generic upper/lower case function that works for all. SQLite could have an erroneous and basic case insensitive match (already has a basic one, for 7-bit ASCII), but that would not solve nothing that isn't already solved by the correct solution, which is the use of the ICU extension. It's very easy to replace the SQLite functions with user-defined ones, so if someone wants to go the easy way (partial support for just the common western scripts) it's easy. And already done by many, if you search the mailing list. As a final note, SQLite 2 never had any support for ISO-8859-X collations, so you have no reason to believe SQLite 3 would have it. Regards, ~Nuno Lucas > > Thanks. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
