You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Database tracker is able to detect changed values #43
For more info & context, read the Use Case in #44.
Story
When user tells the database tracker to create a commit (see #41)
Then it detects the changes
and converts them to blocks in a jawn commit
And writes the commit to the feed in jawn
Additional Info
What kind of Database?
For now we will assume sqlite.
How does it figure out what changes have occurred?
The database tracker will need to be able to compare what's in the jawn feed and what's in the database.
Either way, the feed should have info about when data was last pulled from the database.
Option 1: Parse the database log
Option 2: If the database tables keep a "last updated" timestamp then we can just query for things that have been updated since the last time the feed was updated. -- we can either ask the Open Data team whether their data has this info, or we can read up on ArcGiS ourselves.
Just wanted to give you an update on what I'm going. Here is my current thinking for how to detect updates, assuming we have a lastUpdate column in each table in the db.
varsqlite3=require('sqlite3').verbose()varfs=require('fs')module.exports=DatabaseTrackerfunctionDatabaseTracker(dbfile){varexists=fs.existsSync(dbfile)this.db=newsqlite3.Database(dbfile)}DatabaseTracker.prototype.getAllTables=function(callback){varquery="Select name FROM sqlite_master WHERE type = 'table'"vardbTracker=thisthis.db.serialize(function(){dbTracker.db.all(query,function(err,tables){if(err){thrownewError(err)}callback(tables)})})}DatabaseTracker.prototype.getChanges=function(date,callback){varcurrentDb=this.dbfunctionfindAllChanges(tables){varchanges={}vartableNumber=tables.lengthtables.forEach(function(item,index){//TODO: might be better to use db.All() instead of statementvarstatement=currentDb.prepare('SELECT * FROM (?) t WHERE t.lastUpdate > '+date)statement.all(item.name,function(err,rows){if(err){console.log(err)}changes[item.name]=rowsstatement.finalize()if(changes.length=tableNumber){callback(changes)}})})}this.getAllTables(findAllChanges)}
This code does not fully run yet, but that is how far I've gotten.
Activity
flyingzumwalt commentedon May 11, 2016
Standardized SQL functions in ArcGIS Online
benjaminettori commentedon May 11, 2016
Start with one table toy example.
Then move on to several tables. How do you represent multiple tables of data in one feed? Or do we separate tables into multiple feeds?
flyingzumwalt commentedon May 12, 2016
My current thinking on how to do this: Storing Tabular Data in a DAG. If you need context, this is from the larger Replication Patterns deck, which outlines how Git structures its DAG.
I do have some new tweaks to the ideas in that deck, based on the way "live" feeds work in hypercore, but the slides get at the essential idea.
Especially look at slides 18 and 19, plus Mapping Git Patterns to Hypercore, which starts on slide 22
benjaminettori commentedon May 16, 2016
@flyingzumwalt @CameronSima
Just wanted to give you an update on what I'm going. Here is my current thinking for how to detect updates, assuming we have a lastUpdate column in each table in the db.
This code does not fully run yet, but that is how far I've gotten.
flyingzumwalt commentedon May 18, 2016
We should put the database tracker code into a separate repository. I set up a github repo here: https://github.com/CfABrigadePhiladelphia/jawn-db-tracker (cc @mels065)