Price Implementation for Commerce 2.x

I actually don't see anything bad in your list. Those are design decisions that make a lot of sense to me.

+1 for database normalization - still having nightmares from UberCart's attributes

Your premise is wrong. There is no such thing as "In Drupal the database is used to exchange date between different modules." Querying the database directly is discouraged, and we are going toward less normalization (in the sense "data split in more tables"), not more.

Yep, we'll need to address some of these items going forward. Moving price components into a separate table has been on my hit list for some time for 2.x, primarily for the purpose of generating easier tax reports. Responding to points in particular,

1. Price storage - we can't depend on BC Math, so from a PHP standpoint, the data type is a non-starter. On a higher level, I disagree that modules should be able to expect instantly usable data out of the database. This isn't true with many other fields - text is one clear example (we filter prior to display). The raw data is raw data, and formatting prices can involve adding a decimal point at the right place just as it does adding a currency symbol. Two price fields' amount columns are in fact meaningless on their own unless you take into consideration their currency_code columns, and if they're different currencies you'd have to depend on the API for comparison between the two anyways (using the currency conversion function). By using integers, we store more precise numbers in an effort to stamp out rounding problems... basically, we're saying your rounding will be sorted out in PHP prior to the save, though component prices have no such limitation. In fact, this may be one reason why we prefer to leave component prices in the data column... we'll see. I hadn't thought of that before.
2. Components - As I mentioned earlier, this is a known problem spot, but as I also just pointed out, I'm not sure there's going to be an easy solution. Generating a tax report on a batch process that looks at the taxes collected in a range of orders' order total fields isn't going to be that difficult (additionally you could keep a running total based on incoming payment receipts)... it just means you can't whip it up in a View for now. I'd like to work on this in contrib for now and reconsider price component storage in 2.x.
3. Multiple prices - Mentioned in the other issue, just create an additional field for the different currency. Use Rules to determine which currency to convert to for the current customer. Problem solved. This has nothing to do with the price component system. (I see that perhaps you're saying a single product might cost $5 and 5 EUR; that'd just be crazy and I don't see any need for us to accommodate that in core right now or in 2.x.)
4. Currency storage - No way. A price is an amount with a currency, and we're not changing it. There is the capability in the code for a more intelligent order total calculation process. What it should be doing for 1.x (I think I have an open issue for this, if not in the queue, at least locally) is either totaling in the currency of the line items if they're all the same; if not, converting them all to the site's default currency assuming a line item exists in the default currency; otherwise we'll need a hook to determine what currency to total in (i.e. how do we pick the "dominant currency" or do we just always convert to the site's default currency). However it works, we're not removing the currency association. The only alternative we could explore in 2.x is maintaining multiple order totals, 1 per currency, but that's a complexity we intentionally avoided for 1.x.
5. DB normalization - That's always a goal, and data columns are my worst nightmare. : )
6. Integration with contribs - Each one of these modules can accommodate everything we need now. There shouldn't be a problem here. Whether the price amount is stored as an integer or decimal, you can still perform math / grouping on prices (of the same currency) with no trouble. The underlying data type has nothing to do with this, and the presentation of these prices can be easily handled on display. In other words - if we write appropriate Views handlers and entity property definitions, we can store the prices however we want to. And let me stress again, that price amounts on their own are useless anyways - you're always going to have to take into consideration the currency of different prices when comparing / doing math on prices and when formatting them for display. Oooh, in fact you'll even need to use the API for rounding prices in some currencies like CHF which rounds to the nearest .05.

To summarize, I do think there are some things to address in the component API / data storage, but I don't think there are things to address with the very basic price structure. I'd like to work out a solution for improved price component storage in 2.x, but I'm a little worried about the way components don't currently have forced rounding.

I'm putting this postponed b/c it's not a 1.x issue, but feel free to respond to my points above. I just might not get back to you immediately as I'm really trying to push out a beta3. : )

To add my thoughts on price storage there are problems with storing net values with only two decimal places when working with VAT.

Although no longer a correct example there will be similar ones. The UK rate of VAT was 17.5% and £2.99 might be a common price of a product including VAT. This cannot be calculated if the net price only has two decimal places because the rounding should be applied to the inclusive of VAT price.

£2.54 x 1.175 = £2.98
£2.55 x 1.175 = £3.00

but

£2.545 x 1.175 = £2.99

We are developing a project management system for real estate development. Evaluating the commerce price module we stumbled upon the maximum possible value 21,474,836.47 USD/EUR/...(2147483647 cents) using a 32bit system. We need more money... ;-) ...and exact VAT calculations as aforementioned by dwkitchen #6 are important for us too.

To get rid of this limitations concerning the price storage/implementation we vote for something like:

+1 BigInteger/bcmath/gmp and DECIMAL(19,4) on the database-side
(perhabs as an option if bcmath/gmp is not available and with fallback to an integer implementation)

Having 64bit php integer with DECIMAL(15,4) might be an alternative without bcmath/gmp. That's what we are trying in our project now.

#7 has a good point: we should move from int to bigint on the database side (int is generally 32bit, bigint is generally 64bit; at least that's true on both MySQL and PostgreSQL).

Most of the world is 64bit today, so I don't think we should care that much about 32bit platforms. If you want bigger number handling, deploy on 64bit.

The type of scenario mentioned in #6 should not happen. The components that make up the price are always stored in the maximum floating-point precision (... that PHP is able to serialize).

Here's a thought: GnuCash, the GPL accounting system, follows the same approach as Commerce in storing price values in integer form (rather than floats) to ensure proper rounding and arithmetic. But GnuCash takes it one step further by providing two fields in the database for each price: a value, and a denominator. The purpose of the denominator is to indicate what the decimal precision of the value field should be.

So for example, to represent $99.99 in GnuCash, the following is stored in the GnuCash data file:
value = 9999
denominator = 100

Then, it's just a simple matter of loading both values and dividing the value by the denominator.

Perhaps Commerce can implement a similar storage schema, and automatically perform the conversion for other modules when data is being inserted.

This would solve the potential issues involved with having a store that requires some products to have 2 points after the decimal, and others to have 3. This was raised by hunziker in issue: #1124416: Revise the way we handle price amount values (specifically this comment)

@Damien Tournoud: Also worth noting that GnuCash uses 64-bit integers for both the value and the denominator columns.

Isn't that basically what we achieve by storing the currency code with the price amount? We know how the data is stored based on that, and because currency formatting is pluggable, the way the decimal points are expressed for products using the same currency can be modified there at the display layer. I'm not sure I agree with having different storage for the same currency, though I wouldn't rule it out.

Oh true, that makes sense. Although the use case that I have in mind still might not be covered:

I'm working with a site built in Ubercart, and recently the client said they needed some products to have 3 decimal places, rather than 2 (but most just use 2). They sell products in very large bulk quantities, so the 3rd decimal place is necessary in some cases. It works in Ubercart with just a bit of custom formatting on the display side, because Ubercart uses a decimal storage type with 5 decimal points, so the database can handle it. But it wouldn't work in Commerce because both products would still be using the same currency, so that restricts the decimal points for all the products in a currency.

So I guess the method I'm suggesting would untether the "number of decimal points" from the currency, and instead make it unique to each product. This may create a bit more overhead, but it would provide a lot more flexibility in terms of product-by-product prices.

I'm very interested in determining the best way to do this, as I'm currently in the process of building a bookkeeping module (Ledger) that has similar requirements for exact precisions. I started it with floating values, but quickly learned the pitfalls of doing so (see #1477534: Store values in integer form to avoid issues with float rounding). Luckily the module is still in DEV so I can switch things up easily. ;-)

I've been following Commerce's lead in a lot of the data architecture decisions I've been making in Ledger, but I think this one needs more consideration. It seems that looking into the way projects like GnuCash deal with decimals (and other things like multiple currencies and commodities) makes sense.

Yeah, I see your point re: the ease of solving it at the data storage level. I'm not a huge fan of storing the data for the same currency in two different ways, but I wouldn't rule it out out of hand. For example, it can be a problem is you suddenly decide to change the precision of your storage as it is right now. It's almost like we'd need to recommend just using a new currency for that if it came to it; USD, US1, US2, etc. for each different precision under the current architecture. So the question is, would that really be any better? : P

a big +10 for "storing decimal denominator" like said in #11

and confirming: not handling decimal prices out of the box is a knockout for commerce for quite some customers

i think i understand your point ryan, that this might fit in the existing model as another currency.
but in fact it is not: switching denominator is a trivial migration, while switching currency is not.

my gut feeling is denominator should be handles separately from currency, but only be configurable globally per currency, to keep things manageable.

I think I like where you're heading with that, hunziker. We'd just need to ensure that would work fine throughout Views / Rules / the Entity system. I fear we'd run into the same silly issues we have with price fields and the serialization / unserialization of data arrays. : (

@hunziker: the issue with using a decimal type field in the database, though, is that you are then restricted to that many decimal points for ALL of your currencies. So if you wanted to add a currency (or just a product) that required 5 decimal points, you're out of luck.

It seems to me that adding a denominator column is the best solution, because it allows all prices to define how many decimal points they need themselves, without relying on a constant value anywhere (whether it be in the database structure, or in Commerce configuration).

I like your idea about calculating the final price within the query also, although I think you meant to divide, not multiply. So it would look something like this:

SELECT price / denominator AS price ....

@hunziker: Why build an application with any restrictions if you can make them unrestricted without too much more overhead? I don't know how much overhead the extra denominator column would add, but it would certainly remove all restriction on decimal points across the board.

And of course I agree, in most cases no one will ever need to work with 0.00000001 units, but one "currency" that does require that much precision is Bitcoin. And who knows, maybe there will be a country that experiences hyper-deflation one day, and requires such small divisions. ;-)

I'm not sure I understand what you mean about defining the denominator, but I can't see where multiplication would work. Unless you defined the denominator as a decimal itself, but then you've got the same problem all over again: how many decimal points should it be? As far as I can tell, the denominator would have to be an integer value like 100, 1000, 10000, etc.

For instance, bitcoins (yeah yeah) have 8 significant decimal places. A bitcoin might be worth 3$ US today, but it could (ha!) be 3000$ or 30,000$. Since there's a limited amount of bitcoins, those decimal places matter a lot in the long run.

> Is it really a requirement that we can build applications with such small amounts?
prices with fractions of cents are not uncommon to me.

and further, configurable denominator would happily :-)) solve a VAT tax problem, stated short "trivial calculus hows that there are target total prices that can't be reached with any integer net price"
see #1362002: VAT Tax Rounding Problem - Impossible Prices

Cross posting this #1350528: Add support for Commerce module price fields.
It seems there currently is no simple way to create a price based index for search and filtering by price is a requirement on many projects.

I just finished converting the Ledger module to a 2-column integer storage scheme. The two columns are called value_num (numerator) and value_denom (denominator). Previously it was a single 'value' field of type 'float' (I quickly learned the pitfalls of float arithmetic). Here is the related issue: #1477534: Store values in integer form to avoid issues with float rounding

In Ledger's case, it mainly involved adding custom load/save logic to the entity controller class's load() and save() methods. load() simply loads both values, and divides value_num by value_denom, and sticks the result into $entity->value, which is what the original field was called. save() does some tricky string manipulation to convert it back to 2 numbers before sticking them back in the database. Not sure if it's the "best" way to do it, but it works. I'd love to hear opinions:

<?php
// Count the number of decimal places.
$precision = strlen(substr(strrchr($entity->value, '.'), 1));

// Calculate the denominator based on the precision.
$entity->value_denom = pow(10, $precision);
      
// Calculate the numerator based on the denominator.
$entity->value_num = $entity->value * $entity->value_denom;
?>

Then, I just had to use a custom Views field handler to perform the same basic calculation whenever the field was used. This is what it looks like:

ledger_handler_field_value.inc

