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.
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 back-end platforms if you want to take a look at that.
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
Method | Return type | Brief description |
---|---|---|
getId() |
String |
Returns the item’s id in the database. |
toJson() |
String |
Returns 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
Method | Return type | Brief description |
---|---|---|
allOk(mutateResults) |
Boolean |
Returns true if all of the items in the result set were successful. |
anyOf(values) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value matches any of the passed in values. |
anyValue() |
QueryOperator |
Returns a query operator that evaluates to true if the field has any value. |
between(value1, value2) |
QueryOperator |
Returns a query operator that evaluates to true if the field has a value in-between the two passed in values. |
count(query) |
Integer |
Returns the number of items that match the query. |
greaterThan(value) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value is greater than the passed in value. |
greaterThanOrEqualTo(value) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value is greater than or equal to the passed in value. |
lessThan(value) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value is less than the passed in value. |
lessThanOrEqualTo(value) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value is less than or equal to the passed in value. |
load(id) |
ScriptDbMap |
Loads an item from the database by id. |
load(ids) |
ScriptDbMap[] |
Loads items from the database by id. |
not(value) |
QueryOperator |
Returns a query operator that evaluates to true if the field’s value does not match the passed in value. |
query(query) |
ScriptDbResult |
Query the database for matching items. |
remove(item) |
void |
Removes an item from the database. |
removeBatch(items, atomic) |
MutationResult[] |
Removes items from the database. |
removeById(id) |
void |
Removes an item from the database by id. |
removeByIdBatch(ids, atomic) |
MutationResult[] |
Removes items from the database by id. |
save(item) |
ScriptDbMap |
Saves a new item to the database. |
save(item) |
ScriptDbMap |
Saves 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 back-end abstracted platforms.
Property | Type | Description |
---|---|---|
ASCENDING |
SortDirection |
Ascending sort direction. |
DESCENDING |
SortDirection |
Descending sort direction. |
LEXICAL |
SortStrategy |
Lexical sort strategy. |
NUMERIC |
SortStrategy |
Numeric sort strategy. |
The Numeric parameter will be accepted, but ignored and lexical sorting will be done instead.
Method | Return type | Brief description |
---|---|---|
getSize() |
Integer |
Returns the total number of items in the query results. |
hasNext() |
Boolean |
Returns true if there are more items left in the query results. |
limit(number) |
ScriptDbResult |
Limits the number of items in the query results. |
next() |
ScriptDbMap |
Returns the next item in the query results. |
paginate(pageNumber, pageSize) |
ScriptDbResult |
Limits the query results to a single page of items, using the passed in page number and size. |
sortBy(fieldPath) |
ScriptDbResult |
Sorts the query results by the specified field, lexically and ascending. |
sortBy(fieldPath, direction) |
ScriptDbResult |
Sorts the query results by the specified field, lexically and using the passed in direction. |
sortBy(fieldPath, direction, strategy) |
ScriptDbResult |
Sorts the query results by the specified field, using the passed in direction and strategy. |
sortBy(fieldPath, strategy) |
ScriptDbResult |
Sorts the query results by the specified field, ascending and using the passed in strategy. |
startAt(number) |
ScriptDbResult |
Starts 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.
Method | Return type | Brief description |
---|---|---|
successful() |
Boolean |
Returns 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());
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 back-ends 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 forum.