The OData version 4 standard is large, and Invantive Bridge Online reads the part of it that serves a download. This topic states which part that is, and just as precisely which part it is not, so that a request can be written without having to try it first. What is not listed here is not offered. A request using it is refused, with one exception which is named under Deviations From the Standard below. ## Addresses Two addresses are offered: - the entity set, which retrieves the rows of one table; - `$metadata`, which retrieves the data model of the database. The following addresses of the standard are not offered: - the service document, which is the address ending in `odata4/` with no table behind it; - a single entity by its key, as in `.../odata4/<Table>(<key>)`; - a navigation property behind an entity, as in `.../odata4/<Table>(<key>)/<Navigation>`; - `/$count`, `/$value` and `/$ref` as a path segment; - `/$batch`, so several requests are never bundled into one; - actions and functions. ## Read-Only Only the HTTP method `GET` is offered. `POST`, `PATCH`, `PUT` and `DELETE` are not, so data is never written through this protocol. Writing data is what [[Invantive App Online/Invantive App Online|Invantive App Online]] and [[Invantive Data Hub/Invantive Data Hub|Invantive Data Hub]] are for. ## System Query Options The following system query options are read: - `$select`: the columns to retrieve, separated by commas. A column not named is not retrieved from the platform either, which makes this the cheapest way to shrink a download. - `$filter`: the condition the rows have to meet. The operators and functions allowed in it are listed below. - `$orderby`: the columns to sort by, each optionally followed by `asc` or `desc`. - `$top`: the maximum number of rows. - `$count`: with the value `true`, the number of rows instead of the rows. - `$apply`: a grouping with its totals instead of the rows. Available from release 27.0. - `$skip`: only with the value `0`, which has no effect. The following system query options are not read: - `$skip` with a value other than `0`, and `$skipToken`, because the protocol offers no paging. - `$expand`. The data model holds no navigation properties, so there is nothing to expand. - `$search`, `$compute`, `$index`, `$schemaversion` and `$levels`. - `$format`. The answer is always JSON. ## Filter Operators The following operators are read in a `$filter`: - comparison: `eq`, `ne`, `gt`, `ge`, `lt`, `le`; - logic: `and`, `or`; - membership: `in`, with the values between brackets; - arithmetic: `add`, `sub`, `mul`, `div`. The following operators of the standard are not read: `not`, `mod`, `has`, `any`, `all`, `cast` and `isof`. Two shapes are folded into something a platform accepts more easily. A condition which excludes values of one column, such as `DivisionName ne 'ACME BV' and DivisionName ne 'ACME Holding BV'`, becomes a single exclusion list. A condition which asks for a set of rows as alternatives, one per key, becomes the conditions the alternatives share plus a membership list on the column that differs. Both keep a request with many keys within the limits of the platform behind it. A value named more than once in a membership list is asked for once, so a client may repeat itself without paying for it. ## Filter Functions Four functions are read in a `$filter`: - `concat(<a>,<b>)`: the two values joined; - `contains(<column>,'<text>')`: the column holds the text somewhere; - `startswith(<column>,'<text>')`: the column begins with the text; - `endswith(<column>,'<text>')`: the column ends with the text. Every other function of the standard is not read, among them `tolower`, `toupper`, `trim`, `length`, `substring`, `indexof`, `matchesPattern`, `year`, `month`, `day`, `date`, `time`, `now`, `round`, `floor`, `ceiling` and the geospatial functions. The three text functions become a pattern comparison, in which the per cent sign and the underscore carry a meaning of their own: the per cent sign stands for any number of characters and the underscore for exactly one. A searched value holding either of them therefore matches more than it appears to, so `contains(Code,'1_0')` also finds `100` and `140`. ## Grouping and Totals The `$apply` system query option is read from release 27.0 onwards, in four shapes: - `groupby((<c1>,...,<cn>),aggregate(<totals>))`: one row per combination of the named columns, with the totals per combination; - `groupby((<c1>,...,<cn>))`: one row per combination of the named columns, without totals; - `aggregate(<totals>)`: one row holding the totals over the whole table; - `filter(<condition>)/` in front of any of the three, which drops rows before they are grouped. Six totals are read: `$count`, `min`, `max`, `sum`, `average` and `countdistinct`. Each is written as `<column> with <total> as <name>`, except the number of rows, which is written as `$count as <name>`. The following shapes are refused: - a total over a calculation instead of over a column, such as `aggregate(AmountDC mul 2 with sum as Doubled)`, because the grouping is meant to reach the platform and no calculation of ours travels with it; - a grouping over a navigation path; - one name used twice in the same answer, whether between two totals or between a total and a grouped column; - more than one grouping, and a total over the groups as in `groupby((<c>))/aggregate($count as Groups)`; - the transformations `compute`, `expand`, `concat`, `topcount`, `topsum` and `toppercent`, and their bottom counterparts. Beside a `$apply` the options `$top` and `$count` are read, and the options `$filter`, `$orderby` and `$select` are refused. All three would name columns of the grouped answer rather than of the table, and answering them over the table would answer a different question without saying so. A condition on the rows belongs inside the `$apply` as `filter(...)/groupby(...)`. A condition on the totals themselves is not offered at all. Grouping distinguishes upper case from lower case, and it distinguishes a trailing space. Two values differing only in case are therefore two groups, exactly as they would be in a query written by hand. ## Number of Columns The data model offers at most 1,000 columns per table. A table with more columns is offered with its first 1,000, and the rest is absent from both the data model and the answer. The limit exists because Microsoft Power BI becomes very slow on extremely wide tables. ## Deviations From the Standard Three points differ from what a reader of the standard expects, and all three are deliberate: - `$count=true` answers the number itself, as a bare number in the body, instead of a collection carrying `@odata.count`. A request therefore asks either for the rows or for their number, never for both at once. - There is no server-driven paging and no `@odata.nextLink`. - A refused request is answered with HTTP status 500 instead of HTTP status 400. One further point is a limitation rather than a choice. A `$filter` holding a function which is not read is not always refused: the condition may be dropped from the query instead, and the answer then holds more rows than were asked for. Only the four functions listed above may therefore be used in a `$filter`.