CODEWITHSUNDEEP
ruby-on-railssqlite
Dean Brundage
Dean Brundage

Reputation: 2048

sqlite3 can't find a record by attribute

I have a strange little Rails problem. Imagine an empty Invitation class inheriting from ActiveRecord::Base. There is one Invitation in the sqlite3 database but ActiveRecord can't find it by its token attribute. Follow along as we try to find it and see if you can figure out why.

0 urzatron work/sunrise % rails c                          
Loading development environment (Rails 3.1.0)

irb(main):001:0> Invitation.all
  Invitation Load (0.5ms)  SELECT "invitations".* FROM "invitations" 
=> [#<Invitation id: 1, accepted_at: nil, assignment_id: 1, contact: "[email protected]", network: "email", sender_id: 1, sent_at: "2011-11-07 02:50:10", token: "41a673223a2b059b212ca3753896d3fd38e1d1dd">]

irb(main):002:0> Invitation.find_by_token "41a673223a2b059b212ca3753896d3fd38e1d1dd"
  Invitation Load (0.8ms)  SELECT "invitations".* FROM "invitations" WHERE "invitations"."token" = '41a673223a2b059b212ca3753896d3fd38e1d1dd' LIMIT 1
=> nil

irb(main):003:0> Invitation.find 1
  Invitation Load (82.7ms)  SELECT "invitations".* FROM "invitations" WHERE "invitations"."id" = ? LIMIT 1  [["id", 1]]
=> #<Invitation id: 1, accepted_at: nil, assignment_id: 1, contact: "[email protected]", network: "email", sender_id: 1, sent_at: "2011-11-07 02:50:10", token: "41a673223a2b059b212ca3753896d3fd38e1d1dd">

irb(main):004:0> Invitation.where( :token => "41a673223a2b059b212ca3753896d3fd38e1d1dd" ).first
  Invitation Load (0.6ms)  SELECT "invitations".* FROM "invitations" WHERE "invitations"."token" = '41a673223a2b059b212ca3753896d3fd38e1d1dd' LIMIT 1
=> nil

So, that's a little weird.

0 urzatron work/sunrise % rails dbconsole
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> select * from invitations;
1||1|[email protected]|email|1|2011-11-07 02:50:10.818984|41a673223a2b059b212ca3753896d3fd38e1d1dd

sqlite> select * from invitations where token = "41a673223a2b059b212ca3753896d3fd38e1d1dd";

sqlite>

Here's the migration for the curious.

class CreateInvitations < ActiveRecord::Migration
  def change
    create_table :invitations do |t|
      t.datetime :accepted_at
      t.references :assignment
      t.string :contact, :null => false
      t.string :network, :null => false
      t.references :sender, :null => false
      t.datetime :sent_at
      t.string :token, :null => false
    end
    add_index :invitations, :accepted_at
    add_index :invitations, [:network,:token]
    add_index :invitations, :token
    add_column :users, :invitation_id, :integer
  end
end

Upvotes: 1

Views: 397

Answers (3)

Jeff Ancel
Jeff Ancel

Reputation: 3091

I ran into this issue just yesterday, and it turned out that changing the 'token' type to a string instead of text. Once I did this, SQLite3 was then able to make the select properly.

So in the migration (or alter if you will), I hadn't released yet, so I just re-migrated:

t.text :token

would change to

t.string :token

All the best.

Upvotes: 0

G&#233;al
G&#233;al

Reputation: 1482

I just encountered the same bug (I think). I generated my tokens with SecureRandom.hex(8) and couldn't do a *find_by_token*.

If I replace the token generation by SecureRandom.urlsafe_base64(8), the bug is fixed.

See https://github.com/NoamB/sorcery/issues/19 for more information.

Upvotes: 1

sethvargo
sethvargo

Reputation: 26997

So, it's 1 of 2 things:

  1. Strings are automatically being truncated by sqlite for some reason
  2. Token is a reserved word in Rails 3.1 and it's just undocumented (yet)

Upvotes: 0

Related Questions