[wp-hackers] Table updater wish

[Stephen O'Connor]

Sorry Matt, I didn't realize you wanted actual working code. Here's some 
functions:

function get_tables( $filter = "." ) {
    global $wpdb;
    $tables = $wpdb->get_results("SHOW TABLES", ARRAY_A);
    $i = array();
    foreach($tables as $table) {
        foreach($table as $table_title) {
            if( preg_match("/$filter/", $table_title) ) {
                $i[] = $table_title;
            }
        }
    }
    return $i;
}

function list_fields( $table_name ) {
    global $wpdb;
    $columns = $wpdb->get_results("SHOW COLUMNS FROM $table_name");
    $j = array();
    foreach( $columns as $i=>$column ) {
        $j[$column->Field] = array($column->Type, $column->Null, 
$column->Key, $column->Default, $column->Extra);
    }
    return $j;
}

function database_compare( $your_db = array() ) {
    global $table_prefix;
    global $wpdb;
    $table_list = get_tables("^$table_prefix");
    $my_db = array();
    foreach( $table_list as $table ) {
        $my_db[$table] = list_fields( $table );
    }
    foreach( $your_db as $table => $fields ) {
        if( ! isset( $my_db[$table] ) ) {
            // create the table
            $query = "CREATE TABLE $table (";
            foreach( $fields as $field_name => $field_type ) {
                $query .= "\n$field_name $field_type";
            }
            $query .= ");";
        } else {
            foreach( $fields as $field_name => $field_type ) {
                if( ! isset( $my_db[$table][$field_name] ) ) {
                    // create the field
                    $wpdb->query("ALTER TABLE $table ADD $field_name 
$field_type");
                } elseif( $my_db[$table][$field_name] != $field_attr ) {
                    // field is the wrong type
                    // TODO: need migration script
                }
            }
        }
    }
}

All you should need is database_compare(). Pass it an array of the 
database you want to build:

array(
    "table_name" => array(
        "field_name" => "field_type",
        "field_name2" => "field_type"
    ),
    "table_name2" => array(
        [more of the same]
    )
);

There are two shortcomings of this script. It can't handle specific 
attributes such as PRIMARY and UNIQUE and it can't migrate from one 
data-type to another. If anyone has any ideas on how to fix this, I 
would love to check it out.

- Stephen