Hey!
sorry - I tend to ramble on and not be clear in my posts - so apologies if I wasn't clear.
We are developing a new ReferenceData system that will serve multiple internal systems with this referencedata. For example - in the new ReferenceData system there will be a table called 'country'. This will hold various country related details - countryname, countrycode, currency, etc. and these internal systems will source this data rather to populate certain field attributes in their applications rather than contain referencedata tables themselves.
These internal systems will access the ReferenceData system via a GET request. The GET request's parameters are split into template/uri and query parameters. The REST API's template/uri parameters specify the table being queried, the REST API's query parameters wiill specify the columns (within the table being queried) (and some additional operators equivalent to a number of partial match operators as well as sorting the returned data). These internal systems can query the ReferenceData system's tables using ANY of the columns of the columns in the tables.
Essentially the GET request will execute the equivalent of an SQL select query with the following basic format:
select * from table
where column1 = 'X'
and column2 = 'Y'
and columnX = 'Z'
The request also includes SQL partial matches as follows (SQL equivalent and REST API equivalent below too):
All records where table's column 'contains', SQL is (column like '%value%'), REST API --> QueryParm=~value
All records where table's column 'starts with', SQL is (column like 'value%'), REST API --> QueryParm=^value
All records where table's column 'ends with', SQL is (column like '%value'), REST API --> QueryParm=$value
All records where table's column does 'not contain', SQL is (column not like '%value%), REST API --> QueryParm=!value
The request also includes support of the IN operator
All records where table's column contains values 'A' or 'B' or 'C' (column in ('A', 'B', 'C') --> QueryParm=[A,B,C]
The request also includes support of the ORDER BY (SORT) operator
Sort records by table's columnA in descending order, SQL (order by columnA desc), REST API --> _sort=QueryParm desc
To summarise the GET resource and parameters would look like the following - I've covered every function and SQL operator that is required (like, not like, =, IN, ORDER BY):
/api/1/{schema}/{table}?Column1=X
/api/1/{schema}/{table}?Column1=X&Column2=Y
/api/1/{schema}/{table}?Column1=X&Column2=Y&_sort=Column3 desc
/api/1/{schema}/{table}?Column1=~X
/api/1/{schema}/{table}?Column1=^X
/api/1/{schema}/{table}?Column1=$X
/api/1/{schema}/{table}?Column1=!X
/api/1/{schema}/{table}?Column1=[X,Y,Z]
There is no limit to the number of table columns I can specify in the GET request, so if a table has 50 attributes, then strictly speaking I should be able to specify 50 columns as QueryParameters in the GET request.
Before I realised you couldn't parameterize a REST API's Query parameter names, I envisioned each of my tests have 3 test steps.
Step1 - GET (against a table - e.g. country) using a certain filter (currencycode=GBP)
Step2 - Properties - grab the filter value from Step1's resultset
Step3 - JDBCStep - select * from country where country.currency = '${Properties#currencycode}'
Essentially I was going to execute the GET request and follow up with a JDBC step - proving the GET request worked correctly by verifying the same data was retrieved in the GET request as what was returned in the JDBC step.
The purpose of my testing is as follows:
1. Prove the GET request works correctly (retrieves the info I expect),
2. Prove the different operator functions (contains, not contains, starts with, ends with, sort etc.) that equate to the SQL operators (like, not like, order by, IN etc.) work correctly
3. Prove the data is returned correctly (in json response) - charset is a ISO-8859-1 (so I'm inserting a record with the whole 8859 charset to ensure it displays correctly in the response
4. Prove the data is NOT truncated in the json response (I am going to insert a record maxing out the varchar columns that are included in the json response and verify the values are displayed correctly
Have I been clear? I hope so!
