Module: LoadDataInfile::MySql

Defined in:
lib/load_data_infile.rb

Defined Under Namespace

Classes: Context

Constant Summary collapse

LOAD_DATA_INFILE_SQL = 
<<-SQL
  LOAD DATA <%= low_priority_or_concurrent %> <%= local %> INFILE <%= file_name %>
      <%= replace_or_ignore %>
      INTO TABLE <%= table_name %>
      <%= charset %>
      <%= fields_definitions %>
      <%= lines_defitions %>
      <%= ignores %>
      <%= columns %>
      <%= mappings %> ;
SQL

Instance Method Summary collapse

Instance Method Details

#disable_keys(table = quoted_table_name) ⇒ Object

Disables key updates for model table



15
16
17
 
# File 'lib/load_data_infile.rb', line 15

def disable_keys(table = quoted_table_name)
  connection.execute("ALTER TABLE #{table} DISABLE KEYS")
end

#enable_keys(table = quoted_table_name) ⇒ Object

Enables key updates for model table



20
21
22
 
# File 'lib/load_data_infile.rb', line 20

def enable_keys(table = quoted_table_name)
  connection.execute("ALTER TABLE #{table} ENABLE KEYS")
end

#load_data_infile(opt = {}) ⇒ Object

Load csv from a file using MySql's LOAD DATA INFILE You can set defaults for all these options using the accesor load_data_infile_defaults:

class ActiveRecord::Base load_data_infile_defaults = { :ignore => 1 } end

For details see: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Options:

path

CSV file path

charset

[OPTIONAL] Charset

columns

[OPTIONAL] Array of columns. Tries to use all columns if not provided. Use @dummy as column name to ignore a column. E.G.: (column_a, @column_b, @dummy)

concurrent

[OPTIONAL] True or false

enclosed_by

[OPTIONAL] Character

escaped_by

[OPTIONAL] Character

ignore

[OPTIONAL] Number, If provided, skips that number of lines.

lines_starting_by

[OPTIONAL] Character

lines_terminated_by

[OPTIONAL] Character

local

[OPTIONAL] true or fase. Defaults to true

low_priority

[OPTIONAL] true or false

mappings

[OPTIONAL] An array to map column values according to the mysql manual. E.G.: { :column_a => "TRIM(@column_b)"}

on_duplicates

[OPTIONAL] Action to perform when a duplicate row is found. Can be IGNORE or REPLACE

optionally_enclosed_by

[OPTIONAL] Character

table

[OPTIONAL] Table name. Defaults to quoted_table_name (won't work if used from an abstract class, e.g. ActiveRecord::Base')

terminated_by

[OPTIONAL] Character

disable_keys

[OPTIONAL] true or false. Defaults to true. Disables foreign keys while running the import.



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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/load_data_infile.rb', line 63

def load_data_infile(opt = {})
  options = (load_data_infile_defaults || Hash.new).merge(opt)

  disable_keys_option = !options.member?(:disable_keys) || options[:disable_keys]

  c = Context.new

  if options[:low_priority]
    c.low_priority_or_concurrent = :LOW_PRIORITY
  elsif options[:concurrent]
    c.low_priority_or_concurrent = :CONCURRENT
  end

  c.local = :LOCAL if !options.member?(:local) || options[:local]

  c.file_name = quote_value options[:path]

  c.replace_or_ignore = options[:on_duplicates] if [:REPLACE, :IGNORE].include?(options[:on_duplicates])

  c.table_name = options[:table] ? "`#{ options[:table] }`" : quoted_table_name

  c.charset = "CHARACTER SET #{options[:charset]}" if options[:charset]

  if options[:terminated_by] || options[:enclosed_by] || options[:optionally_enclosed_by] || options[:escaped_by]
    c.fields_definitions = " FIELDS " # or COLUMNS
    c.fields_definitions << " TERMINATED          BY '#{ options[:terminated_by] }' "          if options[:terminated_by]
    c.fields_definitions << " ENCLOSED            BY '#{ options[:enclosed_by] }' "            if options[:enclosed_by]
    c.fields_definitions << " OPTIONALLY ENCLOSED BY '#{ options[:optionally_enclosed_by] }' " if options[:optionally_enclosed_by]
    c.fields_definitions << " ESCAPED             BY '#{ options[:escaped_by] }' "             if options[:escaped_by]
  end

  if options[:lines_terminated_by] || options[:lines_starting_by]
    c.lines_defitions = " LINES "
    c.lines_defitions << " STARTING BY   '#{options[:lines_starting_by]}' "   if options[:lines_starting_by]
    c.lines_defitions << " TERMINATED BY '#{options[:lines_terminated_by]}' " if options[:lines_terminated_by]
  end

  c.ignores = "IGNORE #{options[:ignore].to_i} LINES" if options[:ignore].to_i > 0

  c.columns = " (#{options[:columns].join(", ")}) " if options[:columns]

  if options[:mappings] && options[:mappings].length > 0
    s = options[:mappings].map{|column, mapping| "#{column} = #{mapping}" }.join(", ")
    c.mappings = " SET #{s} "
  end

  disable_keys(c.table_name) if disable_keys_option

  connection.execute(ERB.new(LOAD_DATA_INFILE_SQL).result(c.binding).gsub(/^\s*\n/, ""))
ensure
  enable_keys(c.table_name) if disable_keys_option
end

#truncate_table(table = quoted_table_name) ⇒ Object

Deletes all rows in table very fast, but without calling destroy method nor any hooks.



10
11
12
 
# File 'lib/load_data_infile.rb', line 10

def truncate_table(table = quoted_table_name)
  connection.execute("TRUNCATE TABLE #{table}")
end

#with_keys_disabled(table = quoted_table_name) ⇒ Object

Disables keys, yields block, enables keys.



25
26
27
28
29
30
 
# File 'lib/load_data_infile.rb', line 25

def with_keys_disabled(table = quoted_table_name)
  disable_keys(table)
  yield
ensure
  enable_keys(table)
end