Plugin database tables in Wordpress - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

How does a WordPress plugin typically create its database tables?

When a WordPress plugin needs to store custom data, it often creates its own database tables. How does the plugin usually create these tables?

A By running SQL commands directly inside the plugin's main file without any checks.

B By modifying the WordPress core database files manually during plugin activation.

C By using the <code>register_activation_hook</code> to run a function that creates tables with <code>dbDelta()</code> after checking the table doesn't exist.

D By creating tables only when the plugin's admin page is loaded for the first time.

💡 Hint

Think about how plugins safely create tables only once during activation.

WordPress plugins use register_activation_hook to run code when the plugin activates. Inside that code, they use dbDelta() to create or update tables safely. This avoids errors and ensures tables are created only once.

📝 Syntax

intermediate

Identify the correct SQL syntax for creating a plugin table with charset in WordPress

Which SQL statement correctly creates a plugin table with the proper charset and collation in WordPress?

A CREATE TABLE wp_myplugin_data (id INT NOT NULL) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

B CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

C CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

D CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

💡 Hint

WordPress recommends using InnoDB engine and utf8mb4 charset for compatibility.

Option B uses InnoDB engine and utf8mb4 charset with unicode collation, which is the recommended setup for WordPress plugin tables to support emojis and multilingual content.

🔧 Debug

advanced

Why does this plugin table creation code fail to create the table?

Consider this plugin activation code snippet:

global $wpdb;
$table_name = $wpdb->prefix . 'myplugin';
$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

Why might the table not be created?

A The SQL statement has a syntax error: the closing parenthesis is followed by a semicolon inside the string.

B The SQL statement is missing the charset and collation specification, so dbDelta ignores it.

C The <code>dbDelta</code> function requires the SQL string to have the <code>ENGINE=InnoDB</code> clause to create tables.

D The <code>dbDelta</code> function only works if the table name is lowercase without prefix.

💡 Hint

Check the SQL syntax carefully, especially punctuation inside the string.

The SQL string ends with a closing parenthesis followed immediately by a semicolon inside the string. dbDelta expects no semicolon at the end of the SQL statement. This causes the table creation to fail.

❓ state_output

advanced

What is the value of $table_version after plugin activation with version check?

A plugin stores its database version in the option myplugin_db_version. On activation, it runs this code:

global $wpdb;
$table_version = get_option('myplugin_db_version');
if ( $table_version != '1.0' ) {
  // create or update tables
  update_option('myplugin_db_version', '1.0');
}

If the option myplugin_db_version does not exist before activation, what will be its value after activation?

A null

B '' (empty string)

C false

D '1.0'

💡 Hint

Think about what update_option does after the check.

If the option does not exist, get_option returns false, so the condition is true. The plugin then updates the option to '1.0'. After activation, the option value is '1.0'.

🧠 Conceptual

expert

Which approach ensures plugin database tables are updated safely on plugin upgrade?

When a plugin updates to a new version that changes the database schema, what is the best practice to update the plugin's database tables safely?

A Use a version number stored in options to detect schema changes and run incremental <code>dbDelta()</code> calls during admin_init hook.

B Drop the existing tables and recreate them from scratch during plugin activation.

C Manually run ALTER TABLE SQL commands without checking the current schema version.

D Require users to uninstall and reinstall the plugin to update the database tables.

💡 Hint

Think about how to avoid data loss and keep schema in sync.

Storing a database version in options and checking it on admin_init allows the plugin to run dbDelta() safely to update tables incrementally without losing data. Dropping tables or requiring reinstall causes data loss or poor user experience.