class Aqueduct::Wrappers::Mysql

Public Instance Methods

column_values(table, column) click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 152
def column_values(table, column)
  error = ''
  result = []
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
    column_found = false
    db_connection.query("DESCRIBE #{table}").each do |field|
      column_found = true if field['Field'] == column
    end
    if column_found
      results = db_connection.query("SELECT `#{column}` as 'column', count(*) FROM #{table} GROUP BY `#{column}`;")
      results.each do |row|
        if row['column'].class != String and row['column'].respond_to?('round') and row['column'].round == row['column']
          result << row['column'].round
        else
          result << row['column']
        end
      end
    end
  rescue Mysql2::Error => e
    error = "Error: #{e.inspect}"
  ensure
    db_connection.close if db_connection
  end
  { result: result, error: error }
end
connect() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 12
def connect
  @db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
end
connected?() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 31
def connected?
  result = false
  error = ''
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
  rescue Mysql2::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    result = true if db_connection
    db_connection.close if db_connection
  end
  { result: result, error: error }
end
count(query_concepts, conditions, tables, join_conditions, concept_to_count) click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 179
def count(query_concepts, conditions, tables, join_conditions, concept_to_count)
  result = 0
  error = ''
  sql_conditions = ''
  begin
    t = Time.now
    if tables.size > 0
      sql_conditions = "SELECT count(#{concept_to_count ? 'DISTINCT ' + concept_to_count : '*'}) as record_count FROM #{tables.join(', ')} WHERE #{join_conditions.join(' and ')}#{' and ' unless join_conditions.blank?}#{conditions}"
      Rails.logger.info sql_conditions
      db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
      if db_connection
        results = db_connection.query(sql_conditions)
        results.each do |row|
          result = row['record_count']
        end
      end
    else
      error = "Database [#{@source.name}] Error: No tables for concepts. Database not fully mapped."
    end
  rescue Mysql2::Error => e
    error = "Database [#{@source.name}] Error: #{e}"
  ensure
    db_connection.close if db_connection
  end
  { result: result, error: error, sql_conditions: sql_conditions }
end
disconnect() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 16
def disconnect
  @db_connection.close if @db_connection
  true
end
get_all_values_for_column(table, column) click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 114
def get_all_values_for_column(table, column)
  values = []
  error = ''
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
    if db_connection
      column_found = false
      db_connection.query("DESCRIBE #{table}").each do |field|
        column_found = true if field['Field'] == column
      end
      if not column_found
        error += " <i>#{column}</i> does not exist in <i>#{@source.database}.#{table}</i>"
      else
        results = db_connection.query("SELECT `#{column}` FROM #{table};")
        results.each do |row|
          row.values.each do |value|

            if value.class != String and value.respond_to?('round') and value.round == value
              values << value.round
            else
              values << value
            end
          end
        end
      end
    end
  rescue Mysql2::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    if db_connection
      db_connection.close
    else
      error += " unable to connect to <i>#{@source.name}</i>"
    end
  end
  { values: values, error: error }
end
get_table_metadata() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 45
def get_table_metadata
  result = {}
  error = ''
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
    if db_connection
      tables = []
      results = db_connection.query("SHOW TABLES;")
      results.each do |row|
        row.values.each do |table|
          tables << table
        end
      end

      tables.sort{|table_a, table_b| table_a.downcase <=> table_b.downcase}.each do |my_table|
        results = db_connection.query("SHOW COLUMNS FROM #{my_table}")
        columns = []
        results.each do |row|
          columns << {column: row['Field'], datatype: row['Type']}
        end
        result[my_table] = columns.sort{|a,b| a[:column].downcase <=> b[:column].downcase}
      end
    end
  rescue Mysql2::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    db_connection.close if db_connection
  end
  { result: result, error: error }
end
query(sql_statement) click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 21
def query(sql_statement)
  results = []
  total_count = 0
  if @db_connection
    results = @db_connection.query(sql_statement, as: :array)
    total_count = results.each.size
  end
  [results, total_count]
end
sql_codes() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 8
def sql_codes
  { text: 'CHAR(255)', numeric: nil, open: '`', close: '`' } # Using "5.4" + 0.0 to convert
end
table_columns(table) click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 97
def table_columns(table)
  columns = []
  error = ''
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
    if db_connection
      results = db_connection.query("SHOW COLUMNS FROM #{table}")
      results.each { |row| columns << {column: row['Field'], datatype: row['Type']} }
    end
  rescue Mysql2::Error => e
    error = "Error retrieving column information. Please make sure that this database is configured correctly."
  ensure
    db_connection.close if db_connection
  end
  { columns: columns, error: error }
end
tables() click to toggle source
# File lib/aqueduct/wrappers/mysql.rb, line 76
def tables
  tables = []
  error = ''
  begin
    db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port)
    if db_connection
      results = db_connection.query("SHOW TABLES;")
      results.each do |row|
        row.values.each do |table|
          tables << table
        end
      end
    end
  rescue Mysql2::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    db_connection.close if db_connection
  end
  { result: tables, error: error }
end