dbtng_example.module

<?php

/**
 * @file
 * This is an example outlining how a module can make use of the new DBTNG
 * database API in Drupal 7.
 *
 * @todo Demonstrate transaction usage.
 *
 * General documentation is available at
 * @link database Database abstraction layer documentation @endlink and
 * at @link http://drupal.org/node/310069 @endlink.
 */

/**
 * @defgroup dbtng_example Example: Database (DBTNG)
 * @ingroup examples
 * @{
 * Database examples, including DBTNG.
 *
 * 'DBTNG' means 'Database: The Next Generation.' Yes, Drupallers are nerds.
 *
 * General documentation is available at
 * @link database.inc database abstraction layer documentation @endlink and
 * at @link http://drupal.org/node/310069 Database API @endlink.
 *
 * The several examples here demonstrate basic database usage.
 *
 * In Drupal 6, the recommended method to save or update an entry in the
 * database was drupal_write_record() or db_query().
 *
 * In Drupal 7 and forward, the usage of db_query()
 * for INSERT, UPDATE, or DELETE is deprecated, because it is
 * database-dependent. Instead specific functions are provided to perform these
 * operations: db_insert(), db_update(), and db_delete() do the job now.
 * (Note that drupal_write_record() is also deprecated.)
 *
 * db_insert() example:
 * @code
 *   // INSERT INTO {dbtng_example} (name, surname) VALUES('John, 'Doe')
 *   db_insert('dbtng_example')
 *     ->fields(array('name' => 'John', 'surname' => 'Doe'))
 *     ->execute();
 * @endcode
 *
 * db_update() example:
 * @code
 *   // UPDATE {dbtng_example} SET name = 'Jane' WHERE name = 'John'
 *   db_update('dbtng_example')
 *     ->fields(array('name' => 'Jane'))
 *     ->condition('name', 'John')
 *     ->execute();
 * @endcode
 *
 * db_delete() example:
 * @code
 *   // DELETE FROM {dbtng_example} WHERE name = 'Jane'
 *   db_delete('dbtng_example')
 *     ->condition('name', 'Jane')
 *     ->execute();
 * @endcode
 *
 * See @link database Database Abstraction Layer @endlink
 * @see db_insert()
 * @see db_update()
 * @see db_delete()
 * @see drupal_write_record()
 */

/**
 * Save an entry in the database.
 *
 * The underlying DBTNG function is db_insert().
 *
 * In Drupal 6, this would have been:
 * @code
 *   db_query(
 *     "INSERT INTO {dbtng_example} (name, surname, age)
 *       VALUES ('%s', '%s', '%d')",
 *     $entry['name'],
 *     $entry['surname'],
 *     $entry['age']
 *   );
 * @endcode
 *
 * Exception handling is shown in this example. It could be simplified
 * without the try/catch blocks, but since an insert will throw an exception
 * and terminate your application if the exception is not handled, it is best
 * to employ try/catch.
 *
 * @param array $entry
 *   An array containing all the fields of the database record.
 *
 * @see db_insert()
 */
function dbtng_example_entry_insert($entry) {
  $return_value = NULL;
  try {
    $return_value = db_insert('dbtng_example')
      ->fields($entry)
      ->execute();
  } catch (Exception $e) {
    drupal_set_message(t('db_insert failed. Message = %message, query= %query', array(
      '%message' => $e
        ->getMessage(),
      '%query' => $e->query_string,
    )), 'error');
  }
  return $return_value;
}

/**
 * Update an entry in the database.
 *
 * The former, deprecated techniques used db_query() or drupal_write_record():
 * @code
 *  drupal_write_record('dbtng_example', $entry, $entry['pid']);
 * @endcode
 *
 * @code
 *  db_query(
 *    "UPDATE {dbtng_example}
 *     SET name = '%s', surname = '%s', age = '%d'
 *     WHERE pid = %d",
 *     $entry['pid']
 *  );
 * @endcode
 *
 * @param array $entry
 *   An array containing all the fields of the item to be updated.
 *
 * @see db_update()
 */
