sqlite3 adapter drops :decimal columns precision & scale when migration tries to alter them

I've hit this problem too.
Looking into the sqlite adaptor code, you can see that it's simply dropping any+all options.

Here's a test case that fails without the above patch and passes when it is applied. It tests that you can use change_column to alter a column's scale and precision. The attributes are dropped in the code without this patch, and they are properly changed when it is applied.

Ok, when I click on Paul's patch I get some sort of key error, so here's my own patch that includes a test that fails with the current Rails code, as well as Adnrey's code - which makes it pass.

Note - I've made the test-case specific to sqlite3 as that's where the issue is located.

I meant to add... but technically the test should pass on all adapters. Only I can't test Postgres - because I don't have that installed.
it runs fine for all the other standard adapters ie it doesn't fail when I remove the adapter-check line except for the postgres section which doesn't run at all.

Ok - that's weird. Looks like running a migration with this sets the local values for precision/scale... but doesn't actually change the real db properly.
You can test that with a migration that changes two columns' precisions:

class M£ < ActiveRecord::Migration
def self.up

change_column :users, :v1, :decimal, :precision => 12, :scale => 5
change_column :users, :v2, :decimal, :precision => 12, :scale => 5

end

def self.down end end

expected resulting schema:

ActiveRecord::Schema.define(:version => 20090706090649) do
create_table "users", :force => true do |t|

t.string  "name"
t.decimal "v1",   :precision => 12, :scale => 5
t.decimal "v2",   :precision => 12, :scale => 5

end end

actual resulting schema:

ActiveRecord::Schema.define(:version => 20090706090649) do
create_table "users", :force => true do |t|

t.string  "name"
t.decimal "v1"
t.decimal "v2",   :precision => 12, :scale => 5

end end

If you reverse the order of two change_column names in the migration... you get:

ActiveRecord::Schema.define(:version => 20090706090649) do
create_table "users", :force => true do |t|

t.string  "name"
t.decimal "v1",   :precision => 12, :scale => 5
t.decimal "v2"

end end

ie - only the last migration seems to actually change the schema...

hmmm... and "edit last comment" feature would be appreciated.. ;)

Hokay... so after much digging there is one important fact we need to remember.
SQLite doesn't actually (natively) support :precision/scale.

The troubles that my company ran into were more based on the fact that in doing the migration... the schema lost the precision/scale - which means that when we uploaded into a NON-sqlite instance of the db... we then ran into issues.

I'm not sure how to test for this as we don't have Rails tests (that I see) where we dump the schema from one adaptor, then try to schema:load into another.

In any case, I ran across one other place where the precision/scale are "lost" in the sqlite adaptor... adding the following patch seemed to solve that... but I can't officially test it.

Any comments/suggestions welcome.

So if I understand this correctly, the schema should retain the precision and scale, but the sqlite3 adapter should ignore it when creating the columns, correct?

I've ran into this as well. Yes, it would be nice for the schema to retain the precision and scale for when we actually load it on a production database.

I think it's more than nice, it's required. The recommendation to load from schema rather than running thru the migrations on a production deploy or any new instance deploy makes this pretty high priority.

Matt - yep - that's the ticket.
The patches I've submitted do this now.

We ran into the issue because during testing we used Sqlite - but production (of course) is on a real db... and loading the schema started causing all sorts of truncation errors in our money columns :P