Merge lp:~stefanor/ibid/db-case-insensitive-401320 into lp:~ibid-core/ibid/old-trunk-1.6

[Stefano Rivera]

[Stefano Rivera (stefanor)]

You'll love this.
There's a good chance that there are still some bugs in here, so backup first.
But it seems to work.

[Stefano Rivera (stefanor)]

The review diff isn't very heplful because I broke models.py into db/models.py and db/versioned_schema.py.

Here are some diffs for those sections:
http://paste.pocoo.org/show/158502/
http://paste.pocoo.org/show/158503/

[lp:~stefanor/ibid/db-case-insensitive-401320]

805. By Stefano Rivera on 2009-12-21

SQLAlchemy 0.4 compatibility

806. By Stefano Rivera on 2009-12-21

Don't lower() in ibid_improt and ibid-factpack

807. By Stefano Rivera on 2009-12-21

Unicode please, ensure we have a factpack ID before setting it everywhere

808. By Stefano Rivera on 2009-12-22

Merge from trunk

809. By Stefano Rivera on 2009-12-22

Merge from trunk

810. By Stefano Rivera on 2009-12-22

summon- uses and_

[Jonathan Hitchcock (vhata)]

review: Approve

[lp:~stefanor/ibid/db-case-insensitive-401320]

811. By Stefano Rivera on 2009-12-22

Querying the wrong table, use .filter() syntax

812. By Stefano Rivera on 2009-12-29

Merge from trunk

[Michael Gorven (mgorven)]

Let's break stuff! ;-)
 review approve
 status approved

review: Approve

=== modified file 'ibid/auth.py'
--- ibid/auth.py	2009-10-19 15:07:40 +0000
+++ ibid/auth.py	2009-12-29 16:00:32 +0000
@@ -8,7 +8,7 @@
 
 import ibid
 from ibid.compat import hashlib
-from ibid.models import Credential, Permission
+from ibid.db.models import Credential, Permission
 
 chars = string.letters + string.digits
 permission_re = re.compile('^([+-]?)(\S+)$')
@@ -23,8 +23,8 @@
 def permission(name, account, source, session):
     if account:
         permission = session.query(Permission) \
-                .filter_by(account_id=account) \
-                .filter_by(name=name).first()
+                .filter_by(account_id=account, name=name) \
+                .first()
 
         if permission:
             return permission.value
@@ -123,9 +123,10 @@
             return False
 
         for credential in event.session.query(Credential) \
-                .filter_by(method=u'password') \
-                .filter_by(account_id=event.account) \
-                .filter(or_(Credential.source == event.source, Credential.source == None)).all():
+                .filter_by(method=u'password', account_id=event.account) \
+                .filter(or_(Credential.source == event.source,
+                            Credential.source == None)) \
+                .all():
             if hash(password, credential.credential) == credential.credential:
                 return True
 

=== modified file 'ibid/core.py'
--- ibid/core.py	2009-12-21 12:43:58 +0000
+++ ibid/core.py	2009-12-29 16:00:32 +0000
@@ -12,6 +12,7 @@
 
 import ibid
 from ibid.event import Event
+from ibid.db import SchemaVersionException, schema_version_check
 
 import auth
 
@@ -60,8 +61,8 @@
 
         filtered = []
         for response in event['responses']:
