Beyond ORM and Stored Procedures

Business logic consists of database read & write, structured data computation, and procedural control. The first two parts are what SQL was designed for and thus easy to handle. But SQL lacks procedural control statements (loop and judgement), it cannot achieve the whole business logic on its own. In order to address this issue, database vendors invented a new language based on SQL and procedural control statements. That is stored procedure.

The stored procedure has more complete functionalities to achieve business logics independently

The language combines SQL and procedural control statements. Their cooperation gives the stored procedure complete functionalities to implement the whole business logic. For example:

SELECT SFIEDL INTO SIGNS FROM DTABLE WHERE ID=1;
SI_MAX := SIGNS;
WHILE SIGNS > 0 LOOP
    …
    IF CALCULATED = 0 THEN 
        IF SIGNS = SI_MAX THEN 
            CONDITIONS := CONDITIONS || F_ALI || L_ID || '.' || CURRENT_VALUE;
        ELSE
            CONDITIONS := CONDITIONS || 'AND ' || F_ALI || L_ID || '.' || CURRENT_VALUE;
        END IF; 
    END IF; 
    SIGNS := SIGNS - 1;
END LOOP;

The above code contains SQL query statement, WHILE loop statement and IF…ELSE judgment statement. It covers most of the business logic and is relatively complete. The stored procedure also has a series of advantages like meticulous privilege management and good performance.

Yet the stored procedure has architectural defects

While the combination of SQL and procedural statements let stored procedures have rather complete functionalities, it also brings serious architectural flaws.

The stored procedure language is tightly bound with database vendors. Each type of database offers its own stored procedure syntax that applies only to computations handled by the corresponding database, making code hard to migrate between different database products. The stored procedure, in theory, serves the front-end application, but it is physically stored in the database. Its maintenance involves both, causing tight coupling between application and database. As stored procedures are written in databases, code versions cannot be managed through a third-party tool and it is hard to perform teamwork. Managing stored procedures is high-cost because it requires the privilege of DBA. Writing stored procedures involves table creation, modification, read and write, which poses a threat to data security. Other disadvantages of stored procedures include style inconsistency and difficult debugging.

These architectural defects make stored procedures fall short of the contemporary programming principles. The new generation of applications generally uses an outside-database high-level language, like JAVA.NET, to implement business logics through the cooperation of SQL and their procedural control statements. But the high-level language’s native class library is far different from SQL syntax style and data structure. The cooperation is bumpy and type conversion is complicated, slowing down development process and raising cost for learning and administration. It is in this context that the ORM technique was invented.

ORM uses uniform syntax style and data structure to implement business logics

ORM is a technique that maps the structure data (table/record) to an object in high-level language. Familiar ORM-based products are Hibernate, QueryDSL and JOOQ. To achieve the complete business logic under the Java system, ORM (take Hibernate as an example) reads data from and writes data to the database using entity classes, controls computing procedure through Java for/while/if statements, and computes structured data in HQL or Java. HQL is an object-oriented query language that has much better computing ability than Java and is database-independent. To perform a fuzzy query, for instance:

String hql="select orderId,client,amount from OrdersEntity where (amount between  2000 and 3000) and client  like '%s%'";
Query query = session.createQuery(hql );

ORM inherits all Java advantages, including ease of migration, loose coupling, teamwork convenience, high data protection level and ease of debug. More importantly, ORM uses uniform syntax style and data structure to implement business logics under the Java system. This effectively reduces cost of learning and code management and speeds up development.

ORM has inadequate computing ability

ORM is excellent in database read/write and procedural control, but it is not good at structured data computations. Many logics cannot be described and many functions are not supported in HQL. It is difficult to express complex business logics with ORM.

The ORM HQL has better computing ability than Java but it is far outperformed by SQL. There are many computing logics that HQL cannot describe, such as FROM subquery and computations involving window functions and row numbers. Below is an example:

select orderId, m from (select orderId, month(orderDate) m from OrdersEntity) t1

Many commonly used date functions and string functions are not supported in HQL. Relevant computations include getting a date before or after a specified date, finding the ordinal number of the specified date in the year, string replacement, left truncation and getting corresponding ASCII code. The following example gets a date after the specified date with the date increase function:

select date_add(OrderDate,INTERVAL 3 DAY) from OrdersEntity

There are two methods to achieve the same functionality. Introducing a SQL dialect or hardcoding it in Java. The former disrupts ORM’s consistent syntax style, making the technique not ORM any longer. The latter maintains the consistency of syntax style but produces a huge amount of code. In order to make up for Java’s weakness in computing structured data, Hibernate released Criteria and Java rolled out Stream. But both class libraries have fallen short of expectations with their computing abilities no better than HQL.

SPL has all-around capabilities and can achieve business logics with uniform syntax style and data structure

Both stored procedures and ORM have their pros and cons. If only there were a technique that combines their merits, having ORM’s architectural advantages and stored procedures’ computing strength at the same time.

esProc SPL is what we wish for.

It is a JVM-based, open-source structured data computing language. It boasts specialized database read/write ability, great capability of computing structured data and procedural control. It can achieve business logics independently using the uniform syntax style and data structure.

Database read/write ability

To read records of an external database into internal structured data objects, ORM uses primary-based query and HQL query. To do the same thing, SPL executes SQL using its query function.

Get one record:

=r=db.query("select * from sales where orderid=?",201)

Get a table sequence (SPL set of records):

=T=db.query("select * from salesR where SellerID=?",10)

To persist internal structured data objects into the database, ORM uses save (add a new entity), update (modify an entity) and delete (delete an entity). SPL uses update functions to performs these o

For instance, through adding new records, modifying and deleting records, the original table sequence T becomes NT and we want to save the new table sequence to the database through data persistence:

=db.update(NT:T,sales;ORDERID)

Capability to compute structured data

Business logics revolve around structured data objects. Stored procedures, ORM and SPL have built-in, specialized structured data objects and each offers their own structured data computation functions. The SPL structured data objects are record and table sequences.

Get values of a specific field in a record: =r.AMOUNT*0.05

Modify values of a specific field in a record: =r.AMOUNT= T.AMOUNT*1.05

Get a column from a table sequence: T.(AMOUNT)

Append records to a table sequence:

T.insert(0,31,"APPL",10,2400.4)

Based on table sequences, SPL provides a large number of SQL-style functions:

Filter:

T.select(Amount>1000 && Amount<=3000 && like(Client,"*bro*"))

Sort:

T.sort(-Client,Amount)

Distinct:

T.id(Client)

Aggregate:

T.max(Amount)

Group, aggregate and filter:

T.groups(year(OrderDate),Client; avg(Amount):amt).select(amt>2000)

Join:

join(Orders:o,SellerId ; Employees:e,EId).groups(e.Dept; sum(o.Amount))

Calculate intersection:

T1.id(Client) ^ T2.id(Client)

Get topN:

T.top(-3;Amount)

Get intra-group topN:

T.groups(Client;top(3,Amount))

In addition, SPL supplies string functions, date functions, mathematical functions and many other types of functions to meet computing requirements of business logics and make up for ORM’s insufficient computing ability.

Procedural control ability

One difficulty in implementing business logics is the complex procedural control. Java has for/while/if statements, stored procedures have loop/if statement, and SPL offers all-around procedural control abilities. Below is SPL branch judgement statement:

    A   B
2   … 
3   if T.AMOUNT>10000   =T.BONUS=T.AMOUNT*0.05
4   else if T.AMOUNT>=5000 && T.AMOUNT<10000    =T.BONUS=T.AMOUNT*0.03
5   else if T.AMOUNT>=2000 && T.AMOUNT<5000 =T.BONUS=T.AMOUNT*0.02

SPL loop statement:

    A   B
1   =db=connect("db")   
2   =T=db.query@x("select * from sales where SellerID=? order by OrderDate",9)
3   for T   =A3.BONUS=A3.BONUS+A3.AMOUNT*0.01
4   
=A3.CLIENT=CONCAT(LEFT(A3.CLIENT,4), "co.,ltd.")
5   
　…

SPL can also use the break keyword to exit (terminate) the current loop body, or the next keyword to skip (ignore) the current round of loop. Here we just skip the topic.

SPL achieves optimal framework

SPL provides JDBC driver, is interpreted execution, and supports outside-database computations and code migration, completely eliminating the architectural defects stored procedures have.

JDBC driver and loose coupling

