Requests for comment/SQL framework
| SQL framework | |
|---|---|
| Component | General |
| Creation date | |
| Author(s) | Owen Davis |
| Document status | in draft |
Summary
[edit]Wikia has ported a simple fluent SQL framework from Java to PHP and merged it with our codebase as a vendor library. We are starting to use this for extensions which make heavy use of database queries in order to simplify development, debugging and add some nice features to the database API (caching of results, and potentially different centralized logging/profiling back ends). The source code is visible on Github: Wikia/fluent-sql-php
Proposal
[edit]We propose adding this sort of query interface to the existing mediawiki database library. The current database API uses positional arguments and while it works for simple queries, when you get to the point of wanting to build a complex query, the various approaches to building arrays of arrays of key/value pairs differs a lot from one developer to another and it is much more difficult to see what a query is doing without executing it and capturing debug output. Since we are dealing with SQL in the end anyway, the most natural API for the database interface is something that resembles SQL.
Example query
[edit] $pageviews = (new WikiaSQL())->skipIf(empty($app->wg->StatsDBEnabled))->cacheGlobal(60*60*12)
->SELECT("date_format(time_id,'%Y-%m-%d')")->AS_('date')
->FIELD('pageviews')->AS_('cnt')
->FROM('rollup_wiki_pageviews')
->WHERE('period_id')->EQUAL_TO($periodId)
->AND_('wiki_id')->EQUAL_TO($wikiId)
->AND_('time_id')->BETWEEN($startDate, $endDate)
->runLoop($db, function (&$pageViews, $row) {
$pageViews[$row->date] = $row->cnt;
});
// Example of building a more dynamic query
$sql = (new WikiaSQL())->skipIf(empty($app->wg->StatsDBEnabled))->cacheGlobal(43200)
->SELECT('r.wiki_id')->AS_('id')
->FIELD($field)->AS_('pageviews')
->FROM('report_wiki_recent_pageviews')->AS_('r')
->ORDER_BY(['pageviews', 'desc'])
->LIMIT($limitUsed);
if (is_integer($public)) {
$sql
->JOIN('dimension_wikis')->AS_('d')
->ON('r.wiki_id', 'd.wiki_id')
->WHERE('d.public')->EQUAL_TO($public);
}
if (!empty($langs)) {
$sql->AND_('r.lang')->IN($langs);
}
if (!empty($hub)) {
$sql->AND_('r.hub_name')->EQUAL_TO($hub);
}
$topWikis = $sql->runLoop($db, function(&$topWikis, $row) {
$topWikis[$row->id] = $row->pageviews;
});
return $topWikis;