Class: SqliteMagic::Connection

Inherits:
Object
  • Object
show all
Defined in:
lib/sqlite_magic.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_loc = 'sqlite.db') ⇒ Connection

Returns a new instance of Connection.



14
15
16
 
# File 'lib/sqlite_magic.rb', line 14

def initialize(db_loc='sqlite.db')
  @database = SQLite3::Database.new(db_loc)
end

Instance Attribute Details

#database ⇒ Object (readonly)

Returns the value of attribute database.



13
14
15
 
# File 'lib/sqlite_magic.rb', line 13

def database
  @database
end

Instance Method Details

#add_columns(tbl_name, col_names) ⇒ Object 



18
19
20
21
22
23
24
 
# File 'lib/sqlite_magic.rb', line 18

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 ⇒ Object 



26
27
28
 
# File 'lib/sqlite_magic.rb', line 26

def close
  database.close
end

#commit ⇒ Object 



30
31
32
 
# File 'lib/sqlite_magic.rb', line 30

def commit
  database.commit
end

#create_table(tbl_name, col_names, unique_keys = nil) ⇒ Object 



34
35
36
37
38
39
 
# File 'lib/sqlite_magic.rb', line 34

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
end

#execute(query, data = nil) ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/sqlite_magic.rb', line 41

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 = 'ocdata') ⇒ Object

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



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/sqlite_magic.rb', line 57

def insert_or_update(uniq_keys, values_hash, tbl_name='ocdata')
  field_names_as_symbol_string = values_hash.keys.map{ |k| ":#{k}" }.join(',') # need to appear as symbols

  sql_statement = "INSERT INTO #{tbl_name} (#{values_hash.keys.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 - uniq_keys).map { |k| "#{k}=:#{k}" }.join(', ')
  sql_statement = "UPDATE #{tbl_name} SET #{update_keys} WHERE #{unique_key_constraint}"
  database.execute sql_statement, values_hash
rescue SQLite3::SQLException => e
  puts "Exception (#{e.inspect}) raised: #{sql_statement}" if verbose?
  # defer to save_data, which can handle missing tables, columns etc
  save_data(uniq_keys, values_hash, tbl_name)
end

#save_data(uniq_keys, values_array, tbl_name) ⇒ Object

#save data into the database



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/sqlite_magic.rb', line 74

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? ⇒ Boolean

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

Returns:

  • (Boolean) 


103
104
105
 
# File 'lib/sqlite_magic.rb', line 103

def verbose?
  ENV['VERBOSE']
end