This is the the driver for DB.SCRATCH described in Database abstraction with google apps script
MTnrkatWa6Lrta8eAg6_H0qi_d-phDA33
Background to implementation.
Note that this technique relies on the predictability of CacheService. Google warn that it’s not necessarily predictable, so at this point I’m not sure how robust this approach is, but I have many people out there using this now and haven’t had any issues reported. Having said that, its use is intended for testing, logging and so on in preparation for and in advance of moving to a more permanent back end
This driver uses cache to provide transient database storage. By default it lives for 1 hour, but its lifetime is extended each time it is accessed. It is ideal for early testing in preparation for other backends or passing data back and forwards between co-operating processes. It can handle much more data than the Properties service, and can be protected using cache communities.Authentication
There is no authentication required, since it uses cache – normal auth will apply. However here are some tips for using this database for collaboration. You need to invent your own codes to name database elements so may as well start with a plan.
|
1 2 3 4 5 6 7 8 |
var handler = new cDbAbstraction.DbAbstraction ( cDriverScratch , { dbid:'your invented api key', siloid:'your invented application key', scratchexpiry:60*60, // default is die after one hour private:false, // default is true for cache restricted to you cachecommunity:'optionally some community to share with' }); |
- siloid – use a consistent key to refer to a particular set of data. This would be roughly equivalent to an application key in some of the other databases.
- dbid – use this to refer to the owner of a dataset. This could be equivalent to an api key.
- scratchexpiry – by default this is 1 hour. The maximum is 6 hours. Note that a database lifetime will be extended each time you access it, so as long as you are using it, it will persist.
- private – by default it uses the library cache restricted to the current user. This means that all your scripts, running as you, using the same library will be able to access the data if you know the siloid and dbid. If you want to extend this to be able to be accessible by other users, then set private to false. This means that anyone who can access the library and knows the siloid and dbid can access the data
- specificcache – if you do not want to use the library cache (private or public) you can pass another cache object through this parameter – for example “specificcache”: CacheService.getUserCache() would use the cache from your script rather than the library.
- cachecommunity – whichever cache you are using can be further restricted by specifying a cache community. This is some code that you share between collaborators who would want to work on a particular set of data. All of the siloid, dbid and cache community as well as access to the selected library and cache are needed to be able to access the data.
Locking and caching
Transactions, Locking and rollback are all supported by this driver. In addition, results are cached just like any other driver. In this case, the mechanism which prolongs lifetime will not be invoked.
The code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
"use strict"; function createDriver (handler,siloId,driverSpecific,driverOb, accessToken,options) { return new DriverScratch(handler,siloId,driverSpecific,driverOb, accessToken,options); } function getLibraryInfo () { return { info: { name:'cDriverScratch', version:'0.0.2', key:'MTnrkatWa6Lrta8eAg6_H0qi_d-phDA33', share:"https://script.google.com/d/1ka9ODRxrUKUOTnuv4CkL9QnBTjkrXQhP0yJF-R2wjVz-qVdEK9G2S9sH/edit?usp=sharing", description:"scratch driver for dbabstraction" }, dependencies:[ cDriverMemory.getLibraryInfo(), cDelegateMemory.getLibraryInfo(), cCacheHandler.getLibraryInfo() ] }; } /** * DriverScratch * @param {cDataHandler} handler the datahandler thats calling me * @param {string} keyName this is the siloid - combined with dbid to create a unique key * @param {string} id some id you create for identifying this collection * @param {string} optDriverOb somw driver options * @param {object] options that were passed to dbabstraction * @return {DriverScratch} self */ /* options are * @param {Cache} [specificcache] a cache object to use, otherwise a public/private one belonging to cacheservice will be used * @param {string} [cachecommunity] a cache community id to restrict cache to particular groups * @param {boolean} [private=true] whether to use a private cache (not relevant if specificcache is passed) * @param {number} [scratchexpiry] in seconds - default time for db to live for */ var DriverScratch = function (handler,keyName,id,optDriverOb,accessToken, options) { var siloId = keyName; var dbId = id; var self = this; var driverOb = options || {}; var cacheSilo = 'd' + dbId + 's' + siloId; var DATAEXPIRY = 60 * 60 * 6; // data will always live max time var READEXTENSION = 60 * 5; // a read will only cause an extension if there hasnt been one for a while var parentHandler = handler; var enums = parentHandler.getEnums(); var handle, handleError, handleCode , handleIds, handleKey; var transactionBox_ = null; var scratch_; // this is the scratch data store var cacheData = new cCacheHandler.CacheHandler(DATAEXPIRY,cacheSilo,driverOb.private,false,driverOb.specificcache,driverOb.cachecommunity); // this is the scratch key store var cacheScratch = new cCacheHandler.CacheHandler(DATAEXPIRY,'s' + cacheSilo,driverOb.private,false,driverOb.specificcache,driverOb.cachecommunity); // im able to do transactions self.transactionCapable = true; // i definitely need transaction locking self.lockingBypass = false; // i am aware of transactions and know about the locking i should do self.transactionAware = true; /** * checks that the transaction matches the one stored * @param {string} id transaction id * @return {boolean} whether id matches */ self.isTransaction = function (id) { return transactionBox_ && transactionBox_.id === id ; }; /** * begins transaction and store current content * @param {string} id transaction id */ self.beginTransaction = function (id) { transactionBox_ = delegate.beginTransaction (id); }; self.transactionData = function (){ return delegate.transactionData(); }; /** * commits transaction * @param {string} id transaction id * @return {object} a normal result package */ self.commitTransaction = function (id) { return delegate.commitTransaction(id); }; self.clearTransactionBox = function () { transactionBox_ = null; }; /** * roll back transaction - resets memory to beginnging of transaction * @param {string} id transaction id * @return {object} a normal result package */ self.rollbackTransaction = function (id) { return delegate.rollbackTransaction(id); }; self.getTransactionBox = function () { return transactionBox_; }; self.getSiloId = function () { return siloId; }; self.getParentHandler = function () { return parentHandler; }; self.getType = function () { return enums.DB.SCRATCH; }; /** each saved records gets a unique key * @return {string} a unique key */ self.generateKey = function () { return parentHandler.generateUniqueString(); }; self.getDbId = function () { return dbId; }; handle = cacheScratch; var delegate = new cDelegateMemory.DelegateMemory(self); /** * @return {DriverScratch} the folder for the file */ self.getDriveHandle = function () { return handle; }; self.isExpired = function(entry) { var expired = !entry || entry.lastUpdated + entry.scratchExpiry * 1000 < new Date().getTime(); // clean up any detected expired data if (expired && entry) { cacheData.removeCache (entry.data); cacheScratch.removeCache(); } return expired; }; self.getScratch =function() { var entry = cacheScratch.getCache(); return self.isExpired(entry) ? null : entry; }; self.getData = function (entry) { return entry ? cacheData.getCache (entry.data) : null; } self.putScratch = function (entry, data) { var now = new Date().getTime(); entry = entry || {}; entry.lastUpdated = now; entry.dataUpdated = now; entry.scratchExpiry = driverOb.scratchexpiry || entry.scratchExpiry || 60 * 60; // the key will by default live an hour entry.data = entry.data || self.generateKey(); cacheData.putCache (data , entry.data); cacheScratch.putCache (entry); return entry; } self.resetExpiry= function (entry) { // we'll reset the expiry time on a read, but not too often if (entry) { var now = new Date().getTime(); if (entry.lastUpdated + READEXTENSION*1000 < now) { parentHandler.writeGuts ('reset expiry' , function (bypass) { // time for a complete refresh if the data is nearing expiration if ( entry.dataUpdated + DATAEXPIRY * 1000 <= now + entry.scratchExpiry * 1000) { Logger.log('doing a full reset of expiry'); self.putScratch (entry , self.getData(entry)) ; } else { // no need to write the data, just the key Logger.log('doing a key reset of expiry'); entry.lastUpdated = now; cacheScratch.putCache (entry); } }, function(bypass) { // do nothing if in a transaction }); } } } /** set the contents to the property, creating it if necessary * @param {string} json content * @return {File} the existing or created entry */ self.writeContent = function (content) { return self.putScratch (scratch_ , content) ; }; /** get the contents of the property * @return {object} the parsed content of the file */ self.getContent= function () { scratch_ = self.getScratch(); return scratch_ ? delegate.getContentSimpleKeys( self.getData(scratch_) ) : null; }; /** * get the memory - if its a transaction we already have it, if not read the sheet and make one * @return {DriverMemory} the men object */ self.getMem = function () { return parentHandler.inTransaction() ? transactionBox_.content : self.take(new cDriverMemory.DriverMemory(parentHandler, siloId)) ; }; /** * -------------------------------- * DriverSheet.replace () * replaces current sheet with whats in memory * @param {DriverMemory} mem to be saved * @return {Object} headingOb with indexes */ self.replaceWithMemory = function (mem) { return self.putBack(mem); }; self.putBack = function (mem) { return delegate.putBackSimpleKeys(mem); }; self.take = function (mem) { return mem.makeContent ( parentHandler.rateLimitExpBackoff ( function () { return (self.getContent() || []); })); }; /** create the driver version * @return {string} the driver version */ self.getVersion = function () { var v = getLibraryInfo().info; return v.name + ':' + v.version; }; /** * DriverScratch.getTableName() * @return {string} table name or silo */ self.getTableName = function () { return siloId; }; self.query = function (queryOb,queryParams,keepIds) { var result = delegate.query(queryOb,queryParams,keepIds); self.resetExpiry(scratch_); return result; }; /** * DriverScratch.remove() * @param {object} queryOb some query object * @param {object} queryParams additional query parameters (if available) * @return {object} results from selected handler */ self.remove = function (queryOb,queryParams) { return delegate.remove(queryOb,queryParams); }; /** * DriverMemory.removeById() * @param {string} keys key to remove * @return {object} results from selected handler */ self.removeByIds = function (keys) { return delegate.removeByIds (keys,'key'); }; /** * DriverScratch.save() * @param {Array.object} obs array of objects to write * @return {object} results from selected handler */ self.save = function (obs) { return delegate.save(obs, self.getMem()); }; /** * DriverScratch.count() * @param {object} queryOb some query object * @param {object} queryParams additional query parameters (if available) * @return {object} results from selected handler */ self.count = function (queryOb,queryParams) { return delegate.count(queryOb,queryParams); }; /** * DriverScratch.get() * @param {string} keys the unique return in handleKeys for this object * @return {object} results from selected handler */ self.get = function (keys) { return delegate.get (keys); }; self.getGuts = function (keys) { return self.getMem().get ( keys,true,'key'); }; /** * DriverScratch.update() * @param {string} keys the unique return in handleKeys for this object * @param {object} obs what to update it to * @return {object} results from selected handler */ self.update = function (keys,obs) { return delegate.update(keys,obs); }; self.updateGuts = function (keys,obs) { var memory = self.getMem(); var r = memory.update (keys,obs); return r.handleCode < 0 ? r : self.putBack (memory); } return self; } |
for help and more information, join our forum, follow the blog and or follow me on Twitter