#4 new
bahuvrihi

SQL Hash

Reported by bahuvrihi | December 12th, 2008 @ 11:17 AM

You might be able to use external in combination with an sqlite database to implement an external hash.

Here is a sketch.


require 'rubygems'
require 'sqlite3'

module External
  module SqlUtils
    module_function
    
    def tables(db)
      db.execute(%Q{SELECT name FROM sqlite_master WHERE type = 'table' AND NOT name = 'sqlite_sequence'}).map do |row|
        row[0]
      end
    end
  end
  
  class SqlHash
  
    attr_reader :db, :table_name
    
    def initialize(path, table_name='sql_hash')
      @db = SQLite3::Database.new(path)
      @table_name = table_name
    end
    
    # Closes the db connection
    def close
      db.close
    end
    
    # True if the tablename table exists in db.
    def table_exists?
      SqlUtils.tables(db).include?(table_name)
    end
    
    def table_structure
      structure = db.execute("PRAGMA table_info(#{table_name})")
      raise("Could not find table '#{table_name}'") if structure.empty?
      structure
    end
    
    def create_table(options={})
      options = {
        :primary_key => :key,
        :columns => [['key', 'string'],['value', 'string']]
      }
      
      columns = options[:columns].collect do |definition|
        definition.join(' ')
      end
      columns << %Q{PRIMARY KEY (#{options[:primary_key]})}
      
      db.execute %Q{CREATE TABLE #{table_name} (#{columns.join(', ')});}
    end
    
    def [](key)
      results = db.execute(%Q{SELECT value FROM #{table_name} WHERE key='#{key}';})
      results.empty? ? nil : results[0][0]
    end
    
    def find(flag, conditions)
    end
    
    def []=(key, value)
      db.execute %Q{REPLACE INTO #{table_name} (key,value) VALUES ('#{key}', '#{value}');}
    end
    
    def merge!(hash)
      db.transaction do
        hash.each_pair do |key, value|
          db.execute %Q{REPLACE INTO #{table_name} (key,value) VALUES ('#{key}', '#{value}');}
        end
      end
    end
    
  end
end

# require 'rubygems'
# require 'sqlite3'
# 
# db_file = "data.db"
# db_exists = File.exists?(db_file)
# db = SQLite3::Database.new( db_file )
# 
# if !db_exists
#   db.execute( "create table hash (key string, value string)" )
# end
# 
# start = Time.now
# 
# 0.upto(10) do |factor|
#   db.transaction do
#     (factor * 100000).upto((factor+1) * 100000) do |key|
#       db.execute "INSERT INTO hash (key, value) VALUES ('#{key}','#{key}:#{key}')"
#     end
#   end
# end
# 
# puts "#{Time.now-start}s"
# 
# db.close

require File.join(File.dirname(__FILE__), '../external_test_helper.rb') 
require 'external/sql_hash'
require 'tap/test/file_methods'

class SqlHashTest < Test::Unit::TestCase
  acts_as_file_test
  
  attr_reader :sql
  def setup
    super
    @sql = External::SqlHash.new(method_tempfile)
  end
  
  def teardown
    sql.close
  end
  
  def test_table_exists_is_false_if_table_does_not_exist
    assert !sql.table_exists?
  end  
  
  def test_create_table
    assert !sql.table_exists?
    sql.create_table
    assert sql.table_exists?
  end
  
  def test_get_set
    sql.create_table
    
    assert_nil sql['key']
    sql['key'] = 'value'
    assert_equal 'value', sql['key']
    
    sql['key'] = 'alt'
    assert_equal 'alt', sql['key']
  end
end

No comments found

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Indexing and array-like access to data stored on disk (rather than in memory).

People watching this ticket

Pages