SQLite3 acting very strangely

good evening (and morning, also!) everyone. this is my first attempt
at using sqlite3 for a rails app, and so far, not so good

when I try to save a very basic record from the controller, it gives
me back an ActiveRecord::StatementInvalid error.

"that doesn't sound very nice"

so I switched the app to mysql and sure enough, it went through
without a hitch. unsatisfied, I switched back to sqlite3 and fired up
the console

  mike@lua:~/Rails/hotspots$ ./script/console
  Loading development environment.
  >> spot = Hotspot.new :title => "testing", :address => "222 water
st"
  => #<Hotspot:0xb732af48 @attributes={"title"=>"testing",
"notes"=>"NULL", "user_id"=>0, "lat"=>"NULL", "address"=>"222 water
st", "long"=>"NULL"}, @new_record=true>
  >> spot.save
  => true
  >>

"???"

and here's the source, right from the controller's mouth:

  def create
    if session[:user]
      spot = Hotspot.new :title => "testing", :address => "222 water st"
      spot.save
    end
  end

and here's what that gives me:

Processing ApiController#create (for 127.0.0.1 at 2007-05-09 23:30:52)
[POST]
  Session ID: b859a7e645f2c60ddd4747368a677b04
  Parameters: {"title"=>"asdf", "action"=>"create", "notes"=>"",
"controller"=>"api", "address"=>"asdf", "lat"=>"47.54501765940571",
"long"=>"-52.7204704284668"}
  User Load (0.000560) SELECT * FROM users WHERE (login = 'mikew'
and password = 'test') LIMIT 1
  SQL (0.000000) SQLite3::SQLException: SQL logic error or missing
database: INSERT INTO hotspots ("title", "notes", "user_id",
"address", "lat", "long") VALUES('testing', 'NULL', 0, '222 water st',
'NULL', 'NULL')

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error
or missing database: INSERT INTO hotspots ("title", "notes",
"user_id", "address", "lat", "long") VALUES('testing', 'NULL', 0, '222
water st', 'NULL', 'NULL'))

now I can't make any sense of what's going on

Have you checked your database.yml file? I suspect it might not be
set up properly.

The relevant section of database.yml should look like:
adapter: sqlite3
dbfile: db/development.db

Note that SQLite uses "dbfile" rather than "database" to point to the
database, and "dbfile" points to the actual file in the filesystem.
There's no "username" or "password" fields, since these aren't used by
SQLite.

For what it's worth, once you get these sort of issues sorted out with
SQLite, I've found it's an extremely robust solution for small Rails
sites.

Regards

Dave M.

the rails app was generated with `rails appname --database=sqlite3`,
so it created the database and setup the database config for me. you
can see that right above the troublesome query, it even loads data
from the user table.

the schema is all controlled by db:migrate as well, so the MySQL and
SQLite databases are identical

here is my database.yml file though:

mike@lua:~/Rails/hotspots$ cat config/database.yml
# SQLite version 3.x
# gem install sqlite3-ruby
development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000

# Warning: The database defined as 'test' will be erased and
# re-generated from your development database when you run 'rake'.
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  timeout: 5000

my guess is that the 'NULL' values are the clues.

null values in the database should come back as nil in rails

ex:

u = User.find(2)

=> #<User:0xb740fd54 @attributes={"created_on"=>"2007-04-30 06:34:09",
"last_login"=>nil, "name"=>"Bob Jones", "login_flag"=>"0",
"updated_on"=>"2007-04-30 06:34:09", "active_flag"=>"1", "id"=>"2",
"admin_flag"=>"1", "station"=>nil, "login"=>"bjones"}>

my guess is that you are storing the actual string NULL in the
database somehow when you shouldn't be and mysql is being forgiving
about it whereas sqlite3 is not.

look in your model or your migrations for where you might be setting
default values incorrectly.

other than that, nothing else jumps out as being strange or wrong.

oh, when I do fetch the record in the console, those null values come
back as nil for ruby

what I don't understand is how the same code can be used in the
console and the controller and work in one and not the other

the models are blank except for the has_many/belongs_to/habtm
relationships. and the migrations don't set any default values

Do you know which version of sqlite3 you're using?

This might help:

  http://weblog.rubyonrails.org/2007/1/29/using-sqlite3-with-rails

d.

well, something is causing the empty attributes to be set to "NULL"
instead of nil.

here's some data i have in an sqlite3 db

select * from topics where parent_id IS NULL limit 1;

id name url_name parent_id
---------- ---------- ---------- ----------
1 Technology technology

script/console

t = Topic.find(1)

=> #<Topic:0x382ce10 @attributes={"name"=>"Technology", "id"=>"1",
"url_name"=>"technology", "parent_id"=>nil}>

t = Topic.new :name => "Foo", :url_name => "foo"

=> #<Topic:0x382910c @new_record=true, @attributes={"name"=>"Foo",
"parent_id"=>nil, "url_name"=>"foo"}>

another thing you might try is to go into the sqlite3 console and
check out the schema for the table
$>sqlite3 db/development_db

.schema <table_name>

and see what the default is

let us know what you find out

oh sorry chris, you were right. I thought I saw it returning 'nil'
values, but it turns out it sets and returns NULL values

$ ./script/console
Loading development environment.

spot = Hotspot.new :title => "test", :address => "2 signal hill rd"

=> #<Hotspot:0xb73ebea0 @attributes={"created_on"=>nil,
"title"=>"test", "notes"=>"NULL", "user_id"=>0, "lat"=>"NULL",
"address"=>"2 signal hill rd", "long"=>"NULL"}, @new_record=true>

