RipDB - scriptDB emulator

As you all know, scriptDB, the handy little nosql database associated with each script, was deprecated some time ago. Most people will have already moved to an alternative. Some have moved to my database abstraction solution, which provides a noSQL layer to a selection of backend datastores and databases.

If you are like me (and I was a very heavy user of ScriptDB), you will still have a few projects around that are on ice because you didn't get round to converting them to something else. Looking through this web site, I found about 6 that I hadn't converted yet. It's fairly easy to move over to database abstraction, but where's the fun in that, when I could instead play around with creating a layer on top of db abstraction that looks just like the old scriptDB did. That way I don't have to do anything much to the old code - just add some libraries and pick a database platform. 

So here it is - I'm calling it RipDB in honor of ScriptDB that has passed on to a better place. I've copied in the deprecated ScriptDB docs into this pages to preserve them for posterity in case they disappear sometime. Pretty much every method and property has been replicated as well as I could reverse engineer how I believe they used to work.

As a word of caution, I don't really recommend this approach for big data stores, but it's fine for all those small projects you didn't yet get  round to converting. However, if you are using DriverScratchDriverSheetDriverDrive or even DriverProperties for small datastores (and that's all they are good for), you can wrap your entire script in a dbabstraction transaction (as long as you don't  mind locking out other users for the duration) and it will all happen in memory and be super fast with automatic rollback. More about that later.

Regardless of the back end chosen you will automatically get the caching and locking built in to Database abstraction with google apps script

Getting a handler.

This is the only thing that really changes from your scriptDB code. Since we are using DB abstraction to fill in for ScriptDB, we just need to get a  handler for our chosen database. As usual, for testing, I'm using DriverScratch to start with, as this avoids me having to clean up later.

  var handler = new cDbAbstraction.DbAbstraction ( cDriverScratch , {
    siloid:'dbtest',
    dbid:'ripdb'
  });

Next we'll do the equivalent of the old ScriptDB.getMyDb(), which now looks like this

var db = new RipDB(handler);

Of course we'll need to include the appropriate libraries - you'll need cDbAbstraction and whichever driver you choose. I'm taking cDriverScratch. You can find all the library dependencies listed here, or get all the source and info from github if you prefer. There's also a little test script here using a selection of backend platforms if you want to take a look at that.

https://script.google.com/macros/s/AKfycbwZ2Hht93wTNzvRmYINYF7obaOHciBXWcP_wAiEtyGq70_x3cI/exec?list=cDbAbstraction,cDriverScratch,cRipDb

And that's it. You can now use db as if it was a scriptDB class, but behind the scenes it will use Db abstraction along with whichever db platform you've chosen.

Emulating the Classes from ScriptDb

I've tried to religiously emulate the same classes, properties and methods as ScriptDB, but unfortunately it's no longer available to test against so let me know if things don't seem to work as before. 



RipDbMap

This is the ScriptDbMap that's returned from a save or load operation. Both methods are implemented as in the original below

MethodReturn typeBrief description
getId()StringReturns the item's id in the database.
toJson()StringReturns the JSON string representation of this object.
Here's the test from the ScriptDb documentation, modified to use the dbAbstraction handler.

function test2() {
  // get some abstraction handler
  var handler = new cDbAbstraction.DbAbstraction ( cDriverScratch , {
    siloid:'dbtest',
    dbid:'ripdb'
  });
  
  // simulate a scriptDB
  var db = new cRipDB.RipDB(handler);
 
  var item = db.save({
    type: 'person',
    name: 'fred',
    age: 25
  });
  Logger.log('Name: ' + item.name);
  Logger.log('Age: ' + item['age']);

}

and the result
[15-02-25 11:32:21:090 GMT] Name: fred
[15-02-25 11:32:21:091 GMT] Age: 25

RipDb

This is equivalent to the ScriptDbInstance class and all these methods are implemented - substituting my version for the returned objects
MethodReturn typeBrief description
allOk(mutateResults)BooleanReturns true if all of the items in the result set were successful.
anyOf(values)QueryOperatorReturns a query operator that evaluates to true if the field's value matches any of the passed in values.
anyValue()QueryOperatorReturns a query operator that evaluates to true if the field has any value.
between(value1, value2)QueryOperatorReturns a query operator that evaluates to true if the field has a value in-between the two passed in values.
count(query)IntegerReturns the number of items that match the query.
greaterThan(value)QueryOperatorReturns a query operator that evaluates to true if the field's value is greater than the passed in value.
greaterThanOrEqualTo(value)QueryOperatorReturns a query operator that evaluates to true if the field's value is greater than or equal to the passed in value.
lessThan(value)QueryOperatorReturns a query operator that evaluates to true if the field's value is less than the passed in value.
lessThanOrEqualTo(value)QueryOperatorReturns a query operator that evaluates to true if the field's value is less than or equal to the passed in value.
load(id)ScriptDbMapLoads an item from the database by id.
load(ids)ScriptDbMap[]Loads items from the database by id.
not(value)QueryOperatorReturns a query operator that evaluates to true if the field's value does not match the passed in value.
query(query)ScriptDbResultQuery the database for matching items.
remove(item)voidRemoves an item from the database.
removeBatch(items, atomic)MutationResult[]Removes items from the database.
removeById(id)voidRemoves an item from the database by id.
removeByIdBatch(ids, atomic)MutationResult[]Removes items from the database by id.
save(item)ScriptDbMapSaves a new item to the database.
save(item)ScriptDbMapSaves an existing item to the database, updating it.
saveBatch(items, atomic)Object[]Saves items to the database.


Here's an example of saving and reloading

  var db = new cRipDB.RipDB(handler);

  
  // do a few  saves
  var m1 = db.save({name:'fred',age:21}); 
  var m2 = db.save({name:'mary',age:55});
  var m3 = db.save({name:'harry',age:55});

  // load then all by  id
  Logger.log(JSON.stringify(db.load ([
    m3.getId(),
    m2.getId(),
    m1.getId()
  ])));

and the result

[{"name":"fred","age":21},{"name":"mary","age":55},{"name":"harry","age":55}]



Doing a query

Queries work the same way as with ScriptDB. A query returns a RipDbResult which is a simulation of a ScriptDbResult. In fact, just like ScriptDb, the query is not actually executed behind the scenes until the results are retrieved. This allows you to continue to add constraints to the the query in multiple operations.

  // do a query
  var result = db.query({age:55}).sortBy("name",db.ASCENDING);

  // retrieve the results
  while (result.hasNext()) {
    Logger.log(result.next());
  }

the result

[15-02-25 13:02:25:019 GMT] {age=55, name=harry}
[15-02-25 13:02:25:019 GMT] {age=55, name=mary}

RipDbResult

These are simulations of a ScriptDbResult and are returned by a query. In fact, they really are the unexecuted query parameters which have been built up. Issuing a hasNext(), a next() or a getSize() all cause the query to be finally executed automatically. All of these methods have been implemented, with the exception of numeric sorting - which is not available on many of the backend abstracted platforms. 
PropertyTypeDescription
ASCENDINGSortDirectionAscending sort direction.
DESCENDINGSortDirectionDescending sort direction.
LEXICALSortStrategyLexical sort strategy.
NUMERICSortStrategyNumeric sort strategy.
The Numeric parameter will be accepted, but ignored and lexical sorting will be done instead.

MethodReturn typeBrief description
getSize()IntegerReturns the total number of items in the query results.
hasNext()BooleanReturns true if there are more items left in the query results.
limit(number)ScriptDbResultLimits the number of items in the query results.
next()ScriptDbMapReturns the next item in the query results.
paginate(pageNumber, pageSize)ScriptDbResultLimits the query results to a single page of items, using the passed in page number and size.
sortBy(fieldPath)ScriptDbResultSorts the query results by the specified field, lexically and ascending.
sortBy(fieldPath, direction)ScriptDbResultSorts the query results by the specified field, lexically and using the passed in direction.
sortBy(fieldPath, direction, strategy)ScriptDbResultSorts the query results by the specified field, using the passed in direction and strategy.
sortBy(fieldPath, strategy)ScriptDbResultSorts the query results by the specified field, ascending and using the passed in strategy.
startAt(number)ScriptDbResultStarts the query results with the item at the passed in index.

You can combine these - for example.
  var result = db.query({age:55}).sortBy("name",db.ASCENDING).limit(4).startAt(2);

All of the constraints available in the RipDb class are implemented. Here's an example.
  var result = db.query({name:db.anyOf(['harry','fred'])}).sortBy("name",db.ASCENDING);

and of combined constraints
 var result = db.query({age:db.greaterThan(22),name:db.lessThan("mary")}).sortBy("name",db.ASCENDING);

Count

Count takes the same syntax as a query, but is executed right away, returning the integer count of matches

Logger.log(db.count({age:db.between(20,22)}));

RipDbMutationResults

A couple of the batch methods return an array of MutationResults - essentially an array corresponding in length to the input array with a method to test if it worked. I've emulated this, but the results will be all false or all true. DbAbstraction doesn't have the concept of a partially successful operation - it's either all successful or all failed. It looks like this and you can use it the same way as the ScriptDB MutationResult class.
MethodReturn typeBrief description
successful()BooleanReturns true if the mutation was successful.

Debugging and sharing

Since RipDb uses DbAbstraction behind the scenes, you can can mix dbAbstraction syntax with RipDb syntax -- for example - a common debug pattern like this:

var result = db.query();
while (result.hasNext()) {
    Logger.log (result.next());
}

can be achieved more simply and with more information by using the handler directly natively..

Logger.log(handler.query());

To avoid carrying around references to both the handler and the db, you can get the handler a db is using in order to be able to use it directly, like this.

Logger.log(db.getHandler().query());

Wrapping the whole thing in a transaction. 

As I mentioned previously, certain backends benefit from being part of the same transaction because everything happens in memory. To do that the pattern looks like this. 

  var db = new RipDB(handler);
  
  var transactionResult = handler.transaction(function(hob) {

         ... your script with all the ScriptDB stuff....

         // you must signal success otherwise everything will be rolled back
        return hob.allDone();
  }
  
  // now test for success
  if ( hob.transaction.code <0) {
      // do something because it failed.
  } 

And of course, since the underlying platform is whatever you choose it to be the the data is available outside apps script or by interacting with DbAbstraction apps. Implementing this has been a lot of fun. I'd love your feedback on our G+ community.

For help and more information join our forum,follow the blog or follow me on twitter . For more on this see  Database abstraction with google apps script



Comments