On 10/30/2013 8:51 AM, Ulrich Goebel wrote:
I want to use such a collation even on columns which are indeces, so I
would like to "connect" the collation to the column/index at the time
creating the column/index, not only in the later SELECTs. And I suppose,
that I really don't have to specify the collation in the SELECTs if it
is connected to the index. Is that right?
If you specify the collation on the column in CREATE TABLE statement,
this collation will be used by default by any indexes and expressions
mentioning this column, unless explicitly overridden.
It should work like that:
CREATE TABLE person (name text);
CREATE INDEX idx_name on person (name COLLATE umlaute);
No, that's wrong. Move the COLLATE clause to CREATE TABLE.
and later on
SELECT name FROM person ORDER BY name;
/* whithout specifying the collation again! */
As written, this would use the regular BINARY collation, because that's
what's specified in the table. The index will not be used at all, since
it's created with a different collation. Again, move the COLLATE clause
to CREATE TABLE, then everything will work the way you expect.
By the way: I define my db, tables and indices using a SQL script, which
I execute by the .read command in sqlite3 (under Ubuntu Linux). But I
don't find any possibility to define any collation. I just found how to
use an allredy existing collation. Isn't it possible, what I want?
http://sqlite.org/c3ref/create_collation.html
http://sqlite.org/lang_corefunc.html#load_extension
You would have to write a shared library implementing your custom
collation. Or, you could use collations provided by ICU:
http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt
but I don't think it works quite the way you want. ICU collations are
case sensitive, and the German one doesn't make A=Ä - it makes Ä sort
right after A and before B.
Another way could be possible: I use python as my programming language,
with the apsw module to connect to the SQLite db. There is a method
apsw.connection.createcollation, which registers a
(python)-sorting-(collate-)function as a collation. That can be used
later, for example in SELECTs. Could it also be used in CREATE TABLEs or
CREATE INDEXs?
Yes. Note that, once you do that, you will be limited in how you can
manipulate this database outside of your program (e.g. in sqlite3
command line shell). Any statement that would need to utilize the
collation would throw "unknown collation" error.
In this case I could define my db, tables and indices
within python/apsw instead of the SQL-script and .read. Right?
Right.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
