Last modified: 2012-10-01 19:55:33 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T19221, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 17221 - Database class does not provide support for table aliases
Database class does not provide support for table aliases
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
1.15.x
All All
: Low enhancement (vote)
: ---
Assigned To: Nobody - You can work on this!
: patch, patch-reviewed
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2009-01-29 06:34 UTC by Ralph Taylor
Modified: 2012-10-01 19:55 UTC (History)
5 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments
Diff file of Database.php (1.85 KB, patch)
2009-01-29 06:34 UTC, Ralph Taylor
Details
Database Diff file in correct diff format (3.92 KB, patch)
2009-02-15 01:44 UTC, Ralph Taylor
Details

Description Ralph Taylor 2009-01-29 06:34:58 UTC
Created attachment 5743 [details]
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.

###########  ###########  ###########
#  foo    #  # foo_bar #  #   bar   #
###########  ###########  ###########
# foo_id  #  # foo_id  #  # bar_id  #
# foo_name#  # bar_id  #  # bar_name#
###########  ###########  ###########


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.
Comment 1 Siebrand Mazeland 2009-02-02 16:33:04 UTC
+need-review
Comment 2 Ralph Taylor 2009-02-15 01:44:53 UTC
Created attachment 5814 [details]
Database Diff file in correct diff format

Added diff file with context and checked against trunk
Comment 3 Ralph Taylor 2009-02-15 02:12:40 UTC
Updated mediawiki version
Comment 4 Bryan Tong Minh 2009-11-12 22:48:44 UTC
I would prefer a syntax where instead of a two dimensional array a hashmap is used (e.g. 'tablename' => 'tablealias')
Comment 5 p858snake 2011-04-30 00:09:58 UTC
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Comment 6 db [inactive,noenotif] 2012-10-01 19:55:33 UTC
(In reply to comment #4)
> I would prefer a syntax where instead of a two dimensional array a hashmap is
> used (e.g. 'tablename' => 'tablealias')

This syntax exists since r77597

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links