https://github.com/SPLWare/esProc

SQLite also has its inconveniences. It has weak and complicated support for external data files and other data sources; it does not provide stored procedures and needs the main program’s participation in implementing the procedure, leading to frequent data exchanges between it and the main program (because the procedure flow depends on data) and resulting in inefficient and tedious code; it is also hard to code the complicated computations in SQL and the development efficiency is low.

esProc SPL can solve all these problems. esProc is a free, open-source software.

esProc is developed purely in Java. Users just need to import its jars in the Java application for use. The integration is completely seamless.

esProc also supplies standard JDBC driver, through which it can be invoked by the Java main program, like the way to access database. However, the query language esProc uses is SPL, instead of SQL.

For example, we want to find the first n customers whose sum of order amounts takes up at least half of the total and sort them by order amount in descending order. The goal is written in SQL like this:

with A as (select client,amount,row_number() over (order by amount) ranknumber from sales) select client,amount from (select client,amount,sum(amount) over (order by ranknumber) acc from A) where acc>(select sum(amount)/2 from sales) order by amount des It is difficult for SQL to handle the record on the dividing line. We should use a roundabout way: compute the cumulative amounts, sort them in ascending order and find customers whose amounts do not form the second half of the total. Moreover, SQL requires writing the complicated logic in a single statement. Even if a with clause (which acts as the intermediate variable) and window functions are used, a nested query is still required, along with great skills and hard debugging.

SPL boasts a rich collection of set operations. It is easy to perform step-by-step computations in SPL. Users just need to write code according to the natural way of thinking:

SPL itself has a complete set of flow control statements, such as for loop and if branch statement, and supports invocation of the subprogram. This amounts to the stored procedure capability. SPL alone can implement complex business logics, almost without cooperation of the main program. The main program just needs to invoke the SPL code as it calls the stored procedure:

Difference is that SPL scripts are interpreted execution. Any modifications take effect instantly, while changes to the stored procedure require compilation. Particularly, SPL scripts can be placed separately from the main program and modifications of them do not need recompilation and redeployment of the latter, which enables the real-time hot swapping. The benefit does not exist for logics implemented by the cooperation of the main program and SQL in the database.

SPL supports a large variety of data sources – whether they are familiar or unfamiliar – including text files of various formats, Excel files, relational databases, NoSQL databases, HTTP, Kafka, … and JSON/XML data. esProc provides access interface for each of them. Users just need one or two lines of code to read/write over them. ...

Like SQLite, esProc is light weight. Its core jar file has a size of only 15M, and the complete deployment takes up about only 1G. It can run smoothly on Android. The pity is that esProc only offers a Java edition for the time being. It is inconvenient to integrate it in a non-Java application. And it cannot run under IOS that does not have a mature JVM environment.