dbtng_example.module in Examples for Developers 7
Same filename and directory in other branches
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 Database abstraction layer documentation and at http://drupal.org/node/310069.
File
dbtng_example/dbtng_example.moduleView source
<?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".
*/Functions
Name |
Description |
|---|---|
| dbtng_example_advanced_list | Render a filtered list of entries in the database. |
| dbtng_example_convert_resultset_to_table_render_array | This function renders array for table 'dbtng_example' |
| dbtng_example_entry_delete | Delete an entry from the database. |
| dbtng_example_entry_insert | Save an entry in the database. |
| dbtng_example_entry_load | Read from the database using a filter array. |
| dbtng_example_entry_update | Update an entry in the database. |
| dbtng_example_execute_group_by_select_query | 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 |
| dbtng_example_form_add | Prepare a simple form to add an entry, with all the interesting fields. |
| dbtng_example_form_add_submit | Submit handler for 'add entry' form. |
| dbtng_example_form_update | Sample UI to update a record. |
| dbtng_example_form_update_callback | AJAX callback handler for the pid select. |
| dbtng_example_form_update_submit | Submit handler for 'update entry' form. |
| dbtng_example_grouping_list | This function groups the result set by the specified field and render a list of entries in the database |
| dbtng_example_help | Implements hook_help(). |
| dbtng_example_list | Render a list of entries in the database. |
| dbtng_example_menu | Implements hook_menu(). |
| dbtng_example_render_resultset_as_table | This function renders a resultset as table |
| dbtng_example_selective_list | Select only certain fields from the database |