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.
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!
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.
SELECT iconType(JQL.ISSUE) || ' ' || link(JQL.ISSUE, '_blank') as "Defect", ed.* 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) join ZEPHYR.EXECUTION_DEFECT ed ON ed.EXECUTION_ID = e.ID join JQL ON JQL.ISSUEID = ed.DEFECT_ID WHERE p.key = 'MSP' |
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.
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) LEFT JOIN ZEPHYR.EXECUTION_CF ecf ON ecf.EXECUTION_ID = e.ID WHERE p.key = 'MSP' |
Select the tests reported by me.
SELECT iconType(JQL.ISSUE) || ' ' || link(JQL.ISSUE, '_blank') as "Test", summary(JQL.ISSUE) as "Summary" FROM JQL JOIN ZEPHYR.TEST_STEP ts ON ts.ISSUE_ID = JQL.ISSUEID WHERE QUERY = 'type = ' || ZEPHYR.TESTISSUETYPE()|| ' and reporter = currentUSer()' |
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.
SELECT iconType(JQL.ISSUE) || ' ' || link(JQL.ISSUE, '_blank') as "Test", ts.* FROM JQL JOIN ZEPHYR.TEST_STEP ts ON ts.ISSUE_ID = JQL.ISSUEID WHERE QUERY = 'type = ' || ZEPHYR.TESTISSUETYPE()|| ' and reporter = currentUSer()' |
SELECT sr.* 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.STEP_RESULT sr ON sr.EXECUTION_ID = e.ID WHERE p.key = 'MSP' |
SELECT * 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.STEP_RESULT sr ON sr.EXECUTION_ID = e.ID JOIN ZEPHYR.STEP_DEFECT sd on sd.STEP_RESULT_ID = sr.ID WHERE p.key = 'MSP' |
SELECT e.id as "EXECUTION", '<a href="' || a.DOWNLOAD_URL || '">' || a.FILE_NAME || '</a>' AS "ATTACHMENT" 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.ATTACHMENT a ON a.ENTITY_ID = e.ID WHERE p.key = 'MSP' |
Note: The hyperlink to the file is built by concatenating texts in SQL
SELECT * FROM ZEPHYR.CHANGE_GROUP g JOIN ZEPHYR.CHANGE_ITEM i ON i.CHANGE_GROUP_ID = g.ID WHERE g.FROM_DATE = '2020-01-09' AND g.TO_DATE ='2020-01-10' |
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.
There are some permissions and groups created by Zephyr:
SELECT * FROM ADMIN.GLOBALPERMISSIONS WHERE KEY LIKE '%ZEPHYR%' |
KEY | NAME | DESCRIPTION |
---|---|---|
ZEPHYR_TEST_MANAGEMENT_PERMISSION | Zephyr Test Management | Ability to access/view Zephyr Test Management. |
SELECT * FROM ADMIN.PERMISSIONS WHERE KEY LIKE '%ZEPHYR%' |
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. |
SELECT * FROM ADMIN.GROUPS WHERE NAME LIKE '%zephyr%' |
NAME |
---|
zephyr-testers |
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:
SELECT * FROM USERGROUPS WHERE GROUPNAME = 'zephyr-testers' |
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.
SELECT * FROM USERGROUPS JOIN <ANY ZEPHYR TABLE HERE> WHERE GROUPNAME = 'zephyr-testers' AND <OTHER ZEPHYR CONDITONS HERE> |
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.