David B. Harrison

Back to seeding databases. My previous database seeding post addressed seeing with SQLite. This time, I’m using Oracle 11g as the back-end database, with the Oracle-Enhanced ActivieRecord adapter.

Someone pointed a Rails plugin, yaml_db, for Dumping database schemas & data, as well as loading those schemas & data into other databases. This makes it very easy to dump the database definition & data to a flat-file, and then load that into other databases. This makes it much easier to push seed and development data into other copies of a database. It also makes it trivially easy to re-image a database during development.

yaml_db makes it super easy to dump a copy of the current database with the command

rake db:dump

At some point, you’ll need to put the data dump into svn.

svn add db/data.yml
svn commit db/data.yml -m "initial data seed commit"

I like to automate subsequent dumps with the series of commands, on the source schema and Ruby on Rails instance.

rake db:dump
svn commit db/data.yml -m "Updated database seeds"

Then on the destination instances, the schema and data can be loaded into the destination database with the following series of commands

svn update
rake db:load

However…. it appears that reset_pk_sequence doesn’t work with yaml_db and Oracle-Enhanced ActiveRecord. Upon initial glance, it looks like Oracle-Enhanced doesn’t define a method for resetting the sequences. Well… sequences have to get updated anyway…. Otherwise, future inserts via CRUD could fail, based on incorrect sequence numbers and possible duplicate IDs for the primary key.

Here is a way to do it…. I’ve put the following into my seeds.db file.

require 'find'

@@oracleFlag = ActiveRecord::Base.connection.adapter_name=="OracleEnhanced"

def resequence(obj)
    puts "  #{obj} Resequencing"
    if obj.methods.include?(:maximum) && obj.column_names.include?('id')
      puts "  #{obj}, record count #{obj.count}, maximum(:id) #{obj.maximum(:id)}"
      max_id = obj.maximum(:id)
      next_id = max_id.nil? ? 10000 : max_id+1

      seq = ""
      if @@oracleFlag
        begin
          seq = "#{obj buy cytotec online.table_name[0..25]}_SEQ"
          ActiveRecord::Base.connection.execute("DROP SEQUENCE #{seq}")
          puts "  #{obj.table_name} sequence #{seq} dropped"
        rescue => ex
          puts "  #{obj.table_name} Sequence #{seq} didn't exist"
        end
      else # 'SQLite'
        seq = "#{obj.table_name}"
      end

      # Recreate the sequence
      seq_statement=""
      if @@oracleFlag
        seq_statement = "CREATE SEQUENCE #{seq} START WITH #{next_id}"
      else # 'SQLite'
        seq_statement = "update sqlite_sequence set seq = #{next_id} where name = '#{seq}';"
      end

      res = ActiveRecord::Base.connection.execute(seq_statement);
      puts "    #{obj.table_name} sequence #{seq} created/updated"
      puts "    sequence nextval #{next_id}"
      puts "\n"
   end
end

def get_all_models
  @models= Dir['app/models/*.rb'].sort.map {|f| File.basename(f,'.*').camelize.constantize}
end

def resequence_all_models
  @models= get_all_models
  @models.each do |obj|
    resequence(obj)
  end
end


if ENV["UPDATE_SEQ"]
  resequence_all_models
end

I can then reset the sequences for all of my application’s ActiveRecord models, by doing the following:

svn update
rake db:load
rake UPDATE_SEQ=1 db:seed