Architecture Summit 2014/SQL abstraction

SQL Abstraction

This was the second of two sessions. See Architecture Summit 2014/TitleValue for the first session.

Requests for comment/SQL framework

Wikia library for fluentSQL: https://github.com/Wikia/fluent-sql-php

Example use case: https://github.com/Wikia/app/blob/dev/includes/wikia/services/DataMartService.class.php

Class before refactoring: https://github.com/Wikia/app/blob/36b8e8a1f8671b451a2abcdb54e7a60e72a3623b/includes/wikia/services/DataMartService.class.php

Presentation from Owen

• Wikia not happy with the database abstraction in Core
• alternatives: fluent-sql
  • use as a library

Discussion

• Max: problem with fluent-sql: MediaWiki has to support several database back ends which is what the database abstraction helps with.
• Tim: looks like what we're doing currently with database abstraction
  • counter-argument - but the code looks much better with fluent-sql

• Tim: unifying idea - use fluent-sql on top of the database abstraction
• Owen showing an example of copy paste code that was re-written with fluent-sql
  • Before and after of https://github.com/Wikia/app/blob/dev/includes/wikia/services/DataMartService.class.php
  • using callback functions
  • handling memcache directly

• one great use case is that we can use some common queries with different where clauses by just passing around the query object as we build it
• Gabriel: likes looking at this as a service
  • Roan: the database abstraction class is already somewhat service oriented
  • Gabriel: but different level of abstraction

• Roan: support the general idea of making query abstractions clear (as opposed to several "array" calls etc.). Too focused on making it read like SQL though. Indenting, etc. Confused about chaining nature of fluent-sql and would rather a more object oriented abstraction that exposes the structure explicitly.
• Owen: open to such a library but fluent-sql just was the best thing available.
• Andrew G.: important to recognize the style element here, and he for example doesn't like nested arrays.
• Tim: Asks Roan what his thoughts are on the fluent interface vs array of array implementation
• Roan: OO query builder with possibility of fluent builders for some arguments
• Yuri: reiterates the similarity of this to the status quo. And doesn't think it's worth paying the cost of the migration
• Rob: what are we trying to solve? Is it the question of "do we need to change here"?
• After a straw poll, it seems that a small set of people are interested in change.
• Owen: way forward - use future external library declarations to allow extensions to use this
• Roan: interested in shooting holes in future proposals along this line.

Outcome

Resolution: small working group that tries to battle the inertia here (someone took down the group members but I missed it). Consensus is that things to tackle that would generate more interest are: security, caching, and expressive expression building

• Chris: this helps sanitize code in ways that make the API safer against SQL injection attacks
• Brad: agrees with general idea but doesn't like fluent-sql for the same reasons as Roan
• Roan: very cool that you can runLoop on the result of the query generation
• Brian: consistency is important so he'd rather not see multiple libraries in Core that do the same thing
• Gabriel: reinforce the fact that wrapping up database access and memcaching into a single class moves things towards useful reusable services.