DevOps Plan REST API queries and result sets

Queries can be stored in the workspace, which exists inside a valid repository and database. It can be accessed by using GET https://localhost:8190/ccmweb/rest/repos/<repo_name>/databases/<database_name>/workspace/folders. The user can see the public and personal workspace folders, which contain other folders, charts, rules, and queries.

[
    {
        "name": "Personal Queries",
        "dbId": "33554447",
        "dbIdParent": "",
        "masterReplicaName": "<local>",
        "workspaceItemType": "WORKSPACE_FOLDER",
        "children": [],
        "isReadable": true,
        "isWritable": true
    },
    {
        "name": "Public Queries",
        "dbId": "33554441",
        "dbIdParent": "",
        "masterReplicaName": "<local>",
        "workspaceItemType": "WORKSPACE_FOLDER",
        "children": [
            {
                "name": "All Defects",
                "dbId": "33554700",
                "workspaceItemType": "WORKSPACE_QUERY"
            },
            {
                "name": "Needs Verification",
                "dbId": "33554691",
                "workspaceItemType": "WORKSPACE_QUERY"
            },
…	
        ],
        "isReadable": true,
        "isWritable": false
    }
]

To execute a query, create a result set using the ID of the desired query that is found in the workspace response. This can be done by using POST https://localhost:8190/ccmweb/rest/repos/<repo_name>/databases/<database_name>/workspace/queryDefs/33554700/resultsets and returns information about the query execution, including a result set ID, the columns, and a record count.

{
    "result_set_id": "d2311c60-0766-462e-8291-bb35af2ea9f9",
    "columns": [
        {
            "dataType": "PD_C_SLONG",
            "name": "dbid",
            "sortOrder": 0,
            "sortType": "NO_SORT"
        },
        {
            "dataType": "PD_C_CHAR",
            "name": "id",
            "sortOrder": 0,
            "sortType": "SORT_ASC"
        },
        {
            "dataType": "PD_C_CHAR",
            "name": "Headline",
            "sortOrder": 0,
            "sortType": "NO_SORT"
        }
    ],
    "recordCount": 45,
    "resultSetIsLimited": 0,
    "sql": "select distinct T1.dbid,T1.id,T2.name,T1.headline from Defect T1,statedef T2 where T1.state = T2.id and (T1.dbid <> 0) order by T1.id ASC",
    "queryLimitHard": 0,
    "queryLimitSoft": 0
}

The same query ID and this result set can be used to retrieve the results of the query execution by using GET https://localhost:8190/ccmweb/rest/repos/9.0.0/databases/SAMPL/workspace/queryDefs/33554700/resultsets/d2311c60-0766-462e-8291-bb35af2ea9f9.

{
    "rows": [
        {
            "values": [
                "33554433",
                "SAMPL00000001",
                "Opened",
                "spelling error in login screen"
            ]
        },
        {
            "values": [
                "33554434",
                "SAMPL00000002",
                "Resolved",
                "sales tax incorrect if item deleted from purchase"
            ]
        },
	…
    ]
}

Queries can also be created by using the REST API. The minimum information required to create a query includes the following:

1. A name.
2. A parent database ID (of a folder where the query will be stored in the workspace).
3. A primary entity definition name (Defect for example).
4. A query field definition that is set to be shown.
5. A root filter node with a Boolean operator.

Sample request:

{
    "name": "Sample Query",
    "dbIdParent": "33554447",
    "primaryEntityDefName": "Defect",
    "queryFieldDefs": [
        {
            "fieldPathName": "Headline",
            "isShown": true
        }
    ],
    "filterNode": {
        "boolOp": "BOOL_OP_AND",
        "fieldFilters": []
    }
}