Datastore driver

This is the the driver for DB.DATASTORE described in Database abstraction with google apps script
The library reference is MPZF_EC6nOZFAjMRqCxEaUyz3TLx7pV4j
 This tutorial references the the cDataHandler and cEzyOauth2, both of which have been superseded by cDbAbstraction and cGoa which are easier to implement. If you’d rather work with those, the slides below show an example.

Background to implementation.

This driver has been tricky to implement. The Cloud Datastore calls itself  “Google Cloud Datastore: A Fully Managed NoSQL Data Storage Service” and is normally used with Google App Engine using Python, Java, PHP or Go. There is no API for Google Apps Script, it is not normally used directly from a JavaScript client, and neither is it a database as ‘we would know it’.
There is a JSON API, and along with the new concepts of entities , paths, ancestors and tricky indexing it’s quite difficult to get started with. It does have a GQL query language which is SQL-ish, but you can only use it for querying (not updating). It is also only really able to handle objects that are flat, and complicated queries need to have indexes pre-setup – in other words you need to know what you’l ask before you ask. 
Since this is a database abstraction driver, I don’t need the full capabilities of DataStore since I only need those that are present in other databases in the sub-universe. I also need to use  Flattening an object with dot syntax to allow for multi level object storage and querying, and because of the limitation around pre-indexing, I need to do hybrid querying with some of it happening in datastore, and more complex constraints happening in the DataHandler. 

Here is a quick primer:

Quotas

Before you choose to use DataStore, you’ll need to think about quotas. It’s very easy to blow the free daily allowance – for any meaningful application you’ll probably end up paying something. There is a quota dashboard on the Google Developers Cloud console.
The data abstraction library has caching enabled from the start, so any data taken from cache doesn’t eat into your quota.

Authentication

If you’ve been avoiding oAuth2, you can’t. DataStore demands authentication. The DataHandler can accept and access token, and you can easily use EzyOauth2 – taking some pain out of Apps Script API authentication to get one for both web app and regular applications. More about that later.

Locking

Although Datastore has the concept of ‘Transactions’, you can’t protect sections of code like you can when you use it with App Engine. I’m Using named locks with Google Apps Scripts to protect sensitive transactions.

Setup

There’s a little bit of setting up to do

  • Create an application in the Google Cloud Console
  • Enable datastore
  • Get some oAuth2 credentials
  • Create your app from EzyOauth2 patterns
  • Run a web app to allow access and store your access/refresh token environment.

Here’s some libraries you’ll need or are used internally

librarykeycomments
cDataHandlerMj61W-201_t_zC9fJg1IzYiz3TLx7pV4jAbstracted interface to back end databases, and all known drivers
cCacheHandlerM3reA5eBxtwxSqCEgPywb9ai_d-phDA33Manages caching of query results
cNamedLockMpv7vUR0126U53sfSMXsAPai_d-phDA33Cross script locking of abstract resources
cEzyOauth2MSaYlTXSVk7FAqpHNCcqBv6i_d-phDA33Manages oAuth2 credentials and offline refreshing of access tokens
cFlattenMqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4jFlattens complex objects to 1 level dot syntax objects  so they can be stored/queries in a 2 dimensional space
ezyOauth2  templateshttps://script.google.com/d/1ll5nvMOZL5YODaj71l0-XSaD0BBciG_zIV2I0Neu3Nz1LaKY6-4WiJAt/edit?usp=sharingPatterns for web and nonweb apps for easy oath2 authentication. Take a copy of this

How it all fits together

Oauth2 pattern

As described in EzyOauth2 patterns, apps that require oAuth2 can be complicated. However, once you’ve copied the template, the pattern is simple and repeatable. Here are the steps

  • First time in – store your credentials and scope. You do this only once. You can delete this after running it once, so you don’t have to carry credentials in your code.
setAuthenticationPackage_ ({ 
    clientId : "xxx.apps.googleusercontent.com",
    clientSecret : "xxx",
    scopes : ['https://www.googleapis.com/auth/datastore','https://www.googleapis.com/auth/userinfo.email'],
    service: 'google',
    packageName: 'googleDatastore'
  });
  • First time in, provoke a doGet() dialog by publishing as a web app to create an access and refresh token. This will grab an offline authorization which will refresh automatically when needed. These will be used for both web apps and non-webapps. However you do need to publish and run once to create the dialog. If this is a non-web app, then unpublish it again when done. Your doGet() function looks like this. If this is a web actually a webapp, then the doSomething() function is where you create your app.
