face / rails_db_convert_using_adapters

Rake task to convert a Rails database from one platfrom to another, say Postgres to MySQL and back again. Uses two open db adapters and works with binary data in blobs.

This URL has Read+Write access

raw normal view history
16feabbb » face 2008-10-10 first version 1 #
2 # Convert/transfer data from production => development. This facilitates
3 # a conversion one database adapter type to another (say postgres -> mysql )
4 #
5 # WARNING 1: this script deletes all development data and replaces it with
6 # production 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 development database has a schema identical to the production
15 # database, but will delete any data before importing the production data
16 #
17 # A couple of the outer loops evolved from
18 # http://snippets.dzone.com/posts/show/3393
19 #
20 # For further instructions see
21 # http://myutil.com/2008/8/31/rake-task-transfer-rails-database-mysql-to-postgres
22 #
225fd7c9 » face 2008-10-10 Added link to the github pr... 23 # The master repository for this script is at github:
24 # http://github.com/face/rails_db_convert_using_adapters/tree/master
25 #
16feabbb » face 2008-10-10 first version 26 # Author: Rama McIntosh
27 # Matson Systems, Inc.
28 # http://www.matsonsystems.com
29 #
30 # This rake task is released under this BSD license:
31 #
32 # Copyright (c) 2008, Matson Systems, Inc. All rights reserved.
33 #
34 # Redistribution and use in source and binary forms, with or without
35 # modification, are permitted provided that the following conditions
36 # are met:
37 #
38 # * Redistributions of source code must retain the above copyright
39 # notice, this list of conditions and the following disclaimer.
40 # * Redistributions in binary form must reproduce the above copyright
41 # notice, this list of conditions and the following disclaimer in the
42 # documentation and/or other materials provided with the distribution.
43 # * Neither the name of Matson Systems, Inc. nor the names of its
44 # contributors may be used to endorse or promote products derived
45 # from this software without specific prior written permission.
46 #
47 # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
48 # "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
49 # LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
50 # FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
51 # COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
52 # INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
53 # BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
54 # LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
55 # CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
56 # LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
57 # ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
58 # POSSIBILITY OF SUCH DAMAGE.
59
60 # PAGE_SIZE is the number of rows updated in a single transaction.
61 # This facilitates tables where the number of rows exceeds the systems
62 # memory
63 PAGE_SIZE=10000
64
65 namespace :db do
66 namespace :convert do
67 desc 'Convert/import production data to development. DANGER Deletes all data in the development database. Assumes both schemas are already migrated.'
68 task :prod2dev => :environment do
69
70 # We need unique classes so ActiveRecord can hash different connections
71 # We do not want to use the real Model classes because any business
72 # rules will likely get in the way of a database transfer
73 class ProductionModelClass < ActiveRecord::Base
74 end
75 class DevelopmentModelClass < ActiveRecord::Base
76 end
77
78 skip_tables = ["schema_info", "schema_migrations"]
79 ActiveRecord::Base.establish_connection(:production)
80 (ActiveRecord::Base.connection.tables - skip_tables).each do |table_name|
81
82 ProductionModelClass.set_table_name(table_name)
83 DevelopmentModelClass.set_table_name(table_name)
84 DevelopmentModelClass.establish_connection(:development)
85 DevelopmentModelClass.reset_column_information
86 ProductionModelClass.reset_column_information
87 DevelopmentModelClass.record_timestamps = false
88
89 # Page through the data in case the table is too large to fit in RAM
90 offset = count = 0;
91 print "Converting #{table_name}..."; STDOUT.flush
92 # First, delete any old dev data
93 DevelopmentModelClass.delete_all
94 while ((models = ProductionModelClass.find(:all,
95 :offset=>offset, :limit=>PAGE_SIZE)).size > 0)
96
97 count += models.size
98 offset += PAGE_SIZE
99
100 # Now, write out the prod data to the dev db
101 DevelopmentModelClass.transaction do
102 models.each do |model|
103 new_model = DevelopmentModelClass.new(model.attributes)
104 new_model.id = model.id
105 new_model.save(false)
106 end
107 end
108 end
109 print "#{count} records converted\n"
110 end
111 end
112 end
113 end