Introduction to NoSqlOnSql Implementing NOSQL on SQL

Introduction

NoSqlOnSql is based on augmenting a SQL database to support NoSQL features without any kernel changes. In this sense, it is similar to other initiatives like https://www.torodb.com or https://www.microsoft.com/en-us/research/project/json-server-2/.

However, the approach of NoSqlOnSql is different, instead of hooking middleware in the processing flow, the NoSQL semantics are handled before the processing starts. NoSqlOnSql is implemented as a source-to-source transpiler: NoSqlOnSql statements are compiled into regular SQL statements which are executed normally by the database. This compilation can take place at any moment, even during development of the queries before the database is created.

NoSqlOnSql is capable of creating, updating and querying documents without any schema definition. Documents are defined using a notation similar to JSON.

Implementation

SQL implementations are incompatible between vendors. In addition to this, the level of conformance to standards is pretty diverse. This means that, unfortunately each implementation of NoSqlOnSql must target a specific SQL flavour. The current implementation of NoSqlOnSql targets Transact-SQL or T-SQL. This is the language used in Microsoft SQL Server.

Additionally, the following considerations have been taken into account in the current implementation:

• Any valid SQL statement is a valid NoSqlOnSql statement. SQL and NoSqlOnSql statements can be combined. NoSqlOnSql are translated to equivalent SQL statements. For example, SQL VIEWS can be created from NoSqlOnSql SELECT queries.
• Implementation should be as un-intrusive as possible. The number of permanent artefacts created by NoSqlOnSql is reduced to ONE hierarchy TABLE implementing all the documents in a collection.
• When processing complex queries, NoSqlOnSql might need to create temporary objects, which will be discarded after the query is finished. To avoid name clashing, all NoSqlOnSql objects begin with the NOSQL_ prefix.
• NoSqlOnSql follows a write-only paradigm, data is never deleted or updated.

If a value needs to be changed, a new version of the value is created. As a result of this:

• By default, only the latest version of a value is available, but it is possible to visit the history of changes of a document. This makes a NoSqlOnSql storage a perfect media to implement a Blockchain.
• This approach also follows the Event Store paradigm: https://en.wikipedia.org/wiki/Event_store
• At the SQL layer, implementing a collection only requires support for INSERT and SELECT. That is no record on the table gets never modified or deleted. This provides a number of advantages when considering fault tolerance (i.e., replication) and scalability (i.e., sharding).

How Does It Look?

The NoSqlOnSql syntax resembles that of SQL, it follows the same paradigm of statements, data sets and operations on data sets. All NoSqlOnSql statements begin with nosql.

NoSqlOnSql is fully integrated in SQL, both types of statements and their results can be intermixed.

As an example is worth a thousand words, the example below is rather self-explanatory. At this moment, do not pay too much attention to the syntax, just focus on the general feeling and how NoSqlOnSql mixes with SQL.

nosql create springfield override   
nosql insert {
    name: "Homer", 
    nationality:"US",
    age: 34, color:"green",  
    lastname:"Simpson" ,
    weight:90,
    likes:['football','doughnuts'], 
    children: [ 
              {name: "Bart",lastname:"Simpson",weight:38,age:10},
              {name:"Lisa",lastname:"Simpson",age:8,likes:['music']}
    ]
}   into springfield 
 
nosql insert { name: "Moe",lastname:"Szyslak",occupation:"bartender"}  into springfield 
       
nosql select ?,$name,$lastname,#age,$children[0].name as kid0 from springfield _
               where $Lastname='Simpson' 

This returns:

    id | name  | lastname | age | kid0
    ----------------------------------
0    1 | Homer | Simpson  |  34 | Bart
1    4 | Bart  | Simpson  |  10 |
2    5 | Lisa  | Simpson  |   8 |

Installation and Sample

NoSqlOnSql for T-SQL is available as a NuGet package for .NET Framework.

Install-Package nosqlonsql -Version 1.0.1

Using it is straightforward, just create a NoSql object.

var nosql = new NoSql();  

Call Transpile with the nosql query and the type of SQL dialect supported (“mssql” stands for MS SQL Server). A false result means a syntax error:

if (!nosql.Transpile(nosqlquery, "mssql")) throw new Exception(nosql.Error);

Code, comments and host callbacks are generated as a result of transpiling, but we are just interested in Code blocks, which are just regular SQL code. As such, we execute them as usual:

using (var database=new SqlConnection(connectionString))
{
    for (var i = 0; i < nosql.Code.Length; i++)
    {
        if (nosql.Code[i].Type == BlockType.Code)
        {
            using (SqlCommand command = new SqlCommand(nosql.Code[i].Value, database))
            {
               using (var rs = command.ExecuteReader())
                 ...PROCESS AS USUAL !!
            }
        }
     }
}

A more detailed example is available at https://github.com/jsegarra1971/nosqlonsql/tree/master/Demo.

Feedback

NoSqlOnSql is not production ready. Currently, it is just a Proof of Concept (PoC) to showcase that the gap between SQL and NoSQL document based is not a technical gap, but a conceptual one. So it can be covered purely with the existing functionalities, plus some thinking.

The PoC is valid and solid, however the effort required to transform this into an operational product is not something I am willing to step into on my own. So, if you are interested in expanding this, just drop me a line or write a comment.

History

• 12^th October, 2020: Initial version