function _updateMySQL()
{
global $TABLE_PREFIX, $schema;
$escapedTableName = mysql_escape($_REQUEST['tableName']);
// get current column name and type
$oldColumnName = $_REQUEST['fieldname'];
$newColumnName = $_REQUEST['newFieldname'];
$oldColumnType = getMysqlColumnType($_REQUEST['tableName'], $oldColumnName);
$newColumnType = getColumnTypeFor($newColumnName, $_REQUEST['type'], @$_REQUEST['customColumnType']);
// create/alter/remove MySQL columns
$isOldColumn = $oldColumnType;
$isNewColumn = $newColumnType != 'none' && $newColumnType != '';
$doEraseColumn = $isOldColumn && !$isNewColumn;
$doCreateColumn = !$oldColumnType && $isNewColumn;
$doAlterColumn = $isOldColumn && $isNewColumn;
// remove existing index (if any) - always dropping/recreating indexes ensure they match renamed fields, etc
list($oldIndexName, $oldIndexColList) = getIndexNameAndColumnListForField($oldColumnName, $oldColumnType);
$indexExists = (bool) mysql_get_query("SHOW INDEX FROM `{$escapedTableName}` WHERE Key_name = '{$oldIndexName}'");
if ($indexExists) {
mysql_query("DROP INDEX `{$oldIndexName}` ON `{$escapedTableName}`") or die("Error dropping index `{$newIndexName}`:" . htmlencode(mysql_error()));
}
// update table: create, alter, or erase field
if ($doCreateColumn) {
// create field
$query = "ALTER TABLE `" . mysql_escape($_REQUEST['tableName']) . "`\n ADD COLUMN `" . mysql_escape($newColumnName) . "` {$newColumnType}";
$result = mysql_query($query) or die("There was an error creating the MySQL Column, the error was:\n\n" . mysql_error());
} else {
if ($doAlterColumn) {
// change field type
$result = mysql_query("ALTER TABLE `" . mysql_escape($_REQUEST['tableName']) . "`\n CHANGE COLUMN `" . mysql_escape($oldColumnName) . "`\n `" . mysql_escape($newColumnName) . "` {$newColumnType}") or die("There was an error changing the MySQL Column, the error was:\n\n" . mysql_error() . "\n");
} else {
if ($doEraseColumn) {
// erase mysql field
$query = "ALTER TABLE `" . mysql_escape($_REQUEST['tableName']) . "`\n DROP COLUMN `" . mysql_escape($oldColumnName) . "`";
$result = mysql_query($query) or die("There was an error removing the MySQL Column, the error was:\n\n" . mysql_error() . "\n");
}
}
}
// add/re-create index if required
if (@$_REQUEST['indexed']) {
list($newIndexName, $newIndexColList) = getIndexNameAndColumnListForField($newColumnName, $newColumnType);
$result = mysql_query("CREATE INDEX `{$newIndexName}` ON `{$escapedTableName}` {$newIndexColList}") or die("Error creating index `{$newIndexName}`:" . htmlencode(mysql_error()));
}
// update uploads table (rename upload field if it was changed)
$uploadFieldRenamed = $_REQUEST['type'] == 'upload' && $oldColumnName && $oldColumnName != $newColumnName;
if ($uploadFieldRenamed) {
$tableNameWithoutPrefix = getTableNameWithoutPrefix($_REQUEST['tableName']);
$query = "UPDATE `{$TABLE_PREFIX}uploads`";
$query .= " SET fieldName='" . mysql_escape($newColumnName) . "'";
$query .= " WHERE fieldName='" . mysql_escape($oldColumnName) . "' AND";
$query .= " tableName='" . mysql_escape($tableNameWithoutPrefix) . "'";
mysql_query($query) or die("There was an error updating the uploads database:\n\n" . htmlencode(mysql_error()) . "\n");
}
}
function createMissingSchemaTablesAndFields()
{
global $APP, $TABLE_PREFIX;
$schemaTables = getSchemaTables();
$mysqlTables = getMysqlTablesWithPrefix();
// create missing schema tables in mysql
foreach ($schemaTables as $tableName) {
// create mysql table
$mysqlTableName = $TABLE_PREFIX . $tableName;
if (!in_array($mysqlTableName, $mysqlTables)) {
notice(t("Creating MySQL table for schema table: ") . $tableName . "<br/>\n");
$result = mysql_query("CREATE TABLE `" . mysql_escape($mysqlTableName) . "` (num int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (num)) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
if (!$result) {
alert(sprintf("Error creating MySQL table: %s<br/>\\MySQL error was: ", $mysqlTableName) . htmlencode(mysql_error()) . "\n");
}
if (is_resource($result)) {
mysql_free_result($result);
}
// run defaultSqlData if applicable
$defaultSqlFile = DATA_DIR . "/schema/{$tableName}.defaultSqlData.php";
if (file_exists($defaultSqlFile)) {
restoreDatabase($defaultSqlFile, $tableName);
notice(t("Importing default data for schema table: ") . $tableName . "<br/>\n");
}
}
// get schema fieldnames
$schemaFieldnames = array();
$tableSchema = loadSchema($tableName);
foreach ($tableSchema as $name => $valueOrArray) {
if (is_array($valueOrArray)) {
array_push($schemaFieldnames, $name);
}
// only fields has arrays as values
}
// get mysql fieldnames
$mysqlFieldnames = array();
$result = mysql_query("SHOW COLUMNS FROM `" . mysql_escape($mysqlTableName) . "`") or die("MySQL Error: " . htmlencode(mysql_error()) . "\n");
while ($row = mysql_fetch_assoc($result)) {
array_push($mysqlFieldnames, strtolower($row['Field']));
}
if (is_resource($result)) {
mysql_free_result($result);
}
// add missing fieldnames to mysql
$addFieldSQL = '';
foreach ($schemaFieldnames as $fieldname) {
if (!in_array(strtolower($fieldname), $mysqlFieldnames)) {
$columnType = getColumnTypeFor($fieldname, @$tableSchema[$fieldname]['type'], @$tableSchema[$fieldname]['customColumnType']);
if (!$columnType) {
continue;
}
if ($addFieldSQL) {
$addFieldSQL .= ", ";
}
$addFieldSQL .= " ADD COLUMN `" . mysql_escape($fieldname) . "` {$columnType}";
// add index?
if (@$tableSchema[$fieldname]['indexed']) {
list($indexName, $indexColList) = getIndexNameAndColumnListForField($fieldname, $columnType);
$addFieldSQL .= ", ADD INDEX `{$indexName}` {$indexColList}";
}
}
}
if ($addFieldSQL) {
mysql_query("ALTER TABLE `" . mysql_escape($mysqlTableName) . "` {$addFieldSQL}") or die("Error adding fields to '{$mysqlTableName}', the error was:\n\n" . htmlencode(mysql_error()));
notice(t("Adding MySQL fields for schema table:") . " {$tableName}<br/>\n");
}
}
}