-            source = response['source'].lower()
-            if source == event.source.lower():
+            source = response['source']
+            if source == event.source:
                 if (response.get('action', False)
                         and 'action' not in ibid.sources[source].supports):
                     response['reply'] = '* %s %s' % (
@@ -238,8 +239,8 @@
                         self.log.debug("Skipping Processor: %s.%s", name, klass.__name__)
 
             try:
-                ibid.models.check_schema_versions(ibid.databases['ibid'])
-            except ibid.models.SchemaVersionException, e:
+                schema_version_check(ibid.databases['ibid'])
+            except SchemaVersionException, e:
                 self.log.error(u'Tables out of date: %s. Run "ibid-db --upgrade"', e.message)
         except Exception, e:
             self.log.exception(u"Couldn't instantiate %s processor of %s plugin", classname, name)
@@ -306,8 +307,8 @@
 
         if check_schema_versions:
             try:
-                ibid.models.check_schema_versions(self['ibid'])
-            except ibid.models.SchemaVersionException, e:
+                schema_version_check(self['ibid'])
+            except SchemaVersionException, e:
                 self.log.error(u'Tables out of date: %s. Run "ibid-db --upgrade"', e.message)
                 raise
 

=== added directory 'ibid/db'
=== added file 'ibid/db/__init__.py'
--- ibid/db/__init__.py	1970-01-01 00:00:00 +0000
+++ ibid/db/__init__.py	2009-12-29 16:00:32 +0000
@@ -0,0 +1,18 @@
+from ibid.db.types import TypeDecorator, Integer, DateTime, Boolean, \
+                          IbidUnicode, IbidUnicodeText
+
+from sqlalchemy import Table, Column, ForeignKey, Index, UniqueConstraint, \
+                       PassiveDefault, or_, and_, MetaData as _MetaData
+from sqlalchemy.orm import eagerload, relation, synonym
+from sqlalchemy.sql import func
+from sqlalchemy.ext.declarative import declarative_base as _declarative_base
+
+from sqlalchemy.exceptions import IntegrityError
+
+metadata = _MetaData()
+Base = _declarative_base(metadata=metadata)
+
+from ibid.db.versioned_schema import VersionedSchema, SchemaVersionException, \
+                                     schema_version_check, upgrade_schemas
+
+# vi: set et sta sw=4 ts=4:

=== renamed file 'ibid/models.py' => 'ibid/db/models.py'
--- ibid/models.py	2009-12-11 12:13:05 +0000
+++ ibid/db/models.py	2009-12-29 16:00:32 +0000
@@ -1,428 +1,29 @@
 from datetime import datetime
-import logging
-import re
 
-from sqlalchemy import Column, Integer, Unicode, UnicodeText, DateTime, \
-        ForeignKey, UniqueConstraint, MetaData, Table, Index, \
-        __version__ as sqlalchemy_version
+from ibid.db.types import IbidUnicode, IbidUnicodeText, Integer, DateTime
+from sqlalchemy import Table, Column, ForeignKey, UniqueConstraint
 from sqlalchemy.orm import relation
-from sqlalchemy.ext.declarative import declarative_base
-from sqlalchemy.exceptions import InvalidRequestError, OperationalError, \
-        ProgrammingError
-
-if sqlalchemy_version < '0.5':
-    NoResultFound = InvalidRequestError
-else:
-    from sqlalchemy.orm.exc import NoResultFound
-
-metadata = MetaData()
-Base = declarative_base(metadata=metadata)
-log = logging.getLogger('ibid.models')
-
-class VersionedSchema(object):
-    """For an initial table schema, set
-    table.versioned_schema = VersionedSchema(__table__, 1)
-    Table creation (upgrading to version 1) is implicitly supported.
-
-    When you have upgrades to the schema, instead of using VersionedSchema
-    directly, derive from it and include your own upgrade_x_to_y(self) methods,
-    where y = x + 1
-
-    In the upgrade methods, you can call the helper functions:
-    add_column, drop_column, rename_column, alter_column
-    They try to do the correct thing in most situations, including rebuilding
-    tables in SQLite, which doesn't actually support dropping/altering columns.
-    For column parameters, while you can point to columns in the table
-    definition, it is better style to repeat the Column() specification as the
-    column might be altered in a future version.
-    """
-    foreign_key_re = re.compile(r'^FOREIGN KEY\(.*?\) (REFERENCES .*)$', re.I)
-
-    def __init__(self, table, version):
-        self.table = table
-        self.version = version
-
-    def is_up_to_date(self, session):
-        "Is the table in the database up to date with the schema?"
-
-        if not session.bind.has_table(self.table.name):
-            return False
-
-        try:
-            schema = session.query(Schema) \
-                    .filter_by(table=unicode(self.table.name)).one()
-            return schema.version == self.version
-        except NoResultFound:
-            return False
-
-    def upgrade_schema(self, sessionmaker):
-        "Upgrade the table's schema to the latest version."
-
-        for fk in self.table.foreign_keys:
-            dependency = fk.target_fullname.split('.')[0]
-            log.debug("Upgrading table %s before %s",
-                    dependency, self.table.name)
-            metadata.tables[dependency].versioned_schema \
-                    .upgrade_schema(sessionmaker)
-
-        self.upgrade_session = session = sessionmaker()
-        self.upgrade_reflected_model = MetaData(session.bind, reflect=True)
-
-        if self.table.name == 'schema':
-            if not session.bind.has_table(self.table.name):
-                metadata.bind = session.bind
-                self._create_table()
-
-                schema = Schema(unicode(self.table.name), self.version)
-                session.save_or_update(schema)
-                return
-            Schema.__table__ = self._get_reflected_model()
-
-        schema = session.query(Schema) \
-                .filter_by(table=unicode(self.table.name)).first()
-
-        try:
-            if not schema:
-                log.info(u"Creating table %s", self.table.name)
-
-                self._create_table()
-
-                schema = Schema(unicode(self.table.name), self.version)
-                session.save_or_update(schema)
-
-            elif self.version > schema.version:
-                for version in range(schema.version + 1, self.version + 1):
-                    log.info(u"Upgrading table %s to version %i",
-                            self.table.name, version)
-
-                    session.commit()
-
-                    getattr(self, 'upgrade_%i_to_%i' % (version - 1, version))()
-
-                    schema.version = version
-                    session.save_or_update(schema)
-
-                    self.upgrade_reflected_model = \
-                            MetaData(session.bind, reflect=True)
-
-            session.commit()
-
-        except:
-            session.rollback()
-            raise
-
-        session.close()
-        del self.upgrade_session
-
-    def _index_name(self, col):
-        """
-        We'd like not to duplicate an existing index so try to abide by the
-        local customs
-        """
-        session = self.upgrade_session
-
-        if session.bind.engine.name == 'sqlite':
-            return 'ix_%s_%s' % (self.table.name, col.name)
-        elif session.bind.engine.name == 'postgres':
-            return '%s_%s_key' % (self.table.name, col.name)
-        elif session.bind.engine.name == 'mysql':
-            return col.name
-
-        log.warning(u"Unknown database type, %s, you may end up with "
-                u"duplicate indices" % session.bind.engine.name)
-        return 'ix_%s_%s' % (self.table.name, col.name)
-
-    def _mysql_constraint_createstring(self, constraint):
-        """
-        Generate the description of a constraint for insertion into a CREATE
-        string
-        """
-        return ', '.join(
-            (isinstance(column.type, UnicodeText)
-                    and '"%(name)s"(%(length)i)'
-                    or '"%(name)s"') % {
-                'name': column.name,
-                'length': column.info.get('ibid_mysql_index_length', 8),
-            } for column in constraint.columns
-        )
-
-    def _create_table(self):
-        """
-        Check that the table is in a suitable form for all DBs, before
-        creating. Yes, SQLAlchemy's abstractions are leaky enough that you have
-        to do this
-        """
-        session = self.upgrade_session
-        indices = []
-        old_indexes = list(self.table.indexes)
-        old_constraints = list(self.table.constraints)
-
-        for column in self.table.c:
-            if column.unique and not column.index:
-                raise Exception(u"Column %s.%s is unique but not indexed. "
-                    u"SQLite doesn't like such things, "
-                    u"so please be nice and don't do that."
-                    % (self.table.name, self.column.name))
-
-        # Strip out Indexes and Constraints that SQLAlchemy can't create by
-        # itself
-        if session.bind.engine.name == 'mysql':
-            for type, old_list in (
-                    ('constraints', old_constraints),
-                    ('indexes', old_indexes)):
-                for constraint in old_list:
-                    if any(True for column in constraint.columns
-                            if isinstance(column.type, UnicodeText)):
-                        indices.append((
-                            isinstance(constraint, UniqueConstraint),
-                            self._mysql_constraint_createstring(constraint)
-                        ))
-
-                        getattr(self.table, type).remove(constraint)
-            # In case the database's DEFAULT CHARSET isn't set to UTF8
-            self.table.kwargs['mysql_charset'] = 'utf8'
-
-        self.table.create(bind=session.bind)
-
-        if session.bind.engine.name == 'mysql':
-            for constraint in old_constraints:
-                if constraint not in self.table.constraints:
-                    self.table.constraints.add(constraint)
-
-            for index in old_indexes:
-                if index not in self.table.indexes:
-                    self.table.indexes.add(index)
-
-            for unique, columnspec in indices:
-                session.execute('ALTER TABLE "%s" ADD %s INDEX (%s);' % (
-                    self.table.name, unique and 'UNIQUE' or '', columnspec))
-
-    def _get_reflected_model(self):
-        "Get a reflected table from the current DB's schema"
-
-        return self.upgrade_reflected_model.tables.get(self.table.name, None)
-
-    def add_column(self, col):
-        "Add column col to table"
-
-        session = self.upgrade_session
-        table = self._get_reflected_model()
-
-        log.debug(u"Adding column %s to table %s", col.name, table.name)
-
-        constraints = table.constraints.copy()
-        table.append_column(col)
-        constraints = table.constraints - constraints
-
-        sg = session.bind.dialect.schemagenerator(session.bind.dialect,
-                session.bind)
-        description = sg.get_column_specification(col)
-
-        for constraint in constraints:
-            sg.traverse_single(constraint)
-
-        constraints = []
-        for constraint in [x.strip() for x in sg.buffer.getvalue().split(',')]:
-            m = self.foreign_key_re.match(constraint)
-            if m:
-                constraints.append(m.group(1))
-            else:
-                constraints.append(constraint)
-
-        session.execute('ALTER TABLE "%s" ADD COLUMN %s %s;'
-                % (table.name, description, " ".join(constraints)))
-
-    def add_index(self, col, unique=False):
-        "Add an index to the table"
-
-        engine = self.upgrade_session.bind.engine.name
-
-        try:
-            Index(self._index_name(col), col, unique=unique) \
-                    .create(bind=self.upgrade_session.bind)
-
-        # We understand that occasionaly we'll duplicate an Index.
-        # This is due to differences in index-creation requirements
-        # between DBMS
-        except OperationalError, e:
-            if engine == 'sqlalchemy' and u'already exists' in unicode(e):
-                return
-            if engine == 'mysql' and u'Duplicate' in unicode(e):
-                return
-            raise
-        except ProgrammingError, e:
-            if engine == 'postgres' and u'already exists' in unicode(e):
-                return
-            raise
-
-    def drop_column(self, col_name):
-        "Drop column col_name from table"
-
-        session = self.upgrade_session
-
-        log.debug(u"Dropping column %s from table %s",
-                col_name, self.table.name)
-
-        if session.bind.engine.name == 'sqlite':
-            self._rebuild_sqlite({col_name: None})
-        else:
-            session.execute('ALTER TABLE "%s" DROP COLUMN "%s";'
-                    % (self.table.name, col_name))
-
-    def rename_column(self, col, old_name):
-        "Rename column from old_name to Column col"
-
-        session = self.upgrade_session
-        table = self._get_reflected_model()
-
-        log.debug(u"Rename column %s to %s in table %s",
-                old_name, col.name, table.name)
-
-        if session.bind.engine.name == 'sqlite':
-            self._rebuild_sqlite({old_name: col})
-        elif session.bind.engine.name == 'mysql':
-            self.alter_column(col, old_name)
-        else:
-            session.execute('ALTER TABLE "%s" RENAME COLUMN "%s" TO "%s";'
-                    % (table.name, old_name, col.name))
-
-    def alter_column(self, col, old_name=None):
-        """Change a column (possibly renaming from old_name) to Column col."""
-
-        session = self.upgrade_session
-        table = self._get_reflected_model()
-
-        log.debug(u"Altering column %s in table %s", col.name, table.name)
-
-        sg = session.bind.dialect.schemagenerator(session.bind.dialect,
-                session.bind)
-        description = sg.get_column_specification(col)
-        old_col = table.c[old_name or col.name]
-
-        if session.bind.engine.name == 'sqlite':
-            if (isinstance(col.type, (UnicodeText, Unicode))
-                        and isinstance(old_col.type, (UnicodeText, Unicode))
-                    ) or (isinstance(col.type, Integer)
-                        and isinstance(old_col.type, (Integer))):
-                # SQLite doesn't enforce value length restrictions
-                # only type changes have a real effect
-                return
-
-            self._rebuild_sqlite(
-                    {old_name is None and col.name or old_name: col})
-
-        elif session.bind.engine.name == 'mysql':
-            # Special handling for columns of TEXT type, because SQLAlchemy
-            # can't create indexes for them
-            recreate = []
-            if isinstance(col.type, UnicodeText) \
-                    or isinstance(old_col.type, UnicodeText):
-                for type in (table.constraints, table.indexes):
-                    for constraint in list(type):
-                        if any(True for column in constraint.columns
-                                if old_col.name == column.name):
-                            constraint.drop()
-
-                            constraint.columns = [
-                                (old_col.name == column.name) and col or column
-                                for column in constraint.columns
-                            ]
-                            recreate.append((
-                                isinstance(constraint, UniqueConstraint),
-                                self._mysql_constraint_createstring(constraint)
-                            ))
-
-            session.execute('ALTER TABLE "%s" CHANGE "%s" %s;' %
-                (table.name, old_col.name, description))
-
-            for unique, columnspec in recreate:
-                session.execute('ALTER TABLE "%s" ADD %s INDEX (%s);' %
-                    (self.table.name, unique and 'UNIQUE' or '', columnspec))
-
-        else:
-            if old_name is not None:
-                self.rename_column(col, old_name)
-
-            session.execute('ALTER TABLE "%s" ALTER COLUMN "%s" TYPE %s;' %
-                (table.name, col.name, description.split(" ", 3)[1]))
-
-            if old_col.nullable != col.nullable:
-                session.execute(
-                    'ALTER TABLE "%s" ALTER COLUMN "%s" %s NOT NULL;'
-                    % (table.name, col.name, col.nullable and 'DROP' or 'SET')
-                )
-
-    def _rebuild_sqlite(self, colmap):
-        """
-        SQLite doesn't support modification of table schema - must rebuild the
-        table.
-        colmap maps old column names to new Columns
-        (or None for column deletion).
-        Only modified columns need to be listed, unchaged columns are carried
-        over automatically.
-        Specify table in case name has changed in a more recent version.
-        """
-
-        session = self.upgrade_session
-        table = self._get_reflected_model()
-
-        log.debug(u"Rebuilding SQLite table %s", table.name)
-
-        fullcolmap = {}
-        for col in table.c:
-            if col.name in colmap:
-                if colmap[col.name] is not None:
-                    fullcolmap[col.name] = colmap[col.name].name
-            else:
-                fullcolmap[col.name] = col.name
-
-        for old, col in colmap.iteritems():
-            del table.c[old]
-            if col is not None:
-                table.append_column(col)
-
-        session.execute('ALTER TABLE "%s" RENAME TO "%s_old";'
-                % (table.name, table.name))
-
-        # SQLAlchemy indexes aren't attached to tables, they must be dropped
-        # around now or we'll get a clash
-        for constraint in table.indexes:
-            constraint.drop()
-
-        table.create()
-
-        session.execute('INSERT INTO "%s" ("%s") SELECT "%s" FROM "%s_old";'
-            % (
-                table.name,
-                '", "'.join(fullcolmap.values()),
-                '", "'.join(fullcolmap.keys()),
-                table.name
-        ))
-
-        session.execute('DROP TABLE "%s_old";' % table.name)
-
-        # SQLAlchemy doesn't pick up all the indexes in the reflected table.
-        # It's ok to use indexes that may be further in the future than this
-        # upgrade because either we can already support them or we'll be
-        # rebuilding again soon
-        for constraint in self.table.indexes:
-            try:
-                constraint.create(bind=session.bind)
-            except OperationalError:
-                pass
+from ibid.db import Base
+from ibid.db.versioned_schema import VersionedSchema
 
 class Schema(Base):
     __table__ = Table('schema', Base.metadata,
         Column('id', Integer, primary_key=True),
-        Column('table', Unicode(32), unique=True, nullable=False, index=True),
+        Column('table', IbidUnicode(32), unique=True, nullable=False,
+               index=True),
         Column('version', Integer, nullable=False),
         useexisting=True)
 
     class SchemaSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_index(self.table.c.table, unique=True)
+            self.add_index(self.table.c.table)
+        def upgrade_2_to_3(self):
+            self.drop_index(self.table.c.table)
+            self.alter_column(Column('table', IbidUnicode(32), unique=True,
+                              nullable=False, index=True), force_rebuild=True)
+            self.add_index(self.table.c.table)
 
-    __table__.versioned_schema = SchemaSchema(__table__, 2)
+    __table__.versioned_schema = SchemaSchema(__table__, 3)
 
     def __init__(self, table, version=0):
         self.table = table
@@ -435,9 +36,10 @@
     __table__ = Table('identities', Base.metadata,
         Column('id', Integer, primary_key=True),
         Column('account_id', Integer, ForeignKey('accounts.id'), index=True),
-        Column('source', Unicode(32), nullable=False, index=True),
-        Column('identity', UnicodeText, nullable=False, index=True,
-            info={'ibid_mysql_index_length': 32}),
+        Column('source', IbidUnicode(32, case_insensitive=True),
+               nullable=False, index=True),
+        Column('identity', IbidUnicodeText(32, case_insensitive=True),
+               nullable=False, index=True),
         Column('created', DateTime),
         UniqueConstraint('source', 'identity'),
         useexisting=True)
@@ -449,12 +51,25 @@
             self.add_index(self.table.c.identity)
 
         def upgrade_2_to_3(self):
+            self.alter_column(Column('source', IbidUnicode(32),
+                                     nullable=False, index=True))
+            self.alter_column(Column('identity', IbidUnicodeText,
+                                     nullable=False, index=True))
+        def upgrade_3_to_4(self):
+            self.drop_index(self.table.c.source)
+            self.drop_index(self.table.c.identity)
             self.alter_column(Column('source',
-                    Unicode(32), nullable=False, index=True))
+                                     IbidUnicode(32, case_insensitive=True),
+                                     nullable=False, index=True),
+                              force_rebuild=True)
             self.alter_column(Column('identity',
-                    UnicodeText, nullable=False, index=True))
+                                     IbidUnicodeText(32, case_insensitive=True),
+                                     nullable=False, index=True),
+                              force_rebuild=True)
+            self.add_index(self.table.c.source)
+            self.add_index(self.table.c.identity)
 
-    __table__.versioned_schema = IdentitySchema(__table__, 3)
+    __table__.versioned_schema = IdentitySchema(__table__, 4)
 
     def __init__(self, source, identity, account_id=None):
         self.source = source
@@ -469,9 +84,10 @@
     __table__ = Table('account_attributes', Base.metadata,
         Column('id', Integer, primary_key=True),
         Column('account_id', Integer, ForeignKey('accounts.id'),
-            nullable=False, index=True),
-        Column('name', Unicode(32), nullable=False, index=True),
-        Column('value', UnicodeText, nullable=False),
+               nullable=False, index=True),
+        Column('name', IbidUnicode(32, case_insensitive=True),
+               nullable=False, index=True),
+        Column('value', IbidUnicodeText, nullable=False),
         UniqueConstraint('account_id', 'name'),
         useexisting=True)
 
@@ -480,9 +96,18 @@
             self.add_index(self.table.c.account_id)
             self.add_index(self.table.c.name)
         def upgrade_2_to_3(self):
-            self.alter_column(Column('value', UnicodeText, nullable=False))
+            self.alter_column(Column('value', IbidUnicodeText, nullable=False))
+        def upgrade_3_to_4(self):
+            self.drop_index(self.table.c.name)
+            self.alter_column(Column('name',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     nullable=False, index=True),
+                              force_rebuild=True)
+            self.alter_column(Column('value', IbidUnicodeText, nullable=False),
+                              force_rebuild=True)
+            self.add_index(self.table.c.name)
 
-    __table__.versioned_schema = AttributeSchema(__table__, 3)
+    __table__.versioned_schema = AttributeSchema(__table__, 4)
 
     def __init__(self, name, value):
         self.name = name
@@ -495,10 +120,11 @@
     __table__ = Table('credentials', Base.metadata,
         Column('id', Integer, primary_key=True),
         Column('account_id', Integer, ForeignKey('accounts.id'),
-                nullable=False, index=True),
-        Column('source', Unicode(32), index=True),
-        Column('method', Unicode(16), nullable=False, index=True),
-        Column('credential', UnicodeText, nullable=False),
+               nullable=False, index=True),
+        Column('source', IbidUnicode(32, case_insensitive=True), index=True),
+        Column('method', IbidUnicode(16, case_insensitive=True),
+               nullable=False, index=True),
+        Column('credential', IbidUnicodeText, nullable=False),
         useexisting=True)
 
     class CredentialSchema(VersionedSchema):
@@ -507,11 +133,25 @@
             self.add_index(self.table.c.source)
             self.add_index(self.table.c.method)
         def upgrade_2_to_3(self):
-            self.alter_column(Column('source', Unicode(32), index=True))
+            self.alter_column(Column('source', IbidUnicode(32), index=True))
             self.alter_column(Column('credential',
-                    UnicodeText, nullable=False))
+                                     IbidUnicodeText, nullable=False))
+        def upgrade_3_to_4(self):
+            self.drop_index(self.table.c.source)
+            self.drop_index(self.table.c.method)
+            self.alter_column(Column('source',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     index=True), force_rebuild=True)
+            self.alter_column(Column('method',
+                                     IbidUnicode(16, case_insensitive=True),
+                                     nullable=False, index=True),
+                              force_rebuild=True)
+            self.alter_column(Column('credential', IbidUnicodeText,
+                                     nullable=False), force_rebuild=True)
+            self.add_index(self.table.c.source)
+            self.add_index(self.table.c.method)
 
-    __table__.versioned_schema = CredentialSchema(__table__, 3)
+    __table__.versioned_schema = CredentialSchema(__table__, 4)
 
     def __init__(self, method, credential, source=None, account_id=None):
         self.account_id = account_id
@@ -523,9 +163,10 @@
     __table__ = Table('permissions', Base.metadata,
         Column('id', Integer, primary_key=True),
         Column('account_id', Integer, ForeignKey('accounts.id'),
-                nullable=False, index=True),
-        Column('name', Unicode(16), nullable=False, index=True),
-        Column('value', Unicode(4), nullable=False),
+               nullable=False, index=True),
+        Column('name', IbidUnicode(16, case_insensitive=True),
+               nullable=False, index=True),
+        Column('value', IbidUnicode(4, case_insensitive=True), nullable=False),
         UniqueConstraint('account_id', 'name'),
         useexisting=True)
 
@@ -533,8 +174,18 @@
         def upgrade_1_to_2(self):
             self.add_index(self.table.c.account_id)
             self.add_index(self.table.c.name)
+        def upgrade_2_to_3(self):
+            self.drop_index(self.table.c.name)
+            self.alter_column(Column('name',
+                                     IbidUnicode(16, case_insensitive=True),
+                                     index=True), force_rebuild=True)
+            self.alter_column(Column('value',
+                                     IbidUnicode(4, case_insensitive=True),
+                                     nullable=False, index=True),
+                              force_rebuild=True)
+            self.add_index(self.table.c.name)
 
-    __table__.versioned_schema = PermissionSchema(__table__, 2)
+    __table__.versioned_schema = PermissionSchema(__table__, 3)
 
     def __init__(self, name=None, value=None):
         self.name = name
@@ -543,15 +194,22 @@
 class Account(Base):
     __table__ = Table('accounts', Base.metadata,
         Column('id', Integer, primary_key=True),
-        Column('username', Unicode(32), unique=True, nullable=False,
-                index=True),
+        Column('username', IbidUnicode(32, case_insensitive=True),
+               unique=True, nullable=False, index=True),
         useexisting=True)
 
     class AccountSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_index(self.table.c.username, unique=True)
+            self.add_index(self.table.c.username)
+        def upgrade_2_to_3(self):
+            self.drop_index(self.table.c.username)
+            self.alter_column(Column('username',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     unique=True, nullable=False, index=True),
+                              force_rebuild=True)
+            self.add_index(self.table.c.username)
 
-    __table__.versioned_schema = AccountSchema(__table__, 2)
+    __table__.versioned_schema = AccountSchema(__table__, 3)
 
     identities = relation(Identity, backref='account')
     attributes = relation(Attribute, cascade='all, delete-orphan')
@@ -564,44 +222,4 @@
     def __repr__(self):
         return '<Account %s>' % self.username
 
-
-class SchemaVersionException(Exception):
-    """There is an out-of-date table.
-    The message should be a list of out of date tables.
-    """
-    pass
-
-
-def check_schema_versions(sessionmaker):
-    """Pass through all tables, log out of date ones,
-    and except if not all up to date"""
-
-    session = sessionmaker()
-    upgrades = []
-    for table in metadata.tables.itervalues():
-        if not hasattr(table, 'versioned_schema'):
-            log.error("Table %s is not versioned.", table.name)
-            continue
-
-        if not table.versioned_schema.is_up_to_date(session):
-            upgrades.append(table.name)
-
-    if not upgrades:
-        return
-
-    raise SchemaVersionException(u", ".join(upgrades))
-
-def upgrade_schemas(sessionmaker):
-    "Pass through all tables and update schemas"
-
-    # Make sure schema table is created first
-    metadata.tables['schema'].versioned_schema.upgrade_schema(sessionmaker)
-
-    for table in metadata.tables.itervalues():
-        if not hasattr(table, 'versioned_schema'):
-            log.error("Table %s is not versioned.", table.name)
-            continue
-
-        table.versioned_schema.upgrade_schema(sessionmaker)
-
 # vi: set et sta sw=4 ts=4:

=== added file 'ibid/db/types.py'
--- ibid/db/types.py	1970-01-01 00:00:00 +0000
+++ ibid/db/types.py	2009-12-29 16:00:32 +0000
@@ -0,0 +1,62 @@
+from sqlalchemy.types import TypeDecorator, Integer, DateTime, Boolean, \
+                             Unicode as _Unicode, UnicodeText as _UnicodeText
+
+class _CIDecorator(TypeDecorator):
+    "Abstract class for collation aware columns"
+
+    def __init__(self, length=None, case_insensitive=False):
+        self.case_insensitive = case_insensitive
+        super(_CIDecorator, self).__init__(length=length)
+
+    def load_dialect_impl(self, dialect):
+        if hasattr(dialect, 'name'):
+            self.dialect = dialect.name
+        # SQLAlchemy 0.4:
+        else:
+            self.dialect = {
+                'SQLiteDialect': 'sqlite',
+                'PGDialect': 'postgres',
+                'MySQLDialect': 'mysql',
+            }[dialect.__class__.__name__]
+
+        return dialect.type_descriptor(self.impl)
+
+    def get_col_spec(self):
+        colspec = self.impl.get_col_spec()
+        if hasattr(self, 'case_insensitive'):
+            collation = None
+            if self.dialect == 'mysql':
+                if self.case_insensitive:
+                    collation = 'utf8_general_ci'
+                else:
+                    collation = 'utf8_bin'
+            elif self.dialect == 'sqlite':
+                if self.case_insensitive:
+                    collation = 'NOCASE'
+                else:
+                    collation = 'BINARY'
+            elif self.dialect == 'postgres' and self.case_insensitive:
+                return 'CITEXT'
+
+            if collation is not None:
+                return colspec + ' COLLATE ' + collation
+        return colspec
+
+class IbidUnicode(_CIDecorator):
+    "Collaiton aware Unicode"
+
+    impl = _Unicode
+
+    def __init__(self, length, **kwargs):
+        super(IbidUnicode, self).__init__(length, **kwargs)
+
+class IbidUnicodeText(_CIDecorator):
+    "Collation aware UnicodeText"
+
+    impl = _UnicodeText
+
+    def __init__(self, index_length=8, **kwargs):
+        self.index_length = index_length
+        super(IbidUnicodeText, self).__init__(length=None, **kwargs)
+
+# vi: set et sta sw=4 ts=4:

