<?php
// $Id$

/**
 * @file
 * This is an example outlining how a module can make use of the new database
 * API in Drupal 7: DBTNG.
 *
 */

/**
 * Writting to the database
 * 
 * Since Drupal 6, the recommended method to save or update an entry in the 
 * database is drupal_write_record(), however, using db_query it is still 
 * possible to perform INSERT operations.
 *
 * In Drupal 6, the db_query sentence may have some undefined arguments that
 * will be replaced with know values, passed in order to the function:
 *
 * @code
 *   $query = "INSERT INTO {dbtng_example} (name, surname) VALUES('%s','%s')";
 *   db_query($query, $name, $surname);
 * @endcode 
 *  
 * In Drupal 7, the usage of db_query for INSERT, UPDATE, or DELETE is not 
 * recommended any more, and specific functions are provided to perform this 
 * operations: db_insert(), db_update() and db_delete().
 * 
 * db_insert() does not require any condition, only a group of fields:
 * @code
 *   // INSERT INTO {dbtng_example} (name, surname) VALUES('John, 'Doe')
 *   db_insert('dbtng_example')
 *     ->fields(array('name' => 'John', 'surname' => 'Doe'))
 *     ->execute();
 * @endcode
 * 
 * db_update() requires the fields to be saved, and the condition to select wich
 * rows should be modified:
 * @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() does not require any fields, just the condition statement:
 * @code
 *   // DELETE FROM {dbtng_example} WHERE name = 'Jane'
 *   db_delete('dbtng_example')
 *     ->condition('name', 'Jane')
 *     ->execute();
 * @endcode
 *
 * @see db_insert()
 * @see db_update()
 * @see db_delete()
 * @see drupal_write_record()
 */


/**
 * Save an entry in the database.
 *
 * @param $entry
 *   an array containing all the fields of the entry.
 *
 * @ingroup writting the database
 * @see db_insert
 */
function dbtng_example_entry_save($entry) {
  // The usage of drupal_write_record() is preferred when saving entries in the
  // database:
  // @code
  //   drupal_write_record('example', $entry);
  // @endcode
  //
  // To show you how does the new database system look like, we are going to use
  // an specific db_insert query:
  //
  // In Drupal 6, the creating operation we have been using is:
  // @code
  //   db_query(
  //     "INSERT INTO {dbtng_example} (name, surname, age)
  //       VALUES ('%s', '%s', '%d')",
  //     $entry['name'],
  //     $entry['surname'],
  //     $entry['age']
  //   );
  // @endcode
  // In Drupal 7, there is an specific database API call to manage updates.
  db_insert('dbtng_example')
    ->fields($entry)
    ->execute();

}

/**
 * Update an entry in the database.
 *
 * @param $entry
 *   an array containing all the fields of the entry.
 *
 * @ingroup writting the database
 * @see db_update
 */
function dbtng_example_entry_update($entry) {
  // The usage of drupal_write_record() is preferred when updating entries in
  // the database:
  // @code
  //   drupal_write_record('example', $entry, 'pid');
  // @endcode
  //
  // To show you how does the new database system look like, we are going to use
  // an specific db_update query:
  //
  // In Drupal 6, the update operation we have been using is:
  // @code
  //   db_query(
  //     "UPDATE {dbtng_example}
  //       SET name = '%s', surname = '%s', age = '%d'
  //       WHERE pid = %d",
  //     $entry['pid']
  //   );
  // @endcode
  // In Drupal 7, there is an specific database API call to manage updates.
  db_update('dbtng_example')
    ->fields($entry)
    ->condition('pid', $entry['pid'])
    ->execute();

}

/**
 * Delete an entry in the database.
 *
 * @param $entry
 *   an array containing at least the person identifier 'pid' element.
 *
 * @ingroup writting the database
 * @see db_delete
 */
function dbtng_example_entry_delete($entry) {
  // The usage of db_query now is not recommended any more to make database
  // deletions. This is the way used in Drupal 6 to remove entries:
  // @code
  //   db_query("DELETE FROM {dbtng_example} WHERE pid = %d", $entry['pid]);
  // @endcode
  //
  // In Drupal 6, the deleting operation we have been using is:
  // @code
  //   db_query("DELETE FROM {dbtng_example} WHERE pid = %d", $entry['pid]);
  // @endcode
  // In Drupal 7, there is an specific database API call to manage deletions.
  db_delete('dbtng_example')
    ->condition('pid', $entry['pid'])
    ->execute();

}

/**
 * Reading the database
 *
 * Reading the database is more complex than just writting, because of the
 * number of possible combinations.
 *
 * In Drupal 6, the standard function to perform read queries is db_query().
 *
 * db_query() used a query string as parameter, being this query as more SQL99
 * complaint as possible. The query string should include all the SQL parts
 * required for the query: SELECT (what), FROM, JOINS (and from) , WHERE
 * (conditions), ORDER and LIMIT. Additionally, other db_query helpers are
 * available to write especific SQL code, db_query_range() is an example.
 *
 * This query string used in Drupal 6 supports unamed placeholders, that should
 * be passed to db_query as a single array or a serie of parameters but they
 * must be in correct order.
 * @code
 *  $query = "SELECT * FROM {dbtng_example} n WHERE n.uid = %d AND name = '%s'";
 *  $result = db_query($query, $uid, $name);
 * @endcode
 * This call returns a database resource that must be pulled using:
 * db_object(), db_fetch_array() or db_result() depending on the query.
 *
 * Drupal 7 DBTNG provides a better and more complex interface, allowing the
 * query creation process to be easier to write, read and understand for long
 * and complex queries.
 *
 * db_query() can be used for static, and dynamic / conditional queries, and may
 * return an object, array or result information without additional API calls.
 *
 * @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 select statements, Drupal provides the db_select() API method. This
 * end up in several ways to perform the same SQL query.
 *
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   db_select('dbtng_example')
 *     ->condition('uid', 0)
 *     ->condition('name', 'John')
 *     ->execute();
 * @endcode
 *
 * DBTNG also accepts strings for condition evaluation. Note that in Drupa 7,
 * the unnamed placeholders are a single '?' character, instead of '%s', %d
 * or previously used placeholders.
 * This is the same query but now using a where condition
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   db_select('dbtng_example')
 *     ->where('uid = ? AND name = ?', array(0, 'John'))
 *     ->execute();
 * @endcode
 *
 * Using named placeholders is preferred to avoid errors or security issues in
 * the database layer. When using named placeholders, there order of the
 * arguments is not important.
 * @code
 *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
 *   $arguments = array(':name' => 'John', ':uid' => 0);
 *   db_select('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 about this, you can 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')
 *     ->condition('age', 18, '>')
 *     ->execute();
 * @endcode
 *
 * @see db_query()
 * @see db_select()
 */

/**
 * Read an entry in the database.
 *
 * @param $entry
 *   an array containing all the fields used to search the entry in the table.
 * @return
 *   an array containing the loaded entry if found.
 *
 * @ingroup reading the database
 * @see db_select
 */
function dbtng_example_entry_load($entry) {
  /**
   * In Drupal 6, we have used this code to make as an example implementation
   * of this function:
   * @code
   *   $cond = array();
   *   $arguments = array();
   *
   *   // Turn the conditions into a query.
   *   foreach ($entry as $key => $value) {
   *     $cond[] = db_escape_table($key) ." = '%s'";
   *     $arguments[] = $value;
   *   }
   *   $cond = implode(' AND ', $cond);
   *   $query = "SELECT * FROM {dbtng_example} WHERE ";
   *   return db_fetch_array(db_query($query. $cond, $arguments));
   * @endcode
   *
   * Using DBTNG things are much easier.
   */

  // Read from the dbtng_example table
  $select = db_select('dbtng_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 array format.
  return $select->execute()->fetchAssoc();
}