198 Lotus blogs updated hourly. Who will post next? Home | Blogs | Search | About 
 
Latest 7 Posts
Universal Data Replication
Wed, Oct 18th 2017 182
The power of Domain Specific Languages
Thu, Jun 15th 2017 3
Darwino as the IBM Domino reporting graal
Sat, Mar 11th 2017 6
Schemaless GraphQL
Fri, Mar 3rd 2017 7
Get your apps integrated with IBM Connections, cloud and on-premises!
Fri, Feb 17th 2017 7
When SQL meets NoSQL, you get the best of both worlds!
Thu, Jan 26th 2017 6
ReactJS or AngularJS? What about something else?
Wed, Jan 25th 2017 4
Top 10
Universal Data Replication
Wed, Oct 18th 2017 182
Why AngularJS sounds familiar to XPages developers...
Tue, Jan 3rd 2017 11
Darwino, and Java applications, on Microsoft Azure!
Wed, Jul 20th 2016 8
When your Domino app meet the cloud, IBM Connections or Microsoft Azure!
Thu, Sep 22nd 2016 8
IBM Bluemix - Eclipse Developer Exprience
Mon, May 16th 2016 7
Get your apps integrated with IBM Connections, cloud and on-premises!
Fri, Feb 17th 2017 7
Schemaless GraphQL
Fri, Mar 3rd 2017 7
The very first Darwino workshop in Germany was a success!
Tue, Dec 6th 2016 6
The king is dead, long live to the new iOS Java king!
Thu, Dec 22nd 2016 6
When SQL meets NoSQL, you get the best of both worlds!
Thu, Jan 26th 2017 6


When SQL meets NoSQL, you get the best of both worlds!
Twitter Google+ Facebook LinkedIn Addthis Email Gmail Flipboard Reddit Tumblr WhatsApp StumbleUpon Yammer Evernote Delicious
   

At the heart of Darwino is an advanced, portable JSON document store, implemented on top of any relational database. I'm often being asked the following question "why did you implement that on top of an RDBMS?". Behind the scene, the real question is: "why are you not using MongoDB or another nosql database?"
Well, I'm generally answering it with multiple arguments:
  • It leverages all the RDBMS well known capabilities: transactions, data integrity, security, backups, performance, reporting, analytics...
  • Nowadays, RDBMS handle JSON natively, thus providing high performance access to any data in this format. 
  • Portability: it deploys on existing infrastructure, whenever it is cloud or on premises. Run your app on IBM Bluemix on top of DB2, or MS Azure on top of SQL Server... And on-premises, I don't know any organization that does not already have an RDBMS validated by the IT department. 
But there is now another big reason: queries! This is another step in leveraging the native JSON support available in all major RDBMS.

From the beginning, Darwino comes with a JSON based, MongoDB like, query language. It fully abstracts the relational database details, by converting the JSON query into SQL for the target database. It hides the relational model and behaves exactly the same on every single relational database. Plus it adds IBM Domino like capabilities, including category records, response documents, full text search... Of course, it honors the document based security. So it is very powerful. You can see it in action here.

But it also has its own set of limitations. In particular, because it hides the relational model, it cannot take advantage of it. I'm thinking about joins, subqueries, unions... Remember, it is modeled over MongoDB and IBM Domino, which do not support these capabilities.

That's the reason why are introducing JSQL, which stands for JSON SQL. It complements the existing query language. In a nutshell, this is SQL targeting JSON document collections. Think a document collection as relational table, where each document is a row. The columns become JSON paths on the documents.

The beauty of implementing a NoSQL store on top of a RDBMS, is that you don't have to implement the SQL query engine yourself, but you can rely on the well proven underlying database. I don't know any NoSQL database that is just approaching what a mature SQL database can do in term of query. With behind it, decades of research and query optimization techniques that will be hard to re-implement! And finally, if a vendor does not give you entire satisfaction, then you can move to the next one. Frankly, isn't that more trustable than any proprietary NoSQL database, even open source?

Ok, back to JSQL. Let's start with a simple example. Suppose that you have a collection of JSON documents named 'Pinball'. Each pinball document has a few fields, like these:
  { name: 'Revenge of Mars', manufacturer: 'Bally', ....}

A JSQL query to list all the pinball machines in the database would be:
 SELECT $.manufacturer as manufacturer, $.name as name 
