A Challenger of SQLite
Many small and micro applications also need some data processing & computing capability, but the framework becomes too heavy if we integrate a database in them. In view of this, the small and lightweight SQLite has become a good choice and been widely used.
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, which is offered in https://github.com/SPLWare/esProc.
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.
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery("=T(\"Orders.csv\").select(Amount>1000 && like(Client,\"*s*\")
It is much simpler to write a relatively complicated computing goal in SPL than in 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:
| A | B | |
| 1 | =sales.sort(amount:-1) | / Sort amounts in descending order |
| 2 | =A1.cumulate(amount) | / Get a sequence of cumulative amounts |
| 3 | =A2.m(-1)/2 | / Get the last cumulative amount, which is the total |
| 4 | =A2.pselect(~>=A3) | / Get the position where the cumulative amount exceeds half of the total |
| 5 | =A1(to(A4)) | / Get values according to positions |
Unlike most of the programming languages that write code as text, SPL code is written in a grid. Find more information HERE.
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:
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call queryOrders()}");
statement.execute();
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.
T("Orders.csv").select(Amount>2000 && Amount<=3000)
Orders=json(httpfile("http://127.0.0.1:6868/api/orders").read())
db=connect("mysql")
db.query("select * from salesR where SellerID=?",10)
...
To access the external data, just read it directly without the need to create a table. It is very convenient. In addition, these files and data sources are writable in SPL. They can be used for data persistence so that the written data can be accessed by other applications.
file("Orders.csv").export@t(A2)
file("Orders.xlsx").xlsexport@t(A2)
db.update(NewTable:OldTable)
SPL also offers its own binary files, which enable higher read/write performance..
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.
