Currency: failure with postgresql: "value too long for type character varying(3)"

[zorun]

The currency is declared as a string of length 3:

# Project
default_currency = db.Column(db.String(3))
# Bill
original_currency = db.Column(db.String(3))

However, an empty currency is encoded as the string No Currency, which is longer than 3 characters.

Strangely enough, sqlite doesn't seem to care, and happily stores longer values:

sqlite> SELECT * FROM bill;
1|1|42.0|2020-04-30|Test||2020-04-30||42.0|No Currency
2|2|100.0|2020-04-30|Bill en euros||2020-04-30||100.0|EUR

But with postgresql, it is not even possible to apply the migration, because the default value is larger than the maximum length:

INFO  [alembic.runtime.migration] Running upgrade cb038f79982e -> 927ed575acbd, Add currencies
Traceback (most recent call last):
  File "/home/zorun/tmp/venv3-ihatemoney/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "/home/zorun/tmp/venv3-ihatemoney/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.StringDataRightTruncation: value too long for type character varying(3)

The above exception was the direct cause of the following exception:

sqlalchemy.exc.DataError: (psycopg2.errors.StringDataRightTruncation) value too long for type character varying(3)

[SQL: ALTER TABLE bill ADD COLUMN original_currency VARCHAR(3) DEFAULT 'No Currency']

[zorun]

Apparently, the code XXX is used to denoted "no currency": https://en.wikipedia.org/wiki/ISO_4217#X_currencies

To use this code, currency codes should probably be converted to a "human-friendly" string when choosing a currency in the list of currencies. Or at least an exception would have to be made for XXX.

[indatwood]

This seems to be the simple option, but comes with the need of a new feature, to translate the currency names.

Another simple fix would be to extend the size of the field ;-)

[indatwood]

Note for later : we might want to rely on this lib for the translations : https://pypi.org/project/iso4217/

[Glandos]

@indatwood after a loooong search, I discovered that Babel has everything we need:

• http://babel.pocoo.org/en/latest/api/numbers.html#babel.numbers.format_currency
• http://babel.pocoo.org/en/latest/api/numbers.html#babel.numbers.get_currency_name
• http://babel.pocoo.org/en/latest/api/numbers.html#babel.numbers.get_currency_symbol

I am currently working on this.