module ArJdbc::Vertica

Constants

ADAPTER_NAME
INSERT_TABLE_EXTRACTION
NATIVE_DATABASE_TYPES
TIMESTAMP_COLUMNS

Public Class Methods

current_time() click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 75
def self.current_time
  ::ActiveRecord::Base.default_timezone == :utc ? ::Time.now.utc : ::Time.now
end

Public Instance Methods

adapter_name() click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 79
def adapter_name
  ADAPTER_NAME
end
add_index(*args) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 83
def add_index(*args)
  # no op
end
bulk_insert(table_name, primary_key, sequence_name, column_names, data) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 87
def bulk_insert(table_name, primary_key, sequence_name, column_names, data)
  sql = "INSERT INTO #{table_name} (#{column_names.join(',')},#{primary_key || 'id'}) #{$/}"
  row_count = data.size
  last_index = row_count - 1
  temp_table_name = vertica_random_temp_table_name
  column_types = vertica_column_types_for(table_name, column_names)

  sql << "SELECT #{temp_table_name}.*, #{sequence_name}.nextval FROM ("
  data.each_with_index do |data_row, index|
    sql << "SELECT #{vertica_bulk_insert_select_for(column_types, data_row)} "

    unless index == last_index
      sql << "#{$/} UNION ALL #{$/}"
    end
  end
  sql << ") #{temp_table_name};"

  execute(sql)
end
columns(table_name, name = nil) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 107
def columns(table_name, name = nil)
  sql = "SELECT * from V_CATALOG.COLUMNS WHERE table_name = '#{table_name}';"
  raw_columns = execute(sql, name || "SCHEMA")

  columns = raw_columns.map do |raw_column|
    ::ActiveRecord::ConnectionAdapters::VerticaColumn.new(
      raw_column['column_name'],
      raw_column['column_default'],
      raw_column['data_type_id'],
      raw_column['data_type'],
      raw_column['is_nullable'], 
      raw_column['is_identity']
    )
  end

  return columns
end
create_sequence(sequence_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 138
    def create_sequence(sequence_name)
      sql = <<-SQL
        CREATE SEQUENCE #{sequence_name};
      SQL

      return execute(sql)
    end
create_table(table_name, options = {}) click to toggle source

Override create_table to create the sequences needed to manage primary keys

Calls superclass method
# File lib/arjdbc/vertica/adapter.rb, line 129
def create_table(table_name, options = {})
  super

  sequence_name = sequence_name_for(table_name, options[:primary_key] || "id")
  unless sequence_exists?(sequence_name)
    create_sequence(sequence_name)
  end
end
drop_sequence(sequence_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 146
    def drop_sequence(sequence_name)
      sql = <<-SQL
        DROP SEQUENCE #{sequence_name};
      SQL

      return execute(sql)
    end
drop_table(table_name, options = {}) click to toggle source
Calls superclass method
# File lib/arjdbc/vertica/adapter.rb, line 154
def drop_table(table_name, options = {})
  super

  sequence_name = sequence_name_for(table_name, options[:primary_key] || "id")
  if sequence_exists?(sequence_name)
    drop_sequence(sequence_name)
  end
end
exec_insert(sql, name, binds, primary_key = nil, sequence_name = nil) click to toggle source

Vertica JDBC does not work with JDBC GET_GENERATED_KEYS so we need to execute the sql raw and then lookup the LAST_INSERT_ID() that occurred in this “session”

# File lib/arjdbc/vertica/adapter.rb, line 168
def exec_insert(sql, name, binds, primary_key = nil, sequence_name = nil)
  # Execute the SQL
  execute(sql, name, binds)
end
extract_table_ref_from_insert_sql(sql) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 173
def extract_table_ref_from_insert_sql(sql)
  match_data = INSERT_TABLE_EXTRACTION.match(sql)
  match_data[:table_name].strip if match_data[:table_name]
end
native_database_types() click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 178
def native_database_types
  NATIVE_DATABASE_TYPES
end
next_insert_id_for(sequence_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 182
def next_insert_id_for(sequence_name)
  return select_value("SELECT NEXTVAL('#{sequence_name}');")
end
next_sequence_value(sequence_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 186
def next_sequence_value(sequence_name)
  next_insert_id_for(sequence_name)
end
prefetch_primary_key?(table_name = nil) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 190
def prefetch_primary_key?(table_name = nil)
  true
end
primary_key_for(table_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 194
def primary_key_for(table_name)
  primary_keys(table_name)
end
primary_keys(table_name) click to toggle source

Vertica should “auto-discover” the primary key if marked on the table

# File lib/arjdbc/vertica/adapter.rb, line 201
def primary_keys(table_name)
  @primary_keys ||= {}
  return @primary_keys[table_name] if @primary_keys[table_name]

  keys = self.execute("SELECT column_name FROM v_catalog.primary_keys WHERE table_name = '#{table_name}';")
  @primary_keys[table_name] = [ keys.first && keys.first['column_name'] ]
  @primary_keys[table_name]
end
quote_table_name_for_assignment(table, attr) click to toggle source

Vertica does not allow the table name to prefix the columns when setting a value, this is not a pleasant work-around, but it works

# File lib/arjdbc/vertica/adapter.rb, line 214
def quote_table_name_for_assignment(table, attr)
  quote_column_name(attr)
end
remove_index(*args) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 222
def remove_index(*args)
  # no op
end
rename_column(table_name, column_name, new_column_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 218
def rename_column(table_name, column_name, new_column_name)
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
end
rename_index(*args) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 226
def rename_index(*args)
  # no op
end
sequence_exists?(sequence_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 230
    def sequence_exists?(sequence_name)
      sql = <<-SQL
        SELECT 1 from v_catalog.sequences WHERE sequence_name = '#{sequence_name}';
      SQL

      sequence_present = select_value(sql)
      return sequence_present == 1
    end
sequence_name_for(table_name, primary_key_name = nil) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 239
def sequence_name_for(table_name, primary_key_name = nil)
  "#{table_name}_#{primary_key_name || 'id'}_seq"
end
vertica_bulk_insert_select_for(column_types, data_row) click to toggle source

Custom Vertica methods to allow bulk insert operations on a db engine that does not support multi-insert

# File lib/arjdbc/vertica/adapter.rb, line 247
def vertica_bulk_insert_select_for(column_types, data_row)
  insert_values = data_row.each_with_index.map do |value, index|
    "#{quote(value)}::#{column_types[index]}"
  end

  return insert_values.join(",")
end
vertica_column_type_for(table_name, column_name) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 259
def vertica_column_type_for(table_name, column_name)
  column = vertica_memoized_columns(table_name).find { |column| column.name == "#{column_name}" }
  return column.sql_type if column
  return nil
end
vertica_column_types_for(table_name, column_names) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 255
def vertica_column_types_for(table_name, column_names)
  column_names.map { |column_name| vertica_column_type_for(table_name, column_name) }
end
vertica_memoized_columns(table_name, name = nil) click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 265
def vertica_memoized_columns(table_name, name = nil)
  @vertica_memoized_columns ||= {}
  normalized_table_name = "#{table_name}"

  unless @vertica_memoized_columns.has_key?(normalized_table_name)
    @vertica_memoized_columns[normalized_table_name] = columns(table_name, name)
  end

  return @vertica_memoized_columns[normalized_table_name]
end
vertica_random_temp_table_name() click to toggle source
# File lib/arjdbc/vertica/adapter.rb, line 276
def vertica_random_temp_table_name
  # Generate a Random "table_name" to prevent collisions (not sure if needed)
  "temporary_table_#{::SecureRandom.hex}"
end