decimal field using sqlite

[Arthur MM]

hi, I am having some problem here with field Decimal

My Class:

require 'dm-core'

class Registry
include DataMapper::Resource

property :num, Decimal, :key => true, :precision => 20, :scale => 0
end

require 'dm-migrations'
DataMapper.finalize
DataMapper.setup(:default, 'sqlite::memory:')
DataMapper.auto_migrate!

My Test:

// I start irb
$ irb -r ./dm
/home/arthur/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/yaml.rb:56:in
`<top (required)>':
It seems your ruby installation is missing psych (for YAML output).
To eliminate this warning, please install libyaml and reinstall your
ruby.

// and create a new registry
ruby-1.9.3-p0 :001 > r = Registry.new(:num => 321512420108210029)
=> #<Registry @num=#<BigDecimal:9771c54,'0.3215124201 08210029E18',
18(27)>> // bigdecimal is showing correct num
ruby-1.9.3-p0 :002 > r.save
=> true
ruby-1.9.3-p0 :003 > Registry.all
=> [#<Registry @num=#<BigDecimal:98358e8,'0.3215124201 08210048E18',
18(27)>>] // num is wrong
ruby-1.9.3-p0 :004 >
Registry.all.first.num.to_i //
trying to convert to int
=>
321512420108210048 //
wrong value
ruby-1.9.3-p0 :005 >
BigDecimal.new(321512420108210029).to_i //
here, aparently bigdecimal is working correct
=> 321512420108210029

Anyone can help me ? What am I doing wrong ?
I try too on mysql, and I have the same problem.

Thanks

[Dan Kubb (dkubb)]

Hi Arthur,

hi, I am having some problem here with field Decimal

Can you try running the following script and paste the output: https://gist.github.com/2346702

I was able to repro it here using that script and I want to confirm it's the same problem you're seeing. I think it may be caused by the following line in the DataObjects driver:

  https://github.com/datamapper/do/blob/master/data_objects/lib/data_objects/quoting.rb#L37-39

What I'm thinking is that we need to add something to the case statement that returns the result of decimal.to_s('F') so that it can be handled properly by the underlying datastore. I haven't tested this, but that's the approach I'll probably test out when you respond with your results.

-- 

Dan

[Dan Kubb (dkubb)]

Hi Arthur,

It just occurred to me that this could also be a problem with SQLite storing the data using a double precision floating point column behind the scenes. If that's so, then there could be a loss of accuracy, and it would be possible for the value you write to the database is not the same as what is returned.

I'm still operating under the assumption that DM or DO is responsible for this, but it may end up this is a result of something outside of our control.

-- 

Dan 

[Arthur M. Meskelis]

Hi Dan, 

  First, the script's output (the same problem):

 ~ (0.000265) PRAGMA table_info("registries")

 ~ (0.000091) SELECT sqlite_version(*)

 ~ (0.000045) DROP TABLE IF EXISTS "registries"

 ~ (0.000016) PRAGMA table_info("registries")

 ~ (0.000324) CREATE TABLE "registries" ("num" DECIMAL(20, 0) NOT NULL, PRIMARY KEY("num"))

 ~ (0.000107) INSERT INTO "registries" ("num") VALUES (0.321512420108210029E18)

321512420108210029.0

 ~ (0.000077) SELECT "num" FROM "registries" ORDER BY "num" LIMIT 1

321512420108210048.0

  I have the same problem with mysql.

  I put my tests as a comment on  https://gist.github.com/2346702

So long

Arthur

2012/4/9 Dan Kubb (dkubb) <dan....@gmail.com>

--
You received this message because you are subscribed to the Google Groups "DataMapper" group.

To view this discussion on the web visit https://groups.google.com/d/msg/datamapper/-/0poxwET3fykJ.

To post to this group, send email to datam...@googlegroups.com.
To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.

--
------------------------------------------
 1. Notebook para você ficar livre e programar a vontade: R$ 2300
 2. Curso de Programação/SysAdmin/DBA: R$ 5000
 3. Pedir a solução pronta para um problema numa lista de discussão: Não tem preço !

E para todas as outras existe RTFM, STFW e  LMGTFY

[Arthur M. Meskelis]

Hi Dan,

So, is possible a new property, that inherit of Decimal, but, on save it execute to_i.to_s ? To me, the problem is that bigdecimal.to_s generates a number like  0.321512420108210029E18, mysql and sqlite3 don't know how to work with it, but to_i.to_s generates a number like 321512420108210030, and that number works. What do you think? I'm trying to do this, but without success https://gist.github.com/2361312 .

Thanks

2012/4/10 Arthur M. Meskelis <hallofr...@gmail.com>

[Dan Kubb (dkubb)]

Hi Arthur,

So, is possible a new property, that inherit of Decimal, but, on save it execute to_i.to_s ?

Sure, check out some of the types in dm-types to see how you make custom types.

I haven't tested it, but I'm guessing you'll be able to implement this type in probably no more than 5-10 lines of code.

-- 

Dan 

[Arthur M. Meskelis]

Hi Dan,

According to http://dev.mysql.com/doc/refman/5.0/en/precision-math-numbers.html, scientific notation is considered a approximate value numeric literal by MySQL (and possibly SQLite). As you mentioned, DO should convert the BigDecimal to a String using #to_s("F") so that a exact value numeric literal is used.

Regards,
Arthur

2012/4/15 Dan Kubb (dkubb) <dan....@gmail.com>

-- 

Dan 

--

You received this message because you are subscribed to the Google Groups "DataMapper" group.

To view this discussion on the web visit https://groups.google.com/d/msg/datamapper/-/u7HgcKDUY0EJ.

To post to this group, send email to datam...@googlegroups.com.
To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.