Support public synonyms in CAP ( support service with empty namespace )

[pepl]

Hi!

Let me please describe my use case. I would like to serve the following cds:

@cds.persistence.exists
entity ![VIEWS] {
    SCHEMA_NAME : String;
    VIEW_NAME  : String
}<br>

For that purpose I need to have a service like that:

service public {    
    entity VIEWS ...
}<br>

The problem here - this kind of service will expect to have a synonym like public.TABLES in the user schema, but what I want - is just to select from VIEWS/TABLES and other public Hana synonyms without the namespace.

Therefore what my suggestion is - allow us to define an anonymous service:

service {    
    entity VIEWS ...
}

an alternative solution, along with path we may allow to redefine the namespace:

@namespace: ''
service public {    
    entity VIEWS ...
}

So as a result I expect CDS to generate select from VIEWS, and also to support nested association joins. So namespace public. must be in use with a special directive.

What do you think of this proposal? Do you think is feasible?

Which benefits we may reach:

- by use of public synonyms we may build service which won't need any deployment

- it will create space for numerous utilities on tob of standard tables ( for example CDS generators )

Thank you!

[gregorw]

Hi Petr,

I had had the same challenge now and tried different variants. Based on what I saw as the generated SQL in the SAP HANA Database Explorer where FROM "PUBLIC"."TABLE_COLUMNS" is used:

I've tried this variants without success:

a) Synonym on the Public Synonym:

Error: com.sap.hana.di.synonym: Database error 7: feature not supported: cannot create synonym for synonym: line 1 col 52 (at pos 51) [8201003] at "src/tableColumns.hdbsynonym" (0:0)

b) View doing a select on the Public Synonym:

Error: com.sap.hana.di.view: "PUBLIC"."TABLE_COLUMNS": the reference has to be schema-local in "VIEW" "PUBLIC_TABLE_COLUMNS" [8250002] at "src/PUBLIC_TABLE_COLUMNS.hdbview" (16:6-30)

But finaly I solved it with a Synonyms to SYS. Check this commit:

tableColumns.hdbsynonym

Best Regards
Gregor

[Daniel7]

Btw. in case of sqlite this one works without any custom logic:

service SQLite {
  @cds.persistence.exists entity Schema {
    key name : String;
    tbl_name : String;
    type     : String;
    rootpage : Integer;
    sql      : String;
  }
}


// just to have some content:
entity Foo { key ID : UUID; }

Requests will be turned into database queries like SELECT ... from sqlite_schema.

[Daniel7]

I think you already have found the best solution. Maybe a bit simplified and less hacky:

service public {
  @cds.persistence.exists entity Views {
    key schema_name : String;
    key view_name : String;
  }
}

with this service implementation:

module.exports = function(){
  this.before ('READ','Views', req => req.query.SELECT.from.ref = ['views'])
}

I don't think we should introduce additional concepts like anonymous services for that.

Best regards,
Daniel

[pepl]

Hi Daniel, thank you for your reply.

Well - that's correct - we can replace from.ref

But point is - there may be also $expand options which will be converted to joins.

So we will have to scan the whole cdn to find all places where public. namespace is used.

So That's why I've came with string processing as it supports nested objects.

It's great that it can support SQLite already , that's the exact idea how I would love to use it for Hana out of the box ( with some instruction of course )

so coming back to your example:

why not like this then ?

service {
  @cds.persistence.exists entity Schema {
    key name : String;
    tbl_name : String;
    type     : String;
    rootpage : Integer;
    sql      : String;
  }
}


// just to have some content:
entity Foo { key ID : UUID; }

what was the role of SQLite? If it's not in use - may be we can just skip that. Especially that path may be controlled with a dedicated annotation.

What do you think of this proposal to allow nameless services?

Thanks!

[pepl]

Here is the way how I solved this task in a hacky way as usual 😃

const cds = require('@sap/cds');

module.exports = class extends cds.ApplicationService {
  async init() {
    this.before('SELECT', (req) => {
      Object.assign(
        req.context.query.SELECT,
        JSON.parse(
          JSON.stringify(req.context.query.SELECT).replaceAll('public.', '')
        )
      );
    });
    await super.init();
  }
};

basically I'm replacing all public.* entities without this namespace. I've tested it with a simple select and with expand as well. Works like a charm - now I can start writing own CDSgen 😃