Last modified: 2014-09-24 01:23:54 UTC
Most of the Database:: wrapper functions for the various SQL queries allow arrays for most of their arguments (tables, where, etc). Database::update does not support multiple tables. This is commonly needed in SQL if not so far needed in MediaWiki. (My extension does need it).
Created attachment 3275 [details] Patch for Database::update array of tables
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Patch from 2007, 4 years ago. Really Database::update should take $tables array and $join_conds, just like Database::select does. My scenario, update touch on all pages belonging to a category: UPDATE page INNER JOIN categorylinks SET page_touched = CURRENT_TIMESTAMP() WHERE page.page_id = categorylinks.cl_from AND categorylinks.cl_to = 'MyCategoryName';
Thank you for the patch, Andrew. Adding need-review keyword to signal developers to review it. I'm sorry for the wait.
Applied in r103706, also added to relevant places
This lacks join conditions etc as they're used on Database::select(). Is this actually useful as is without that? The scenario in comment 3 with an inner join ..... should I think be able to get away with just what's in the where clause. But outer joins etc would need more...
I can't do multiple tables unless parameter 1 lets me do $table=array(), which it did not. You're correct, once I have $table with an array, I can do INNER JOIN using $conds as usual. But until then nada.
Seams like we should keep this open until $join_conds is added to Database::update.
Reverted in r104926 Not coming back Only seemingly supported by MySQL, so unless we did workarounds for non MySQL backends... Err, yeah - no.
This is not MySQL-specific. It's supported by Microsoft SQL Server and PostgreSQL. But only through sub-queries on SQLite and Oracle. So since support is at best 50/50 without workarounds, I can see why MW shouldn't include pure join support. It seems to me like the sub-query method is universally supported by everyone, instead of a join syntax. It can be a tiny bit complicated (there may be issues with keys depending on how you write the query?), is it worth standardizing it and hiding the implementation detail from the user so they don't have to write SQL? If it's a lot more taxing and should be discouraged, perhaps a separate function? So what's the recommended workaround/method? Do your SELECT with joins ahead of time, cache the results, then run a massive ugly UPDATE statement? My actual current example: UPDATE page INNER JOIN categorylinks SET page_touched = CURRENT_TIMESTAMP() WHERE page.page_id = categorylinks.cl_from AND categorylinks.cl_to = 'MyCategoryName'; Since the API didn't let me do it, and didn't tell my WHY I couldn't do it, I just went ahead and did it anyways, this was the wrong option. If we have an abstraction API with clear limitations, we should be clearly pointing out why certain functionality isn't included (and pointing them to how they should do it, say on a wiki page). Even if this is a documentation problem I think it's far from resolved. Personally I think even if we have to run multiple statements, we should just do it for the user auto-magically instead of require them to jump through hoops. It makes a ton of sense API wise and it's already confused a lot of people and it would be safer.
