Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #4605 (closed defect: duplicate)

Opened 3 years ago

Last modified 2 years ago

[PATCH] fixed-precision data type -- ActiveRecord is forcing use of float for currency

Reported by: robbat2@gentoo.org Assigned to: David
Priority: normal Milestone: 1.2
Component: ActiveRecord Version: 1.1.0
Severity: critical Keywords: needs_review
Cc: tom@popdog.net, bitsweat

Description

This issue can be a showstopper for many projects willing to use rails.

We really need support for a fixed-precision data type in order to have reliable currency-handling. We're forced to use float for currency because we want to use rails.

Float data type should be avoided like the plague when dealing with currency like dollars.

Some people might say, 'just use BigDecimal or leetsofts Money' but that doesn't solve the problem of SQL statements using float and having to do extra work everywhere (anti-DRY and fragile).

In theory, adding a 'numeric' aka 'decimal' fixed-precision data type should be easy for someone familiar with ActiveRecord. This is supported by all the rdbms I've seen.

The benefit would be huge for those of us dealing with currency in systems using rails.

Using float is a nightmare for currency of a magnitude that can only be understood by those who tried it in complex systems. Please don't force us rails users to use float for currency.

Attachments

rails-svn-add-decimal-type.diff (30.3 kB) - added by robbat2@gentoo.org on 05/10/06 07:22:18.
Initial patch for decimal/numeric support.
rails-svn-add-decimal-type.2.diff (28.2 kB) - added by robbat2@gentoo.org on 05/10/06 07:27:21.
Slight cleanup of patch.

Change History

05/10/06 06:08:25 changed by robbat2@gentoo.org

I'm busy implementing this using BigDecimal, and I've got it working on mysql/postgres/sqlite so far. I do however have a question about what the default behavior should be with regards to precision/scale.

I'm writing this to store high-precision sensor readings.

In the database adapters, should I use the actual limitations of the database in the :limit clause, or the default behavior of the database?

The SQL standard says the default scale should be 0, scale <= precision, and makes no comments about the requirements of precision.

MySQL: precision [1..63], scale [0..30]. Default is (10,0). PostGresql: precision [1..infinity], scale [0..infinity]. No default. Oracle: precision [1..38], scale [-84..127]. Default is (38,0). DB2: precision [1..63], scale [0..62]. Default unknown. Sqlite: Any precision and scale may be used. No default. Firebird: precision [1..18], scale [0..18]. Default (9,0). *Numeric and Decimal have different storage rules, decimal being better. FrontBase: precision [1..38], scale [0..38]. Default (38,0). *Max precision/scale for NUMERIC is 19, and DECIMAL is 38. SqlServer: precision [1..38], scale [0..38]. Default (38,0). Sybase: precision [1..38], scale [0..38]. Default (38,0). OpenBase: Unknown. Documentation site not accessible.

So, all I need is this question answered, and then I'll post up my patch here.

05/10/06 06:10:52 changed by robbat2@gentoo.org

Argh, it ate my formatting.

  • The SQL standard says the default scale should be 0, scale <= precision, and makes no comments about the requirements of precision.
  • MySQL: precision [1..63], scale [0..30]. Default is (10,0).
  • PostGres: precision [1..infinity], scale [0..infinity]. No default.
  • Oracle: precision [1..38], scale [-84..127]. Default is (38,0).
  • DB2: precision [1..63], scale [0..62]. Default unknown.
  • Sqlite: Any precision and scale may be used. No default.
  • Firebird: precision [1..18], scale [0..18]. Default (9,0). WARNING Numeric and Decimal have different storage rules, decimal being better.
  • FrontBase: precision [1..38], scale [0..38]. Default (38,0). WARNING Max precision/scale for NUMERIC is 19, and DECIMAL is 38.
  • SqlServer: precision [1..38], scale [0..38]. Default (38,0).
  • Sybase: precision [1..38], scale [0..38]. Default (38,0).
  • OpenBase: Unknown. Documentation site not accessible presently.

05/10/06 07:22:18 changed by robbat2@gentoo.org

  • attachment rails-svn-add-decimal-type.diff added.

Initial patch for decimal/numeric support.

05/10/06 07:26:46 changed by robbat2@gentoo.org

Ok, my initial patch is attached. Something in the MySQL portion is broken again, it's casting to float somewhere, and then back to BigDecimal, losing precision :-(.

The testcases are part of migrations_test (which also had a bug in how assert_equals was used for bob) and validations_test.

05/10/06 07:27:21 changed by robbat2@gentoo.org

  • attachment rails-svn-add-decimal-type.2.diff added.

Slight cleanup of patch.

05/23/06 10:46:29 changed by tomafro

  • cc set to tom@popdog.net.

06/03/06 11:39:31 changed by anonymous

  • cc changed from tom@popdog.net to tom@popdog.net mislav@nippur.irb.hr.

06/21/06 04:28:25 changed by robbat2@gentoo.org

Please close this ticket, and mark as a duplicate of 5454, which contains my merged patch.

06/21/06 18:41:06 changed by bitsweat

  • cc changed from tom@popdog.net mislav@nippur.irb.hr to tom@popdog.net, mislav@nippur.irb.hr, bitsweat.
  • status changed from new to closed.
  • summary changed from Please add fixed-precision data type -- ActiveRecord is forcing use of float for currency to [PATCH] fixed-precision data type -- ActiveRecord is forcing use of float for currency.
  • resolution set to duplicate.
  • reporter changed from anonymous to robbat2@gentoo.org.

Thank you. Superseded by #5454.

10/04/06 14:16:28 changed by mislav

  • cc changed from tom@popdog.net, mislav@nippur.irb.hr, bitsweat to tom@popdog.net, bitsweat.