GQL (Glide Query Language) Part 7: GQL Pad

Last time, we saw how the processor handles web service requests and responses, executing GQL statements and returning encoded result sets. This time, let's build a simple app where we can edit a GQL statement, run it, and see the results in various formats; we'll call it GQL Pad. This will showcase everything we've done so far and interactively demonstrate the capabilities of GQL.

REQUIREMENTS

Here are some requirements for GQL Pad:

GQL Pad is a web application that runs on the ServiceNow Helsinki release.
User must have the "itil" role.
GQL Pad provides a user interface (UI) with the following features:
1. User can enter and edit a GQL statement in a text editor.
2. User can select the desired output format among CSV, HTML, JSAN, JSON.
3. User can execute the GQL statement in the editor.
4. User can see the results of the execution on the same web page (rather than downloading).
5. User can see how long it took for execution.
GQL Pad provides GQL syntax checker in the editor. [future enhancement]
GQL Pad provides GQL syntax highlighting in the editor. [future enhancement]
GQL Pad provides Database Schema Browser showing a list of tables and columns that can be entered into the editor. [future enhancement]

UI PAGE CONFIGURATION

To get started, let's create a new UI Page in the "gql" application scope, as shown in the screenshot below:

Endpoint is what we need to use in the URL to open this UI Page; we'll use it when we create an application module for this. Be sure to check the Direct checkbox. This allows us to bypass Jelly, since we'll be using AngularJS instead, and use simpler HTML of our own without any ServiceNow additions (you can later view the page source and compare with other out-of-the-box pages).

HTML CONTENT

The HTML content looks like

