Design For Automated testing of a Grid template UI usnig ReadyAPI

[krispokkuluri]

##URGENT### Hi Guys,

My team is looking to automate our application Grid Based Application. I am very new to automation and the requirement is to build a test-suite in ReadyAPI . The Requirement is to validate the Stored Procs/ Various services that run behind to generate data and validate it against a query that gets the same data using query . As a QA i have very minimal knowledge of SQL and to be able to do something requires a humongous effort .

Question : Can someone please help suggest any framework design in mind for such validation or any other ideas for example if we could take the data from the website and validate it against the query would it make sense . Please suggest and share your expertise .

[richie]

Hey @krispokkuluri,

If I understand what you're asking for, this doesnt sound that complicated, however, the problem is that you're going to need good SQL to be able to pull this off.

What I mean by this is, if you need to verify the storedprocs are doing what is required, then you'll need to be able to turn those stored proc requirements into SQL - thinking about the typical stored procs I see, you'd need to at least be able to put together inner/outer joins, sub-queries and perhaps even use some SQL functions like CAST, CASE & COALESCE to match the requirement.

Initially I think I'd create the JDBC DB connection in the JDCB Connection profile 'Preferences'

In my test case I'd create a copy of the stored proc in a JDBC test step

I'd have a groovy step to write out the results to a Properties step

I'd add a Properties test step 

I'd have a JDBC test step containing the SQL I'd put together to match the requirement

I'd have a groovy step to do grab the results of the SQL, write it to the Properties step and then compare the storedproc results against the SQL step's results

So my testcase hierarchy would be something like the following:

JDBC TestStep (storedProc)

Groovy TestStep (grab storedproc, write results to Properties step)

Properties

JDBC TestStep (SQL)

Groovy TestStep (grab SQL, write results to Properties step and compare storedproc results against SQL results)

You wouldn't be able to use the website's front end for a couple of reasons - firstly ReadyAPI! doesnt capture front end content - you'd need something like Selenium to do this - you can use groovy and import declare Selenium, but that's just adding more complication and I think this is gonna be complicated enough for you) - secondly - yes the front end is displaying data sourced from the database - however, you do not know if the data displayed in a specific field in the HTML table on the page is actually sourced from the correct table.attribute in your database.  This is one of the reasons when testing front ends, you should actually alter the underlying table's data so that all data displayed on the page is unique - to ensure that page.field1 is being populated by table.field1 correctly - rather than page.field1 being incorrectly populated by table.field2.  Hence the reason you should disregard the front end and just focus on the underlying database.

It sounds like you're quite new to ReadyAPI! and if you're not well versed in SQL, I think this is going to be rather a tall order for you.  Are you working with anyone who knows ReadyAPI! and has good SQL skills?  Even if your SQL is very good doesn't necessarily mean your T-SQL or PL/pgSQL or PL/SQL  (T-SQL, PL/pgSQL, PL/SQL are used by SQLServer, PostgreSQL and Oracle to create their storedprocs) is good.

A further complication is how your requirements describing the storedprocs are defined.  Will you have a textual description defining the requirement of what the storedprocs are supposed to be doing or are you expected to analyse the storedprocs yourself to determine what they are doing?  If the latter, then you'll definitely need to have excellent T-SQL, Pl/pgSQL, PL/SQL, etc.)

Sorry I can't be a bit more encouraging, but I think what you're asking is a tall order unless you're SQL is very, very good along with very good ReadyAPI! skills.

Cheers,

Rich  

[krispokkuluri]

Hi @richie ,

Thanks for being so articulate. I have people who have implemented the solution ,although i need to lay down the basic groundwork inorder to reach them .The team has allowed me to involve in upgrading my skills and also are open to other solutions such as getting the data from the UI in excel format .

Another solution is to validate JSON response with the JDBC Step . Can you please help me with this ?

I have my JSON Response something like this :

