Does @cap-js/hana support quoted sql mapping?

[ThePlenkov]

Hi! Following the advice from SAP we've migrated to @cap-js/hana. However it seems like this connector doesn't support yet quoted sql mapping. At least our sql queries fail with error like Invalid table name: Could not find table/view API_ODATA_BUCKET_BALANCE_MONTHLY while we have it generated like this:

VIEW "api.odata.bucket_balance_monthly" AS SELECT

[johannes-vogel]

not yet, Pr still pending #681

[ThePlenkov]

just as an idea to what could be a solution. Since we have a source table like "schema.table" which gets converted to SCHEMA_TABLE, and since we generate also hdbtable, hdbview. Isn't it a good idea also to delegate to framework to generate hdbsynonym files? That would help us may be to migrate from quoted mapping if we could just rebuild the project with synonyms, generated from the model. What do you think about this? Thanks!

[ThePlenkov]

So let's say in case of the entity like:

@cds.persistence.exists: true
entity namespace sap.fe.cap.Travel {
...
}

we will not generate hdbtable ( because it exists ), but we'll generate synonym ( it's ok if it's for same schema )

like
{ " SAP_FE_CAP_TRAVEL " : {
object: "sap.fe.cap.Travel"
}
}

[ThePlenkov]

I've just got a wild issue finding out why quoted is important.

So somehow our data team managed to create tables with columns called "timestamp" in lower case

So I have a view like:

@cds.persistence.exists: true
entity Entity {
...
      @title: 'Expiration Date'
      EXPIRY_TS              : Timestamp;
      timestamp              : Timestamp;
};

which ends up in hdbview once I consume it:

  asset_relationships_0.expiry_ts,
  asset_relationships_0."TIMESTAMP",
  asset_relationships_0.effective_ts,

because of this "TIMESTAMP" i have an error during deployment

Error: com.sap.hana.di.view: Database error 260: : invalid column name: BIZENTASSETRELATIONSHIPS_0.TIMESTAMP:

This transformation happens in a function smartId which automatically transforms lowercase words like "timestamp" to upper case if it's one of the reserved words.

Unfortunately this makes us not possible to use plain sql mapping in our project anyhow. Because we cannot even deploy artifacts for such tables.

May be it's a complex issue and it also needs to be addressed to a relavant team.

[ThePlenkov]

@johannes-vogel do you know which team to address mentioned above problem with cds compiler which compiles hdbview which is not possible to deploy if sql mode is plain? Thanks!

[ThePlenkov]

@danjoa Daniel, do you know may be - is it possible to generate hdbview with columns like "timestamp" in a lower case if plain mode is used? Thanks!

[ThePlenkov]

@danjoa one more point about usage of npm dependencies. Now to test the logic with and without @cap-js/hana I have to change package.json dependencies and reinstall packages every time. This is very inconvenient. I'm sure there must be a better way not to use hew driver / plugin even if it's being installed, right? Thanks!