Case insensitive where clause in gql query for StringProperty

Using the google appengine datastore, is there a way to perform a gql query that specifies a WHERE clause on a StringProperty datatype that is case insensitive? I am not always sure what case the value will be in. The docs specify that the where is case sensitive for my values, is there a way to make this insensitive?

for instance the db Model would be this:

from google.appengine.ext import db
class Product(db.Model):
    id = db.IntegerProperty()
    category = db.StringProperty()

and the data looks like this:

id         category
===================
1          cat1
2          cat开发者_StackOverflow中文版2
3          Cat1
4          CAT1
5          CAT3
6          Cat4
7          CaT1
8          CAT5

i would like to say

gqlstring = "WHERE category = '{0}'".format('cat1')
returnvalue = Product.gql(gqlstring)

and have returnvalue contain

id         category
===================
1          cat1
3          Cat1
4          CAT1
7          CaT1

I don't think there is an operator like that in the datastore.

Do you control the input of the category data? If so, you should choose a canonical form to store it in (all lowercase or all uppercase). If you need to store the original case for some reason, then you could just store two columns - one with the original, one with the standardized one. That way you can do a normal WHERE clause.

The datastore doesn't support case insensitive comparisons, because you can't index queries that use them (barring an index that transforms values). The solution is to store a normalized version of your string in addition to the standard one, as Peter suggests. The property classes in the AETycoon library may prove helpful, in particular, DerivedProperty.

This thread was helpful and makes me want to contribute with similar approach to make partial search match possible. I add one more field on datastore kind and save each word on normalized phrase as a set and then use IN filter to collide. This is an example with a Clojure. Normalize part should easy translate to java at least (thanks to @raek on #clojure), while database interaction should be convertable to any language:

(use '[clojure.contrib.string :only [split lower-case]])
(use '[appengine-magic.services.datastore :as ds])

; initialize datastore kind entity
(ds/defentity AnswerTextfield [value, nvalue, avalue]) 

; normalize and lowercase a string
(defn normalize [string-to-normalize]
  (lower-case
    (apply str
      (remove #(= (Character/getType %) Character/NON_SPACING_MARK)
               (java.text.Normalizer/normalize string-to-normalize java.text.Normalizer$Form/NFKD)))))

; save original value, normalized value and splitted normalized value
(defn textfield-save! [value]
  (ds/save! 
    (let [nvalue (normalize value)]
      (ds/new* AnswerTextfield [value nvalue (split #" " nvalue)]))))

; normalized search
(defn search-normalized [value]
  (ds/query :kind AnswerTextfield
            :filter [(= :nvalue (normalize value))]))

; partial normalized word search
(defn search-partial [value]
  (flatten
    (let [coll []]
      (for [splitted-value (split #" " (normalize value))]
        (merge coll 
          (ds/query :kind AnswerTextfield
                    :filter [(in :avalue [splitted-value])]))))))