Zephyr Examples
Cycle
Get the cycles of a Jira project and the cycle summaries:
SELECT
s.*,
c.*
FROM
PROJECTS p
JOIN
ZEPHYR.CYCLE c on c.PROJECT_ID = p.ID
JOIN
ZEPHYR.CYCLE_SUMMARY s on s.CYCLE_ID = c.ID
WHERE
p.key = 'MSP'
Tip: click on the HTML_SUMMARY bar to open the cycle summary in a Zephyr view.
Execution
Note: Execution is named Schedule in the Zephyr' native database model
SELECT
es.*,
e.*
FROM
PROJECTS p
JOIN
ZEPHYR.CYCLE c ON c.PROJECT_ID = p.ID
JOIN
ZEPHYR.EXECUTION e ON e.CYCLE_ID = c.ID
JOIN
ZEPHYR.EXECUTION_SUMMARY es ON es.EXECUTION_ID = e.ID
WHERE
p.key = 'MSP'
Note: The ZEPHYR.EXECTION.ZQL column allows searching executions by providing any ZQL query!
Â
Folder
Select the executions of a cycle and get folder information
SELECT
*
FROM
PROJECTS p
JOIN
ZEPHYR.CYCLE c ON c.PROJECT_ID = p.ID
JOIN
ZEPHYR.EXECUTION e ON e.CYCLE_ID = c.ID
LEFT JOIN
ZEPHYR.CYCLE_FOLDER f ON f.id = nvl(e.folder_id,-1)
WHERE
p.key = 'MSP'
Note: In the Zephyr’s native data model database the relation between Cycles and Folders is M:N.
Execution Defect
Note: Defects in Zephyr are regular issues in Jira, so you can use the Jira data model (JQL table) to get extra information about them.
Â
Execution Custom Field
Test
Select the tests reported by me.
Note 1: The ZEPHYR.TESTSSUETYPE() returns the ID of the Jira Issue configured as Test by Zephyr.
Note 2: Tests in Zephyr are regular issues in Jira, so you can use the Jira data model (JQL table) to get extra information about zephyr tests.
Test Step
Step Result
Â
Step Defect
Â
Attachment
Note: The hyperlink to the file is built by concatenating texts in SQL
Â
Change History
Note 1: to search by date in the groups, you the FROM_DATE and TO_DATE columns must be used. The type of those columns is VARCHAR and the date pattern is yyyy-MM-dd
Note 2: to search for the changes of a specific date (example above) FROM_DATE must point to the target date and TO_DATE to the next day in order to search from the beginning of FROM_DATE (00:00:00.000) to the beginning of next day to search between the 00:00:00.000 and the 23:59:59.999 hours.
Â
Permissions & Groups
There are some permissions and groups created by Zephyr:
Global permissions
KEY | NAME | DESCRIPTION |
---|---|---|
ZEPHYR_TEST_MANAGEMENT_PERMISSION | Zephyr Test Management | Ability to access/view Zephyr Test Management. |
Â
Project & Issue Permissions
KEY | CATEGORY | NAME | DESCRIPTION |
---|---|---|---|
ZEPHYR_BROWSE_ATOM | PROJECTS | Zephyr - Browse Atom | Ability to Browse Atom Page. Browse Atom provides user with permission to browse atom and tasks within it. |
ZEPHYR_BROWSE_CYCLE | PROJECTS | Zephyr - Browse Test Cycle | Ability to Browse Test Cycle. Browse Cycle provides user with permissions to browse cycle and executions within it. |
ZEPHYR_CREATE_CYCLE | PROJECTS | Zephyr - Create Test Cycle | Ability to create Test Cycle. |
ZEPHYR_CREATE_EXECUTION | PROJECTS | Zephyr - Create Test Execution | Ability to create Test Execution. |
ZEPHYR_DELETE_CYCLE | PROJECTS | Zephyr - Delete Test Cycle | Ability to delete Test Cycle. |
ZEPHYR_DELETE_EXECUTION | PROJECTS | Zephyr - Delete Test Execution | Ability to delete Test Execution assuming the user has Browse Cycle permission. |
ZEPHYR_EDIT_CYCLE | PROJECTS | Zephyr - Edit Test Cycle | Ability to edit Test Cycle. |
ZEPHYR_EDIT_EXECUTION | PROJECTS | Zephyr - Edit Test Execution | Ability to edit Test Execution. |
Â
Â
Groups
NAME |
---|
zephyr-testers |
Checking
For example, if you want only the users belonging to the zephyr-testers group can see the data you have to check if the current user belongs to it:
Â
GROUPNAME |
---|
zephyr-testers |
Note how the USERGROUPS table returns the same GROUPNAME if the user belongs to the group and it returns no records otherwise.
This allows easily and efficiently dropping records if the current user does not belong to the group.
Â
If the current user does not belong to the group, then the USERGROUPS table will return no records and the rest of the query will be ignored. Otherwise, the rest of the Zephyr tables will be invoked.
Similarly, you can check global, project and issue permissions.