Design of read query - filter, join, fields, sort, pagination

[kdhrubo]

Current Syntax

<url_base>/{table/view}?select=<list_of_columns>&filter=<rsql_filter_statement>page=<page_size>&size=<page_size>&sort=<col_id>&sort=<col_name>,<sort_order>

• url_base : server base URL.
• table/view : root table or view.
• select: list of columns to be retrieved from the query execution. If the list is empty or the select request parameter is missing, then all the columns of the root table/view are retrieved. select can also specify implicit joins or resource embedding.

select=film_id:id,title,description,release_year:yearOfRelease,language_id;language(name)
In this case, DB2Rest checks if an FK relationship exists between film -> language tables. If a foreign key exits, then an implicit join is applied and data is retrieved. The retrieved rows - include columns from the root table and the field name from the language table.

This is a very concise syntax. However, it is difficult to understand by looking at it. Also, it results in complex parser code. In case, there is no foreign key between the tables, there is no way currently to specify an explicit join.

• filter : This results in the where clause of the SQL statement. This follows the RSQL specification. However, the RSQL specification only supports the columns of the root table. It also needs to support fields of any join tables going forward.

• page : page number of the search result to retrieve.

• size : specifies the number of records to retrieve

page and size are used to compute offset and limit for offset pagination.

Is it possible to combine page and size along with filter for more efficient seek pagination?

• sort: parameter list to specify the fields to sort and order. This one needs no change, is verbose, and easy to understand.

Proposal for new Syntax and defaults

1. Rename the select request parameter to fields
2. fields to include fields of both root and join table columns. (what if a table has two foreign keys to another table - viz. film has two fields language_id fields as foreign keys of the language table - then how this will be specified?. See below)
3. Introduce a new request parameter join or embed. There can be zero or more join parameters.
4. join=language: looks for an implicit join relation between the root table (e.g.: film) and the language table. This will apply inner join if the foreign key relationship is not nullable, otherwise left join is applied. Also, this will include all the columns from the language table as part of the columns list.
5. join=language?fields:language_id,name: Does same as in item#4, however only includes the columns language_id, name.
6. join=language?fields:language_id,name&condition:language_id, language_id; inner: In this case, explicit join is applied because the foreign keys are not defined. The columns language_id, and name are included. There is an explicit inner-join between the language_id of the root table (e.g. film) and the language.language_id column.
7. join=language?fields:language_id,name&condition:alternate_language_id, language_id; left: In this case, there is an explicit left-join between the alternate_language_id of the root table (e.g. film) and the language.language_id column.

In the case of no#6, no#7 - the first column belongs to the root table and the second column belongs to the non-root table. In case, the first table is non-root, use the <table_name>.<column_name> syntax.

If no column from the join table is to be included, specify that with -: e.g join=language?fields=-

In case of implicit join and foreign key is not found - raise the error unable to find table relationship.

Do not include any table alias only column alias are allowed.

8. The schema request parameter is to be removed completely. Schema/catalog to be detected based on access permission when looking up a table in the metadata cache.
9. filter - can include conditions for filtering based on join table columns - e.g 'year_of_release=eq=2023;director.country_id=eq=1' this will translate to

where 
       film.year_of_release = 2003
and director.country_id = 1

10. Seek pagination can be done with only size and filter conditions (thoughts ?)
11. Subquery - not supported - please use the custom query execution option.

That should be it. Please let me know your thoughts if anything I have overlooked or missed.

[thadguidry]

I feel like mapping or key/value construction should either use = or : , but your examples use ; and that feels awkward to me. Semicolon also can legitimately be part of an identifer in PostgreSQL when the identifier is quoted:

The semicolon (;) terminates an SQL command. It cannot appear anywhere within a command, except within a string constant or quoted identifier.
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

You'll need to account for usage of quoted identifiers somehow.

Let's breakdown this:
&condition:alternate_language_id, language_id; left
It can be subtraced as:
&condition:alternate_language_id; left
&condition:language_id; left <-- this guy feels weird and also "language_id;" itself might actually be an identifier (including the semicolon)...so...

