SQLite Order By places umlauts&结束时的特殊字符 [英] SQLite Order By places umlauts & speical chars at end

问题描述

我使用Phonegap为iOS做字典应用程序。

当查询数据库的字母表时，我使用 COLLATE NOCASE ：

  ORDER BY条款COLLATE NOCASE ASC 
  pre> 
 
 
这解决了以小写字母开头的术语附加到结尾的问题（从该问题）。
 
 
 
但是非标准字符öäüéêè仍然排序在结束〜这里2个例子：
 预期：ÖffnungszeitenOberved： Zuzahlung 
 ZuzahlungÖffnungszeiten
 
（or）clécliquer sur 
 cliquer surclé
  
我环顾四周，发现了类似的问题，这里或此处但是它似乎一般的建议是安装某种类型的扩展
 
 
这个扩展可能会帮助你... 
 
 
 
 ...使用ICU作为扩展
 
 
 
 SQLite支持与ICU集成... 
 
 
但我不知道这是否适用于我的情况，数据库不是由自己托管，而是在客户设备上运行。所以我想我会把这个扩展w /我的应用程序包。
 
我不是很熟悉iOS，但我有的感觉，将是复杂的 - 至少。 / p> 
 
 
此外，在官方论坛我发现了以下提示：
  SQLite无法正确处理重音字符。 
  
并且在文本中一点点提到在SQLite中的一个错误。 
 
 
 
我发现的所有链接在> = 1年没有活动，而他们似乎并不处理我目前开发的移动环境。 
 
所以我想知道是否有人在他们的iOS项目上找到了解决方案。
 
 
 
文档声明他们只有3个默认的COLLATION选项： / p> 
 
 
 
 
 6.0整理序列
 
 
 
当SQLite比较两个字符串时，或
整理函数（用于同一事物的两个字）来确定哪个
字符串更大或者两个字符串是否相等。 SQLite有三个
内置的整理函数：BINARY，NOCASE和RTRIM。
  BINARY  - 比较字符串数据memcmp（），不管文本编码。 
 NOCASE  - 与二进制相同，除了ASCII的26个大写字符在
  
 
之前折叠到它们的小写等价
 
比较。注意只有ASCII字符是
折叠的情况。 SQLite不会尝试执行完整的UTF案例折叠，因为所需的表的
大小。 
 RTRIM  - 除了尾随空格字符被忽略之外，与二进制相同。
 
 
现在我最好的猜测是在JavaScript中进行排序，但我怀疑这对整体性能不会有好处。 
解决方案
原因是iOS上的SQLite没有启用ICU。所以你需要建立自己的SQLite版本与ICU启用+您自己的ICU版本作为静态lib +添加ICU .dat，并使SQLite加载这个.dat文件。然后，您可以通过一个简单的SQL命令（即'icu_load_collat​​ion（de_DE，DEUTSCH）'加载任何排序规则，一旦打开数据库）
 
 
 
只是听起来像是脏的工作，它真的是。尝试找到一个版本的SQLite + ICU，所有这一切已经完成。
 
I'm using Phonegap to do a dictionary app for iOS.

When querying the database for an alphabetical list I use COLLATE NOCASE:
ORDER BY term COLLATE NOCASE ASC
This solved the problem that terms starting with a lower case letter where appended to the end (Picked it up from that question).

However non-standard characters as öäüéêè  still get sorted in the end ~ here 2 examples:
    Expected: Öffnungszeiten     Oberved: Zuzahlung
              Zuzahlung                   Öffnungszeiten

(or)          clé                         cliquer sur
              cliquer sur                 clé
I looked around and found similar matters discussed here or here but it seems the general advice is to install some type of extension 

  
This extension can probably help you ...
  
  
...use ICU either as an extension
  
  
SQLite supports integration with ICU ...
But I'm not sure if this is applicable in my situation where the database is not hosted by myself but running on the customers device. So I'd guess I'd to ship this extension w/ my app-package.

I'm not very familiar with iOS but I've got the feeling that would be complicated - at least.

Also in the official forum I've found that hint:
SQLite does not properly handle accented characters.
and a little bit down in the text the poster mentions a bug in SQLite. 

All the links I've found haven't been active for >= 1 year and non of them seems to deal with the mobile environment I'm currently developing in.

So I was wondering if anyone else found a solution on their iOS projects.

The documentation states they're only 3 default COLLATION option:

  
6.0 Collating Sequences
  
  
When SQLite compares two strings, it uses a collating sequence or
  collating function (two words for the same thing) to determine which
  string is greater or if the two strings are equal. SQLite has three
  built-in collating functions: BINARY, NOCASE, and RTRIM.
BINARY - Compares string data using memcmp(), regardless of text encoding.
NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the
comparison is performed. Note that only ASCII characters are case
  folded. SQLite does not attempt to do full UTF case folding due to the
  size of the tables required.
      RTRIM - The same as binary, except that trailing space characters are ignored.
For now my best guess would be to do the sorting in JavaScript but I suspect that this wouldn't do anything well to overall performance. 
解决方案
The reason is that the SQLite on iOS doesn't come with ICU enabled. So you need to build your own SQLite version with ICU enabled + your own ICU version as static lib + add the ICU .dat and make SQLite load this .dat file. Then you can load any collation via a simple SQL command (i.e. 'icu_load_collation("de_DE", "DEUTSCH")', once after the db was opened) 

It doesn't only sound like it's dirt work, it really is. Try to find a version of SQLite + ICU with all of it done already.

                        
这篇关于SQLite Order By places umlauts&结束时的特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！