Cannot filter /schema/objects endpoint

[MichalisDBA]

Can not filter /schema/objects endpoint to search for specific schemas, tables, views.

/schema/objects?filter=schema==
/schema/objects?filter=name==
/schema/objects?filter=type==

[thadguidry]

Hi @MichalisDBA is this where you want to expose schema objects in your application? Was this so you can expose in a selector some categories or particular domains to your app users? Or did you have some other uses in mind of schema selection in your application you are building with DB2Rest?

Learning more about your use cases will help us understand better and perhaps come up with more logical filtering mechanisms to expose schema objects when the db user configured has those rights.

[MichalisDBA]

Hi @thadguidry maybe would be better to be configurabled via env variables the schema/objects path.
My use case is to see what objects are loaded and available to me so i can query them.

That way i can give a user a single endpoint that he can see what objects are available to him to query instead of maintaining a list for every user of what he can query.

[thadguidry]

Righto. OK. Hmm, isn't there a nice custom SQL expression to use instead for this with Oracle where you can query the USER_OBJECTS view? I know I can do this easily in PostgreSQL.
Sorry, but I've long forgotten my Oracle skills and the differences between stupid things like local user and common user etc.

Regardless, this https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/managing-schema-objects.html#GUID-827749E0-E67C-44DB-ABA4-2596167F2EAE in section 17.13 does seem to resonate with this issue. Perhaps custom query to return ALL_OBJECTS or USER_OBJECTS views?

Let me know.

[thadguidry]

We "could" in theory more easily expose this through the API via something that might look like:

http://localhost:8080/?objectsOwned
http://localhost:8080/?objectsAll

or maybe /user?objectsOwned ? Wait NO, that syntax would be asking for the user table and a query parameter ... hmm. We need to come up with some good syntax for doing schema queries easily, while keeping our /<table>? convention. Maybe some keyword before the / ? or special parameter keywords directly after the / as my examples above?

@dhrubo ?

[kdhrubo]

JDBC meta data builder queries these tables under the hood. Because the driver is implemented by the database vendor.

We rely on the metadata to provide the endpoint.Essentially it serves from the schema cache.

We can add the filters to this endpoint. Should be easy as the type of object is also in the cache.

[MichalisDBA]

Sorry i think you misunderstand me. I am talking about the objects that are cached when you start db2rest.

db2rest introduced an /schema/objects endpoint that you can view all the objects that are cached from the application. You do not need to query the database system tables. Just the filtering does not work.

[kdhrubo]

Will add filters in 0.2.9 😁

[thadguidry]

well, the problem with that endpoint is that...

• In PostgreSQL and others, you can actually have a table name called "schema". So that endpoint convention sorta breaks/rubs against our existing table syntax convention. You cannot have objects prefixed with pg_ however as they are reserved for system schemas. DB's have lots of reserved object names and differ by vendor.

@kdhrubo Not so sure about an endpoint named like that?

[kdhrubo]

If a table named schema is present in user schema it will be cached and returned by this query.

[thadguidry]

Huh? So confused. Then how do I tell it the difference between...

1. http://localhost:8080/schema <- my user created table named "schema" that holds some stupid video movies
2. http://localhost:8080/schema <- what JDBC meta data builder exposes, which under the hood, I assume it would do this:

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'YOUR_USER';

[kdhrubo]

This endpoint ends with /objects so the MVC controller will use this one when the query is for db objects

When you want to view the records of the schema table then that endpoint does not end with /objects. This is how we can differentiate. Similar to the join syntax.

I am not sure what query the JDBC driver actually runs under the hood.

[thadguidry]

@kdhrubo I understand that. But that's like saying your sub-collection is dictating the actual real collection. But I am saying that it is not the way to do it properly at all. It actually breaks the REST best practices with Nesting to show Relationships between Sub-collection Resources, as well as noun pluralization for Collection Resources.

/posts/author
/posts/postId/comments
/schema
/schemas
/schemas/objects

We are doing it Non-RESTful then.
https://restfulapi.net/resource-naming/
https://www.javaguides.net/2018/06/restful-api-design-best-practices.html

And I don't really like that or appreciate that we break many RESTful best practices.

[kdhrubo]

Not really it's very common to use command or controller pattern for such scenarios.

If you can suggest a better name for this end point we can make changes.

[thadguidry]

It's very common that 1000's of developers do it WRONG. I absolutely know that. It's a common misunderstanding that gets abused in command and controller patterns because of lack of knowledge of RESTful Collection Naming conventions. We're the experts here and should stick to correct conventions of industry norms.

I'd suggest that we stick to our original plan of the first Collection to always be the <table name> resource.
I'd further suggest that when we don't care about dealing with Tables as the first Collection default resource, but instead system commands, that we can adopt a prefix convention such as $:

http://localhost:8080/$schema
http://localhost:8080/$version
http://localhost:8080/$help
etc...

This is like saying, give me the resource document for schema, version, help, etc.
When there is no document prefix, then we treat that first Collection resource as a <table name>

Google does this sorta thing for their API's, for example, play around

https://eventarc.googleapis.com/$discovery/rest?version=v1
https://eventarc.googleapis.com/$discovery

Btw, even JSON fields (keys) can use prefix conventions for various reasons:

"response": {
  "$ref": "GoogleLongrunningOperation"
}

When a <table name> resource literally named $myTable then we ask for quoted table names.
https://localhost:8080/"$myTable"

The URL https://localhost:8080/"$myTable" needs to be properly encoded for use in web requests. Let’s break it down:

Original URL: https://localhost:8080/"$myTable"
URI Encoding:
The $ character should be encoded as %24.
The double quotes (") should be encoded as %22.
Encoded URL: https://localhost:8080/%22%24myTable%22
Now you can use the encoded URL in your web requests! 😊

[kdhrubo]

Makes sense..we can adopt the $ pattern.

Can you pls review the join syntax and recommend there (a separate issue please) 🥺

[kdhrubo]

[kdhrubo]

[kdhrubo]

Filter by schema name (like search on schema cache)

[kdhrubo]

[kdhrubo]

Search by type = table

Search by type = view

[kdhrubo]

Will be available in 0.2.9 release

[kdhrubo]

@MichalisDBA - please share your feedback on this issue. let us know if it can be closed.

[MichalisDBA]

@kdhrubo

Yes it is working fine. Just mentioned in the docs that to search for a table you have to use $schemas?filter=name==search and not use =like= operator.

[kdhrubo]

Closing follow up here -
9tigerio/db2rest-web#45