# # Convert/transfer data from Source => Dest. This facilitates # a conversion one database adapter type to another (say postgres -> mysql ) # # WARNING 1: this script deletes all Dest data and replaces it with # Source data # # WARNING 2: This script assumes it is the only user updating either database. # Database integrity could be corrupted if other users where # writing to the databases. # # Usage: rake db:convert:prod2dev # # It assumes the Dest database has a schema identical to the Source # database, but will delete any data before importing the Source data # # A few little refinements added to Rama's script by M.Taylor: # # * Add sequence reset after the transfer (necessary for Postgres and some other DB's. (NB: Adapter specific code needed) # * Code commeted out to migrate just a few listed tables if desired. # * Suppress single-table inheritance behaviour for tables containing a column named "type" # * Do not copy column id if the table doesn't have one. # # A couple of the outer loops evolved from # http://snippets.dzone.com/posts/show/3393 # # For further instructions see # http://myutil.com/2008/8/31/rake-task-transfer-rails-database-mysql-to-postgres # # The master repository for this script is at github: # http://github.com/face/rails_db_convert_using_adapters/tree/master # # # Author: Rama McIntosh # Matson Systems, Inc. # http://www.matsonsystems.com # # This rake task is released under this BSD license: # # Copyright (c) 2008, Matson Systems, Inc. All rights reserved. # # Redistribution and use in source and binary forms, with or without # modification, are permitted provided that the following conditions # are met: # # * Redistributions of source code must retain the above copyright # notice, this list of conditions and the following disclaimer. # * Redistributions in binary form must reproduce the above copyright # notice, this list of conditions and the following disclaimer in the # documentation and/or other materials provided with the distribution. # * Neither the name of Matson Systems, Inc. nor the names of its # contributors may be used to endorse or promote products derived # from this software without specific prior written permission. # # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS # "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT # LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS # FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE # COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, # INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, # BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; # LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER # CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT # LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN # ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # PAGE_SIZE is the number of rows updated in a single transaction. # This facilitates tables where the number of rows exceeds the systems # memory PAGE_SIZE=10000 namespace :db do namespace :convert do desc 'Convert/import Source data to Dest. DANGER Deletes all data in the Dest database. Assumes both schemas are already migrated.' task :prod2dev => :environment do # We need unique classes so ActiveRecord can hash different connections # We do not want to use the real Model classes because any business # rules will likely get in the way of a database transfer class SourceModelClass < ActiveRecord::Base self.inheritance_column = :_type_disabled end class DestModelClass < ActiveRecord::Base self.inheritance_column = :_type_disabled end SourceModelClass.establish_connection(:production) DestModelClass.establish_connection(:development) skip_tables = ["schema_info", "schema_migrations"] # Generate a list of all tables needing conversion table_list = (SourceModelClass.connection.tables - skip_tables).sort # Use this line to convert just a specific list of tables # table_list = ["custom_fields_trackers","custom_fields_projects","groups_users","projects_trackers"] table_list.each do |table_name| SourceModelClass.set_table_name(table_name) DestModelClass.set_table_name(table_name) DestModelClass.reset_column_information SourceModelClass.reset_column_information DestModelClass.record_timestamps = false # Page through the data in case the table is too large to fit in RAM offset = count = 0; print "Converting #{table_name}..."; STDOUT.flush # First, delete any old dev data DestModelClass.delete_all while ((models = SourceModelClass.find(:all, :offset=>offset, :limit=>PAGE_SIZE)).size > 0) count += models.size offset += PAGE_SIZE # Now, write out the prod data to the dev db DestModelClass.transaction do models.each do |model| new_model = DestModelClass.new(model.attributes) if (model.attributes.has_key?('id')) new_model.id = model.id else new_model.id = 0 # Trick for PG end new_model.save(false) end end end print "#{count} records converted\n" case DestModelClass.connection.adapter_name when 'SQLite' new_max = maximum(primary_key) || 0 update_seq_sql = "update sqlite_sequence set seq = #{new_max} where name = '#{table_name}';" DestModelClass.connection.execute(update_seq_sql) when 'PostgreSQL' DestModelClass.connection.reset_pk_sequence!(table_name) else raise "Task not implemented for this DB adapter" end end end end end