SQL INSERT to ActiveRecord converter

Posted by Luke
on Wednesday, March 14

If you’ve got a file full of SQL INSERT statements, this code may help you. It converts them into Ruby ActiveRecord create calls so you can use them in Ruby on Rails migrations.

Note that the format of the inserts is assumed to be like this:

INSERT INTO `foobars` (`id`,`value`) VALUES (1,'stuff');

You will need to modify the regex if the format differs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

require 'rubygems'
require 'active_support'

if ARGV.empty?
  puts "Usage: #{$0} file.sql"
  exit
end

class Array
  def to_h(keys)
    Hash[*keys.zip(self).flatten]
  end
end

File.open( ARGV[0], 'r' ).readlines.each do |line|

  if line =~ /^INSERT INTO/
    matchdata = line.match( /^INSERT INTO `([\w]+)` \((.+)\) VALUES \((.+)\)/ )
  
    columns = matchdata[2].split(',').collect! do |column_name|
      column_name[1,column_name.length-2].intern
    end
    
    values = matchdata[3].split(',').collect! do |value|
      if value =~ /^'.*'$/
        value.sub!( /^'(.*)'$/, '\1')
      end
      
      if value == 'NULL'
        value = nil
      end
      
      value
    end
    
    puts "#{matchdata[1].classify}.create( #{values.to_h( columns ).inspect} )"
  end
end