<?php
/**
 * @file
 * Contains the value field handler.
 * This handler can be used by modules that store values in value_num and value_denom fields.
 */
class ledger_handler_field_value extends views_handler_field_numeric {

  // Add fields to the query.
  function query() {

    // Ensure the main table for this field is included.
    $this->ensure_my_table();

    // Formula for calculating the final value, by dividing value by denom.
    $formula = $this->table_alias . '.value_num / ' . $this->table_alias . '.value_denom';
    $this->field_alias = $this->query->add_field(NULL, $formula, $this->table . '_' . $this->field);
  }
}
?>

Hopefully it wouldn't take much more than that to convert Commerce to the same model. As long as the load() function provides you with the same 'value' field ('price', 'amount', whatever it is) on the entity, all the rest of the code should work like before. Obviously you'll also have to get rid of the original code that was performing the conversion from minor units. I'm sure there's other stuff as well.

Let me know if I can help at all with this... if you do decide to take a 2-column approach.

I haven't read the entire issue, so the concern I'd like to address here, may have been discussed before.

What I just realized is that if we store prices as integers plus currencies and use the number of decimals of those currencies to get the actual price value, we limit the precision to the currencies' maximum number of subunits. This breaks things like dynamic price calculation. Say you have a product that is €4.99 and you get a 24% discount, the product's price becomes €3.7924. If this is the only product to work with, we can simply round up and store the price as EUR379 (€3.79, because the euro has two decimals), but if this is for a bulk order where the buyer wants 100 of these products, we end up with a total of €379 rather than €379.24.

If I'm not mistaken, since we leave rounding options up to you when you create your pricing rule, you can determine whether or not you want the resulting price to be rounded o not. If you choose not to round it when the discount is applied, I think you'll still get the correct result whether the product ends up being purchased alone or in bulk.

