Class: ETL::Control::DatabaseDestination

Inherits:
Destination show all
Defined in:
lib/etl/control/destination/database_destination.rb

Overview

Destination which writes directly to a database. This is useful when you are dealing with a small amount of data. For larger amounts of data you should probably use the bulk loader if it is supported with your target database as it will use a much faster load method.

Instance Attribute Summary collapse

Attributes inherited from Destination

#append_rows, #buffer_size, #condition, #configuration, #control, #mapping, #unique

Instance Method Summary collapse

Methods inherited from Destination

class_for_name, #current_row, #errors, #write

Constructor Details

#initialize(control, configuration, mapping = {}) ⇒ DatabaseDestination

Initialize the database destination

  • control: The ETL::Control::Control instance

  • configuration: The configuration Hash

  • mapping: The mapping

Configuration options:

  • :database: The database name (REQUIRED)

  • :table: The table to write to (REQUIRED)

  • :truncate: Set to true to truncate before writing (defaults to false)

  • :unique: Set to true to only insert unique records (defaults to false)

  • :adapter: The adapter to use (defaults to :mysql)

  • :username: The database username (defaults to ‘root’)

  • :password: The password to the database (defaults to nothing)

  • :host: The host for the database (defaults to ‘localhost’)

  • :append_rows: Array of rows to append

Mapping options:

  • :order: The order of fields to write (REQUIRED)

Raises:



33
34
35
36
37
38
39
40
 
# File 'lib/etl/control/destination/database_destination.rb', line 33

def initialize(control, configuration, mapping={})
  super
  @truncate = configuration[:truncate] ||= false
  @unique = configuration[:unique]
  @order = mapping[:order] || order_from_source
  raise ControlError, "Order required in mapping" unless @order
  connect
end

Instance Attribute Details

#order ⇒ Object (readonly)

Specify the order from the source



9
10
11
 
# File 'lib/etl/control/destination/database_destination.rb', line 9

def order
  @order
end

#truncate ⇒ Object (readonly)

Set to true to truncate the destination table first



12
13
14
 
# File 'lib/etl/control/destination/database_destination.rb', line 12

def truncate
  @truncate
end

Instance Method Details

#close ⇒ Object

Close the connection



72
73
74
75
76
 
# File 'lib/etl/control/destination/database_destination.rb', line 72

def close
  buffer << append_rows if append_rows
  flush
  ETL::ActiveRecord::Base.connection.disconnect!
end

#flush ⇒ Object

Flush the currently buffered data



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/etl/control/destination/database_destination.rb', line 43

def flush
  conn = ETL::ActiveRecord::Base.connection
  conn.transaction do
    conn.truncate(configuration[:table]) if truncate
    
    buffer.flatten.each do |row|
      # check to see if this row's compound key constraint already exists
      # note that the compound key constraint may not utilize virtual fields
      next unless row_allowed?(row)

      # add any virtual fields
      add_virtuals!(row)
      
      names = []
      values = []
      order.each do |name|
        names << name
        values << conn.quote(row[name]) # TODO: this is probably not database agnostic
      end
      q = "INSERT INTO #{configuration[:table]} (#{names.join(',')}) VALUES (#{values.join(',')})"
      ETL::Engine.logger.debug("Executing insert: #{q}")
      conn.insert(q, "Insert row #{current_row}")
      @current_row += 1
    end
    buffer.clear
  end
end