DBJSON: SELECT * FROM persons AS JSON | Page 2 of 6

Legacy Data Applications

All of the current data-managing legacy infrastructure can get the same DBJSON facade/mediator component. Just imagine WMI with the same DBJSON^tm facade in front of it. If you ever had programmatically used WMI, I am sure you will find the DBJSON^tm one obvious and superior solution, to current quirky WMI concept of “classes”, which imposes yet another kind-of-a-oo language to learn. (for further reading please refer to : MOF Syntax for WMI Data and Event Blocks )

WMI actually uses standard RDBM (desktop version) with a different non standard SQL facade (aka OQL). This architecture required a lot of implementation effort and development to render a questionable result that we know and hate today as WMI API. So why OQL? Because SQL is not a natural fit (and was an overkill) for a desktop database storing hierarchy of objects. Hierarchy of objects? But this is DBJSON by default : natural representation of hierarchy of objects!

Also. Differences among numerous data binding and usage mechanisms on .NET/WIN platform are questionable and not very clear. OQL.NET vs WMI OQL vs SQL 92 = Confusion. Which one to use? When? Why? How?

Here you might excitedly point out: LINQ!? Where I will immediately ask:Why LINQ? Here is why: Exactly because of the need to remove the paradigm shift which in turn required programming effort of constant transformations to/from SQL and C#. LINQ is a kind-of-a (non existent ) C#SQL facade to be used by C#. OLE DB for OLAP (MDX) is exactly the same. Yet another query language. Yet another new complicated thing to learn.

My proposal

To develop DBJSON^tm protocol and service facade, to each end every data based technology: WMI, AD, OLAP, RDBMS, WBEM, etc … Enable people to use DBJSON^tm with AJAX to develop very fast HTML based app’s. Imagine this JavaScript snippet:

