SPL 报表开发：可跨库移植的报表
背景
与一般事务处理（TP）系统相比，报表中的 SQL 更频繁地使用各种计算函数，计算逻辑也更加复杂，对 SQL 的依赖更强。报表开发中可能应对数据库的变化，不同数据库的 SQL 语法虽大体相似，但在细节上还会存在差异，这要修改报表的 SQL 才能适应不同数据库。这种工作既耗费大量人力又容易引发错误。
完全自动改造 SQL 是不现实的，因为数据库功能的差异使得某些复杂计算很难直接迁移。不过，梳理一下会发现，大部分不兼容问题都是由于 SQL 函数写法不同造成的。特别是日期和字符串相关的函数，业界没有标准，各个数据库各行其是。
比如将字符串 "2020-02-05" 转换成日期，不同数据库有不同的写法。
#ORACLE：
select TO_DATE('2020-02-05', 'YYYY-MM-DD') from USER
#SQL Server：
select CONVERT(varchar(100), '2020-02-05', 23) from USER
#MySQL：
select DATE_FORMAT('2020-02-05','%Y-%m-%d') from USER
用了这类 SQL 的报表在不同的数据库之间切换时，就需要改写 SQL 语句了。
SPL 方案
SPL 针对跨库移植 SQL 的场景提供了 SQL 转换功能，可以将 SPL 定义过的标准 SQL 转换成不同数据库对应的语句，从而实现报表在各个数据库之间移植，更换数据库也不必修改报表。
SPL 提供 sql.sqltranslate(dbtype) 函数来实现 SQL 翻译功能，前面的 sql 是需要翻译的 SQL 语句，参数 dbtype 是数据库类型。函数要在 SPL 的标准 SQL（SPL 称为简单 SQL）中定义过，未定义的不会被翻译。已定义的函数列表和数据库类型可查阅 sqltranslate 函数帮助：https://d.raqsoft.com.cn:6443/esproc/func/sqltranslate.html
SPL 与报表、数据库的关系如下：
SPL 可以嵌入报表应用内使用，报表通过标准 JDBC 与 SPL 连接，报表将标准 SQL 发送给 SPL，SPL 通过转换函数（sqltranslate）将标准 SQL 转换成不同数据库的 SQL 语句执行，最后将执行结果返回给报表完成数据呈现。
我们先在 SPL 的 IDE 内尝试一下，将
SELECT EID, NAME, BIRTHDAY, ADDDAYS(BIRTHDAY,10) DAY10 FROM EMP
转换成不同数据库对应的语法。
可以看到 ADDDAYS 这个函数被翻译成各个数据库不同的语法，实现了 SQL 在不同数据库之间移植。
再看更多的例子。
按季度统计每个季度的订单总金额
SELECT QUARTER(order_date) AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY QUARTER(order_date);
通过 sqltranslate 翻译成不同数据库的语法：
#ORACLE:
SELECT TO_CHAR(order_date, 'Q') AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY TO_CHAR(order_date, 'Q');
#SQLSVR:
SELECT DATEPART(QUARTER, order_date) AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY DATEPART(QUARTER, order_date);
#MYSQL:
SELECT QUARTER(order_date) AS quarter, SUM(total_amount) AS total_sales FROM orders GROUP BY QUARTER(order_date);
#POSTGRES:
SELECT EXTRACT(QUARTER FROM order_date) AS quarter, SUM(total_amount) AS total_sales FROM orders GROUP BY EXTRACT(QUARTER FROM order_date);
找出每年订单数最多的月份及订单总数
SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY YEAR(order_date), order_count DESC;
转换后：
#ORACLE:
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, order_count DESC;
#SQLSVR:
SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, order_count DESC;
#MYSQL:
SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY YEAR(order_date), order_count DESC;
#POSTGRES:
SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, order_count DESC;
计算过去 30 天内的订单总金额和订单数量
SELECT SUM(total_amount) AS total_sales, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= ADDDAYS(NOW(),-30);
转换后：
#ORACLE:
SELECT SUM(total_amount) AS total_sales, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= SYSDATE - 30;
#SQLSVR:
SELECT SUM(total_amount) AS total_sales, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE());
#MYSQL:
SELECT SUM(total_amount) AS total_sales, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE());
#POSTGRES:
SELECT SUM(total_amount) AS total_sales, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= NOW() - INTERVAL '30 DAYS';
还有很多我们就不一一列举了，这些五花八门的差异都可以用 SPL 的 sqltranslate 来转换。
集成进报表应用
SPL 与报表应用集成非常简单，只要将 SPL 的 esproc-bin-xxxx.jar 和 icu4j-60.3.jar 两个 jar 包引入到应用中（一般在 [安装目录]\esProc\lib 下），然后复制 raqsoftConfig.xml（也在上述路径下）到应用的类路径下即可。
raqsoftConfig.xml 是 SPL 的核心配置文件，名称不可更改，后续的数据源和网关配置都需要用到。
从报表应用的视角，SPL 就是一个可以屏蔽底层数据库差异的逻辑数据库，报表通过 JDBC 与 SPL 连接。比如，我们在报表应用中配置的 SPL 数据源连接如下：
<Context>
<Resource name="jdbc/esproc"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username=""
password=" "
driverClassName=" com.esproc.jdbc.InternalDriver "
url=" jdbc:esproc:local://? jobVars=dbType:MYSQL,dbName:MYDATASOURCE "/>
</Context>
这里需要注意 JDBC 中的 URL 指定了两个参数，数据库类型 dbType 是 MYSQL，数据源名称 dbName 是 MYDATASOURCE，两个参数被存放到 SPL 任务变量中。
然后报表查询就可以基于不同数据库进行查询了，报表在不同数据库之间切换完全不需要修改，无缝移植。像前面的例子，标准 SQL 是：
SELECT QUARTER(order_date) AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY QUARTER(order_date);
配置连接参数 dbType 为 ORACLE 时，数据库执行的 SQL 会是：
SELECT TO_CHAR(order_date, 'Q') AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY TO_CHAR(order_date, 'Q');
当连接参数 dbType 为 MYSQL 时，数据库执行的 SQL 会是：
SELECT QUARTER(order_date) AS quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY QUARTER(order_date);
这似乎有点神奇？因为并没有配置具体的数据库信息，也没用到前面提到的 sqltranslate 函数，那是怎么完成的呢？
关键点在于 SPL 的 JDBC 网关。我们要事先配置一个 SPL 脚本，SPL JDBC 中接收到的所有语句（包括 SQL）都会先交给这个脚本处理。也就是说，SQL 的翻译和执行都是在脚本中完成的。
要使用 JDBC 网关，需要在 raqsoftConfig.xml 中的 JDBC 节点配置 SPL 脚本，比如这里配置的 gateway.splx。
<JDBC>
<load>Runtime,Server</load>
<gateway>gateway.splx</gateway>
</JDBC>
网关脚本需要两个参数，一个 sql 参数用于接收 SQL 语句，另一个 args 参数则用于接收 SQL 语句中的参数，也就是 JDBC 给 SQL 传递的参数。
下面有个“最后一个参数是动态参数” 的选项 要勾选，这样才能接收到 SQL 语句的多个参数。
网关脚本很简单，只有几行：
A |
B |
|
1 |
=sql=trim(sql).sqltranslate(dbType) |
|
2 |
=argsN=args.len() |
=("sql"|argsN.("args("/~/")")).concat@c() |
3 |
=connect(dbName) |
return A3.query@x(${B2}) |
A1 使用 sqltranslate 进行 SQL 翻译，这里的 dbType 就是前面 JDBC URL 中设置的参数，在脚本里可以直接使用。
A2 计算参数个数，并在 B2 将参数拼成一个串，比如查询订单明细的语句：
select * from orders where orderdate>=? and orderdate <=?
中有 2 个参数（参数值分别为 20240101 和 20241231），这时 B2 的结果是这样的：
sql,args(1),args(2)
A3 进行数据源连接，其中 dbName 和上面的 dbType 一样，都是在 JDBC URL 中设置的。这里的数据源是在 raqsoftConfig.xml 中配置的，增加 DB 节点配置相应数据源连接信息即可，多个数据源可以依次配置。
<DB name="MYDATASOURCE">
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb?useCursorFetch=true"/>
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="type" value="10"/>
<property name="user" value="root"/>
<property name="password" value="root"/>
<property name="batchSize" value="0"/>
<property name="autoConnect" value="false"/>
<property name="useSchema" value="false"/>
<property name="addTilde" value="false"/>
<property name="caseSentence" value="false"/>
</DB>
B3 使用 db.query 函数进行查询并获得结果返回给报表。其中 @x 代表查询后关闭数据库连接。这里使用了 SPL 宏，宏替换的语句是这样:
A4.query@x(sql,args(1),args(2))
解析后最终会执行：
A4.query@x(“select * from orders where orderdate>=? and orderdate <=?” ,20240101,20241231)
查询出数据并返回给报表。
脚本很简单，以后修改脚本也不需要重启应用，因为 SPL 是解释执行的，支持热切换。
报表有时还会面临多个数据库的情况，这时要为报表应用配置多个 SPL 数据源，分别对应原来的多个数据库。比如：
<Context>
<Resource name="jdbc/esproc"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username=""
password=" "
driverClassName=" com.esproc.jdbc.InternalDriver "
url=" jdbc:esproc:local://? jobVars=dbType:MYSQL,dbName:mysqlds"/>
<Resource name="jdbc/esproc"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username=""
password=" "
driverClassName=" com.esproc.jdbc.InternalDriver "
url=" jdbc:esproc:local://? jobVars=dbType:ORACLE,dbName:oracleds "/>
</Context>
然后借助上面的网关脚本就可以实现 SQL 转换了。
这里需要注意，多库的时候在 raqsoftConfig.xml 里要分别配置数据库连接，比如：
<DB name="mysqlds">
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb?useCursorFetch=true"/>
<property name="driver" value="com.mysql.jdbc.Driver"/>
…
</DB>
<DB name="oracleds">
<property name="url" value="jdbc:oracle:thin:@192.168.0.108:1521:orcl"/>
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
…
</DB>
函数定义与扩展
SPL 的函数定义和扩展功能通过 funtion.xml 文件进行管理，该文件位于 esproc-bin.jar 包中的 /com/scudata/dm/sql/ 路径下。SPL 已经预定义了许多函数，但并未涵盖所有可能的情况。在实际使用中，若需要新的函数或支持新的数据库，可以手动扩展该文件。
<?xml version="1.0" encoding="utf-8"?>
<STANDARD>
<FUNCTIONS type="FixParam">
<FUNCTION name="ADDDAYS" paramcount="2" value="">
<INFO dbtype="ORACLE" value="?1+NUMTODSINTERVAL(?2,'DAY')"/>
<INFO dbtype="SQLSVR" value="DATEADD(DD,?2,?1)"/>
<INFO dbtype="DB2" value="?1+?2 DAYS"/>
<INFO dbtype="MYSQL" value="?1+INTERVAL ?2 DAY"/>
<INFO dbtype="HSQL" value="DATEADD('dd', ?2, ?1)"/>
<INFO dbtype="TERADATA" value="?1+CAST(?2 AS INTERVAL DAY)"/>
<INFO dbtype="POSTGRES" value="?1+interval '?2 days'"/>
<INFO dbtype="ESPROC" value="elapse(?1,?2)"/>
</FUNCTION>
</FUNCTIONS>
</STANDARD>
FUNCTIONS 节点代表一个函数组，type 是函数组类型，FixParam 表示参数个数固定的函数组。FUNCTION 节点代表一个简单 SQL 函数，name 是函数名，paramcount 是参数个数，value 是翻译本函数时的默认值，空串时表示无需翻译。INFO 节点代表一种数据库，dbtype 是数据库名称，空串时表示是 SPL 中的简单 SQL，value 是翻译到本数据库时的对应值。value 中的? 或?1 代表函数的第 1 个参数值，?2 代表函数的第 2 个参数值，依此类推。当 INFO 中的 value 值为空串时，则使用父节点 FUNCTION 的 value 值。
在翻译时，如果 FUNCTION 节点下没有指定数据库的 INFO 节点定义，则此函数保持原样，不会被翻译。
函数扩展
比如要增加函数来计算两个日期的相差天数，我们就可以增加 FUNCTION 节点，定义 DATEDIFF 函数名，然后在 INFO 节点分别配置不同数据库的写法。
<FUNCTION name="DATEDIFF" paramcount="2" value="">
<INFO dbtype="ORACLE" value="?1-?2"/>
<INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"/>
<INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"/>
<INFO dbtype="POSTGRES" value="?1-?2"/>
<INFO dbtype="ESPROC" value="interval(?2,?1)"/>
</FUNCTION>
类似地，如果还要增加对其他数据库的支持，直接增加 INFO 节点信息，把新数据库配置上就可以。比如这里要增加对 SQLite 的支持，来完成日期相差天数的翻译。
<FUNCTION name="DATEDIFF" paramcount="2" value="">
<INFO dbtype="ORACLE" value="?1-?2"/>
<INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"/>
<INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"/>
<INFO dbtype="POSTGRES" value="?1-?2"/>
<INFO dbtype="ESPROC" value="interval(?2,?1)"/>
<INFO dbtype="SQLite" value="JULIANDAY(?1) - JULIANDAY(?2)"/>
</FUNCTION>
不固定个数参数情况
我们前面看到的都是函数参数个数固定的例子，但还有一些事先无法固定参数个数的情况，比如字符串连接，case when，以及取多个参数中的第一个非空值等。
SPL 对这种动态参数个数的情况也提供支持，将 FUNCTIONS 节点的 type 值配置成 AnyParam，也就是任意个数参数。
<FUNCTIONS type="AnyParam">
<FUNCTION name="coalesce">
<INFO dbtype="ESPROC" script='"ifn(" + ?.concat@c() +")"'/>
</FUNCTION>
<FUNCTION name="concat">
<INFO dbtype="ESPROC" script='"concat("+ ?.concat@c() +")"'/>
</FUNCTION>
</FUNCTIONS>
我们只需要在 FUNCTION 增加数据库类型和对应的翻译脚本（SPL 语法），比如我们要为字符串函数 CONCAT 增加对 Oracle 的支持，在<FUNCTION name="concat"> 下添加如下内容：
<INFO dbtype="ORACLE" script=' "("+ ?.concat(" || ") +")" '/>
即可完成。
配置完后，当 SQL 语句：
SELECT EID,NAME,BIRTHDAY,concat(EID,"_",NAME) FROM EMP
数据库类型是ORACLE时会被翻译成：
SELECT EID,NAME,BIRTHDAY,(EID || "_" || NAME) FROM EMP
数据库类型是ESPROC时会被翻译成：
SELECT EID,NAME,BIRTHDAY,concat(EID,"_",NAME) FROM EMP
有了 SPL，报表就可以在任意数据库之间移植而无需修改了。
英文版