BP_SQL_Schema_Parser

Parses SQL schema statements for comparison to real table structures.

Defined (1)

The class is defined in the following location(s).

/bp-forums/bbpress/bb-includes/backpress/class.bp-sql-schema-parser.php  
  1. class BP_SQL_Schema_Parser 
  2. /** 
  3. * Builds a column definition as used in CREATE TABLE statements from 
  4. * an array such as those returned by DESCRIBE `foo` statements 
  5. */ 
  6. function get_column_definition( $column_data ) 
  7. if ( !is_array( $column_data ) ) { 
  8. return $column_data; 
  9.  
  10. $null = ''; 
  11. if ( $column_data['Null'] != 'YES' ) { 
  12. $null = 'NOT NULL'; 
  13.  
  14. $default = ''; 
  15.  
  16. // Defaults aren't allowed at all on certain column types 
  17. if ( !in_array( 
  18. strtolower( $column_data['Type'] ),  
  19. array( 'tinytext', 'text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob' ) 
  20. ) ) { 
  21. if ( $column_data['Null'] == 'YES' && $column_data['Default'] === null ) { 
  22. $default = 'default NULL'; 
  23. } elseif ( preg_match( '@^\d+$@', $column_data['Default'] ) ) { 
  24. $default = 'default ' . $column_data['Default']; 
  25. } elseif ( is_string( $column_data['Default'] ) || is_float( $column_data['Default'] ) ) { 
  26. $default = 'default \'' . $column_data['Default'] . '\''; 
  27.  
  28. $column_definition = '`' . $column_data['Field'] . '` ' . $column_data['Type'] . ' ' . $null . ' ' . $column_data['Extra'] . ' ' . $default; 
  29. return preg_replace( '@\s+@', ' ', trim( $column_definition ) ); 
  30.  
  31. /** 
  32. * Builds an index definition as used in CREATE TABLE statements from 
  33. * an array similar to those returned by SHOW INDEX FROM `foo` statements 
  34. */ 
  35. function get_index_definition( $index_data ) 
  36. if ( !is_array( $index_data ) ) { 
  37. return $index_data; 
  38.  
  39. if ( !count( $index_data ) ) { 
  40. return $index_data; 
  41.  
  42. $_name = '`' . $index_data[0]['Key_name'] . '`'; 
  43.  
  44. if ( $index_data[0]['Index_type'] == 'BTREE' && $index_data[0]['Key_name'] == 'PRIMARY' ) { 
  45. $_type = 'PRIMARY KEY'; 
  46. $_name = ''; 
  47. } elseif ( $index_data[0]['Index_type'] == 'BTREE' && !$index_data[0]['Non_unique'] ) { 
  48. $_type = 'UNIQUE KEY'; 
  49. } elseif ( $index_data[0]['Index_type'] == 'FULLTEXT' ) { 
  50. $_type = 'FULLTEXT KEY'; 
  51. } else { 
  52. $_type = 'KEY'; 
  53.  
  54. $_columns = array(); 
  55. foreach ( $index_data as $_index ) { 
  56. if ( $_index['Sub_part'] ) { 
  57. $_columns[] = '`' . $_index['Column_name'] . '`(' . $_index['Sub_part'] . ')'; 
  58. } else { 
  59. $_columns[] = '`' . $_index['Column_name'] . '`'; 
  60. $_columns = join( ', ', $_columns ); 
  61.  
  62. $index_definition = $_type . ' ' . $_name . ' (' . $_columns . ')'; 
  63. return preg_replace( '@\s+@', ' ', $index_definition ); 
  64.  
  65. /** 
  66. * Returns a table structure from a raw sql query of the form "CREATE TABLE foo" etc. 
  67. * The resulting array contains the original query, the columns as would be returned by DESCRIBE `foo` 
  68. * and the indices as would be returned by SHOW INDEX FROM `foo` on a real table 
  69. */ 
  70. function describe_table( $query ) 
  71. // Retrieve the table structure from the query 
  72. if ( !preg_match( '@^CREATE\s+TABLE(\s+IF\s+NOT\s+EXISTS)?\s+`?([^\s|`]+)`?\s+\((.*)\)\s*([^\)|;]*)\s*;?@ims', $query, $_matches ) ) { 
  73. return $query; 
  74.  
  75. $_if_not_exists = $_matches[1]; 
  76.  
  77. // Tidy up the table name 
  78. $_table_name = trim( $_matches[2] ); 
  79.  
  80. // Tidy up the table columns/indices 
  81. $_columns_indices = trim( $_matches[3], " \t\n\r\0\x0B, " ); 
  82. // Split by commas not followed by a closing parenthesis ")", using fancy lookaheads 
  83. $_columns_indices = preg_split( '@, (?!(?:[^\(]+\)))@ms', $_columns_indices ); 
  84. $_columns_indices = array_map( 'trim', $_columns_indices ); 
  85.  
  86. // Tidy the table attributes 
  87. $_attributes = preg_replace( '@\s+@', ' ', trim( $_matches[4] ) ); 
  88. unset( $_matches ); 
  89.  
  90. // Initialise some temporary arrays 
  91. $_columns = array(); 
  92. $_indices = array(); 
  93.  
  94. // Loop over the columns/indices 
  95. foreach ( $_columns_indices as $_column_index ) { 
  96. if ( preg_match( '@^(PRIMARY\s+KEY|UNIQUE\s+(?:KEY|INDEX)|FULLTEXT\s+(?:KEY|INDEX)|KEY|INDEX)\s+(?:`?(\w+)`?\s+)*\((.+?)\)$@im', $_column_index, $_matches ) ) { 
  97. // It's an index 
  98.  
  99. // Tidy the type 
  100. $_index_type = strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[1] ) ) ); 
  101. $_index_type = str_replace( 'INDEX', 'KEY', $_index_type ); 
  102. // Set the index name 
  103. $_index_name = ( 'PRIMARY KEY' == $_matches[1] ) ? 'PRIMARY' : $_matches[2]; 
  104. // Split into columns 
  105. $_index_columns = array_map( 'trim', explode( ', ', $_matches[3] ) ); 
  106.  
  107. foreach ( $_index_columns as $_index_columns_index => $_index_column ) { 
  108. preg_match( '@`?(\w+)`?(?:\s*\(\s*(\d+)\s*\))?@i', $_index_column, $_matches_column ); 
  109.  
  110. $_indices[$_index_name][] = array( 
  111. 'Table' => $_table_name,  
  112. 'Non_unique' => ( 'UNIQUE KEY' == $_index_type || 'PRIMARY' == $_index_name ) ? '0' : '1',  
  113. 'Key_name' => $_index_name,  
  114. 'Seq_in_index' => (string) ( $_index_columns_index + 1 ),  
  115. 'Column_name' => $_matches_column[1],  
  116. 'Sub_part' => ( isset( $_matches_column[2] ) && $_matches_column[2] ) ? $_matches_column[2] : null,  
  117. 'Index_type' => ( 'FULLTEXT KEY' == $_index_type ) ? 'FULLTEXT' : 'BTREE' 
  118. ); 
  119. unset( $_index_type, $_index_name, $_index_columns, $_index_columns_index, $_index_column, $_matches_column ); 
  120.  
  121. } 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 ) ) { 
  122. // It's a column 
  123.  
  124. // Tidy the NOT NULL 
  125. $_matches[5] = isset( $_matches[5] ) ? strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[5] ) ) ) : ''; 
  126.  
  127. $_columns[$_matches[1]] = array( 
  128. 'Field' => $_matches[1],  
  129. 'Type' => ( is_numeric( $_matches[3] ) ) ? $_matches[2] . '(' . $_matches[3] . ')' . ( ( isset( $_matches[4] ) && strtolower( $_matches[4] ) == 'unsigned' ) ? ' unsigned' : '' ) : $_matches[2],  
  130. 'Null' => ( 'NOT NULL' == strtoupper( $_matches[5] ) ) ? 'NO' : 'YES',  
  131. 'Default' => ( isset( $_matches[7] ) && 'default' == strtolower( $_matches[7] ) && 'NULL' !== strtoupper( $_matches[8] ) ) ? trim( $_matches[8], "'" ) : null,  
  132. 'Extra' => ( isset( $_matches[6] ) && 'auto_increment' == strtolower( $_matches[6] ) ) ? 'auto_increment' : '' 
  133. ); 
  134. unset( $_matches, $_columns_indices, $_column_index ); 
  135.  
  136. // Tidy up the original query 
  137. $_tidy_query = 'CREATE TABLE'; 
  138. if ( $_if_not_exists ) { 
  139. $_tidy_query .= ' IF NOT EXISTS'; 
  140. $_tidy_query .= ' `' . $_table_name . '` (' . "\n"; 
  141. foreach ( $_columns as $_column ) { 
  142. $_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_column_definition( $_column ) . ", \n"; 
  143. unset( $_column ); 
  144. foreach ( $_indices as $_index ) { 
  145. $_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_index_definition( $_index ) . ", \n"; 
  146. $_tidy_query = substr( $_tidy_query, 0, -2 ) . "\n" . ') ' . $_attributes . ';'; 
  147.  
  148. // Add to the query array using the table name as the index 
  149. $description = array( 
  150. 'query_original' => $query,  
  151. 'query_tidy' => $_tidy_query,  
  152. 'columns' => $_columns,  
  153. 'indices' => $_indices 
  154. ); 
  155. unset( $_table_name, $_columns, $_indices, $_tidy_query ); 
  156.  
  157. return $description; 
  158.  
  159. /** 
  160. * Helper function to flatten arrays 
  161. */ 
  162. function _flatten_array( $array, $cut_branch = 0, $keep_child_array_keys = true ) 
  163. if ( !is_array( $array ) ) { 
  164. return $array; 
  165.  
  166. if ( empty( $array ) ) { 
  167. return null; 
  168.  
  169. $temp = array(); 
  170. foreach ( $array as $k => $v ) { 
  171. if ( $cut_branch && $k == $cut_branch ) 
  172. continue; 
  173. if ( is_array( $v ) ) { 
  174. if ( $keep_child_array_keys ) { 
  175. $temp[$k] = true; 
  176. $temp += BP_SQL_Schema_Parser::_flatten_array( $v, $cut_branch, $keep_child_array_keys ); 
  177. } else { 
  178. $temp[$k] = $v; 
  179. return $temp; 
  180.  
  181. /** 
  182. * Splits grouped SQL statements into queries within a highly structured array 
  183. * Only supports CREATE TABLE, INSERT and UPDATE 
  184. */ 
  185. function parse( $sql ) 
  186. // Only accept strings or arrays 
  187. if ( is_string( $sql ) ) { 
  188. // Just pop strings into an array to start with 
  189. $queries = array( $sql ); 
  190. } elseif ( is_array( $sql ) ) { 
  191. // Flatten the array 
  192. $queries = BP_SQL_Schema_Parser::_flatten_array( $sql, 0, false ); 
  193. // Remove empty nodes 
  194. $queries = array_filter( $queries ); 
  195. } else { 
  196. return false; 
  197.  
  198. // Clean up the queries 
  199. $_clean_queries = array(); 
  200. foreach ( $queries as $_query ) { 
  201. // Trim space and semi-colons 
  202. $_query = trim( $_query, "; \t\n\r\0\x0B" ); 
  203. // If it exists and isn't a number 
  204. if ( $_query && !is_numeric( $_query ) ) { 
  205. // Is it more than one query? 
  206. if ( strpos( ';', $_query ) !== false ) { 
  207. // Explode by semi-colon 
  208. foreach ( explode( ';', $_query ) as $_part ) { 
  209. $_part = trim( $_part ); 
  210. if ( $_part && !is_numeric( $_part ) ) { 
  211. // Pull out any commented code 
  212. // Can't properly deal with /*!4321 FOO `bar` */ version specific inclusion, just includes it regardless of version 
  213. $_part = preg_replace( '@/\*![0-9]*([^\*]*)\*/@', '$1', $_part ); 
  214. $_part = preg_replace( '@/\*[^\*]*\*/@', '', $_part ); 
  215. $_part = preg_replace( '@[\-\-|#].*$@m', '', $_part ); 
  216. $_clean_queries[] = trim( $_part ) . ';'; 
  217. unset( $_part ); 
  218. } else { 
  219. $_clean_queries[] = $_query . ';'; 
  220. unset( $_query ); 
  221. if ( !count( $_clean_queries ) ) { 
  222. return false; 
  223. $queries = $_clean_queries; 
  224. unset( $_clean_queries ); 
  225.  
  226. $_queries = array(); 
  227. foreach ( $queries as $_query ) { 
  228. // Only process table creation, inserts and updates, capture the table/database name while we are at it 
  229. if ( !preg_match( '@^(CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?|INSERT\s+INTO|UPDATE)\s+`?([^\s|`]+)`?@im', $_query, $_matches ) ) { 
  230. continue; 
  231.  
  232. // Tidy up the type so we can switch it 
  233. $_type = strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[1] ) ) ); 
  234. $_table_name = trim( $_matches[2] ); 
  235. unset( $_matches ); 
  236.  
  237. switch ( $_type ) { 
  238. case 'CREATE TABLE': 
  239. case 'CREATE TABLE IF NOT EXISTS': 
  240. $_description = BP_SQL_Schema_Parser::describe_table( $_query ); 
  241. if ( is_array( $_description ) ) { 
  242. $_queries['tables'][$_table_name] = $_description; 
  243. break; 
  244.  
  245. case 'INSERT INTO': 
  246. // Just add the query as is for now 
  247. $_queries['insert'][$_table_name][] = $_query; 
  248. break; 
  249.  
  250. case 'UPDATE': 
  251. // Just add the query as is for now 
  252. $_queries['update'][$_table_name][] = $_query; 
  253. break; 
  254. unset( $_type, $_table_name ); 
  255. unset( $_query ); 
  256.  
  257. if ( !count( $_queries ) ) { 
  258. return false; 
  259. return $_queries; 
  260.  
  261. /** 
  262. * Evaluates the difference between a given set of SQL queries and real database structure 
  263. */ 
  264. function delta( $db_object, $queries, $ignore = false, $execute = true ) 
  265. if ( !$db_object || !is_object( $db_object ) || !( is_a( $db_object, 'BPDB' ) || is_a( $db_object, 'BPDB_Multi' ) || is_a( $db_object, 'BPDB_Hyper' ) ) ) { 
  266. return __( 'Passed variable is not a BackPress database object.' ); 
  267.  
  268. if ( !$_queries = BP_SQL_Schema_Parser::parse( $queries ) ) { 
  269. return __( 'No schema available.' ); 
  270.  
  271. // Set up default elements to ignore 
  272. $ignore_defaults = array( 
  273. 'tables' => array(), // Just a list of tablenames, including prefix. Does not affect INSERT and UPDATE queries. 
  274. 'columns' => array(), // Arrays of column names, keyed with the table names, including prefix. 
  275. 'indices' => array() // Arrays of index names, keyed with the table names, including prefix. 
  276. ); 
  277.  
  278. // Add the elements to ignore that were passed to the function 
  279. if ( !$ignore || !is_array( $ignore ) ) { 
  280. $ignore = $ignore_defaults; 
  281. } else { 
  282. if ( isset( $ignore['tables'] ) && is_array( $ignore['tables'] ) ) { 
  283. $ignore['tables'] = array_merge( $ignore_defaults['tables'], $ignore['tables'] ); 
  284. if ( isset( $ignore['columns'] ) && is_array( $ignore['columns'] ) ) { 
  285. $ignore['columns'] = array_merge( $ignore_defaults['columns'], $ignore['columns'] ); 
  286. if ( isset( $ignore['indices'] ) && is_array( $ignore['indices'] ) ) { 
  287. $ignore['indices'] = array_merge( $ignore_defaults['indices'], $ignore['indices'] ); 
  288.  
  289. // Build an array of $db_object registered tables and their database identifiers 
  290. $_tables = $db_object->tables; 
  291. $db_object_tables = array(); 
  292. foreach ( $_tables as $_table_id => $_table_name ) { 
  293. if ( is_array( $_table_name ) && isset( $db_object->db_servers['dbh_' . $_table_name[0]] ) ) { 
  294. $db_object_tables[$db_object->$_table_id] = 'dbh_' . $_table_name[0]; 
  295. } else { 
  296. $db_object_tables[$db_object->$_table_id] = 'dbh_global'; 
  297. unset( $_tables, $_table_id, $_table_name ); 
  298.  
  299. $alterations = array(); 
  300.  
  301. // Loop through table queries 
  302. if ( isset( $_queries['tables'] ) ) { 
  303. foreach ( $_queries['tables'] as $_new_table_name => $_new_table_data ) { 
  304. if ( in_array( $_new_table_name, $ignore['tables'] ) ) { 
  305. continue; 
  306.  
  307. // See if the table is custom and registered in $db_object under a custom database 
  308. if ( 
  309. isset( $db_object_tables[$_new_table_name] ) && 
  310. $db_object_tables[$_new_table_name] != 'dbh_global' && 
  311. isset( $db_object->db_servers[$db_object_tables[$_new_table_name]]['ds'] ) 
  312. ) { 
  313. // Force the database connection 
  314. $_dbhname = $db_object->db_servers[$db_object_tables[$_new_table_name]]['ds']; 
  315. $db_object->_force_dbhname = $_dbhname; 
  316. } else { 
  317. $_dbhname = 'dbh_global'; 
  318.  
  319. // Fetch the existing table column structure from the database 
  320. $db_object->suppress_errors(); 
  321. if ( !$_existing_table_columns = $db_object->get_results( 'DESCRIBE `' . $_new_table_name . '`;', ARRAY_A ) ) { 
  322. $db_object->suppress_errors( false ); 
  323. // The table doesn't exist, add it and then continue to the next table 
  324. $alterations[$_dbhname][$_new_table_name][] = array( 
  325. 'action' => 'create_table',  
  326. 'message' => __( 'Creating table' ),  
  327. 'query' => $_new_table_data['query_tidy'] 
  328. ); 
  329. continue; 
  330. $db_object->suppress_errors( false ); 
  331.  
  332. // Add an index to the existing columns array 
  333. $__existing_table_columns = array(); 
  334. foreach ( $_existing_table_columns as $_existing_table_column ) { 
  335. // Remove 'Key' from returned column structure 
  336. unset( $_existing_table_column['Key'] ); 
  337. $__existing_table_columns[$_existing_table_column['Field']] = $_existing_table_column; 
  338. $_existing_table_columns = $__existing_table_columns; 
  339. unset( $__existing_table_columns ); 
  340.  
  341. // Loop over the columns in this table and look for differences 
  342. foreach ( $_new_table_data['columns'] as $_new_column_name => $_new_column_data ) { 
  343. if ( isset( $ignore['columns'][$_new_table_name] ) && in_array( $_new_column_name, $ignore['columns'][$_new_table_name] ) ) { 
  344. continue; 
  345.  
  346. if ( !in_array( $_new_column_data, $_existing_table_columns ) ) { 
  347. // There is a difference 
  348. if ( !isset( $_existing_table_columns[$_new_column_name] ) ) { 
  349. // The column doesn't exist, so add it 
  350. $alterations[$_dbhname][$_new_table_name][] = array( 
  351. 'action' => 'add_column',  
  352. 'message' => sprintf( __( 'Adding column: %s' ), $_new_column_name ),  
  353. 'column' => $_new_column_name,  
  354. 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD COLUMN ' . BP_SQL_Schema_Parser::get_column_definition( $_new_column_data ) . ';' 
  355. ); 
  356. continue; 
  357.  
  358. // Adjust defaults on columns that allow defaults 
  359. if ( 
  360. $_new_column_data['Default'] !== $_existing_table_columns[$_new_column_name]['Default'] && 
  361. !in_array( 
  362. strtolower( $_new_column_data['Type'] ),  
  363. array( 'tinytext', 'text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob' ) 
  364. ) { 
  365. // Change the default value for the column 
  366. $alterations[$_dbhname][$_new_table_name][] = array( 
  367. 'action' => 'set_default',  
  368. 'message' => sprintf( __( 'Setting default on column: %s' ), $_new_column_name ),  
  369. 'column' => $_new_column_name,  
  370. 'query' => 'ALTER TABLE `' . $_new_table_name . '` ALTER COLUMN `' . $_new_column_name . '` SET DEFAULT \'' . $_new_column_data['Default'] . '\';' 
  371. ); 
  372. // Don't continue, overwrite this if the next conditional is met 
  373.  
  374. if ( 
  375. $_new_column_data['Type'] !== $_existing_table_columns[$_new_column_name]['Type'] || 
  376. ( 'YES' === $_new_column_data['Null'] xor 'YES' === $_existing_table_columns[$_new_column_name]['Null'] ) || 
  377. $_new_column_data['Extra'] !== $_existing_table_columns[$_new_column_name]['Extra'] 
  378. ) { 
  379. // Change the structure for the column 
  380. $alterations[$_dbhname][$_new_table_name][] = array( 
  381. 'action' => 'change_column',  
  382. 'message' => sprintf( __( 'Changing column: %s' ), $_new_column_name ),  
  383. 'column' => $_new_column_name,  
  384. 'query' => 'ALTER TABLE `' . $_new_table_name . '` CHANGE COLUMN `' . $_new_column_name . '` ' . BP_SQL_Schema_Parser::get_column_definition( $_new_column_data ) . ';' 
  385. ); 
  386. unset( $_existing_table_columns, $_new_column_name, $_new_column_data ); 
  387.  
  388. // Fetch the table index structure from the database 
  389. if ( !$_existing_table_indices = $db_object->get_results( 'SHOW INDEX FROM `' . $_new_table_name . '`;', ARRAY_A ) ) { 
  390. continue; 
  391.  
  392. // Add an index to the existing columns array and organise by index name 
  393. $__existing_table_indices = array(); 
  394. foreach ( $_existing_table_indices as $_existing_table_index ) { 
  395. // Remove unused parts from returned index structure 
  396. unset( 
  397. $_existing_table_index['Collation'],  
  398. $_existing_table_index['Cardinality'],  
  399. $_existing_table_index['Packed'],  
  400. $_existing_table_index['Null'],  
  401. $_existing_table_index['Comment'] 
  402. ); 
  403. $__existing_table_indices[$_existing_table_index['Key_name']][] = $_existing_table_index; 
  404. $_existing_table_indices = $__existing_table_indices; 
  405. unset( $__existing_table_indices ); 
  406.  
  407. // Loop over the indices in this table and look for differences 
  408. foreach ( $_new_table_data['indices'] as $_new_index_name => $_new_index_data ) { 
  409. if ( isset( $ignore['indices'][$_new_table_name] ) && in_array( $_new_index_name, $ignore['indices'][$_new_table_name] ) ) { 
  410. continue; 
  411.  
  412. if ( !in_array( $_new_index_data, $_existing_table_indices ) ) { 
  413. // There is a difference 
  414. if ( !isset( $_existing_table_indices[$_new_index_name] ) ) { 
  415. // The index doesn't exist, so add it 
  416. $alterations[$_dbhname][$_new_table_name][] = array( 
  417. 'action' => 'add_index',  
  418. 'message' => sprintf( __( 'Adding index: %s' ), $_new_index_name ),  
  419. 'index' => $_new_index_name,  
  420. 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition( $_new_index_data ) . ';' 
  421. ); 
  422. continue; 
  423.  
  424. if ( $_new_index_data !== $_existing_table_indices[$_new_index_name] ) { 
  425. // The index is incorrect, so drop it and add the new one 
  426. if ( $_new_index_name == 'PRIMARY' ) { 
  427. $_drop_index_name = 'PRIMARY KEY'; 
  428. } else { 
  429. $_drop_index_name = 'INDEX `' . $_new_index_name . '`'; 
  430. $alterations[$_dbhname][$_new_table_name][] = array( 
  431. 'action' => 'drop_index',  
  432. 'message' => sprintf( __( 'Dropping index: %s' ), $_new_index_name ),  
  433. 'index' => $_new_index_name,  
  434. 'query' => 'ALTER TABLE `' . $_new_table_name . '` DROP ' . $_drop_index_name . ';' 
  435. ); 
  436. unset( $_drop_index_name ); 
  437. $alterations[$_dbhname][$_new_table_name][] = array( 
  438. 'action' => 'add_index',  
  439. 'message' => sprintf( __( 'Adding index: %s' ), $_new_index_name ),  
  440. 'index' => $_new_index_name,  
  441. 'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition( $_new_index_data ) . ';' 
  442. ); 
  443. unset( $_new_index_name, $_new_index_data ); 
  444.  
  445. // Go back to the default database connection 
  446. $db_object->_force_dbhname = false; 
  447. unset( $_new_table_name, $_new_table_data, $_dbhname ); 
  448.  
  449. // Now deal with the sundry INSERT and UPDATE statements (if any) 
  450. if ( isset( $_queries['insert'] ) && is_array( $_queries['insert'] ) && count( $_queries['insert'] ) ) { 
  451. foreach ( $_queries['insert'] as $_table_name => $_inserts ) { 
  452. foreach ( $_inserts as $_insert ) { 
  453. $alterations['dbh_global'][$_table_name][] = array( 
  454. 'action' => 'insert',  
  455. 'message' => __( 'Inserting data' ),  
  456. 'query' => $_insert 
  457. ); 
  458. unset( $_insert ); 
  459. unset( $_table_name, $_inserts ); 
  460. if ( isset( $_queries['update'] ) && is_array( $_queries['update'] ) && count( $_queries['update'] ) ) { 
  461. foreach ( $_queries['update'] as $_table_name => $_updates ) { 
  462. foreach ( $_updates as $_update ) { 
  463. $alterations['dbh_global'][$_table_name][] = array( 
  464. 'action' => 'update',  
  465. 'message' => __( 'Updating data' ),  
  466. 'query' => $_update 
  467. ); 
  468. unset( $_update ); 
  469. unset( $_table_name, $_updates ); 
  470.  
  471. // Initialise an array to hold the output messages 
  472. $messages = array(); 
  473. $errors = array(); 
  474.  
  475. foreach ( $alterations as $_dbhname => $_tables ) { 
  476. // Force the database connection (this was already checked to be valid in the previous loop) 
  477. $db_object->_force_dbhname = $_dbhname; 
  478.  
  479. // Note the database in the return messages 
  480. $messages[] = '>>> ' . sprintf( __( 'Modifying database: %s (%s)' ), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host'] ); 
  481.  
  482. foreach ( $_tables as $_table_name => $_alterations ) { 
  483. // Note the table in the return messages 
  484. $messages[] = '>>>>>> ' . sprintf( __( 'Table: %s' ), $_table_name ); 
  485.  
  486. foreach ( $_alterations as $_alteration ) { 
  487. // If there is no query, then skip 
  488. if ( !$_alteration['query'] ) { 
  489. continue; 
  490.  
  491. // Note the action in the return messages 
  492. $messages[] = '>>>>>>>>> ' . $_alteration['message']; 
  493.  
  494. if ( !$execute ) { 
  495. $messages[] = '>>>>>>>>>>>> ' . __( 'Skipped' ); 
  496. continue; 
  497.  
  498. // Run the query 
  499. $_result = $db_object->query( $_alteration['query'] ); 
  500. $_result_error = $db_object->get_error(); 
  501.  
  502. if ( $_result_error ) { 
  503. // There was an error 
  504. $_result =& $_result_error; 
  505. unset( $_result_error ); 
  506. $messages[] = '>>>>>>>>>>>> ' . __( 'SQL ERROR! See the error log for more detail' ); 
  507. $errors[] = __( 'SQL ERROR!' ); 
  508. $errors[] = '>>> ' . sprintf( __( 'Database: %s (%s)' ), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host'] ); 
  509. $errors[] = '>>>>>> ' . $_result->error_data['db_query']['query']; 
  510. $errors[] = '>>>>>> ' . $_result->error_data['db_query']['error']; 
  511. } else { 
  512. $messages[] = '>>>>>>>>>>>> ' . __( 'Done' ); 
  513. unset( $_result ); 
  514. unset( $_alteration ); 
  515. unset( $_table_name, $_alterations ); 
  516. unset( $_dbhname, $_tables ); 
  517.  
  518. // Reset the database connection 
  519. $db_object->_force_dbhname = false; 
  520.  
  521. return array( 'messages' => $messages, 'errors' => $errors ); 
  522. } // END class BP_SQL_Schema_Parser