function bb_sql_parse($sql)
{
require_once BACKPRESS_PATH . 'class.bp-sql-schema-parser.php';
bb_log_deprecated('function', __FUNCTION__, 'BP_SQL_Schema_Parser::parse');
return BP_SQL_Schema_Parser::parse($sql);
}
/**
* Evaluates the difference between a given set of SQL queries and real database structure
*/
function delta($db_object, $queries, $ignore = false, $execute = true)
{
if (!$db_object || !is_object($db_object) || !(is_a($db_object, 'BPDB') || is_a($db_object, 'BPDB_Multi') || is_a($db_object, 'BPDB_Hyper'))) {
return __('Passed variable is not a BackPress database object.');
}
if (!($_queries = BP_SQL_Schema_Parser::parse($queries))) {
return __('No schema available.');
}
// Set up default elements to ignore
$ignore_defaults = array('tables' => array(), 'columns' => array(), 'indices' => array());
// Add the elements to ignore that were passed to the function
if (!$ignore || !is_array($ignore)) {
$ignore = $ignore_defaults;
} else {
if (isset($ignore['tables']) && is_array($ignore['tables'])) {
$ignore['tables'] = array_merge($ignore_defaults['tables'], $ignore['tables']);
}
if (isset($ignore['columns']) && is_array($ignore['columns'])) {
$ignore['columns'] = array_merge($ignore_defaults['columns'], $ignore['columns']);
}
if (isset($ignore['indices']) && is_array($ignore['indices'])) {
$ignore['indices'] = array_merge($ignore_defaults['indices'], $ignore['indices']);
}
}
// Build an array of $db_object registered tables and their database identifiers
$_tables = $db_object->tables;
$db_object_tables = array();
foreach ($_tables as $_table_id => $_table_name) {
if (is_array($_table_name) && isset($db_object->db_servers['dbh_' . $_table_name[0]])) {
$db_object_tables[$db_object->{$_table_id}] = 'dbh_' . $_table_name[0];
} else {
$db_object_tables[$db_object->{$_table_id}] = 'dbh_global';
}
}
unset($_tables, $_table_id, $_table_name);
$alterations = array();
// Loop through table queries
if (isset($_queries['tables'])) {
foreach ($_queries['tables'] as $_new_table_name => $_new_table_data) {
if (in_array($_new_table_name, $ignore['tables'])) {
continue;
}
// See if the table is custom and registered in $db_object under a custom database
if (isset($db_object_tables[$_new_table_name]) && $db_object_tables[$_new_table_name] != 'dbh_global' && isset($db_object->db_servers[$db_object_tables[$_new_table_name]]['ds'])) {
// Force the database connection
$_dbhname = $db_object->db_servers[$db_object_tables[$_new_table_name]]['ds'];
$db_object->_force_dbhname = $_dbhname;
} else {
$_dbhname = 'dbh_global';
}
// Fetch the existing table column structure from the database
$db_object->suppress_errors();
if (!($_existing_table_columns = $db_object->get_results('DESCRIBE `' . $_new_table_name . '`;', ARRAY_A))) {
$db_object->suppress_errors(false);
// The table doesn't exist, add it and then continue to the next table
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'create_table', 'message' => __('Creating table'), 'query' => $_new_table_data['query_tidy']);
continue;
}
$db_object->suppress_errors(false);
// Add an index to the existing columns array
$__existing_table_columns = array();
foreach ($_existing_table_columns as $_existing_table_column) {
// Remove 'Key' from returned column structure
unset($_existing_table_column['Key']);
$__existing_table_columns[$_existing_table_column['Field']] = $_existing_table_column;
}
$_existing_table_columns = $__existing_table_columns;
unset($__existing_table_columns);
// Loop over the columns in this table and look for differences
foreach ($_new_table_data['columns'] as $_new_column_name => $_new_column_data) {
if (isset($ignore['columns'][$_new_table_name]) && in_array($_new_column_name, $ignore['columns'][$_new_table_name])) {
continue;
}
if (!in_array($_new_column_data, $_existing_table_columns)) {
// There is a difference
if (!isset($_existing_table_columns[$_new_column_name])) {
// The column doesn't exist, so add it
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'add_column', 'message' => sprintf(__('Adding column: %s'), $_new_column_name), 'column' => $_new_column_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD COLUMN ' . BP_SQL_Schema_Parser::get_column_definition($_new_column_data) . ';');
continue;
}
// Adjust defaults on columns that allow defaults
if ($_new_column_data['Default'] !== $_existing_table_columns[$_new_column_name]['Default'] && !in_array(strtolower($_new_column_data['Type']), array('tinytext', 'text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob'))) {
// Change the default value for the column
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'set_default', 'message' => sprintf(__('Setting default on column: %s'), $_new_column_name), 'column' => $_new_column_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` ALTER COLUMN `' . $_new_column_name . '` SET DEFAULT \'' . $_new_column_data['Default'] . '\';');
// Don't continue, overwrite this if the next conditional is met
}
if ($_new_column_data['Type'] !== $_existing_table_columns[$_new_column_name]['Type'] || ('YES' === $_new_column_data['Null'] xor 'YES' === $_existing_table_columns[$_new_column_name]['Null']) || $_new_column_data['Extra'] !== $_existing_table_columns[$_new_column_name]['Extra']) {
// Change the structure for the column
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'change_column', 'message' => sprintf(__('Changing column: %s'), $_new_column_name), 'column' => $_new_column_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` CHANGE COLUMN `' . $_new_column_name . '` ' . BP_SQL_Schema_Parser::get_column_definition($_new_column_data) . ';');
}
}
}
unset($_existing_table_columns, $_new_column_name, $_new_column_data);
// Fetch the table index structure from the database
if (!($_existing_table_indices = $db_object->get_results('SHOW INDEX FROM `' . $_new_table_name . '`;', ARRAY_A))) {
continue;
}
// Add an index to the existing columns array and organise by index name
$__existing_table_indices = array();
foreach ($_existing_table_indices as $_existing_table_index) {
// Remove unused parts from returned index structure
unset($_existing_table_index['Collation'], $_existing_table_index['Cardinality'], $_existing_table_index['Packed'], $_existing_table_index['Null'], $_existing_table_index['Comment']);
$__existing_table_indices[$_existing_table_index['Key_name']][] = $_existing_table_index;
}
$_existing_table_indices = $__existing_table_indices;
unset($__existing_table_indices);
// Loop over the indices in this table and look for differences
foreach ($_new_table_data['indices'] as $_new_index_name => $_new_index_data) {
if (isset($ignore['indices'][$_new_table_name]) && in_array($_new_index_name, $ignore['indices'][$_new_table_name])) {
continue;
}
if (!in_array($_new_index_data, $_existing_table_indices)) {
// There is a difference
if (!isset($_existing_table_indices[$_new_index_name])) {
// The index doesn't exist, so add it
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'add_index', 'message' => sprintf(__('Adding index: %s'), $_new_index_name), 'index' => $_new_index_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition($_new_index_data) . ';');
continue;
}
if ($_new_index_data !== $_existing_table_indices[$_new_index_name]) {
// The index is incorrect, so drop it and add the new one
if ($_new_index_name == 'PRIMARY') {
$_drop_index_name = 'PRIMARY KEY';
} else {
$_drop_index_name = 'INDEX `' . $_new_index_name . '`';
}
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'drop_index', 'message' => sprintf(__('Dropping index: %s'), $_new_index_name), 'index' => $_new_index_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` DROP ' . $_drop_index_name . ';');
unset($_drop_index_name);
$alterations[$_dbhname][$_new_table_name][] = array('action' => 'add_index', 'message' => sprintf(__('Adding index: %s'), $_new_index_name), 'index' => $_new_index_name, 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition($_new_index_data) . ';');
}
}
}
unset($_new_index_name, $_new_index_data);
// Go back to the default database connection
$db_object->_force_dbhname = false;
}
unset($_new_table_name, $_new_table_data, $_dbhname);
}
// Now deal with the sundry INSERT and UPDATE statements (if any)
if (isset($_queries['insert']) && is_array($_queries['insert']) && count($_queries['insert'])) {
foreach ($_queries['insert'] as $_table_name => $_inserts) {
foreach ($_inserts as $_insert) {
$alterations['dbh_global'][$_table_name][] = array('action' => 'insert', 'message' => __('Inserting data'), 'query' => $_insert);
}
unset($_insert);
}
unset($_table_name, $_inserts);
}
if (isset($_queries['update']) && is_array($_queries['update']) && count($_queries['update'])) {
foreach ($_queries['update'] as $_table_name => $_updates) {
foreach ($_updates as $_update) {
$alterations['dbh_global'][$_table_name][] = array('action' => 'update', 'message' => __('Updating data'), 'query' => $_update);
}
unset($_update);
}
unset($_table_name, $_updates);
}
// Initialise an array to hold the output messages
$messages = array();
$errors = array();
foreach ($alterations as $_dbhname => $_tables) {
// Force the database connection (this was already checked to be valid in the previous loop)
$db_object->_force_dbhname = $_dbhname;
// Note the database in the return messages
$messages[] = '>>> ' . sprintf(__('Modifying database: %s (%s)'), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host']);
foreach ($_tables as $_table_name => $_alterations) {
// Note the table in the return messages
$messages[] = '>>>>>> ' . sprintf(__('Table: %s'), $_table_name);
foreach ($_alterations as $_alteration) {
// If there is no query, then skip
if (!$_alteration['query']) {
continue;
}
// Note the action in the return messages
$messages[] = '>>>>>>>>> ' . $_alteration['message'];
if (!$execute) {
$messages[] = '>>>>>>>>>>>> ' . __('Skipped');
continue;
}
// Run the query
$_result = $db_object->query($_alteration['query']);
$_result_error = $db_object->get_error();
if ($_result_error) {
// There was an error
$_result =& $_result_error;
unset($_result_error);
$messages[] = '>>>>>>>>>>>> ' . __('SQL ERROR! See the error log for more detail');
$errors[] = __('SQL ERROR!');
$errors[] = '>>> ' . sprintf(__('Database: %s (%s)'), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host']);
$errors[] = '>>>>>> ' . $_result->error_data['db_query']['query'];
$errors[] = '>>>>>> ' . $_result->error_data['db_query']['error'];
} else {
$messages[] = '>>>>>>>>>>>> ' . __('Done');
}
unset($_result);
}
unset($_alteration);
}
unset($_table_name, $_alterations);
}
unset($_dbhname, $_tables);
// Reset the database connection
$db_object->_force_dbhname = false;
return array('messages' => $messages, 'errors' => $errors);
}