V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
爱意满满的作品展示区。
jingwei8340885
V2EX  ›  分享创造

怎样写出可在各种数据库间移植的 SQL 语句

  •  
  •   jingwei8340885 · 121 天前 · 1083 次点击
    这是一个创建于 121 天前的主题,其中的信息可能已经有所发展或是发生改变。

    任意的 SQL 语句都可移植是做不到的。因为各种数据库的功能并不一样,某些数据库的语法在另一种数据库中根本就没有对应的实现机制,这时当然就不可能移植了。

    如果我们不用数据库的特殊功能,只在国际标准的范围内,也就是只使用所有数据库的功能交集,其实也相当丰富了,这总该能移植了吧。

    基本的情况确实是可以,比如

    select * from employee where age>50
    select area,sum(amount) from orders group by area
    

    在所有数据库中都能正常执行。

    但是,还有很多 SQL 函数在各种数据库中的写法不一样,特别是字符串和日期相关的,比如

    MySQL: year( x )
    Oracle: extract( year from x )
    

    国际标准没有对这些函数做规定,这种 SQL 语句就会和数据库相关而无法移植了,而这些函数在应用开发中还特别常见。

    Hibernate 能将包括这些函数的标准 HQL 语句转换成不同数据库的 SQL ,但 HQL 支持的函数太少,碰到不认识的函数还是要分别注册,这就丧失了可移植性;而且 HQL 能生成的 SQL 本身也比较简单,覆盖面太窄了。

    转换 SQL 语句中的不同的函数,esProc SPL 是个更好的解决方案。

    esProc SPL 是个 Java 写的开源软件,在这里 https://github.com/SPLWare/esProc

    SPL 中有个 sqltranslate 函数,能够使用了某些“标准”的函数语法书写的 SQL 翻译成各种数据库的函数。比如:

    sql = "select * from Orders where year(OrderDate)=2000"
    sql.sqltranslate("MySQL") -> "select * from Orders where year(OrderDate)=2000"
    sql.sqltranslate("Oracle") -> "select * from Orders where extract( year from OrderDate )=2000"
    

    sqltranslate 中已经预定义了很多数据库中很多函数的写法,比如:

    sql = "select * from Orders where ADDDAYS(OrderDate,3)>ShipDate"
    sql.sqltranslate("MySQL") -> "select * from Orders where (OrderDate + INTERVAL 3 DAY)>ShipDate"
    sql.sqltranslate("Oracle") -> "select * from Orders where (OrderDate + NUMTODSINTERVAL(3, "DAY")>ShipDate"
    sql.sqltranslate("DB2") -> "select * from Orders where (OrderDate + 3 DAYS )>ShipDate"
    

    如果碰到了新的函数或新的数据库,esProc 还允许程序员自己在配置表中增加。

    esProc 提供了 JDBC 接口,很容易被集成进 Java 应用以实现 SQL 移植。这里有完整的使用方法:SPL:跨数据库移植 SQL

    esProc 当然并不是仅仅为了移植 SQL 而设计的,SPL 是功能强大的结构数据处理语言,转换 SQL 只是 SPL 顺便实现的一点点小功能而已,SPL 更多的作用在于替代 SQL 实现复杂的查询运算。

    当计算需求复杂时我们会用到某些数据库特有的语法或函数。比如 Oracle 有个 KEEP 函数可以方便地计算每组的第一条/最后一条记录,其它很多数据库都没有。如果 SQL 语句中用到了这个函数,就不能再用上面的简单办法移植到其它数据库上了。这种情况下,我们可以只用基本的 SQL 读取数据,而把更复杂的运算交给 SPL 来做,SPL 是完全不依赖于数据库的,这样写出来的代码就可以继续拥有可移植性了。

    select Area, max(Amount) KEEP( dens_rank first order by extract( month from OrderDate) )
    from Orders where extract( year from OrderDate)=2000 group by Area
    

    这条 Oracle 的 SQL 语句可以用 SPL 改成这样:

    A B
    1 'select OrderDate, Area, Amount from Orders where year(OrderDate)=2000 /读数 SQL
    2 =A1.sqltranslate("Oracle") /转成目标数据库的 SQL
    3 =db.query@x(A2) /读出数据
    4 =A3.group(Area;~.minp@a(month(OrderDate)).max(Amount)) /用 SPL 计算目标结果

    ( SPL 代码写在格子里,这和普通程序语言很不像,参考这里:写在格子里的程序语言

    SPL 拥有所有 SQL 的运算能力(过滤、分组、连接等),并且都是自行实现的(不是翻译成 SQL ),不依赖于任何数据库,保证这个代码可以在任何数据库上正确执行,就可以移植了。

    事实上,SPL 提供的运算能力远比 SQL 更强大丰富(所以也不可能翻译成 SQL 执行)。有些即使可以用 SQL 写出来的复杂运算,用 SPL 也会更简单。比如我们经常举例的任务:计算一支股票最长连续上涨的天数,SQL 要写成多层嵌套,冗长且难懂:

        select max(ContinuousDays) from (
            select count(*) ContinuousDays from (
                select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
                    select TradeDate,case when Price>lag(price) over ( order by TradeDate) then 0 else 1 end UpDownTag from Stock ))
            group by NoRisingDays )
    
    SPL 提供了更多的基础函数,同样的计算逻辑,写起来要简单得多:
    
        Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())
    

    这样的代码,是不是既开发简单又容易移植了?

    SPL 资料

    1 条回复    2023-12-14 10:00:01 +08:00
    beetlerx
        1
    beetlerx  
       120 天前
    orm 不就是用来干这事的
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2615 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 15:31 · PVG 23:31 · LAX 08:31 · JFK 11:31
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.