change-tracking can't handle non-ID named key fields and sends invalid query

[patricebender]

1. cds init change-tracking-bug
2. cd change-tracking-bug
3. npm add @cap-js/change-tracking
4. npm i
5. touch db/schema.cds Add the following content to db/schema.cds:

namespace my.bookshop;

entity Books {
  key NOT_ID : Int16;
      @changelog
      title  : String;
}

6. touch srv/api.cds Add the following content to srv/api.cds:

using { my.bookshop as shop } from '../db/schema';

Service CatalogService {
    entity Books as projection on shop.Books;
}

7. touch test/CatalogService.http Add the following content to test/CatalogService.http:

@server=http://localhost:4004
@username=alice
@password=

### Books
# @name Books_POST
PUT {{server}}/odata/v4/catalog/Books(NOT_ID='42')
Content-Type: application/json
Authorization: Basic {{username}}:{{password}}

{
  "title": "title-4711"
}

8. cds watch and then fire the request in test/CatalogService.http
9. Observe the error:

[error] - 500 - Error: "ID" not found in the elements of "CatalogService.Books"
    at stepNotFoundInCombinedElements (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:794:13)
    at /Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:501:11
    at Array.forEach (<anonymous>)
    at inferArg (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:441:13)
    at processToken (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:307:11)
    at Array.forEach (<anonymous>)
    at walkTokenStream (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:311:19)
    at inferQueryElements (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:280:16)
    at infer (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/infer/index.js:71:22)
    at cqn4sql (/Users/patricebender/SAPDevelop/change-tracking-bug/node_modules/@cap-js/db-service/lib/cqn4sql.js:77:14) {
  code: ''
}

10. The issue is that the change-tracking logic expects an ID field by default, but the entity uses a different key field name (NOT_ID). This results in the error when trying to process the request.

Here is the loc which generates the faulty query:

const getCurObjFromDbQuery = async function (entityName, queryVal, /**optional*/ queryKey='ID') {
  if (!queryVal) return {}
  // REVISIT: This always reads all elements -> should read required ones only!
  const obj = await SELECT.one.from(entityName).where({[queryKey]: queryVal})
  return obj || {}
}

→ this is the invalid query which is send to the db-service (which is rightfully rejected):

{
  "SELECT": {
    "one": true,
    "from": {
      "ref": [
        "CatalogService.Books"
      ]
    },
    "where": [
      {
        "ref": [
          "ID" // ❌ There is no ID in Books
        ]
      },
      "=",
      {
        "val": "undefined" // ❌ this is also broken
      }
    ],
    "expand": "root"
  }
}

[Sv7enNowitzki]

Hi @patricebender ,

This is a known limitation. Currently, the plugin only supports root entity where the key is an ID, and it must be a single key as shown below:

entity Books {
  key ID : UUID;
  ...
}

Hello @schiwekM , maybe we can merge this issue into #130. I've already merged similar issues into #130 previously.

Best Regards,
Wenjun

[patricebender]

Hi @Sv7enNowitzki,

thanks for letting me know. Somewhere in the back of my mind I knew that I encountered something similar before. Probably with a composite primary key.

As this leads to hard-to-debug issues and is not obvious at all, it would be beneficial to document this limitation more prominently. How about mentioning this in the readme of this plugin? Even better would be an error if your model prerequisites are not fullfilled.

BR

[schiwekM]

Hi Patrice and @Sv7enNowitzki,

this issue and #130 are now fixed by the linked PR to support singular keys not named ID. @stefanrudi is in the process of a larger refactoring leveraging DB triggers and with that we would also support composite keys to lift the assumptions about the model.

BR,
Marten