Updating ScriptDB

Using ScriptDB

I already have a silo class  for scriptDB partitioning – which would be kind of analogous to the parse Class, and caching  to abstract getting data from sheets- like the Google visualization data fetching , so the structure can be more or less the same.

Here’s the whole application – a whole lot simpler than the parse version. But it takes so long to run. I’ll do some benchmarks of all the variations at the end of this, but as usual, I hit the dreaded “execution time exceeded“, so I have to run it a couple of times to get all the rows done.

function loadToScriptDB () {
// get the color map
// duplicate the process as in https://ramblings.mcpher.com/parse-com/updating-parse-com/ for parse.com
  var maxLoad = 0, batchSize = 1000, wcount = 0;
  
// get the sheet data, and the silo to store it in, and the current silo contents
  var data = mcpher.sheetCache("colorTable");
  var silo = mcpher.scriptDbSilo("colorSchemes",colorschemer.publicStuffDb());
  var all = silo.queryArray({},0);
  all.sort(obCompare);
    
  Logger.log (all.length + " loaded from scheme");
  
// find the column headers
  var nr = maxLoad || data.getRowCount()-1 ,nc = data.getColumnCount();
  Logger.log (data.getRowCount()-1 + " data rows loaded from workbook");
  
  var colHeaders = {};
  for ( var i = 0; i < nc; i ++ ){
    colHeaders[mcpher.LCase(data.getValue(1,i+1))] = i+1;
  }
  
  //now traverse the data and write it to the silo - note that row numbering starts at 1
  for (var i = 2  ; i <= nr; i++) {
    var key = data.getValue(i,colHeaders.name).toString();
    var ob = 
      {key: key, 
       scheme : data.getValue(i,colHeaders.scheme).toString(),   
       label : data.getValue(i,colHeaders.label).toString(), 
       code : data.getValue(i,colHeaders.code).toString(), 
       hex : data.getValue(i,colHeaders.hex).toString() }  ;
          
      var a = findInDb (all,ob);
      var dirty = true;
      if (a) {
      // has it changed ?
        dirty = false;
        for (var k in ob) {
          dirty = dirty || ob[k] != a[k];
        }
      }
      // write or update?
      if (dirty) {
        if (silo.batchLength() >= batchSize) { 
          silo.saveBatch();
        }
        silo.save(ob,true);
        wcount++;
      }
    
  }
  if (silo.batchLength()) silo.saveBatch();
  data.quit();
  Logger.log ("written " + wcount + " rows to db");
}
function findInDb (all,ob) {
  return mcpher.binarySearch(all,ob,0,all.length-1,obCompare);
}
function obCompare(a,b) {
  if (a.key < b.key)
     return -1;
  if (a.key > b.key)
    return 1;
  return 0;
}

Batching