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
17
18
 
# 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

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 



20
21
22
23
24
25
26
 
# 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 ⇒ Object 



28
29
30
 
# File 'lib/sqlite_magic.rb', line 28

def close
  database.close
end

#commit ⇒ Object 



32
33
34
 
# File 'lib/sqlite_magic.rb', line 32

def commit
  database.commit
end

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



36
37
38
39
40
41
42
43
44
45
46
 
# 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) ⇒ Object 



48
49
50
51
52
53
54
55
56
57
58
59
60
 
# 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 = {}) ⇒ Object

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



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# 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) ⇒ Object

#save data into the database



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
116
117
 
# 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? ⇒ Boolean

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

Returns:

  • (Boolean) 


120
121
122
 
# File 'lib/sqlite_magic.rb', line 120

def verbose?
  ENV['VERBOSE']
end