Topic: SQLite3 decimal values: No restrictions on :precision and :scale

I'm stuck a little bit with how to implement the validation for a decimal attribute when using SQLit"3 DB. It seems like it does not support options defined in the migration like other do:

class CreateOperations < ActiveRecord::Migration
  def change
    create_table :operations do |t|
      t.decimal :rate, precision: 5, scale: 2 #scale and precision options do not work !

      t.timestamps
    end
  end
end

I tried to play with the validation defined as follows in the model, but it does not work:

class Operation < ActiveRecord::Base
  attr_accessible :rate
  VALID_DECIMAL_REGEX = /^\d+(\.|,)?(\d{0,2})?$/
  validates :rate, presence: true, format: { with: VALID_DECIMAL_REGEX }
  
  before_save :format_decimal_value
  
  private
  def format_decimal_value
    self.rate = rate.to_s.sub(/,/, '.').to_f    
  end
end

What I'm trying to achieve - is just to validate the User input for rate field which should be ONLY a decimal value. The problem is that when using French local, the comma is used as a separator for decimal part.
I tried to pass different values from the rails console:

rails c --sandbox

irb(main):001:0> o = Operation.new
=> #<Operation id: nil, rate: nil, created_at: nil, updated_at: nil>
irb(main):002:0> o.rate = 1.98
=> 1.98
irb(main):003:0> o.save
   (0.0ms)  SAVEPOINT active_record_1
remove comma from 1.98 => to 1.98
  SQL (46.9ms)  INSERT INTO "operations" ("created_at", "rate", "updated_at") VALUES (?, ?, ?)  [["created_at", Mon, 24
Sep 2012 12:28:14 UTC +00:00], ["rate", #<BigDecimal:21d85a0,'0.198E1',18(45)>], ["updated_at", Mon, 24 Sep 2012 12:28:1
4 UTC +00:00]]
   (0.0ms)  RELEASE SAVEPOINT active_record_1
=> true
irb(main):004:0> o.rate.to_s
=> "1.98"
irb(main):005:0> o.rate = "1.75"
=> "1.75"
irb(main):006:0> o.save
   (0.0ms)  SAVEPOINT active_record_1
remove comma from 1.75 => to 1.75
   (0.0ms)  UPDATE "operations" SET "rate" = 1.75, "updated_at" = '2012-09-24 12:28:38.635595' WHERE "operations"."id" =
 1
   (0.0ms)  RELEASE SAVEPOINT active_record_1
=> true
irb(main):007:0> o.rate.to_s
=> "1.75"
irb(main):008:0>
irb(main):009:0* o.rate = "1,28"
=> "1,28"
irb(main):010:0> o.save
   (0.0ms)  SAVEPOINT active_record_1
remove comma from 1.0 => to 1.0
   (0.0ms)  UPDATE "operations" SET "rate" = 1.0, "updated_at" = '2012-09-24 12:29:12.900781' WHERE "operations"."id" =
1
   (0.0ms)  RELEASE SAVEPOINT active_record_1
=> true
irb(main):011:0> o.rate.to_s
=> "1.0"
irb(main):012:0>

As you see any attempt to pass a value with comma separated decimal part results in saving 1.0 as rate value.
Is it possible to solve that without using a Javascript library (which would remove the comma just after leaving the text field of 'rate')?
Thank you.

Last edited by Javix (2012-09-25 03:04:12)

Re: SQLite3 decimal values: No restrictions on :precision and :scale

Hi Javix,
I could be wrong on this as I haven't really done much with foreign character sets but I have found rthat scale and precision work very well but only if delared in a specific way  as per AWDWR

 add_column amount, :decimal, :precision => 8, :scale => 2

Bit of a long shot I know, but it might be worth re running your mogrations with the scale and precision redefined

Otherwise you could convert to string and strip out anything other than numerics and decimal. I would have thought that the number format would take care of commas ertc... in the view though

your problem seems very odd

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: SQLite3 decimal values: No restrictions on :precision and :scale

Thanks James, for your help.
I don't really see the difference between my way of migration definition and the yours one, except hash syntax smile
May be, when I implement the French locale, the conversion will happen out of the box?
Actually, when I enter 1,75 value in the rate text field, the log shows it already changed for 1.0:

Last edited by Javix (2012-09-27 07:39:09)

Re: SQLite3 decimal values: No restrictions on :precision and :scale

That indicates truncation of the decimal places. Is this happening in all modes? Does the field in the database actual match what you think it is?
What happens if you enter data directly into the table through a dbms rather than through Rails - Firefox sqlite plugin is your firiend smile ?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: SQLite3 decimal values: No restrictions on :precision and :scale

I took a try with SQL plugin for Firefox. Here are the results I got on PC with Windows XP French:

Modifying records previously saved via Rails application:

#entered a comma separated value
Are you sure you want to execute the following statement(s):

UPDATE "main"."operations" SET "rate" = ?1 WHERE  "id" = 2
Parameters:
param 1 (text): 1,98
1,98

#entering a dot separated value


Are you sure you want to execute the following statement(s):

UPDATE "main"."operations" SET "rate" = ?1 WHERE  "id" = 1
Parameters:
param 1 (real): 1.14

#Results display after executing the query:

SELECT rate FROM operations

"1.14"
"2,15"

I checked the structure of the 'rate' column, it is declared as decimal (5,2) what corresponds to the migration code:

class CreateOperations < ActiveRecord::Migration
  def change
    create_table :operations do |t|
      t.decimal :rate, precision: 5, scale: 2

      t.timestamps
    end
  end
end

Now I'm really surprised that SQLite had accepted both values, with comma and dot sad

Re: SQLite3 decimal values: No restrictions on :precision and :scale

Is sqlite going to be the production dbms?
If not (and I hope not!) then I suggest you switch your development to use whatever RDBMS you will be using in production. That way you will be confident of your expected results when you go live then test again and see what happens

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: SQLite3 decimal values: No restrictions on :precision and :scale

OK, I'll try to switch to the PG and see what will be going on, thank you.

Re: SQLite3 decimal values: No restrictions on :precision and :scale

Good luck. Think I'm going to take some time out to test this for myself

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)