Depot app, adding price

Hi,
I’m working my way through the book, I’m at the step where we add a price column, and am getting an error I don’t know what to do with:

ray-schamps-computer:~/Sites/ror/depot Ray$ rake db:migrate        
(in /Users/Ray/Sites/ror/depot)
== 2 AddPriceToProduct: migrating =============================================
-- add_column(:products, :price, :decimal, {:precision=>8, :default=>0, :scale=>2})
rake aborted!
SQLite3::SQLException: near "ADD": syntax error: ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0

(See full trace by running task with --trace)

After it didn’t work initially, I copied and pasted from the download link:

class AddPriceToProduct < ActiveRecord::Migration
  def self.up
    add_column :products, :price, :decimal,
      :precision => 8, :scale => 2, :default => 0
  end

  def self.down
    remove_column :products, :price
  end
end

Can someone tell me what’s up?

Thanks,
Ray

PS I wish this forum comments box had a preview.

Try these commands directly against sqlite3. Can you tell me if they work? What version of sqlite are you running?

$ sqlite3 test.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> CREATE TABLE products ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "image_url" varchar(255) DEFAULT NULL, "created_at" datetime DEFAULT NULL, "updated_at" datetime DEFAULT NULL);
sqlite> ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0;
sqlite> .exit

Thanks for responding!

Apparently I am running 3.1.3, so since you have 3.4.2 I will look into upgrading. Here’s what I get:

ray-schamps-computer:~/Sites/ror/depot Ray$ sqlite3 test.db
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> CREATE TABLE products ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "image_url" varchar(255) DEFAULT NULL, "created_at" datetime DEFAULT NULL, "updated_at" datetime DEFAULT NULL);
sqlite> ALTER TABLE products ADD "price" decimal(8,2) DEFAULT 0;
SQL error: near "ADD": syntax error
sqlite> ALTER TABLE products ADD [price] decimal(8,2) DEFAULT 0;
SQL error: near "ADD": syntax error
sqlite> .exit

After I updated sqlite, your tests work, but rails gives the same error. In my searches online I found a few other people with the similar problems if a program shipped expecting sqlite3 > 3.1, but didn’t warn OS 10.4 users to upgrade. It’s strange that the SQL syntax would have changed between versions…

I will see if rails sees the new version of sqlite after a restart.

Update: no, it was not a restart it needed, but a sudo gem install sqlite3-ruby. Works now!

Thanks a lot,
Ray

It looks like support for ALTER TABLE ADD COLUMN was added to SQLite3 in version 3.2.0.

I have found this : On Mac OS X 10.4 Tiger, try uninstalling the gem (sudo gem uninstall sqlite3), then use DarwinPorts to install SWIG (sudo port install swig), then re-install the SQLite3 gem (sudo gem install sqlite3).

If you can verify that this works for you, I’ll update the instructions in the book.

Someone else will have to confirm this because I already built the latest version of sqlite3 and replaced my old binary with that one. It was pretty painless. Thanks for your help though.

I have found a few strange areas of the beta book as I’m reading it straight through—is it useful for people to post copy errors to this forum, or do you have proofreaders for that?

There will be proofreaders, but any errors that are reported will be addressed, and undoubtedly will make the copyeditors jobs easier.

Ray:

However, it’s probably easier both for you and for us to log errors in the errata pages—just click the link at foot of each page and you’ll be taken there.

Dave

hi, I am having the same problems with the ADD command with sqlite3 3.1.3. and the instruction above with the SWIG and to upgrade sqlite3 on macos 10.4 does not work for me.

The way I fixed it was to download the latest stable release of sqlite3 for 10.4 and build it from the source, which was easy, no snags:

untar the package
make a new directory in the folder that holds the source
cd into that, and run >../configure
Let it go, and then run > make

Note what > sqlite3 --version and >which sqlite3 returns

run > make install and see if > sqlite3 --version returns something different. If it’s the new version, you’re all set. If it’s the old one still, run > which sqlite3, and this should return the location of the new binary. I just replaced my old binary with the new one, and then it worked after that.

Sorry if these instructions are confusing, I’m sure there’s a better way to do this.

-Ray