Need to give unique names to indexes in sqlite3

Rails
1

I’m trying to use sqlite3 as my testing engine for cruisecontrol (I’m
using Geoffrey Grossenbach’s memory-test-fix plugin for this). When CC
tries to build the schema in sqlite3, this occurs:

[snip]
– add_index(“events”, [“bookmark_id”], {:name=>“bookmark_id”})
-> 0.0005s
[other stuff snipped]

– add_index(“flags”, [“bookmark_id”], {:name=>“bookmark_id”})
/usr/bin/ruby -Ilib:test “/usr/lib/ruby/gems/1.8/gems/rake-0.7.3/lib/
rake/rake_test_loader.rb” SQLite3::SQLException: index bookmark_id
already exists: CREATE INDEX “bookmark_id” ON flags (“bookmark_id”)

Based on this error, it seems to me that the namespace for indexes in
sqlite3 is database-wide, so when i make a bookmark_id index for
events, i can’t make the same index named “bookmark_id” on the flags
table.

So I thought I’d give an explicit name to each index, hoping that the
sqlite3-ruby gem would honor it. I changed my migrations to say this:

execute "alter table events add index bookmark_id_idx_events

(bookmark_id)"
execute “alter table flags add index bookmark_id_idx_flags
(bookmark_id)”

…and it looks like the schema.rb picks up the change:

add_index “events”, [“bookmark_id”], :name =>
“bookmark_id_idx_events”
add_index “flags”, [“bookmark_id”], :name => “bookmark_id_idx_flags”

…but when sqlite3 runs the schema.rb to build the db, it is ignoring
the :name attribute, tries to make a second bookmark_id index, and
keeps giving the same error.

My questions are:

  1. does the sqlite3-ruby gem support named indexes?
  2. If so, what am I doing wrong?

All advice is greatly appreciated!

Thanks,
-Jason