4

I have a simple merge query, with one key and three fields, something like:

$query = db_merge('product')
->key(array(
  'id' => $id_product,
))
->fields(array(
  'name'   => $form_state['values']['name'],
  'symbol' => $form_state['values']['symbol'],
  'weight' => $form_state['values']['weight'],
));

Usually it works all right, but some combinations of data passed to it and data already in database cause me headache. I'm sure I would be able to know what's wrong and why, if only I could grab actual SQL query that goes to my database.

Usually I would do:

  kpr((string)$query);

It works well for insert for example, because insert has __toString() well implemented. Sadly, implementation of MergeQuery::__toString is somewhat nonexistent:

public function __toString() {
}

It isn't even a proper implementation of this magic method, as it should always return string, but OK, I can assume it was on purpose. Anyway, this changes nothing: I can't simply cast it to see it.

What other options do I have, to see queries generated by db_merge() / MergeQuery for database interaction debugging?

0

2 Answers 2

3

A MergeQuery just runs a SELECT and INSERT internally - there isn't a single query string available, so the __toString() method doesn't make sense (which is probably why it's short-circuited).

SelectQuery and InsertQuery both implement __toString() fully, so your best bet for debugging would be to temporarily add some code to MergeQuery::execute() to dump the $select and/or $insert query before they're run, so you can inspect the query string.

8
  • It seems it's working. I would prefer to avoid hacking core, but for now it's good enough.
    – Mołot
    Apr 25, 2014 at 8:47
  • Problem is that InsertQuery doesn't invoke an alter hook. You could subclass a bunch of database classes and provide your own debuggable implementation of the relevant methods, but it seems overkill for one-time debugging
    – Clive
    Apr 25, 2014 at 8:49
  • I fully agree... It worked and was straightforward enough, yet I feel reluctant to accept hacking core as an answer. I mean, upvoted of course, but I'll leave it unaccepted for now, in hope of attracting non-hackish answers.
    – Mołot
    Apr 25, 2014 at 10:06
  • 1
    @Mołot Temporary debug code is hardly 'hacking core'; logically the only solution without changing core files is to replace the database class with your own (quite a bit of work). I'm not sure another solution is physically possible, looking at the code (but yeah, of course, don't accept this if you're not happy with it :)
    – Clive
    Apr 25, 2014 at 10:07
  • I have seen disastrous results of "temporary debug", when person who debugged accidentally "cleaned up" one line too much... I'm not sure if it's possible to do it any other way. But hooking variable_set() without hacking core seemed impossible, too, and yet it wasn't even that hard, it only needed one nice spark of craziness ;)
    – Mołot
    Apr 25, 2014 at 10:11
1

Ok, I realise that this is going against the principle of OOP however...

function debug_merge_query($query, $show_placeholder_values = false) {

  // Unprotect object.
  $query_arr = (array) $query;
  $query_str = json_encode($query_arr);
  $query_str = str_replace('\u0000*\u0000', '', $query_str);
  $query_arr = (array) json_decode($query_str);
  //pdie($query_arr);

  if(!isset($query_arr['table'])) {
    drupal_set_message('You cannot execute a merge query whilst it is being debugged.');
    return false;
  }

  // Extract variables
  $table = $query_arr['table'];
  $fields = array();
  $first = true;
  foreach($query_arr['insertFields'] as $k => $v) {
    if($first) {
      $condition_key = $k;
      $condition_val = $v;
      $first = false;
    } else {
      $fields[$k] = $v;
    }
  }

  // Build select query.
  $select_query = db_select($table, $table)
    ->condition($condition_key, $condition_val);
  $select_query->addExpression('1', 'expression');

  if($show_placeholder_values) {
    dpq($select_query);
  } else {
    dpm((string)$select_query);
  }

  $row_exists = $select_query->execute()->fetchField();

  if($row_exists) {
    // Build update query.
    $update_query = db_update($table)
      ->fields($fields)
      ->condition($condition_key, $condition_val);

    $update_query_str = dpq($update_query, TRUE);

    if($show_placeholder_values) {
      $update_query_str = replace_placeholders($update_query_str, $fields);
    }

    dpm($update_query_str);

  } else {
    // Build insert query.
    $fields[$condition_key] = $condition_val;
    $insert_query = db_insert($table)
      ->fields($fields);
    $insert_query->preExecute();

    $insert_query_str = (string) $insert_query;

    if($show_placeholder_values) {
      $insert_query_str = replace_placeholders($insert_query_str, $fields);
    }

    dpm($insert_query_str);

  }

}

function replace_placeholders($query, $values) {
  $x = 0;
  foreach($values as $k => $v) {
    $replace = is_numeric($v) ? $v : '"'.$v.'"';
    $query = str_replace(':db_insert_placeholder_'.$x, $replace, $query);
    $query = str_replace(':db_update_placeholder_'.$x, $replace, $query);
    $x++;
  }
  return $query;
}

So to test this function you can use the following code:

$id_product = 1;

$query = db_merge('product')
  ->key(array(
    'id' => $id_product,
  ))
  ->fields(array(
    'name'   => 'test',
    'symbol' => 'apple',
    'weight' => 12,
  ))
  //->execute()
  ;

debug_merge_query($query, true);
1
  • 1
    I think this proves my point - a patch to core is far better than reproducing all this code in procedural functions :)
    – Clive
    Apr 25, 2014 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.