#2872 sqlite3 adapter drops :decimal columns precision & scale when migration tries to alter them - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#2872 incomplete

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

Reported by Andrey "Zed" Zaikin | July 6th, 2009 @ 10:42 AM | in 3.0.6

example migrations:

class M1 < ActiveRecord::Migration
  def self.up

create_table 'users' do |t|
  t.string 'name'
  t.decimal 'v1', :precision =&gt; 10, :scale =&gt; 3
end





  end



  def self.down

drop_table 'users'





  end
end



class M2 < ActiveRecord::Migration
  def self.up

change_column :users, :v1, :decimal, :precision =&gt; 12, :scale =&gt; 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  &quot;name&quot;
t.decimal &quot;v1&quot;,   :precision =&gt; 12, :scale =&gt; 5





  end
end

actual resulting schema:

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

t.string  &quot;name&quot;
t.decimal &quot;v1&quot;





  end
end

Comments and changes to this ticket

You flagged this item as spam.
Andrey "Zed" Zaikin July 6th, 2009 @ 10:46 AM
- Tag changed from “migrations, patch, schema, sqlite3” to “activerecord, active_record, migrations, patch, schema, sqlite3”
- rails_sqlite3_decimal_precision.diff 819 Bytes
You flagged this item as spam.
Taryn July 6th, 2009 @ 10:49 AM
I've hit this problem too.
Looking into the sqlite adaptor code, you can see that it's simply dropping any+all options.
Yehuda Katz (wycats) July 8th, 2009 @ 12:12 AM
- Milestone cleared.
- State changed from “new” to “incomplete”
- Assigned user set to “Yehuda Katz (wycats)”
Any chance I could get you to include a new test case for this?
You flagged this item as spam.
Paul Hinze July 20th, 2009 @ 07:43 PM
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.
- test_change_column_does_not_lose_decimal_precision_and_scale.patch 1.1 KB
You flagged this item as spam.
Taryn East July 22nd, 2009 @ 12:19 PM
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.
- sqlite_adapter_precision_scale_patch.diff 3 KB
You flagged this item as spam.
Taryn East July 22nd, 2009 @ 12:27 PM
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.
You flagged this item as spam.
Taryn East July 22nd, 2009 @ 01:27 PM
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...
You flagged this item as spam.
Taryn East July 22nd, 2009 @ 01:28 PM
hmmm... and "edit last comment" feature would be appreciated.. ;)
You flagged this item as spam.
Taryn East July 22nd, 2009 @ 05:04 PM
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.
- precision_scale_patch_two.diff 724 Bytes
You flagged this item as spam.
Matt Kern August 7th, 2009 @ 12:54 AM
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?
You flagged this item as spam.
Bryce Thornton August 9th, 2009 @ 08:38 PM
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.
You flagged this item as spam.
Matt Kern August 9th, 2009 @ 08:51 PM
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.
You flagged this item as spam.
Taryn East August 10th, 2009 @ 10:47 AM
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
Dan Pickett May 9th, 2010 @ 06:23 PM
- Tag changed from “activerecord, active_record, migrations, patch, schema, sqlite3” to “activerecord, active_record, bugmash, migrations, patch, schema, sqlite3”
Jeremy Kemper August 30th, 2010 @ 04:10 AM
- Milestone cleared.
- Importance changed from “” to “”
[bulk edit]
Ryan Bigg October 9th, 2010 @ 10:02 PM
- Tag cleared.
Automatic cleanup of spam.
Jeff Kreeftmeijer October 10th, 2010 @ 01:04 PM
- Title changed from “[PATCH] sqlite3 adapter drops :decimal columns precision & scale when migration tries to alter them” to “sqlite3 adapter drops :decimal columns precision & scale when migration tries to alter them”
- Tag set to “activerecord, patch, sqlite3”
Using the "patch" tag instead of prefixing the ticket title with "[PATCH]" to make sure patched tickets end up in the open patches bin. :)
Jeremy Kemper October 15th, 2010 @ 11:01 PM
- Milestone set to “3.0.2”
[bulk edit]
Jeff Kreeftmeijer November 7th, 2010 @ 04:53 PM
- Tag cleared.
Automatic cleanup of spam.
Santiago Pastorino November 15th, 2010 @ 09:04 PM
- Milestone cleared.
[bulk edit]
Santiago Pastorino February 12th, 2011 @ 09:58 PM
- Milestone set to “3.0.5”
[bulk edit]
Santiago Pastorino February 27th, 2011 @ 03:15 AM
- Milestone changed from “3.0.5” to “3.0.6”
[bulk edit]