Last time, we designed the parser that decomposes a GQL statement into the four main parts:
SELECT column_list
FROM table
WHERE encoded_query_string
LIMIT [offset,] row_count
We saw how the SELECT column_list would be parsed and which Scoped GlideRecord methods we might use for the FROM, WHERE, and LIMIT clauses. This time, let's work on the retriever that runs the query and retrieves the resultset.
Let's take a look at how we may put this together:
// run query & retrieve data
var records = []; // array of rows
var gr = new GlideRecord(table);
if (encodedQuery) gr.addEncodedQuery(encodedQuery);
if (offset) gr.chooseWindow(offset, offset + limit);
else if (limit) gr.setLimit(limit);
gr.query();
while (gr.next()) records.push(getRow());
This gets quite simple with the help of Scoped GlideRecord; it iterates over all glide records and returns an array of record objects. The getRow() function returns the columns, either in raw or display values (if the field name is prefixed with "dv_"):
function getRow() {
var row = {};
for (var i = 0; i < columns.length; i++) {
var field = columns[i].field, isDV = /(^|\.)dv_/.test(field);
var value = isDV ? gr.getDisplayValue(field.replace('dv_', '')) || '' : gr.getElement(field).toString();
row[columns[i].label] = value;
}
return row;
}
The gr.getDisplayValue() and gr.getElement() methods handle dot-walking for us. gr.getElement() returns GlideElement so we need to convert it to string to get the raw value (if not converted, the JSON encoder will treat it as an object, which is not what we want). The columns array is obtained from the column_list and each array element is an object representing a column with field and label (column heading) properties:
// parse column_list
columns = columns.split(/\s*,\s*/); // split column_list column_1, column_2 [column_2_heading], column_3
for (var i = 0; i < columns.length; i++) { // parse column heading
matches = /([.\w]+)\s*(?:\[(.+)\])?/.exec(columns[i]);
columns[i] = { field: matches[1], label: matches[2] || matches[1] }; // if no label, use field name
}
Putting everything together, we have a Script Include with the GQL class:
/**
* GQL (Glide Query Language) class
*/
var GQL = Class.create();
GQL.prototype = {
initialize: function() { },
type: 'GQL'
};
/**
* Takes a GQL statement and returns the resultset in an array of records
*
* @param {string} gql - GQL statement
* @return {object} resultset in an array of records
*/
GQL.prototype.query = function(gql) {
// parse gql
gql = gql.replace(/\s*--.*/g, ''); // remove comments
var rxParser = /SELECT\s*([^]+?)\s*FROM\s*(.+?)\s*(?:WHERE\s*([^]+?(?=\s*LIMIT)|[^]+?)\s*)?(?:LIMIT\s*([,\d\s]+?)\s*)?$/;
var matches = rxParser.exec(gql);
var columns = matches[1] || ''; // SELECT column_list
var table = matches[2] || ''; // FROM table name
var encodedQuery = matches[3] || ''; // WHERE encoded_query_string
var limit = matches[4] || ''; // LIMIT [offset,] row_count
var offset = 0;
if (limit) { // parse offset, row_count
limit = limit.split(',');
if (limit.length > 1) offset = parseInt(limit[0], 10) || 0;
limit = parseInt(limit[limit.length > 1 ? 1 : 0], 10) || 0;
}
// parse column_list
columns = columns.split(/\s*,\s*/); // split column_list column_1, column_2 [column_2_heading], column_3
for (var i = 0; i < columns.length; i++) { // parse column heading
matches = /([.\w]+)\s*(?:\[(.+)\])?/.exec(columns[i]);
columns[i] = { field: matches[1], label: matches[2] || matches[1] }; // if no label, use field name
}
// run query & retrieve data
var records = []; // array of rows
var gr = new GlideRecord(table);
if (encodedQuery) gr.addEncodedQuery(encodedQuery);
if (offset) gr.chooseWindow(offset, offset + limit);
else if (limit) gr.setLimit(limit);
gr.query();
while (gr.next()) records.push(getRow());
return { records: records };
function getRow() {
var row = {};
for (var i = 0; i < columns.length; i++) {
var field = columns[i].field, isDV = /(^|\.)dv_/.test(field);
var value = isDV ? gr.getDisplayValue(field.replace('dv_', '')) || '' : gr.getElement(field).toString();
row[columns[i].label] = value;
}
return row;
}
};
This doesn't yet have any GQL syntax checking or run-time error handling, but will give us a good starting point. By having this in a Script Include, we can use it not only for web services, but also in any server-side scripts.
Next time, we'll take a look at the encoder.
Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse.
John Chun, PhD PMP 
5 Comments
