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



47
48
49
 
# File 'lib/exporter/sql_commands.rb', line 47

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

.dump_flags ⇒ Object

Default dump flags



62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/exporter/sql_commands.rb', line 62

def self.dump_flags
  {"add-locks" => true,
    "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



211
212
213
214
215
216
217
218
219
220
221
222
223
224
 
# File 'lib/exporter/sql_commands.rb', line 211

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_outfile_sql(options) ⇒ Object

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



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/exporter/sql_commands.rb', line 121

def self.get_outfile_sql options 
  Kernel.p "===== SELECT * INTO OUTFILE OPTIONS====="
  Kernel.p options
  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"
      sql += "WHERE #{options[:incremental_col]} >= '#{options[:incremental_val]}'"
      sql += "WHERE #{options[:incremental_col]} >= '#{(DateTime.parse(options[:incremental_val]) -1.day).to_s(:db)}'" #buffer 1 day
    elsif options[:incremental_col_type] == "int"
      sql += "WHERE #{options[:incremental_col]} >= #{options[:incremental_val].to_i - 10000}" #buffer 10000 
    end
  end

  return sql
end

.max_value_sql(*args) ⇒ Object

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



230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/exporter/sql_commands.rb', line 230

def self.max_value_sql *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



79
80
81
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
 
# File 'lib/exporter/sql_commands.rb', line 79

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



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
# File 'lib/exporter/sql_commands.rb', line 152

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} " 
    cmd += db_configs(db)["port"].blank? ? "-P3306 " : "-P#{db_configs(db)["port"]} "
  end
  
  cmd += "--execute=\"#{get_outfile_sql(options)}\" "
  
  puts cmd
  return cmd
end

.mysql_flags ⇒ Object

Default flags for mysql export



201
202
203
204
205
206
 
# File 'lib/exporter/sql_commands.rb', line 201

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
 
# 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"]} "
  cmd += "-h#{db_host} "
  cmd += " -P#{db_configs(db)["port"]} " if db_configs(db)["port"]
  cmd += " #{db} "
  cmd += " #{options[:table_name]} "
  cmd += "--result-file=#{options[:filepath]} "

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

.ssh_configs(db) ⇒ Object

Configs needed for SSH connection to source server



55
56
57
 
# File 'lib/exporter/sql_commands.rb', line 55

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