$_index_column ) { preg_match( '@`?(\w+)`?(?:\s*\(\s*(\d+)\s*\))?@i', $_index_column, $_matches_column ); $_indices[$_index_name][] = array( 'Table' => $_table_name, 'Non_unique' => ( 'UNIQUE KEY' == $_index_type || 'PRIMARY' == $_index_name ) ? '0' : '1', 'Key_name' => $_index_name, 'Seq_in_index' => (string) ( $_index_columns_index + 1 ), 'Column_name' => $_matches_column[1], 'Sub_part' => ( isset( $_matches_column[2] ) && $_matches_column[2] ) ? $_matches_column[2] : null, 'Index_type' => ( 'FULLTEXT KEY' == $_index_type ) ? 'FULLTEXT' : 'BTREE' ); } unset( $_index_type, $_index_name, $_index_columns, $_index_columns_index, $_index_column, $_matches_column ); } elseif ( preg_match( "@^`?(\w+)`?\s+(?:(\w+)(?:\s*\(\s*(\d+)\s*\))?(?:\s+(unsigned)){0,1})(?:\s+(NOT\s+NULL))?(?:\s+(auto_increment))?(?:\s+(default)\s+(?:(NULL|'[^']*'|\d+)))?@im", $_column_index, $_matches ) ) { // It's a column // Tidy the NOT NULL $_matches[5] = isset( $_matches[5] ) ? strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[5] ) ) ) : ''; $_columns[$_matches[1]] = array( 'Field' => $_matches[1], 'Type' => ( is_numeric( $_matches[3] ) ) ? $_matches[2] . '(' . $_matches[3] . ')' . ( ( isset( $_matches[4] ) && strtolower( $_matches[4] ) == 'unsigned' ) ? ' unsigned' : '' ) : $_matches[2], 'Null' => ( 'NOT NULL' == strtoupper( $_matches[5] ) ) ? 'NO' : 'YES', 'Default' => ( isset( $_matches[7] ) && 'default' == strtolower( $_matches[7] ) && 'NULL' !== strtoupper( $_matches[8] ) ) ? trim( $_matches[8], "'" ) : null, 'Extra' => ( isset( $_matches[6] ) && 'auto_increment' == strtolower( $_matches[6] ) ) ? 'auto_increment' : '' ); } } unset( $_matches, $_columns_indices, $_column_index ); // Tidy up the original query $_tidy_query = 'CREATE TABLE'; if ( $_if_not_exists ) { $_tidy_query .= ' IF NOT EXISTS'; } $_tidy_query .= ' `' . $_table_name . '` (' . "\n"; foreach ( $_columns as $_column ) { $_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_column_definition( $_column ) . ",\n"; } unset( $_column ); foreach ( $_indices as $_index ) { $_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_index_definition( $_index ) . ",\n"; } $_tidy_query = substr( $_tidy_query, 0, -2 ) . "\n" . ') ' . $_attributes . ';'; // Add to the query array using the table name as the index $description = array( 'query_original' => $query, 'query_tidy' => $_tidy_query, 'columns' => $_columns, 'indices' => $_indices ); unset( $_table_name, $_columns, $_indices, $_tidy_query ); return $description; } /** * Helper function to flatten arrays */ function _flatten_array( $array, $cut_branch = 0, $keep_child_array_keys = true ) { if ( !is_array( $array ) ) { return $array; } if ( empty( $array ) ) { return null; } $temp = array(); foreach ( $array as $k => $v ) { if ( $cut_branch && $k == $cut_branch ) continue; if ( is_array( $v ) ) { if ( $keep_child_array_keys ) { $temp[$k] = true; } $temp += BP_SQL_Schema_Parser::_flatten_array( $v, $cut_branch, $keep_child_array_keys ); } else { $temp[$k] = $v; } } return $temp; } /** * Splits grouped SQL statements into queries within a highly structured array * Only supports CREATE TABLE, INSERT and UPDATE */ function parse( $sql ) { // Only accept strings or arrays if ( is_string( $sql ) ) { // Just pop strings into an array to start with $queries = array( $sql ); } elseif ( is_array( $sql ) ) { // Flatten the array $queries = BP_SQL_Schema_Parser::_flatten_array( $sql, 0, false ); // Remove empty nodes $queries = array_filter( $queries ); } else { return false; } // Clean up the queries $_clean_queries = array(); foreach ( $queries as $_query ) { // Trim space and semi-colons $_query = trim( $_query, "; \t\n\r\0\x0B" ); // If it exists and isn't a number if ( $_query && !is_numeric( $_query ) ) { // Is it more than one query? if ( strpos( ';', $_query ) !== false ) { // Explode by semi-colon foreach ( explode( ';', $_query ) as $_part ) { $_part = trim( $_part ); if ( $_part && !is_numeric( $_part ) ) { // Pull out any commented code // Can't properly deal with /*!4321 FOO `bar` */ version specific inclusion, just includes it regardless of version $_part = preg_replace( '@/\*![0-9]*([^\*]*)\*/@', '$1', $_part ); $_part = preg_replace( '@/\*[^\*]*\*/@', '', $_part ); $_part = preg_replace( '@[\-\-|#].*$@m', '', $_part ); $_clean_queries[] = trim( $_part ) . ';'; } } unset( $_part ); } else { $_clean_queries[] = $_query . ';'; } } } unset( $_query ); if ( !count( $_clean_queries ) ) { return false; } $queries = $_clean_queries; unset( $_clean_queries ); $_queries = array(); foreach ( $queries as $_query ) { // Only process table creation, inserts and updates, capture the table/database name while we are at it if ( !preg_match( '@^(CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?|INSERT\s+INTO|UPDATE)\s+`?([^\s|`]+)`?@im', $_query, $_matches ) ) { continue; } // Tidy up the type so we can switch it $_type = strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[1] ) ) ); $_table_name = trim( $_matches[2] ); unset( $_matches ); switch ( $_type ) { case 'CREATE TABLE': case 'CREATE TABLE IF NOT EXISTS': $_description = BP_SQL_Schema_Parser::describe_table( $_query ); if ( is_array( $_description ) ) { $_queries['tables'][$_table_name] = $_description; } break; case 'INSERT INTO': // Just add the query as is for now $_queries['insert'][$_table_name][] = $_query; break; case 'UPDATE': // Just add the query as is for now $_queries['update'][$_table_name][] = $_query; break; } unset( $_type, $_table_name ); } unset( $_query ); if ( !count( $_queries ) ) { return false; } return $_queries; } /** * 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(), // Just a list of tablenames, including prefix. Does not affect INSERT and UPDATE queries. 'columns' => array(), // Arrays of column names, keyed with the table names, including prefix. 'indices' => array() // Arrays of index names, keyed with the table names, including prefix. ); // 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 ); } } // END class BP_SQL_Schema_Parser