GlideRecord Glimmerings: Windows...

[SlightlyLoony]

Have you ever needed to write a script that did some work on a lot of records in a table? In such situations it can be very useful to work on one "chunk" of records at a time. There are many reasons why you might want to do this — but usually it's either because you want to produce intermediate results as your script progresses, or because doing the work a bit at a time will reduce the impact on other users. But how can you go about doing something like this, since GlideRecord will always return the entire set of records you've queried for?

It turns out that windows are involved:

And not the Microsoft kind, either!. The "windows" we're talking about here are windows into the records that GlideRecord returns after a query. There's a pair of methods on GlideRecord that let you pick the window you want, as shown in this little script:

var gr = new GlideRecord('cmdb_ci');
gr.addQuery('name', '>=', 'm');
gr.chooseWindow(10, 20);
gr.query();
while (gr.next())
   gs.log(gr.name);

gr.initialize();
gr.addQuery('name', '<', 'm');
gr.setLimit(10);
gr.query();
while (gr.next())
   gs.log(gr.name);

The chooseWindow(first,last) method lets you set the first and last row number that you want to retrieve. The rows for any given query result are numbered 0..(n-1), where there are n rows. The first parameter is the row number of the first result you'll get. The second parameter is the number of the row after the last row to be returned. In the example above, the parameters (10, 20) will cause 10 rows to be returned: rows 10..19, inclusive.

The setLimit(n) is exactly the same thing as chooseWindow(0,n). By setting a limit, you're telling GlideRecord to return the first n records, with row numbers 0..(n-1).

Here's a slightly more complex example, showing how you might make use of chunking in a real problem:

var gr = new GlideRecord('cmdb_ci_computer');
gr.addQuery('install_status', 1);
var nextChunkOfCIs = chunker(gr, 100);
var ram_total = 0;
while (nextChunkOfCIs()) {
   var ram_chunk = 0;
   while (gr.next()) {
      ram_chunk += gr.ram;
   }
   ram_total += ram_chunk;
   gs.log('Chunk RAM: ' + ram_chunk + ' mb');
}
gs.log('Total RAM: ' + ram_total + ' mb');


function chunker(gr, size) {
   var window_start = 0;
   var window_size = size;
   var chunky_gr = gr;
   return nextChunk;

   function nextChunk() {
      gr.chooseWindow(window_start, window_start + window_size);
      gr.query();
      var has_next = (window_start < gr.getRowCount());
      window_start += window_size;
      return has_next;
   }
}

The function chunker is a factory function, returning a function that can be called to get the next chunk of records. It uses closures to implement iteration through the chunks. The code example uses this to process chunks of 100 computers, adding up the RAM for each chunk (and in the end, the total for all computers)...