The thing is there are no rounding options if prices are stored as integers with a limited number of decimals. Rounding is what happens if numbers are of a higher precision than required, but if we use the integer approach, the numbers are never stored with too high a precision, because as soon as they're set, we round them: €3.7924 would be stored as EUR379, so we already round the value the moment we set the price in the integer format, thereby losing data.

I have to agree with Xano. We don't want to limit the storage of data, if possible. Rounding should be a method of formatting the end result or display price, but we shouldn't be dropping decimal places before saving them to the database. It reduces the possibilities for future calculations.

I'm confident that a two column approach for value storage will work, and that it can be abstracted from users and module developers alike, so that they don't have to worry about it (unless they need to implement their own data storage, that is).

I wonder if it would make sense to write a separate module, which both Ledger and Commerce could depend on, which abstracts the storage of decimal numbers like this. Then all of the necessary logic of dealing with the two-column approach could be easily leveraged by any contrib module that needs it. I might be willing to put that together, based on my experience with it in the Ledger module. Would that be worthwhile?

The plan is to make Currency the standard for handling currencies and prices, in terms of metadata, (localized) display, storage and conversion.

@Xano, re: Currency module

AFAIK the Currency module currently only provides currency conversion. It doesn't deal with the storage of values like this.

I actually don't think we need to worry about currencies at all, in fact. This is a simpler issue. It is all about decimal storage in MySQL.

See my comment (#11) above for a description of how GnuCash, an open source accounting package, handles the same problem. I implemented the same approach in the Ledger module, so we have code to start with already. It can easily be abstracted out into another module. Maybe Currency is that module, but I think that this is useful for any application that needs dynamic decimal storage, not just those dealing with money and currencies.

The problem with your example, Xano, is that you actually don't want what you're trying to make the data model support or nothing is stopping you from changing Commerce to work like you do.

On the first point, you don't want a shopping cart that shows the customer a unit price of 3.79, a quantity of 100, and a total of $379.24. Any reasonable customer will look at that and assume you have a rounding problem in your cart system. We round at the unit price level to avoid precisely this problem - because as soon as we start calculating prices and multiplying by odd numbers, you're going to end up with floating point problems in your storage and calculation.

However, if what you want is to show the unit price as 3.7924, a quantity of 100, and a total of 379.24, then you can simply create the site with an altered currency info array for EUR to support saving decimals to the fourth decimal point. You're free to make this decision if you know that's a valid use case for your site, and literally nothing in Commerce would stop you or cease to work properly if you altered things like so. The only thing missing would be the ability to mix the precision to which you stored prices on a price by price basis, which is the solution m.stenta is proposing above.

This topic was discussed on the DC 2.x planning sprint, and some notes are presented here: http://www.drupalcommerce.org/dc-2x-roadmap/price-taxes-payment

The notes are very brief though, and I've been pinged about them a few times, so I wanted to summarize what we discussed:

1) Since PHP only knows integers and floats, prices need to be integers. We should also start storing the denominator.
This is the GnuCash model described above. It is a pretty common solution across applications, on the web, etc.

(Other reasons for avoiding the decimal type in mysql are different precisions for different currencies and the need to support multiple databases types which might cause problems here.)

2) Different currencies have different precisions. Also, the "calculation precision" needs to be higher.
So if precision for USD is 2 (99.99), the calculation precision should be at least 4, so that you don't
lose precision when calculating discounts and taxes. This problem (precision loss) can be reproduced with Commerce 1.x
dwkitchen even advised going with precision 6 in this case, though I'm not sure if there are rational reasons for that.

3) We now get to the storage problem.
PHP only supports signed integers. That's 2^31 on a 32bit setup.
So a precision 6 means that your highest price is 2147.
This means that you need a 64bit system in order to store integers properly.
I'm not sure that's a feasible requirement (even my local machine has a 32bit PHP / MySQL).
This means you need to use strings (varchars on the db side) to store the amounts.

4) So, we come down to "storage as a string" + http://phpseclib.sourceforge.net/math/intro.html
The BigInteger library linked above is a wrapper that has a userspace implementation, and can use bcmath or gmp if they are available.
This (string + BigInteger library) is our only option if we don't want to require bcmath or gmp or a 64bit system (meaning, we keep Commerce just as portable as it is now).
Of course, this will give us problems with sorting and filtering (requiring the column to be CAST when used) which we need to investigate.

Hunziker mentioned Magento as an example that could be followed.
Magento doesn't really care about the problems listed above at all.
And as far as I've seen their bcmath dependency comes from Zend Framework itself.

