MsSql/PgSql : Schema Validation for REST requests #99
Assignees
Labels
mssql pgsql mysql
an issue that applies to all relational databases, same as labeling with `mssql` `mysql` and `pgsql`
Projects
Milestone
Comments
Aniruddh25
added
the
mssql pgsql mysql
Aniruddh25
added a commit
that referenced
this issue
Nov 23, 2021
## Summary Alongwith graphql, DataGateway Service should support REST api requests too. This change introduces the necessary classes that help add REST support and implements the GET verb for the FindById operation for **MsSql** only. ## How - Adds a `RestController` to accept routes matching `https://localhost:5001/users/id/1?_f=id,username` where `users` is the `entityName`, `id` is the primary key field name, `1` is the value for the primary key `_f` in the query string is the keyword used for the selected field names. - For composite primary keys, the route would be `https://localhost:5001/users/id/1/partition_key/200?_f=id,username` where `id` and `partition_key` together form the primary key. - Adds a `RestService` to handle the request by invoking the `RequestParser` to parse the request and populate the `FindQueryStructure` class which holds the major components of the query to be generated - `MsSqlQueryBuilder` `PostgresQueryBuilder` use the `FindQueryStructure` class to build the required query for the FindById operation. ## Testing - Tested using PostMan that the route for FindById returns expected Json document when given no fields and also with specific fields. - Added `MsSqlRestApiTests` to test the same. Moved some common test code to `MsSqlTestBase`. ## Motivation and future thoughts - This change uses some of the request parsing logic from existing work done here [SqlRestApi](https://msdata.visualstudio.com/DefaultCollection/Database%20Systems/_git/SqlRestApi). In future, for addition of filter clause etc, we can similarly reuse that parsing logic. - It is also inspired by the draft PR(#55) for query generation for GraphQL. The `FindQueryStructure` is similar to the `SqlQueryStructure` class from that PR(#55). `FindQueryStructure` could be useful for CosmosDb query generation so the class `SqlQueryStructure` should be derived from `FindQueryStructure`. Once this and the draft PR are merged in, queries for SQL like databases will be autogenerated for both REST and GraphQL. - When support for CosmosDb query generation #71 is added to the mix, its possible to further redesign the query generation class structure. - For other REST verbs like POST, PUT, DELETE, we would need similar classes like `FindQueryStructure`, at which point again more abstraction would be needed. - [Routing](https://docs.microsoft.com/en-us/aspnet/core/fundamentals/routing?view=aspnetcore-6.0) ## Issues to be addressed in future - Schema Validation - verify PrimaryKey matches the one obtained from the schema tracked by issue : #99 - Tested against PostgreSQL but it needs parameter names in the query to be type casted to the right type. This would need reading schema from the database and add the parameter accordingly. This is tracked by issue : #103 - Do we need streaming ? #102
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Verify PrimaryKey field names sent in the url match the ones obtained from the table schema
The text was updated successfully, but these errors were encountered: