API updating and inserting rows in data tables

[andrej98]

Hello, the API for inserting new record and updating existing record does not work as expected. In DataRecordService.php in insertData and updateData functions this block of code checks if the columns in the request match the columns in the entity.

$columns = $this->dbStructureDAO->getColumns($table->getName());
foreach ($newData as $k => $v) {
    if (!array_key_exists($k, $columns))
        return array("response" => Response::HTTP_BAD_REQUEST, "result" => null);
}

The problem is that this line $this->dbStructureDAO->getColumns($table->getName()); returns column names in lowercase, so you have to send your request body in lowercase even though the actual fields are in camelCase. This is in 5.0-dev branch, in master there is isset($columns[$k]) instead of array_key_exists($k, $columns), but it does not change the behaviour.

I am able to insert the data when I put all my columns in lowercase.

However, updating of the column that is in camelCase does not work at all, because in DBDataDAO.php in function updateRow we compare the column name in $k which had to be sent in lower case to pass the check from DataRecordService.php even though in DB it is in camelCase. Then we compare it to $col->getName() that returns the actual DB column name with camelCase.

$cols = $this->connection->getSchemaManager()->listTableColumns($table_name);
        ....
            foreach ($cols as $col) {
                if ($col->getName() == $k) {
                    $found = true;
                    break;
                }
            }
            if (!$found) continue;

For this reason, fields in camelCase cannot be updated at all.

Concerto Platform version

5.0.27, branches master and 5.0-dev

Expected behavior

I send POST/PUT API request to /api/data/<table>/<id> for update and /api/data/<table> for insert with the same case that is in my data tables. For example:

{
    "fixedIndex" : 2,
    "trait" : "GLy16",
    "question" : "K6",
    "p1" : 1,
    "p2" : 1,
    "p3" : 0,
    "p4" : 1,
    "skippable" : 0,
    "instructions" : "",
    "itemSet" : "Gly16",
    "responseLabel1" : "1",
    "responseValue1" : "1",
    "responseScore1" : 1,
    "responseLabel2" : "0",
    "responseValue2" : "0",
    "responseScore2" : 0,
    "type" : "options"
}

Actual behavior

The response is 400 BAD REQUEST. I have to send it in lowercase:

{
    "fixedindex" : 2,
    "trait" : "Gly16A",
    "question" : "K6",
    "p1" : 1,
    "p2" : 1,
    "p3" : 0,
    "p4" : 1,
    "skippable" : 0,
    "instructions" : "",
    "itemset" : "Gly16A",
    "responselabel1" : "1",
    "responsevalue1" : "1",
    "responsescore1" : 1,
    "responselabel2" : "0",
    "responsevalue2" : "0",
    "responsescore2" : 0,
    "type" : "options"
}

Insert works in lowercase but update does not update camelCase columns such as itemSet.

Steps to reproduce the issue

Send API POST request with the same case that is in your data table.