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