FROM pinball
ORDER BY $.manufacturer, $.name

Easy, isn't it? The syntax '$.a' is actually a JSON path that extracts the field 'a' from the JSON document. It can obviously be more complex, like '$.x.y.z', to extract hierarchical data.
Note that you can also get the whole JSON document with the simple '$' JSON Path:
  SELECT $ as doc from pinball

Under the hood, Darwino parses the original JSQL query and generates the final query for the target RDBMS. For example, here is how the query above is converted to Postgresql:
  WITH TB1 AS 
    (SELECT *
    FROM playground_DOC
    WHERE STOREID='pinball'
            AND INSTID='')
  SELECT
    jsonb_extract_path_text(JSON,
'manufacturer')::text AS manufacturer,
    jsonb_extract_path_text(JSON,'name')::text AS name
  FROM TB1
  ORDER BY
    jsonb_extract_path_text(JSON,'manufacturer')::text,
    jsonb_extract_path_text(JSON,'name')::text

The JSQL queries can be as complex as the database supports: it includes clauses like WHERE, ORDER BY, GROUP BY/HAVING, any JOIN, subqueries, functions, aggregation, UNION... Again, anything supported by the underlying database can be used. Also, during the conversion step, Darwino hides as much as possible the SQL differences between the database vendors. It even works on mobile devices on top of SQLite!

Here is, another example: a query joining 3 document collections: pinballs, owners and a relation (owns) between the pinballs and the owners:
SELECT O.$.firstName firstname, 
  O.$.lastName lastname,
  P.$.brand brand,
  P.$.name name
FROM owners O
LEFT OUTER JOIN owns R ON R.$.owner=O._unid
LEFT OUTER JOIN pinball P ON R.$.ipdb=P._unid
ORDER BY firstname, lastname
(The field syntax _xyz allows access to the document meta-data, stored outside of the JSON document. It includes the document unid, the creation date...)

Another one? What about a subquery to find the most expensive pinball:
  SELECT P.$.name name, P.$.manufacturer manufacturer,        P."$.value"::number "value"
FROM pinball P,
(SELECT MAX("$.value"::number) val FROM pinball) MT
WHERE P."$.value"::number=MT.val
Live Example
(Because there is no JSON schema, any JSON path is assumed to be a string by default. Darwino supports the :: cast operator to specify other data types, like ::number)

It, of course, preserves the document based security (a.k.a. readers/editors). Look at this example, and you'll see that the generated SQL is decorated with the proper security condition on the reader fields.

Thinking even further, we can provide a JDBC driver that will allow any JDBC client, like report engines, to connect to the database, while preserving the whole security model! For the record, I already created such a remote JDBC driver for DomSQL, so it would be easy reuse this piece of code.

This feature is still under development but can be previewed in the Darwino 2.0 code stream, and live from the Playground. Hope you guys like it, and see the value of having an RDBMS behind the scene! Any feedback is more than welcome.

BTW, I'm a fan of Pinball machines, and so are my friends at WebGate. :-) I can show you my pinball database, augmented with AI, at Connect 2017. See you at booth #630 in the showcase.




---------------------
http://blog.riand.com/2017/01/when-sql-meets-nosql-you-get-best-of.html
Jan 26, 2017
7 hits



