wrappers for database operations

D7 ships with pdo and a serie of query classes (SelectQuery, DeleteQuery,...) in the db layer. So you write queries by using objects.
Before D7 it was possible to write queries compatible for both D5&6. With the growing adoption for D7 in drush, all queries must be written twice, and this involves a lot of spaguetti when the query is not a predefined one but needs to be dinamically built.

Well, there aren't so much database queries in drush, but most complex are in drush_core_watchdog_* and I'm struggling my head with watchdog :) ..... also other queries could be introduced in a future... and it can be a benefit for drush contribs.

option 1
-------
Easiest queries are SELECT, as D7 provides db_query() for select statements.

Main difference with D5&6 is placeholders. D7 accepts unnamed placeholders (with ?) or named, with :column.
So a hypothetical function drush_db_query() accepting queries with named placeholders and replacing them if running on D5&6 will help.
This is a quick and dirty approach to replacing placeholders (not so dirty if you know your query args are ok) with some code copied from D7's db code:

<?php
function _drush_replace_query_placeholders($where, $args) {
  foreach ($args as $key => $data) {
    if (is_array($data)) {
      $new_keys = array();
      foreach ($data as $i => $value) {
        $new_keys[$key . '_' . $i] = $value;
      }
      $where = preg_replace('#' . $key . '\b#', implode(', ', array_keys($new_keys)), $query);
      unset($args[$key]);
      $args += $new_keys;
    }
  }
  foreach ($args as $key => $data) {
    $where = str_replace($key, $data, $where);
  }
  
  return $where;
}
??>

... but this is only useful for selects.

option 2
-------
D7 provides also db_select(), db_delete(), etc. those return Query objects. An option is defining those in d5,6 environments and also a "fake" class with the same interface but running db_query() on ->execute(). Actually it won't be a fake but perhaps classes inheriting from SelectQuery, DeleteQuery, etc. In any case this is perhaps a unnecessary overhead for drush (at least in terms of # lines of code).

With this option all queries will be written for D7 and will be converted as needed if running D5|6.

option 3
-------
this is an extension to option #1. D7 Query classes have a method for WHERE predicates with placeholders. In fact, you can do

db_select($table)->fields()->where('id = :id', array(':id' => $id));

So the idea here is to provide wrapper functions: drush_db_select(), drush_db_delete(), etc.

This is a running code for drush_db_select() that I've developed and tested while working on #170583: watchdog fixes and improvements:

<?php
function drush_db_select($table, $fields = '*', $where = NULL, $args = NULL, $start = 0, $length = NULL, $order_by_field = NULL, $order_by_direction = 'ASC') {

  if (drush_drupal_major_version() >= 7) {
    if ($fields == '*') {
      $fields = array();
    }
    $query = db_select($table, $table)
      ->fields($table, $fields);
    if (!empty($where)) {
      $query = $query->where($where, $args);
    }
    if (!is_null($length)) {
      $query = $query->range($start, $length);
    }
    if (!is_null($order_by_field)) {
      $query = $query->orderBy($order_by_field, $order_by_direction);
    }
    return $query->execute();
  }
  else {
    if ($fields != '*') {
      $fields = implode(',', $fields);
    }
    $query = "SELECT $fields FROM {$table}";
    if (!empty($where)) {
      $where = _drush_replace_query_placeholders($where, $args);
      $query .= " WHERE ".$where;
    }
    if (!is_null($count)) {
      $limit = " LIMIT $count";
      if (!is_null($start)) {
        $limit .= " OFFSET $start";
      }
      $query .= $limit;
    }
    if (!is_null($order_by_field)) {
      $query .= "ORDER BY $order_by_field $order_by_direction";
    }
    return db_query($query, $args);
  }
}
?>

I also have drush_db_delete() and no use case for update or inserts. All of this still needs some work (ej: limit/offset/range), and some improvements can be added (ej: DISTINCT), but first I'd like to know if you see this of interest or is acceptable to write twice wherever a query is needed.