Further thoughts welcome.

EDIT: We should have examples that illustrate the problem (and the scale of how acceptable / unacceptable it is).
We can easily replicate #2, but replicating the PHP precision loss itself (with and without bcmath) will require some more work.

Great summary of the issues and the options, bojanz! The use of a string + BigInteger library seems to make the most sense for storage, although we do need to think more about the sorting/filtering implications of that, as you suggested.

One of the bigger issues in my mind is the (de)coupling of price precision and currency precision, and I'm not sure if that's addressed here (if I'm misunderstanding, and it is, then apologies for the redundancy... I just want to make sure it's being considered).

I think it's important to separate the concept of "what the precision of a certain price is" and "what the precision of a certain currency is". By that I mean, you should be able to have prices that have a different precision than the currency they are representing. A perfect example (although maybe not in the world of ecommerce) is gas prices. Typically gasoline will be sold at the pump with thousandths precision (ie: $3.599/gal), even though the final bill you get after filling your tank has a hundredths precision (because we don't exchange currency less than one cent). In Commerce 1.x, the same precision is used for both the price and the currency. I believe that those should be decoupled in 2.x.

The "price precision" should be on a product-by-product (or price-by-price) basis, and the "currency precision" should be for the currency. The currency precision would then basically act similar to a text-formatter... it would only be applied at the end, when the transaction of money is actually taking place, or when a "total" price needs to be displayed (ie: the order total).

So all the calculations should be done using whatever precisions each individual price is using, and then the "final" price (the order total) should be rounded to the currency's precision. Does that make sense? Or is that what you meant already?

The "price precision" should be on a product-by-product (or price-by-price) basis, and the "currency precision" should be for the currency. The currency precision would then basically act similar to a text-formatter... it would only be applied at the end, when the transaction of money is actually taking place, or when a "total" price needs to be displayed (ie: the order total).

Prices/amounts are meaningless without currencies. The price is the quantity, the currency is the what. Both pieces of information go hand in hand, so it is unfortunately not possible to decouple those two entirely, meaning that when you enter a price for a product, you'll also need to enter a currency.
We can, and should, however separate the two precisions, if only to store prices with a higher precision than currencies have decimals, so we don't get rounding errors.

For your information, a new module that's sideways related to Commerce and prices: http://drupal.org/project/currency_commerce

I think we are in agreement here. I didn't mean to say that prices and currencies should be decoupled entirely. I meant that the precision of each should be independent. So yea, we're on the same page. A price needs to be associated with a currency... but the currency should not determine the precision of individual prices.

Hey,

I'm working on e-commerce solution for Symfony2 - called Sylius - http://sylius.org.
Right now I'm considering use of php money implementation - https://github.com/mathiasverraes/money or https://github.com/merk/Dough. Currently the first seems to be winner - because it's simpler.
I wanted to hear your opinion on such libs, maybe we can work out some generic solution together.

Thats my first post on drupal.org, so hi awesome Drupal community!

Bests,
Paweł

Welcome Paweł! Thanks for chiming in! It's great to share ideas across open source projects... it really helps everyone.

I took a quick look at http://github.com/mathiasverraes/money. It looks very promising! I am generally in favor of sharing external libraries wherever possible (but, alas, I am not a core Commerce developer, so my opinion only has so much sway). There are reasonable arguments on both sides, it seems. The main argument against third-party libraries seems to be that it increases the setup time for new projects. As opposed to packaging everything into one module, so all you have to do is turn that on and run with it (less dependencies, in other words). I've heard some of those sentiments in the Commerce community, and while I may not agree 100% with the logic, I do see the value in it. (And packaging the Money library with Commerce wouldn't be possible due to differences in the software license, I believe, so it would always need to be a two-step process.)

That said, I think it's worth discussing further. I just emailed the author of the Money project with a question related to what we've been discussing here: the relationship between price precision and currency precision. I'm interested to hear what he says, and I'll share it with this issue when I hear back.

Welcome aboard, Paweł. Don't suppose you've had any contact with Vespolina? I know they're trying to build an eCommerce framework plugin for Symfony2 as well, would be another project to examine.

Hi,

I'm the author of https://github.com/mathiasverraes/money

The library currently takes a very simple approach: it stores all amounts in integers, in the smallest unit that the currency officially supports. Eg Money::EUR(9999) represents EUR 99.99. Operations like the allocation http://money.readthedocs.org/en/latest/Allocation.html assume that the unit, eurocent in this case, is indivisible.

However, I'd very much like to expand the library to support more use cases, so any ideas/suggestions/pull requests are welcome. I'll look at merk/Dough, see what we can learn from them.

