This project is archived and is in readonly mode.

#2872 incomplete
Andrey "Zed" Zaikin

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

  • Andrey "Zed" Zaikin

    Andrey "Zed" Zaikin July 6th, 2009 @ 10:46 AM

    • Tag changed from migrations, patch, schema, sqlite3 to activerecord, active_record, migrations, patch, schema, sqlite3
  • Taryn

    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)

    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?

  • Paul Hinze

    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.

  • Taryn East

    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.

  • Taryn East

    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.

  • Taryn East

    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...

  • Taryn East

    Taryn East July 22nd, 2009 @ 01:28 PM

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

  • Taryn East

    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.

  • Matt Kern

    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?

  • Bryce Thornton

    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.

  • Matt Kern

    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.

  • Taryn East

    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

    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

    Jeremy Kemper August 30th, 2010 @ 04:10 AM

    • Milestone cleared.
    • Importance changed from “” to “”
  • Ryan Bigg

    Ryan Bigg October 9th, 2010 @ 10:02 PM

    • Tag cleared.

    Automatic cleanup of spam.

  • Jeff Kreeftmeijer

    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

    Jeremy Kemper October 15th, 2010 @ 11:01 PM

    • Milestone set to 3.0.2
  • Jeff Kreeftmeijer

    Jeff Kreeftmeijer November 7th, 2010 @ 04:53 PM

    • Tag cleared.

    Automatic cleanup of spam.

  • Santiago Pastorino
  • Santiago Pastorino
  • Santiago Pastorino

    Santiago Pastorino February 27th, 2011 @ 03:15 AM

    • Milestone changed from 3.0.5 to 3.0.6

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Pages