/** 
 * this is your web app
 * @param {object} webapp param object
 * return {HtmlOutput} 
 */

function doGet (e) {
  return doGetPattern(e, constructConsentScreen, doSomething, 'googleDatastore') ;
}

/**
 * tailor your consent screen with an html template
 * @param {string} consentUrl the url to click to provide user consent
 * @return {string} the html for the consent screen
 */
function constructConsentScreen (consentUrl) {
  return '<a href = "' + consentUrl + '">Authenticate to datastore</a> ';
}

/**
 * this is your main processing - will be called with your access token
 * @param {string} accessToken - the accessToken
 */
function doSomething (accessToken) {
 
   var options = {
     method: "POST",
     headers: {
       authorization: "Bearer " + accessToken
     }
   };

  var result = UrlFetchApp.fetch("https://www.googleapis.com/datastore/v1beta2/datasets/xliberationdatastore/lookup", options);
  return HtmlService.createHtmlOutput (' it worked' + result.getContentText());

}

/**
 * gets the property key against which you want the authentication package stored
 * @param {string} optPackageName
 * @return {string}
 */
function getPropertyKey_ (optPackageName) {
  return "EzyOauth2Datastore" + (optPackageName ? '_' + optPackageName : '');
}

If this is not a webapp, then you need something that looks like this too, where dataStoreTest is your application that is going to d the work.

function dataStoreExample() {
  // this will get an access token and pass it to doTheWork()
  return doGetPattern({} , constructConsentScreen, dataStoreTest,'googleDatastore');
}

More info

The datastore code

If you are interested in the implementation, you’ll find the code here

The datastore test.

If you’ve already used Database abstraction with google apps script then this will be familiar. The database access is the same for datastore as it is for any other backend. The only specific coding is setting up the handler. Here are 3 different setups for 3  different backends – datastore, parse.com and Drive. You’ll notice they are very similar. In this i’ve opted out of analytics tracking, and turned off caching- since I’m going to run the same tests on each platform to make sure I get the same result. You’ll find these tests in the Patterns template and you may find them useful to check out your set up.

function dataStoreTest (accessToken) {
   
   // get a datastore handler
   var handler = new cDataHandler.DataHandler (
     'flattener',                              // Kind
     cDataHandler.dhConstants.DB.DATASTORE,    // Datastore
     undefined, 
     'xliberationdatastore',                   // project id
     undefined, 
     undefined,
     true,                                     // analytics opt out
     'bruce',                                  // analytics debugging tracking 
     accessToken,                              // the access token
     true);                                    // disable caching for testing  
     

  assert(handler.isHappy(), 'unable to get datastore handler','handler');
  return testFlattener (handler, accessToken)
}

function driveTest (accessToken) {
   
   // get a datastore handler
   var handler = new cDataHandler.DataHandler (
     'flattener.json',                              // Kind
     cDataHandler.dhConstants.DB.DRIVE,    // Datastore
     undefined, 
     '/datahander/driverdrive',                   // project id
     undefined, 
     undefined,
     true,                                     // analytics opt out
     'bruce',                                  // analytics debugging tracking 
     accessToken,                              // the access token
     true);                                    // disable caching for testing  
     

  assert(handler.isHappy(), 'unable to get drive handler','handler');
  return testFlattener (handler, accessToken)
}

function parseTest () {
   
   // get a parse handler
  var userStore = PropertiesService.getScriptProperties();
  var handler = new cDataHandler.DataHandler (
      'flattener',                            // class
      cDataHandler.dhConstants.DB.PARSE,      // parse
      undefined,
      'mp',
      JSON.parse(userStore.getProperty("parseKeys")), // parse credentials
      undefined,                       
      true,             
      'bruce',
      undefined,
      true);
     
  assert(handler.isHappy(), 'unable to get parse handler','handler');
  return testFlattener (handler,null)
}

Here’s a set of tests – it doesn’t matter which back end you are using. The code is the same.