body { margin-bottom:20pt; } label { margin-right: 9px; } table { font:9pt Arial; border-collapse:collapse; } th,td { padding:3px; border:1px solid #aaa; empty-cells:show; vertical-align:top; } th { font-weight:bold; text-align:left; background-color:#eee; } tr:nth-child(even) { background-color:#fafafa; } tr:hover { background-color:lightyellow; }

GQL (Glide Query Language) Pad

Output format:

Run

<https://ajax.googleapis.com/ajax/libs/angularjs/1.5.6/angular.min.js>">

The HTML adds various AngularJS attributes that start with "ng-"; we will use them later in script.

Line 13 adds the AngularJS directive "ng-app" and is the start of the GQL Pad app.

Line 15 is the start of the ng-conroller "EditorCtrl", which we'll see more of in the script.

Line 16 adds a text editor using the "" tag. Line 19 adds a <select> list of desired output formats (the options will be added by the script later). Line 21 is the Run button (access key of "r" allows pressing Alt-R on Windows keyboard to invoke the click button action). Line 22 is a placeholder for displaying execution duration. Line 24 is a placeholder for displaying result. Line 28 adds the <script> tag for the AngularJS source, which we're fetching from a CDN (content delivery network). <h3>CLIENT SCRIPT</h3> The Client Script looks like /** <ul> <li>GQL (Glide Query Language) Pad</li> </ul> */ var app = angular.module('gqlpad', []); app.controller('EditorCtrl', ['$scope', '$http', '$sce', function($scope, $http, $sce) { // set initial value $scope.gql = 'SELECT -- sample GQL statement\r number [Number]\r , short_description [Title]\r , dv_state [State]\r , caller_id.email [Caller Email]\r , caller_id.manager.email [Manager Email]\r , dv_cmdb_ci [CI]\r , cmdb_ci\rFROM incident\rWHERE active=truepriority<2EQORDERBYpriorityORDERBYDESCnumber\rLIMIT 5,10'; $scope.formats = [ {name:'CSV' }, {name:'HTML'}, {name:'JSAN'}, {name:'JSON'}, ]; $scope.format = $scope.formats[1]; // default to HTML $scope.run = function() { var timer = Date.now(); $scope.timer = ''; $scope.result = ''; var params = { gql:$scope.gql, format:$scope.format.name, now:Date.now() }; // add now() to prevent caching $http.get('/x_64935_gql_proc.do', { params: params }).then(function(response) { var data = response.data; $scope.result = $sce.trustAsHtml(typeof data == 'object' ? JSON.stringify(data) : data); // stringify to show JSON on page $scope.timer = 'took ' + ((Date.now() - timer) / 1e3) + ' seconds'; }); } }]); This is a simple AngularJS script that works with the above HTML to do the following: Line 5 defines the GQL Pad app from HTML Line 13. Line 7 is the beginning of the editor controller, which was declared on HTML Line 15. Line 12 lists the available formats for the output; these will populate the <select> tag on HTML line 19. Line 20 is executed when the Run button is clicked. It clears the output fields and sends a GET command to the processor we saw in <a href="https://community.servicenow.com/community?id=community%5Fblog&sys%5Fid=ce2ee66ddbd0dbc01dcaf3231f961990" target="_blank" rel="noopener"> Part 6</a>, passing on the parameters gql and format in the query string. Line 27 is the start of the callback function that's executed when a response is returned from the processor. It simply shows the result in the result <div> on HTML line 24; the result is converted to a string to prevent it from opening up in Excel (in case of CSV) or in another window (in case of JSON) to conform to Requirement c.4. It also shows the execution duration in the defined on HTML line 22. <h3>APPLICATION AND MODULE MENUS</h3> To cap this off, let's add application and module menus. Create a new application menu with the settings in the screenshot below: <img src="https://community.servicenow.com/community/image/serverpage/image-id/181539iA9D0646368D2BC1C/image-size/large?v=v2&px=999" alt="image"> We set the Roles to "itil" as specified in Requirement b and set Category to "Custom Applications". After saving the application menu configuration page, let's add a new module using the New button in the Modules related list. In the Module configuration page, set the values as in the screenshot below: <img src="https://community.servicenow.com/community/image/serverpage/image-id/181535i8DAC386A607C2D14/image-size/large?v=v2&px=999" alt="image"> We set Roles to "itil", again as per Requirement b. For Link type, set it to "URL (from Arguments)" and set Arguments to "x_64935_gql_pad.do", which is the Endpoint from the GQL Pad UI Page configuration setting. After refreshing the Navigator (or the entire page), let's make sure the new "GQL" application and "GQL Pad" module menus show up in the Navigator. <h3>GQL PAD IN ACTION</h3> Here's a screenshot of GQL Pad in action. When you navigate to GQL > GQL Pad, the GQL Pad UI Page opens up with the default GQL statement in the editor. When you click on the Run button, the result is displayed below the Run button. The screenshot below is showing Incident demo data; your results may look different. <img src="https://community.servicenow.com/community/image/serverpage/image-id/181545i5E1BE0B33D4594DC/image-size/large?v=v2&px=999" alt="image"> You can change Output format to "CSV" and click Run, which gives the below output: <img src="https://community.servicenow.com/community/image/serverpage/image-id/181543iE674523D246DDDF7/image-size/large?v=v2&px=999" alt="image"> You can change Output format to "JSAN" and click Run, which gives the below output: <img src="https://community.servicenow.com/community/image/serverpage/image-id/181547i9258D65EE1C3319D/image-size/large?v=v2&px=999" alt="image"> You can change Output format to "JSON" and click Run, which gives the below output: <img src="https://community.servicenow.com/community/image/serverpage/image-id/181549i522FF1416A86D67B/image-size/large?v=v2&px=999" alt="image"> You can use GQL Pad to try out various GQL statements from other tables and inspect data, which may be useful for some debugging scenarios. You can also use it to test encoded query strings. The beauty is it's very interactive and fast. This wraps up our work to date. <a href="https://community.servicenow.com/community?id=community%5Fblog&sys%5Fid=765de629dbd0dbc01dcaf3231f961973" target="_blank" rel="noopener">Next time</a>, I'll show you how to get the app from GitHub and discuss limitations and potential enhancements for the future. Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse. <table><thead> <tr> <th>John Chun, PhD PMP [<img src="https://community.servicenow.com/community?id=community%5Fuser%5Fprofile&user=53101665db581fc09c9ffb651f9619f6" alt="image"></th> </tr> </thead><tbody> </tbody></table>