SPL provides JDBC driver to be seamlessly integrated in Java code. To save the previous SPL code as a script file and invoke the file name in Java in the way of calling a stored procedure, for instance:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call getClient()");

Saving SPL code outside the Java application and invoking it by the corresponding file name make the code independent of both the database and the Java application, separating business logic from the front-end code and creating more loose coupling than ORM.

Interpreted execution and hot swap

It is common that business logics are many, complex and variable. A comprehensive and solid system framework should be able to deal with the volatile business logics. The essence of ORM is Java code, so an ORM-based language needs to be compiled before execution. Generally, this requires a system shutdown and the process for handling changeable business logic is complicated. SPL is a Java-based interpreted language that executes code without compilation. The modification of an SPL script takes effects in real-time. The language also supports non-shutdown hot-swap, making it suitable for handling variable business logics.

Outside-database computing ability

Using stored procedures to compute within the database result in difficult teamwork, high-cost management and potential data risks. SPL computes data outside database. Its code can be managed through a third-party tool, and teamwork is convenient with SPL. A SPL script can be stored in file directories, which is convenient, flexible and easy to manage. SPL requires the same database privilege as Java does, putting no threats to data security.

Convenient code migration

Stored procedures are tightly bound to corresponding databases. They almost do not have any ease of migration. SPL is completely independent of databases and its code can be conveniently migrated.

According to the previous pieces of code, SPL retrieves data from the database through the reference of data source name. To migrate SPL code between databases, we just need to change the data source configuration information in the configuration file without modifying the SPL code. SPL supports dynamic data sources and thus can switch between different databases through a parameter or macro, making code migration more convenient. The migration becomes even easier as SPL also offers the standard SQL syntax that does not belongs to any specific database. The feature let programmers to convert a standard SQL statement to the mainstream SQL dialect using sqltranslate function. The translated SQL statement is still executed through query function.

SPL has remarkable computational capability

SPL offers rich functions, convenient methods of handling set-oriented and order-based computations, specialized structured data types, and easy to use database read/write methods. These characteristics help SPL overcome ORM’s weaknesses and acquire greater computing capability, letting it much better at simplifying complex business logics.

A rich set of functions

ORM (HQL) does not have enough functions to implement many functionalities, or it resorts to write lengthy Java code. SPL outplays ORM in both the number and functionalities of the functions. It can achieve a functionality directly with much shorter code.

To get the date before/after the specified date with a time function, for instance:

elapse("2020-02-27",5) //Return 2020-03-03

Find the day of the week of a specified date:

day@w("2020-02-27") // Return 5, which means Thursday

Get a date after N workdays of a specified date:

workday(date("2022-01-01"),25) //Return 2022-02-04

String functions:

Check if a string all consists of numbers:

isdigit("12345") //Return true

Get the string before a specified substring:

substr@l("abCDcdef","cd") //Return abCD

Split a string into an array of strings by the vertical bar:

"aa|bb|cc".split("|") //Return ["aa","bb","cc"]

SPL has more functions than stored procedures (SQL), too. It offers functions to get a date before or after a number of years, get the ordinal number of a date in the year, get which quarter the date belongs to, split a string according to a regular expression, get the where or select part of a SQL statement, get the words from a string, split HTML by the specific marker, etc.

The powerful ability to handle set-oriented and order-based computations

To perform set-oriented computations on structured data objects (entity sets), ORM generally uses the loop statement to hardcode them. This is inconvenient and the code is extremely long and complicated. SPL offers concise, easy to understand interpreted Lambda syntax and a rich set of set-oriented functions, much more than ORM and more convenient than SQL.

For instance, batch modify records:

T.run(BONUS+AMOUNT*0.01: AMOUNT, concat(left(CLIENT,4), "co.,ltd."): CLIENT)

Filter:

T.select(Amount>1000 && Amount<=3000)

Filter an ordered table sequence using binary search:

T.select@b(Amount>1000 && Amount<=3000)

Group & aggregate:

T.groups(Client;sum(Amount))

Order-based grouping (put neighboring records having same values for a specific field into one group):

T.groups@b(Client;sum(Amount))

The inter-row, set-oriented computations are usually not easy, like the calculations of link relative ratio and YOY. ORM does not provide optimizations for such computations, so its code for handling them is exceedingly complicated. SQL can achieve the inter-row calculations using a join or a window function, but the code is hard to understand. SPL uses "field[relative position]" to reference another row or a value in it. This considerably simplifies the code. SPL can also automatically handle special cases, such as out-of-range values in an array. To append a computed column rate containing amount growth rate for each order, for instance:

