A completely new database API has been added

er, this seems critical.

I've started on making this work. This patch just includes updates to db_query("SELECT...") that rewrites conditions. So far it rewrites "field = %x" and "field IN('%s')" conditions. What are the other conditions we need to handle?

This patch beings the work for handling more complex conditional statements. So far it doesn't handle nested conditionals or anything other than AND operators, but it's a start, and should handle a good percentage of the upgrade cases. I'll continue working it to see if I can get those issues included.

I just reviewed and updated this a bit.

it now handles string / numeric literals for both conditions and assignment in insert statements.

http://github.com/Vertice/coder/commit/fb846eb9a77210bd515ba6d6f903ef027...

the patch is on there.

I think there's still some work that needs to happen with this. For coder_upgrade_parser_sql_conditions, I think we can skip nested conditionals for now, but it's important to be able to handle AND, OR, and XOR operators.

There was an error with how literals were being handle in select statements, this patch fixes it.

Just noticed this, too (thanks crell):

When adding a node listing to your module, be sure to use a dynamic query created by db_select() and add a tag of "node_access" to ensure that only nodes to which the user has access are retrieved.

http://api.drupal.org/api/group/node_access/7

So if a select query is in the form of "SELECT ... FROM {node}..." then it should be rewritten into a dynamic query with the tag "node_access".

Haven't read the patch in detail, just a few things to think about. I guess not all of these can be handled automatically...

- Actually, we should check on db_rewrite_sql instead of SELECT .... FROM {node} since there are also other tags (term_access, ....)

- Also, all INSERT/DELETE/UPDATE statements need to be updated to use db_insert()/db_delete()/db_update()

- "while ($row = db_fetch_object($result)) { " became "foreach ($result as $row) {" (and db_fetch_array() needs to set the fetch mode)

- "db_result(db_query())" became "db_query()->fetchField()"

- pager_query() and tablesort_sql() are gone, the query needs to be converted into a dynamic select query and extended with PagerDefault/Tablesort

- hook_db_rewrite_sql() is obviously gone too

- ...

Found another db_query() that fails:

db_query('SELECT * FROM {node} WHERE title = \'%s\'', 'title');

is translated to:

db_query('SELECT * FROM {node} WHERE title = :title', array(':title' => % s, '' => 'title'));

Question?

In the upgrade process what about older update_hook_N(). I ran todays CVS of coder and found

 // TODO update_sql has been removed. Use the database API for any schema or data changes.
  $ret[] = array() /* update_sql("UPDATE {quotes_blocks} SET view_text='" . $view_text . "', view_weight=" . $view_weight . ", show_citation=" . $show_citation . ", max_length=" . $max_len . " WHERE 1=1") */;

this was on a quotes_update_6014. shouldn't upgrade ignore the update_sql function as it does with db_schema translations

@solotandem: the t() function on description

here's a section

function quotes_update_6102() {
  global $db_type;
  $items = $create = array();

  // Skip this if upgrading from 5.x later than this addition.
  if (db_table_exists('quotes_authors')) {
    return $items;
  }

  $schema['quotes_authors'] = array(
    'module' => 'Quotes',
    'description' => t('Quotes authors data.'),
    'fields' => array(
      'aid' => array(
        'description' => t('Author identifier.'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'disp-width' => '10',
        ),
      'name' => array(
        'description' => t('Author of the quote.'),
        'type' => 'varchar',
        'length' => '255',
        'not null' => TRUE,
        ),
      'bio' => array(
        'description' => t("Author's biography."),
        'type' => 'text',
        'not null' => FALSE,
        ),
      ),
    'primary key' => array('aid'),
    'unique keys' => array(
      'name' => array('name')),
    );

  db_create_table('quotes_authors', $schema['quotes_authors']);

  // Add the aid column after the vid column.
  db_add_column($items, 'quotes', 'aid', 'INT UNSIGNED NOT NULL AFTER vid');

  $add = $items[count($items) - 1]['success'];
  if (!$add) {
    drupal_set_message(t('Add column "aid" failed.'), 'error');
    return;
  }

  // Add an index for the aid.
  db_add_index('quotes', 'quotes_aid', array('aid'));
  $add = $items[count($items) - 1]['success'];
  if (!$add) {
    drupal_set_message(t('Add index for "aid" failed.'), 'error');
    return;
  }

  // Get all the authors.
  $result = db_query("SELECT DISTINCT(author) FROM {quotes} ORDER BY author");

  while ($q = db_fetch_array($result)) {
    $author = $q['author'];
    // If the current author field has a mini-bio, split it off.
    $paren = strpos($author, '(');
    $comma = strpos($author, ',');
    $sub = min(($paren === FALSE ? drupal_strlen($author) : $paren), ($comma === FALSE ? drupal_strlen($author) : $comma));
    if ($sub === FALSE) {
      $bio = NULL;
    }
    else {
      $bio = trim(drupal_substr($author, $sub));
      $author = trim(drupal_substr($author, 0, $sub));
    }
    // Add the row to the new table.
    // TODO update_sql has been removed. Use the database API for any schema or data changes.
    $items[] = array() /* update_sql("INSERT INTO {quotes_authors} (name, bio) VALUES ('" . db_escape_string($author) . "', '" . db_escape_string($bio) . "')") */;
    $add = $items[count($items) - 1]['success'];
    if ($add === FALSE) {
      $aid = 'failed';
      $upd = 'bypassed';
    }
    else {
      $aid = db_query("SELECT LAST_INSERT_ID()")->fetchField();
      if ($aid < 1) {
        drupal_set_message(t('Invalid aid returned: ') . $aid, 'error');
      }
      $query = 'UPDATE {quotes} SET aid=' . $aid . " WHERE author='" . db_escape_string($q['author']) . "'";
      // TODO update_sql has been removed. Use the database API for any schema or data changes.
      $items[] = array() /* update_sql($query) */;
    }
  }

from what I can tell, upgrader leaves everything in update_hook_N() as it should except for removing update_sql(). Since 6 is going to be around for a while think it should be left in.

Ummm,

Nevermind, I'm Brain Dead. For some reason I was thinking leaving 6x functions in 7x version.

Pure stupidity on me.

oops, didn't mead to change status

I've been working getting database conversions integrated with SqlParser (http://drupal.org/project/sql_parser), and wanted to post up my progress. Currently this only converts insert/update/delete commands into DBTNG statements using SqlParser. Work still needs to be done to properly convert select commands using SqlParser.

Here's an updated version that implements string to string parsing and converting for select commands. There's still some bugs within sql_parser (if you find any more, please post new issues there), but it seem to be working fairly well.

Solotandem, let me know if I'm going in the right direction and what you think of sql_parser.

Another reroll to keep up with the changes happening in sql_parser. This adds proper error handling so coder_upgrade doesn't blow up if an error is found when parsing a sql statement.

This module has been a huge help but it did miss the db_fetch_array() call. I think linking it to the documentation or a refence to the data in post 8 would be great.

Coder 7.x-1.x is frozen now and will not receive any updates. Coder 8.x-2.x can be used to check code for any Drupal version, Coder 8.x-2.x also supports the phpcbf command to automatically fix conding standard errors. Please check if this issue is still relevant and reopen against that version if necessary.