function dbtng_example_entry_update($entry) {
  try {

    // db_update()...->execute() returns the number of rows updated.
    $count = db_update('dbtng_example')
      ->fields($entry)
      ->condition('pid', $entry['pid'])
      ->execute();
  } catch (Exception $e) {
    drupal_set_message(t('db_update failed. Message = %message, query= %query', array(
      '%message' => $e
        ->getMessage(),
      '%query' => $e->query_string,
    )), 'error');
  }
  return $count;
}

/**
 * Delete an entry from the database.
 *
 * The usage of db_query is deprecated except for static queries.
 * Formerly, a deletion might have been accomplished like this:
 * @code
 *  db_query("DELETE FROM {dbtng_example} WHERE pid = %d", $entry['pid]);
 * @endcode
 *
 * @param array $entry
 *   An array containing at least the person identifier 'pid' element of the
 *   entry to delete.
 *
 * @see db_delete()
 */
function dbtng_example_entry_delete($entry) {
  db_delete('dbtng_example')
    ->condition('pid', $entry['pid'])
    ->execute();
}

/**
 * Read from the database using a filter array.
 *
 * In Drupal 6, the standard function to perform reads was db_query(), and
 * for static queries, it still is.
 *
 * db_query() used an SQL query with placeholders and arguments as parameters.
 *
 * @code
 *  // Old way
 *  $query = "SELECT * FROM {dbtng_example} n WHERE n.uid = %d AND name = '%s'";
 *  $result = db_query($query, $uid, $name);
 * @endcode
 *
 * Drupal 7 DBTNG provides an abstracted interface that will work with a wide
 * variety of database engines.
 *
 * db_query() is deprecated except when doing a static query. The following is
 * perfectly acceptable in Drupal 7. See
 * @link http://drupal.org/node/310072 the handbook page on static queries @endlink
 *
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   db_query(
 *     "SELECT * FROM {dbtng_example} WHERE uid = :uid and name = :name",
 *     array(':uid' => 0, ':name' => 'John')
 *   )->execute();
 * @endcode
 *
 * But for more dynamic queries, Drupal provides the db_select() API method, so
 * there are several ways to perform the same SQL query. See the
 * @link http://drupal.org/node/310075 handbook page on dynamic queries. @endlink
 *
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   db_select('dbtng_example')
 *     ->fields('dbtng_example')
 *     ->condition('uid', 0)
 *     ->condition('name', 'John')
 *     ->execute();
 * @endcode
 *
 * Here is db_select with named placeholders:
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   $arguments = array(':name' => 'John', ':uid' => 0);
 *   db_select('dbtng_example')
 *     ->fields('dbtng_example')
 *     ->where('uid = :uid AND name = :name', $arguments)
 *     ->execute();
 * @endcode
 *
 * Conditions are stacked and evaluated as AND and OR depending on the type of
 * query. For more information, read the conditional queries handbook page at:
 * http://drupal.org/node/310086
 *
 * The condition argument is an 'equal' evaluation by default, but this can be
 * altered:
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE age > 18
 *   db_select('dbtng_example')
 *     ->fields('dbtng_example')
 *     ->condition('age', 18, '>')
 *     ->execute();
 * @endcode
 *
 * @param array $entry
 *   An array containing all the fields used to search the entries in the table.
 *
 * @return object
 *   An object containing the loaded entries if found.
 *
 * @see db_select()
 * @see db_query()
 * @see http://drupal.org/node/310072
 * @see http://drupal.org/node/310075
 */
function dbtng_example_entry_load($entry = array()) {

  // Read all fields from the dbtng_example table.
  $select = db_select('dbtng_example', 'example');
  $select
    ->fields('example');

  // Add each field and value as a condition to this query.
  foreach ($entry as $field => $value) {
    $select
      ->condition($field, $value);
  }

  // Return the result in object format.
  return $select
    ->execute()
    ->fetchAll();
}

/**
 * Select only certain fields from the database
 *
 * As with any database query we should only bring in the data we need.
 * DBTNG gives us the field method that expects the table name followed by an
 * array of the fields we want, in this case the table dbtng_example and
 * the fields name and age.
 *
 */
function dbtng_example_selective_list() {
  $output = '';

  // Bring in two fields from the dbtng_example table for the uid 1.
  $select = db_select('dbtng_example')
    ->fields('dbtng_example', array(
    'name',
    'age',
  ))
    ->condition('uid', 1)
    ->execute();
  $rows = array();
  foreach ($select as $entry) {

    // Sanitize the data before handing it off to the theme layer.
    $rows[] = array_map('check_plain', (array) $entry);

    // Make a table for them.
    $header = array(
      t('Name'),
      t('Age'),
    );
    $output .= theme('table', array(
      'header' => $header,
      'rows' => $rows,
    ));
  }
  return $output;
}

/**
 * Render a filtered list of entries in the database.
 *
 * DBTNG also helps processing queries that return several rows, providing the
 * found objects in the same query execution call.
 *
 * This function queries the database using a JOIN between users table and the
 * example entries, to provide the username that created the entry, and creates
 * a table with the results, processing each row.
 *
 * SELECT
 *  e.pid as pid, e.name as name, e.surname as surname, e.age as age
 *  u.name as username
 * FROM
 *  {dbtng_example} e
 * JOIN
 *  users u ON e.uid = u.uid
 * WHERE
 *  e.name = 'John' AND e.age > 18
 *
 * @see db_select()
 * @see http://drupal.org/node/310075
 */
function dbtng_example_advanced_list() {
  $output = '';
  $select = db_select('dbtng_example', 'e');

  // Join the users table, so we can get the entry creator's username.
  $select
    ->join('users', 'u', 'e.uid = u.uid');

  // Select these specific fields for the output.
  $select
    ->addField('e', 'pid');
  $select
    ->addField('u', 'name', 'username');
  $select
    ->addField('e', 'name');
  $select
    ->addField('e', 'surname');
  $select
    ->addField('e', 'age');

  // Filter only persons named "John".
  $select
    ->condition('e.name', 'John');

  // Filter only persons older than 18 years.
  $select
    ->condition('e.age', 18, '>');

  // Make sure we only get items 0-49, for scalability reasons.
  $select
    ->range(0, 50);

  // Now, loop all these entries and show them in a table. Note that there is no
  // db_fetch_* object or array function being called here. Also note that the
  // following line could have been written as
  // $entries = $select->execute()->fetchAll() which would return each selected
  // record as an object instead of an array.
  $entries = $select
    ->execute()
    ->fetchAll(PDO::FETCH_ASSOC);
  if (!empty($entries)) {
    $rows = array();
    foreach ($entries as $entry) {

      // Sanitize the data before handing it off to the theme layer.
      $rows[] = array_map('check_plain', $entry);
    }

    // Make a table for them.
    $header = array(
      t('Id'),
      t('Created by'),
      t('Name'),
      t('Surname'),
      t('Age'),
    );
    $output .= theme('table', array(
      'header' => $header,
      'rows' => $rows,
    ));
  }
  else {
    drupal_set_message(t('No entries meet the filter criteria (Name = "John" and Age > 18).'));
  }
  return $output;
}

/**
 * Implements hook_help().
 *
 * Show some help on each form provided by this module.
 */
function dbtng_example_help($path) {
  $output = '';
  switch ($path) {
    case 'examples/dbtng':
      $output = t('Generate a list of all entries in the database. There is no filter in the query.');
      break;
    case 'examples/dbtng/selectivelist':
      $output = t('Only bring in certain fields with your select query.') . ' ';
      $output .= t('Only the name and age are brought in with this query. We should only bring in the fields we need rather than always using *');
      break;
    case 'examples/dbtng/advanced':
      $output = t('A more complex list of entries in the database.') . ' ';
      $output .= t('Only the entries with name = "John" and age older than 18 years are shown, the username of the person who created the entry is also shown.');
      break;
    case 'examples/dbtng/update':
      $output = t('Demonstrates a database update operation.');
      break;
    case 'examples/dbtng/add':
      $output = t('Add an entry to the dbtng_example table.');
      break;
    case 'examples/dbtng/grouping_list':
      $output = t('Groups the result set by the specified field and render a list of entries in the database. e.g The records will be displayed in grouping format for column "name" and COUNT("name") is used as aggregate function');
      break;
  }
  return $output;
}

/**
 * Implements hook_menu().
 *
 * Set up calls to drupal_get_form() for all our example cases.
 */
