Using WordPress dbDelta Function

Many of us who develop WordPress plugin might have come across dbDelta function. dbDelta function is usually used when one wish to create table for your WordPress plugin. However, this function might not be that easy to deal with since it is not an official function in WordPress. Nonetheless it is a powerful function that majority of us would want to utilize. In this article, we will talk about dbDelta function and how we can ensure that it perform what it is made to do.

dbDelta Function

Like i mention before in one of my article, dbDelta function has the ability to examine the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates of our plugin. However, unlike many WordPress function, dbDelta function is the most picky and troublesome one.  In order for dbDelta function to work, a few criteria will have to be met.

1. You have to put each field on its own line in your SQL statement.
2. You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.
3. You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.

Well, the above criteria's seem easy to achieve. But wait till it hits you.

Strengthen and Weakness of dbDelta Function

The strength of this function is that we are assure that any modification to our table structure will be shown on the plugin. Hence, we won't have to worry about our user plugin not being updated whenever we change our table structure to accommodate new features. This function which is build by the WordPress community is definitely much more secure than other function that an individual came up with to solve such problem. Hence, the function itself is much more reliable. Using dbDelta also removes the need to request each individual instruction to be execute separately. Code can be sum up and dump into dbDelta function for it to run.

On the other end, like i mention earlier, this can be a real pain in the ass. dbDelta function is not very tolerant against mistakes. Hence, any mistakes made on your SQL query might just fail this function. Furthermore, certain restriction is given to you in order to utilize this function. If you accidentally break such restriction, the function will fail. Moreover, no documentation were provided for this method which makes it much more time consuming to get the hang of it. In case you haven't notice, dbDelta will only update new fields or keys. This means that if you decide to remove any particular field or keys on your table and hoping dbDelta will help you out with it, you are wrong.  And if dbDelta function fail to work for you, debugging it might just be as headache since printing out the message on dbDelta might not work well for you. To make things worst,  Wordpress will mark an error on your plugin if you try to exit(0) on some part of the script instead of stopping and display the printing message.

Using dbDelta function

Initially using dbDelta function wasn't that bad. We just have to be very careful with the spaces given. An example given by WordPress would be the one shown below,

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

Well, if you copy directly and change the field name, it should work nicely for you. But take note of the spaces given. Here are a few example that will cause dbDelta function to fail.

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above contains an extra space between CREATE and TABLE. Hence, instead of one space we have two and dbDelta fail. The same thing might happen if there are an extra space between TABLE and your table name. Another good example might occur on the key level.

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id, time)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above fail due to this:

UNIQUE KEY id (id, time)

the correct writing should be this:

UNIQUE KEY id (id,time)

where there are no spaces between the commons. On the other hand, try to avoid having any spaces between each commons such as these

$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

It is always safe to ensure that all keyword are separated by one space and between each commas there shouldn't be any spacing. Another thing to take note is that every table creation should have a KEY in order for it to work. And like the criteria stated, each field should have its own line like the one shown on WordPress example. And at the end of each instruction there should be semicolon to be safe!

The above are things you should be cautious when using dbDelta. However, i did learn some tricks when reading the code of dbDelta function. If you are creating multiple table or query with dbDelta, it can be done using one call.

#$table_name = 'test1'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test2'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test3'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

The above is similar to having one instruction per dbDelta such as this:

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
#$table_name = 'test1'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);
#$table_name = 'test2'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);
#$table_name = 'test3'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
dbDelta($sql);

Hence, you might want to practice the initial one to make your code run more efficient. Another interesting thing to take note is that every last instruction will not be required to have semicolon. Hence,

#$table_name = 'test2'
$sql .= "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	);";
#$table_name = 'test3'
$sql = "CREATE  TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT , 
	  time bigint(11) DEFAULT '0' NOT NULL , 
	  name tinytext NOT NULL , 
	  text text NOT NULL , 
	  url VARCHAR(55) NOT NULL , 
	  UNIQUE KEY id (id, time)
	)";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

will work. This means that if you have only 1 SQL instruction for dbDelta to run, you can safely remove the semicolon. But if you have many SQL instructions, only the last instruction can exclude semicolon since dbDelta function use semicolon as delimiter for splitting each instruction and remove the last array data if it is empty. Hence, leaving the last semicolon will reduce the step required to complete dbDelta function.

Conclusion

dbDelta in WordPress can be really useful. We just need to be careful not to make those silly mistakes that might just cost us our precious time debugging it. Although it can't remove fields for us, it definitely help us save a lot of time by adding in new fields!