Same Table / View names, different Databases, Collision Problem

[MichalisDBA]

Hello.

There is a problem when you start db2rest and the user account has access to multiple databases and these databases have tables or views that have same names.

For example. We have in a MySQL server 2 databases.

DB1 and DB2.

Both these databases have a table named users.

So the schema looks like this

DB1.users and DB2.users

When you start db2rest with a user account that has access to these 2 databases, only the latest table name that is fetched is cached. In this example only DB2.users will be available in http://localhost:8080/users endpoint

I believe the API endpoint must change as @kdhrubo said to something like /db/schema_catalog/table

[kdhrubo]

@MichalisDBA @thadguidry
There can be headers to specify the catalog and schema.
Then a lookup will pick the correct table.

The schema thing was removed to enable joining across tables in a different schema. A restriction can be added if a schema is specified explicitly then only tables in that schema would be considered.

[thadguidry]

Sure, and later we'll just need to mention in Docs that schema restriction mechanism is not a replacement for good security practices with user account management in a DB and that our schema restriction config is only provided as a convenience option

[MichalisDBA]

So @kdhrubo will this solve the problem? Will be there a way to query both tables that have the same name but they are in different schemas?

[grabdoc]

This should solve the problem.

• for GET use header Accept-Profile to specify schema.

Then two different tables can be queried.

GET /users HTTP/1.1
Accept-Profile: DB1

GET /users HTTP/1.1
Accept-Profile: DB2

Joins are using POST and can ignore such header for now to allow multi-schema joins.

[kdhrubo]

@MichalisDBA this will work hope to make a release with these changes in a week.

[kdhrubo]

Closing this ticket with reference to the documentation/web project

kdhrubo/db2rest-web#45

[kdhrubo]

To be available in release 0.3.1

[MichalisDBA]

Hello.

Can confirm that now it is working.

With Accept-Profile: DB1 and Accept-Profile: DB2, tables that have the same name can now be queried. Also they appear at $schemas endpoint correctly.

Please mention it in the docs.