DTBNG Database layer is difficult to debug: Improve instrumentation

I think moshe added EXPLAIN capabilities into the 7.x Devel module, but that wouldn't help with debugger integration, I suppose.

I'm still unclear what you mean here. I understand instrumentation to be the fancy way of saying "lotsa print_r() calls". :-)

The DB layer logs as much as it can about a query if asked. One of the catches with prepared statements is that the "full query string" as we have known it before does not ever exist, anywhere, in any system[1]. Therefore at best we can take the data available and simulate creating what it probably would have looked like if it existed. That would be devel's job at this point.

If we can figure out a way to track what alter hooks impact a given query without harming performance, I'm up for it. I'm not sure how we can do that though without just asking all alter hooks to mess with the query comment.

[1] Well actually since we are using simulated prepared statements in the PDO C code for MySQL and Postgres, what the DB server receives is a "complete" query string. I don't think we have any way to access that information, though.

Well at the risk of repeating myself, there is no query string to print, anywhere. We can, at best create "if this had been done as a straight query string it would probably have looked like this: XXXXX". That logic probably belongs in the devel module, which already has access to as much data as we can give it.

This is not a Drupal-specific problem either.

Adding more debugging features is really a feature request, not a bug

Furthermore, Devel already allows showing the complete query, either with or without the arguments filled in, so I'm not sure if there is anything more that can be done here...

#8 sounds like it's proposing a sort of "debug mode driver". If we go that route it should definitely be a new driver, or an injectable feature like logging. The performance hit will be too big otherwise.

What sort of debug information do we need to track that isn't already available in the query logger? The query logger provides more or less parity with the D6 query log.

I was thinking a debug mode driver might be cool, although I'm sure it would be hard to write, and I have no ability in that area. Perhaps it would do something like submit the query to the debug driver, which would do nothing, and also to the real driver. That way, the debug driver's queries would have the parameters substituted by PDO, and be logged, right?

No, because the substitution happens down in C code where we cannot get to it. If we want to display "this is the query that would have been run if we weren't using prepared statements", we have to build that ourselves. That string never exists in memory in any place that we have access to.

The feature request here is still too vague. Clarify.

What I want is this.

see dpq() in devel module. is isn't a nice chainable method but it does sanitize the arguments without check_plain and this can be copy/paste into a SQL app. nothing really wrong with this proposal, its just that the need is dimished now.

1) Didn't know about dpq() yet.

2) The chainable query method is actually what is most often required during development to see at which exact point during query building something goes wrong.

3) Current outputs and differences between ->debug() and dpq():

->debug():
      Debug:

      'SELECT n.nid AS nid
      FROM 
      node n
      ORDER BY n.changed '

      in SelectQueryExtender->__call() (line 829 of includes\database\select.inc).

dpq():
      SELECT n.nid AS nid
      FROM 
      {node} n
      ORDER BY n.changed 
      LIMIT 50 OFFSET 0

Most of the dpX() functions take a name parameter. I frequently put __FUNCTION__ or __LINE__ in as the name. It looks like dpq() doesn't, but it should.

We certainly can't add anything that doesn't do the full cleanup work that dpq() does. And if dpq() is already doing it, I really don't see why we have to replicate that code.

It looks like the ability to inject a debug into the middle of the chain would be very helpful, though. Could that be added in contrib, or does it have to be a core API change?

In theory it would be possible to implement as an extender but it would be quite ugly. PHP != Javascript, so such contrib-additions are not really feasible.

The other issue here is that, for select queries, compiling to a string modifies the query (or can, depending on what extenders are available). I don't know that it's safe to encourage people to just throw random ->debug() calls into a method chain. If you do that and then call more methods on the query, then what runs may not be what you are printing.

I really think at this point we should be working to improve dpq() in devel.

It is very odd that query extenders like PagerDefault are doing their job in ->execute() instead of ->preExecute(). That is what actually makes the difference in the pasted output above, because I appended the dpq() line after the query had been executed. Moving the dpq() to the same location as the ->debug() method results in the same output (however, dpq() doesn't run ->prefixTables()).

I don't understand why query extenders like PagerDefault don't act in ->preExecute() instead. This means that hook_query_alter() implementations cannot alter the query additions of the extender, and thus, one cannot alter the limit/offset of a pager query, because it's effectively hardcoded in the extenders ->execute() override method.

Attached patch performs that preExecute() change, and also tries to expose debug() on a potentially extended SelectQuery class.....

--
OT:
The more I dive into our current OOP patterns and usage in D7, the more I'm seriously worried. My last hope was that at least database query extenders would introduce a pattern that is actually suitable for Drupal's modularity, but now, after figuring out how they actually work, it looks like we're wrapping semi-usual Drupal coding patterns with a giant, overly complex OO layer, which still does not allow multiple modules to alter and customize multiple individual aspects of the object/thing at hand. This means we are intentionally preventing and disallowing Drupal's key strength (modularity and customization) for the sole purpose of using OOP. From what I've learned, I fully agree with statements like "PHP != Javascript, so such contrib-additions are not really feasible." and it makes me very sad. And angry, because even though we know the hard limitations that OO PHP imposes on us, we're still trying to use it for more and more stuff, entirely ignoring or not understanding that these actions will have extremely bad long-term consequences for the Drupal project at glance.

FWIW, I'd like to see and join a "Drupal OOP" task force to finally figure out The OOP Pattern™ that works for Drupal's modularity and customization. Each and every attempt I've seen so far destroys Drupal.

@sun - berdir gave me a bit more detail about this at #934052-13: Add a dpq() function for helpful debugging of DBTNG queries, and properly log pager queries, too and replies. He'd be a good person to review this patch.

This thread is interesting an I'm glad to know about dpq().

I'm trying (failing completely) to debug a broken EntityFieldQuery. It can't be passed to dpq(). Does anyone have any idea how I might be able to debug that? (re: #1440312: Image style previews don't show when using a private file system)

In case anyone is still interested in this... I created a Drupal 10 feature request @ #3313355: Allow the database query log to be dispatched as log events for dispatching an event as queries are logged - as the ability to subscribe to events can be helpful for anyone instrumenting the db layer.