[sqlite] Unicode support in SQLite
Hello, I'm glad to announce that nunicode SQLite extension was updated to support Unicode-conformant case folding and was improved on performance of every component provided to SQLite. You can read about and download this extension at BitBucket page of nunicode library: https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension This extension provides the following Unicode-aware components: - upper(X) - lower(X) - X LIKE Y ESCAPE Z - COLLATE NU700 : case-sensitive Unicode 7.0.0 collation - COLLATE NU700_NOCASE : case-insensitive Unicode 7.0.0 collation ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode support in SQLite
On 14/10/14 17:02, Kevin Benson wrote: https://bitbucket.org/alekseyt/nunicode/downloads/libnusqlite3-1.4-4a0e4773-win32.zip <--- 404 response code Thank you, fixed now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unicode case insensitive
On 24/10/14 20:44, dd wrote: dd, Any sample/open source avail to custom collation. Will it work for like queries. Any performance degradation? You might try nunicode: https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension. I think COLLATE NU700_NOCASE should do what you expect. Will work for like queries, performance is covered at the same page. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stuck and drowning trying to work out some SQL.
On 01/11/14 12:19, Rob Willett wrote: Rob, I’d like to produce an output table based on the above data set that looks a bit like this. Disruption_id | Start Time | End Time 1 | 1 | 4 2 | 1 | 5 3 | 1 | 2 4 | 2 | 4 What do you think about self-joining original table, then doing two mins() on Time_Event? sqlite> SELECT d1.x, min(d1.y) AS start, min(d2.y) AS end FROM data AS d1 LEFT JOIN data AS d2 ON d1.x=d2.x AND d1.z='active' AND d2.z='closed' GROUP BY d1.x; 1|1|4 2|1|5 3|1|2 4|2|4 5|4| x - Disruption_id y - Time_Event z - State Hope this helps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unicode support in SQLite
Hello, I'm glad to announce that nunicode SQLite extension was updated to support Unicode 8.0. This extension support the following encodings: UTF-8, UTF-16, UTF-16LE, UTF-16BE and only 230Kb in size (approximately). This extension provides the following Unicode-aware components: - upper(X) - lower(X) - X LIKE Y ESCAPE Z - COLLATE NU800 : case-sensitive Unicode 8.0.0 collation - COLLATE NU800_NOCASE : case-insensitive Unicode 8.0.0 collation You can read about and download this extension at BitBucket page of nunicode library: https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension If you were using previous version of nunicode extension for SQLite, please note that extension was renamed from libnunicode to libnusqlite3. The entry points remained the same: sqlite3_nunicode_init() and nunicode_sqlite3_static_init(). Sorry for the inconvenience. Also note that this version of extension is no longer providing NU700 and NU700_NOCASE collations, they are replaced with NU800 and NU800_NOCASE. Complete changelog is available here: https://bitbucket.org/alekseyt/nunicode/raw/master/CHANGELOG
[sqlite] Mozilla wiki 'avoid SQLite'
On 15/06/15 01:00, Simon Slavin wrote: Simon, > Perhaps the next generation of computer languages will be designed by > computer, to let us speak to them in an efficient manner. > I'm sure computer would insist on C, if not, then it's apparently a software bug. On a serious note, i think it's rather question of programming computers or programming another programs. As you've mentioned, higher level languages often compiles into byte-code which is then interpreted by virtual machine. So you don't speak to machine, you speak to mediator who speak to machine. This by definition an overhead, with growing complexity of the program, overhead will grow accordingly, this is unavoidable. I think it's also fair to say that SQL is not for programming machines (no offense), it's for programming SQLite and other database implementations. Even if something is called "virtual machine", VM always behaves somehow differently from The Machine, thus programmer' efforts has mediated effect on latter. Of course VM could do a good job in a specific domain, but each VM limits the liberty of expressing yourself to the machine and vice versa. In my opinion best database language would reflect the way in which database works and best computer programming language would reflect the way in which computer works, as close as reasonably possible.
[sqlite] COLLATE NU800_NOCASE as a loadable extension?
On Sun, Mar 13, 2016 at 9:34 AM, Torleif Haug?deg?rd wrote: Torleif, > I?ve read about COLLATE NU800_NOCASE here: > > http://www.mail-archive.com/sqlite-users at > mailinglists.sqlite.org/msg02380.ht > ml > > > How can I make this a loadable extension? You can get prebuilt loadable extension here: https://bitbucket.org/alekseyt/nunicode/downloads Or build it from sources with cmake (mkdir build && cd build && cmake .. && make). Build instructions are in "BUILD" file, example of loadable extension usage is in sqlite3/loadextension.c Hope this helps.
[sqlite] COLLATE NU800_NOCASE as a loadable extension?
03/13/2016 07:19 PM, Torleif Haug?deg?rd ?: > I unpacked libnusqlite3-1.6-34f278da-win32.zip and get libnusqlite3.dll > When I try to load it as extension there is a question "Select entry point". > Sorry, need more help. > Try to set sqlite3_nunicode_init as entry point (reference: https://bitbucket.org/alekseyt/nunicode/src/master/sqlite3/samples/loadextension.c). Does that help? This entry point issue is now on my list of things to fix, it's going to be fixed in next commits.
[sqlite] COLLATE NU800_NOCASE as a loadable extension?
On 03/17/2016 07:29 PM, Torleif Haug?deg?rd wrote: Torleif, > Now the resultat was this one; > > Exception: > -- >2.1 Date : Thu, 17 Mar 2016 18:27:00 +0100 >2.2 Address : 00AA83F5 >2.3 Module Name : SQLiteExpertPers.exe - (The complete administration > tool for SQLite) >2.4 Module Version: 3.5.92.2512 >2.5 Type : ESQLiteException >2.6 Message : no such collation sequence:_RMNOCASE >. >SQL Statement: > > . >SELECT given FROM NameTable ORDER BY given. > Unfortunately i'm not familiar with SQLite Expert, however >2.6 Message : no such collation sequence:_RMNOCASE Collation provided by nunicode SQLite extension is called NU800_NOCASE (or NU800 for case-sensitive collation), that name is need to be used if you want nunicode to handle "COLLATE" expressions. Here is documentation on nunicode SQLite extension: https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension with some usage examples.
[sqlite] Unicode support in SQLite
Dear SQLite users,
I'd like to present you Unicode support extension i've implemented for
SQLite, it does full Unicode (6.3.0) collations, case mapping and
untailored ordering, and takes only ~100Kb to do that if you link it
statically. It's also open source and free (MIT license):
https://bitbucket.org/alekseyt/nunicode
What it does exactly:
sqlite> .load ./sqlite3/libnusqlite3.so
sqlite> SELECT 'MASSE' LIKE 'Maße';
1
sqlite> SELECT 'æ' LIKE 'AE';
1
sqlite> SELECT 'Masse' == 'Maße' COLLATE NUNICODE;
1
sqlite> SELECT upper('Maße');
MASSE
As you can see, this is truly full Unicode collation and case mapping
with untailored special casing. Extension provides the following
functions, statements and collations:
- upper()/lower()
- X LIKE Y ESCAPE Z
- COLLATE NOCASE
- COLLATE NUNICODE
Supported encodings: UTF-8, UTF-16 (host-endian), UTF-16BE, UTF-16LE.
If you wish to try it, you can find some pre-built binaries for Windows
and Linux in downloads section on BitBucket, documentation is embedded
into sources.
Any ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode support in SQLite
On 11/04/2013 11:50 AM, Aleksey Tulinov wrote: Hey, As you can see, this is truly full Unicode collation and case mapping with untailored special casing. Extension provides the following functions, statements and collations: I've updated extension, examples and documentation, now it's easier to link extension statically. Everything, including new prebuilt binaries, is available on BitBucket, changelog is available here: https://bitbucket.org/alekseyt/nunicode/src/master/CHANGELOG ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 11/11/2013 12:36 AM, Igor Korot wrote: Well from strictly mathematical point of view maximum or minimum of nothing is nothing. And since nothing is 0, than it is zero. Thank you. max() can't simply pull 0 out of the air, for it 0 is the same random number as 1e-129 which might be also considered 0 if only 128 decimal digits are significant to you. SELECT produce empty set which is nothing in algebra of sets, max() produce scalar value where nothing is undefined if you're looking at numbers. Hence max() on empty set produce NULL, which is nothing in SQL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite does not order greek characters correctly
On 12/08/2013 11:34 PM, Nikos Platis wrote: Here is the correct order of greek characters (mixed case) as produced by LibreOffice Calc: α Α ά Ά β Β γ Γ δ Δ ε Ε έ Έ ζ Ζ η Η ή Ή θ Θ ι Ι ί Ί ϊ Ϊ ΐ κ Κ λ Λ μ Μ ν Ν ξ Ξ ο Ο ό Ό π Π ρ Ρ σ Σ τ Τ υ Υ ύ Ύ ϋΫ ΰ φ Φ χ Χ ψ Ψ ω Ω ώ Ώ Upper case letters are sorter right after the respective lower case ones, and, most importantly, accented vowels are sorted right after the non-accented ones. Nikos, you could try nunicode, it won't order lower-upper-lower-upper, but it should be able to correctly handle accents. How it works is explained in this section: https://bitbucket.org/alekseyt/nunicode#markdown-header-strings-collation-and-case-mapping When i finish with Unicode tailoring (localization extension), you should be able to implement correct collation for Greek characters. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unicode support in SQLite
Hey, I've just updated nunicode to version 1.2: https://bitbucket.org/alekseyt/nunicode Now all collations are backed by reduced DUCET. Library grew in size a little bit, you'll get Unicode collations for around 200Kb, but at the same time you will also get several languages completely working out of the box as they don't need any collation tailoring. You can also write your own tailoring, this is somewhat described here: https://bitbucket.org/alekseyt/nunicode#markdown-header-custom-collations and also covered in embedded Doxygen doc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using ICU case folding support
On 03/18/2014 10:02 PM, Alex Loukissas wrote:
Alex,
I suppose I can declare the column as BINARY and use LOWER( ) in my select
statements. Browsing through the code though, I do see uses of u_foldCase
in certain places, which leads me to believe that what I want may be
already there. I'll try to unit test this.
Correct me if i'm wrong, but i think i reproduced bug you've described
in SQLite with ICU extension compiled in:
sqlite> CREATE TABLE test (x COLLATE NOCASE);
sqlite> INSERT INTO test VALUES ('s');
sqlite> INSERT INTO test VALUES ('S');
sqlite> INSERT INTO test VALUES ('ё'); -- Russian e with diacritic
sqlite> INSERT INTO test VALUES ('Ё'); -- Russian E with diacritic
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
sqlite> SELECT * FROM test WHERE x = 's';
s
S
This might be a point of interest for SQLite ICU extension developers.
On the other hand, SQLite nunicode extension, which i developed, do that:
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
sqlite> .load ./libnusqlite3.so
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
Ё
You can try nunicode by following this link:
https://bitbucket.org/alekseyt/nunicode/overview#markdown-header-sqlite3-extension
Hope this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using ICU case folding support
On 03/19/2014 05:32 PM, Dan Kennedy wrote: home-grown tokenizer named "unicode61" that folds case in the same unicode-aware way as nunicode. If the unicode standard changes to define more pairs of case equivalent characters, we will not be able simply upgrade "unicode61". For the same reasons - modifying the comparison function creates an incompatible system. Instead, we would name it "unicode62" or similar, to be sure that databases created using the old version continue to use it. Thank you for the detailed answer. Is assume working this around with custom collation as in "SELECT ... x = 'ё' COLLATE NOCASE_U61" will imply performance penalty regarding index search, but custom case-insensitive collation assigned to table column, "CREATE TABLE test (x COLLATE NOCASE_U61)", will work as expected. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode support in SQLite
Hey, According to previous discussion in this mailing list, i've updated nunicode SQLite extension not to override default NOCASE collation due to possible issues with database indexing. Version 1.2.1 removes nunicode-specific NOCASE and NUNICODE collations and introduces NU630 and NU630_NOCASE collations instead. First is case-sensitive Unicode 6.3.0 collation, second is case-insensitive, both implements default Unicode collation ordering (DUCET). In all other regards, it's not different from 1.2 version of extension and based on the same nunicode 1.2. Full changelog is available here: https://bitbucket.org/alekseyt/nunicode/src/master/CHANGELOG Pre-compiled extensions are available under "Downloads" for Win32 and i386/amd64 Linux. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode support in SQLite
Hello, I'm glad to announce that nunicode SQLite extension was updated to support Unicode 7.0.0 character set. It also implements LIKE operation which is faster compared to previous releases. This extension provides the following Unicode-aware components: - upper(X) - lower(X) - X LIKE Y ESCAPE Z - COLLATE NU700 : case-sensitive Unicode 7.0.0 collation - COLLATE NU700_NOCASE : case-insensitive Unicode 7.0.0 collation Collation functions implement default Unicode collation (based on DUCET). Previously implemented Unicode 6.3.0 collations NU630 and NU630_NOCASE were removed from this version of extension. You can find implementation details, changelog and downloads at BitBucket page of nunicode library: https://bitbucket.org/alekseyt/nunicode ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
