Skip to content
Newer
Older
100644 202 lines (157 sloc) 5.3 KB
691c5bf First commit
Chris Cummer authored Mar 1, 2009
1 #!/usr/bin/env ruby
2
3 require 'fileutils'
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
4 require 'yaml'
691c5bf First commit
Chris Cummer authored Mar 2, 2009
5
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
6 begin
7 require 'rubygems'
8 require 'sequel'
9 rescue Exception=>e
10 end
11
691c5bf First commit
Chris Cummer authored Mar 2, 2009
12
13 # ------------------------------ Classes ------------------------------ #
14
15
16 class MySQL2SqliteConverter
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
17
18 def initialize(args)
19 @config = {
20 :database => nil,
21 :username => nil,
22 :password => nil,
23 :overwrite => true,
24 :tables => nil,
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
25 :mysqldump => 'mysqldump5',
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
26 }
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
27
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
28 init_result = (args.length == 1) ? init_from_yaml(args) : init_from_command_line(args)
814d5bb Passwords in the YAML config file are now optional
Chris Cummer authored May 29, 2009
29
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
30 if (init_result && @config[:database] && @config[:username])
1b33644 Cleaned up various lines
Chris Cummer authored Mar 2, 2009
31 @file_deletion_delay = 10
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
32 @output_file, @sqlite_database = @config[:database] + ".sql", @config[:database] + ".sqlite"
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
33 else
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
34 raise 'Invalid configuration'
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
35 end
691c5bf First commit
Chris Cummer authored Mar 2, 2009
36 end
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
37
691c5bf First commit
Chris Cummer authored Mar 2, 2009
38 def mysql_to_sqlite()
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
39 handle_existing_files()
40
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
41 arr = []
691c5bf First commit
Chris Cummer authored Mar 2, 2009
42
c81a83a Ability to define specific tables for export via YAML config file
Chris Cummer authored Mar 8, 2009
43 mysqldump_str = generate_mysqldump_str()
44
45 IO.popen( mysqldump_str ) do |pipe|
691c5bf First commit
Chris Cummer authored Mar 2, 2009
46 pipe.each_line do |line|
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
47 next if contains_disallowed_sql( line )
691c5bf First commit
Chris Cummer authored Mar 2, 2009
48
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
49 line = translate_sql_differences( line )
50 line = translate_character_differences( line )
691c5bf First commit
Chris Cummer authored Mar 2, 2009
51
52 arr << line
53 end
54 end
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
55
56 unless arr.empty?
57 @line_count = arr.length
58
59 complete_str = arr.join( '' )
60 complete_str.gsub!( /,\n\);/, "\);\n" )
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
61 complete_str.gsub!( /\\r/, "\r" )
62 complete_str.gsub!( /\\n/, "\n" )
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
63
64 puts "Writing out to: #{@output_file}"
65 File.open( @output_file, 'w') { |f| f.write( complete_str ) }
66
67 puts "Writing out to: #{@sqlite_database}"
68 return system( "cat #{@output_file} | sqlite3 #{@sqlite_database}" )
69 end
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
70 end
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
71
72 # If you have Sequel gem, this method fixes the booleans
73 # 't' / 'f' instead of a tinyint 0/1
74 def fix_booleans
75 puts "Fix booleans on: #{@sqlite_database}"
76 db = Sequel.sqlite(@sqlite_database)
77 db.tables.each do |t|
accd21c @mig-hub Change fix_boolean according to previous commit
authored May 3, 2011
78 boolean_cols = db.schema(t).map{|c,v|c if v[:db_type]=="boolean"}.compact!
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
79 boolean_cols.each do |b|
80 db[t].filter(b=>0).update(b=>false)
81 db[t].filter(b=>1).update(b=>true)
82 end
83 end
84 end
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
85
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
86 private
87
88 def init_from_yaml(args)
89 if (File.exists?(args[0]))
90 config = YAML::load_file(args[0])
91 # Backward compatibility with old version of configs
92 config = config['config'] if config['config']
93
94 config.each do |key, value|
95 key = key.to_sym
96 if @config.has_key? key
8069631 Fixes password append issue (missing a required space)
Chris Cummer authored Jul 7, 2010
97 @config[key] = value
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
98 else
99 raise 'Invalid option'
100 end
101 end
102 true
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
103 end
104 end
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
105
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
106 def init_from_command_line( args )
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
107 if (args.length >= 2)
108 @config[:database], @config[:username], = args[0], args[1]
109 @config[:password] = args[2] if args[2]
110 @config[:overwrite] = args[3] if args[3] && ("1" == args[3] || "true" == args[3])
111 true
112 end
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
113 end
114
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
115 def handle_existing_files()
116 handle_existing_file( @output_file )
117 handle_existing_file( @sqlite_database )
118 end
119
120 def handle_existing_file( file )
121 # TODO: Replace this with a query to the user, defaulting to Y
122 if ( File.exists?( file ) )
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
123 if ( true != @config[:overwrite] )
f8697bc Added option to over-ride file over-write warnings
Chris Cummer authored Mar 2, 2009
124 (1..@file_deletion_delay).each do |count|
125 puts "WARNING: File #{file} already exists and will be over-written in #{@file_deletion_delay - count} seconds. Press ctl-C to Quit"
126 sleep 1
127 end
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
128 end
129
130 FileUtils.rm( file )
131 end
132 end
133
134
c81a83a Ability to define specific tables for export via YAML config file
Chris Cummer authored Mar 8, 2009
135 def generate_mysqldump_str()
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
136 table_str = ( nil != @config[:tables] ) ? @config[:tables].join( ' ' ) : ''
137
138 mysqldump_str = "#{@config[:mysqldump]} -u #{@config[:username]} --compact --compatible=ansi --complete-insert --skip-extended-insert --default-character-set=binary #{@config[:database]} " + table_str
8069631 Fixes password append issue (missing a required space)
Chris Cummer authored Jul 7, 2010
139 mysqldump_str += " -p#{@config[:password]}" if @config[:password]
c81a83a Ability to define specific tables for export via YAML config file
Chris Cummer authored Mar 8, 2009
140
141 return mysqldump_str
142 end
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
143
144
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
145 # Don't attempt to include lines of MySQL data that Sqlite doesn't recognize
146 def contains_disallowed_sql( line )
147 return true if line.include?( 'KEY "' )
148 return true if line.include?( 'UNIQUE KEY ' )
149 return true if line.include?( 'PRIMARY KEY ' )
150 return false
151 end
152
153
154 # Replaces the MySQL terms with Sqlite-friendly terms
155 def translate_sql_differences( line )
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
156 line.gsub!( /UNSIGNED /i, '' )
157 line.gsub!( /AUTO_INCREMENT/i, ' primary key' )
158 line.gsub!( /SMALLINT\([0-9]*\)/i, 'integer' )
c411850 @mig-hub Fix boolean db_type
authored May 3, 2011
159 line.gsub!( /TINYINT\(1\)/i, 'boolean' )
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
160 line.gsub!( /TINYINT\([2-9]*\)/i, 'integer' )
161 line.gsub!( /INT\([0-9]*\)/i, 'integer' )
162 line.gsub!( /CHARACTER SET [^ ]+/i, '' )
163 line.gsub!( /ENUM\([^)]*\)/i, 'varchar(255)' )
164 line.gsub!( /ON UPDATE [^,]*/i, '' )
165 line.gsub!( /COLLATE [^\s]+/i, '')
691c5bf First commit
Chris Cummer authored Mar 2, 2009
166
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
167 #line.gsub!( /" text/i, '" text' )
7922b40 Added hack to add missing comma after text field type.
Chris Cummer authored Jul 7, 2010
168
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
169 return line
170 end
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
171
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
172 # Replace other syntactic differences
173 def translate_character_differences( line )
174 line.gsub!( /\`/, '"' )
175 line.gsub!( /\\'/, '\'\'' )
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
176 line.gsub!( /\\"/, '"')
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
177
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
178 return line
691c5bf First commit
Chris Cummer authored Mar 2, 2009
179 end
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
180
691c5bf First commit
Chris Cummer authored Mar 2, 2009
181 end
182
183
184 # ------------------------------ Main ------------------------------ #
185
186
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
187 if __FILE__ == $0
188 begin
189 conv = MySQL2SqliteConverter.new(ARGV)
190 rescue => e
191 puts 'ERROR: ' + e
192 puts
eb0462b Now accepts YAML file for configuration; some more error handling aro…
Chris Cummer authored Mar 2, 2009
193 puts "Usage: ./mysql2sqlite.rb database_name username password "
194 puts " or: ./mysql2sqlite.rb config_file.yaml"
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
195 else
196 result = conv.mysql_to_sqlite()
737ccd9 @mig-hub Couple of modifications in regex and add fix_booleans when Sequel gem…
authored Apr 20, 2011
197 conv.fix_booleans if defined?(Sequel)
691c5bf First commit
Chris Cummer authored Mar 2, 2009
198
452b3f0 @Shumkov refactor initialize and configuration
Shumkov authored Mar 25, 2010
199 puts ( result ) ? "Done." : "Error."
200 end
8ac683d Refactored some of the main class to compartmentalize
Chris Cummer authored Mar 2, 2009
201 exit
691c5bf First commit
Chris Cummer authored Mar 2, 2009
202 end
Something went wrong with that request. Please try again.