module Myreplicator::SqlCommands
Public Class Methods
db_configs(db)
click to toggle source
Db configs for active record connection
# File lib/exporter/sql_commands.rb, line 50 def self.db_configs db ActiveRecord::Base.configurations[db] end
dump_flags()
click to toggle source
Default dump flags
# 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)
click to toggle source
Builds SQL needed for incremental exports
# 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)
click to toggle source
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 );
# 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)
click to toggle source
Mysql export data into outfile option Provided for tables that need special delimiters
# 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)
click to toggle source
Gets the Maximum value for the incremental column of the export job
# 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)
click to toggle source
# 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)
click to toggle source
Mysql exports using -e flag
# 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)
click to toggle source
Export using outfile \0 delimited terminated by newline Location of the output file needs to have 777 perms
# 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()
click to toggle source
Default flags for mysql export
# File lib/exporter/sql_commands.rb, line 264 def self.mysql_flags {"column-names" => false, "quick" => true, "reconnect" => true } end
mysqldump(*args)
click to toggle source
# 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)
click to toggle source
Configs needed for SSH connection to source server
# File lib/exporter/sql_commands.rb, line 58 def self.ssh_configs db Myreplicator.configs[db] end