Requests for comment/SQL framework

Request for comment (RFC)

SQL framework
Component	General
Creation date	18 December 2013
Author(s)	Owen Davis
Document status	in draft

Summary

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

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

	$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;