GQL (Glide Query Language) Part 4: The Retriever

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;

}