View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 4884 | Composr | ecommerce | public | 2022-08-11 02:03 | 2022-08-11 17:48 |
| Reporter | Chris Graham | Assigned To | Guest | ||
| Priority | normal | Severity | feature | ||
| Status | new | Resolution | open | ||
| Summary | 4884: Support for CURRENCY data field (on hold) | ||||
| Description | Floating point numbers are currently used represent currency. I have documented the reasoning for that https://gitlab.com/composr-foundation/composr/-/commit/bc0f065d5503dd790a195dd4c164752902e640f6. This simple command shows that using floats is unreliable: php -r 'var_dump(number_format(0.1,100));' string(102) "0.1000000000000000055511151231257827021181583404541015625000000000000000000000000000000000000000000000" As I've documented, it's not usually a practical problem given how we are rounding off the numbers when converting to strings. And we use floats for good reason: PHP does not directly support fixed point maths, and dealing only with cents has its own problems. But, it stinks frankly. If we add a dependency on BCMath, and complexify our code, we can do better. It may not be worth the tradeoffs involved to be frank, hence why I am marking this issue 'on hold', but I wanted to get it onto the tracker. Our data type layer uses our own abstract types to insulate us from the quirks of different DB backends. We don't need the full flexibility of different types+settings that databases support, we just need to support every usage category we have. We could add a new data type called CURRENCY. It would map to a DECIMAL (i.e. fixed point non-integer) on MySQL, and probably the same on every other DB driver - but this needs confirming. We would have the precision set so we have 2 decimal places of accuracy, positive/negative, and then any remaining bits to represent the non-decimal portion. 7 bits needed for decimal places 1 bit needed for sign 32-7-1=24 bits 2^24=16777216 which is 7 digits of accuracy. That means DECIMAL(7,2) for 32 bits. Which is accuracy up to single digit millions. Not good enough actually, due to us wanting to cleanly support hyper-inflated currencies. If we use 64 bits (which actually should work even on 32 bit MySQL - DECIMALS can be really huge). Let's do the math again... 7 bits needed for decimal places 1 bit needed for sign 64-7-1=56 bits 2^56=7206 trillion (I think) which is 15 digits of accuracy. That means DECIMAL(15,2) for 64 bits. Which is accuracy up to hundreds of trillions. When the parameters are read in, we would use a new post_param_currency, which would check we don't have numbers exceeding the input bounds. and that are well formed. Possibly also a parameter that decides whether negatives are allowed. Within PHP and to/from the database we would need to store as strings, due to lack of native PHP fixed number support. Any calculations within PHP would use the BCMath extension. | ||||
| Tags | No tags attached. | ||||
| Attach Tags | |||||
| Time estimation (hours) | 32 | ||||
| Sponsorship open | |||||
|
|
An alternative is to support a new REAL_DOUBLE data field type, once 3046 is implemented (i.e. once we know we can use it in PHP code). This solves the problem of inflated currencies, and reduces the chance of accuracy errors - but it is not as clean. Or, to use BIGINT, once 3046 is implemented and deal in cents. Probably not very future proof and somewhat messy. For reference, I looked into it and MySQL 32 bit can handle BIGINT (64 bit), DOUBLE (64 bit), and very long DECIMAL up to 65 digits. This is because it uses either advanced CPU instructions or compiler magic to work beyond the system architecture's word size. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-11 02:03 | Chris Graham | New Issue | |
| 2022-08-11 02:03 | Chris Graham | Relationship added | related to 3046 |
| 2022-08-11 17:44 | Chris Graham | Description Updated | |
| 2022-08-11 17:48 | Chris Graham | Note Added: 0007425 |