Recent Blog Posts
182
Universal Data Replication
Wed, Oct 18th 2017 7:21p   Philippe Riand
One of the Darwino piece of code that I'm the most proud of is the replication engine. If a majority of our customers see it as a Domino to JSON replication engine, it goes far beyond that. In reality, it can replicate between virtually any data sources. It is a true, two ways, multi-point replication engine, borrowing some ideas from IBM Domino but going beyond its venerable ancestor in multiple places. The architectureThe main idea is that any data set can be represented as a set decorated J
3
The power of Domain Specific Languages
Thu, Jun 15th 2017 11:03p   Philippe Riand
We are all used to configuration files, whenever they are XML, JSON or simply text based. If this is ok for simple configurations, but it falls short when the complexity increases. The biggest issue is that they cannot provide more than what they are designed for, basically static values. Let's suppose, for example, that we have an XML configuration file exposing a property like: trueOk, simple enough. This property can be true or false. But now what if I want a more dynamic value, like true
6
Darwino as the IBM Domino reporting graal
Sat, Mar 11th 2017 8:31p   Philippe Riand
Reports, dashboards, data analytics... have been the conundrum of IBM Notes/Domino since the beginning. Its proprietary data structure, the absence of standard APIs and its deficient query capability make it very difficult. This has been ranked as one of the top need for any for business applications. I know several business partners who created great Domino solutions but struggling with poor reporting capabilities. Of course some attempts were made to fix it: LEI, DB2NSF,.. all incomplete and
7
Schemaless GraphQL
Fri, Mar 3rd 2017 11:56p   Philippe Riand
FaceBook officially introduced a few months ago a new technology called GraphQL. Well, rather than really being new, FaceBook made public and open source their internal graph query engine. It starts to be widely used for exposing APIs. For example, IBM Watson Worskpace makes use of it. I also heard that IBM Connections will also use it. In a nutshell, it allows powerful, tailored queries including navigation between the data sources, in a single query. As a result, it minimizes the number of re
7
Get your apps integrated with IBM Connections, cloud and on-premises!
Fri, Feb 17th 2017 4:51a   Philippe Riand
I've been using this blog to share some of the techniques we use in ProjExec to get tightly integrated with the Connections platform. I got a lot of feedback from developers who wanted to know more, so I'm moving a step further: Jesse Gallagher and I will describe these techniques in a breakout session @Connect 2017! DEV-1430 : IBM Connections Integration: Exploring the Long List of Options Program : Development, Design and Tools Topic : Enterprise collaboration Session Type : Breakout Sessio
7
When SQL meets NoSQL, you get the best of both worlds!
Thu, Jan 26th 2017 11:46p   Philippe Riand
At the heart of Darwino is an advanced, portable JSON document store, implemented on top of any relational database. I'm often being asked the following question "why did you implement that on top of an RDBMS?". Behind the scene, the real question is: "why are you not using MongoDB or another nosql database?" Well, I'm generally answering it with multiple arguments:It leverages all the RDBMS well known capabilities: transactions, data integrity, security, backups, performance, reporting, a
4
ReactJS or AngularJS? What about something else?
Wed, Jan 25th 2017 9:25p   Philippe Riand
So far, ProjExec has been a really good citizen in the IBM/ICS world as we tried to reuse the core Connections stack as much as can (Dojo, OneUI, ...). But these technologies start to age while the browser technologies evolved a lot in the past years: what required a whole bunch of JavaScript using Dojo/JQuery can now be squeezed in a few lines using new libraries! It is time to change gears. We started to look at what technology would better fit our needs. The main requirements are: Make the d
11
Why AngularJS sounds familiar to XPages developers...
Tue, Jan 3rd 2017 5:53p   Philippe Riand
When I started to look at AngularJS a few years ago, I surprisingly found myself quickly comfortable with this technology. One of the reason is that many of its concepts are shared with XPages. Of course, there are fundamental differences, the most obvious being AngularJS a pure client technology while XPages, based on JSF, is a server side one. But still, they share a lot! If you know XPages, your experience understanding AngularJS should be similar to mine. I'm basing my experience on Angul
6
The king is dead, long live to the new iOS Java king!
Thu, Dec 22nd 2016 11:08p   Philippe Riand
When presenting Darwino, I'm often asked the question how do we run Java natively on iOS, or other platforms that are not known to be Java friendly? Well, we started this journey a few years ago, but the landscape of cross platform Java evolved a lot since that. Let me go through the history and explain where we stand now, as a new king has just been proclaimed. If you thing the story is too long, then just go to Multi OS Engine for the answer. Back to the dark age, the most promising solution
6
The very first Darwino workshop in Germany was a success!
Tue, Dec 6th 2016 5:12p   Philippe Riand
It happened in Cologne, Germany, where 8 different companies joined us to better understand Darwino and get their hands dirty with the code. It reminded me the first XPages workshop we did in Westford in 2008, before we launched the technology at Lotusphere. Same spirit, with highly motivated people, ready to enhance their existing Domino applications!We had several IBM champions, OpenNTF board members, and even the CTO of the great Multi-OS-Engine. If the crowd learnt about the technology, we




Created and Maintained by Yancy Lent - About - Planet Lotus Blog - Advertising - Mobile Edition