A Snowflake development environment — Part 3 — oData & Restful
Web UIs using data stored in Snowflake need a Restful interface that is secure but also flexible. This is the third functional area of the AppContainer solution.
From a user point of view it is very simple: Every Snowflake database view and database table can be selected from and every Snowflake Stored procedure invoked with input and output.
It starts with a straight forward query method where any kind of select statement is passed in as parameter an the data is returned as Json. All the user permissions get applied, so the user can only read from tables he is entitled to and sees the columns and rows he has permissions on.
Similar is the lookup endpoint except it returns a Json object with a single row instead of a Json array with multiple rows. This is important for some UI controls when they can deal with single objects only.
For visualization queries often nested data must be created out of relational queries. Example: select table_schema, table_name from tables. The json returned by a query would be many pairs of records. The group query returns one record per schema and within an array of all tables. Instead of converting the data in the UI manually, this endpoint does the conversion.
The endpoint for executing stored procedures can be used for reading data as well but more important, insert data in a consistent and validated way into the database.
oData
Common to all these plain Restful endpoints is that they lack more advanced capabilities. How about pagination — the capability to read the first n-rows and then fetch the next? A programmatic filter option? Building the select statement based on the fields needed by the UI?
All these capabilities are provided by the oData protocol, a special kind of Restful endpoint according to the oasis standard.
For reading tables the oData protocol is very well suited and used a lot in the various UI5 controls. Hence every view and table is available as oData endpoint automatically.
Example: Snowflake has the view TABLES in the INFORMATION_SCHEMA of the currently connected database. Hence specifying this link AppContainer/protected/odata/INFORMATION_SCHEMA/TABLES/$metadata returns the metadata of the view.
Replacing the url .../$metadata with.../TABLE?$select=... selects the rows and returns the data as Json/XML table structure.
In UI5 controls all the backend interactions are created automatically. Only the endpoint is specified, rest is done by the controls.