function testFlattener (handler, accessToken) {

  var testData = [
      {
        name:'john',
        stuff:{age:25,sex:'male'},
        networks:{
          google:{name:'john',usage:'frequent'},
          facebook:{name:'jsmith',usage:'occassionally'}
        }
      },
      { 
        name:'mary',
        stuff:{age:26,sex:'female'},
        networks:{
          google:{name:'mjones',usage:'frequent'},
          facebook:{name:'mary',usage:'occassionally'}
        }
      },
      { 
        name:'ethel',
        stuff:{age:50,sex:'female'},
        networks:{
          google:{name:'ethel',usage:'seldom'},
          facebook:{name:'ejones',usage:'seldom'}
        }
      },
      { 
        name:'fred',
        stuff:{age:60,sex:'male'},
        networks:{
          google:{name:'fred',usage:'occassionaly'},
          facebook:{name:'fred',usage:'seldom'}
        }
      },
      { 
        name:'freda',
        stuff:{age:61,sex:'female'},
        networks:{
          google:{name:'freda',usage:'often'},
          facebook:{name:'freda',usage:'seldom'}
        }
      },
      { 
        name:'wilma',
        stuff:{age:20,sex:'female'},
        networks:{
          google:{name:'wilma',usage:'occassionaly'},
          facebook:{name:'wflintstone',usage:'seldom'}
        }
      },
      { 
        name:'betty',
        stuff:{age:21,sex:'female'},
        networks:{
          google:{name:'betty',usage:'occassionaly'},
          facebook:{name:'brubble',usage:'often'}
        }
      },
      { 
        name:'barney',
        stuff:{age:21,sex:'male'},
        networks:{
          google:{name:'barney',usage:'often'},
          facebook:{name:'rubble',usage:'never'}
        }
      },
      { 
        name:'mr slate',
        stuff:{age:91,sex:'male'},
        networks:{
          google:{name:'slate',usage:'seldom'},
          facebook:{name:'slate',usage:'never'}
        }
      },
      { 
        name:'dino',
        stuff:{age:10,sex:'male'},
        networks:{
          google:{name:'dino',usage:'seldom'},
          facebook:{name:'dino',usage:'often'}
        }
      }
    ];
    
  // delete everything
  var result = handler.remove();
  assert (result.handleCode >= 0 ,
    result,'remove');

  //query everything
  var result = handler.query();
  assert (result.handleCode >= 0 &amp;&amp;
    result.data.length === 0,
    result, 'querypostdelete');

  // add some data that needs flattened
  var result = handler.save (testData);
  assert (result.handleCode >= 0 ,
    result,'save');  

  //query everything
  var result = handler.query();
  assert (result.handleCode >= 0 &amp;&amp;
    result.data.length === testData.length,
    result, 'query');

  // query everything with limit
  var result = handler.query(undefined,{limit:1});
  assert (result.handleCode >= 0 &amp;&amp; 
    result.data.length === 1, 
    result,'querylimit');

  // sort reverse
  var result = handler.query(undefined,{sort:'-name'});
  assert (result.handleCode >= 0 &amp;&amp;
    result.data.length === testData.length,
    result, 'querysort');

  // sort reverse, skip 1
  var result = handler.query(undefined,{skip:9,sort:'name'});
    var result = handler.query(undefined,{skip:9});
  Logger.log(result.data.length);
  Logger.log(testData.length);
  assert (result.handleCode >= 0 &amp;&amp;
    result.data.length === testData.length -1,
    result,'querysort+skip');
  
  // flattten query everything
  var result = handler.query({"name":'ethel'});
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { return d.name ==='ethel'; }).length === result.data.length ,
    result,'filterdot0'); 

  // flattten query everything
  var result = handler.query({stuff:{sex:'female'}});
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { return d.stuff.sex ==='female'; }).length === result.data.length ,
    result,'filter'); 
    
  // try constraints
  var c = cDataHandler.dhConstants.CONSTRAINTS;

  // check a single constraint works
  var result = handler.query({"stuff.age":handler.constraints([[c.GT,25]])});
  assert (result.handleCode >= 0 &amp;&amp; 
    testData.filter(function(d) { 
      return   d.stuff.age > 25; })
      .length === result.data.length , 
      result,'filterdotc1'); 

  // two constraints on same property
  var result = handler.query ( { 
      'stuff.age': handler.constraints([[c.LT,60],[c.GT,25]])
    });
  assert (result.handleCode >= 0 &amp;&amp; testData.filter(function(d) { 
    return  d.stuff.age > 25 &amp;&amp; d.stuff.age < 60; }
    ).length === result.data.length , result,'filterdotc2'); 
    
   // check two constraints plus a different property
  var result = handler.query ( { 
    'stuff.age': handler.constraints([[c.LT,60],[c.GTE,25]]),
    'stuff.sex': 'male'},
    undefined,1,true);
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { 
      return  d.stuff.age >= 25 &amp;&amp; d.stuff.age < 60 &amp;&amp; d.stuff.sex === 'male'; }
    ).length === result.data.length ,
    result,'filterdotc3'); 
  
  var result = handler.query ( { name:handler.constraints([[c.IN,['ethel','fred']]])});
  assert (result.handleCode >= 0 &amp;&amp; testData.filter(function(d) { 
    return   ['ethel','fred'].indexOf(d.name) != -1; }
    ).length === result.data.length , result,'filterdotc4'); 
  
  // check text constraints  
  var result = handler.query ( { 
    'stuff.age': handler.constraints([[c.GT,25]]), 
    'stuff.sex': 'male',
    'name':handler.constraints([[c.IN,['john','mary']]])
    });
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { 
      return  d.stuff.age > 25 &amp;&amp; d.stuff.sex === 'male' &amp;&amp; ['john','mary'].indexOf(d.name) != -1; }
    ).length === result.data.length 
    , result,'filterdotc5'); 

  // make sure we're getting the right id with complex constaints
  var result = handler.query ( { 
      'stuff.age': handler.constraints([[c.GT,25]])
    },
    {limit:1}, 
    1,
    true );
  assert (result.handleCode >= 0 &amp;&amp;
    result.data.length ===1 &amp;&amp; 
    result.handleKeys.length ===1, 
    result, "limitkeycheck1");
  
  var r2 = handler.get(result.handleKeys);
  assert (
    r2.handleCode >= 0 &amp;&amp; 
    r2.data.length === result.data.length &amp;&amp;
    r2.data.every (function(d) { return d.stuff.age > 25 ; }), r2, "limitgetcheck1");
    
   // lets try updating
  var result = handler.query ( { 
    'stuff.sex': 'male'},
    undefined,1,true);
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { 
      return d.stuff.sex === 'male'; }
    ).length === result.data.length ,
    result,'update1'); 
   
  var r2 = handler.update ( result.handleKeys, result.data.map (function(d) { d.stuff.man = d.stuff.sex === 'male'; return d; } ));
  assert (r2.handleCode >= 0, r2, 'update 2');
  
  var r2 = handler.query ( { 
    'stuff.man': true} );
  assert (r2.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { 
      return d.stuff.sex === 'male'; }
    ).length === result.data.length ,
    result,'update 3'); 
 
  // lets try counting
  var result = handler.count ();
  assert (result.handleCode >= 0 &amp;&amp;
    testData.length === result.data[0].count ,
    result,'count 1'); 
    
  // check count
  var result = handler.count ( { 
    'stuff.age': handler.constraints([[c.GT,25]]), 
    'stuff.sex': 'male',
    'name':handler.constraints([[c.CONTAINS,'el']])
    });
  assert (result.handleCode >= 0 &amp;&amp;
    testData.filter(function(d) { 
      return  d.stuff.age > 25 &amp;&amp; d.stuff.sex === 'male' &amp;&amp; d.name.indexOf('el') != -1; }
    ).length === result.data[0].count
    , result,'count 2'); 
}

