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
