SQLite3 id not being updated immediately after save

Rails
#1

Summary
For some reason right after I save a model to the db, the id says
‘0’…yet next transaction the db properly has it listed as the correct
id.

Details:

I have a SQLite3 DB with the following schema:
CREATE TABLE projects (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
address TEXT NOT NULL,
created_on INTEGER,
updated_on INTEGER
);

I have a corresponding Project model.

In one of my controllers, I have the following code:
project = Project.new
project.address = @params[ ‘address’ ]
if project.save
p “Assigning id: #{project.id}!!!”
# …stuff that relies on the id being correct…
end

When I run that controller/view, I see “Assigning id: 0!!!” in the
output.
When I then do a Project.find_all, or query the DB directly, it
properly has an id of 1.

This occurs for all projects created by that view (not just the first).
The id 0 problem occurs not just using project.save directly in a
controller, but also when using the after_create or after_save
callbacks inside the model.

Is this a known problem with SQLite3? Do I need to bite the bullet and
let rails tell me what kind of DB to use?

Or is this a misunderstanding on my part? Is there something else I
need to do to get the actual ID of the row that I’ve just created? (My
desire is to then create a bunch of other models as defaults that are
associated in a has_many relationship with the Project.)

#2

I’ve got something of a test case in the console here:

Slim:~/Sites/SpecBook gavinkistner$ script/console
Loading development environment.

Project.find_all
=> []

proj = Project.new( :address=>‘foo’, :project_status_id=>1 )
=> #<Project:0x1379438 @new_record=true,
@attributes={“created_on”=>nil, “project_status_id”=>1,
“updated_on”=>nil, “address”=>“foo”}>

if proj.save then p proj end
#<Project:0x1379438 @new_record_before_save=true,
@errors=#<ActiveRecord::Errors:0x1356244 @errors={},
@base=#<Project:0x1379438 …>>, @new_record=false,
@project_status=#<ProjectStatus:0x134bb28
@attributes={“name”=>“Internal”, “id”=>“1”}>,
@attributes={“created_on”=>nil, “project_status_id”=>1,
“updated_on”=>Sun Oct 29 21:16:13 -0700 2006, “id”=>0,
“address”=>“foo”}>
=> nil

proj.id
=> 0

Project.find_all
=> [#<Project:0x131d868 @attributes={“created_on”=>nil,
“project_status_id”=>“1”, “updated_on”=>“1162181773”, “id”=>“3”,
“address”=>“foo”}>]

What’s going on here? Is this expected behavior?

#3

On 10/29/06, Phrogz [email protected] wrote:

“updated_on”=>nil, “address”=>“foo”}>

proj.id
=> 0

Project.find_all
=> [#<Project:0x131d868 @attributes={“created_on”=>nil,
“project_status_id”=>“1”, “updated_on”=>“1162181773”, “id”=>“3”,
“address”=>“foo”}>]

What’s going on here? Is this expected behavior?

I ran into this recently. The fix I did is is below.

The odd thing is, on two different, but exactly configured systems,
one would show this problem, and the other would work fine.

I never received any responses to my emails, but ended up digging this
solution, after several days.

Hope it helps.

For anyone else who happens to run into this. Apparently, something
changed somewhere to cause sqlite3 and ruby to not work (some details
here:
http://scottstuff.net/blog/articles/2006/07/01/sqlite-3-and-ruby-finally-working-on-os-x
)

I ended up installing SWIG from source (I don’t have Darwinports
installed), and removed and reinstalled the sqlite3 gem and it’s all
working again.

#4

John T. wrote:

I ran into this recently. The fix I did is is below.

The odd thing is, on two different, but exactly configured systems,
one would show this problem, and the other would work fine.

I never received any responses to my emails, but ended up digging this
solution, after several days.
[snip]

Fantastic, that did it. Thanks!
(Also on Mac OS X, 10.4.)

I think I even read something that said “You need SWIG installed”, and
then when the gem installed OK I said “Huh…I guess I had it
installed.”

For the record, I did “sudo gem uninstall sqlite3-ruby”, then
downloaded the SWIG project and built it locally, and then ran “sudo
gem install sqlite3-ruby”.

Items properly have their ID now after saving.

Be nice if someone savvier than I could figure out the root cause of
this (endianness?) and fix it so that no one else runs into the trouble.