Posted by Stevel on June 22, 2010 at 9:24pm
10 followers
Jump to:
| Project: | Drupal core |
| Version: | 8.x-dev |
| Component: | database system |
| Category: | task |
| Priority: | major |
| Assigned: | Unassigned |
| Status: | needs work |
| Issue tags: | Issue summary initiative, needs backport to D7 |
Issue Summary
There is currently no documentation about when static queries should be used, vs when the dynamic queries are preferred. This would be expected on either http://drupal.org/node/310069 or somewhere under http://drupal.org/coding-standards.
The current coding style in HEAD seems to be that when the query string ($query) is constant (i.e. a single string without variables or concatenations), a static query is used, and in other cases a dynamic query is used.
Comments
#1
I got access to edit the coding standards page and added the coding style there: http://drupal.org/coding-standards#selectqueries.
#2
I think this belongs more in the Database API section rather than on the coding standards page. The coding standards page deals with syntax conventions more than how to use the Drupal APIs.
#3
I would argue that, although it is not a syntactical convention, it is still a coding standard, and not API documentation.
For select queries, it could be appropriate to use db_query even for more complex queries (eg. for performance reasons).
I think it is more a convention used in the Drupal project which tells when to use what form of querying.
I think it should probably go on http://drupal.org/node/2497 (SQL coding conventions), but that page hasn't been updated for D7 yet.
#4
OK... The rest of the main coding standards page is not anything like the new section. Agreed: it should go onto the SQL coding conventions sub-page... not sure when/if that will be updated for D7. But this new section definitely doesn't fit with the rest of the main coding standards page.
#5
Also, I'm moving this to the DB section so that DB maintainers can review what you wrote... No one but me regularly reads the doc queue.
#6
#7
The text is sound, ok to move it to 2497.
#8
The text is really backward. Static queries should be preferred UNLESS you need the capabilities of dynamic queries, and it should be documented why (performance and flexibility; there are queries that the select builder can't handle).
I also think this information should go in the DB API section.
#9
@Crell: hm? On the contrary, dynamic queries are the only option unless you have (1) a purely static query, (2) that don't require alteration or extending.
Dynamic queries are the norm, static queries the well-bounded exception.
#10
Oh wow; we're not just going to have a bikeshed discussion we're going to have an existential bikeshed discussion. Awesome!
The select builder was very consciously designed as an "only when needed" API. In fact, its primary use case was Views and other heavily dynamic code, not normal module authors. Since then we've discovered just how many places in core had their own horribly bad "query builder" for various one-off cases so it ended up more widely used than anticipated, but it is still a much heavier system with more overhead than static queries.
Most queries are purely static and do not require alteration or extending.
There are some query types that you simply cannot do with the query builder. SQL is too complex to completely represent 100% in any reasonably efficient PHP model.
The query builder does have non-trivial overhead. A not-overly-complex query involves somewhere around a dozen function calls, lots of array processing, and string compilation, plus potentially hook invocations, above and beyond the cost of db_query().
Although the query builders are pretty darned slick, there are still some DX gotchas with them, such as not all of the methods of the select builder are chainable. There are perfectly good reasons for that in most cases, but it still is more annoying if you're doing a not-too-interesting query. Maybe we can clean that up a bit in D8, but for now it is what it is.
People's first inclination should be, and we should guide them to, using static queries. There are cases, clear specific cases, where you'd want to use the query builder. But if you don't need it, don't use it. If unsure, default to static unless there is a specific reason to go with the builder.
Drupal 7's performance is pathetic enough as is. Let's not encourage people to make it worse.
#11
I pulled the text off the coding standards page until we get an agreement here. Origintal text below:
Static vs Dynamic Select Queries
While it is possible to perform select operations using both static and dynamic queries, you should only use a static query (i.e. db_query) when ALL of the following conditions hold:
In all other circumstances, you should use a dynamic query (i.e. db_select)
#12
On http://blog.worldempire.ch/story/howto-convert-module-dbtng, I used the following to describe when to use a dynamic query:
DBTNG provides a flexible Query Builder, which needs to be used if one or multiple of the following things apply to a specific query:
* Others need to be able or might want to extend the query (Especially if the query used db_rewrite_sql() before)
* If the query is very dynamic and does add different conditions, joins and other things based on something.
* If so called query extenders (see below) are being used.
Feel free to re-use anything from that blog post if it is useful ;)
#13
This is pretty important. :)
#14
I'd suggest adding a table indicating which (My)SQL features can be done without worry in
db_query(), and which requiredb_select(). E.g., http://drupal.org/node/310072 does not include any examples of queries with JOIN statements, so I was under the impression that one must usedb_select()for even simple joins.#15
There is a very incomplete list started in the documentation: http://drupal.org/node/773090 "For portability across supported database engines your code should only use functions known to be part of the ANSI standard and supported across all databases that Drupal supports."
However, it does not include joins or modifiers like GROUP BY.
And given that "all the databases that Drupal supports" should at least consider drivers that are well-supported in contrib such as MSSQL and Oracle, i'm thinking we need a table... but it seems nothing can be that simple here's a comparison of command implementations in MySQL, PostgreSQL, MS SQL Server, and Oracle among others: http://troels.arvin.dk/db/rdbms/
(And it doesn't cover GROUP BY either.)
#16
I have updated http://drupal.org/node/310072 to make it clear that it supports arbitrarily complex query strings and is the preferred way of handling SELECTs.
#17
Oh please :). The addition crell made is very understandable and that is the most important thing, right? I don't think its usefull to add a ton of complex technical stuff in those docs. Most of the developpers are helped with that additon. If someone has a more complex question he/she should ask it in irc.
I'm closing this. If this hurts someone feelings, feel free to reopen... (please don't ;) )
#18
That addresses the original post, but not the woeful state of Functions and operators.
Could we at least have a link to the recommended ANSI standard?
#19
mlncn sure, if you got a link I'll place it in:
"s supported database engines your code should only use functions known to be part of the ANSI standard and su"
#20
i don't know what it should be linked to, or i'd edit the handbook page already. See #14 and #15 for reason / as much as i know.
#21
I'm getting tired of explaining, but you should only use
db_query()for *super simple* SELECT queries. Crell is dead wrong in his recommendation here.db_query()is definitely not the preferred way of handling SELECTs.Anything that is not a straight
SELECT aaa FROM bbb WHERE c = :acauses portability troubles. For example, the use of something as simple as theLIKEoperator requiresdb_select(). Even the behavior ofORDER BYis different between database engines (most of them except MySQL requires that (1) ORDER BY use only columns or expressions from the SELECT clause, (2) ORDER BY do no use aliases from the SELECT clause).#22
See #1182428: Document that db_like() does not work when used with db_query() for an example of problems with
db_query().#23
I edited http://drupal.org/node/310072 to at least stop the bleeding.
#24
Ok, that are good reasons to open this again :)
#25