SelectQueryBuilder is a new fluent interface for constructing database queries, which has been merged to master for release in MediaWiki 1.35. Please consider using it in new code.
SELECT page_id FROM page WHERE page_namespace=$namespace AND page_title=$title
becomes
$id = $db->newSelectQueryBuilder() ->select( 'page_id' ) ->from( 'page' ) ->where( [ 'page_namespace' => $namespace, 'page_title' => $title, ] ) ->fetchField();
As explained on the design task T243051, SelectQueryBuilder was loosely based on the query builder in Doctrine, but I made an effort to respect existing MediaWiki conventions, to make migration easy.
SelectQueryBuilder is easy to use for simple cases, but has the most impact on readability when it is used for complex queries. That's why I chose to migrate the showIndirectLinks query in Special:WhatLinksHere as a pilot -- it was one of the gnarliest queries in core.
SelectQueryBuilder excels at building joins, including parenthesized (nested) joins and joins on subqueries.
SelectQueryBuilder can be used as a structured alternative to the "query info" pattern, in which the parameters to Database::select() are stored in an associative array. It can convert to and from such arrays. As a pilot of this functionality, I converted ApiQueryBase to use a SelectQueryBuilder to store accumulated query info.
Check it out!
-- Tim Starling
That's really cool.
It would be interesting if we could use this as an excuse to move away from what i would consider antipatterns in our sql layer e.g., having no high level way of specifying WHERE comparisons (x > "foo". Currently we do addQuotes() instead of automatic or `field1` = `field2`). Similarly the whole thing with field names only getting automatically addIdentifierQuotes() if it looks like its not sql, always seemed sketch. This might provide us a path forward to address those while still maintaining backwards compat.
-- Brian
p.s. Now all i need to dream about is a fluent version of Html class.
On Thursday, May 21, 2020, Tim Starling tstarling@wikimedia.org wrote:
SelectQueryBuilder is a new fluent interface for constructing database queries, which has been merged to master for release in MediaWiki 1.35. Please consider using it in new code.
SELECT page_id FROM page WHERE page_namespace=$namespace AND page_title=$title
becomes
$id = $db->newSelectQueryBuilder() ->select( 'page_id' ) ->from( 'page' ) ->where( [ 'page_namespace' => $namespace, 'page_title' => $title, ] ) ->fetchField();
As explained on the design task T243051, SelectQueryBuilder was loosely based on the query builder in Doctrine, but I made an effort to respect existing MediaWiki conventions, to make migration easy.
SelectQueryBuilder is easy to use for simple cases, but has the most impact on readability when it is used for complex queries. That's why I chose to migrate the showIndirectLinks query in Special:WhatLinksHere as a pilot -- it was one of the gnarliest queries in core.
SelectQueryBuilder excels at building joins, including parenthesized (nested) joins and joins on subqueries.
SelectQueryBuilder can be used as a structured alternative to the "query info" pattern, in which the parameters to Database::select() are stored in an associative array. It can convert to and from such arrays. As a pilot of this functionality, I converted ApiQueryBase to use a SelectQueryBuilder to store accumulated query info.
Check it out!
-- Tim Starling
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Yes, it would be interesting to add an expression builder. It is much easier to do so now that the Database acts as a factory for the query builder. Originally I was trying to make the data in SelectQueryBuilder DBMS-independent. Now that we always have a connection object in SelectQueryBuilder, it's possible to have it call functions like addQuotes() while building the underlying condition array.
-- Tim Starling
On 22/5/20 2:14 pm, Brian Wolff wrote:
That's really cool.
It would be interesting if we could use this as an excuse to move away from what i would consider antipatterns in our sql layer e.g., having no high level way of specifying WHERE comparisons (x > "foo". Currently we do addQuotes() instead of automatic or `field1` = `field2`). Similarly the whole thing with field names only getting automatically addIdentifierQuotes() if it looks like its not sql, always seemed sketch. This might provide us a path forward to address those while still maintaining backwards compat.
-- Brian
p.s. Now all i need to dream about is a fluent version of Html class.
On Thursday, May 21, 2020, Tim Starling tstarling@wikimedia.org wrote:
SelectQueryBuilder is a new fluent interface for constructing database queries, which has been merged to master for release in MediaWiki 1.35. Please consider using it in new code.
SELECT page_id FROM page WHERE page_namespace=$namespace AND page_title=$title
becomes
$id = $db->newSelectQueryBuilder() ->select( 'page_id' ) ->from( 'page' ) ->where( [ 'page_namespace' => $namespace, 'page_title' => $title, ] ) ->fetchField();
As explained on the design task T243051, SelectQueryBuilder was loosely based on the query builder in Doctrine, but I made an effort to respect existing MediaWiki conventions, to make migration easy.
SelectQueryBuilder is easy to use for simple cases, but has the most impact on readability when it is used for complex queries. That's why I chose to migrate the showIndirectLinks query in Special:WhatLinksHere as a pilot -- it was one of the gnarliest queries in core.
SelectQueryBuilder excels at building joins, including parenthesized (nested) joins and joins on subqueries.
SelectQueryBuilder can be used as a structured alternative to the "query info" pattern, in which the parameters to Database::select() are stored in an associative array. It can convert to and from such arrays. As a pilot of this functionality, I converted ApiQueryBase to use a SelectQueryBuilder to store accumulated query info.
Check it out!
-- Tim Starling
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Hey Tim,
I really like this new feature—thanks for making it happen! It reminds me of the fluent SQL builder[1] that Owen presented at the 2014 Architecture Summit.[2] While integration with that library was ultimately rejected, I wonder if there are any useful concepts or features that could be cherry-picked from there to enhance the functionality of MediaWiki’s new query builder.
What do you think?
— [1] https://github.com/Wikia/fluent-sql-php [2] https://www.mediawiki.org/wiki/Architecture_Summit_2014/SQL_abstraction
Cheers, Máté Szabó SOFTWARE ENGINEER he - him - his
Fandom Poland sp. z o.o. z siedzibą w Poznaniu, ul. Abp. A. Baraniaka 6 Sąd Rejonowy Poznań – Nowe Miasto i Wilda w Poznaniu, VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, KRS 0000254365 NIP: 5252358778 Kapitał zakładowy: 50.000,00 złotych
On 22 May 2020, at 03:37, Tim Starling tstarling@wikimedia.org wrote:
SelectQueryBuilder is a new fluent interface for constructing database queries, which has been merged to master for release in MediaWiki 1.35. Please consider using it in new code.
SELECT page_id FROM page WHERE page_namespace=$namespace AND page_title=$title
becomes
$id = $db->newSelectQueryBuilder() ->select( 'page_id' ) ->from( 'page' ) ->where( [ 'page_namespace' => $namespace, 'page_title' => $title, ] ) ->fetchField();
As explained on the design task T243051, SelectQueryBuilder was loosely based on the query builder in Doctrine, but I made an effort to respect existing MediaWiki conventions, to make migration easy.
SelectQueryBuilder is easy to use for simple cases, but has the most impact on readability when it is used for complex queries. That's why I chose to migrate the showIndirectLinks query in Special:WhatLinksHere as a pilot -- it was one of the gnarliest queries in core.
SelectQueryBuilder excels at building joins, including parenthesized (nested) joins and joins on subqueries.
SelectQueryBuilder can be used as a structured alternative to the "query info" pattern, in which the parameters to Database::select() are stored in an associative array. It can convert to and from such arrays. As a pilot of this functionality, I converted ApiQueryBase to use a SelectQueryBuilder to store accumulated query info.
Check it out!
-- Tim Starling
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
wikitech-l@lists.wikimedia.org