One idea is to have a second type called BigMoney, which allows cents to be divided. See https://github.com/mathiasverraes/money/issues/7

In any case, I'd be happy to help you guys with figuring out what the best way to do this would be.

As for licensing, Money is http://en.wikipedia.org/wiki/MIT_License, which is compatible with the GPL.

@m.stenta thanks for pointing me here :)

@mathiasverraes Ok, that's actually exactly what we do now, we just store the precision a little insufficiently. We actually need to be able to store / operate at a much greater precision than just the minor unit of the currency - for example, particular VAT rules have to be applied to the 4th and 5th decimal place. : P

I like the sound of BigMoney as a data type, though - might need to figure out how to wire some o' that to my bank. : D

Just FYI https://github.com/merk/Dough was created by a Symfony2 contributor (doesnt mean that it should be preferred) but I dont know if Vespolina is using it.
And yes Sylius and Vespolina folks have talked, but I guess Vespolina is taking a slightly "grander" approach while Pawel seemed to have wanted something useable faster.

@bojanz, do you perhaps have new information on this?

@m.stenta and I have discussed number storage a few times over the past months, and this week we both brought forward two different approaches. Both approaches are based on numeric strings (which means they may be decimal, such as "123.45") and BCMath. I'll let him explain his approach. Here is mine.

When storing numbers, they are split into their major and minor unit. This means that "123.45" becomes "123" and "45". The major unit is stored in an integer column, and the minor unit in a varchar. Because we are storing the minor unit separately, we cannot use an integer column, as that would cause the leading zeroes to disappear. Storing the two number fragments separately instead of a decimal circumvents the problem of precision loss with floats.

Pros

• Converting numbers from PHP to SQL storage is a simple matter of splitting and concatenating strings during CRUD operations.
• SQL storage is fairly human-readable.
• Numbers as decimal strings means that for PHP operations that work on prices as numbers, no longer need the currency.

Cons

• The length of the varchar column for the minor unit has a relatively large impact on sorting performance. A length of 6, which is realistic, because it add sat least 3 digits of extra precision to any currently existing currency performed well in my short test.

Usage

• Split number strings when saving them.
• Concatenate number strings when loading them.
• Create Views field/filter/sort handlers for a pseudo-field that controls both the major and minor field. When sorting, sort on both columns. When filtering, split the user-entered number (similar, but not identical to when saving a number), and compare each number part to the corresponding DB column. When rendering the field, add both columns to the query.

To follow up on Xano's post above (#46), the approach I suggested is the same one I suggested in #11: a fraction-based storage of decimals, with a numerator and denominator field in the database (both integer fields).

I put together an initial demonstration of this approach in this module: http://drupal.org/project/fraction. It simply provides a Fraction field (via Field API), which stores two integer values to represent a decimal. It is conceivable that this module could be the basis for price storage in Commerce and other modules that need precise decimal storage (Ledger is the module I'm working on with this in mind).

There are still some considerations to be worked out in terms of this Fraction field... namely how best to store additional information alongside it's values, like currency, or Commerce price data. But it may also be worth considering the option of basing the Commerce Price field off of it, in the same way that Image Field was based off of File Field. I think these details are still up for consideration.

So just to summarize the difference between what Xano described and this... they are primarily a difference in how the decimal is stored in the database. Both are suggesting that decimals be stored in two fields.

Xano is suggesting breaking decimals up into major and minor units (so 100.05 becomes "100" and "05") stored in one integer field (the major units), and one varchar field (the minor units).

On the other hand, the fraction approach I'm suggesting involves storing two integers: numerator and denominator (so 100.05 becomes "10005" and "100"). Querying is faster with this approach, but I am in the process of putting together a comprehensive test to show the costs/benefits of each (after a lot of back and forth with Xano in IRC). I will post those results when I have them.

In both approaches, it is assumed that the value will ultimately be treated as a string once it gets into PHP, and will be operated upon using bcmath. I think it makes sense to put these operations into the Fraction module itself, and expose API functions for other modules to use when performing them, to offload some of that logic from Commerce and other modules. It already provides the ability to convert a decimal to a fraction, and vice versa, but at the moment it is using PHP's normal float operations. It would not be hard to add bcmath functions instead for better precision.

The Ledger module is already using the fraction approach (but not yet with bcmath) in it's own price storage tables, and I am now considering leveraging the Fraction module in it instead.

