Implement Numeric and Money types

[xxgreg]

There is currently no Dart type for storing arbitrary precision real numbers. Perhaps for the meantime implement parsing and store it in a numeric class.

class Numeric {
final int value;
final int scale;
}

Note this is VM specific, as Javascript doesn't support arbitrary precision integers.

[brianoh]

Hi,

I've written a Class for handling decimal in Dart which I have used in a number of programs. I will probably release it some time when I know Dart better. There is another one on Github written just after I wrote mine. With mine and I guess the other, decimal is held external to the class as a decimal string. To use it with my class, there is the function "assign" which converts the decimal string to an internal integer with decimal places stored (eg. 2). For example "123.67" would be stored internally as integer "12367" with implied decimals 2. The Class is only about 75 lines of code.

I have used this decimal Class with your package for Postgres. I just have to convert the decimal number in your List from the Select to a string, and then using my Class do an assign which makes it numeric. Using my Class, I then do any addition/subtraction/formatting needed. To update the database, I then use the Class to return the value as a decimal string, which I then use to update the database.

For example this is the code I used in my test:

oDb.execute("Start Transaction").then((oResult){
oDb.query("SELECT ikey, dbalance FROM test01 ORDER BY RANDOM() Limit 1 For Update")
.toList()
.then((lResult){
...............
String sBalance = "${lResult[0][1]}"; // lResult is the Rows from Select
dgAmount.assign(sBalance, 2); // dgAmount is my decimal Class instantiation (2 decimals)
String sAmount = "123.77";
dgAmount.add(sAmount); // dgAmount now contains the new amount
sAmount = dgAmount.toStringDecimal();
String sResult = "UPDATE test01 SET dBalance = $sAmount WHERE ikey = $sKey"; // Used for Update
................
I presume that the package on Github will do it in a similar way.

So, I don't think you need to do much if anything for decimal.

[xxgreg]

Thanks for the comment. I agree, I definitely like the current behaviour, it's simple.

This might change in the future though, once there's a solid commonly used Decimal package.

Glad that this package is working for you. Let me know if you run into any problems. Very curious to hear what you're working on...

[brianoh]

I'm writing some applications using Dart and Go, mainly testing. I need to evaluate databases as part of that. Both Dart and Go are gr8 to work with IMO. I'm not sure why Dart doesn't have decimal data-type. However, there doesn't appear to be much to it. There's not much I'd want to change in either Dart or Go, but decimal is one. Async in Dart is probably the other (await). It's early days however.

With regard to databases, I think that the Dart packages need a common framework. I've tested yours and SqlJocky and they are quite a bit different. With Go, as you may know, they have a Database/Sql framework, and it makes it easy to cater for multiple RDBMS's using the same code.

That's my $0.02.

[xxgreg]

One of the big blockers on Decimal on Dart is Integer behaviour when
compiled to js. There is talk about making arbitrary precision integers
work the same as on the VM. If this happens then Decimal is easy.

Regarding a common framework I agree - but in the meantime it would be
pretty easy to write a shim that did this. However this still doesn't solve
problems like mysql not implementing the returning clause, and relying on
the driver to return last insert ids. Basically if you want solve those
type of problems I think you need a micro-orm to paper over the differences.

Btw, not sure if you discovered the not yet documented feature:
c.query('select name from person where id=@id', {'id': 42});

I'd quite like to see a common db interface look something like this. This
approach can be expanded to a micro-orm similar to Dapper.net.

On Mon, Apr 29, 2013 at 2:06 AM, Brianoh [email protected] wrote:

I'm writing some applications using Dart and Go, mainly testing. I need to
evaluate databases as part of that. Both Dart and Go are gr8 to work with
IMO. I'm not sure why Dart doesn't have decimal data-type. However, there
doesn't appear to be much to it. There's not much I'd want to change in
either Dart or Go, but decimal is one. Async in Dart is probably the other
(await). It's early days however.

With regard to databases, I think that the Dart packages need a common
framework. I've tested yours and SqlJocky and they are quite a bit
different. With Go, as you may know, they have a Database/Sql framework,
and it makes it easy to cater for multiple RDBMS's using the same code.

That's my $0.02.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5#issuecomment-17134543
.

[brianoh]

I agree with most of what you say. However, I cannot see that decimal on JS would be an insurmountable problem. I'm no expert however. I presume that "they" would have to define a decimal class for JS, and the code to handle it in much the same way that has been done with Dart by developers.

Even without decimal in Dart, which I think is needed to make it a better language, decimal is not a huge problem. I've written software in the past where all decimal had to be stored and used as integer. It's no huge deal. However, with SQL databases, decimal needs to be used, because one doesn't want to store decimal as integer in the database - it's just too confusing. So, really, all that is "essential" is to be able to convert decimal stored as integer to a string and vice versa. As far as any SQL drivers go, as things currently stand, treating decimal as a string in Selects would suit me (I think).

With regard to a standard framework (a la Go), I agree that there are obvious differences between RDBMS systems. However from my limited experience (to date), that is (relatively) trivial compared to handling completely different SQL "components", which from my limited experience requires separate programs for each RDBMS.

I'm interested in your other comments such as the "not yet documented feature", and Dapper.net. I will look at that.

The thing that I haven't looked at currently is the List of Rows from a Select (in your package), and how decimal is stored there. From memory, in my test program, I had to use string interpolation to make it a string. What it was in the List, I don't know. Hopefully, that is not a problem. Is it worth storing it as String?

[xxgreg]

Decimal in dart2js is definitely not insurmountable, it's just if they do
it now, then once arbitrary precision integers land, they'll have to throw
the code away and start again. So it's just a timing/effort issue. (Btw I
think someone in the community is working on this, they've only implemented
bigints so far though: https://github.com/Dartist/dart-bignum/)

I disagree that Decimal should be part of the language. With Dart's
operator overloading implementing Decimal as a library is fine.

Back to postgresql. Numerics are always returned as a string in the
postgresql format. i.e. this is exactly what postgresql sends over the wire
and is not changed in any way by the postgresql dart library.

Here's a description of the numeric string syntax:
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#AEN1452

On Mon, Apr 29, 2013 at 10:24 PM, Brianoh [email protected] wrote:

I agree with most of what you say. However, I cannot see that decimal on
JS would be an insurmountable problem. I'm no expert however. I presume
that "they" would have to define a decimal class for JS, and the code to
handle it in much the same way that has been done with Dart by developers.

Even without decimal in Dart, which I think is needed to make it a better
language, decimal is not a huge problem. I've written software in the past
where all decimal had to be stored and used as integer. It's no huge deal.
However, with SQL databases, decimal needs to be used, because one doesn't
want to store decimal as integer in the database - it's just too confusing.
So, really, all that is "essential" is to be able to convert decimal stored
as integer to a string and vice versa. As far as any SQL drivers go, as
things currently stand, treating decimal as a string in Selects would suit
me (I think).

With regard to a standard framework (a la Go), I agree that there are
obvious differences between RDBMS systems. However from my limited
experience (to date), that is (relatively) trivial compared to handling
completely different SQL "components", which from my limited experience
requires separate programs for each RDBMS.

I'm interested in your other comments such as the "not yet documented
feature", and Dapper.net. I will look at that.

The thing that I haven't looked at currently is the List of Rows from a
Select (in your package), and how decimal is stored there. From memory, in
my test program, I had to use string interpolation to make it a string.
What it was in the List, I don't know. Hopefully, that is not a problem. Is
it worth storing it as String?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5#issuecomment-17159256
.

[brianoh]

Thanks for the feedback. It was SqlJocky (MySql) where the List returned by a select needed String Interpolation to convert decimal (double) to String.

Having a re-think, as I see it, Javascript and MySql have survived until now effectively using double for integers. The reason for that I guess is that on the client-side doubles are big enough (at least for most if not all western currencies), and most applications. For server-side applications (in Dart), I presume that all calculations (eg. interest) could be done using integer and scaling, and Dart handles BigInt or the equivalent.

The question for me is - am I ever going to deal with an account with a balance exceeding c. $9 trillion.99? I'd say for any western country in the next 20 years, the answer is no. So, although I wrote my Decimal package, I don't think that I really need it. MySql appears to have survived without it. That's not to say that arbitrary-precision decimal a la Postgres would not be better, and in some countries maybe it is essential on the Client-side.

I still think that adopting an Sql "framework" similar to Go would be a good idea.

[xxgreg]

I think it will take a bit of experience using the existing database
libraries to figure out what the best interface for such a framework is.
Dart is different than Go, and Java. So the framework API should be
different too.

From memory, Go took a similar approach, there was at least a year between
the first database libraries being developed, and a common interface being
written.

On Tue, Apr 30, 2013 at 7:24 PM, Brianoh [email protected] wrote:

Thanks for the feedback. It was SqlJocky (MySql) where the List returned
by a select needed String Interpolation to convert decimal (double) to
String.

Having a re-think, as I see it, Javascript and MySql have survived until
now effectively using double for integers. The reason for that I guess is
that on the client-side doubles are big enough (at least for most if not
all western currencies), and most applications. For server-side
applications (in Dart), I presume that all calculations (eg. interest)
could be done using integer and scaling, and Dart handles BigInt or the
equivalent.

The question for me is - am I ever going to deal with an account with a
balance exceeding c. $9 trillion.99? I'd say for any western country in the
next 20 years, the answer is no. So, although I wrote my Decimal package, I
don't think that I really need it. MySql appears to have survived without
it. That's not to say that arbitrary-precision decimal a la Postgres would
not be better, and in some countries maybe it is essential on the
Client-side.

I still think that adopting an Sql "framework" similar to Go would be a
good idea.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5#issuecomment-17213104
.

[brianoh]

I've done more testing, and thought that I would add an update on it. At this stage, I haven't found any issues. The driver appears to be extremely fast. I needed to use "LOCK TABLE" because I'm generating my own unique key, and it "worked" fine. I tested with 3 "users" doing "batch" Inserts and Updates, and it was still extremely fast. I haven't tested "SELECT FOR UPDATE" yet, but I am using it, however it is a random Select and contention there may not have arisen (I checked the insert and update totals of the currency field). I will test it a little later for contention while attempting to update the same row. I presume that SELECT FOR UPDATE should be the correct way to handle contention in updates.

[xxgreg]

"The driver appears to be extremely fast"

Probably because it doesn't do much ;) However, I'm pretty sure it will be
slow for big result sets. I have some ideas that will drop the overhead and
make it significantly more efficient for large result sets.

Regarding your question about select for update, I'm not an expert on using
Postgresql, so perhaps best to ask on the postgresql mailing list for
advice.

Thanks for the feedback. Feel free to email me if you have further
questions, or information you'd like to share. I will set up a mailing list
at some point. But private email is fine for the moment. My email address
is on pub website - click on the email icon next to my name.
http://pub.dartlang.org/packages/postgresql

On Mon, May 6, 2013 at 7:56 AM, Brianoh [email protected] wrote:

I've done more testing, and thought that I would add an update on it. At
this stage, I haven't found any issues. The driver appears to be extremely
fast. I needed to use "LOCK TABLE" because I'm generating my own unique
key, and it "worked" fine. I tested with 3 "users" doing "batch" Inserts
and Updates, and it was still extremely fast. I haven't tested "SELECT FOR
UPDATE" yet, but I am using it, however it is a random Select and
contention there may not have arisen (I checked the insert and update
totals of the currency field). I will test it a little later for contention
while attempting to update the same row. I presume that SELECT FOR UPDATE
should be the correct way to handle contention in updates.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5#issuecomment-17457916
.