Including SQL Transformation in the QueryBundle and Deprecating Standalone Service
This document is about a proposed replacement for the POST /table/sql/transform service. It is tracked by PLFM-6275 - Getting issue details... STATUS .
Background
The POST /table/sql/transform service is used to transform a complex Synapse Table query object, which may contain filters applied via SQL and selectedFacets, to a single SQL query string.
In the web client, this SQL query is provided to the user so that
They may inspect the SQL and alter it, especially to accomplish complex queries not supported by the web client
The SQL query may be passed to a programmatic client (where the SQL query may be altered further as above).
The service was created because at some point, the web client could not reliably use the backend SQL parser utilities: PLFM-5498 - Getting issue details... STATUS .
Since the service was created, an additionalFilters field was added to the Query object to provide another option for adding a filter to a table query. See
PLFM-6192
-
Getting issue details...
STATUS
for more information.
This has created an issue in the web client where a filter may be applied via additionalFilters, but is not shown in the SQL provided by the Transform SQL service. See
PORTALS-2308
-
Getting issue details...
STATUS
for more information.
Proposal
We propose adding the SQL transformation operation to the QueryBundleRequest + QueryResultBundle and deprecating the existing SQL transform service.
We want to move the transformation operation to the bundle because
The transformation request object should be the same as, or tightly-coupled with, the Query object used to make a request. If a new field is added to the Query object, then the transformation service API should immediately support it.
We are not aware of a use case where a client may request SQL for columns that don’t exist--instead of requiring the client to include column models in the request, the SQL can be generated using the actual column models in the table.
The client would lose the ability to generate SQL for an arbitrary set of columns, but we do not believe that to be a real use case.
API Changes
We propose adding the following field to the QueryResultBundle object:
Field | Type | Description |
|---|---|---|
transformedSql | STRING | A SQL statement that would return the same results as the request Query object. |
The transformedSql field would be returned if the QueryBundleRequest partMask includes the appropriate bit flag (e.g. 0x80).
The new field will properly transform all fields in the Query object, including additionalFields, limit, offset, and sort.
We will deprecate the POST /table/sql/transform. Once we verify no clients are using the service (we suspect the web client is the only client that makes the request, but have not confirmed), it will be removed.
Examples
The following examples show current and proposed behavior for the service.
Scenario | Request Body | Currently Supported by existing service | Response transformedSql |
|---|---|---|---|
No selectedFacets, no additionalFilters (existing implementation) | {
"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
"query": {
"sql": "SELECT * FROM syn123"
"selectedFacets": [],
"additionalFilters": [],
"offset": null,
"limit": null,
"sort": []
}
} | ✅ |
|
Uses a selected facet, no additional filters | {
"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
"query": {
"sql": "SELECT * FROM syn123",
"selectedFacets": [{
"concreteType": "org.sagebionetworks.repo.model.table.FacetColumnValuesRequest",
"columnName": "col1",
"facetValues": ["a", "b"]
}],
"additionalFilters": [],
}
}The column model for {
"name": "col1",
"columnType": "STRING",
"enumValues": ["a", "b", "c"],
"facetType": "enumeration"
} | ✅ |
|
ColumnSingleValueQueryFilter | ❌ |
| |
ColumnMultiValueFunctionQueryFilter | ❌ |
| |
TextMatchesQueryFilter | ❌ |
| |
Kitchen Sink
| ❌ |
|