Introduction
NoSqlOnSql is a strict syntactical superset of SQL; it adds NoSQL document-oriented capabilities to the SQL language. For this, it uses uses JSON-like documents, integrating them into the normal flow of SQL statements. 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, instead of hooking middleware in the processing flow, the NoSQL semantics are handled before any actual query starts. NoSqlOnSql is implemented as a source-to-source transpiler, that is, NoSqlOnSql statements are compiled into regular SQL statements which are executed normally by the database engine. 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, the schema is inferred from the query itself. 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 among database engines 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 used in Microsoft SQL Server.
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.
Additionally, the following considerations have been taken into account in the current implementation:
- 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 (ie: replication) and scalability (ie: sharding).
Syntax
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 the nosql keyword. 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'
After compilation, an execution returns:
id | name | lastname | age | kid0 ---------------------------------- 1 | Homer | Simpson | 34 | Bart 4 | Bart | Simpson | 10 | 5 | Lisa | Simpson | 8 |
Note that JSON and SQL are both typed languages. Whenever it is possible, NoSqlonSql infers the type of a value from the context. However this is not always possible, so type prefixes need to be used ($ string, # integer,....). For example:
nosql update in springfield where $name='Homer' set age is 35 -- Set age to 35 nosql update in springfield where $name='Homer' set age is #(#age+1) -- Increate age by one nosql select ?,$name,$lastname,#age from springfield where $Lastname='Simpson'
This returns:
id | name | lastname | age --------------------------- 1 | Homer | Simpson | 36 4 | Bart | Simpson | 10 5 | Lisa | Simpson | 8
History
NoSqlOnSql uses a write only model. This means that no record never gets modified or deleted. It is a pure Event Store dataset, so it always possible to track the changes and history of every document:
nosql show nested history in springfield where $name='Homer'
Returns
object | id | parent | name | stamp | kind | value | history
------------------------------------------------------------------------------------------
1 | 1 | 0 | #object | 2020-10-13 02:19:53 | #object | |
1 | 9 | 1 | age | 2020-10-13 02:19:53 | int | 34 | Overriden
1 | 32 | 1 | age | 2020-10-13 02:19:53 | int | 35 | Overriden
1 | 33 | 1 | age | 2020-10-13 02:19:53 | int | 36 | Valid
1 | 16 | 1 | children | 2020-10-13 02:19:53 | link | 3 | Valid
1 | 10 | 1 | color | 2020-10-13 02:19:53 | string | green | Valid
1 | 11 | 1 | lastname | 2020-10-13 02:19:53 | string | Simpson | Valid
1 | 13 | 1 | likes | 2020-10-13 02:19:53 | link | 2 | Valid
1 | 7 | 1 | name | 2020-10-13 02:19:53 | string | Homer | Valid
1 | 8 | 1 | nationality | 2020-10-13 02:19:53 | string | US | Valid
1 | 12 | 1 | weight | 2020-10-13 02:19:53 | int | 90 | Valid
2 | 2 | 0 | #array | 2020-10-13 02:19:53 | #array | |
2 | 14 | 2 | [1] | 2020-10-13 02:19:53 | string | football | Valid
2 | 15 | 2 | [2] | 2020-10-13 02:19:53 | string | doughnuts | Valid
3 | 3 | 0 | #array | 2020-10-13 02:19:53 | #array | |
3 | 17 | 3 | [1] | 2020-10-13 02:19:53 | link | 4 | Valid
3 | 22 | 3 | [2] | 2020-10-13 02:19:53 | link | 5 | Valid
4 | 4 | 0 | #object | 2020-10-13 02:19:53 | #object | |
4 | 21 | 4 | age | 2020-10-13 02:19:53 | int | 10 | Valid
4 | 19 | 4 | lastname | 2020-10-13 02:19:53 | string | Simpson | Valid
4 | 18 | 4 | name | 2020-10-13 02:19:53 | string | Bart | Valid
4 | 20 | 4 | weight | 2020-10-13 02:19:53 | int | 38 | Valid
5 | 5 | 0 | #object | 2020-10-13 02:19:53 | #object | |
5 | 25 | 5 | age | 2020-10-13 02:19:53 | int | 8 | Valid
5 | 24 | 5 | lastname | 2020-10-13 02:19:53 | string | Simpson | Valid
5 | 26 | 5 | likes | 2020-10-13 02:19:53 | link | 6 | Valid
5 | 23 | 5 | name | 2020-10-13 02:19:53 | string | Lisa | Valid
6 | 6 | 0 | #array | 2020-10-13 02:19:53 | #array | |
6 | 27 | 6 | [1] | 2020-10-13 02:19:53 | string | music | Valid
Notice that the different values of property age for Homer are shown.
Integration
SQL and NoSqlOnSql statements can be combined. For example, extending the previous example:
drop table if exists COUNTRY
CREATE TABLE COUNTRY(
ID [nvarchar](3) NOT NULL,
NAME [nvarchar](15) NULL
) ON [PRIMARY]
INSERT INTO COUNTRY(id,name) select 'US','United States'
select s.today,s.name,s.lastname,c.name from COUNTRY as c
inner join
( nosql select $name,$lastname,$nationality,getdate() as today
from springfield where $Lastname='Simpson') as s
on s.nationality=c.id
Would return:
today | name | lastname | name ------------------------------------------------------ 2020-10-13 02:08:11 | Homer | Simpson | United States
How to Get NoSqlOnSql
NoSqlOnSql for T-SQL is available as a NuGet package for .Net Framework. You can use this command to get the package.
Install-Package nosqlonsql -Version 1.0.1
Detailed examples can be found at https://github.com/jsegarra1971/nosqlonsql/tree/master/Demo
Final comment
This is a Proof of Concept work, just to showcase that the gap between NoSQL and SQL can be covered by creative thinking, without hooking any additional layer. If any one is interested in moving this further, please let me know.