// connection string is actually URI to the REST end-point
var db = DBJSON.open(” http://myserver.com/dbjson_enabled_db” ) ;
// from part is separated from querying
var from = “products” ;
// querying language is superset of XPATH
var where = “[@price > 1000]“ ;
// every query is asynchronous
db.select( on_select, from, where) ; // oql select statement as a string
// NOTE: rows are transformed into JSON objects, and columns are their properties.
// NOTE: there is no ‘what’ part in the query. All the columns are carried over, but not all the rows
// this is because actual query might be executed localy on the DBJSON set returned.
// Here we receive the DBJSON result
var dbjson_result = null ;
function on_select ( dbjson_arrival )
{
if ( dbjson_arrival.error != null )
{
// DBJSON error sub-object contains full error description, etc
} else
dbjson_result = dbjson_arrival ;
}
// now we can use the resulting DBJSON set of objects
// as already defined by initial WHERE XPATH statement
apply_to_datagrid( dbjson_result.select() ) ;
// or we can *locally* defined another select statement
apply_to_datagrid( dbjson_result.select(“[@name = 'ALFKI]“) ) ;
// or we can do update
dbjson_result.update(“[@name = 'ALFKI]“, “DBJ”, on_update ) ;
// or deletion
dbjson_result.delete(“[@name = 'ALFKI]“, on_delete ) ;
// delete the whole
dbjson_result.delete( on_delete ) ;
// or insertion
dbjson_result.append( { name: ‘DBJ’ }, on_append ) ;
// above will try to append a row where only column ‘name’ will have value

// connection string is actually URI to the REST end-point

var db = DBJSON.open(” http://myserver.com/dbjson_enabled_db” ) ;

// from part is separated from querying

var from = “products” ;

// querying language is superset of XPATH

var where = “[@price > 1000]“ ;

// every query is asynchronous

db.select( on_select, from, where) ; // oql select statement as a string

// NOTE: rows are transformed into JSON objects, and columns are their properties.

// NOTE: there is no ‘what’ part in the query. All the columns are carried over, but not all the rows

// this is because actual query might be executed localy on the DBJSON set returned.

// Here we receive the DBJSON result

var dbjson_result = null ;

function on_select ( dbjson_arrival )

{

if ( dbjson_arrival.error != null )

{

// DBJSON error sub-object contains full error description, etc

} else

dbjson_result = dbjson_arrival ;

}

// now we can use the resulting DBJSON set of objects

// as already defined by initial WHERE XPATH statement

apply_to_datagrid( dbjson_result.select() ) ;

// or we can *locally* defined another select statement

apply_to_datagrid( dbjson_result.select(“[@name = 'ALFKI]“) ) ;

// or we can do update

dbjson_result.update(“[@name = 'ALFKI]“, “DBJ”, on_update ) ;

// or deletion

dbjson_result.delete(“[@name = 'ALFKI]“, on_delete ) ;

// delete the whole

dbjson_result.delete( on_delete ) ;

// or insertion

dbjson_result.append( { name: ‘DBJ’ }, on_append ) ;

// above will try to append a row where only column ‘name’ will have value

Above is based on an imaginary JavaScript library object called DBJSON. Which communicates to the DBJSON Mediator, through the DBJSON Protocol.

SQL and beyond

[This is already implemented in different way in PostgreSQL and few other RDBMS’s]

DBJSON Mediator, and DBJSON communication protocol are actually not necesary to unleash DBJSON. How? The core “trick” is to make DBJSON part of the database itself and thus make it ubiquitous. There is one very simple “thing” that can be done to SQL, by database makers like Microsoft, Oracle, etc. I would like to see SQL extended with a “DBJSON” formating directive, so instead of saying :

 SELECT * FROM persons AS XML -- standard in TSQL

1	SELECT * FROM persons AS XML -- standard in TSQL

One could say:

SELECT * FROM persons AS JSON -- return DBJSON formated reply

1	SELECT * FROM persons AS JSON -- return DBJSON formated reply

And receive back DBJSON result. Having this used inside the javascript based app’s would be a huge productivity boost. The whole SQL+XML+XSL paradigm transformation issue gone! This solution will still require the SQL to talk to the DB, but not the usual transformations to process the result. Here is the resulting web app, transformation chain.

`DB+DBJSON`	`SQL (to DB)` `(from DB) DBJSON`	`DBJSONService`	`DBJSON (to Svc)` `(from Svc) DBJSON`	`AJAX`	`Browser`	`HTML`
`Server`		`Server`		`Browser`

DBJSON Service here is the web service transforming DBJSON messages to the SQL for the DBJSON enabled database, which returns DBJSON messages, through the “magic” of the “AS DBJSON” formatting directive. As a result, browser side has no knowledge of SQL whatsoever. And Service has to do the transformation only for the traffic to the DB, not from it.

JSONHttpRequest

Less ambitious (maybe) but equally useful project would be a universal browser DOM object that acts as a DBJSON mediator between JavaScript, in the page or server side component, and on-line Service of choice.

/**/
var dbjsonX = JSONHttpRequest()  ;
dbjsonX.connect(”… connection string URI …. “, on_connect) ;
dbjsonX.select(”* from category where name = ‘ALFKI’”, on_result) ;
/*
 you know the drill ... async interface etc ...
 now use dbjson_rezult as any other JSON object …
*/

/**/

var dbjsonX = JSONHttpRequest() ;

dbjsonX.connect(”… connection string URI …. “, on_connect) ;

dbjsonX.select(”* from category where name = ‘ALFKI’”, on_result) ;

you know the drill ... async interface etc ...

now use dbjson_rezult as any other JSON object …

JSONHttpRequest , could be built to mirror the model of the XMLHttpRequest. In essence and in accordance with the current specification , the whole XHR object description can be taken over and only one property has to be changed, to arrive to instant JSONHttpRequest spec :

Instead of : readonly attribute Document responseXML ;

It should be: readonly attribute DOMString responseJSON ;

Why stop here? Ditch DOM, make JSON browser

We are here and now meeting with very big issues like: why is DOM Document based on XML, not JSON ? Or, why we do not have MIME type for JSON¹. Or JSON to replace HTML ? Maybe, but currently I am not sure JSON could replace HTML? This would require a browser which would render pages from JSON code like this (wild) example :

/*helloworld.json */
{ html : {
header : { title : "JSON", script : { source: "dbjquery.js" } } ,
body : { p : { id: "paragraph", text : "Hello World!"} , 
        onload : function () 
{ alert("Indeed...") } }
}
}

/*helloworld.json */

{ html : {

header : { title : "JSON", script : { source: "dbjquery.js" } } ,

body : { p : { id: "paragraph", text : "Hello World!"} ,

onload : function ()

{ alert("Indeed...") } }

}

This little snippet in essence peeks into the future (one of the possible futures) where HTML, DOM and XML are made redundant. And replaced with JSON*DM (Json Document Model) .

Pages: 1 2 3 4 5 6

6 Replies to “DBJSON: SELECT * FROM persons AS JSON”

Ian Smith says:
2008-11-05 at 17:32
I Like the idea very much. I think the bigger problem in the short term is that web browsers aren’t secure so you couldn’t use DBJSON to expose your database directly or else anyone could run any query they wanted to by hacking the JavaScript. It could work out of the box with Aptana’s Jaxer, with Rhino, or maybe even with Adobe AIR where the JavaScript runtime is more in the developers control. I’ll be interested to see how this goes.
Dusan says:
2008-11-05 at 18:18
Thanks Ian,
Text currently (05Nov08) on the blog contains a good idea but is shamefully chaotic, I know.
The comment you made on the security is of course valid. Answering it properly opens a “can of worms” which is called : Server Side JavaScript ;o)
I certainly would not allow my team to send SQL select statements from a browser page back to the RDBMS.
But server side components written with JavaScript are an entirely different matter. Also.
I can easily envisage an architecture where client-to-server selections are totally removed. And client-to-client_data selections are totally ok. Like TAFFY does.
Interesting near future development are client side DB-s, full blown but lightweight , like SQL CE 3.5 SP1, etc … This is where client app talks to the local DB which is transparently synchronized with separate infrastructure with a DB back end, over the wire. I can immediately (today) write AJAX app which will run inside HTA pages and talk to the local SQLSVR CE3.5. Only DBJSON mediator is missing ;o)
PS: DBJSON can have a secondary but still usefull role here: as an security broker/mediator.
Regards: Dusan
1. DBJ says:
  2016-02-29 at 11:41
  Zoom to March 2016: Who would think that NODE.JS would look so uncertain and distant, way back in 2008?
  But ok, I was not very far of the mark with “Server Side JavaScript” :)
Ian Smith says:
2008-11-05 at 18:29
Very true. In fact I think Server Side JavaScript with tools like this has a really bright future and makes a lot of sense. Looking forward to seeing how this goes.
shadowbq says:
2009-06-15 at 00:50
couchdb?
Dusan says:
2009-06-15 at 17:35
@shadowbq : Thanks for your somewhat terse comment ;o)
JavaScript in “CouchDB” is partial implementation of the “MapReduce” concept and framework.
( http://en.wikipedia.org/wiki/MapReduce )
And on “MapReduce”, I am affraid I largely agree with David DeWitt and Michael Stonebraker, pioneering experts in parallel databases and shared nothing architectures. In short: thumbs down.
I am talking here about ubiquitous add on to current SQL syntaxes inside RDBMS-es, which would be a key to people adopting and understanding JSON.

Comments are closed.