More readable?
&condition(alternate_language_id, language_id AND left) <-- RSQL actual parse where semicolon is AND shortcut construct, but "AND left" seems it's lacking context of the join condition type and brings semantic ambiguity.

More readable and more context and allows for unicode and also multi-conditions in future?
&condition=(left:"alternate_language_id, language_id" right:"data_id" right:"U&"d!0061t!+000061" UESCAPE '!'") <-- but problem there on the right: join condition with the double quote and parser eagerness... so maybe ... dunno, ya'll need to research the naming conventions doc link above and come up with something. How to account for identifiers with escaping? (my eyes 👀 need a break)

[thadguidry]

what does | before condition mean?
kinda agree with @susanta-mukhopadhyay that the RSQL =INNER= can possibly be confusing. It's concise syntax, don't get me wrong, but I don't think you are addressing the fact that...

1. There are unquoted identifiers - fine, great.
2. But a database could also have and use quoted identifiers - <-- THINK and SHOW how this will work in the query URL syntax.

Quoted identifiers can contain any character, except the character with code zero.
Which means the below can happen in reality where even = or any char! except char(0) could be part of a table or field identifier (name).

join=language?fields=language_id,name|condition='_lang=uage=id=INNER=langua=ge_i=d;language_id=gt=10

We MUST account for quoted SQL identifiers (table identifiers, column identifiers, object identifiers, etc.) which can contain any characters, including Unicode chars including CJK multibyte chars. Database server encoding is UTF-8 nowadays by default.

You need to add double quote handling - i.e. maybe make it a rule that in the URL query syntax that all identifiers must be wrapped in double quotes. (as we did with the Freebase API - which turned into Google API's) somehow, someway.
That's what I was trying to tell you and show you in my last paragraph prior comment. Did you read the PostgreSQL Docs link in my comment? Please do.

[kdhrubo]

separator. I think this can be simplified bit further? merge the fields with table name with ()

[thadguidry]

The other thing that I don't see addressed is when a join has multiple keys joined...i.e. multiple ON statements.
Example:

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

[thadguidry]

BTW, I found out today that Groovy has a GQuery DSL https://groovy-lang.org/using-ginq.html

[kdhrubo]

This works on collection objects. Won't switch from mybatis dynamic.

[kdhrubo]

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

/weather?fields=city, temp_lo:low, temp_high:high
&join=weather?joinFields=city,temp_low:low,temp_hi:high&joinFilter=temp_lo=lt=temp_lo;temp_hi=le=temp_hi

So I am trying to keep this similar to the root table with fields, filter with joinFields, joinFilter for joinTable

Restriction - is you have to put this in order, db2rest will walk from root --> child 1 .... child n

Please share more of these scenarios so that the syntax can be firmed up.

[kdhrubo]

/fims?fields=id, title, year_of_release:yearOfRelease
&join=language?joinFields=language_id,name&joinFilter=language_id=eq=language_id
&join=language?joinFields=language_id,name&joinFilter=alternate_language_id=eq=language_id

How to indicate LEFT JOIN RIGHT JOIN?

[thadguidry]

joinType="left"

[kdhrubo]

Very difficult to do this with request params.
Hence changing to a new endpoint with POST with joins specified in the JSON body.

[kdhrubo]

@susanta-mukhopadhyay @thadguidry - take some time to review the syntax of ORDS

https://www.thatjeffsmith.com/archive/2019/09/some-query-filtering-examples-in-ords/

[kdhrubo]

What is request path syntax?

[thadguidry]

In my previous long comment above

Request syntax rules
Developing Oracle REST Data Services Applications
👀 About Request Path Syntax Requirements

[susanta-mukhopadhyay]

I agree with @thadguidry . Request path rules are good to take care in our queries.

[kdhrubo]

Very difficult to do this with request params.
Hence changing to a new endpoint with POST with joins specified in the JSON body.

[kdhrubo]

Let the syntax evolve now @thadguidry @susanta-mukhopadhyay @rajivmca2004 @souravroy @musibs

[kdhrubo]

Opening a new discussion for the join request body syntax evolution. This one is already way too long. We kind of all convinced on filter, fields, sort, pagination, and join. (We may also consider subquery in the future :))