WordPress – Tips on Creating and Updating Tables with dbDelta

One of the reasons I LOVE WordPress is that it makes some mundane programming chores very easy, particularly working with the database. WP uses a modified version of the ezSQL to interact with the database, and provides a number of data cleansing functions, both of which are huge time savers.

One other thing that WP provides is the ability to create and update functions with a nifty little function: dbDelta()

I hit a snag with dbDelta today and after a few hours of working my code around and around, I finally went to Google and found my answer. It was right there in the official WordPress instruction for creating tables. (I’ll jump ahead so I don’t keep you in suspense…my problem was that I was using \r for the new line character instead of \n. dbDelta() requires each field in your SQL statement to be on a separate line).

Tips on Using dbDelta()

For starters, here is an example of how you would use dbDelta() in code:

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');  // This will load the proper functions
global $wpdb;
$table_name = $wpdb->prefix . "pxp_ratings";

$sql = "CREATE TABLE " . $table_name . " (
	rating_id BIGINT(20) NOT NULL AUTO_INCREMENT,
	image_id BIGINT(20) NOT NULL,
	rating_type TINYINT(1),
	user_id BIGINT(20),
	user_ip VARCHAR(30) ,
	rating TINYINT(1),
	created_date DATETIME,
	updated_date TIMESTAMP NOT NULL,
	status TINYINT(1) NOT NULL DEFAULT '0',
	PRIMARY KEY  (rating_id),
	INDEX rating_idx (rating_type,image_id)
	)  $charset_collate;";

dbDelta($sql);

The first time this gets executed, your new table should be created. If you need to make a change to a field or an index, you can just change it in your CREATE TABLE code and when the plugin is initialized (you should call your database creation code on activation), it will analyze the differences and update the table with any deltas, hence dbDelta() ;-).

Some Tips

  1. Each field or index you add should be on a new line, as shown above.
    • Note: if you’re adding in the new line character manually — not just hitting ‘enter’ — you need the \\n, not \\r. That’s what got me…I was imploding an array with like
      • $ret = implode(",\r", $fields);
    • It should have been:
      • $sql = implode(“,\n”, $fields);
  2. Two spaces are required between the words PRIMARY KEY and the field(s) that will be your primary key
  3. The official instruction says to use KEY instead of the word INDEX, I’m not sure if they mean just for the PRIMARY KEY or for secondary indices as well. But INDEX seems to work on secondary indices.
  4. The instruction also says you need to have at least one KEY…I’ll take their word for it
  5. BEWARE: if you have multiple indices on your table, you need to manually drop the non-PRIMARY ones (you can only have 1 primary) before you run dbDelta() to update the table. If you don’t it will create duplicate indices. This can be a problem if you have big data…it eats up memory and can slow things down. I’ll show you how to prevent duplicating indices below.
  6. Just so you know, if you change the name of a field, it doesn’t drop the old field, but rather just appends a field with the new name to the bottom of the table

Preventing Duplicate Indices

I’m not 100% sure why sometimes you get duplicate indices and sometimes you don’t, but just to be careful, you may want to drop extra indices before updating your tables. Here’s some commented code that demonstrates how to do that in WordPress.

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');  // This will load the proper functions
global $wpdb;

//Create the IMAGE RATINGS table
			//Drop Old Index
			$table_name = $wpdb->prefix . "pxp_ratings";
			if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") == $table_name) {

				//Image Ratings Table
				$sql = "ALTER TABLE " . $table_name .
					" DROP INDEX rating_idx";
				$wpdb->query($sql);
				
			}
			
			$sql = "CREATE TABLE " . $table_name . " (
				rating_id BIGINT(20) NOT NULL AUTO_INCREMENT,
				image_id BIGINT(20) NOT NULL,
				rating_type TINYINT(1),
				user_id BIGINT(20),
				user_ip VARCHAR(30) ,
				rating TINYINT(1),
				created_date DATETIME,
				updated_date TIMESTAMP NOT NULL,
				status TINYINT(1) NOT NULL DEFAULT '0',
				PRIMARY KEY  (rating_id),
				INDEX rating_idx (rating_type, image_id)
				)  $charset_collate;";
			dbDelta($sql);

Maybe that will be helpful to someone!

Cheers,
Byron

,

Comments are closed.

Powered by WordPress. Designed by Woo Themes