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"); } } }