(Can't provide actual data for security purposes but this is the response and they are severeal of these based on the request)

{
"$id" : "1",
"$type" : "XYZ",
"FeatureName" : "XYZ",
"FeatureQuantity" : 1,
"ToolFamily" : "XYZ",
"IsPrimary" : true,
"SupplierName" : "XYZ",
"ProductCode" : "XYZ",
"Description" : "XYZ",
"ToolIsActive" : true,
"FeatureIsActive" : true,
"IsRemixable" : true,
"IsAnnualized" : false,
"ListPrice" : 10500.00000000,
"ModifiedBy" : null,
"ModifiedDate" : null,
"CatalogId" : 21774,
"Features" : "XYZ",
"ProductType" : "A-la-carte"
},

And with the help of the developer i have got the query that gives the response to certain fields from this and there are multiple records and vary with data required . Can you please provide any familiar way to validate such requests and the response can have n number of entries and have only numbers for nodes but i want to extract only the data that i need and have to validate it with the jdbc response . Let's say i have both can you please provide a solution to validate the same .

Please let me know if there is anyway i could connect i terribly need to get this done by today .

[richie]

Hey @krispokkuluri,

Where's this json response come from?
Executing a JDBC step returns results in xml format. If youve got this response from an HTTP/ReST step we can convert the response to xml by adding an 'Accept' header with value of 'application/xml' to convert the response to xml instead of json so both HTTP and JDBC responses are same datatype.

You state ".......and the response can have n number of entries and have only numbers for nodes but i want to extract only the data that i need and have to validate it with the jdbc response"

Firstly can you clarify what you mean by "....have only numbers for nodes"? I dont know what you mean by this.

Where you say ".....but i want to extract only the data that i need and have to validate it with the jdbc response". I'm a bit confused. You can add filters to your SQL (in the WHERE clause of your SQL query) to control what is returned by your JDBC query, however, if youre attempting to prove the storedproc works correctly then adding filters to your SQL query to retrieve specific info that doesnt match what the storedproc retrieves exactly wont verify the storedproc is working correctly.....or am i misunderstanding something?

Oh, one more question: does your database support querying via HTTP/ReST as an alternative to querying via JDBC/SQL?

Cheers,

Rich

[krispokkuluri]

Hi @richie 

Where's this json response come from?

I ran a REST API call to the Application API (Grid based Application  ) than the stored proc and received the JSON response . As you can see the response nodes are individual numbers and there is no global node which making it hard to get as a data source and then validate with the JDBC response . My idea is something like this as i have very less time at hand . I would appreciate if there is any script suggestion that could help here . I have attached the two responses below .- Hope this helps to clarify  Also would appreciate if you could provide a world view to the same as in what could be the best practice to validate the Response from this application in general. 

P.S: I need a dynamic solution as the data that i will be validating would only increase in number  can't keep individualy adding data for each node .

This is the JDBC Step:

[richie]

Hey @krispokkuluri,

Ok. Now i am confused.

On your initial post you stated that you had a storedproc which you needed to verify was working correctly and you suggested creating an SQL query to match the storedproc requirements to verify the storedproc worked correctly.

What youve described in this most recent post is entirely different.

You gotta remember i dont know what you do, i dont know what youre working on and on top of that, im really dumb, so you need to explain everything from scratch.

From your most recent post you state:

You ran a REST request, then ran the storedproc and the storedproc returned a json response.

Q1. what is the purpose of the REST request?
Q2. What is the purpose of the storedproc?
Q3. Is the storedproc doing something to the data retrieved in the REST request?
Q4. Are you saying the storedproc returns a json rather than xml payload?
Q5. Can you please attach rather than embed the screenshots? I cant see whats going on with embedded screenshots.

Sorry to be a pain asking all these questions, but unless i completely understand i wont be able to help!

Nice one!

Rich

[krispokkuluri]

Hi @richie 

Apologies for the misunderstanding !!

Context : We are trying to build an API automation test suite for a Grid based Application where in we input certain parameters and recieve data in the form of a grid like this : 

This is the application frontend it works on the basis of stored Procs that are written which are called when a particular functionality is called  and drops the data for the particular parameter . This is what we do validate manually for now and we would like to automate the same hence API Automation felt like a natural step .

Q1. what is the purpose of the REST request?

A) REST Request is used to interact with the application to recieve the responses based on the request and we are looking to validate the same .

Q2. What is the purpose of the storedproc?

A) Purpose of the stored proc is to run when a related api call is called to throw the requested data . 
Q3. Is the storedproc doing something to the data retrieved in the REST request?

