douganggu4392 2016-12-23 13:10
浏览 291
已采纳

通过PDO :: sqlite创建函数在sqlite中实现timestampdiff

We're using MySQL as our main database for a Laravel project.

Laravel uses in-memory sqlite combined with factories/seeders for functional/acceptance tests by default, to keep tests isolated and fast. We're currently trying to write small unit tests with mocks as much as possible, but we also have a few hundred older "functional" tests which fire up these little sqlite instances and use Laravel testing functions like seeInDatabase().

Laravel's Eloquent ORM is pretty good at abstracting the database functions away, but there's some functionality which sqlite does not provide by default.

I'm trying to extend the sqlite functionality using this PDO method, to support a few often used MySQL functions in our codebase:

$pdo->sqliteCreateFunction('timestampdiff', function($unit, $start, $end){
    return ...
});

I understand how to implement the UDF (I've already implemented trig functions, regex, datediff, etc).

The problem is that the first parameter passed to timestampdiff is a unit, an unquoted plain label like MINUTE or YEAR.

This parameter is interpreted as a column by sqlite. Faking MySQL's date_add() is also problematic, as the third parameter to that function is unquoted with spaces, like INTERVAL 2 HOUR.

My current best effort is to replace timestampdiff queries with floor((unix_timestamp(end) - unix_timestamp(start))/60), as both floor and unix_timestamp are creatable through sqliteCreateFunction.

This all feels very hacky though. I realize I might be pushing the limits of PHP/PDO/sqlite here, but would love suggestions on a more elegant way to do this.

  • 写回答

1条回答 默认 最新

  • dounuogong0358 2016-12-23 13:37
    关注

    User-defined functions cannot change the syntax rules.

    Your only choice is to get the ORM to use different functions for different databases, or to install your own UDFs in MySQL.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 爬取豆瓣电影相关处理
  • ¥15 手机淘宝抓清除消息接口
  • ¥15 C#无selenium
  • ¥15 LD衰减计算的结果过大
  • ¥15 用机器学习方法帮助保险公司预测哪些是欺诈行为
  • ¥15 计算300m以内的LD衰减
  • ¥15 数据爬取,python
  • ¥15 怎么看 cst中一个面的功率分布图,请说明详细步骤。类似下图
  • ¥15 为什么我的pycharm无法用pyqt6的QtWebEngine
  • ¥15 FOR循环语句显示查询超过300S错误怎么办