Amitesh · August 9, 2012 10:05 · Mar 31, 2012 · Mar 15, 2012
diff --git a/convert.rake b/convert.rake
@@ -14,36 +14,45 @@
       end
     end
 
-    conn.run_sql "ALTER DATABASE MyDB CHARACTER SET utf8 collate utf8_unicode_ci"
+    conn.run_sql "ALTER DATABASE my_database CHARACTER SET utf8 collate utf8_unicode_ci"
 
-    # Regexp to identify views that should be skipped
+    # Don't covert views
     VIEWS = /(view|_v$)/
+    big = []
 
-    # Tables that don't make Rails' inflection rules or tables that do not have corresponding models.
-    mapping = { :pos => Pos, :categories_products => true, :delayed_jobs => Delayed::Job,
-                :redemption_codes => true, :return_items => true, :schema_migrations => true }.with_indifferent_access
-    tables = conn.tables.select { |tbl| tbl !~ VIEWS }
+    # These are table_name => model_class mappings that aren't rails standard or
+    # tables that we don't wish to convert (table_name => true).
+    mapping = { :pos => Pos,
+                :categories_products => true,
+                :delayed_jobs => Delayed::Job,
+                :schema_migrations => true
+              }.with_indifferent_access
+    tables = (conn.tables - big).select { |tbl| tbl !~ VIEWS }
     puts "Converting #{tables.inspect}"
 
+    #(tables - big).each do |tbl|
     tables.each do |tbl|
       a = ['CHARACTER SET utf8 COLLATE utf8_unicode_ci']
       b = []
       model = mapping[tbl] || tbl.to_s.classify.constantize
       model.columns.each do |col|
         type = col.sql_type
 
+        nullable = col.null ? '' : ' NOT NULL'
+        default = col.default ? " DEFAULT '#{col.default}'" : ''
+
         case type
         when /varchar/
           a << "CHANGE #{col.name} #{col.name} VARBINARY(#{col.limit})"
-          b << "CHANGE #{col.name} #{col.name} VARCHAR(#{col.limit}) CHARACTER SET utf8 COLLATE utf8_unicode_ci"
+          b << "CHANGE #{col.name} #{col.name} VARCHAR(#{col.limit}) CHARACTER SET utf8 COLLATE utf8_unicode_ci#{nullable}#{default}"
         when /text/
           a << "CHANGE #{col.name} #{col.name} BLOB"
-          b << "CHANGE #{col.name} #{col.name} TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci"
+          b << "CHANGE #{col.name} #{col.name} TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci#{nullable}#{default}"
         end
       end unless model == true
 
-      conn.run_sql "ALTER TABLE #{tbl} #{a.join(',')}"
-      conn.run_sql "ALTER TABLE #{tbl} #{b.join(',')}" if b.present?
+      conn.run_sql "ALTER TABLE #{tbl} #{a.join(', ')}"
+      conn.run_sql "ALTER TABLE #{tbl} #{b.join(', ')}" if b.present?
     end
 
     puts Time.now

diff --git a/convert.rake b/convert.rake
@@ -0,0 +1,51 @@
+  desc "convert a latin1 database with utf8 data into proper utf8"
+  task :convert_to_utf8 => :environment do
+    puts Time.now
+    dryrun = ENV['DOIT'] != '1'
+    conn = ActiveRecord::Base.connection
+    if dryrun
+      def conn.run_sql(sql)
+        puts(sql)
+      end
+    else
+      def conn.run_sql(sql)
+        puts(sql)
+        execute(sql)
+      end
+    end
+
+    conn.run_sql "ALTER DATABASE MyDB CHARACTER SET utf8 collate utf8_unicode_ci"
+
+    # Regexp to identify views that should be skipped
+    VIEWS = /(view|_v$)/
+
+    # Tables that don't make Rails' inflection rules or tables that do not have corresponding models.
+    mapping = { :pos => Pos, :categories_products => true, :delayed_jobs => Delayed::Job,
+                :redemption_codes => true, :return_items => true, :schema_migrations => true }.with_indifferent_access
+    tables = conn.tables.select { |tbl| tbl !~ VIEWS }
+    puts "Converting #{tables.inspect}"
+
+    tables.each do |tbl|
+      a = ['CHARACTER SET utf8 COLLATE utf8_unicode_ci']
+      b = []
+      model = mapping[tbl] || tbl.to_s.classify.constantize
+      model.columns.each do |col|
+        type = col.sql_type
+
+        case type
+        when /varchar/
+          a << "CHANGE #{col.name} #{col.name} VARBINARY(#{col.limit})"
+          b << "CHANGE #{col.name} #{col.name} VARCHAR(#{col.limit}) CHARACTER SET utf8 COLLATE utf8_unicode_ci"
+        when /text/
+          a << "CHANGE #{col.name} #{col.name} BLOB"
+          b << "CHANGE #{col.name} #{col.name} TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci"
+        end
+      end unless model == true
+
+      conn.run_sql "ALTER TABLE #{tbl} #{a.join(',')}"
+      conn.run_sql "ALTER TABLE #{tbl} #{b.join(',')}" if b.present?
+    end
+
+    puts Time.now
+    puts 'Done!'
+  end
No results found