Similar to the creation of configuration options that we covered back in the Creating default user settings on plugin initialization recipe from Chapter 3, User Settings and Administration Pages, custom database tables are typically created when a plugin is activated in a WordPress installation. By using the activation hook, we register code to be executed when the plugin is first activated and when upgrades are performed. When the callback is executed, we will have our first encounter with the global wpdb class. This utility class is instantiated by WordPress and gives us access to a number of methods that can be used to interact with the underlying MySQL website database, as well as to help prevent data-related security risks. These methods vary in complexity, ranging from simple calls that will quickly insert or update records to more complex member functions that require knowledge of SQL commands to produce the expected results.

Before making the call to create the actual table, the activation function makes a call to the get_blog_prefix method of the wpdb class to retrieve the table prefix associated with the website (set to wp_ in a default installation). On retrieval, this prefix is immediately sent to the ch8bt_create_table function to build an SQL command designed to create a new table.

While the SQL command has multiple lines, we can see that it is actually quite simple if we break it down into small sections. The first line of the command specifies that a new table named <prefix>ch8_bug_data should be created if it does not exist already on the server. If the creation takes place, the following five lines specify the name and data type for each field, along with information indicating whether the field can contain a NULL value and what the default value should be in some cases. There is also a special command associated with the bug_id field, called the AUTO_INCREMENT command, which tells the system to automatically populate this field with auto-incrementing values when new records are added to the table. Last, but not least, the last line of the code indicates that the primary key for the table is the bug_id field.

Once the query is ready, it is stored in a variable and executed by calling the query method of the wpdb object. This method executes any SQL command on the website database and returns a numeric value indicating how many rows were affected by the query.