Using data from multiple scriptDB

Deprecated

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.

There are a number of examples on this site of one library being used to provide data to another script. You may want to do this for a number of reasons.

The limit for a scriptDB is 50mb. A scriptDB is associated with a script, and one script can reference multiple libraries. It follows then that if you use multiple libraries, you can create multiple 50mb scriptDBs for a given application. Note though that there is a 50mb overall quota for a particular user, so if you did plan to go over 50mb in total, you could create additional google apps IDs, and create additional scripts in these as required.
You may want to partition your data such that different applications should have access to only certain items. Applications would associate with different libraries (and access their scriptDB) depending on how you wanted to organize that.
In Siloing, i showed how to split up the scriptdb into sections so that multiple kinds of data can be kept in the same DB. This gives the added benefit of abstraction – the silo class itself worries about the physical db, not your calling module. That means that you can, if necessary move data around without making any changes in your calling application, whether for space or organizational reasons.

Page Content hide

1 An example

2 Now here’s an example application

3 And here’s the logged output

An example

Let’s say that we wanted to keep customer data in a separate DB from orders data, with various applications accessing one or both of these data items. To demonstrate this, I’m going to create 3 scripts.

customersDB
ordersDB
useMultipleDB

The first two are libraries, that are referenced by the third, like this. I’m using functions from the mcpher library, so we need that too.

Taking a look at the orders and customers libraries, they could not be simpler. Their only role is to provide a handle into their respective scriptDBs and the code for each is exactly the same aside from the arbitrary silo name I’ve assigned .

// this can be used by other scripts as a dumping ground
function getSilo  () {
  // get a scriptDB silo area in scriptDB associated with this script, and hash the user email address
  return mcpher.scriptDbSilo("orders", ScriptDb.getMyDb(), true, true);
  
}

Now here’s an example application

// combine data from multiple scriptdb
function testSilo () {
  // get a scriptDB silo area in scriptDB associated with this script, and hash the user email address
  var customerSilo = customersDB.getSilo();
  var orderSilo = ordersDB.getSilo();
  var silo= mcpher.scriptDbSilo("accounts",publicStuffDb() );
  // get rid of all current records
  customerSilo.remove();
  orderSilo.remove();
  
  // save some data to different scriptDBs
  customerSilo.save( {customer:{Id:1, name:"john"}} );
  customerSilo.save( {customer:{Id:2,name:"fred"}} );
  
  orderSilo.save ( {order:{Id:1,customerId:1,"items":[{"a":20},{"b":10}]}});
  orderSilo.save ( {order:{Id:2,customerId:1,"items":[{"c":20}]}});
  orderSilo.save ( {order:{Id:3,customerId:2,"items":[{"d":9}]}});
  
  // get orders for customer  and show them
  var customerResults = customerSilo.query({});
  while (customerResults.hasNext()) {
    var c = customerResults.next();
    var orderResults = orderSilo.query ({order:{customerId: c.customer.Id}} );
    while( orderResults.hasNext()) {
      var r = orderResults.next();
      r.name = c.customer.name;
      Logger.log (JSON.stringify(r));
    }
  }
  // get orders created by the currently logged in user
  Logger.log("-- orders created by current user --");
  var orderResults = orderSilo.query ({userStamp:orderSilo.who()} );
  while( orderResults.hasNext()) {
      
      Logger.log (JSON.stringify(orderResults.next()));
  }
  // write combined orders to current db
  silo.remove();
  var customerResults = customerSilo.query();
  while (customerResults.hasNext()) {
    var c = customerResults.next();
    var orderResults = orderSilo.query ({order:{customerId: c.customer.Id}});
    while( orderResults.hasNext()) {
      var r = orderResults.next();
      silo.save( {data:{customer:c.customer,order:r.order}} );
    }
  }

}

And here’s the logged output

{"siloId":"orders","timeStamp":1358559849276, "order":{"customerId":1,"items":[{"a":20},{"b":10}],"Id":1},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y=","name":"john"}
{"siloId":"orders","timeStamp":1358559849570, "order":{"customerId":1,"items":[{"c":20}],"Id":2},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y=","name":"john"}
{"siloId":"orders","timeStamp":1358559849963, "order":{"customerId":2,"items":[{"d":9}],"Id":3},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y=","name":"fred"}
-- orders created by current user --
{"siloId":"orders","timeStamp":1358559849276, "order":{"customerId":1,"items":[{"a":20},{"b":10}],"Id":1},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y="}
{"siloId":"orders","timeStamp":1358559849570, "order":{"customerId":1,"items":[{"c":20}],"Id":2},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y="}
{"siloId":"orders","timeStamp":1358559849963, "order":{"customerId":2,"items":[{"d":9}],"Id":3},"userStamp":"tocp4ah7+IDlR4ttxKeOTWzLV2Y="}

For help and more information join our forum, follow the blog or follow me on Twitter