A) No It is what is throwing the data to the frontend
Q4. Are you saying the storedproc returns a json rather than xml payload?

A) Stored proc return XML payload only . I can validate using the stored proc or json (We utilize it based on the scenario)
Q5. Can you please attach rather than embed the screenshots? I cant see whats going on with embedded screenshots.

attached

A) The Ask is to validate the data treating them as individual objects by either callind the REST request or the Stored Proc and write a custom query to get similar data and validate the same . This is the proposed approach .

Questions:

1) What i am looking for is your understanding of the design and feasibility of the same . Does it make sense in real world QA to validate in this way if yes then fine if no then please state your opinion .

2) Can you please tell me how i can implement the same in a short way that gives me a quick validation and a more logical way that would be dynamic and covers longer range 

[richie]

Hey @krispokkuluri,

No need to apologise at all.....i just need to ensure i understand.

Firstly, just an FYI. I understand security concerns, but you dont need to scrub out the data values in the payloads unless this is live personally identifiable information. You also dont need to hide the URI (the bit of the URL after the domain/ip address/host) of the REST request. You DO need to hide the domain/ip address/hostname and you DO need to hide any authentication credentials (like username and password and the Authorization header value). From a security perspective, displaying the content of a payload makes no difference as long as there isnt any live personally identifiable info in there cos with the domain and logon credentials the rest of the info is no help to a hacker.
Likewise for your SQL step, you dont need to scrub out the data in the result set, you just need to ensure the JDBC connection string (containing the db name, and logon username and password) are scrubbed out. Data is useless unless the hacker knows where it is to try and access it.

Ok, so final two questions.

Q1. So the REST requests you mention are the calls being made (under the covers) when the user interacts with the front end to retrieve the data to populate the html form....is that correct?

Q2. The storedproc. Is this storedproc the one that fires when REST request is made? What i mean is......the user alters the query parameters on the front end (altering the query filter), the user submits the request via the front end, and the changes the user made on the front end alters the underlying REST request's query parameters and payload. The REST request goes through the DB abstraction layer which causes the storedproc to fire (and the variables of the storedproc are populated by the REST requests query parms and payload values), the storedproc retrieves the data, the jdbc converts the SQL response back into HTTP which is then passed through the abstraction layer back up to the webapp and the page reloads based on the results of the returned http request.

Is that it....is that the way it works? Does this mean that youre trying ro verify the storedproc and REST request populates the web front end correctly? Is that right?

If thats the case, then theres a couple of different ways to handle this. You could go with the basic make the rest call and verify the results using hardcoded assertions on all the fields, based on firing the storedproc, seeing the values come back and then make sure your REST steps specific field assertions' values are identical (Which is the naff way of doing it, although it gets the job done)
Or, the way id do it: id have a test JDBC test step execute the storedproc, id save off those specific field values from the JDBC response into a Properties step (for later comparison). Id have my REST step execute and i'd save off all my json attribute values in a separate Properties step (for comparison against the JDBC step's response values saved in first Properties step) id then have a groovy step which would then compare the JDBC property values in the first Properties step against the associated property values (sourced from the REST step) in the second Properties step.
The advantage of this approach over the first boring approach i mentioned is that there'd be no hardcoding of values in your assertions, so if your input data changed in your jdbc step (which would alter the response content), you wouldnt need to change anything in your assertions, cos its all parameterized.

Testcase hierarchy would look like the following:

Step1: JDBC teststep (response atrributes saved to Properties1 step)
Step2: PropertyTransfer step (transfers values from JDBC step to Properties1 step)
Step3: Properties1 step
Step4: REST step (response atrributes saved to Properties2 step)
Step5: PropertyTransfer2 (transfer values from REST step to Properties2 step)
Step6: Properties2 step
Step7: Groovy step (compares attributes in Properties1 against equivalent attributes in Properties2)

Only slightly complicated bit is that you need to add some groovy in, but its pretty straightforward groovy so the ReadyAPI! experts there will be able to do it without any problems.

You dont really need the explicit PropertyTransfer step in your test, but i tend to add them in anyway, ao people can see whats happeninf jist by looking at the test.

Ok, thats it....was that clear? Thats the way id do it. Or at least thats what my brains telling me id do, but its 12:30 on the night, so thats all i got right now.....

Cheers,

Rich