Class: Wukong::Load::SQLLoader

Inherits:
Loader
  • Object
show all
Defined in:
lib/wukong-load/loaders/sql.rb

Overview

Loads data into SQL databases.

Uses the 'mysql' gem to connect and write data. Yes, MySQL != SQL but we'll get there, I promise...

Allows loading records into a given database and table. Records can have fields _database and _table which override the given database and table on a per-record basis.

Records can have an _id field which indicates an update, not an insert.

The names of these fields within each record (_database, _table, and _id) can be customized.

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from Loader

#process

Instance Attribute Details

#client ⇒ Object

The Mongo::MongoClient we'll use for talking to MongoDB.



57
58
59
 
# File 'lib/wukong-load/loaders/sql.rb', line 57

def client
  @client
end

Instance Method Details

#database_name_for(record) ⇒ Object

:nodoc:



130
131
132
 
# File 'lib/wukong-load/loaders/sql.rb', line 130

def database_name_for record
  identifier_for(record[database_field] || self.database)
end

#field_names_of(record) ⇒ Object

:nodoc:



110
111
112
 
# File 'lib/wukong-load/loaders/sql.rb', line 110

def field_names_of record
  record.keys.reject { |key| [database_field, table_field, id_field].include?(key) }.sort
end

#fields_and_values_of(record) ⇒ Object

:nodoc:



125
126
127
 
# File 'lib/wukong-load/loaders/sql.rb', line 125

def fields_and_values_of record
  field_names_of(record).map { |name| [identifier_for(name), value_for(record[name])].join('=') }.join(', ')
end

#fields_of(record) ⇒ Object

:nodoc:



115
116
117
 
# File 'lib/wukong-load/loaders/sql.rb', line 115

def fields_of record
  field_names_of(record).map { |name| identifier_for(name) }.join(', ')
end

#id_for(record) ⇒ Object

:nodoc:



156
157
158
 
# File 'lib/wukong-load/loaders/sql.rb', line 156

def id_for record
  value_for(record[id_field]) if record[id_field]
end

#identifier_for(thing) ⇒ Object

:nodoc:



140
141
142
 
# File 'lib/wukong-load/loaders/sql.rb', line 140

def identifier_for thing
  '`' + client.escape(thing.to_s) + '`'
end

#insert_query(record) ⇒ Object

:nodoc:



100
101
102
 
# File 'lib/wukong-load/loaders/sql.rb', line 100

def insert_query record
  "INSERT INTO #{database_name_for(record)}.#{table_name_for(record)} (#{fields_of(record)}) VALUES (#{values_of(record)}) ON DUPLICATE KEY UPDATE #{fields_and_values_of(record)}"
end

#load(record) ⇒ Object

Load a single record into the database.

If the record has an ID, we'll issue an update, otherwise an insert.

Parameters:

  • Hash (record) 


88
89
90
91
92
93
94
95
96
97
 
# File 'lib/wukong-load/loaders/sql.rb', line 88

def load record
  id = id_for(record)
  if id
    perform_query(update_query(record))
    log.info("Updated #{id}")
  else
    perform_query(insert_query(record))
    log.info("Inserted")
  end
end

#perform_query(query) ⇒ Object

:nodoc:



161
162
163
 
# File 'lib/wukong-load/loaders/sql.rb', line 161

def perform_query query
  client.query query
end

#setup ⇒ Object

Creates the client connection.



60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/wukong-load/loaders/sql.rb', line 60

def setup
  begin
    require 'mysql2'
  rescue LoadError => e
    raise Error.new("Please ensure that the 'mysql2' gem is installed and available (in your Gemfile)")
  end
  log.debug("Connecting to SQL server at <#{host}:#{port}> as <#{username}>#{' using password' if password}...")
  begin
    self.client = Mysql2::Client.new(sql_params)
  rescue => e
    raise Error.new(e)
  end
end

#sql_params ⇒ Object

:nodoc:



75
76
77
78
79
80
 
# File 'lib/wukong-load/loaders/sql.rb', line 75

def sql_params
  {:host => host, :port => port}.tap do |params|
    params[:username] if username
    params[:password] if password
  end
end

#table_name_for(record) ⇒ Object

:nodoc:



135
136
137
 
# File 'lib/wukong-load/loaders/sql.rb', line 135

def table_name_for record
  identifier_for(record[table_field] || self.table)
end

#update_query(record) ⇒ Object

:nodoc:



105
106
107
 
# File 'lib/wukong-load/loaders/sql.rb', line 105

def update_query record
  "UPDATE #{database_name_for(record)}.#{table_name_for(record)} SET #{fields_and_values_of(record)} WHERE `id`=#{id_for(record)}"
end

#value_for(thing) ⇒ Object

:nodoc:



145
146
147
148
149
150
151
152
 
# File 'lib/wukong-load/loaders/sql.rb', line 145

def value_for thing
  case thing
  when Fixnum then thing
  when nil    then 'NULL'
  else
    '"' + client.escape(thing.to_s) + '"'
  end
end

#values_of(record) ⇒ Object

:nodoc:



120
121
122
 
# File 'lib/wukong-load/loaders/sql.rb', line 120

def values_of record
  field_names_of(record).map { |name| value_for(record[name]) }.join(', ')
end