View Source

This document is about a proposed replacement for the POST /table/sql/transform service. It is tracked by .

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: .

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 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 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": []
	}
}

✅

SELECT * FROM syn123

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 col1 is the following:

{
	"name": "col1",
	"columnType": "STRING",
	"enumValues": ["a", "b", "c"],
	"facetType": "enumeration"
}

✅

SELECT * FROM syn123 WHERE ( ( "col1" = 'a' OR "col1" = 'b' ) )

ColumnSingleValueQueryFilter

{
	"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
	"query": {
		"sql": "SELECT * FROM syn123",
		"additionalFilters": [{
			"concreteType": "org.sagebionetworks.repo.model.table.ColumnSingleValueQueryFilter",
			"columnName": "col1",
			"operator": "LIKE",
			"values": ["a", "b%"]
		}]
	}
}

❌

SELECT * FROM syn123 WHERE ( ( "col1" LIKE 'a' OR "col1" LIKE 'b%' ) )

ColumnMultiValueFunctionQueryFilter

{
	"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
	"query": {
		"sql": "SELECT * FROM syn123",
		"additionalFilters": [{
			"concreteType": "org.sagebionetworks.repo.model.table.ColumnMultiValueFunctionQueryFilter",
			"columnName": "col1",
			"function": "HAS"
			"values": ["a", "b"]
		}],
	}
}

❌

SELECT * FROM syn123 WHERE ( ( "col1" HAS ('a', 'b') ) )

TextMatchesQueryFilter

{
	"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
	"query": {
		"sql": "SELECT * FROM syn123"
		"additionalFilters": [{
			"concreteType": "org.sagebionetworks.repo.model.table.TextMatchesQueryFilter",
			"searchExpression": "full text search query"
		}],
	}
}

❌

SELECT * FROM syn123 WHERE ( ( text_matches('full text search query') ) )

Kitchen Sink

Filter in SQL
Filter from facet
Filter from each kind of additionalFilter

{
	"concreteType": "org.sagebionetworks.repo.model.table.QueryRequestBundle",
	"query": {
		"sql": "SELECT * FROM syn123 WHERE \"col2\" = 1",
		"selectedFacets": [{
			"concreteType": "org.sagebionetworks.repo.model.table.FacetColumnValuesRequest",
			"columnName": "col1",
			"facetValues": ["a", "b"]
		}],
		"additionalFilters": [{
			"concreteType": "org.sagebionetworks.repo.model.table.ColumnSingleValueQueryFilter",
			"columnName": "col1",
			"operator": "LIKE",
			"values": ["a", "b%"]
		},
		{
			"concreteType": "org.sagebionetworks.repo.model.table.ColumnMultiValueFunctionQueryFilter",
			"columnName": "col1",
			"function": "HAS"
			"values": ["a", "b"]
		},
		{
			"concreteType": "org.sagebionetworks.repo.model.table.TextMatchesQueryFilter",
			"searchExpression": "full text search query"
		}],
		"limit": 5,
		"offset": 20,
		"sort": [{
			"column": "col1",
			"direction": "ASC"
		}]
	}
}

❌

SELECT * FROM syn123
WHERE
   ( \"col2\" = 1 )
   AND
   ( "col1" = 'a' OR "col1" = 'b' )
   AND
   ( "col1" LIKE 'a' OR "col1" LIKE 'b%' )
   AND
   ( "col1" HAS ('a', 'b') )
   AND
   ( text_matches('full text search query') )
LIMIT 5
OFFSET 20
ORDER BY "col1" ASC