Project

General

Profile

Defect #10537 » convert.rake

M T, 2012-04-03 12:10

 
1
#
2
# Convert/transfer data from Source => Dest.    This facilitates
3
# a conversion one database adapter type to another (say postgres -> mysql )
4
#
5
# WARNING 1: this script deletes all Dest data and replaces it with
6
#                     Source data
7
#
8
# WARNING 2: This script assumes it is the only user updating either database.
9
#                     Database integrity could be corrupted if other users where 
10
#                     writing to the databases.
11
#
12
# Usage:  rake db:convert:prod2dev
13
#
14
# It assumes the Dest database has a schema identical to the Source 
15
# database, but will delete any data before importing the Source data
16
#
17
# A few little refinements added to Rama's script by M.Taylor:
18
# 
19
#    * Add sequence reset after the transfer (necessary for Postgres and some other DB's. (NB: Adapter specific code needed)
20
#    * Code commeted out to migrate just a few listed tables if desired.
21
#    * Suppress single-table inheritance behaviour for tables containing a column named "type"
22
#    * Do not copy column id if the table doesn't have one.
23
#
24
# A couple of the outer loops evolved from 
25
#    http://snippets.dzone.com/posts/show/3393
26
#
27
# For further instructions see 
28
#    http://myutil.com/2008/8/31/rake-task-transfer-rails-database-mysql-to-postgres
29
#
30
# The master repository for this script is at github:
31
#    http://github.com/face/rails_db_convert_using_adapters/tree/master
32
#
33
#
34
# Author: Rama McIntosh
35
#         Matson Systems, Inc.
36
#         http://www.matsonsystems.com
37
#
38
# This rake task is released under this BSD license:
39
#
40
# Copyright (c) 2008, Matson Systems, Inc. All rights reserved.
41
# 
42
# Redistribution and use in source and binary forms, with or without
43
# modification, are permitted provided that the following conditions
44
# are met:
45
# 
46
# * Redistributions of source code must retain the above copyright
47
#   notice, this list of conditions and the following disclaimer.
48
# * Redistributions in binary form must reproduce the above copyright
49
#   notice, this list of conditions and the following disclaimer in the
50
#   documentation and/or other materials provided with the distribution.
51
# * Neither the name of Matson Systems, Inc. nor the names of its
52
#   contributors may be used to endorse or promote products derived
53
#   from this software without specific prior written permission.
54
#
55
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
56
# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
57
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
58
# FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
59
# COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
60
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
61
# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
62
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
63
# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
64
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
65
# ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
66
# POSSIBILITY OF SUCH DAMAGE.
67

    
68
# PAGE_SIZE is the number of rows updated in a single transaction.
69
# This facilitates tables where the number of rows exceeds the systems
70
# memory
71
PAGE_SIZE=10000
72

    
73
namespace :db do
74
  namespace :convert do    
75
    desc 'Convert/import Source data to Dest.   DANGER Deletes all data in the Dest database.   Assumes both schemas are already migrated.'
76
    task :prod2dev => :environment do
77

    
78
      # We need unique classes so ActiveRecord can hash different connections
79
      # We do not want to use the real Model classes because any business
80
      # rules will likely get in the way of a database transfer
81
      class SourceModelClass < ActiveRecord::Base
82
        self.inheritance_column = :_type_disabled
83
      end
84
      class DestModelClass < ActiveRecord::Base
85
        self.inheritance_column = :_type_disabled
86
      end
87

    
88
      
89
      SourceModelClass.establish_connection(:production)
90
      DestModelClass.establish_connection(:development)
91
      
92
      skip_tables = ["schema_info", "schema_migrations"]
93
      
94
      # Generate a list of all tables needing conversion
95
      table_list = (SourceModelClass.connection.tables - skip_tables).sort
96
    
97
      # Use this line to convert just a specific list of tables   
98
      # table_list = ["custom_fields_trackers","custom_fields_projects","groups_users","projects_trackers"]
99
      
100

    
101
      table_list.each do |table_name|
102
 
103
        SourceModelClass.set_table_name(table_name)
104
        DestModelClass.set_table_name(table_name)
105
        DestModelClass.reset_column_information
106
        SourceModelClass.reset_column_information
107
        DestModelClass.record_timestamps = false
108

    
109
        # Page through the data in case the table is too large to fit in RAM
110
        offset = count = 0;
111
        print "Converting #{table_name}..."; STDOUT.flush
112
        # First, delete any old dev data
113
        DestModelClass.delete_all
114
        while ((models = SourceModelClass.find(:all, 
115
                :offset=>offset, :limit=>PAGE_SIZE)).size > 0)
116

    
117
          count += models.size
118
          offset += PAGE_SIZE
119

    
120
          # Now, write out the prod data to the dev db
121
          DestModelClass.transaction do
122
            models.each do |model|
123
              new_model = DestModelClass.new(model.attributes)
124
              if (model.attributes.has_key?('id'))
125
                new_model.id = model.id
126
              else
127
                new_model.id = 0   # Trick for PG
128
              end
129
              new_model.save(false)
130
            end
131
          end
132
        end
133
        print "#{count} records converted\n"
134
        
135
        case DestModelClass.connection.adapter_name
136
        when 'SQLite'
137
          new_max = maximum(primary_key) || 0
138
          update_seq_sql = "update sqlite_sequence set seq = #{new_max} where name = '#{table_name}';"
139
          DestModelClass.connection.execute(update_seq_sql)
140
        when 'PostgreSQL'
141
          DestModelClass.connection.reset_pk_sequence!(table_name)
142
        else
143
          raise "Task not implemented for this DB adapter"
144
        end
145

    
146
        
147
        
148
      end
149
    end
150
  end
151
end
(3-3/3)