Cloud Backend
Backend as a Service and Middleware Integrator with complete authorization management, Frontend SQL Query API and Flexible plugins setup
SqlJson can be used as:
The diagram above illustrates the position of SQLJSON as a hub and the ability to integrate enterprise IT applications.
The orange line describes access by the registered applications on SQLJSON. Firstly, from any kind of device run the applications on which would be connected to public ARDVRO SQLJSON Services through the public internet. Secondly, ARDVRO SQLJSON Services will run the application's components which describe in blue lines. Finally, the application's components will access databases and return the data results. However, ARDVRO SQLJSON has capabilities to access the applications' components database, which is illustrated in dark orange lines, as well as accessing the other 3rd party databases.
The green line describes the standard flow of existing applications access, in which each application has its own opened public services on the internet. And perhaps, each application has its own users itself which is different between applications. The silver line shows our proposed solutions to secure and integrate the enterprise applications through ARDVRO SQLJSON.
ARDVRO SQLJSON also has capabilities to communicate to the legacy applications, either directly to its database or to its applications services. Moreover, ARDVRO SQLJSON also has a user membership structure that allows enterprises organization units to collaborate securely in a single ecosystem. ARDVRO SQLJSON is able to merge the legacy applications user membership into one single enterprise application and enable the Single Sign-On through the enterprise IT environment. However, we need to develop a driver adapter to integrate the user membership, thus we need to analyze the system before it.
The integrations mechanism works through the direct API and managed integration. For more detail about the integrations API click here.
SqlJson is a project code name whereby the research project was undertaken to discover a new software architecture design with a secure, stable and reliable performance end-to-end from the frontend to the database access. Furthermore, the system should be the backbone on which other applications will much depend, especially the authorization functions and standard CRUD operations. However, it does not mean that it will be the core of the whole platform, but it will help to reduce the number of codes.
Although SqlJson has a user interface, however, SqlJson can run without any user interface, since all the functions that are used by the user interface, also available for public consumption. The configuration and setup can be done directly to the server without a user interface. The user interface only tool to help administrators.
By using SqlJson it is not only utilising a software, but it also means that using the sophisticated software architecture which lead to the stability and high performance of the applications.
SQL Query API
Open WebSocket Connection.
const wsdata = {
AppName: "www.yourwebsitename.com",
WebSocketUrl: "yourwebsitename.com",
AppCode: "36 chars guid",
ClientRsaKey: 'generated client rsa key',
ServerRsaKeyMod: 'generated server rsa key mod',
ServerRsaKeyExp: 'generated server rsa key exp',
};
let _connector = new WebSocketClient(cfg.DocumentationWsData);
_connector.Open(onConnectionConfirmedHandler, onUnrequestedDataReceivedHandler, onConnectionClosedHandler);
function onConnectionConfirmedHandler() {
intializeDbContext(connectionInfo, _connector);
}
Initialize DbContext.
function intializeDbContdext(connectionInfo, _connector) {
let dbcontext = new SqlJson({
Connector: _connector,
OnLoad: function (db) {
dbcontext = db;
if (callback != null) {
callback(this);
}
}
});
}
Sample Query Syntax.
dbcontext.tableName.Where("columnName=?", varname).OrderBy("Name DESC, Id").List()
.Send(function(results} {
handler(results);
}
);
dbcontext.tableName.Join("Address, Profile b").Where("b.Name=? and Address.Id=?", varname, variid)
.OrderBy("Name DESC, Id").Limit(0, 10).PagingJsons("b.Name, Address.PostCode, Id")
.Send(function(results} {
handler(results);
}
);
Save.
let data = {
Name: "Customer name",
Address: "Road 999 I",
City : {
Id: 2
Name: "Jakarta",
}
Orders:[
{ SkuId: 1, Qty: 3, Price: 1000},
{ SkuId: 3, Qty: 7, Price: 700},
{ SkuId: 6, Qty: 1, Price: 1200}
]
};
dbcontext.tableName.Save(data).Send(function (result) {
if (callback != null) {
callback(result);
}
});
Upsert, Update, Insert.
let data = {
Name: "Customer name",
Address: "Road 999 I"
};
dbcontext.tableName.Upsert(data).Send(function (result) {
if (callback != null) {
callback(result);
}
});
dbcontext.tableName.Insert(data).Send(function (result) {
if (callback != null) {
callback(result);
}
});
dbcontext.tableName.Update(data).Send(function (result) {
if (callback != null) {
callback(result);
}
});
Delete.
let data = {
Name: "Customer name",
Address: "Road 999 I"
};
//delete recursive
dbcontext.tableName.Delete(data).Send(function (result) {
if (callback != null) {
callback(result);
}
});
let customerId = 9;
dbcontext.tableName.Delete(customerId).Send(function (result) {
if (callback != null) {
callback(result);
}
});
Store Procedure and Function.
let param2 = 90;
let param3 = 800.50;
let pageIndex = 1;
let rows = 100;
dbcontext.sp_theStoreProcedureName.ProcedureJsons("param1", param2, param3, pageIndex, rows).Send(function (jsons) {
if (callback != null) {
let list = [];
if (jsons != null && jsons != "") {
list = JSON.parse(jsons);
}
callback(list);
}
});
dbcontext.sp_theOtherStoreProcedure.Procedure("param1").Send(function (results) {
if (callback != null) {
callback(results);
}
});
dbcontext.fn_theDbFunctionName.Function("param1").Send(function (result) {
if (callback != null) {
callback(result);
}
});
- SqlJson Documentation
- Products Documentation
- GitHub
- Sample SqlJson index.html
- Sample SqlJson Javascript
Fuzzy Logic Query
SQL Fuzzy Query
let criterias = [
{
Field: "IDF",
Operator: ">=",
Value: "HIGH",
Compound: "AND"
},
{
Field: "Persediaan",
Operator: "=",
Value: "Sedikit",
Compound: "AND"
}
];
dbcontext.tableName.Join("Address, Profile b").Where("b.Name=? and Address.Id=?", varname, variid)
.OrderBy("Name DESC, Id").Fuzzy(criterias).List("b.Name, Address.PostCode, Id")
.Send(function(results} {
handler(results);
}
);
Setup Fuzzy Membership
Middleware Integrator
Direct Integration with Http
//http api gateway to bypass CORS blocking
//use ajax xmlhttpreqquest
let httpwebapi = new HttpWebApi({ Connector: _connector });
httpwebapi.Get("https://yourwebsite.com", "https://yourtargetwebsite.com/?id=params1", "token_if_any",
function (result){
//http get result
}
);
let data = {
Name: "customer name",
OrderId: "999abcdef"
};
httpwebapi.Post("https://yourwebsite.com", "https://yourtargetwebsite.com/?id=params1", data, "token_if_any",
function (result){
//http post result
}
);
//use SqlJson HttpApi xmlhttpreqquest
let httpwebapi = new HttpController({ Connector: _connector });
httpwebapi.Get("https://yourtargetwebsite.com/?id=params1", "token_if_any",
function (result){
//http get result
}
);
httpwebapi.Post("https://yourtargetwebsite.com/?id=params1", data, "token_if_any",
function (result){
//http post result
}
);
Managed Integration
The authorization to call the Managed Integration instance can be configre for each workgroup user roles. There 3 type of managed integrations:
- SqlIntegration.
This is another option if you don't have remote access to database to create store procedure. You only need to enter the sql query script. However, please beware that there is no security filter like the Sql Query API. Therefore, makesure that your Sql only query the table with no sensitive information.Authentication = db connection string. //ie. Server=localhost;Uid=ardvro;Pwd=password;Database=dbname;connect timeout=180; Command = Raw SQL Query.//ie. select * from cmsContent where Status=@status - .NET Assembly dll files.
This is another option if you don't have remote access to the server to add more assembly dll files.Command = method name of the object. //ie. CompressString CommandFormat = Fully qualified assembly name. // ie. ardvro.core.lib.compression.GZipCompression, ardvro.core.lib - Http Web API Call.
Similar to the direct http call, however with this option, you can manage the authorization wich user roles can access your external web api.GET without parameter Command = URL to invoke. //ie.https://app.sandbox.midtrans.com/snap/v1/transactions CommandFormat = POST : ["application/json", "application/x-www-form-urlencoded"]. GET: "". CommandResult = JSON: "application/json". Flat string: "". //optional Authentication = URL to get token. AuthenticationFormat = POST : ["application/json", "application/x-www-form-urlencoded"], GET : "" AuthenticationField = json object that contains token string. Leave it empty if flat string.
Managed Integration API
let data = {
Name: "customer name",
OrderId: "999abcdef"
};
let auth = {
username: "username01",
password: "thepassword"
};
let integrationId = 9;
let ctrl = new IntegrationController({ Connector: _connector });
ctrl.Execute(integrationId, data, auth,
function (result){
//http get result
}
);
//send realtime data/message to user
let usertarget = "user01name";
ctrl.Send(usertarget, data,
function (result){
}
);
//broadcast realtime data/message to user
let usertargets = "user01name,user02name,user03name";
ctrl.Broadcast(usertargets, data,
function (result){
}
);
//call a WebApiIntegration
//HTTP GET, no auth,
//Command = 'https://localhost:11190/webapi/file';
let integrationId = 9;
let ctrl = new IntegrationController({ Connector: _connector });
//data field will be the querystring
let data = {
id:0,
name: "custname"
};
ctrl.Execute(integrationId, data, null,
function (result){
//http get result
}
);
let data = 99; //ie https://localhost/webapi/file/data
ctrl.Execute(integrationId, data, null,
function (result){
//http get result
}
);
let data = {
id:0,
name: "custname"
};
let auth = {
username: "username01",
password: "thepassword"
};//will be sent to target with post
ctrl.Execute(integrationId, data, auth,
function (result){
//http get result
}
);
Secure WebSocket
Open WebSocket Connection
const wsdata = {
AppName: "www.yourwebsitename.com",
WebSocketUrl: "yourwebsitename.com",
AppCode: "36 chars guid",
ClientRsaKey: 'generated client rsa key',
ServerRsaKeyMod: 'generated server rsa key mod',
ServerRsaKeyExp: 'generated server rsa key exp',
};
let _connector = new WebSocketClient(cfg.DocumentationWsData);
_connector.Open(onConnectionConfirmedHandler, onUnrequestedDataReceivedHandler, onConnectionClosedHandler);
function onConnectionConfirmedHandler() {
//do something
}
Application Keys
Applications Management
Create Application
This features will automatically create the database and setting all configuration.
Manage Application
Application Database Viewer and API
User Authentication and Authorization
Users Workgroups
Database Object Authorization
Multi-branches Organization Units
Workgroups
Single Sign On
One user account to login on multiple applications. Non-SSO login option also available. Support three login method which can be configure by user: Password, One Time Password (OTP), and Two Factors Authentication (Email OTP + Password).
Login API
let ctrl = new AuthenticationController({ Connector: _connector});
//Login with username and password
ctrl.Login(username, password, isSaveLogin,
function(jwtObjectToken){
callback(jwtObjectToken);
});
//or you can use the raw lower level API
let args = [username, password];
_connector.Submit(args, "ardvro/wf/sqljson/AuthenticationWorkflow/login", function (box)
{
callback(box.Data);
});
//Login from stored token
ctrl.LoginToken(
function(jwtObjectToken){
callback(jwtObjectToken);
}, onErrorHandler);
//or you can use the raw lower level API
let args = [token];
_connector.Submit(args, "ardvro/wf/sqljson/AuthenticationWorkflow/logintoken", function (box)
{
callback(box.Data);
});
//Two factors login
ctrl.TwoFactorsLogin(username, password, otpcode, isSaveLogin,
function(jwtObjectToken){
callback(jwtObjectToken);
});
//or you can use the raw lower level API
let args = [username, password, otpcode];
_connector.Submit(args, "ardvro/wf/sqljson/AuthenticationWorkflow/TwoFactorsLogin", function (box)
{
callback(box.Data);
});
//Request OTP Code to email, securityType = 'Email';
ctrl.OtpRequest(email, securityType, isSaveLogin,
function(jwtObjectToken){
callback(jwtObjectToken);
});
//or you can use the raw lower level API
let args = [email, securityType];
_connector.Submit(args, "ardvro/wf/sqljson/AuthenticationWorkflow/OtpRequest", function (box)
{
callback(box.Data);
});
//OTP Login
ctrl.OtpLogin(otpcode, isSaveLogin,
function(jwtObjectToken){
callback(jwtObjectToken);
});
//or you can use the raw lower level API
_connector.Submit([otpcode], "ardvro/wf/sqljson/AuthenticationWorkflow/OtpLogin", function (box)
{
callback(box.Data);
});
User Management
Send and Receive Email
Send Email
let ctrl = new EmailController({Connector:_connector});
ctrl.SendEmail(to, subject, content,
function(result){
});
//lower level api
let args = [to, subject, content];
cfg.Connector.Submit(args, "ardvro/wf/sqljson/EmailWorkflow/SendEmail", function (box)
{
callback(box.Data);
});
ctrl.SendEmailWithAttachment(to, subject, content, filename, filecontent
function(result){
});
//lower level api
let args = [to, subject, content, filename, filecontent];
cfg.Connector.Submit(args, "ardvro/wf/sqljson/EmailWorkflow/SendEmailWithAttachment", function (box)
{
callback(box.Data);
});
Receive Email
There are schedulers to retrieve emails based on each workgroup IMAP settings. When receiving emails, the scheduler will execute a store procedure on the settings session to insert the email data.
Realtime Push Notifications
Send and Receive Notification
let ctrl = {};
ctrl.Send = function (receiver, message, url, note, type, onResponse)
{
let args = [receiver, message, type, url, note];
cfg.Connector.Submit(args, "ardvro/wf/sqljson/NotificationWorkflow/Send", function (box)
{
onResponse != null ? onResponse(box == null ? null : box.Data) : null;
});
};
ctrl.UpdateStatus = function (id, status, onResponse)
{
let args = [id, status];
cfg.Connector.Submit(args, "ardvro/wf/sqljson/NotificationWorkflow/UpdateStatus", function (box)
{
onResponse != null ? onResponse(box == null ? null : box.Data) : null;
});
};
ctrl.CountUserNotification = function (onResponse)
{
cfg.Connector.Submit([], "ardvro/wf/sqljson/NotificationWorkflow/CountUserNotification", function (box)
{
onResponse != null ? onResponse(box == null ? null : box.Data) : null;
});
};
ctrl.GetUserNotifications = function (pageIndex, pageSize, onResponse)
{
cfg.Connector.Submit([pageIndex, pageSize], "ardvro/wf/sqljson/NotificationWorkflow/GetUserNotifications", function (box)
{
onResponse != null ? onResponse(box == null ? null : box.Data) : null;
});
};
ctrl.Broadcast = function (flex, onResponse)
{
cfg.Connector.Submit(flex, "ardvro/wf/sqljson/NotificationWorkflow/Broadcast", function (box)
{
onResponse != null ? onResponse(box == null ? null : box.Data) : null;
});
};
ctrl.IsOnline = function (username, callback)
{
cfg.Connector.Submit(username, "ardvro/wf/sqljson/NotificationWorkflow/IsOnline", function (box)
{
callback != null ? callback(box == null ? null : box.Data) : null;
});
};
Handle Incoming Notification. Please refer back to Open Websocket Connection.
let _connector = new WebSocketClient(cfg.DocumentationWsData);
_connector.Open(onConnectionConfirmedHandler, onUnrequestedDataReceivedHandler, onConnectionClosedHandler);
function onUnrequestedDataReceivedHandler(box) {
//_notificator
//Do what your system need to do
if (box == null)
{
return;
}
//however, we already have a context to dealt with it in Web Application environment.
//If you use our Website Framework, then you only need to create a Listener, please revert to documentations page for detail.
let listener = _notificationListeners.find(x => box.Function.startsWith(x.Function));
if (listener != null)
{
if (listener.Instance != null && listener.Instance.Notify != null)
{
listener.Instance.Notify(box);
}
}
}
Notification
Files
Upload File
Download File
JSON Data Storage and Notes
Notes features allow application to save any data in json format, or any free text data format including csv, text data, etcetra. And also can be use to save simple notes. It is also inherit the authentication and authorization function.
JSON & Notes API
let ctrl = new NoteController({ Connector: _connector });
ctrl.Save(username, title, notesData,
function(result) {
callback(result);
});
ctrl.Get(title,
function(result) {
callback(result);
});
ctrl.Delete(title,
function(result) {
callback(result);
});
ctrl.GetByUsername(
function(result) {
callback(result);
});
JSON & Notes Manager
User IP Country Information
Session Logs
User Object Data
The Research Project
Research Background
SqlJson is a project code name whereby the research project was undertaken to discover a new software architecture design with a secure, stable and reliable performance end-to-end from the frontend to the database access. Furthermore, the system should be the backbone on which other applications will much depend, especially the authorization functions and standard CRUD operations. However, it does not mean that it will be the core of the whole platform, but it will help to reduce the number of codes. In fact, this project was built on top of the ARDVRO Core Framework. Therefore, we consider that the most famous data access like Entity Framework, Dapper would not be the best fit for this problem.
Entity Framework is the most developer-friendly and stable data access. However, the author experience that this data access is too complex with a lot of built-in functions that are rarely being used. As a result, when we go too far, a lot of error exceptions would be thrown by data access, and not by the SQL operation exceptions. Moreover, the update, save and delete operations we test it is a little bit slow when dealing with a bunch of data.
On the other hand, the Dapper data access is the most faster and simple data-access framework. Nevertheless, it is not developer-friendly, too much code is needed to add to the repository layer, which would lead to the other bugs disaster and impact the stability of the whole system.
Thus, to realize this ambitious project, ARDVRO needs something between Entity Framework and Dapper, which is developer-friendly, stable, simple and has faster performance.
Research Stages
In the first stage, this project was focused on developing a new data access framework that supports multiple databases, support LINQ queries, and the most important thing that this data access framework should be very easy to use by the developer, should support flexible query and should be faster than the other Entity Framework framework. In this stage, we focus on query functions for the DotNet C# only.
In the second stage, this project extends the query ability to the front-end. We need an ability from the front-end to perform the SQL Query to the database through a service layer. The author don't like to use GraphQL, YQL or something like that, because it's too long to write the query code. The author want a very much simpler syntax, ie:
dbcontext.tableName.Join("Address, Profile b").Where("b.Name=? and Address.Id=?", varname, variid)
.OrderBy("Name DESC, Id").Limit(0, 10).PagingJsons("b.Name, Address.PostCode, Id")
.Send(
function(results}
{
handler(results);
}
);
However, the most important thing at this stage is the security concern. Therefore, the author need to add a multi-layer security level on which we can manage the CRUD access right and horizontal authorization for each table and routine of the user's organizations. Another thing is should SQL injection proof, and for security reasons, the author eliminate some functions and features.
At the third stage, the author developed the dynamic connection features to be able to handle multiple applications on multiple connections but with one single user login for the whole application.
Release
Nowadays, our system uses SqlJson as the backbone, and another application as the plugin. Our system can manage multiple applications, multiple connections on one single instance. Therefore, ARDVRO also offer this SqlJson as the backend solution for enterprises and startups, to reduce cost and delivery time.
Why SqlJson
With SQL JSON, you can focus on the front-end, focus on your user, and do not need to worry about the Backend. We will handle your backend, including storage, authentication, authorisation, web server, and another related backend.
In software development, approximately about 40% of coding effort will be used on the Frontend and about 60% on the Backend side.
On the front end, users have unlimited and unique expectations of how to use your application. You need to engage with your user to know their expectations. Furthermore, for that, you need to build an excellent User Interface with complicated UI logic. You also need to build an authentic and detailed user experience, and it will take time. By that, if you have limited resources, we highly recommend focusing on Frontend, and we will handle your Backend. We are experts on the Backend.
In many cases, the Relational Database still plays a significant role, especially in Business Applications. Many things are much easier to do with an SQL database than the NoSQL database, such as reports, complicated business processes, etcetera. The other thing is that in some particular cases, the SQL database tends to perform faster than the NoSQL database.
Suppose a startup company with less than 60 requests per minute (Approximately 100 - 500 users), probably the 'Pay as you go' options provided by other providers. In that case, it might be fit for them. However, if the request is more than 1000 per minute (> 1000 users), the cost of 'pay as you go' options will grow significantly, unfortunately, many requests it does not always mean much profit. Moreover, developing the backend is not always a good option for all cases, mainly for limited resources. The company will need a Backend Developers Team. It will take time to make it stable, and it will need an extra effort to catch up with the competitors. By that, the solution is an On-Premise package. If you need an On-Premise package Contact Us. We will give you a reasonable and competitive price. Before that, you can try our Free package, and if you grow, you can purchase the Lite or Pro package. You still can have your Free package while you are using the Lite or Pro package. Furthermore, by that, we recommend you use the Free package for development purposes.
We are open to discussing your solution architecture design to find out if SqlJson would be the best fit for you or not. We would analyze it objectively, Contact Us to discuss and we will arrange an online meeting for you. Please mention the language, dates and time you want to discuss it online. We can arrange an online meeting in English and Indonesian language.