=T.derive(AMOUNT/AMOUNT[-1]-1: rate)

Becoming skilled at using SPL Lambda syntax and set-oriented functions can help you greatly simplify complex set-oriented computations. To find employees whose ages are blow the department average age in each department, for instance:

    A
1   …// Skip the process of generating table sequence Employees
2   =Employees.group(DEPT; (a=~.avg(age(BIRTHDAY)),~.select(age(BIRTHDAY)<a)):YOUNG)
3   =A2.conj(YOUNG)

To count the longest days when a stock rises continuously:

    A
1   …// Skip the process of generating table sequence AAPL
2   =a=0,AAPL.max(a=if(price>price[-1],a+1,0))

More specialized structured data types

The ORM structured data type is entity/List. It is uniform but not specialized enough. There are many commonly used access methods that it does not support, like getting one or multiple columns by field name(s). SPL offers truly specialized and more powerful structured data types – record and table sequence. They support all commonly used access methods.

For instance, to get one column from table sequence T and return a simple set:

T.(AMOUNT)

Get multiple columns and return a set of sets:

T.([CLIENT,AMOUNT])

Get multiple columns and return a new table sequence:

T.new(CLIENT,AMOUNT)

Usually, it is difficult to access rows and columns by sequence numbers. But table sequences are naturally ordered, they are convenient for handling relevant computations. One instance is to get multiple columns by column numbers and return a new table sequence:

T.new(#2,#4)

Get records backwards by sequence numbers:

T.m(-2)

Get records by sequence numbers to generate a table sequence:

T([3,4,5])

Get records within a certain range and return a new table sequence:

T(to(3,5))

Get data by field name and then by record number: T.(AMOUNT)(2), which is equivalent to getting data by record number and then by field name:

T(2).AMOUNT

Besides, SPL offers many advanced functionalities based on table sequences, such as getting TopN, getting values in a zigzag way, and order-based join. Table sequences are also more professional than SQL data tables.

More convenient database read/write method

Batch read/write are common in business logics. To do this, ORM can only loop ArrayList and handle records one by one. The code is irritatingly long and complicated, especially when the batch database write involves newly added, modified and deleted records.

Based on table sequences, SPL provides convenient method to read and write records in batch in an excellently concise way. To batch modify record, for instance:

    A   B
1   … 
2   =T=db.query("select * from salesR where SellerID=?",10) / Batch query table sequence T
3   =NT=T.derive()  / Generate new table sequence NT by copying the original one
4   =NT.field("SELLERID",9) / Batch modify
5   =db.update(NT:T,sales;ORDERID)  / Store data to the database through data persistence

In this piece of code, update function performs batch modification. There isn’t a complicated loop statement. The function is well-designed to be able to handle different types of batch database write in one uniform way. This SPL advantage becomes even more prominent when the batch write involves both modified and deleted records.

    A   B
1   … 
2   =T=db.query("select * from salesR where SellerID=?",10) / Retrieve a batch of records
3   =NT=T.derive()  / Generate a new table sequence by copying the records
4   =NT.delete(NT.select(ORDERID==209 || ORDERID==208)) / Batch delete
5   =NT.field("SELLERID",9) / Batch modify
6   =NT.record([220,"BTCH",9,5200,100,date("2022-01-02"),
221,"BTCH",9,4700,200,date("2022-01-03")])  / Batch append
7   =db.update(NT:T,salesR;ORDERID) / Store data to the database through data persistence

Summary

Stored procedures have architectural defects. ORM offers inadequate computing ability. Both have their merits and demerits, and both cannot implement business logics smoothly and efficiently. SPL is the specialized structured data computing language. Its agile syntax and rich functions make it particularly excel in simplifying complex logics. As an interpreted language, SPL supports hot swap, enables loose coupling, supports outside-database computations and convenient code migration, and ultimately, achieves a more optimal framework. With advantages of both stored procedures and ORM, the language can comprehensively increase development efficiency.

Origin: https://blog.scudata.com/beyond-orm-and-stored-procedures/

SPL source code: https://github.com/SPLWare/esProc