Version: SVN (using KDE KDE 3.5.5) Installed from: Gentoo Packages As it is the case, when one uses the Collection Browser with the sqlite backend, the search is case-sensitive for non-ascii characters. This is pretty disturbing if you don't know the correct case of a song name. Here is a patch that fixes this issue by replacing the LIKE() sqlite function with another, more utf8-friendly one. It uses Qt's string comparison functions, so I think it should have good support for world scripts (I myself tested it for Cyrillic letters). I hope it is useful to many people (as the number of bugs on the topic suggest). Reference: Bug 116458
Created attachment 18828 [details] proposed patch
SVN commit 611255 by markey: With SQLite, the search in the collection browser was case-sensitive with UTF-8. Patch by Stanislav Nikolov <stanley_87@mail.ru>. BUG: 138482 M +2 -0 ChangeLog M +30 -0 src/collectiondb.cpp M +1 -0 src/collectiondb.h --- trunk/extragear/multimedia/amarok/ChangeLog #611254:611255 @@ -57,6 +57,8 @@ * Amarok now saves playlists with relative paths by default. BUGFIXES: + * With SQLite, the search in the collection browser was case-sensitive + with UTF-8. Patch by Stanislav Nikolov <stanley_87@mail.ru>. (BR 138482) * (Don't) Show Under Various Artists would not work when multiple albums are selected. Patch by Tobias Knieper <tobias.knieper@micekiller.de>. (BR 112422) --- trunk/extragear/multimedia/amarok/src/collectiondb.cpp #611254:611255 @@ -5979,6 +5979,10 @@ m_initialized = false; if( sqlite3_create_function(m_db, "power", 2, SQLITE_UTF8, NULL, sqlite_power, NULL, NULL) != SQLITE_OK ) m_initialized = false; + if ( sqlite3_create_function(m_db, "like", 2, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK ) + m_initialized = false; + if ( sqlite3_create_function(m_db, "like", 3, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK ) + m_initialized = false; } //optimization for speeding up SQLite @@ -6185,7 +6189,33 @@ sqlite3_result_double( context, pow(a,b) ); } +// this implements a LIKE() function that overrides the default string comparison function +// Reason: default function is case-sensitive for utf8 strings (BUG: 116458, ...) +void SqliteConnection::sqlite_like_new( sqlite3_context *context, int argc, sqlite3_value **argv ) +{ + const unsigned char *zA = sqlite3_value_text( argv[0] ); + const unsigned char *zB = sqlite3_value_text( argv[1] ); + + QString pattern = QString::fromUtf8( (const char*)zA ); + QString text = QString::fromUtf8( (const char*)zB ); + + pattern = QRegExp::escape( pattern ); + + if ( pattern.startsWith("%") ) pattern = ".*" + pattern.mid(1); + if ( pattern.endsWith("%") ) pattern = pattern.left( pattern.length() - 1 ) + ".*"; + + if( argc == 3 ) { + const unsigned char *zEsc = sqlite3_value_text( argv[2] ); + QChar escape = QString::fromUtf8( (const char*)zEsc ).at( 0 ); + pattern.replace( escape + '%', "%" ).replace( escape + '_', "_" ).replace( escape + '/', "/" ); + } + + const QRegExp rx( pattern, 0 /* case-sensitive */, 0 /* simple wildcards */ ); + + sqlite3_result_int( context, rx.search( text ) != -1 ); +} + ////////////////////////////////////////////////////////////////////////////////////////// // CLASS MySqlConnection ////////////////////////////////////////////////////////////////////////////////////////// --- trunk/extragear/multimedia/amarok/src/collectiondb.h #611254:611255 @@ -141,6 +141,7 @@ private: static void sqlite_rand( sqlite3_context *context, int /*argc*/, sqlite3_value ** /*argv*/ ); static void sqlite_power( sqlite3_context *context, int argc, sqlite3_value **argv ); + static void sqlite_like_new( sqlite3_context *context, int argc, sqlite3_value **argv ); sqlite3* m_db; };
Hey everyone, I am experiencing this bug now, still in 2009. I don't get why the bug was set as "Resolved" but is still present in the software after about 2 years. Then again, sorry for being a noob, but what does one have to do with all that code you posted? And the patch, where should it go? If I understand correctly it would be a patch for the source code. This supposes that I download the source code, apply the patch, and recompile the program. But this presents a couple of problems for me, an end-user and not an experienced developer. I imagine that the amarok 2 source code would be rather different and the patch may be not directly applicable. Anyway shouldn't bug-fixes be incorporated into the software? Help me out here.
@Gennady: SQLite has been dropped in Amarok 2.
I am not sure I know what this means. However I must say that the problem persists and probably in amarok 2 the reason is the same, since the bug is seen only when searching for non-ascii entries. Give a look to the bug report I posted: 183743 Thanks