Module: Myreplicator::SqlCommands

Defined in:
lib/exporter/sql_commands.rb

Class Method Summary collapse

Class Method Details

.db_configs(db) ⇒ Object

Db configs for active record connection



50
51
52
 
# File 'lib/exporter/sql_commands.rb', line 50

def self.db_configs db
  ActiveRecord::Base.configurations[db]
end

.dump_flags ⇒ Object

Default dump flags



65
66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/exporter/sql_commands.rb', line 65

def self.dump_flags
  {"add-locks" => false,
    "compact" => false,
    "lock-tables" => false,
    "no-create-db" => true,
    "no-data" => false,
    "quick" => true,
    "skip-add-drop-table" => false,
    "create-options" => false,
    "single-transaction" => false
  }
end

.export_sql(*args) ⇒ Object

Builds SQL needed for incremental exports



274
275
276
277
278
279
280
281
282
283
284
285
286
287
 
# File 'lib/exporter/sql_commands.rb', line 274

def self.export_sql *args
  options = args.extract_options!
  sql = "SELECT * FROM #{options[:db]}.#{options[:table]} " 
  
  if options[:incremental_col] && !options[:incremental_val].blank?
    if options[:incremental_col_type] == "datetime"
      sql += "WHERE #{options[:incremental_col]} >= '#{options[:incremental_val]}'"
    else
      sql += "WHERE #{options[:incremental_col]} >= #{options[:incremental_val]}"
    end
  end

  return sql
end

.get_columns(*args) ⇒ Object

