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



188
189
190
191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/exporter/sql_commands.rb', line 188

def self.export_sql *args
  options = args.extract_options!
  sql = "SELECT * FROM #{options[:db]}.#{options[:table]} " 
  
  if options[:incremental_col] && options[:incremental_val]
    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



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/exporter/sql_commands.rb', line 120

def self.get_outfile_sql options 
  sql = "SELECT * INTO OUTFILE '#{options[:filepath]}' " 
  
  sql += " FIELDS TERMINATED BY ';~;' OPTIONALLY ENCLOSED BY '\\\"'  LINES TERMINATED BY '\\n'"
  
  sql += "FROM #{options[:db]}.#{options[:table]} "

  if options[:incremental_col] && options[:incremental_val]
    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

.max_value_sql(*args) ⇒ Object

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



207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/exporter/sql_commands.rb', line 207

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
 
# 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 ;~; delimited terminated by newline Location of the output file needs to have 777 perms



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/exporter/sql_commands.rb', line 144

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

  # Database host when ssh'ed into the db server
  db_host = ssh_configs(db)["ssh_db_host"].nil? ? "127.0.0.1" : ssh_configs(db)["ssh_db_host"]

  flags = ""

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

  # BOB : You always build the host,password,username part of the command
  # Seems like this should be in a function somewhere 
  cmd = Myreplicator.mysql
  cmd += "#{flags} -u#{db_configs(db)["username"]} -p#{db_configs(db)["password"]} " 
  cmd += "-h#{db_host} " if db_configs(db)["host"].blank?
  cmd += db_configs(db)["port"].blank? ? "-P3306 " : "-P#{db_configs(db)["port"]} "
  cmd += "--execute=\"#{get_outfile_sql(options)}\" "
  
  puts cmd

  return cmd
end

.mysql_flags ⇒ Object

Default flags for mysql export



178
179
180
181
182
183
 
# File 'lib/exporter/sql_commands.rb', line 178

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