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', options = {}) ⇒ Connection

Returns a new instance of Connection.



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

def initialize(db_loc='sqlite.db', options={})
  @database = SQLite3::Database.new(db_loc, options)
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
40
41
42
43
44
 
# 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
  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) ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/sqlite_magic.rb', line 46

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 = {}) ⇒ Object

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



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/sqlite_magic.rb', line 62

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

#save data into the database



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/sqlite_magic.rb', line 89

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) 


118
119
120
 
# File 'lib/sqlite_magic.rb', line 118

def verbose?
  ENV['VERBOSE']
end