Architecture Summit 2014/SQL abstraction

SQL Abstraction

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

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.