exp: SELECT customer_id,firstname,REPLACE(UPPER(‘lastname`), ’NULL’, ‘ABC’),email,..,REPLACE(‘modified_date`, ’0000-00-00’,‘1900-01-01’),.. FROM king.customer WHERE customer_id in ( 261085,348081,477336 );



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
# File 'lib/exporter/sql_commands.rb', line 126

def self.get_columns * args
  options = args.extract_options!
  #Kernel.p "===== GET COLUMNS OPTIONS ====="
  #Kernel.p options
  #
  exp = Myreplicator::Export.find(options[:export_id])
  #
  mysql_schema = Myreplicator::Loader.mysql_table_definition(options)
  mysql_schema_simple_form = Myreplicator::MysqlExporter.get_mysql_schema_rows mysql_schema
  columns = Myreplicator::VerticaLoader.get_mysql_inserted_columns mysql_schema_simple_form
  #Kernel.p "===== table's columns====="
  #Kernel.p columns
  if !exp.removing_special_chars.blank?
    json = JSON.parse(exp.removing_special_chars)
  else
    json = {}
  end
  #Kernel.p exp.removing_special_chars
  #Kernel.p json
  result = []
  columns.each do |column|
    if !json[column].blank?
      puts json[column]
      replaces = json[column]
      sql = ""
      replaces.each do |k,v|
        if sql.blank?
          sql = "REPLACE(\\`#{column}\\`, '#{k}', '#{v}')"
        else
          sql = "REPLACE(#{sql}, '#{k}', '#{v}')"
        end
        sql.gsub!("back_slash","\\\\\\\\\\")
        #puts sql
      end
      result << sql
    else
      result << "\\`#{column}\\`"
    end
  end
  Kernel.p result
  return result
end

.get_outfile_sql(*args) ⇒ Object

Mysql export data into outfile option Provided for tables that need special delimiters



174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/exporter/sql_commands.rb', line 174

def self.get_outfile_sql *args 
  options = args.extract_options!
  #Kernel.p "===== SELECT * INTO OUTFILE OPTIONS====="
  #Kernel.p options
  columns = get_columns options
  sql = "SELECT #{columns.join(',')} INTO OUTFILE '#{options[:filepath]}' "
  #sql = "SELECT * INTO OUTFILE '#{options[:filepath]}' " 
  
  if options[:enclosed_by].blank?
    sql += " FIELDS TERMINATED BY '\\0' ESCAPED BY '' LINES TERMINATED BY ';~~;\n'"
  else
    sql += " FIELDS TERMINATED BY '\\0' ESCAPED BY '' ENCLOSED BY '#{options[:enclosed_by]}'  LINES TERMINATED BY ';~~;\n'"
  end
  
  sql += "FROM #{options[:db]}.#{options[:table]} "

  if options[:export_type]=="incremental" && !options[:incremental_col].blank? && !options[:incremental_val].blank?
    if options[:incremental_col_type] == "datetime"
      if options[:incremental_val] == "0"
        options[:incremental_val] = "1900-01-01 00:00:00"
      end
      sql += "WHERE #{options[:incremental_col]} >= '#{(DateTime.parse(options[:incremental_val]) -1.hour).to_s(:db)}'" #buffer 1 hour
    elsif options[:incremental_col_type] == "int"
      if options[:incremental_val].blank?
        options[:incremental_val] = "0"
      end
      sql += "WHERE #{options[:incremental_col]} >= #{options[:incremental_val].to_i - 10000}" #buffer 10000 
    end
  end
  Kernel.p sql
  return sql
end

.max_value_sql(*args) ⇒ Object

Gets the Maximum value for the incremental column of the export job



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/exporter/sql_commands.rb', line 293

def self.max_value_sql *args
  options = args.extract_options!
  sql = ""

  if options[:incremental_col]
    
    if options[:incremental_col_type] == "datetime" && options[:max_incremental_value] == '0'
      options[:max_incremental_value] = "1900-01-01 00:00:00"
    end
    sql = "SELECT COALESCE(max(#{options[:incremental_col]}),'#{options[:max_incremental_value]}') FROM #{options[:db]}.#{options[:table]}" 
  else
    raise Myreplicator::Exceptions::MissingArgs.new("Missing Incremental Column Parameter")
  end
  
  return sql
end

.max_value_vsql(*args) ⇒ Object 



310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/exporter/sql_commands.rb', line 310

def self.max_value_vsql *args
  options = args.extract_options!
  sql = ""
  
  if options[:incremental_col]
    sql = "SELECT max(#{options[:incremental_col]}) FROM #{options[:db]}.#{options[:table]}"
  else
    raise Myreplicator::Exceptions::MissingArgs.new("Missing Incremental Column Parameter")
  end
        
  return sql
end

.mysql_export(*args) ⇒ Object

Mysql exports using -e flag



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
# File 'lib/exporter/sql_commands.rb', line 82

def self.mysql_export *args
  options = args.extract_options!
  options.reverse_merge! :flags => []
  db = options[:db]
  
  # Database host when ssh'ed into the db server
  
  db_host = "127.0.0.1" 
  
  if !ssh_configs(db)["ssh_db_host"].blank? 
    db_host =  ssh_configs(db)["ssh_db_host"]
  elsif !db_configs(db)["host"].blank?
    db_host = db_configs(db)["host"]
  end
  
  flags = ""

  self.mysql_flags.each_pair do |flag, value|
    if options[:flags].include? flag
      flags += " --#{flag} "
    elsif value
      flags += " --#{flag} "
    end
  end

  cmd = Myreplicator.mysql
  cmd += "#{flags} -u#{db_configs(db)["username"]} -p#{db_configs(db)["password"]} " 

  cmd += "-h#{db_host} " 
  cmd += db_configs(db)["port"].blank? ? "-P3306 " : "-P#{db_configs(db)["port"]} "
  cmd += "--execute=\"#{options[:sql]}\" "
  cmd += " > #{options[:filepath]} "
  
  puts cmd
  return cmd
end

.mysql_export_outfile(*args) ⇒ Object

Export using outfile \0 delimited terminated by newline Location of the output file needs to have 777 perms



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'lib/exporter/sql_commands.rb', line 213

def self.mysql_export_outfile *args
  Kernel.p "===== mysql_export_outfile OPTIONS ====="
  
  options = args.extract_options!
  Kernel.p options
  options.reverse_merge! :flags => []
  db = options[:source_schema]

  # Database host when ssh'ed into the db server
  db_host = "127.0.0.1"
  Kernel.p "===== mysql_export_outfile ssh_configs ====="
  Kernel.p ssh_configs(db)
  if !ssh_configs(db)["ssh_db_host"].blank?
    db_host =  ssh_configs(db)["ssh_db_host"]
  elsif !db_configs(db)["host"].blank?
    db_host = db_configs(db)["host"]
  end
  
  flags = ""
  
  self.mysql_flags.each_pair do |flag, value|
    if options[:flags].include? flag
      flags += " --#{flag} "
    elsif value
      flags += " --#{flag} "
    end
  end
  
  cmd = Myreplicator.mysql
  cmd += "#{flags} "
  
  cmd += "-u#{db_configs(db)["username"]} -p#{db_configs(db)["password"]} "
  
  if db_configs(db).has_key? "socket"
    cmd += "--socket=#{db_configs(db)["socket"]} " 
  else
    cmd += "-h#{db_host} " if db_configs(db)["unuse_host_and_port"].blank?
    if db_configs(db)["unuse_host_and_port"].blank?
      cmd += db_configs(db)["port"].blank? ? "-P3306 " : "-P#{db_configs(db)["port"]} "
    end
  end
  
  cmd += "--execute=\"#{get_outfile_sql(options)}\" "
  Kernel.p cmd
  puts cmd
  return cmd
end

.mysql_flags ⇒ Object

Default flags for mysql export



264
265
266
267
268
269
 
# File 'lib/exporter/sql_commands.rb', line 264

def self.mysql_flags
  {"column-names" => false,
    "quick" => true,
    "reconnect" => true
  }    
end

.mysqldump(*args) ⇒ Object 



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
39
40
41
42
43
44
 
# File 'lib/exporter/sql_commands.rb', line 4

def self.mysqldump *args
  options = args.extract_options!
  options.reverse_merge! :flags => []
  db = options[:db]

  flags = ""

  self.dump_flags.each_pair do |flag, value|
    if options[:flags].include? flag
      flags += " --#{flag} "
    elsif value
      flags += " --#{flag} "
    end
  end

  # Database host when ssh'ed into the db server
  db_host = "127.0.0.1" 

  if !ssh_configs(db)["ssh_db_host"].blank? 
    db_host =  ssh_configs(db)["ssh_db_host"]
  elsif !db_configs(db)["host"].blank?
    db_host = db_configs(db)["host"]
  end

  cmd = Myreplicator.mysqldump
  cmd += "#{flags} -u#{db_configs(db)["username"]} -p#{db_configs(db)["password"]} "
  Kernel.p "==== db_configs(db)['unuse_host_and_port'].blank? ====="
  Kernel.p db_configs(db)
  Kernel.p db_configs(db)["unuse_host_and_port"].blank?
  cmd += "-h#{db_host} " if db_configs(db)["unuse_host_and_port"].blank?
  cmd += " -P#{db_configs(db)["port"]} " if (db_configs(db)["port"] && db_configs(db)["unuse_host_and_port"].blank?)
  cmd += " #{db} "
  cmd += " #{options[:table_name]} "
  cmd += "--result-file=#{options[:filepath]} "

  # cmd += "--tab=#{options[:filepath]} "
  # cmd += "--fields-enclosed-by=\'\"\' "
  # cmd += "--fields-escaped-by=\'\\\\\' "
  puts cmd  
  return cmd
end

.ssh_configs(db) ⇒ Object

Configs needed for SSH connection to source server



58
59
60
 
# File 'lib/exporter/sql_commands.rb', line 58

def self.ssh_configs db
  Myreplicator.configs[db]
end