Class: NcsNavigator::Warehouse::Transformers::SqlTransformer

Inherits:
Object
  • Object
show all
Extended by:
Forwardable
Includes:
StringifyTrace
Defined in:
lib/ncs_navigator/warehouse/transformers/sql_transformer.rb

Overview

A transformer that executes raw SQL (most likely INSERTs or UPDATEs) on the warehouse working database during ETL. It captures and reports affected record counts and errors.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from StringifyTrace

stringify_trace

Constructor Details

#initialize(configuration, options = {}) ⇒ SqlTransformer

Creates a new SqlTransformer. You must provide at exactly one of the :statements, :file, or :script options. If you provide more than one the behavior of this transformer is undefined.

Parameters:

  • configuration (Configuration)
  • options (Hash<Symbol, Object>) (defaults to: {})

Options Hash (options):

  • :statements (Array<String>) —

    a list of SQL statements already separated into strings. These strings will be sent as-is to the database (no further processing).

  • :file (String) —

    the name of a file from which to read semicolon-separated SQL statements. The file will be separated into statements using extract_statements.

  • :script (String) —

    a string containing semicolon-separated SQL statements. The string will be separated into statements using extract_statements.

  • :name (String) —

    the name to use when describing this transformer. A default will be generated if not provided.

  • :adapter (a datamapper adapter object) —

    the adapter against which to execute the statements. The default is nearly always fine; this option is provided for testing.



59
60
61
62
63
64
65
 
# File 'lib/ncs_navigator/warehouse/transformers/sql_transformer.rb', line 59

def initialize(configuration, options={})
  @configuration = configuration
  @statements = select_statements(options)
  @name = options.delete(:name) ||
    "SQL Transformer with #{statements.size} statement#{statements.size == 1 ? '' : 's'}"
  @adapter = options.delete(:adapter)
end

Instance Attribute Details

#name ⇒ Object (readonly)

Returns the value of attribute name.



35
36
37
 
# File 'lib/ncs_navigator/warehouse/transformers/sql_transformer.rb', line 35

def name
  @name
end

#statements ⇒ Object (readonly)

Returns the value of attribute statements.



35
36
37
 
# File 'lib/ncs_navigator/warehouse/transformers/sql_transformer.rb', line 35

def statements
  @statements
end

Class Method Details

.extract_statements(s) ⇒ Object

Splits a string into separate SQL statements on semicolons. It attempts to avoid splitting on semicolons that are in string literals, though it only currently supports standard SQL string literals completely. It will not properly handle PostgreSQL E'' string literals that contain backslash-escaped single quotes.



19
20
21
22
23
24
25
26
27
28
 
# File 'lib/ncs_navigator/warehouse/transformers/sql_transformer.rb', line 19

def extract_statements(s)
  Treetop.load File.expand_path('../sql/sql_statements.treetop', __FILE__)

  parser = Sql::SqlStatementsParser.new
  if result = parser.parse(s)
    result.statements
  else
    fail "Parse failed: #{parser.failure_reason}"
  end
end

Instance Method Details

#transform(transform_status)

This method returns an undefined value.

Executes the configured SQL statments, one at time, stopping at the first error.

Parameters:



73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/ncs_navigator/warehouse/transformers/sql_transformer.rb', line 73

def transform(transform_status)
  stmt_ct = statements.size
  statements.each_with_index do |stmt, i|
    log.info("Executing SQL statement in SQL transformer: \n#{stmt}")
    shell.clear_line_and_say("[#{name}] Executing statement #{i + 1}/#{stmt_ct}...")
    begin
      result = adapter.execute(stmt)
      transform_status.record_count += result.affected_rows
      log.debug("Previous statement affected #{result.affected_rows} row#{result.affected_rows == 1 ? '' : 's'}.")
    rescue Exception => e
      transform_status.transform_errors << NcsNavigator::Warehouse::TransformError.
        for_exception(e, "Exception while executing SQL statement \"#{stmt}\" (#{i + 1} of #{stmt_ct}).")
      shell.clear_line_and_say("[#{name}] Failed on #{i + 1}/#{stmt_ct}.\n")
      log.error("Previous statement failed with exception.\n#{e.class}: #{e}\n#{stringify_trace(e.backtrace)}")
      return
    end
  end
  shell.clear_line_and_say("[#{name}] Executed #{stmt_ct} SQL statement#{stmt_ct == 1 ? '' : 's'}.\n")
end