1 Cycle
2 Execution
3 Folder
4 Execution Defect
5 Execution Custom Field
6 Test
7 Test Step
8 Step Result
9 Step Defect
10 Attachment
11 Change History
12 Permissions & Groups
- 12.1 Global permissions
- 12.2 Project & Issue Permissions
- 12.3 Groups
- 12.4 Checking

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

SELECT 
  iconType(JQL.ISSUE) || '&nbsp;' || 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.

Execution Custom Field

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'

Test

Select the tests reported by me.

SELECT 
 iconType(JQL.ISSUE) || '&nbsp;' || 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.

Test Step

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()'

Step Result

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'

Step Defect

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'

Attachment

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

Change History

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.

Permissions & Groups

There are some permissions and groups created by Zephyr:

Global permissions

SELECT 
    * 
FROM 
  ADMIN.GLOBALPERMISSIONS 
WHERE 
  KEY LIKE '%ZEPHYR%'

KEY	NAME	DESCRIPTION

KEY	NAME	DESCRIPTION
ZEPHYR_TEST_MANAGEMENT_PERMISSION	Zephyr Test Management	Ability to access/view Zephyr Test Management.

Project & Issue Permissions

SELECT 
    * 
FROM 
  ADMIN.PERMISSIONS
WHERE 
  KEY LIKE '%ZEPHYR%'

KEY	CATEGORY	NAME	DESCRIPTION

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

SELECT 
    * 
FROM 
  ADMIN.GROUPS
WHERE
 NAME LIKE '%zephyr%'

NAME

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:

SELECT 
    * 
FROM 
  USERGROUPS 
WHERE
 GROUPNAME = 'zephyr-testers'

GROUPNAME

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.

SQL+JQL Driver for Jira

Zephyr Examples