In this chapter we learn about changing the definition of a database table after it has already been installed. This example expands on the previous chapters by adding a new field to the table to collect the date a user last visited a node. We learn more about hook_update_N() as well as db_add_field().

Any Drupal module that provides custom database tables should implement hook_views_data() to describe the schema of those tables to Views. This hook is used to provide information to Views about the fields in a table, their human-readable names, and the types of data (string, integer, date, etc.) stored in each column. You can also tell Views how it should handle sorting, filtering, and formatting the data. Implementations of hook_views_data() can also be used to describe relationships between tables.

If you're creating a module that implements hook_schema() and adds new tables to the database it's a good idea to also add support for Views. Among other things, it'll allow administrators to create any user-facing displays of data from your table using Views. Then, they can be edited without having to write code. Once you've described your table to Views via hook_views_data() Views will be able to provide a way for administrators to construct queries against your data via the UI.

In this tutorial we'll:

• Use hook_views_data() to expose a custom table defined in a Drupal module to Views.
• Learn how to describe different types of data to Views.
• Demonstrate the relationship between hook_views_data() and what a site administrator has access to in the Views UI.

By the end of this tutorial you should know how to describe custom database tables and their fields to the Views module.

In this chapter we learn how to create a new database table. This example walks through creating a module that defines the the properties of our new table and also programatically creating that table. We learn about hook_schema() and hook_update_N() in our module's install file.

Trainers note: This video starts out with a module that implements hook_node_view() and stores data about views in the $_SESSION variable. It also states that we built this earlier in the series. However, that's not true. We did talk about hook_node_view() in https://drupalize.me/videos/event-driven-hook-system, but we never actually wrote the $_SESSION handling part. So, if you're following along and want the same sample code we start with make sure you download the code attached to this node.

In this chapter we learn how to get data out of the database and display it. The example walks through writing a module that creates a page that displays how many nodes are published and unpublished for each content type in our site. We use the db_select() function to accomplish this.

In this chapter we learn other ways to interact with our database beyond the simple db_select() query from the earlier video. This example walks through writing a module that that saves and displays data about how many times a user has visited a specific page. We learn about db_select(), db_insert(), db_update(), db_merge(), db_delete() and introduce the concept of "get and set" helper functions.

Before we can actually get our field to store data for us we need to define what the data that we're going to store looks like. The Field API does this with hook_field_schema(), which uses a very similar syntax to what is used by the hook_schema() that modules can use to define database tables. In this particular case though we're only defining what the column, or columns, that store our specific data will be and allowing the Field Storage API to decide what the structure of the created table, or tables, should be. This allows our field to remain mostly storage system agnostic and frees us from having to worry about things like how the stored field data is connected back to the entity that it belongs to, or how to format our table for proper handling of revision data or translations.

• Documentation for hook_field_schema - https://api.drupal.org/api/drupal/modules%21field%21field.api.php/funct…
• Schema API docs - https://drupal.org/developing/api/schema

Example hook_field_schema() implemenatation.

/**
 * Implements hook_field_schema().
 */
function rgb_field_schema($field) {
  $columns = array(
    'rgb' => array(
      'type' => 'varchar',
      'length' => 6,
      'not null' => FALSE,
    ),
    'label' => array(
      'type' => 'varchar',
      'length' => 128,
      'not null' => FALSE,
    ),
  );

  $indexes = array(
    'rgb' => array('rgb'),
  );

  return array(
    'columns' => $columns,
    'indexes' => $indexes,
  );
}

Additional resources

• hook_field_schema()
• Schema API documentation

In this excerpt from the Overview of Drupal 7, Angie Byron, talks about the Database API in Drupal 7. This was a round table discussion with other Lullabots on the line who ask and answer questions, in addition to Angie's presentation. This lays a good foundation for how the database system works in Drupal and sets you up for the hands-on tutorials that follow.

Additional resources

Database API documentation

Use hooks to update the database tables and alter form elements.

In this video we'll look at adding yet another field handler, but in this instance we will be adding a Views field that does not directly map to an actual field in the database. Instead we will be creating our own variation on some data and adding it as a field that can be used in our views — a field that shows the percentage of page views as a black bar graph.

Add user data to the database from an existing form.

How to update a contributed theme using Composer and the administrative interface for running the Database Updates script.

How to update a contributed module using Composer and the administrative interface for running the Database Updates script.

This video looks at the basics of working with MySQL from the command line. We get into the mysql environment and look at databases, tables and fields. We cover creating and deleting databases, creating a user, and querying within a particular database.

Note: In some places the command line prompt is cut-off. The YouTube version of this video doesn't have the cut-off problem. We are working on getting this fixed, but in the meantime, check out the YouTube version instead.

Command Line Basics 13: Using MySQL from Command Line (youtube.com)

A common task when developing a Drupal site is loading the database into your local development environment. When working with non-Docker local development environments, command line tools or a graphical application are used to load the database dump. These methods also work for Docker with a bit of container configuration. With Docker, you can include all the tooling in containers, reducing the need for utilities on the host OS.

In this tutorial, we'll:

• Outline the challenges for loading a database into a container
• Identify the methods by which a database can be loaded into a container

Drupal creates a line of separation between what is content and what is configuration. The line is such that content is stored only in the database, whereas configuration is maintained by the configuration management system. While cached to the database for performance reasons, configuration can be thought of primarily living in the sync directory as a series of flat files.

This sounds like a perfectly clear distinction in theory, but there are several times where interdependencies appear between content and configuration. Understanding the key places where these interface can help prevent confusion and "disappearing" settings due to a lack of understanding.