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

通过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.

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

报告相同问题?