Talk:Requests for comment/SQL framework

Other frameworks

I'd like to discuss other frameworks for SQL abstraction like the widely-used Doctrine. -- ☠MarkAHershberger☢(talk)☣ 16:28, 4 January 2014 (UTC)

Doctrine's API looks very similar to Wikia's. The main differences I saw are:

• Doctrine uses lowercase method names (->select()) while Wikia uses uppercase (->SELECT()). I like lowercase better.
• Wikia uses underscores in ->AND_() and ->AS_() to prevent clashing with PHP keywords, Doctrine avoids this. I think the underscores are very confusing, you have to remember which ones have an underscore and which ones don't.
  • Doctrine uses ->andWhere() and ->andHaving() instead of ->AND_(), not only to avoid the underscore problem but also to allow appending to the WHERE and HAVING clauses separately
  • Doctrine does not have ->AS_() or any equivalent functionality :(
• Doctrine uses PDO-style placeholders like user_id = ? which you then have to bind a value to. I don't like this because:
  • Using unnamed placeholders leaves you with a rather meaningless list of values at the end
  • Using named placeholders fixes that, but makes the query quite verbose
  • The disconnect between the query and the values potentially makes #Extensibility harder
• Doctrine explicitly does not paper over differences between SQL implementations in any way. MW's existing SQL framework explicitly does abstract differences between SQL implementations, table prefixes, etc. etc. I think that's an important feature and one we need to retain

--Catrope (talk) 10:13, 7 January 2014 (UTC)

I also think Doctrine is pretty great and it has all the Schema manipulation functions that are missing from this FluentSQL-PHP library. Mediawiki does a lot of that for setup/upgrades etc so it may be a requirement. The uppercase is a stylistic thing for sure, and I do like the idea of renaming the AND_ and AS_ functions. Owyn (talk) 18:13, 23 January 2014 (UTC)

Extensibility

One major advantage of the associative array style we use now is that you can take an associative array that describes a query, hand it to some other piece of code (e.g. an extension hook or a method implemented by a subclass), and get back a slightly modified version of the query. An array-based format makes this easy because you can just do $query['where']['rev_deleted'] = 0; or $query['tables'][] = 'revision'; or whatever.

With the chained style used by both Wikia's SQL framework and Doctrine, it's possible to implement this as well. But you don't quite get it for free, you have to design around it. I think any new way for handling SQL in MW should continue to support query modification like this. --Catrope (talk) 09:14, 7 January 2014 (UTC)

Agree with these comments, I think the major advantage of this is the ability to build SQL inside a factory and manipulate it. Many queries are very similar except for filtering/ordering. Owyn (talk) 18:13, 23 January 2014 (UTC)

ready for IRC discussion?

Hi User:Owyn! If this RfC is ready to be discussed in one of the weekly Architecture meetings then please mention it on wikitech-l so we can schedule it. Thanks! Sharihareswara (WMF) (talk) 20:12, 21 July 2014 (UTC)

Concerns

5 contributors. Only a sinlge release, which was in May 2014. The thing is also not on Packagist. That does not look good to me. Doctrine DBAL on the other hand is used by many thousands of projects, is included by default in things such as the Symfony framework and gets contributions from tons of people. --Jeroen De Dauw (talk) 18:08, 11 January 2015 (UTC)

Next steps

If you want this reviewed, please create a Phabricator task, thanks.

Also, how does this relate to the Abstract table definitions RFC?

-- SPage (WMF) (talk) 01:56, 26 March 2015 (UTC)