Hi, I'm having an odd problem whereby I'm trying to store a hash in an
sqlite VARCHAR(40) column like so:
ruby-1.9.2-head :001 > require "digest/sha1"
=> true
ruby-1.9.2-head :002 > require "sqlite3"
=> true
ruby-1.9.2-head :003 > db = SQLite3::Database.new "test.db"
=> #<SQLite3::Database:0x00000100942388>
ruby-1.9.2-head :004 > the_hash = Digest::SHA1.hexdigest("hello")
=> "aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d"
ruby-1.9.2-head :005 > db.execute("insert into invitations (token)
VALUES (?)", the_hash)
=> []
but I can't retrieve by searching the for the hash
ruby-1.9.2-head :006 > db.execute("select token from invitations where
token = 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'")
=> []
unless I use lower(token)
ruby-1.9.2-head :006 > db.execute("select token from invitations where
lower(token) = 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'")
=> ["aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d"]
The reason it appears is that sqlite3-ruby inserts the hash as type
blob but them I'm trying to read it as type text so I get nothing.
sqlite $ sqlite3 test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select typeof(token) from invitations;
blob
sqlite>
Here is the dump:
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE invitations (
token varchar(40)
);
INSERT INTO "invitations"
VALUES(X'61616634633631646463633565386132646162656465306633623438326364396165613934333464');
So, is there a way that I can force sqlite3-ruby to insert as a string
so that I can read it back by finding by the same string?
More importantly (kinda), is there a way I can do it through
activerecord:
class Invitation < ActiveRecord::Base
before_create :generate_token
def generate_token
self.token = Digest::SHA1.hexdigest([Time.now, rand].join)
end
end
Here's the (messy) stackoverflow thread I made to try and figure this
out:
http://stackoverflow.com/questions/6674644/is-there-are-reason-i-select-by-a-string-columns-which-contains-sha1-hexdigests/6679717#6679717
And Here is the subsequent chat with one of the users where we figured
out what was happening:
http://chat.stackoverflow.com/transcript/1397