public function setup( $verbose ) {
$db = wfGetDB( DB_MASTER );
SNMDBHelper::reportProgress( "Setting up NotifyMe database ...\n", $verbose );
extract( $db->tableNames( 'smw_nm_monitor', 'smw_nm_query', 'smw_nm_relations', 'smw_nm_rss' ) );
// page_id, monitored page id
SNMDBHelper::setupTable( $smw_nm_monitor, array(
'notify_id' => 'INT(8) UNSIGNED NOT NULL',
'page_id' => 'INT(8) UNSIGNED NOT NULL' ), $db, $verbose );
SNMDBHelper::setupIndex( $smw_nm_monitor, array( 'page_id' ), $db );
SNMDBHelper::setupTable( $smw_nm_query, array(
'notify_id' => 'INT(8) UNSIGNED NOT NULL KEY AUTO_INCREMENT',
'user_id' => 'INT(8) UNSIGNED NOT NULL',
'delegate' => 'BLOB',
'name' => 'VARCHAR(255) binary NOT NULL',
'rep_all' => 'TINYINT(1) NOT NULL default \'1\'',
'show_all' => 'TINYINT(1) NOT NULL default \'0\'',
'query' => 'BLOB NOT NULL',
'nm_sql' => 'BLOB',
'nm_hierarchy' => 'BLOB',
'enable' => 'TINYINT(1) NOT NULL default \'0\'' ), $db, $verbose );
SNMDBHelper::setupIndex( $smw_nm_query, array( 'user_id' ), $db );
// page_id, related page / property id in notify query
SNMDBHelper::setupTable( $smw_nm_relations, array(
'notify_id' => 'INT(8) UNSIGNED NOT NULL',
'smw_id' => 'INT(8) UNSIGNED NOT NULL',
// 0 category, 1 instance, 2 property
'type' => 'INT(8) UNSIGNED NOT NULL',
'subquery' => 'INT(8) UNSIGNED NOT NULL default \'0\'' ), $db, $verbose );
SNMDBHelper::setupIndex( $smw_nm_relations, array( 'smw_id', 'notify_id' ), $db );
SNMDBHelper::setupTable( $smw_nm_rss, array(
'msg_id' => 'INT(8) UNSIGNED NOT NULL KEY AUTO_INCREMENT',
'mailed' => 'TINYINT(1) NOT NULL default \'0\'',
'user_id' => 'INT(8) UNSIGNED',
'notify_id' => 'INT(8) UNSIGNED',
'title' => 'VARCHAR(255) binary NOT NULL',
'link' => 'BLOB',
'notify' => 'BLOB NOT NULL',
'timestamp' => 'VARCHAR(14) binary NOT NULL' ), $db, $verbose );
SNMDBHelper::setupIndex( $smw_nm_rss, array( 'user_id' ), $db );
SNMDBHelper::reportProgress( "... done!\n", $verbose );
}
/**
* Make sure the table of the given name has the given fields, provided
* as an array with entries fieldname => typeparams. typeparams should be
* in a normalised form and order to match to existing values.
*
* The function returns an array that includes all columns that have been
* changed. For each such column, the array contains an entry
* columnname => action, where action is one of 'up', 'new', or 'del'
* If the table was already fine or was created completely anew, an empty
* array is returned (assuming that both cases require no action).
*
* NOTE: the function partly ignores the order in which fields are set up.
* Only if the type of some field changes will its order be adjusted explicitly.
*
* @param string $primaryKeys
* This optional string specifies the primary keys if there is more
* than one. This is a comma separated list of column names. The primary
* keys are not altered, if the table already exists.
*/
public static function setupTable($table, $fields, $db, $verbose, $primaryKeys = "")
{
global $wgDBname;
SNMDBHelper::reportProgress("Setting up table {$table} ...\n", $verbose);
if ($db->tableExists($table) === false) {
// create new table
$sql = 'CREATE TABLE ' . $wgDBname . '.' . $table . ' (';
$first = true;
foreach ($fields as $name => $type) {
if ($first) {
$first = false;
} else {
$sql .= ',';
}
$sql .= $name . ' ' . $type;
}
if (!empty($primaryKeys)) {
$sql .= ", PRIMARY KEY(" . $primaryKeys . ")";
}
$sql .= ') TYPE=myisam';
$db->query($sql, 'SNMDBHelper::setupTable');
SNMDBHelper::reportProgress(" ... new table created\n", $verbose);
return array();
} else {
// check table signature
SNMDBHelper::reportProgress(" ... table exists already, checking structure ...\n", $verbose);
$res = $db->query('DESCRIBE ' . $table, 'SNMDBHelper::setupTable');
$curfields = array();
$result = array();
while ($row = $db->fetchObject($res)) {
$type = strtoupper($row->Type);
if (substr($type, 0, 8) == 'VARCHAR(') {
$type .= ' binary';
// just assume this to be the case for VARCHAR, avoid collation checks
}
if ($row->Null != 'YES') {
$type .= ' NOT NULL';
}
if ($row->Key == 'PRI') {
// / FIXME: updating "KEY" is not possible, the below query will fail in this case.
$type .= ' KEY';
}
if ($row->Extra == 'auto_increment') {
$type .= ' AUTO_INCREMENT';
}
if ($row->Default != '') {
$type .= ' default \'' . $row->Default . '\'';
}
$curfields[$row->Field] = $type;
}
$position = 'FIRST';
foreach ($fields as $name => $type) {
if (!array_key_exists($name, $curfields)) {
SNMDBHelper::reportProgress(" ... creating column {$name} ... ", $verbose);
$db->query("ALTER TABLE {$table} ADD `{$name}` {$type} {$position}", 'SNMDBHelper::setupTable');
$result[$name] = 'new';
SNMDBHelper::reportProgress("done \n", $verbose);
} elseif ($curfields[$name] != $type) {
// && stripos("auto_increment", $type) == -1) {
SNMDBHelper::reportProgress(" ... changing type of column {$name} from '{$curfields[$name]}' to '{$type}' ... ", $verbose);
$db->query("ALTER TABLE {$table} CHANGE `{$name}` `{$name}` {$type} {$position}", 'SNMDBHelper::setupTable');
$result[$name] = 'up';
$curfields[$name] = false;
SNMDBHelper::reportProgress("done.\n", $verbose);
} else {
SNMDBHelper::reportProgress(" ... column {$name} is fine\n", $verbose);
$curfields[$name] = false;
}
$position = "AFTER {$name}";
}
foreach ($curfields as $name => $value) {
if ($value !== false) {
// not encountered yet --> delete
SNMDBHelper::reportProgress(" ... deleting obsolete column {$name} ... ", $verbose);
$db->query("ALTER TABLE {$table} DROP COLUMN `{$name}`", 'SNMDBHelper::setupTable');
$result[$name] = 'del';
SNMDBHelper::reportProgress("done.\n", $verbose);
}
}
SNMDBHelper::reportProgress(" ... table {$table} set up successfully.\n", $verbose);
return $result;
}
}
