How do I get case-insensitive queries on non-ASCII with python and sqlite3?

Question

There are some obvious tips and resources about this, but I can't find any direct examples.

http://www.sqlite.org/faq.html#q18 says one can override the NOCASE collation, and the like, lower, and upper functions. I've done so, in a Django testcase, but in my test none of them are actually called.

from django.test import TestCase

from django.contrib.sites.models import Site from basic.blog.models import Post, Settings

class CaseInsenstiveTest(TestCase):
    def setUp(self):
        self.site = Site.objects.create()
        self.settings = Settings.objects.create(site=self.site)
        Settings.get_current = classmethod(lambda cls: self.settings)

    def testLike(self):
        from django.db import connection

        Post.objects.all() # Sets up the connection

        def like(x, y, z=None):
            """Y LIKE X [ESCAPE Z]"""

            assert x.startswith('%') and x.endswith('%')
            like.called = True
            print "like(%r, %r, %r)" % (x, y, z)

            return x[1:-1].lower() in y.lower()
        like.called = False

        def lower(s):
            print "lower(%r)" % (s,)
            return s.lower()

        def upper(s):
            print "upper(%r)" % (s,)
            return s.upper()

        connection.connection.create_function('lower', 1, lower)
        connection.connection.create_function('upper', 1, upper)
        connection.connection.create_function('like', 3, like)

        def NOCASE(a, b):
            print "NOCASE(%r, %r)" % (a, b)
            return cmp(a.lower(), b.lower())
        connection.connection.create_collation('NOCASE', NOCASE)

        Post.objects.create(slug='foo', title='Foo')
        Post.objects.filter(title__icontains='foo')

it seems not a single of the registered functions or the collation are actually called. Can anyone point out what is wrong?

NOTE: I know the like function is not correct, yet. I'm just trying to figure out when what is called so I know what I need to override and how.

Answer 1

It seems to work fine outside django:

So maybe you have a django problem? Are you sure the table fields were created using the NOCASE collation?

import sqlite3

def NOCASE(a, b):
    print 'comparing %r with %r...' % (a, b)
    return cmp(a.lower(), b.lower())

con = sqlite3.connect('')

cur = con.cursor()
cur.execute('CREATE TABLE foo (id INTEGER, text VARCHAR collate NOCASE)')
cur.executemany('INSERT INTO foo (id, text) VALUES (?, ?)', [
    (1, u'test'), (2, u'TEST'), (3, u'uest'), (4, u'UEST')])
con.commit()

con.create_collation('NOCASE', NOCASE)

cur = con.cursor()
cur.execute('SELECT * FROM foo ORDER BY text ASC')
print cur.fetchall()

Output:

comparing 'test' with 'TEST'...
comparing 'test' with 'uest'...
comparing 'TEST' with 'uest'...
comparing 'TEST' with 'UEST'...
comparing 'uest' with 'UEST'...
[(1, u'test'), (2, u'TEST'), (3, u'uest'), (4, u'UEST')]

Similarly, using a defined function works fine (same dataset)

def my_lower(text):
    print "I'm lowering %r myself" % (text,)
    return text.lower()

con.create_function('lower', 1, my_lower)
cur.execute('SELECT lower(text) FROM foo')

The output:

I'm lowering u'test' myself
I'm lowering u'TEST' myself
I'm lowering u'uest' myself
I'm lowering u'UEST' myself
[(u'test',), (u'test',), (u'uest',), (u'uest',)]

Similarly, for LIKE operations, you have to register the function in its 2-arguments form (X LIKE Y) and in its 3-arguments form (X LIKE Y ESCAPE Z) if you plan to support both forms:

def my_like(a, b, escape=None):
    print 'checking if %r matches %r' % (a, b)
    return b.lower().startswith(a[0].lower())

con.create_function('like', 2, my_like) # X LIKE Y
con.create_function('like', 3, my_like) # X LIKE Y ESCAPE Z
cur.execute('SELECT * FROM foo WHERE text LIKE ?', (u't%',))

yields the output:

checking if u't%' matches u'test'
checking if u't%' matches u'TEST'
checking if u't%' matches u'uest'
checking if u't%' matches u'UEST'
[(1, u'test'), (2, u'TEST')]