Author: djnrrd

Description:
Diff file of Database.php

The existing abstraction layers provided in the Database.php class do not provide full support for database aliases.

Use Case:

A developer wishes to create an extension or other project that requires additional database tables. The developers database design preference is to use the same field name where there are relations between tables, e.g.

1. 1. ### ###
2. foo # # foo_bar # # bar #
   1. ### ###
3. foo_id # # foo_id # # bar_id #
4. foo_name# # bar_id # # bar_name#
   1. ### ###

If creating a SQL query utilising a join the developer would use aliases and write:

SELECT f.foo_name, b.bar_name FROM foo AS f LEFT JOIN foo_bar AS j ON f.foo_id = j.foo_id LEFT JOIN bar AS b ON j.bar_id = b.bar_id WHERE b.bar_id = 8;

The developer wishes to use the API provided by the Database class in MediaWiki to maintain code portability and so creates the following code:

$table = array("foo AS f", "foo_bar AS j", "bar AS b");
$vars = array("f.foo_name", "b.bar_name");
$conds = "b.bar_id = {$criteria}";
$options = "";
$join = array("foo_bar" => array("LEFT JOIN", "f.foo_id = j.foo_id"), "bar" => array("LEFT JOIN", "j.bar_id = b.bar_id"));
$dbQuery = $dbSlave->select($table, $vars, $conds, "myFunction", $options, $join);

As the table names are parsed through Database::tableName() they will encounter the rule on line 01467 which will return the table name without adding the $wgSharedPrefix value, resuling in a "Table does not exist" database error.

This may be worked around by using:

$vars = array("{$dbSlave->tableName("foo")}.foo_name", "{$dbSlave->tableName("bar")}.bar_name");
$join = array("foo_bar" => array("LEFT JOIN", "{$dbSlave->tableName("foo")}.foo_id = {$dbSlave->tableName("foo_bar")}.foo_id"), "bar" => array("LEFT JOIN", "{$dbSlave->tableName("foo_bar")}.bar_id = {$dbSlave->tableName("bar")}.bar_id"));

However as the developer's code grows and more joins are required, the code grows larger and uglier.

Suggested solution:

Attached is a diff file of my changes to Database.php which addresses this issue. Aliases can now be created on Select statements by creating a 2 dimensional array in the $table variable as so:

$table = array(array("foo", f"), array("foo_bar", "j"), array("bar", "b"));
$vars = array("f.foo_name", "b.bar_name");
$conds = "b.bar_id = {$criteria}";
$options = "";
$join = array("foo_bar" => array("LEFT JOIN", "f.foo_id = j.foo_id"), "bar" => array("LEFT JOIN", "j.bar_id = b.bar_id"));
$dbQuery = $dbSlave->select($table, $vars, $conds, "myFunction", $options, $join);

Queries that do not utilise aliases can still be performed using the previous methods and should not break existing usage cases.

Version: 1.15.x
Severity: enhancement

attachment Database.php.diff ignored as obsolete