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 



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
 
# 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)] }
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



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/sqlite_magic.rb', line 49

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



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/sqlite_magic.rb', line 66

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)



95
96
97
 
# File 'lib/sqlite_magic.rb', line 95

def verbose?
  ENV['VERBOSE']
end