I've been digging around the archives, web and source code to figure case insensitive queries using SA, and am posting these notes: (a) for review and comment, and (b) to provide some possible hints for others who may follow
For me, a big benefit of using SA is that it insulates me from database specifics. Ideally, I wanted a way to handle case insensitive queries that would work across all supported databases, or at least SQLite, MySQL and Postgres. Previous messages on this list [1][2] suggest that won't happen before SA 0.5 (with no guarantee of that, I guess) [3]. There was a possible answer at [4], but I didn't like the potential implications for query performance. [1] http://groups.google.com/group/sqlalchemy/browse_frm/thread/ce87bd74fa4311ba/9c06644a94b358b6?lnk=gst&q=case+insensitive+query#9c06644a94b358b6 [2] http://groups.google.com/group/sqlalchemy/browse_frm/thread/cc8c775dd54b7b9c/18e059753d819455?lnk=gst&q=case+insensitive+query#18e059753d819455 [3] http://www.sqlalchemy.org/trac/ticket/487 [4] http://groups.google.com/group/sqlalchemy/browse_frm/thread/a7b1c325c267bba8/6ef14c5957b59395?lnk=gst&q=case+insensitive+query#6ef14c5957b59395 So the solution I've adopted, and tested with SQLite, is to use a TypeDecorator class, thus: [[ class CI_String(sqlalchemy.types.TypeDecorator): """ Defines a case insensitive string type using SQLite dialect options TODO: extend case insensitive specification to support other databases """ impl = sqlalchemy.String def get_col_spec(self): return super(CI_String,self).get_col_spec()+" COLLATE NOCASE" ]] Which I use in a Table definition thus: [[ def defineAffyLocalTable(tablename, metadata): """ Return SQLAlchemy table for Affymetrix local data entry. """ table = Table(tablename, metadata, Column('recordid', Integer, Sequence('recordid_seq'), primary_key=True), Column('probeid', CI_String(25), index=True), Column('aly_mean', Float), Column('can_mean', Float), Column('comr_mean', Float), Column('topi_mean', Float), Column('zaa_mean', Float), Column('red_e_mean', Float), Column('description', Text), ) return table ]] Might something like this be a basis for a fairly simple SA-common type that can be implemented appropriately for each database? #g --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