=== added file 'ibid/db/versioned_schema.py'
--- ibid/db/versioned_schema.py	1970-01-01 00:00:00 +0000
+++ ibid/db/versioned_schema.py	2009-12-29 16:00:32 +0000
@@ -0,0 +1,515 @@
+import logging
+import re
+
+from sqlalchemy import Column, Index, UniqueConstraint, MetaData, \
+                       __version__ as _sqlalchemy_version
+from sqlalchemy.exceptions import InvalidRequestError, OperationalError, \
+                                  ProgrammingError, InternalError
+if _sqlalchemy_version < '0.5':
+    NoResultFound = InvalidRequestError
+else:
+    from sqlalchemy.orm.exc import NoResultFound
+
+from ibid.db.types import Integer, IbidUnicodeText, IbidUnicode
+
+from ibid.db import metadata
+
+log = logging.getLogger('ibid.db.versioned_schema')
+
+class VersionedSchema(object):
+    """For an initial table schema, set
+    table.versioned_schema = VersionedSchema(__table__, 1)
+    Table creation (upgrading to version 1) is implicitly supported.
+
+    When you have upgrades to the schema, instead of using VersionedSchema
+    directly, derive from it and include your own upgrade_x_to_y(self) methods,
+    where y = x + 1
+
+    In the upgrade methods, you can call the helper functions:
+    add_column, drop_column, rename_column, alter_column
+    They try to do the correct thing in most situations, including rebuilding
+    tables in SQLite, which doesn't actually support dropping/altering columns.
+    For column parameters, while you can point to columns in the table
+    definition, it is better style to repeat the Column() specification as the
+    column might be altered in a future version.
+    """
+    foreign_key_re = re.compile(r'^FOREIGN KEY\(.*?\) (REFERENCES .*)$', re.I)
+
+    def __init__(self, table, version):
+        self.table = table
+        self.version = version
+
+    def is_up_to_date(self, session):
+        "Is the table in the database up to date with the schema?"
+
+        from ibid.db.models import Schema
+
+        if not session.bind.has_table(self.table.name):
+            return False
+
+        try:
+            schema = session.query(Schema) \
+                    .filter_by(table=unicode(self.table.name)).one()
+            return schema.version == self.version
+        except NoResultFound:
+            return False
+
+    def upgrade_schema(self, sessionmaker):
+        "Upgrade the table's schema to the latest version."
+
+        from ibid.db.models import Schema
+
+        for fk in self.table.foreign_keys:
+            dependency = fk.target_fullname.split('.')[0]
+            log.debug("Upgrading table %s before %s",
+                    dependency, self.table.name)
+            metadata.tables[dependency].versioned_schema \
+                    .upgrade_schema(sessionmaker)
+
+        self.upgrade_session = session = sessionmaker()
+        self.upgrade_reflected_model = MetaData(session.bind, reflect=True)
+
+        if self.table.name == 'schema':
+            if not session.bind.has_table(self.table.name):
+                metadata.bind = session.bind
+                self._create_table()
+
+                schema = Schema(unicode(self.table.name), self.version)
+                session.save_or_update(schema)
+                return
+            Schema.__table__ = self._get_reflected_model()
+
+        schema = session.query(Schema) \
+                .filter_by(table=unicode(self.table.name)).first()
+
+        try:
+            if not schema:
+                log.info(u"Creating table %s", self.table.name)
+
+                self._create_table()
+
+                schema = Schema(unicode(self.table.name), self.version)
+                session.save_or_update(schema)
+
+            elif self.version > schema.version:
+                for version in range(schema.version + 1, self.version + 1):
+                    log.info(u"Upgrading table %s to version %i",
+                            self.table.name, version)
+
+                    session.commit()
+
+                    getattr(self, 'upgrade_%i_to_%i' % (version - 1, version))()
+
+                    schema.version = version
+                    session.save_or_update(schema)
+
+                    self.upgrade_reflected_model = \
+                            MetaData(session.bind, reflect=True)
+
+            session.commit()
+
+        except:
+            session.rollback()
+            raise
+
+        session.close()
+        del self.upgrade_session
+
+    def _index_name(self, col):
+        """
+        We'd like not to duplicate an existing index so try to abide by the
+        local customs
+        """
+        session = self.upgrade_session
+
+        if session.bind.engine.name == 'sqlite':
+            return 'ix_%s_%s' % (self.table.name, col.name)
+        elif session.bind.engine.name == 'postgres':
+            return '%s_%s_key' % (self.table.name, col.name)
+        elif session.bind.engine.name == 'mysql':
+            return col.name
+
+        log.warning(u"Unknown database type, %s, you may end up with "
+                u"duplicate indices" % session.bind.engine.name)
+        return 'ix_%s_%s' % (self.table.name, col.name)
+
+    def _mysql_constraint_createstring(self, constraint):
+        """
+        Generate the description of a constraint for insertion into a CREATE
+        string
+        """
+        names = []
+        for column in constraint.columns:
+            if isinstance(column.type, IbidUnicodeText):
+                names.append('"%s"(%i)'
+                             % (column.name, column.type.index_length))
+            else:
+                names.append(column.name)
+
+        return ', '.join(names)
+
+    def _create_table(self):
+        """
+        Check that the table is in a suitable form for all DBs, before
+        creating. Yes, SQLAlchemy's abstractions are leaky enough that you have
+        to do this
+        """
+        session = self.upgrade_session
+        indices = []
+        old_indexes = list(self.table.indexes)
+        old_constraints = list(self.table.constraints)
+
+        for column in self.table.c:
+            if column.unique and not column.index:
+                raise Exception(u"Column %s.%s is unique but not indexed. "
+                    u"SQLite doesn't like such things, "
+                    u"so please be nice and don't do that."
+                    % (self.table.name, self.column.name))
+
+        # Strip out Indexes and Constraints that SQLAlchemy can't create by
+        # itself
+        if session.bind.engine.name == 'mysql':
+            for type, old_list in (
+                    ('constraints', old_constraints),
+                    ('indexes', old_indexes)):
+                for constraint in old_list:
+                    if any(True for column in constraint.columns
+                            if isinstance(column.type, IbidUnicodeText)):
+                        indices.append((
+                            isinstance(constraint, UniqueConstraint),
+                            self._mysql_constraint_createstring(constraint)
+                        ))
+
+                        getattr(self.table, type).remove(constraint)
+            # In case the database's DEFAULT CHARSET isn't set to UTF8
+            self.table.kwargs['mysql_charset'] = 'utf8'
+
+        self.table.create(bind=session.bind)
+
+        if session.bind.engine.name == 'mysql':
+            for constraint in old_constraints:
+                if constraint not in self.table.constraints:
+                    self.table.constraints.add(constraint)
+
+            for index in old_indexes:
+                if index not in self.table.indexes:
+                    self.table.indexes.add(index)
+
+            for unique, columnspec in indices:
+                session.execute('ALTER TABLE "%s" ADD %s INDEX (%s);' % (
+                    self.table.name, unique and 'UNIQUE' or '', columnspec))
+
+    def _get_reflected_model(self):
+        "Get a reflected table from the current DB's schema"
+
+        return self.upgrade_reflected_model.tables.get(self.table.name, None)
+
+    def add_column(self, col):
+        "Add column col to table"
+
+        session = self.upgrade_session
+        table = self._get_reflected_model()
+
+        log.debug(u"Adding column %s to table %s", col.name, table.name)
+
+        constraints = table.constraints.copy()
+        table.append_column(col)
+        constraints = table.constraints - constraints
+
+        sg = session.bind.dialect.schemagenerator(session.bind.dialect,
+                session.bind)
+        description = sg.get_column_specification(col)
+
+        for constraint in constraints:
+            sg.traverse_single(constraint)
+
+        constraints = []
+        for constraint in [x.strip() for x in sg.buffer.getvalue().split(',')]:
+            m = self.foreign_key_re.match(constraint)
+            if m:
+                constraints.append(m.group(1))
+            else:
+                constraints.append(constraint)
+
+        session.execute('ALTER TABLE "%s" ADD COLUMN %s %s;'
+                % (table.name, description, " ".join(constraints)))
+
+    def add_index(self, col):
+        "Add an index to the table"
+
+        engine = self.upgrade_session.bind.engine.name
+        query = None
+
+        if engine == 'mysql' and isinstance(col.type, IbidUnicodeText):
+            query = 'ALTER TABLE "%s" ADD %s INDEX "%s" ("%s"(%i));' % (
+                    self.table.name, col.unique and 'UNIQUE' or '',
+                    self._index_name(col), col.name, col.type.index_length)
+        elif engine == 'postgres':
+            # SQLAlchemy hangs if it tries to do this, because it forgets the ;
+            query = 'CREATE %s INDEX "%s" ON "%s" ("%s")' % (
+                    col.unique and 'UNIQUE' or '',self._index_name(col),
+                    self.table.name, col.name)
+
+        try:
+            if query is not None:
+                self.upgrade_session.execute(query)
+            else:
+                Index(self._index_name(col), col, unique=col.unique) \
+                        .create(bind=self.upgrade_session.bind)
+
+        # We understand that occasionaly we'll duplicate an Index.
+        # This is due to differences in index-creation requirements
+        # between DBMS
+        except OperationalError, e:
+            if engine == 'sqlite' and u'already exists' in unicode(e):
+                return
+            if engine == 'mysql' and u'Duplicate' in unicode(e):
+                return
+            raise
+        except ProgrammingError, e:
+            if engine == 'postgres' and u'already exists' in unicode(e):
+                return
+            raise
+
+    def drop_index(self, col):
+        "Drop an index from the table"
+
+        engine = self.upgrade_session.bind.engine.name
+
+        try:
+            if isinstance(col, Column):
+                Index(self._index_name(col), col, unique=col.unique) \
+                        .drop(bind=self.upgrade_session.bind)
+            else:
+                col.drop()
+
+        except OperationalError, e:
+            if engine == 'sqlite' and u'no such index' in unicode(e):
+                return
+            if engine == 'mysql' \
+                    and u'check that column/key exists' in unicode(e):
+                return
+            raise
+
+        except ProgrammingError, e:
+            if engine == 'postgres' and u'does not exist' in unicode(e):
+                return
+            # In SQLAlchemy 0.4, the InternalError below is a ProgrammingError
+            # and can't be executed in the upgrade transaction:
+            if engine == 'postgres' and u'requires' in unicode(e):
+                self.upgrade_session.bind.execute(
+                        'ALTER TABLE "%s" DROP CONSTRAINT "%s"' % (
+                        self.table.name, self._index_name(col)))
+                return
+            raise
+
+        # Postgres constraints can be attached to tables and can't be dropped
+        # at DB level.
+        except InternalError, e:
+            if engine == 'postgres':
+                self.upgrade_session.execute(
+                        'ALTER TABLE "%s" DROP CONSTRAINT "%s"' % (
+                        self.table.name, self._index_name(col)))
+
+    def drop_column(self, col_name):
+        "Drop column col_name from table"
+
+        session = self.upgrade_session
+
+        log.debug(u"Dropping column %s from table %s",
+                col_name, self.table.name)
+
+        if session.bind.engine.name == 'sqlite':
+            self._rebuild_sqlite({col_name: None})
+        else:
+            session.execute('ALTER TABLE "%s" DROP COLUMN "%s";'
+                    % (self.table.name, col_name))
+
+    def rename_column(self, col, old_name):
+        "Rename column from old_name to Column col"
+
+        session = self.upgrade_session
+        table = self._get_reflected_model()
+
+        log.debug(u"Rename column %s to %s in table %s",
+                old_name, col.name, table.name)
+
+        if session.bind.engine.name == 'sqlite':
+            self._rebuild_sqlite({old_name: col})
+        elif session.bind.engine.name == 'mysql':
+            self.alter_column(col, old_name)
+        else:
+            session.execute('ALTER TABLE "%s" RENAME COLUMN "%s" TO "%s";'
+                    % (table.name, old_name, col.name))
+
+    def alter_column(self, col, old_name=None, force_rebuild=False):
+        """Change a column (possibly renaming from old_name) to Column col."""
+
+        session = self.upgrade_session
+        table = self._get_reflected_model()
+
+        log.debug(u"Altering column %s in table %s", col.name, table.name)
+
+        sg = session.bind.dialect.schemagenerator(session.bind.dialect,
+                session.bind)
+        description = sg.get_column_specification(col)
+        old_col = table.c[old_name or col.name]
+
+        # SQLite doesn't enforce value length restrictions
+        # only type changes have a real effect
+        if session.bind.engine.name == 'sqlite':
+            if not force_rebuild and (
+                (isinstance(col.type, (IbidUnicodeText, IbidUnicode))
+                    and isinstance(old_col.type, (IbidUnicodeText, IbidUnicode)
+                ) or (isinstance(col.type, Integer)
+                    and isinstance(old_col.type, Integer)))):
+                return
+
+            self._rebuild_sqlite(
+                    {old_name is None and col.name or old_name: col})
+
+        elif session.bind.engine.name == 'mysql':
+            # Special handling for columns of TEXT type, because SQLAlchemy
+            # can't create indexes for them
+            recreate = []
+            if isinstance(col.type, IbidUnicodeText) \
+                    or isinstance(old_col.type, IbidUnicodeText):
+                for type in (table.constraints, table.indexes):
+                    for constraint in list(type):
+                        if any(True for column in constraint.columns
+                                if old_col.name == column.name):
+
+                            self.drop_index(constraint)
+
+                            constraint.columns = [
+                                (old_col.name == column.name) and col or column
+                                for column in constraint.columns
+                            ]
+                            recreate.append((
+                                isinstance(constraint, UniqueConstraint),
+                                self._mysql_constraint_createstring(constraint)
+                            ))
+
+            session.execute('ALTER TABLE "%s" CHANGE "%s" %s;' %
+                (table.name, old_col.name, description))
+
+            for unique, columnspec in recreate:
+                session.execute('ALTER TABLE "%s" ADD %s INDEX (%s);' %
+                    (self.table.name, unique and 'UNIQUE' or '', columnspec))
+
+        else:
+            if old_name is not None:
+                self.rename_column(col, old_name)
+
+            session.execute('ALTER TABLE "%s" ALTER COLUMN "%s" TYPE %s;' %
+                (table.name, col.name, description.split(" ", 3)[1]))
+
+            if old_col.nullable != col.nullable:
+                session.execute(
+                    'ALTER TABLE "%s" ALTER COLUMN "%s" %s NOT NULL;'
+                    % (table.name, col.name, col.nullable and 'DROP' or 'SET')
+                )
+
+    def _rebuild_sqlite(self, colmap):
+        """
+        SQLite doesn't support modification of table schema - must rebuild the
+        table.
+        colmap maps old column names to new Columns
+        (or None for column deletion).
+        Only modified columns need to be listed, unchaged columns are carried
+        over automatically.
+        Specify table in case name has changed in a more recent version.
+        """
+
+        session = self.upgrade_session
+        table = self._get_reflected_model()
+
+        log.debug(u"Rebuilding SQLite table %s", table.name)
+
+        fullcolmap = {}
+        for col in table.c:
+            if col.name in colmap:
+                if colmap[col.name] is not None:
+                    fullcolmap[col.name] = colmap[col.name].name
+            else:
+                fullcolmap[col.name] = col.name
+
+        for old, col in colmap.iteritems():
+            del table.c[old]
+            if col is not None:
+                table.append_column(col)
+
+        session.execute('ALTER TABLE "%s" RENAME TO "%s_old";'
+                % (table.name, table.name))
+
+        # SQLAlchemy indexes aren't attached to tables, they must be dropped
+        # around now or we'll get a clash
+        for constraint in table.indexes:
+            try:
+                constraint.drop()
+            except OperationalError:
+                pass
+
+        table.create()
+
+        session.execute('INSERT INTO "%s" ("%s") SELECT "%s" FROM "%s_old";'
+            % (
+                table.name,
+                '", "'.join(fullcolmap.values()),
+                '", "'.join(fullcolmap.keys()),
+                table.name
+        ))
+
+        session.execute('DROP TABLE "%s_old";' % table.name)
+
+        # SQLAlchemy doesn't pick up all the indexes in the reflected table.
+        # It's ok to use indexes that may be further in the future than this
+        # upgrade because either we can already support them or we'll be
+        # rebuilding again soon
+        for constraint in self.table.indexes:
+            try:
+                constraint.create(bind=session.bind)
+            except OperationalError:
+                pass
+
+
+class SchemaVersionException(Exception):
+    """There is an out-of-date table.
+    The message should be a list of out of date tables.
+    """
+    pass
+
+
+def schema_version_check(sessionmaker):
+    """Pass through all tables, log out of date ones,
+    and except if not all up to date"""
+
+    session = sessionmaker()
+    upgrades = []
+    for table in metadata.tables.itervalues():
+        if not hasattr(table, 'versioned_schema'):
+            log.error("Table %s is not versioned.", table.name)
+            continue
+
+        if not table.versioned_schema.is_up_to_date(session):
+            upgrades.append(table.name)
+
+    if not upgrades:
+        return
+
+    raise SchemaVersionException(u", ".join(upgrades))
+
+def upgrade_schemas(sessionmaker):
+    "Pass through all tables and update schemas"
+
+    # Make sure schema table is created first
+    metadata.tables['schema'].versioned_schema.upgrade_schema(sessionmaker)
+
+    for table in metadata.tables.itervalues():
+        if not hasattr(table, 'versioned_schema'):
+            log.error("Table %s is not versioned.", table.name)
+            continue
+
+        table.versioned_schema.upgrade_schema(sessionmaker)
+
+# vi: set et sta sw=4 ts=4:

=== modified file 'ibid/plugins/auth.py'
--- ibid/plugins/auth.py	2009-10-20 15:45:46 +0000
+++ ibid/plugins/auth.py	2009-12-29 16:00:32 +0000
@@ -1,10 +1,8 @@
 import logging
 
-from sqlalchemy.sql import func
-
 import ibid
+from ibid.db.models import Credential, Permission, Account
 from ibid.plugins import Processor, match, auth_responses, authorise
-from ibid.models import Credential, Permission, Account
 from ibid.auth import hash
 from ibid.utils import human_join
 
@@ -79,8 +77,7 @@
             return
 
         permission = event.session.query(Permission) \
-                .filter_by(account_id=account.id) \
-                .filter(func.lower(Permission.name) == name.lower()).first()
+                .filter_by(account_id=account.id, name=name).first()
         if action.lower() == 'remove':
             if permission:
                 event.session.delete(permission)
@@ -128,7 +125,8 @@
         else:
             if not auth_responses(event, u'accounts'):
                 return
-            account = event.session.query(Account).filter_by(username=username).first()
+            account = event.session.query(Account) \
+                    .filter_by(username=username).first()
             if not account:
                 event.addresponse(u"I don't know who %s is", username)
                 return

=== modified file 'ibid/plugins/factoid.py'
--- ibid/plugins/factoid.py	2009-12-22 09:48:56 +0000
+++ ibid/plugins/factoid.py	2009-12-29 16:00:32 +0000
@@ -4,16 +4,15 @@
 import re
 
 from dateutil.tz import tzlocal, tzutc
-from sqlalchemy import Column, Table, ForeignKey, or_, Boolean, Integer, \
-                       Unicode, UnicodeText, DateTime, PassiveDefault
-from sqlalchemy.orm import relation, synonym
-from sqlalchemy.sql import func
 
 from ibid.plugins import Processor, match, handler, authorise, auth_responses, \
                          RPC
 from ibid.config import Option, IntOption, ListOption
+from ibid.db import IbidUnicode, IbidUnicodeText, Boolean, Integer, DateTime, \
+                    Table, Column, ForeignKey, PassiveDefault, \
+                    relation, synonym, func, or_, \
+                    Base, VersionedSchema
 from ibid.plugins.identity import get_identities