function dbtng_example_menu() {
  $items = array();
  $items['examples/dbtng'] = array(
    'title' => 'DBTNG Example',
    'page callback' => 'dbtng_example_list',
    'access callback' => TRUE,
  );
  $items['examples/dbtng/list'] = array(
    'title' => 'List',
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => -10,
  );
  $items['examples/dbtng/selectivelist'] = array(
    'title' => 'Selective List',
    'page callback' => 'dbtng_example_selective_list',
    'access callback' => TRUE,
    'type' => MENU_LOCAL_TASK,
    'weight' => -9,
  );
  $items['examples/dbtng/add'] = array(
    'title' => 'Add entry',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'dbtng_example_form_add',
    ),
    'access callback' => TRUE,
    'type' => MENU_LOCAL_TASK,
    'weight' => -4,
  );
  $items['examples/dbtng/update'] = array(
    'title' => 'Update entry',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'dbtng_example_form_update',
    ),
    'type' => MENU_LOCAL_TASK,
    'access callback' => TRUE,
    'weight' => -5,
  );
  $items['examples/dbtng/advanced'] = array(
    'title' => 'Advanced list',
    'page callback' => 'dbtng_example_advanced_list',
    'access callback' => TRUE,
    'type' => MENU_LOCAL_TASK,
  );
  $items['examples/dbtng/grouping_list'] = array(
    'title' => 'Grouping list',
    'page callback' => 'dbtng_example_grouping_list',
    'access callback' => TRUE,
    'type' => MENU_LOCAL_TASK,
  );
  return $items;
}

/**
 * Render a list of entries in the database.
 */
function dbtng_example_list() {
  $output = '';

  // Get all entries in the dbtng_example table.
  if ($entries = dbtng_example_entry_load()) {
    $rows = array();
    foreach ($entries as $entry) {

      // Sanitize the data before handing it off to the theme layer.
      $rows[] = array_map('check_plain', (array) $entry);
    }

    // Make a table for them.
    $header = array(
      t('Id'),
      t('uid'),
      t('Name'),
      t('Surname'),
      t('Age'),
    );
    $output .= theme('table', array(
      'header' => $header,
      'rows' => $rows,
    ));
  }
  else {
    drupal_set_message(t('No entries have been added yet.'));
  }
  return $output;
}

/**
 * Prepare a simple form to add an entry, with all the interesting fields.
 */
function dbtng_example_form_add($form, &$form_state) {
  $form = array();
  $form['add'] = array(
    '#type' => 'fieldset',
    '#title' => t('Add a person entry'),
  );
  $form['add']['name'] = array(
    '#type' => 'textfield',
    '#title' => t('Name'),
    '#size' => 15,
  );
  $form['add']['surname'] = array(
    '#type' => 'textfield',
    '#title' => t('Surname'),
    '#size' => 15,
  );
  $form['add']['age'] = array(
    '#type' => 'textfield',
    '#title' => t('Age'),
    '#size' => 5,
    '#description' => t("Values greater than 127 will cause an exception. Try it - it's a great example why exception handling is needed with DTBNG."),
  );
  $form['add']['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Add'),
  );
  return $form;
}

/**
 * Submit handler for 'add entry' form.
 */
function dbtng_example_form_add_submit($form, &$form_state) {
  global $user;

  // Save the submitted entry.
  $entry = array(
    'name' => $form_state['values']['name'],
    'surname' => $form_state['values']['surname'],
    'age' => $form_state['values']['age'],
    'uid' => $user->uid,
  );
  $return = dbtng_example_entry_insert($entry);
  if ($return) {
    drupal_set_message(t("Created entry @entry", array(
      '@entry' => print_r($entry, TRUE),
    )));
  }
}

/**
 * Sample UI to update a record.
 */
function dbtng_example_form_update($form, &$form_state) {
  $form = array(
    '#prefix' => '<div id="updateform">',
    '#suffix' => '</div>',
  );
  $entries = dbtng_example_entry_load();
  $keyed_entries = array();
  if (empty($entries)) {
    $form['no_values'] = array(
      '#value' => t("No entries exist in the table dbtng_example table."),
    );
    return $form;
  }
  foreach ($entries as $entry) {
    $options[$entry->pid] = t("@pid: @name @surname (@age)", array(
      '@pid' => $entry->pid,
      '@name' => $entry->name,
      '@surname' => $entry->surname,
      '@age' => $entry->age,
    ));
    $keyed_entries[$entry->pid] = $entry;
  }
  $default_entry = !empty($form_state['values']['pid']) ? $keyed_entries[$form_state['values']['pid']] : $entries[0];
  $form_state['entries'] = $keyed_entries;
  $form['pid'] = array(
    '#type' => 'select',
    '#options' => $options,
    '#title' => t('Choose entry to update'),
    '#default_value' => $default_entry->pid,
    '#ajax' => array(
      'wrapper' => 'updateform',
      'callback' => 'dbtng_example_form_update_callback',
    ),
  );
  $form['name'] = array(
    '#type' => 'textfield',
    '#title' => t('Updated first name'),
    '#size' => 15,
    '#default_value' => $default_entry->name,
  );
  $form['surname'] = array(
    '#type' => 'textfield',
    '#title' => t('Updated last name'),
    '#size' => 15,
    '#default_value' => $default_entry->surname,
  );
  $form['age'] = array(
    '#type' => 'textfield',
    '#title' => t('Updated age'),
    '#size' => 4,
    '#default_value' => $default_entry->age,
    '#description' => t("Values greater than 127 will cause an exception"),
  );
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Update'),
  );
  return $form;
}

/**
 * AJAX callback handler for the pid select.
 *
 * When the pid changes, populates the defaults from the database in the form.
 */
function dbtng_example_form_update_callback($form, $form_state) {
  $entry = $form_state['entries'][$form_state['values']['pid']];

  // Setting the #value of items is the only way I was able to figure out
  // to get replaced defaults on these items. #default_value will not do it
  // and shouldn't.
  foreach (array(
    'name',
    'surname',
    'age',
  ) as $item) {
    $form[$item]['#value'] = $entry->{$item};
  }
  return $form;
}

/**
 * Submit handler for 'update entry' form.
 */
function dbtng_example_form_update_submit($form, &$form_state) {
  global $user;

  // Save the submitted entry.
  $entry = array(
    'pid' => $form_state['values']['pid'],
    'name' => $form_state['values']['name'],
    'surname' => $form_state['values']['surname'],
    'age' => $form_state['values']['age'],
    'uid' => $user->uid,
  );
  $count = dbtng_example_entry_update($entry);
  drupal_set_message(t("Updated entry @entry (@count row updated)", array(
    '@count' => $count,
    '@entry' => print_r($entry, TRUE),
  )));
}

/**
 * This function groups the result set by the specified field and render a
 * list of entries in the database
 */
function dbtng_example_grouping_list() {
  $result = dbtng_example_execute_group_by_select_query();
  return dbtng_example_render_resultset_as_table($result);
}

/**
 * The code below will result in the following query
 * SELECT ex.pid AS pid, ex.uid AS uid, ex.name AS name, ex.surname AS surname,
 * ex.age AS age FROM {dbtng_example} ex GROUP BY ex.age
 */
function dbtng_example_execute_group_by_select_query() {
  $select = db_select('dbtng_example', 'ex');

  // Select these specific fields for the output.
  $select
    ->fields('ex', array(
    'name',
  ));

  // Count('name') how many times same name comes in table .
  $select
    ->addExpression('COUNT(ex.name)', 'count');

  // 'n.name' is used for groupBy clause.
  $select
    ->groupBy("ex.name");
  $output = $select
    ->execute()
    ->fetchAll();
  return $output;
}

/**
 * This function renders a resultset as table
 */
function dbtng_example_render_resultset_as_table($result) {
  $rows = array();
  if ($result) {
    foreach ($result as $row) {

      // Sanitize the data before handing it off to the theme layer.
      $rows[] = array_map('check_plain', (array) $row);
    }
  }
  return dbtng_example_convert_resultset_to_table_render_array($rows);
}

/**
 * This function renders array for table 'dbtng_example'
 */
function dbtng_example_convert_resultset_to_table_render_array($rows = array()) {
  $header = array(
    t('Name'),
    t('Count'),
  );
  $output = theme('table', array(
    'header' => $header,
    'rows' => $rows,
    'empty' => t('No records found'),
  ));
  return $output;
}

/**
 * @} End of "defgroup dbtng_example".
 */