January 3rd, 2020 by inflectra
One of the maxims I always tell developers is that regardless of what you build, customers will never be satisfied with the reports you offer or the integration that you provide. In fact, the two most underestimated tasks in software development are data feeds and reporting. So one of the nice features in our Spira platform is the ability to do custom graphing, so that you are not limited to just the graphs that ship with the system. This article is the second in a series that explains how to use these powerful custom graphing features, which continues on from our first article that explains the basics. In this article, we'll be diving deeper into some of the intricacies of the Entity SQL language itself.
The language that we use for creating graphs (and reports) in Spira is called "Entity SQL" (abbreviated to ESQL) and is based on the standard database Structured Query Language (SQL) but modified by Microsoft to work against a conceptual object oriented data structure rather than a traditional relational database. According to the Microsoft Entity SQL website:
Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL.
Similar to database SQL, ESQL supports query that consists of the following parts:
select properties or object from entity collection as alias join other entity collections on relationship where conditions group by properties order by properties
When using ESQL with Spira's reporting system, the entity collections you can use are the ones generated from the 'Add New Query' dropdown discussed in the previous article. For example, you have:
The R_xxx prefix is used to distinguish the entities available for reporting from the core entities used by Spira internally for its data access. You will only ever be able query the R_ prefixed entities from within the Spira reporting system.
A simple query used to retrieve all of the requirements in project 1 sorted by hierarchical order then ID would be:
select value RQ from SpiraTestEntities.R_Requirements as RQ where RQ.PROJECT_ID = 1 order by RQ.INDENT_LEVEL, RQ.REQUIREMENT_ID
A more complex query that selects specific requirement properties (vs. the entire object), joins to other table (e.g. to get test case object properties as well) would be:
select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, TC.TEST_CASE_ID, TC.NAME as TEST_CASE_NAME from SpiraTestEntities.R_Requirements as RQ join SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID join SpiraTestEntities.R_TestCases as TC on RT.TEST_CASE_ID = TC.TEST_CASE_ID where RQ.PROJECT_ID = 1 order by RQ.NAME, TC.NAME
Finally, you can add on an aggregation function and group by to group by one property and aggregate the other properties against this. For example to get a count of the test cases associated with each requirement, instead of the test case names would be:
select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, COUNT(TC.TEST_CASE_ID) as TEST_CASE_COUNT from SpiraTestEntities.R_Requirements as RQ join SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID join SpiraTestEntities.R_TestCases as TC on RT.TEST_CASE_ID = TC.TEST_CASE_ID where RQ.PROJECT_ID = 1 group by RQ.REQUIREMENT_ID, RQ.NAME order by TEST_CASE_COUNT desc, RQ.REQUIREMENT_ID
In this last case, we're sorting the list of requirements by the count of associated test cases (in descending order).
So now that we have seen some example queries, let's examine each of the parts of the query in turn:
The select clause of an ESQL query can consist of either:
So for example we could have:
select value RQ
that selects all of the properties in the requirements table (i.e. all the columns).
Alternatively you could select specific properties (columns) from one or more object:
select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, TC.TEST_CASE_ID, TC.NAME as TEST_CASE_NAME
In this case, we omit the value prefix since it's not evaluating all of the properties of an object. Since two of the properties have the same name ("NAME") we are using the as operator to give the property returned a unique name. This is important. If you try and return back two properties with the same name, Spira will give the following error message:
You get this error message because the Entity framework will try and create a name like (NAME #1) that is not allowed by the Spira reporting system. So make sure you used actual named aliases when the same property name is used more than once.
Finally you can use the following aggregations in the SELECT clause to aggregate data from properties that are not being grouped (see later for information on the group by clause):
A full list of Entity SQL aggregate functions can be found on the Microsoft ESQL reference website.
For example, we can count how many times one property appears relative to another column:
select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, COUNT(TC.TEST_CASE_ID) as TEST_CASE_COUNT
Note that in this case we recommend you always specify an alias for the result of the aggregation function using the as operator. If you forget, you'll get the same error message as before:
The from clause in ESQL is relatively simple, it contains the primary object collection being queried and an alias that will be used to reference its properties in the other parts of the query:
from SpiraTestEntities.R_Requirements as RQ
If you are only going to need to work with the properties from a single object collection then you don't need to have any join clauses in your query. However if you are going to need data from multiple object collections, then you will need to use the join clause to add in those other collections. A simple join clause looks like:
join SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID
where you add the name of the entity collection being joined, the alias to refer to it with, and the comparison operator (in this case an equality) used to make the join.
Entity SQL supports the following types of join:
The where clause in ESQL lets you filter the results by one or more condition. In addition to the standard ESQL syntax, you can use the special Spira tokens to filter by dynamic data in the system:
The where clause consists of a set of conditions that are joined by a boolean operator:
Generally and operators have higher precedence than or operators, so you will need to use parenthesis when you want to have or operators that are higher precedence than an and.
where (RQ.PROJECT_ID = 1 or RQ.PROJECT_ID = 2) and RQ.IS_DELETED = 0
means that you will retrieve any un-deleted requirement that is in project 1 or project 2, whereas this would mean something completely different:
where RQ.PROJECT_ID = 1 or RQ.PROJECT_ID = 2 and RQ.IS_DELETED = 0
this would retrieve all (including deleted) requirements in project 1, and any un-deleted ones from project 2.
The type of operator you can use in the various conditions include:
For example you might have a compound conditional clause such as:
where RQ.PROJECT_ID >= 1 and RQ.PROJECT_ID <= 4 and RQ.IS_DELETED = 0 and (RQ.TASK_ACTUAL_EFFORT + RQ.TASK_REMAINING_EFFORT) > 0
In the discussion of the select clause we mentioned that you can use aggregation functions such as count, sum, min, max, etc. If you use these in the select clause, then any object properties that are not being aggregated need to be included in the group by clause:
group by RQ.REQUIREMENT_ID, RQ.NAME
If you don't have any aggregation functions, you can still use a group by clause to simply group similar rows, but generally speaking you omit the group by clause if there are no aggregation functions in the select list.
Finally, you typically want to sort the data by one or more of the object properties, this is done by having an order by clause at the end of the query:
order by TEST_CASE_COUNT desc, RQ.REQUIREMENT_ID asc
The syntax of the order by clause is:
If you sort by a property (e.g. requirement name) that could be held by multiple rows, it is recommended to always add a final sort clause by a guaranteed unique ID such as the primary key (e.g. REQUIREMENT_ID) since that will ensure the results are consistent each time. This is known as 'stable sorting'
Now that we have covered the basics of writing an Entity SQL (ESQL) query, in the next article in this series we'll discuss some of the differences and limitations between ESQL and traditional database SQL.