[fixed] Sorting Order is not "natural" (1 Viewer)

[Lehmden]

Hi,
On alphabetical sorting capital letters are sorted before normal letters. This leads to some unnatural sorting orders:

In this case "SO" is sorted before "Sa". I know the ASCII codes are this way, but human thoughts are different (at least mine are).

 

[morpheus_xx]

I guess that Sqlite does use case sensitive sorting while SqlCe doesn't.

@MJGraf is it possible to set SQLite to case-insensitive sort?

 

[MJGraf]

Hi everyone,
yes, it is possible - but I fear not with the SQLiteDatabase plugin alone. This will require support of the rest of MP2's code. Let me explain:
SQLite is as usual very flexible. For details see here http://www.sqlite.org/datatype3.html and read as of "6.0 Collating Sequences". In short: There are three inbuilt collating sequences: BINARY, NOCASE and RTRIM - the standard being BINARY. What we would need here is NOCASE.

However, there is AFAIK (need to do some more research on it) no single command to tell SQLite: Hey, as of now take NOCASE as standard.

We have two possibilities to change the collation sequence that I know of so far:

• Within the select query: Instead of "select * from ... order by name", we can use "select * from ... order by name COLLATE NOCASE". Obviously we don't do this and it is also the second best solution because it slows down our queries.
• The best solution would be to specify the collation sequence when creating the table. Something like "create table t1 (a TEXT COLLATE NOCASE, b INTEGER, ...);" In this case the collating sequence NOCASE is taken as a standard if any select query orders by the respective column. This is much faster than the first solution because also indices are automatically created using that collation sequence. This is currently also not provided for in our ISQLDatabase interface and therefore not used in MediaLibrary etc. What we could try is "abuse" our SQLiteDatabase.GetSQLType method. It currently returns the SQL database types such as "TEXT" or "INTEGER". If we return "TEXT COLLATE NOCASE" as "SQL type", our create table statements should look correct, but I don't know if the return value of GetSQLType is used for anything else than the create table statement...

And just for the sake of completeness (and because I know we had some hint about e.g. russian localization some time ago): NOCASE only works with ASCII characters. Not with UTF8 - and therefore e.g. russian characters are not sorted case sensitive even when using the inbuilt NOCASE. But SQLite is flexible enough to also handle this case. You can specify a very simple C# method to compare two values, register this c# method with SQLite as a collation sequence and then use this collation sequence in your SQL queries. An example can be found here: http://www.codeproject.com/Articles/44349/Case-Insensitive-Sort-of-UTF8-Data-Using-System-Da (in total 6 lines of code).

I'll have a look on whether we can use GetSQLType as a temporary workaround. @morpheus_xx Do you know how other databases handle this? Can we have something like a "GetCollationCommandForCreateTableStatements" in ISQLDatabase?

 

[MJGraf]

Maybe what I thought is a workaround is not a workaround... Here it says:

The COLLATE operator is a unary postfix operator that assigns a collating sequence to an expression. The COLLATE operator has a higher precedence (binds more tightly) than any binary operator and any unary prefix operator except "~". (COLLATE and "~" are associative so their binding order does not matter.)

To my understanding this means that "TEXT COLLATE NOCASE" can be used in any SQL expression as a valid SQL type. What I did is let the SQLiteDatabase return "TEXT COLLATE NOCASE" instead of just "TEXT". That's an easy three line change and for me it doesn't throw any errors so far. I let it create a new database, imported my music, video, movie and series collection and had a look at the result in MP2 Client without any problems. Unfortunately I couldn't find any MediaItems in my collection to test whether this solves your problem.

So @Lehmden could you please test the attached SQLiteDatabase binaries whether this solves it for you? (these are including also the changes by Morpheus he did today). Unfortunately you have to create a new database in order for this to work. But maybe you can backup your old one and only import a part of your series collection just to see if it works... Thanks!

Michael

[Edit: Binaries removed. Newer version some threads down]

 

[Lehmden]

Hi.
Tried it and it seems to work as expected:

So I really need to rebuild my whole DB to get this working?

 

[MJGraf]

Thanks a lot @Lehmden for testing!

So I really need to rebuild my whole DB to get this working?

I fear yes... As I wrote above, We either have to include this in every select query of MP2 (which would be a huge amount of work) or we have to "store" the sorting information in the table itself - which unfortunately is only possible at the time the table is created, hence the need to recreate the database...

But before you do so, please give me a bit more time. I found the thread again where the user unoparator complained about MP2 not making use of the culture settings correctly (https://forum.team-mediaportal.com/threads/localization-issues.122069/#post-1031482) and when we are at it, I will try to implement our own collation method making use of the C# String.Compare method. This should solve your problem and at the same time work for other cultures as well. I'm not sure whether the result is compatible with what I posted above. So it may be necessary to do one more complete reimport. So to avoid this please give me some more time. I just want to do this "the MP2 way" - when we do it, we do it right

Michael

 

[MJGraf]

Hehe, test import is just running and it also helps for Germans: Herbert Grönemeyer's album "Ö" now appears before "O..." instead of after "Z..."
Just need to check how big the impact is on the speed of the database because now SQLite calls my C# method on every insert which affects an index in the database to compare the two strings. But so far it is not like it takes twice as long as before. Let's see...

 

[MJGraf]

Ok here you are - please test...
It does give us a performance hit - not sure how big, yet. Maybe between 10-20% slower. But I think we have to bite the bullet here. Sorting must be correct - even if the import may take some minutes longer...

@UNOPARATOR: I don't know if you are still using MP2. If so, would you be so kind and test the attached database binaries? They should enable a sorting with CultureInfo.InvariantCulture and at the same time be case insensitive. I don't have any music files with non-German characters, so your help would be most appreciated!

Michael

 

[MJGraf]

Hm, nevertheless I don't like the speed impact: 24:41 minutes instead of 19:17 minutes. Just because of the sorting. Any opinions on this?!?

 

[Holzi]

My opinion is: Perfect sorting is a must have.
Although I understand the concern about import speed but I don't think you will import your collection every day (once SQLite rework is finished )