Monday, August 1, 2011

Database Changes in Drupal 7 from Drupal 6

Drupal 7 introduces a completely new database API, utilizing a number of dynamic query builders and formal prepared statements. The following Drupal 6 functions and hooks were removed: db_affected_rows(), db_distinct_field(), db_error(), db_last_insert_id(), db_placeholders(), db_lock_table(), db_prefix_tables(), db_result(), db_fetch_*(), db_version(), db_rewrite_sql(), hook_db_rewrite_sql(), pager_query(), tablesort_sql(), and others.
For full information, read the Database API guide. A few common examples of Drupal 6 to 7 conversion are also covered below.

Normal SELECT queries:
<?php
 // Drupal 6 
$result = db_query("SELECT nid, title FROM {node} WHERE uid = %d AND type IN (" . db_placeholders(array('page', 'story'), 'varchar') . ")", 5, 'page', 'story');

// Drupal 7 

$result = db_query("SELECT nid, title FROM {node} WHERE uid = :uid AND type IN (:type)", array(
 
':uid' => 5,
 
':type' => array('page', 'story'),
));

?>

Iterating a result set from db_query():
<?php 
// Drupal 6
 while ($record = db_fetch_object($result)) {
 
// Do stuff with $record, which is an object}


// Drupal 7

 foreach ($result as $record) {
 
// Do stuff with $record, which is an object}

 ?>

Insert statements:
<?php 
// Drupal 6 
db_query("INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (%d, '%s', %f)", 5, 'hello world', 3.14);$id = db_last_insert_id();

// Drupal 7 

$id = db_insert('mytable')
  ->
fields(array(
   
'intvar' => 5,
   
'stringvar' => 'hello world',
   
'floatvar' => 3.14,
  ))
  ->
execute(); 

?>

Update statements:
<?php 
// Drupal 6 
db_query("UPDATE {node} SET title='%s', status=%d WHERE uid=%d", 'hello world', 1, 5);

// Drupal 7 

db_update('node')
  ->
fields(array('title' => 'hello world', 'status' => 1))
  ->
condition('uid', 5)
  ->
execute();

 ?>

Delete statements:
<?php 
// Drupal 6 
db_query("DELETE FROM {node} WHERE uid=%d AND created < %d", 5, REQUEST_TIME - 3600);

// Drupal 7 

db_delete('node')
  ->
condition('uid', 5)
  ->
condition('created', REQUEST_TIME - 3600, '<')
  ->
execute(); 

?>
Also note that dynamic queries that have tags may be altered by any module that implements hook_query_alter() or hook_query_TAG_alter(). This takes the place of hook_db_rewrite_sql() in Drupal 6.

file_validate_extensions() enforces check for uid=1

(issue) In 6.x the function for validating file extensions would bypass this check for the uid=1 user. This has been removed in 7.x. If your module depended on this behavior you need to check the user's uid and conditionally specify the file_validate_extensions() validator.

1 comment: