Thursday, January 26, 2017

When SQL meets NoSQL, you get the best of both worlds!

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.


Wednesday, January 25, 2017

ReactJS or AngularJS? What about something else?

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 developers productive - easy to integrate new developers
  • Have a decent performance when the application grows - ProjExec is large!
  • Can integrate with the existing code, and be used incrementally within ProjExec
  • The tool chain should integrate well with what we currently use (Maven, Eclipse...)
  • Can be debugged easily, with mean full logs
  • Have a eco-system with ready to use libraries (bootstrap, mobile, ...)
We already started to use AngularJS 1.x 2 yrs ago for some new modules. The initial steps were pretty easy, although it started to be more difficult when the complexity increased. And anyway, we know that AngularJS 1 is behind us. We cannot decently bet our future on it.

The natural next move was to use Angular 2, as it fixes most of the issues found in 1.x. I would say this is true, but the learning curve grew significantly. That includes some technology choices (ex: Observable vs Promises), Typescript, the tool chain required to get an application properly packaged, and the heaviness of the resulting code... I spent time playing with it for Darwino (we'll release some examples), but I do not see the ProjExec developers being quickly productive with it. This has been further confirmed by one of my friend, a GDE (Google Developer Expert, the equivalent of IBM Champion): his one week training class is barely sufficient to get the developers comfortable. Also, getting it integrated in a venerable existing application raises some challenges.
Ok, we'll might use it later with Ionic 2 for a dedicated mobile UI, but for now, let's look if something easier can be used on the existing web application.

The next candidate on the list is Facebook's ReactJS. Clearly, there is a buzz around this technology. It has very interesting concepts, it creates high performance applications. But the initial learning curve is even bigger than Angular's, particularly for Java developers. Moreover, it is pretty low level, which provides great flexibility but you then have to choose the proper companion libraries.
We'll keep this in the back burner, let's look is something else better suits our needs.

Finally, we discovered that another library got a lot of traction last year: it is called Vue.js (https://vuejs.org/). After a first look at it, it feels like what I hoped Angular 2 would be! Ok, I bought 2 books from Packt ($5 each), got the source code from Github and started my deep learning. It features exactly what we need:
  • Easy learning curve, particularly when you have some Angular 1 background. Anyway easier than Angular or React
  • You don't need a whole set of tools/CLI to be in place up front. Just include the single JS file and you can get started. It happens that we'll finally use Webpack, but this was not a requirement from the beginning.
  • It fixes many of the problems/inconsistencies found in Angular JS 1.x (bind vs values, separation of concerns, single file components...)
  • It easily integrates within existing applications, regardless of what they already use (Dojo, JQuery, ...), even if they are not designed as SPAs
  • It performs as good as ReactJS. Moreover, if you're a JSX fan, it is available as well
  • It is very lightweight and non intrusive
  • Developer's productivity feels superior to ReactJS
  • A good set of tools/libraries/docs is available https://github.com/vuejs/awesome-vue
  • See how it compares to the other libraries: https://vuejs.org/v2/guide/comparison.html
We decided to go with it. The early feedback I got from the development team is very positive. We'll see in the longer run if it keeps its promises, but so far so good.

I'll be interested to hear anybody else experience. Please feel free to comment.


Tuesday, January 3, 2017

Why AngularJS sounds familiar to XPages developers...

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  AngularJS 1.x, although it is also applies to 2.0. 2.0 is even closer to Java programmers with the use of TypeScript and new concepts familiar to Java developers (classes, ...). But this is a different topic.

Let me dive into the similarities between the 2 frameworks:

1- The Document Object Model (DOM) as the page content
If it is obvious that Angular works on top of a DOM, but XPages is also working on a DOM. If AngularJS leverages the browser HTML DOM, XPages manages an hierarchy of JSF components. Both are hierarchical, with a parent-children relationship, and represent the content of the page. 

2- Contextual data
Angular calls that a scope, which is data assigned to a DOM element and its children. If this concept does not exist in the JSF spec, XPages provides it through "complex properties" like data sources, or even the "context" object that lets developers add data to a component and its children.

3- Scripts and formulas
Obviously Angular uses JavaScript as its scripting language, similarly to XPages, plus a JavaScript based formula language for expression evaluation. XPages also uses the JSF EL for basic expression

4- Data binding
Angular does data binding either through attributes to the DOM elements, or expressions within curly braces {{...}}. Well, XPages does kind of the same thing with value binding expressions, ${...}or #{...}

5- Directives or components
Angular does great job extending your browser HTML with directives. In short, you can create your own tags (or even attributes one can use on existing tags). XPages does the same with custom controls, adding new tags to the XML describing the page.

6- Managed beans
Angular uses services, JSF has managed beans to externalize the business logic outside of the page. In both cases, you access the object by its name, using dependency injection for Angular, or faces-config.xml for XPages.

Of course, there are real differences. One of them is the processing model, as XPages is running well known phases while Angular uses an event based model, with a queue of events. But still, as an XPages developer, you'll feel at home pretty quickly when writing an application.

How about Angular 2.0? Well we are currently experimenting with it and I'll share my findings in a little while.