See more like this in Database abstraction with google apps script


Error: Your Requested widget " jetpack_widget_social_icons-7" is not in the widget list.
  • [do_widget_area elegantwp-bottom-widgets]
    • [do_widget id="custom_html-13"]
  • [do_widget_area elegantwp-footer-1]
    • [do_widget id="eu_cookie_law_widget-2"]
  • [do_widget_area elegantwp-footer-2]
    • [do_widget id="text-5"]
  • [do_widget_area elegantwp-footer-3]
    • [do_widget_area elegantwp-footer-4]
      • [do_widget_area elegantwp-fullwidth-widgets]
        • [do_widget_area elegantwp-header-banner]
          • [do_widget_area elegantwp-home-bottom-widgets]
            • [do_widget_area elegantwp-home-fullwidth-widgets]
              • [do_widget_area elegantwp-home-top-widgets]
                • [do_widget_area elegantwp-sidebar-one]
                  • [do_widget id="bellows_navigation_widget-3"]
                • [do_widget_area elegantwp-sidebar-two]
                  • [do_widget id="custom_html-6"]
                  • [do_widget id="text-6"]
                  • [do_widget id="ai_widget-5"]
                • [do_widget_area elegantwp-top-widgets]
                  • [do_widget_area footer_left]
                    • [do_widget id="text-3"]
                  • [do_widget_area sidebar-left-widget-area]
                    • [do_widget id="categories-2"]
                  • [do_widget_area sidebar-right-widget-area]
                    • [do_widget id="custom_html-5"]
                  • [do_widget_area widgets_for_shortcodes]
                    • [do_widget id="jetpack_widget_social_icons-7"]
                  • [do_widget_area wp_inactive_widgets]
                    • [do_widget id="extended-categories-2"]

                  bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines