0004884: Support for CURRENCY data field (on hold) - Composr CMS feature tracker

ID	Project	Category	View Status	Date Submitted	Last Update

0004884	Composr	ecommerce	public	2022-08-11 02:03	2022-08-11 17:48

Reporter	Chris Graham	Assigned To
Severity	Feature-request
Status	non-assigned	Resolution	open
Product Version
Fixed in Version

Summary	0004884: 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.

Time estimation (hours)	32
Sponsorship open

Chris Graham 2022-08-11 17:48 administrator ~0007425	An alternative is to support a new REAL_DOUBLE data field type, once 0003046 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 0003046 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 0003046
2022-08-11 17:44	Chris Graham	Description Updated	View Revisions
2022-08-11 17:48	Chris Graham	Note Added: 0007425