class SqliteMagic::Connection

Attributes

database[R]

Public Class Methods

new(db_loc='sqlite.db', options={}) click to toggle source
# File lib/sqlite_magic.rb, line 14
def initialize(db_loc='sqlite.db', options={})
  busy_timeout = options.delete(:busy_timeout)
  @database = SQLite3::Database.new(db_loc, options)
  @database.busy_timeout = busy_timeout if busy_timeout
end

Public Instance Methods

add_columns(tbl_name, col_names) click to toggle source
# File lib/sqlite_magic.rb, line 20
def add_columns(tbl_name, col_names)
  existing_cols = database.table_info(tbl_name).map{ |c| c['name'] }
  missing_cols = col_names.map(&:to_s) - existing_cols
  missing_cols.each do |col_name|
    database.execute("ALTER TABLE #{tbl_name} ADD COLUMN #{col_name}")
  end
end
close() click to toggle source
# File lib/sqlite_magic.rb, line 28
def close
  database.close
end
commit() click to toggle source
# File lib/sqlite_magic.rb, line 32
def commit
  database.commit
end
create_table(tbl_name, col_names, unique_keys=nil) click to toggle source
# File lib/sqlite_magic.rb, line 36
def create_table(tbl_name, col_names, unique_keys=nil)
  puts "Now creating new table: #{tbl_name}" if verbose?
  query = unique_keys ? "CREATE TABLE #{tbl_name} (#{col_names.join(',')}, UNIQUE (#{unique_keys.join(',')}))" :
                        "CREATE TABLE #{tbl_name} (#{col_names.join(',')})"
  database.execute query
  if unique_keys && !unique_keys.empty?
    query = "CREATE UNIQUE INDEX IF NOT EXISTS #{unique_keys.join('_')} " +
      "ON #{tbl_name} (#{unique_keys.join(',')})"
    database.execute query
  end
end
execute(query,data=nil) click to toggle source
# File lib/sqlite_magic.rb, line 48
def execute(query,data=nil)
  raw_response = data ? database.execute2(query, data) : database.execute2(query)
  keys = raw_response.shift # get the keys
  raw_response.map{|e| Hash[keys.zip(e)] }
rescue SQLite3::SQLException => e
  puts "Exception (#{e.inspect}) raised" if verbose?
  case e.message
  when /no such table/
    raise NoSuchTable.new(e.message)
  else
    raise e
  end
end
insert_or_update(uniq_keys, values_hash, tbl_name='main_table', opts={}) click to toggle source

This is an (expensive) convenience method to insert a row (for given unique keys), or if the row already exists

# File lib/sqlite_magic.rb, line 64
def insert_or_update(uniq_keys, values_hash, tbl_name='main_table', opts={})
  all_field_names = values_hash.keys
  field_names_as_symbol_string = all_field_names.map{ |k| ":#{k}" }.join(',') # need to appear as symbols
  sql_statement = "INSERT INTO #{tbl_name} (#{all_field_names.join(',')}) VALUES (#{field_names_as_symbol_string})"
  database.execute(sql_statement, values_hash)
rescue SQLite3::ConstraintException => e
  unique_key_constraint = uniq_keys.map { |k| "#{k}=:#{k}" }.join(' AND ')
  update_keys = values_hash.keys
  update_keys -= uniq_keys if !opts[:update_unique_keys]
  update_sql = update_keys.map { |k| "#{k}=:#{k}" }.join(', ')
  sql_statement = "UPDATE #{tbl_name} SET #{update_sql} WHERE #{unique_key_constraint}"
  database.execute sql_statement, values_hash
rescue SQLite3::SQLException => e
  puts "Exception (#{e.inspect}) raised" if verbose?
  case e.message
  when /no such table/
    create_table(tbl_name, all_field_names, uniq_keys)
    retry
  when /has no column/
    add_columns(tbl_name, all_field_names)
    retry
  else
    raise e
  end
end
save_data(uniq_keys, values_array, tbl_name) click to toggle source

save data into the database

# File lib/sqlite_magic.rb, line 91
def save_data(uniq_keys, values_array, tbl_name)
  values_array = [values_array].flatten(1) # coerce to an array
  all_field_names = values_array.map(&:keys).flatten.uniq
  all_field_names_as_string = all_field_names.join(',')
  all_field_names_as_symbol_string = all_field_names.map{ |k| ":#{k}" }.join(',') # need to appear as symbols
  begin
    values_array.each do |values_hash|
      # mustn't use nil value in unique value due to fact that SQLite considers NULL values to be different from
      # each other in UNIQUE indexes. See http://www.sqlite.org/lang_createindex.html
      raise DatabaseError.new("Data has nil value for unique key. Unique keys are #{uniq_keys}. Offending data: #{values_hash.inspect}") unless uniq_keys.all?{ |k| values_hash[k] }
      sql_query =  "INSERT OR REPLACE INTO #{tbl_name} (#{all_field_names_as_string}) VALUES (#{all_field_names_as_symbol_string})"
      database.execute(sql_query, values_hash)
    end
  rescue SQLite3::SQLException => e
    puts "Exception (#{e.inspect}) raised" if verbose?
    case e.message
    when /no such table/
      create_table(tbl_name, all_field_names, uniq_keys)
      retry
    when /has no column/
      add_columns(tbl_name, all_field_names)
      retry
    else
      raise e
    end
  end
end
verbose?() click to toggle source

Convenience method that returns true if VERBOSE environmental variable set (at the moment whatever it is set to)

# File lib/sqlite_magic.rb, line 120
def verbose?
  ENV['VERBOSE']
end