Some Views handlers are already written (as I described in #26 above) in Ledger that work with the fraction-based storage. More will be necessary, but it's a start.

I will post my test results comparing the two approaches soon. Feedback is welcome.

We were already convinced by the need for numerator and denominator storage. That's what we wrote up to do in our roadmap notes, but I still need to flesh the page out: http://www.drupalcommerce.org/dc-2x-roadmap/price-taxes-payment

@m.stenta, how does your fraction approach support higher numbers? Your fraction.module (which I know may not reflect recent developments) uses an integer field, limiting the maximum numerator value on 32bit systems to 2147483647, which leaves us with a maximum value of JPY2147 if we use three extra digits for precision, for instance. Also, this re-introduces floats into database operations.

It's not a real stretch to use a big int, and if push came to shove I suppose you could just use a varchar and do the math in the application using bojanz's favored bcmath to perform calculations.

BCMath is great, but PHP only. It doesn't help us anything when sorting or filtering in the query, which is what we want for performance reasons.

In the database, the numerator and denominator are both stored as signed BIGINTs, which have a range of -9223372036854775808 to 9223372036854775807 (http://dev.mysql.com/doc/refman/5.5/en/integer-types.html). Since the denominator will never be negative, it probably makes sense to make it unsigned, which would increase the available range to 0 to 18446744073709551615. That's probably way bigger than anyone will ever need, so we can reduce that to a normal signed int (with a range of 0 to 4294967295), which should be enough. That means that it's possible to represent a number with up to 9 decimal places. I'll make that change in the Fraction module shortly.

In PHP, we will be dealing with the numbers using bcmath, so length is not an issue. We simply SELECT the numerator and denominator fields from the database separately, and then use bcdiv(numerator, denominaotr) to generate the decimal representation as a string.

If bcmath is unavailable, and the integer is larger than 2147483647 (on a 32-bit system), PHP will automatically convert it to a float (http://php.net/manual/en/language.types.integer.php). Obviously we're trying to avoid PHP floats due to their precision limitations, but as a fallback it shouldn't cause too much harm in most cases. In other words, it will still work relatively well even without bcmath.

As for the database operations... the only times it will be necessary to do float arithmetic in queries is when you're using an ORDER BY or WHERE clause to sort/filter by the fraction, or when you are SELECTing the calculated value for display in Views. In those cases, you are not doing any operations between two fractions... just between two integers. The main issue we are trying to solve here is with multiplying/dividing fractions themselves, such as with tax and discount percentage calculations. Queries don't perform this logic... PHP does. So bcmath will cover it.

Additionally, in most cases where you're doing number comparisons / sorts / filtering / etc. in database queries, I'd wager you're going to be dealing with numbers using the same denominator. If that's the case, then you won't have to bother doing any sort of math at all in the query.

Isn't the whole idea behind this issue that we don't want to wager, but provide a solution that will always work and give predictable results? Doing float operations in the database, but not in PHP results in unpredictable, inconsistent results both in the DB itself and between the DB and PHP.

Show me the use case where I'm wrong, then - I can't think of an instance where I'd be sorting, comparing, filtering, etc. entities by price where I haven't first filtered by or converted to a single currency. Yes, it could theoretically exist, but this is not at this point a blocker in my mind. Never mind the fact that this is only a problem when using a super high precision and huge dollar values.

Additionally, you don't actually perform price calculation in queries - so I don't even see where this becomes an issue at all. Perhaps for SUM queries, but again, you wouldn't be SUMming prices of a different currency in most cases, and if you had, say, USD amounts of different precisions that needed to be summed, you could do it other ways. For example, you could return the SUMs for USD price amounts separate by their precisions and then add them in PHP once you got the result set.

In most cases, or in all cases, price calculation / manipulation and currency conversion is happening in the application, not in the storage engine.

Just a quick update on this: I have added Views support to the Fraction module which provides a sort handler, a filter handler, and a click-sorting mechanism for Fraction fields based on their decimal equivalents (calculated in the database query via formula). I would be happy to demonstrate how this works, and discuss if it would be a useful approach for Commerce to take. I plan to use the Fraction module for price storage in Ledger as well (http://drupal.org/project/ledger).

Fraction: http://drupal.org/project/fraction

I understand the hesitation for relying upon another dependency, when the purpose of the module seems so simple. But I feel it's worth considering because there seems to be "just enough" complexity in the Fraction handling that it may make sense to encapsulate it in a separate contrib (as opposed to replicating all of the code in Commerce itself). I am open to sharing co-maintainership of Fraction if that is an influencing factor.

At which point do these values hit the database, and suffer the 32 bit integer limitation?
Only when stored in a price field? Or also later, e.g. when stored as an order or line item? Or somewhere in the middle, after a currency conversion?

I just checked, commerce_calculated_price does have a 32 bit "amount" column, just like field_data_commerce_price.

As an intermediate solution, could we not simply change the type of these db fields to BIGINT ?
Then people in desparation could modify the decimal part in their currency and get the precision they need.

I personally would use this for bitcoin prices converted from EUR.
(all original product prices in the db are in EUR)

On the other hand, this is necessary only if the BTC converted prices are stored somewhere in the db, e.g. during checkout.

#2084171: Custom module to have prices and amounts in the database stored as BIGINT instead of INT

All prices on products, line items, and orders are stored in price fields, the schema for which is defined in the Price module. Unfortunately, there is no hook_field_schema_alter() (see #691932: Add hook_field_schema_alter()), so there isn't much you can do here other than to manually update the tables after they've been created. I'm not even sure there are hooks for field CRUD, so you may even just have to create a UI to manually apply the change.

The price on the payment transaction entity is just stored in properties at the moment, so that can be changed through hook_schema_alter(). Just remember that you'll still be responsible for manually updating the table if that module has already been installed.

Maybe this should be discussed in the linked issue instead?
#2084171: Custom module to have prices and amounts in the database stored as BIGINT instead of INT

I never updated this issue after doing research back in January.

The first surprising thing I discovered is that bcmath is universally supported on shared hosting / dev environments
(if you have a VPS or more, you can do anything you want, so you don't care).
I did some checking:
hostmonster, dreamhost, hostgator, site5, godaddy, hostgator, justhost, ipage, fatcow, civihosting all have it.
echoditto support said they don't have it, but can easily add it
acquit dev desktop, mamp, wamp, xampp all have it.

So, we don't need to use a userspace library like BigInteger, we can rely on bcmath directly.

Research also showed that pretty much every other language had BigInteger and BigDecimal implementations.
If we decided to imitate that in userspace, we'd still need a BigDecimal implementation on top of BigInteger.
But as I said, we have bcmath.

What we also know is that for currencies we need to separate the input precision from the display precision.
When you're entering EUR / USD prices you need a third decimal to be able to get up to amounts such as 19.99 with taxes applied.

The remaining question then just becomes a question of storage.
To me, bcmath + decimal column in the db sounds fine.
It supports the precisions we need. Works naturally.
However, has no equal in MongoDB, so they'd have to implement their own conversion to an amount + precision format, which is probably fine.
Damien might also have additional arguments here.

Alternatives are:
- Use amount (bigint) + precision (int) format. Accept the fact that someone people will be limited by 32bits and will need to upgrade their servers (they have the same problem with file uploads, the file limit on 32bits is 2GB).
- Use Fraction (which I haven't looked into much, but it's still an option).

Some of the links I noted back in January:
http://en.wikipedia.org/wiki/Arbitrary-precision_arithmetic
http://docs.oracle.com/javase/1.5.0/docs/api/java/math/BigDecimal.html - BigDecimal, Java
http://www.ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigDecimal.html - BigDecimal, Ruby
http://docs.python.org/2/library/decimal.html - Decimal, Python
http://code.google.com/p/mpmath - Python
https://github.com/whatgoodisaroad/Big-js/wiki -> JS
http://gmplib.org/ - C
http://joda-money.sourceforge.net/ -> BigDecimal, Java
https://github.com/danielcrenna/money -> 64bit integer, C#
http://rubymoney.github.com/money/ -> BigDecimal, Ruby

Does it make sense to change the version on this?

Curious what the final decisions were on this. I went to the Commerce session at DrupalCon and saw mention of a fraction field in the currency config. Just starting to dig through the code to understand what was done. Would someone mind posting an update to this thread? Last update by @bojanz was over 2 years ago.

Decimal field in the database, bcmath in the codebase. Like I argued for in #62.

Note that the pricing API isn't in place yet, so there are no helpers that use bcmath at the moment. That will change soon.

Ok - so the decimal precision is still tied to currency? This is the issue I outlined in #13.

Example: If I want to have some products with three decimal place prices, and some with two, I would need to create two different USD currencies. Is that still the case in 2.x?

Ah, actually, I see now that the price field provided by Commerce 2.x is of type "numeric" with a precision of 19 and a scale of 6. And currency is stored alongside it.

So I think that would work for the use case I described. :-)

  public static function schema(FieldStorageDefinitionInterface $field_definition) {
    return [
      'columns' => [
        'amount' => [
          'description' => 'The price amount.',
          'type' => 'numeric',
          'precision' => 19,
          'scale' => 6,
        ],
        'currency_code' => [
          'description' => 'The currency code for the price.',
          'type' => 'varchar',
          'length' => 3,
        ],
      ],
    ];
  }