spot.save

=> true

Hotspot.find :first

=> #<Hotspot:0xb7364e28 @attributes={"created_on"=>"2007-05-11
11:43:13", "title"=>"test", "id"=>"1", "notes"=>"NULL",
"user_id"=>"0", "address"=>"2 signal hill rd", "lat"=>"NULL",
"long"=>"NULL"}>

here's the schema:

$ sqlite3 db/development.sqlite3
SQLite version 3.3.13
Enter ".help" for instructions

.schema hotspots

CREATE TABLE hotspots ("id" INTEGER PRIMARY KEY NOT NULL, "title"
varchar(255) DEFAULT NULL, "address" varchar(255) DEFAULT NULL,
"notes" varchar(255) DEFAULT NULL, "user_id" integer DEFAULT NULL,
"lat" varchar(255) DEFAULT NULL, "long" varchar(255) DEFAULT NULL,
"created_on" datetime DEFAULT NULL);

as you can see I'm using sqlite 3.3.13. that's no biggie though, I'll
try manually compiling 3.3.7 and the qslite3-ruby gem and see if that
works out

after installing sqlite3 3.3.7 and reinstalling sqlite3-ruby there's
still no change. I still get that big error in the controller and NULL
values in the console

also, this is interesting. here is the migration used to create the
table:

$ cat db/migrate/001_create_hotspots.rb
class CreateHotspots < ActiveRecord::Migration
  def self.up
    create_table :hotspots do |t|
        t.column :title, :string
        t.column :address, :string
        t.column :notes, :string
        t.column :user_id, :integer
        t.column :lat, :string
        t.column :long, :string
        t.column :created_on, :datetime
    end
  end

  def self.down
    drop_table :hotspots
  end
end

yet here's the schema.rb in the end

$ cat db/schema.rb
# This file is autogenerated. Instead of editing this file, please use
the
# migrations feature of ActiveRecord to incrementally modify your
database, and
# then regenerate this schema definition.

ActiveRecord::Schema.define(:version => 4) do
  ...
  create_table "hotspots", :force => true do |t|
    t.column "title", :string, :default => "NULL"
    t.column "address", :string, :default => "NULL"
    t.column "notes", :string, :default => "NULL"
    t.column "user_id", :integer, :default => 0
    t.column "lat", :string, :default => "NULL"
    t.column "long", :string, :default => "NULL"
    t.column "created_on", :datetime
  end
  ...
end

okay, I've cleaned up those NULL values. the problem was I had
ubuntu's libsqlite3-ruby1.8 package installed. once I uninstalled that
the values were being stored properly as nil. but I still get the
StatementInvalid error from the controller

Processing ApiController#create (for 127.0.0.1 at 2007-05-11 12:35:15)
[POST]
  Session ID: 74e977db220146ab742e2eb0811854d4
  Parameters: {"title"=>"asdf", "action"=>"create", "notes"=>"",
"controller"=>"api", "address"=>"asdf", "lat"=>"47.55857307874545",
"long"=>"-52.71583557128906"}
  User Load (0.000549) SELECT * FROM users WHERE (login = 'mikew'
and password = 'test') LIMIT 1
  SQL (0.000000) SQLite3::SQLException: SQL logic error or missing
database: INSERT INTO hotspots ("created_on", "title", "notes",
"user_id", "address", "lat", "long") VALUES('2007-05-11 12:35:15',
'testing', NULL, NULL, '222 water st', NULL, NULL)

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error
or missing database: INSERT INTO hotspots ("created_on", "title",
"notes", "user_id", "address", "lat", "long") VALUES('2007-05-11
12:35:15', 'testing', NULL, NULL, '222 water st', NULL, NULL))

at the sqlite3 command prompt run

.schema hotspots

and make sure the columns that are being inserted with NULL values can
take NULL.

also, cut/paste that query from the error into the prompt and see what happens

the only field that won't accept null is the `id` field

CREATE TABLE hotspots ("id" INTEGER PRIMARY KEY NOT NULL, "title"
varchar(255) DEFAULT NULL, "address" varchar(255) DEFAULT NULL,
"notes" varchar(255) DEFAULT NULL, "user_id" integer DEFAULT NULL,
"lat" varchar(255) DEFAULT NULL, "long" varchar(255) DEFAULT NULL,
"created_on" datetime DEFAULT NULL);

I cut and pasted the failing code from the error message into the
sqlite prompt and it went through without a hitch

$ sqlite3 db/development.sqlite3
SQLite version 3.3.7
Enter ".help" for instructions

SELECT * FROM hotspots;
INSERT INTO hotspots ("created_on", "title", "notes",

"user_id", "address", "lat", "long") VALUES('2007-05-11 18:42:52',
'testing', NULL, NULL, '222 water st', NULL, NULL);

SELECT * FROM hotspots;

1|testing|222 water st|||||2007-05-11 18:42:52

Mike,

It still looks to me as though the database.yml is wrong. I think

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000

should be

development:
  adapter: sqlite3
  dbfile: db/development.sqlite3
  timeout: 5000

and similarly for test and production.

Regards,

Andrew

actually either works, however :database is the preferred option due
to consistency.

http://dev.rubyonrails.org/ticket/2644

I posted on the Rails Trac, but that doesn't seem to be going anywhere
either
<http://dev.rubyonrails.org/ticket/8360>

so for now, the app has switched to MySQL