Create database on installation

Question

I'm working on a custom wordpress plugin, but I can't seem to figure out how to create the database when installing the plugin. (Or when updating)

When searching around this is what I came up with, but this doesn't seem to do anything.

function to_install() {
    global $wpdb;
    $sql = "CREATE TABLE to_issues (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(125) NOT NULL
    );";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    add_option('to_db_version', TO_VERSION);
}
function to_install_data() {
    global $wpdb;
    $welcome_name = "Hello";
    $welcom_text = "Install successfull";
}
register_activation_hook(__FILE__, 'to_install');
register_activation_hook(__FILE__, 'to_install_data');

On a side note, when updating the plugin, will this reinstall all the tables? Or will wordpress handle this correctly?

Answer 1

Your SQL command is invalid. The error says:

WordPress database error: [Incorrect table definition; there can be only one auto column and it must be defined as a key] CREATE TABLE to_issues ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(125) NOT NULL )

Try:

$sql ="CREATE TABLE {$wpdb->prefix}to_issues (
    id int(11) unsigned NOT NULL auto_increment,
    name varchar(125) NOT NULL,
    PRIMARY KEY  (id)
    );";

Note that dbDelta() is very particular about what is passed to it. For instance you need two spaces between the PRIMARY KEY and (id).

I have added $wpdb->prefix to the table - this adds the (user specified) WordPress prefix. It's good practise to use this, as some people have more than one WordPress install in their database.

dbDelta

The 'register activation' is hook only fired when the plug-in is activated - but not when the plug-in is upgraded. So when a user deactivates and activates the plug-in, dbDelta($sql) is fired - this is intended. dbDelta (see source) recognises 'CREATE' queries, and first checks if the table already exists - and if it doesn't, it creates the table. If the table does exists, it checks that each of the fields match that in the given $sql, and if not, updates them. If the field doesn't exist, but is specified in your $sql, it is created.

This way you can easily update a table by chaging $sql. However, on updating a plug-in, to_install won't called - so you need to manually call it on admin_init after checking that the current 'database version' (TO_VERSION) is an old one. See this post for a detailed explanation of activation/deactivation/uninstall/update procedures..