[web2py] Create indices from DAL

[Michael Toomim]

Here's an improved way to create indices in the DAL. Works only with
postgresql and sqlite.

def create_indices(*fields):
    '''
    Creates a set of indices if they do not exist
    Use like:
       create_indices(db.posts.created_at,
                      db.users.first_name,
                      etc...)
    '''
    for field in fields:

        table = field.tablename
        column = field.name
        db = field.db

        if db._uri.startswith('sqlite:'):
            db.executesql('create index if not exists %s_%s_index on
%s (%s);'
                      % (table, column, table, column))
        elif db._uri.startswith('postgres:'):
            # Our indexes end with "_index", but web2py autogenerates
            # one named "_key" for fields created with unique=True.
            # So let's check to see if one exists of either form.
            index_exists = \
                db.executesql("select count(*) from pg_class where
relname='%s_%s_index' or relname='%s_%s_key';"
                              % (table, column, table, column))[0][0]
== 1

            if not index_exists:
                db.executesql('create index %s_%s_index on %s (%s);'
                              % (table, column, table, column))
    db.commit()

This improves on this one I posted a while back:
  
http://groups.google.com/group/web2py/browse_thread/thread/8f6179915a6df8ee/cb58f509ae0a478d?lnk=gst&q=create+index#cb58f509ae0a478d