Oracle materialized views and error running Rails unit/functional tests

Hi,

I've run into an error when using materialized views in Oracle 9i and running my unit/functional tests. I'm using Rails 1.1.6 and the ruby- oci8 driver.

Our schema is stored in a series of sql scripts (not in the migration files). The process I use for loading the test and development dbs is to run the sql scripts to create the schema and load the default data. We recently converted two views to materialized views. Now when I use rake to run the unit or functional tests, those materialized views end up in schema.rb as create_table commands:

  create_table "person", :id => false, :force => true do |t|     t.column "dataset_id", :float     t.column "person_id", :float, :null => false     t.column "person_id2", :string, :limit => 200     t.column "given_name", :string, :limit => 4000     t.column "family_name", :string, :limit => 4000     t.column "person_privilege_count", :float     t.column "person_uprivilege_count", :float     t.column "person_itemset_count", :float   end

  add_index "person", ["person_id"], :name => "ie_person1"

  create_table "privilege", :id => false, :force => true do |t|     t.column "dataset_id", :float     t.column "privilege_id", :float, :null => false     t.column "privilege_id2", :string, :limit => 200     t.column "privilege_name", :string, :limit => 4000     t.column "privilege_description", :string, :limit => nil     t.column "privilege_qualifier", :string, :limit => nil     t.column "privilege_member_count", :float   end

  add_index "privilege", ["privilege_id"], :name => "ie_privilege1"

This is bad in two ways, they're not true tables, and both the unit and functional tests fail with

rake aborted! OCIError: ORA-00955: name is already used by an existing object: CREATE TABLE person (dataset_id NUMBER, person_id NUMBER NOT NULL, person_id2 VARCHAR2(200), given_name VARCHAR2(4000), family_name VAR CHAR2(4000), person_privilege_count NUMBER, person_uprivilege_count NUMBER, person_itemset_count NUMBER) c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ connection_adapters/abstract_adapter.rb:120:in `log' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ connection_adapters/oracle_adapter.rb:271:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ connection_adapters/abstract/schema_statements.rb:104:in `create_table' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ connection_adapters/oracle_adapter.rb:392:in `create_table' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ migration.rb:273:in `method_missing' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ migration.rb:257:in `say_with_time' c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ migration.rb:257:in `say_with_time' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ migration.rb:271:in `method_missing' ./db/schema.rb:193 c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/ schema.rb:43:in `define' ./db/schema.rb:5 c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/ dependencies.rb:140:in `load' c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.6/lib/tasks/databases.rake:31 c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:357:in `invoke' c:/ruby/lib/ruby/1.8/thread.rb:135:in `synchronize' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:350:in `invoke' c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.6/lib/tasks/databases.rake:72 c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:357:in `invoke' c:/ruby/lib/ruby/1.8/thread.rb:135:in `synchronize' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:350:in `invoke' c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.6/lib/tasks/databases.rake: 139 c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:357:in `invoke' c:/ruby/lib/ruby/1.8/thread.rb:135:in `synchronize' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:350:in `invoke' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:364:in `invoke_prerequisites' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:999:in `each' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:363:in `invoke_prerequisites' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:356:in `invoke' c:/ruby/lib/ruby/1.8/thread.rb:135:in `synchronize' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:350:in `invoke' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:1906:in `run' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:1906:in `run' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.1/bin/rake:7 c:/ruby/bin/rake.bat:25

I'm assuming this is happening because the materialized views aren't dropped before the new tables are created.

Is there any way to fix this? A configuration setting?

Thanks, Henry