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.
