Redmine

#

# 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