Issues with decimal fields and MIGXdb

[hugopeek]

I have a decimal field in the schema like this:

<field key="amount" dbtype="decimal" precision="10,2" attributes="unsigned" phptype="float" null="true" />

Then, in the MIGXdb config it's referenced like this:

{
    "MIGX_id": 100018,
    "field": "amount",
    "caption": "Amount",
    "description": "Monetary value, in Euro.",
    "description_is_code": "0",
    "inputTV": "",
    "inputTVtype": "number",
    "validation": "",
    "configs": {
        "allowDecimals": "Yes",
        "allowNegative": "Yes",
        "decimalPrecision": "2",
        "decimalSeparator": ","
    },
    "restrictive_condition": "",
    "display": "",
    "sourceFrom": "config",
    "sources": "",
    "inputOptionValues": "",
    "default": "",
    "useDefaultIfEmpty": "0",
    "pos": 5
}

I ran into a couple of issues here:

1. When using a . as separator, the decimals would disappear when reopening the MIGX item. This is then also saved on exit.
2. When using the , as separator, it throws the following error: Data truncated for column 'amount' at row 1
3. When leaving the field empty, it won't save when the default value is NULL: Incorrect decimal value: '' for column ...
4. Whatever I try after fixing the above issues, it always jumps back to having only 2 decimals.

My workaround was to use a beforesave hook to switch the . back to a , and reset the value to NULL on empty fields.

Content of the hook: (note that $object is only available after applying the changes in PR #340)

<?php
$object = &$modx->getOption('object', $scriptProperties, null);
$properties = $modx->getOption('scriptProperties', $scriptProperties, '');
$configs = $modx->getOption('configs', $properties, '');

$data = json_decode($properties['data'], true);

$decimalKeys = array(
    'amount',
    ...
);

foreach ($data as $key => $value) {

    // Change separator from , to . for decimals
    if (in_array($key,$decimalKeys) && stripos($value,',') == true) {
        $value = str_replace(',','.',$value);
        $modx->log(modX::LOG_LEVEL_INFO, 'Changed separator for: ' . $key);
        $object->set($key, $value);
    }

    // Reset empty decimals to NULL
    if (in_array($key,$decimalKeys) && $value === '') {
        $modx->log(modX::LOG_LEVEL_INFO, 'NULL was reset for: ' . $key);
        $object->set($key, NULL);
    }
}

// Save changes
$object->save();

return true;

[Bruno17]

I don't think, this is a MIGX - specific issue, but XPDO or php/mysql/locale - settings or something