-from ibid.models import Base, VersionedSchema
 from ibid.utils import format_date
 
 help = {'factoids': u'Factoids are arbitrary pieces of information stored by a key. '
@@ -40,9 +39,10 @@
 class FactoidName(Base):
     __table__ = Table('factoid_names', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('name', UnicodeText, key='_name', nullable=False, unique=True, index=True,
-           info={'ibid_mysql_index_length': 32}),
-    Column('factoid_id', Integer, ForeignKey('factoids.id'), nullable=False, index=True),
+    Column('name', IbidUnicodeText(32, case_insensitive=True),
+           key='_name', nullable=False, unique=True, index=True),
+    Column('factoid_id', Integer, ForeignKey('factoids.id'), nullable=False,
+           index=True),
     Column('identity_id', Integer, ForeignKey('identities.id'), index=True),
     Column('time', DateTime, nullable=False),
     Column('factpack', Integer, ForeignKey('factpacks.id'), index=True),
@@ -51,21 +51,21 @@
 
     class FactoidNameSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_column(Column('factpack', Integer, ForeignKey('factpacks.id')))
+            self.add_column(Column('factpack', Integer,
+                                   ForeignKey('factpacks.id')))
         def upgrade_2_to_3(self):
-            self.add_index(self.table.c.name, unique=True)
+            self.add_index(self.table.c.name)
         def upgrade_3_to_4(self):
-            self.add_index(self.table.c.name, unique=True)
+            self.add_index(self.table.c.name)
             self.add_index(self.table.c.factoid_id)
             self.add_index(self.table.c.identity_id)
             self.add_index(self.table.c.factpack)
         def upgrade_4_to_5(self):
-            self.alter_column(Column('name', Unicode(64), key='_name', nullable=False,
-                                     unique=True, index=True))
+            self.alter_column(Column('name', IbidUnicode(64), key='_name',
+                                     nullable=False, unique=True, index=True))
         def upgrade_5_to_6(self):
-            self.alter_column(Column('name', UnicodeText, key='_name', nullable=False,
-                                     unique=True, index=True,
-                                     info={'ibid_mysql_index_length': 32}))
+            self.alter_column(Column('name', IbidUnicodeText(32), key='_name',
+                                     nullable=False, unique=True, index=True))
         def upgrade_6_to_7(self):
             self.add_column(Column('wild', Boolean, PassiveDefault('0'),
                                    nullable=False, index=True, default=False))
@@ -77,8 +77,15 @@
                     .all():
                 row.wild = True
                 self.upgrade_session.save_or_update(row)
+        def upgrade_7_to_8(self):
+            self.drop_index(self.table.c._name)
+            self.alter_column(Column('name',
+                                     IbidUnicodeText(32, case_insensitive=True),
+                                     key='_name', nullable=False, unique=True,
+                                     index=True), force_rebuild=True)
+            self.add_index(self.table.c._name)
 
-    __table__.versioned_schema = FactoidNameSchema(__table__, 7)
+    __table__.versioned_schema = FactoidNameSchema(__table__, 8)
 
     def __init__(self, name, identity_id, factoid_id=None, factpack=None):
         self.name = name
@@ -102,8 +109,9 @@
 class FactoidValue(Base):
     __table__ = Table('factoid_values', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('value', UnicodeText, nullable=False),
-    Column('factoid_id', Integer, ForeignKey('factoids.id'), nullable=False, index=True),
+    Column('value', IbidUnicodeText, nullable=False),
+    Column('factoid_id', Integer, ForeignKey('factoids.id'), nullable=False,
+           index=True),
     Column('identity_id', Integer, ForeignKey('identities.id'), index=True),
     Column('time', DateTime, nullable=False),
     Column('factpack', Integer, ForeignKey('factpacks.id'), index=True),
@@ -116,8 +124,11 @@
             self.add_index(self.table.c.factoid_id)
             self.add_index(self.table.c.identity_id)
             self.add_index(self.table.c.factpack)
+        def upgrade_3_to_4(self):
+            self.alter_column(Column('value', IbidUnicodeText, nullable=False),
+                              force_rebuild=True)
 
-    __table__.versioned_schema = FactoidValueSchema(__table__, 3)
+    __table__.versioned_schema = FactoidValueSchema(__table__, 4)
 
     def __init__(self, value, identity_id, factoid_id=None, factpack=None):
         self.value = value
@@ -141,7 +152,8 @@
 
     class FactoidSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_column(Column('factpack', Integer, ForeignKey('factpacks.id')))
+            self.add_column(Column('factpack', Integer,
+                                   ForeignKey('factpacks.id')))
         def upgrade_2_to_3(self):
             self.add_index(self.table.c.factpack)
 
@@ -157,14 +169,22 @@
 class Factpack(Base):
     __table__ = Table('factpacks', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('name', Unicode(64), nullable=False, unique=True, index=True),
+    Column('name', IbidUnicode(64, case_insensitive=True),
+           nullable=False, unique=True, index=True),
     useexisting=True)
 
     class FactpackSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_index(self.table.c.name, unique=True)
+            self.add_index(self.table.c.name)
+        def upgrade_2_to_3(self):
+            self.drop_index(self.table.c.name)
+            self.alter_column(Column('name',
+                                     IbidUnicode(64, case_insensitive=True),
+                                     nullable=False, unique=True, index=True),
+                              force_rebuild=True)
+            self.add_index(self.table.c.name)
 
-    __table__.versioned_schema = FactpackSchema(__table__, 2)
+    __table__.versioned_schema = FactpackSchema(__table__, 3)
 
     def __init__(self, name):
         self.name = name
@@ -202,8 +222,7 @@
             query = query.filter(':fact LIKE name ESCAPE :escape') \
                          .params(fact=name, escape='\\')
         else:
-            query = query.filter(func.lower(FactoidName.name) \
-                                 == escape_name(name).lower())
+            query = query.filter(FactoidName.name == escape_name(name))
         # For normal matches, restrict to the subset applicable
         if not literal:
             query = query.filter(FactoidName.wild == wild)
@@ -328,10 +347,10 @@
             return
 
         factoid = event.session.query(Factoid).join(Factoid.names) \
-                .filter(func.lower(FactoidName.name)==escape_name(source).lower()).first()
+                .filter(FactoidName.name==escape_name(source)).first()
         if factoid:
             target_factoid = event.session.query(FactoidName) \
-                    .filter(func.lower(FactoidName.name)==escape_name(target).lower()).first()
+                    .filter(FactoidName.name==escape_name(target)).first()
             if target_factoid:
                 event.addresponse(u"I already know stuff about %s", target)
                 return
@@ -510,7 +529,7 @@
             return
 
         factoid = event.session.query(Factoid).join(Factoid.names)\
-                .filter(func.lower(FactoidName.name)==escape_name(name).lower()).first()
+                .filter(FactoidName.name==escape_name(name)).first()
         if factoid:
             if correction:
                 identities = get_identities(event)

=== modified file 'ibid/plugins/feeds.py'
--- ibid/plugins/feeds.py	2009-12-28 13:34:30 +0000
+++ ibid/plugins/feeds.py	2009-12-29 16:00:32 +0000
@@ -3,15 +3,13 @@
 import logging
 from urlparse import urljoin
 
-from sqlalchemy import Column, Integer, Unicode, DateTime, UnicodeText, \
-        ForeignKey, Table
-from sqlalchemy.sql import func
 import feedparser
 from html2text import html2text_file
 
 from ibid.config import IntOption
+from ibid.db import IbidUnicode, IbidUnicodeText, Integer, DateTime, \
+                    Table, Column, ForeignKey, Base, VersionedSchema
 from ibid.plugins import Processor, match, authorise, run_every
-from ibid.models import Base, VersionedSchema
 from ibid.utils import cacheable_download, human_join
 from ibid.utils.html import get_html_parse_tree
 
@@ -22,25 +20,45 @@
 class Feed(Base):
     __table__ = Table('feeds', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('name', Unicode(32), unique=True, nullable=False, index=True),
-    Column('url', UnicodeText, nullable=False),
+    Column('name', IbidUnicode(32, case_insensitive=True),
+           unique=True, nullable=False, index=True),
+    Column('url', IbidUnicodeText, nullable=False),
     Column('identity_id', Integer, ForeignKey('identities.id'),
-        nullable=False, index=True),
+           nullable=False, index=True),
     Column('time', DateTime, nullable=False),
-    Column('source', Unicode(32), index=True),
-    Column('target', Unicode(32), index=True),
+    Column('source', IbidUnicode(32, case_insensitive=True), index=True),
+    Column('target', IbidUnicode(32, case_insensitive=True), index=True),
     useexisting=True)
 
     class FeedSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_index(self.table.c.name, unique=True)
+            self.add_index(self.table.c.name)
             self.add_index(self.table.c.identity_id)
         def upgrade_2_to_3(self):
-            from sqlalchemy import Column, Unicode
-            self.add_column(Column('source', Unicode(32), index=True))
-            self.add_column(Column('target', Unicode(32), index=True))
+            from ibid.db import IbidUnicode, Column
+            self.add_column(Column('source', IbidUnicode(32), index=True))
+            self.add_column(Column('target', IbidUnicode(32), index=True))
+        def upgrade_3_to_4(self):
+            self.drop_index(self.table.c.name)
+            self.drop_index(self.table.c.source)
+            self.drop_index(self.table.c.target)
+            self.alter_column(Column('name',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     unique=True, nullable=False, index=True),
+                              force_rebuild=True)
+            self.alter_column(Column('url', IbidUnicodeText, nullable=False),
+                              force_rebuild=True)
+            self.alter_column(Column('source',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     index=True), force_rebuild=True)
+            self.alter_column(Column('target',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     index=True), force_rebuild=True)
+            self.add_index(self.table.c.name)
+            self.add_index(self.table.c.source)
+            self.add_index(self.table.c.target)
 
-    __table__.versioned_schema = FeedSchema(__table__, 3)
+    __table__.versioned_schema = FeedSchema(__table__, 4)
 
     feed = None
     entries = None
@@ -82,8 +100,7 @@
     @match(r'^add\s+feed\s+(.+?)\s+as\s+(.+?)$')
     @authorise()
     def add(self, event, url, name):
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if feed:
             event.addresponse(u"I already have the %s feed", name)
@@ -134,8 +151,7 @@
     @match(r'^remove\s+(.+?)\s+feed$')
     @authorise()
     def remove(self, event, name):
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if not feed:
             event.addresponse(u"I don't have the %s feed anyway", name)
@@ -150,8 +166,7 @@
     @match(r'^(?:stop|don\'t)\s+poll(?:ing)?\s(.+)\s+feed$')
     @authorise()
     def no_poll(self, event, name):
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if not feed:
             event.addresponse(u"I don't have the %s feed anyway", name)
@@ -167,8 +182,7 @@
     @match(r'^poll\s(.+)\s+feed\s+(?:to|notify)\s+(.+)\s+on\s+(.+)$')
     @authorise(fallthrough=False)
     def enable_poll(self, event, name, target, source):
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if not feed:
             event.addresponse(u"I don't have the %s feed anyway", name)
@@ -194,8 +208,7 @@
         number = number and int(number) or 10
         start = start and int(start) or 0
 
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if not feed:
             event.addresponse(u"I don't know about the %s feed", name)
@@ -214,8 +227,7 @@
 
     @match(r'^article\s+(?:(\d+)|/(.+?)/)\s+from\s+(.+?)$')
     def article(self, event, number, pattern, name):
-        feed = event.session.query(Feed) \
-                .filter(func.lower(Feed.name) == name.lower()).first()
+        feed = event.session.query(Feed).filter_by(name=name).first()
 
         if not feed:
             event.addresponse(u"I don't know about the %s feed", name)

=== modified file 'ibid/plugins/identity.py'
--- ibid/plugins/identity.py	2009-10-16 16:31:34 +0000
+++ ibid/plugins/identity.py	2009-12-29 16:00:32 +0000
@@ -2,13 +2,10 @@
 from random import choice
 import logging
 
-from sqlalchemy.orm import eagerload
-from sqlalchemy.sql import func
-from sqlalchemy.exceptions import IntegrityError
-
 import ibid
+from ibid.db import eagerload, IntegrityError
+from ibid.db.models import Account, Identity, Attribute
 from ibid.plugins import Processor, match, auth_responses
-from ibid.models import Account, Identity, Attribute
 from ibid.utils import human_join
 
 help = {}
@@ -54,7 +51,7 @@
 
         if admin:
             identity = event.session.query(Identity) \
-                    .filter_by(identity=username, source=event.source.lower()).first()
+                    .filter_by(identity=username, source=event.source).first()
             if identity:
                 identity.account_id = account.id
                 event.session.save_or_update(identity)
@@ -158,15 +155,16 @@
             else:
                 account = event.session.query(Account) \
                         .join('identities') \
-                        .filter(func.lower(Identity.identity) == identity.lower()) \
-                        .filter(func.lower(Identity.source) == source.lower()).first()
+                        .filter(Identity.identity == identity) \
+                        .filter(Identity.source == source).first()
 
                 if account:
                     reverse_attach = True
                 else:
                     username = event.sender['id']
 
-                    account = event.session.query(Account).filter_by(username=username).first()
+                    account = event.session.query(Account) \
+                            .filter_by(username=username).first()
 
                     if account:
                         event.addresponse(u'I tried to create the account %s for you, but it already exists. '
@@ -176,7 +174,8 @@
                     account = Account(username)
                     event.session.save_or_update(account)
 
-                    currentidentity = event.session.query(Identity).get(event.identity)
+                    currentidentity = event.session.query(Identity) \
+                            .get(event.identity)
                     currentidentity.account_id = account.id
                     event.session.save_or_update(currentidentity)
 
@@ -194,7 +193,8 @@
             if not auth_responses(event, 'accounts'):
                 return
             admin = True
-            account = event.session.query(Account).filter_by(username=username).first()
+            account = event.session.query(Account) \
+                    .filter_by(username=username).first()
             if not account:
                 event.addresponse(u"I don't know who %s is", username)
                 return
@@ -203,8 +203,7 @@
             ident = event.session.query(Identity).get(event.identity)
         else:
             ident = event.session.query(Identity) \
-                    .filter(func.lower(Identity.identity) == identity.lower()) \
-                    .filter(func.lower(Identity.source) == source.lower()).first()
+                    .filter_by(identity=identity, source=source).first()
         if ident and ident.account:
             event.addresponse(u'This identity is already attached to account %s',
                     ident.account.username)
@@ -262,8 +261,7 @@
                 return
 
             identity = event.session.query(Identity) \
-                    .filter(func.lower(Identity.identity) == user.lower()) \
-                    .filter(func.lower(Identity.source) == source.lower()).first()
+                    .filter_by(identity=user, source=source).first()
             if not identity:
                 identity = Identity(source, user)
             identity.account_id = account_id
@@ -286,15 +284,15 @@
         else:
             if not auth_responses(event, 'accounts'):
                 return
-            account = event.session.query(Account).filter_by(username=username).first()
+            account = event.session.query(Account) \
+                    .filter_by(username=username).first()
             if not account:
                 event.addresponse(u"I don't know who %s is", username)
                 return
 
         identity = event.session.query(Identity) \
-                .filter_by(account_id=account.id) \
-                .filter(func.lower(Identity.identity) == user.lower()) \
-                .filter(func.lower(Identity.source) == source.lower()).first()
+                .filter_by(account_id=account.id, identity=user,
+                           source=source).first()
         if not identity:
             event.addresponse(u"I don't know about that identity")
         else:
@@ -328,7 +326,8 @@
         else:
             if not auth_responses(event, 'accounts'):
                 return
-            account = event.session.query(Account).filter_by(username=username).first()
+            account = event.session.query(Account) \
+                    .filter_by(username=username).first()
             if not account:
                 event.addresponse(u"I don't know who %s is", username)
                 return
@@ -381,8 +380,8 @@
 
             identity = event.session.query(Identity) \
                     .options(eagerload('account')) \
-                    .filter(func.lower(Identity.source) == event.source.lower()) \
-                    .filter(func.lower(Identity.identity) == event.sender['id'].lower()) \
+                    .filter_by(source=event.source,
+                               identity=event.sender['id']) \
                     .first()
             if not identity:
                 identity = Identity(event.source, event.sender['id'])
@@ -397,8 +396,8 @@
                     log.debug(u'Race encountered creating identity for %s on %s', event.sender['id'], event.source)
                     identity = event.session.query(Identity) \
                             .options(eagerload('account')) \
-                            .filter(func.lower(Identity.source) == event.source.lower()) \
-                            .filter(func.lower(Identity.identity) == event.sender['id'].lower()) \
+                            .filter_by(source=event.source,
+                                       identity=event.sender['id']) \
                             .one()
 
             event.identity = identity.id

=== modified file 'ibid/plugins/karma.py'
--- ibid/plugins/karma.py	2009-10-20 15:45:46 +0000
+++ ibid/plugins/karma.py	2009-12-29 16:00:32 +0000
@@ -2,12 +2,10 @@
 import re
 import logging
 
-from sqlalchemy import Column, Integer, Unicode, DateTime, Table
-from sqlalchemy.sql import func
-
+from ibid.config import BoolOption, IntOption, ListOption
+from ibid.db import IbidUnicode, DateTime, Integer, Table, Column, Base, \
+                    VersionedSchema
 from ibid.plugins import Processor, match, handler, authorise
-from ibid.config import BoolOption, IntOption, ListOption
-from ibid.models import Base, VersionedSchema
 
 help = {'karma': u'Keeps track of karma for people and things.'}
 
@@ -16,7 +14,8 @@
 class Karma(Base):
     __table__ = Table('karma', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('subject', Unicode(64), unique=True, nullable=False, index=True),
+    Column('subject', IbidUnicode(64, case_insensitive=True), unique=True,
+           nullable=False, index=True),
     Column('changes', Integer, nullable=False),
     Column('value', Integer, nullable=False),
     Column('time', DateTime, nullable=False),
@@ -24,11 +23,19 @@
 
     class KarmaSchema(VersionedSchema):
         def upgrade_1_to_2(self):
-            self.add_index(self.table.c.subject, unique=True)
+            self.add_index(self.table.c.subject)
         def upgrade_2_to_3(self):
-            self.alter_column(Column('subject', Unicode(64), unique=True, nullable=False, index=True))
+            self.alter_column(Column('subject', IbidUnicode(64), unique=True,
+                                     nullable=False, index=True))
+        def upgrade_3_to_4(self):
+            self.drop_index(self.table.c.subject)
+            self.alter_column(Column('subject',
+                                     IbidUnicode(64, case_insensitive=True),
+                                     unique=True, nullable=False, index=True),
+                              force_rebuild=True)
+            self.add_index(self.table.c.subject)
 
-    __table__.versioned_schema = KarmaSchema(__table__, 3)
+    __table__.versioned_schema = KarmaSchema(__table__, 4)
 
     def __init__(self, subject):
         self.subject = subject
@@ -45,16 +52,25 @@
 
     permission = u'karma'
 
-    increase = ListOption('increase', 'Suffixes which indicate increased karma', ('++', 'ftw'))
-    decrease = ListOption('decrease', 'Suffixes which indicate decreased karma', ('--', 'ftl'))
-    neutral = ListOption('neutral', 'Suffixes which indicate neutral karma', ('==',))
+    increase = ListOption('increase',
+                          'Suffixes which indicate increased karma',
+                          ('++', 'ftw'))
+    decrease = ListOption('decrease', 'Suffixes which indicate decreased karma',
+                          ('--', 'ftl'))
+    neutral = ListOption('neutral', 'Suffixes which indicate neutral karma',
+                         ('==',))
     reply = BoolOption('reply', 'Acknowledge karma changes', False)
     public = BoolOption('public', 'Only allow karma changes in public', True)
     ignore = ListOption('ignore', 'Karma subjects to silently ignore', ())
-    importance = IntOption('importance', "Threshold for number of changes after which a karma won't be forgotten", 0)
+    importance = IntOption('importance', 'Threshold for number of changes after'
+                           " which a karma won't be forgotten", 0)
 
     def setup(self):
-        self.set.im_func.pattern = re.compile(r'^(.+?)\s*(%s)\s*(?:[[{(]+\s*(.+?)\s*[\]})]+)?$' % '|'.join([re.escape(token) for token in self.increase + self.decrease + self.neutral]), re.I)
+        self.set.im_func.pattern = re.compile(
+                r'^(.+?)\s*(%s)\s*(?:[[{(]+\s*(.+?)\s*[\]})]+)?$' % '|'.join(
+                    re.escape(token) for token
+                    in self.increase + self.decrease + self.neutral
+                ), re.I)
 
     @handler
     @authorise(fallthrough=False)
@@ -66,8 +82,7 @@
         if subject.lower() in self.ignore:
             return
 
-        karma = event.session.query(Karma) \
-                .filter(func.lower(Karma.subject) == subject.lower()).first()
+        karma = event.session.query(Karma).filter_by(subject=subject).first()
         if not karma:
             karma = Karma(subject)
 
@@ -109,8 +124,7 @@
 
     @match(r'^karma\s+(?:for\s+)?(.+)$')
     def handle_karma(self, event, subject):
-        karma = event.session.query(Karma) \
-                .filter(func.lower(Karma.subject) == subject.lower()).first()
+        karma = event.session.query(Karma).filter_by(subject=subject).first()
         if not karma:
             event.addresponse(u'nobody cares, dude')
         elif karma.value == 0:
@@ -143,8 +157,7 @@
     @match(r'^forget\s+karma\s+for\s+(.+?)(?:\s*[[{(]+\s*(.+?)\s*[\]})]+)?$')
     @authorise(fallthrough=False)
     def forget(self, event, subject, reason):
-        karma = event.session.query(Karma) \
-                .filter(func.lower(Karma.subject) == subject.lower()).first()
+        karma = event.session.query(Karma).filter_by(subject=subject).first()
         if not karma:
             karma = Karma(subject)
             event.addresponse(u"I was pretty ambivalent about %s, anyway", subject)

=== modified file 'ibid/plugins/memo.py'
--- ibid/plugins/memo.py	2009-12-05 18:26:45 +0000
+++ ibid/plugins/memo.py	2009-12-29 16:00:32 +0000
@@ -1,16 +1,15 @@
 from datetime import datetime
 import logging
 
-from sqlalchemy import Column, Integer, DateTime, ForeignKey, Boolean, UnicodeText, Table
-from sqlalchemy.orm import relation
-from sqlalchemy.sql import func
-
 import ibid
 from ibid.plugins import Processor, handler, match, authorise
+from ibid.compat import any
 from ibid.config import IntOption
+from ibid.db import IbidUnicodeText, Boolean, Integer, DateTime, \
+                    Table, Column, ForeignKey, relation, Base, VersionedSchema
+from ibid.db.models import Identity, Account
 from ibid.auth import permission
 from ibid.plugins.identity import get_identities
-from ibid.models import Base, VersionedSchema, Identity, Account
 from ibid.utils import ago, format_date
 
 help = {'memo': u'Keeps messages for people.'}
@@ -23,9 +22,11 @@
 class Memo(Base):
     __table__ = Table('memos', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('from_id', Integer, ForeignKey('identities.id'), nullable=False, index=True),
-    Column('to_id', Integer, ForeignKey('identities.id'), nullable=False, index=True),
-    Column('memo', UnicodeText, nullable=False),
+    Column('from_id', Integer, ForeignKey('identities.id'),
+           nullable=False, index=True),
+    Column('to_id', Integer, ForeignKey('identities.id'),
+           nullable=False, index=True),
+    Column('memo', IbidUnicodeText, nullable=False),
     Column('private', Boolean, nullable=False),
     Column('delivered', Boolean, nullable=False, index=True),
     Column('time', DateTime, nullable=False),
@@ -36,8 +37,11 @@
             self.add_index(self.table.c.from_id)
             self.add_index(self.table.c.to_id)
             self.add_index(self.table.c.delivered)
+        def upgrade_2_to_3(self):
+            self.alter_column(Column('memo', IbidUnicodeText, nullable=False),
+                              force_rebuild=True)
 
-    __table__.versioned_schema = MemoSchema(__table__, 2)
+    __table__.versioned_schema = MemoSchema(__table__, 3)
 
     def __init__(self, from_id, to_id, memo, private=False):
         self.from_id = from_id
@@ -47,8 +51,10 @@
         self.delivered = False
         self.time = datetime.utcnow()
 
-Identity.memos_sent = relation(Memo, primaryjoin=Identity.id==Memo.from_id, backref='sender')
-Identity.memos_recvd = relation(Memo, primaryjoin=Identity.id==Memo.to_id, backref='recipient')
+Identity.memos_sent = relation(Memo, primaryjoin=Identity.id==Memo.from_id,
+                               backref='sender')
+Identity.memos_recvd = relation(Memo, primaryjoin=Identity.id==Memo.to_id,
+                                backref='recipient')
 
 class Tell(Processor):
     u"""(tell|pm|privmsg|msg|ask) <person> [on <source>] <message>
@@ -58,8 +64,8 @@
     permission = u'sendmemo'
     permissions = (u'recvmemo',)
 
-    @match(r'^\s*(?:please\s+)?(tell|pm|privmsg|msg|ask)\s+(\S+)\s+(?:on\s+(\S+)\s+)?(.+?)\s*$',
-            version='deaddressed')
+    @match(r'^\s*(?:please\s+)?(tell|pm|privmsg|msg|ask)'
+           r'\s+(\S+)\s+(?:on\s+(\S+)\s+)?(.+?)\s*$', version='deaddressed')
     @authorise(fallthrough=False)
     def tell(self, event, how, who, source, memo):
         source_specified = bool(source)
@@ -69,17 +75,17 @@
             source = source.lower()
 
         if source.lower() == event.source and \
-                [True for name in ibid.config.plugins['core']['names'] if name.lower() == who.lower()]:
+                any(True for name in ibid.config.plugins['core']['names']
+                    if name.lower() == who.lower()):
             event.addresponse(u"I can't deliver messages to myself")
             return
 
         to = event.session.query(Identity) \
-                .filter(func.lower(Identity.identity) == who.lower()) \
-                .filter_by(source=source).first()
+                .filter_by(identity=who, source=source).first()
 
         if not to and not source_specified:
             account = event.session.query(Account) \
-                    .filter(func.lower(Account.username) == who.lower()).first()
+                    .filter_by(username=who).first()
             if account:
                 for identity in account.identities:
                     if identity.source == source:
@@ -90,7 +96,8 @@
         if not to and not source_specified:
             event.addresponse(
                     u"I don't know who %(who)s is. "
-                    u"Say '%(who)s on %(source)s' and I'll take your word that %(who)s exists", {
+                    u"Say '%(who)s on %(source)s' and I'll take your word "
+                    u'that %(who)s exists', {
                     'who': who,
                     'source': source,
             })
@@ -104,20 +111,24 @@
             event.session.save(to)
             event.session.commit()
 
-            log.info(u"Created identity %s for %s on %s", to.id, to.identity, to.source)
+            log.info(u"Created identity %s for %s on %s", to.id, to.identity,
+                     to.source)
 
-        if permission(u'recvmemo', to.account and to.account.id or None, to.source, event.session) != 'yes':
+        if permission(u'recvmemo', to.account and to.account.id or None,
+                      to.source, event.session) != 'yes':
             event.addresponse(u'Just tell %s yourself', who)
             return
 
         memo = u' '.join((how, who, memo))
 
-        memo = Memo(event.identity, to.id, memo, how.lower() in (u'pm', u'privmsg', u'msg'))
+        memo = Memo(event.identity, to.id, memo,
+                    how.lower() in (u'pm', u'privmsg', u'msg'))
         event.session.save_or_update(memo)
 
         event.session.commit()
         log.info(u"Stored memo %s for %s (%s) from %s (%s): %s",
-                memo.id, to.id, who, event.identity, event.sender['connection'], memo.memo)
+                memo.id, to.id, who, event.identity, event.sender['connection'],
+                memo.memo)
         event.memo = memo.id
         nomemos_cache.clear()
         notified_overlimit_cache.discard(to.id)
@@ -151,22 +162,20 @@
 
         # Join on column x isn't possible in SQLAlchemy 0.4:
         identities_to = event.session.query(Identity) \
-                .filter_by(source=source) \
-                .filter(func.lower(Identity.identity) == who.lower()) \
-                .all()
+                .filter_by(source=source, identity=who).all()
 
         identities_to = [identity.id for identity in identities_to]
 
         memos = event.session.query(Memo) \
-                .filter_by(delivered=False) \
-                .filter_by(from_id=event.identity) \
+                .filter_by(delivered=False, from_id=event.identity) \
                 .filter(Memo.to_id.in_(identities_to)) \
                 .order_by(Memo.time.asc())
         count = memos.count()
 
         if not count:
             event.addresponse(
-                    u"You don't have any outstanding messages for %(who)s on %(source)s", {
+                    u"You don't have any outstanding messages for %(who)s on "
+                    u'%(source)s', {
                         'who': who,
                         'source': source,
                 })
@@ -174,7 +183,8 @@
 
         if abs(number) > count:
             event.addresponse(
-                    u"That memo does not exist, you only have %(count)i outstanding memos for %(who)s on %(source)s", {
+                    u'That memo does not exist, you only have %(count)i '
+                    u'outstanding memos for %(who)s on %(source)s', {
                         'count': count,
                         'who': who,
                         'source': source,
@@ -188,12 +198,14 @@
 
         event.session.delete(memo)
         event.session.commit()
-        log.info(u"Cancelled memo %s for %s (%s) from %s (%s): %s",
-                memo.id, memo.to_id, who, event.identity, event.sender['connection'], memo.memo)
+        log.info(u'Cancelled memo %s for %s (%s) from %s (%s): %s',
+                 memo.id, memo.to_id, who, event.identity,
+                 event.sender['connection'], memo.memo)
 
         if count > 1:
             event.addresponse(
-                    u"Forgotten memo %(number)i for %(who)s on %(source)s, but you still have %(count)i pending", {
+                    u'Forgotten memo %(number)i for %(who)s on %(source)s, '
+                    u'but you still have %(count)i pending', {
                         'number': number,
                         'who': who,
                         'source': source,
@@ -215,7 +227,8 @@
     addressed = False
     processed = True
 
-    public_limit = IntOption('public_limit', 'Maximum number of memos to read out in public (flood-protection)', 2)
+    public_limit = IntOption('public_limit', 'Maximum number of memos to read '
+                             'out in public (flood-protection)', 2)
 
     @handler
     def deliver(self, event):
@@ -227,11 +240,13 @@
         if len(memos) > self.public_limit and event.public:
             if event.identity not in notified_overlimit_cache:
                 public = [True for memo in memos if not memo.private]
-                message = u'By the way, you have a pile of memos waiting for you, too many to read out in public. PM me'
+                message = u'By the way, you have a pile of memos waiting for ' \
+                          u'you, too many to read out in public. PM me'
                 if public:
                     event.addresponse(u'%s: ' + message, event.sender['nick'])
                 else:
-                    event.addresponse(message, target=event.sender['connection'])
+                    event.addresponse(message,
+                                      target=event.sender['connection'])
                 notified_overlimit_cache.add(event.identity)
             return
 
@@ -241,7 +256,8 @@
                 continue
 
             if memo.private:
-                message = u'By the way, %(sender)s on %(source)s told me "%(message)s" %(ago)s ago' % {
+                message = u'By the way, %(sender)s on %(source)s told me ' \
+                          u'"%(message)s" %(ago)s ago' % {
                     'sender': memo.sender.identity,
                     'source': memo.sender.source,
                     'message': memo.memo,
@@ -249,7 +265,8 @@
                 }
                 event.addresponse(message, target=event.sender['connection'])
             else:
-                event.addresponse(u'By the way, %(sender)s on %(source)s told me "%(message)s" %(ago)s ago', {
+                event.addresponse(u'By the way, %(sender)s on %(source)s '
+                                  u'told me "%(message)s" %(ago)s ago', {
                     'sender': memo.sender.identity,
                     'source': memo.sender.source,
                     'message': memo.memo,
@@ -272,7 +289,8 @@
     addressed = False
     processed = True
 
-    public_limit = IntOption('public_limit', 'Maximum number of memos to read out in public (flood-protection)', 2)
+    public_limit = IntOption('public_limit', 'Maximum number of memos to read '
+                             'out in public (flood-protection)', 2)
 
     @handler
     def state(self, event):
@@ -326,9 +344,7 @@
 
         # Join on column x isn't possible in SQLAlchemy 0.4:
         identities_to = event.session.query(Identity) \
-                .filter_by(source=source) \
-                .filter(func.lower(Identity.identity) == who.lower()) \
-                .all()
+                .filter_by(source=source, identity=who).all()
 
         identities_to = [identity.id for identity in identities_to]
 
@@ -344,7 +360,8 @@
                 for i, memo in enumerate(memos)
             ))
         else:
-            event.addresponse(u"Sorry, all your memos to %(who)s on %(source)s are already delivered", {
+            event.addresponse(u'Sorry, all your memos to %(who)s on %(source)s '
+                              u'are already delivered', {
                 'who': who,
                 'source': source,
             })
@@ -360,7 +377,8 @@
 
         memo = memos[number]
 
-        event.addresponse(u"From %(sender)s on %(source)s at %(time)s: %(message)s", {
+        event.addresponse(u'From %(sender)s on %(source)s at %(time)s: '
+                          u'%(message)s', {
             'sender': memo.sender.identity,
             'source': memo.sender.source,
             'time': format_date(memo.time),

=== modified file 'ibid/plugins/seen.py'
--- ibid/plugins/seen.py	2009-10-16 16:31:34 +0000
+++ ibid/plugins/seen.py	2009-12-29 16:00:32 +0000
@@ -1,13 +1,11 @@
 from datetime import datetime
 import logging
 
-from sqlalchemy import Column, Integer, Unicode, DateTime, ForeignKey, UnicodeText, UniqueConstraint, Table
-from sqlalchemy.orm import relation
-from sqlalchemy.sql import func
-from sqlalchemy.exceptions import IntegrityError
-
+from ibid.db import IbidUnicode, IbidUnicodeText, Integer, DateTime, \
+                    Table, Column, ForeignKey, UniqueConstraint, \
+                    relation, IntegrityError, Base, VersionedSchema
+from ibid.db.models import Identity, Account
 from ibid.plugins import Processor, match
-from ibid.models import Base, VersionedSchema, Identity, Account
 from ibid.utils import ago, format_date
 
 log = logging.getLogger('plugins.seen')
@@ -17,10 +15,11 @@
 class Sighting(Base):
     __table__ = Table('seen', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('identity_id', Integer, ForeignKey('identities.id'), nullable=False, index=True),
-    Column('type', Unicode(8), nullable=False, index=True),
-    Column('channel', Unicode(32)),
-    Column('value', UnicodeText),
+    Column('identity_id', Integer, ForeignKey('identities.id'), nullable=False,
+           index=True),
+    Column('type', IbidUnicode(8), nullable=False, index=True),
+    Column('channel', IbidUnicode(32)),
+    Column('value', IbidUnicodeText),
     Column('time', DateTime, nullable=False),
     Column('count', Integer, nullable=False),
     UniqueConstraint('identity_id', 'type'),
@@ -30,12 +29,22 @@
         def upgrade_1_to_2(self):
             self.add_index(self.table.c.identity_id)
             self.add_index(self.table.c.type)
+        def upgrade_2_to_3(self):
+            self.drop_index(self.table.c.type)
+            self.alter_column(Column('type', IbidUnicode(8), nullable=False,
+                                     index=True), force_rebuild=True)
+            self.alter_column(Column('channel', IbidUnicode(32)),
+                              force_rebuild=True)
+            self.alter_column(Column('value', IbidUnicodeText),
+                              force_rebuild=True)
+            self.add_index(self.table.c.type)
 
-    __table__.versioned_schema = SightingSchema(__table__, 2)
+    __table__.versioned_schema = SightingSchema(__table__, 3)
 
     identity = relation('Identity')
 
-    def __init__(self, identity_id=None, type='message', channel=None, value=None):
+    def __init__(self, identity_id=None, type='message', channel=None,
+                 value=None):
         self.identity_id = identity_id
         self.type = type
         self.channel = channel
@@ -44,7 +53,8 @@
         self.count = 0
 
     def __repr__(self):
-        return u'<Sighting %s %s in %s at %s: %s>' % (self.type, self.identity_id, self.channel, self.time, self.value)
+        return u'<Sighting %s %s in %s at %s: %s>' % (
+               self.type, self.identity_id, self.channel, self.time, self.value)
 
 class See(Processor):
     feature = 'seen'
@@ -56,8 +66,7 @@
             return
 
         sighting = event.session.query(Sighting) \
-                .filter_by(identity_id=event.identity) \
-                .filter_by(type=event.type).first()
+                .filter_by(identity_id=event.identity, type=event.type).first()
         if not sighting:
             sighting = Sighting(event.identity, event.type)
 
@@ -89,13 +98,14 @@
 
         account = None
         identity = event.session.query(Identity) \
-                .filter(func.lower(Identity.source) == (source and source or event.source).lower()) \
-                .filter(func.lower(Identity.identity) == who.lower()).first()
+                .filter_by(source=(source or event.source), identity=who) \
+                .first()
         if identity and identity.account and not source:
             account = identity.account
 
         if not identity and not source:
-            account = event.session.query(Account).filter_by(username=who).first()
+            account = event.session.query(Account).filter_by(username=who) \
+                    .first()
 
         if not identity and not account:
             event.addresponse(u"I don't know who %s is", who)

=== modified file 'ibid/plugins/url.py'
--- ibid/plugins/url.py	2009-12-28 13:34:30 +0000
+++ ibid/plugins/url.py	2009-12-29 16:00:32 +0000
@@ -7,12 +7,12 @@
 import re
 
 from pkg_resources import resource_exists, resource_stream
-from sqlalchemy import Column, Integer, Unicode, DateTime, UnicodeText, ForeignKey, Table
 
 import ibid
 from ibid.plugins import Processor, match, handler
 from ibid.config import Option, ListOption
-from ibid.models import Base, VersionedSchema
+from ibid.db import IbidUnicode, IbidUnicodeText, Integer, DateTime, \
+                    Table, Column, ForeignKey, Base, VersionedSchema
 from ibid.utils.html import get_html_parse_tree
 
 help = {'url': u'Captures URLs seen in channel to database and/or to delicious, and shortens and lengthens URLs'}
@@ -22,17 +22,23 @@
 class URL(Base):
     __table__ = Table('urls', Base.metadata,
     Column('id', Integer, primary_key=True),
-    Column('url', UnicodeText, nullable=False),
-    Column('channel', Unicode(32), nullable=False),
-    Column('identity_id', Integer, ForeignKey('identities.id'), nullable=False, index=True),
+    Column('url', IbidUnicodeText, nullable=False),
+    Column('channel', IbidUnicode(32, case_insensitive=True), nullable=False),
+    Column('identity_id', Integer, ForeignKey('identities.id'),
+           nullable=False, index=True),
     Column('time', DateTime, nullable=False),
     useexisting=True)
 
     class URLSchema(VersionedSchema):
         def upgrade_1_to_2(self):
             self.add_index(self.table.c.identity_id)
+        def upgrade_2_to_3(self):
+            self.alter_column(Column('url', IbidUnicodeText, nullable=False))
+            self.alter_column(Column('channel',
+                                     IbidUnicode(32, case_insensitive=True),
+                                     nullable=False), force_rebuild=True)
 
-    __table__.versioned_schema = URLSchema(__table__, 2)
+    __table__.versioned_schema = URLSchema(__table__, 3)
 
     def __init__(self, url, channel, identity_id):
         self.url = url

=== modified file 'ibid/source/irc.py'
--- ibid/source/irc.py	2009-10-31 14:28:20 +0000
+++ ibid/source/irc.py	2009-12-29 16:00:32 +0000
@@ -10,7 +10,7 @@
 
 import ibid
 from ibid.config import Option, IntOption, BoolOption, FloatOption, ListOption
-from ibid.models import Credential
+from ibid.db.models import Credential
 from ibid.source import IbidSourceFactory
 from ibid.event import Event
 from ibid.utils import ibid_version
@@ -261,8 +261,10 @@
 
     def auth_hostmask(self, event, credential = None):
         for credential in event.session.query(Credential) \
-                .filter_by(method=u'hostmask').filter_by(account_id=event.account) \
-                .filter(or_(Credential.source == event.source, Credential.source == None)).all():
+                .filter_by(method=u'hostmask', account_id=event.account) \
+                .filter(or_(Credential.source == event.source,
+                            Credential.source == None)) \
+                .all():
             if fnmatch(event.sender['connection'], credential.credential):
                 return True
 

=== modified file 'scripts/ibid-db'
--- scripts/ibid-db	2009-12-10 09:40:08 +0000
+++ scripts/ibid-db	2009-12-29 16:00:33 +0000
@@ -7,14 +7,15 @@
 from os.path import exists
 from sys import stdin, stdout, stderr, exit
 
-from sqlalchemy import select, DateTime, Unicode, UnicodeText
+from sqlalchemy import select, DateTime
 from twisted.python.modules import getModule
 
 import ibid
 from ibid.compat import json
 from ibid.config import FileConfig
 from ibid.core import DatabaseManager
-from ibid.models import metadata, upgrade_schemas
+from ibid.db import metadata, upgrade_schemas
+from ibid.db.types import IbidUnicode, IbidUnicodeText
 from ibid.utils import ibid_version
 
 parser = OptionParser(usage='%prog [options...]', description=
@@ -163,7 +164,8 @@
                 if isinstance(dbtable.c[field].type, DateTime):
                     row[field] = datetime.strptime(row[field],
                             '%Y-%m-%dT%H:%M:%SZ')
-                elif isinstance(dbtable.c[field].type, (Unicode, UnicodeText)):
+                elif isinstance(dbtable.c[field].type,
+                                (IbidUnicode, IbidUnicodeText)):
                     row[field] = unicode(row[field])
             sql = dbtable.insert().values(**row)
             db.execute(sql)

=== modified file 'scripts/ibid-factpack'
--- scripts/ibid-factpack	2009-12-12 12:36:34 +0000
+++ scripts/ibid-factpack	2009-12-29 16:00:33 +0000
@@ -1,11 +1,9 @@
 #!/usr/bin/env python
 
-from sys import argv, exit, stderr
+from sys import exit, stderr
 from os.path import basename, exists
 from optparse import OptionParser
 
-from sqlalchemy.sql import func
-
 import ibid
 from ibid.compat import json
 from ibid.config import FileConfig
@@ -69,7 +67,7 @@
     print >> stderr, u"Invalid factpack"
     exit(4)
 
-name = basename(filename).replace('.json', '')
+name = unicode(basename(filename).replace('.json', ''))
 factpack = session.query(Factpack).filter_by(name=name).first()
 if factpack:
     print >> stderr, u'Factpack is already imported'
@@ -77,18 +75,17 @@
 
 factpack = Factpack(name)
 session.save(factpack)
+session.flush()
 
 existing = []
 for names, values in facts:
     factoid = Factoid(factpack.id)
     for name in names:
-        if session.query(FactoidName) \
-                  .filter(func.lower(FactoidName.name)
-                          == escape_name(name).lower()) \
-                  .first():
+        name = unicode(name)
+        if session.query(FactoidName).filter_by(name=escape_name(name)).first():
             existing.append(name)
             continue
-        fname = FactoidName(unicode(name), None, factpack=factpack.id)
+        fname = FactoidName(name, None, factpack=factpack.id)
         factoid.names.append(fname)
     for value in values:
         fvalue = FactoidValue(unicode(value), None, factpack=factpack.id)

=== modified file 'scripts/ibid-plugin'
--- scripts/ibid-plugin	2009-12-21 12:43:58 +0000
+++ scripts/ibid-plugin	2009-12-29 16:00:33 +0000
@@ -17,8 +17,8 @@
 import ibid
 import ibid.plugins
 from ibid.config import FileConfig
+from ibid.db.models import Identity
 from ibid.event import Event
-from ibid.models import Identity
 
 parser = OptionParser(usage="""%prog [options...] [plugins...]
 plugins is the list of plugins to load. A plugin name followed by a - will be disabled rather than loaded.""")

=== modified file 'scripts/ibid-setup'
--- scripts/ibid-setup	2009-12-13 13:21:53 +0000
+++ scripts/ibid-setup	2009-12-29 16:00:33 +0000
@@ -13,10 +13,11 @@
 from twisted.python.modules import getModule
 
 import ibid
-from ibid.plugins.auth import hash
 from ibid.config import FileConfig
 from ibid.core import DatabaseManager
-from ibid.models import Account, Identity, Permission, Credential, metadata, upgrade_schemas
+from ibid.db import metadata, upgrade_schemas
+from ibid.db.models import Account, Identity, Permission, Credential
+from ibid.plugins.auth import hash
 
 logging.basicConfig(level=logging.DEBUG)
 

=== modified file 'scripts/ibid_import'
--- scripts/ibid_import	2009-12-11 08:29:47 +0000
+++ scripts/ibid_import	2009-12-29 16:00:33 +0000
@@ -4,14 +4,12 @@
 
 from chardet import detect
 from dateutil.tz import tzlocal, tzutc
-from sqlalchemy import create_engine, Column, Integer, String, DateTime, \
-                       or_, ForeignKey, Boolean, Text
+from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker
-from sqlalchemy.sql import func
 
 from ibid.config import FileConfig
-from ibid.models import Identity
+from ibid.db.models import Identity
 from ibid.plugins.seen import Sighting
 from ibid.plugins.factoid import Factoid, FactoidName, FactoidValue
 from ibid.plugins.karma import Karma
@@ -116,8 +114,7 @@
     user = decode(user)
 
     identity = session.query(Identity) \
-            .filter(func.lower(Identity.identity) == user.lower()) \
-            .filter(func.lower(Identity.source) == source.lower()).first()
+            .filter_by(identity=user, source=source).first()
     if not identity:
         identity = Identity(source, user)
         identity.created = created
@@ -153,9 +150,7 @@
             stdout.flush()
 
         fname = ibid.query(FactoidName) \
-                    .filter(func.lower(FactoidName.name)
-                            == kfactoid.decoded_fact.lower()) \
-                    .first()
+                    .filter_by(name=kfactoid.decoded_fact).first()
         if not fname:
             factoid = Factoid()
             try: