Creating custom tables

In typical circumstances, we create the database tables manually before moving onto the implementation. With the WordPress plugin-based architecture, it's certain that we might need to create custom tables using plugins in the later stages of the projects. Creating custom tables through plugins involves certain predefined procedures recommended by WordPress. Since table creation is a one-time task, we can implement the process on plugin activation or installation. This process is similar to the user role creation process in Chapter 2, Implementing Membership Roles, Permissions, and Features.

We will be using activation-based table creation in this book. However, you can try the installation-based table creation to cater to advanced scenarios. We will add the database table creation functionality into the wpwa-forum plugin we created in Chapter 2, Implementing Membership Roles, Permissions, and Features. So, let's get started by creating a new function called create_custom_tables inside the class-wpwaf-config-manager.php file:

    <?php 
        public function create_custom_tables() { 
                // Creating Database Tables 
        } 
    ?> 

Now we can implement the create_custom_tables function to create the tables we need for our application. Basically, we can execute direct SQL queries using the $wpdb->query function to create all the tables we need. WordPress recommends using a built-in function called dbDelta to create custom tables. This function is located in a file outside the default process, and hence, we need to load it manually within our plugins. Let's create the two tables for our application using the dbDelta function:

    public function create_custom_tables() { 
      global $wpdb; 
      require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); 
      $topic_subscriptions_table = $wpdb->prefix.'topic_subscriptions';        
      if($wpdb->get_var("show tables like '$topic_subscriptions_table'") != $topic_subscriptions_table) { 

        $sql = "CREATE TABLE $topic_subscriptions_table ( 
                  id mediumint(9) NOT NULL AUTO_INCREMENT, 
                  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 
                  user_id mediumint(9) NOT NULL, 
                  topic_id  mediumint(9) NOT NULL, 
                  UNIQUE KEY id (id) 
                );"; 
        dbDelta( $sql ); 
      } 
      // favorite_member_topics table will be created in a similar manner 
    } 

Firstly, we have to include the upgrade.php file to make use of the dbDelta function. The next most important thing is to use the prefix for database tables. By default, WordPress creates a prefix called wp_ for all the tables. It's important to use the existing prefix to maintain consistency and avoid issues in multi-site scenarios. Next, we have to check the existence of a database table using the show tables query. Finally, you can define your table creation query and use the dBDelta function to implement it on the database.

We have the function for creating custom tables for our application. This function needs to be executed through the plugin activation hook. In Chapter 2, Implementing Membership Roles, Permissions, and Features, we executed all activation-related function calls inside the activation_handler function of the WPWAF_Config_Manager class. So, we can use the same function to execute the custom table creation function as follows:

    public function activation_handler(){ 
      $this->add_application_user_roles(); 
      $this->remove_application_user_roles(); 
      $this->add_application_user_capabilities(); 
      $this->flush_application_rewrite_rules(); 
      $this->create_custom_tables(); 
    } 

We created the custom tables using the dbDelta function inside the plugin activation. WordPress recommends the dbDelta function rather than direct SQL queries for table creation since it examines the current table structure, compares it to the desired table structure, and makes the necessary modifications without breaking the existing database tables. Apart from table creation, we can execute quite a few database-related tasks on plugin activation such as altering tables, populating initial data to custom tables, and upgrading the plugin tables.

We looked at the necessity of custom tables for web applications. Even though custom tables offer you more flexibility within WordPress, there will be a considerable number of limitations, as listed here:

• Custom tables are hard to manage in WordPress upgrades.
• WordPress default backups will not include custom tables.
• There are no built-in functions for the accessing database. All the queries, filtering, and validation needs to be done from scratch using the existing $wpdb variable.
• User interfaces for displaying these tables' data need to be created from scratch.

Therefore, you should avoid creating custom tables in all possible circumstances, unless they would be advantageous in the context of your application.

A detailed exploration of the PODS framework will be provided in the next chapter, Chapter 4, Building Blocks of Web Applications.