/svntrunk/bp-forums/bbpress/bb-includes/backpress/class.bp-sql-schema-parser.php

https://bitbucket.org/simplemediacode/bptrunk · PHP · 615 lines · 477 code · 61 blank · 77 comment · 94 complexity · c78ed33563b8af9319a744d61be0fafa MD5 · raw file 

    

  1. <?php
    2. 

  2. /**
    3. 

  3.  * Parses SQL schema statements for comparison to real table structures
    4. 

  4.  *
    5. 

  5.  * @package BackPress
    6. 

  6.  **/
    7. 

  7. class BP_SQL_Schema_Parser
    8. 

  8. {
    9. 

  9. 	/**
    10. 

  10. 	 * Builds a column definition as used in CREATE TABLE statements from
    11. 

  11. 	 * an array such as those returned by DESCRIBE `foo` statements
    12. 

  12. 	 */
    13. 

  13. 	function get_column_definition( $column_data )
    14. 

  14. 	{
    15. 

  15. 		if ( !is_array( $column_data ) ) {
    16. 

  16. 			return $column_data;
    17. 

  17. 		}
    18. 

  18. 

  19. 		$null = '';
    20. 

  20. 		if ( $column_data['Null'] != 'YES' ) {
    21. 

  21. 			$null = 'NOT NULL';
    22. 

  22. 		}
    23. 

  23. 

  24. 		$default = '';
    25. 

  25. 

  26. 		// Defaults aren't allowed at all on certain column types
    27. 

  27. 		if ( !in_array(
    28. 

  28. 			strtolower( $column_data['Type'] ),
    29. 

  29. 			array( 'tinytext', 'text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob' )
    30. 

  30. 		) ) {
    31. 

  31. 			if ( $column_data['Null'] == 'YES' && $column_data['Default'] === null ) {
    32. 

  32. 				$default = 'default NULL';
    33. 

  33. 			} elseif ( preg_match( '@^\d+$@', $column_data['Default'] ) ) {
    34. 

  34. 				$default = 'default ' . $column_data['Default'];
    35. 

  35. 			} elseif ( is_string( $column_data['Default'] ) || is_float( $column_data['Default'] ) ) {
    36. 

  36. 				$default = 'default \'' . $column_data['Default'] . '\'';
    37. 

  37. 			}
    38. 

  38. 		}
    39. 

  39. 

  40. 		$column_definition = '`' . $column_data['Field'] . '` ' . $column_data['Type'] . ' ' . $null . ' ' . $column_data['Extra'] . ' ' . $default;
    41. 

  41. 		return preg_replace( '@\s+@', ' ', trim( $column_definition ) );
    42. 

  42. 	}
    43. 

  43. 

  44. 	/**
    45. 

  45. 	 * Builds an index definition as used in CREATE TABLE statements from
    46. 

  46. 	 * an array similar to those returned by SHOW INDEX FROM `foo` statements
    47. 

  47. 	 */
    48. 

  48. 	function get_index_definition( $index_data )
    49. 

  49. 	{
    50. 

  50. 		if ( !is_array( $index_data ) ) {
    51. 

  51. 			return $index_data;
    52. 

  52. 		}
    53. 

  53. 

  54. 		if ( !count( $index_data ) ) {
    55. 

  55. 			return $index_data;
    56. 

  56. 		}
    57. 

  57. 

  58. 		$_name = '`' . $index_data[0]['Key_name'] . '`';
    59. 

  59. 

  60. 		if ( $index_data[0]['Index_type'] == 'BTREE' && $index_data[0]['Key_name'] == 'PRIMARY' ) {
    61. 

  61. 			$_type = 'PRIMARY KEY';
    62. 

  62. 			$_name = '';
    63. 

  63. 		} elseif ( $index_data[0]['Index_type'] == 'BTREE' && !$index_data[0]['Non_unique'] ) {
    64. 

  64. 			$_type = 'UNIQUE KEY';
    65. 

  65. 		} elseif ( $index_data[0]['Index_type'] == 'FULLTEXT' ) {
    66. 

  66. 			$_type = 'FULLTEXT KEY';
    67. 

  67. 		} else {
    68. 

  68. 			$_type = 'KEY';
    69. 

  69. 		}
    70. 

  70. 

  71. 		$_columns = array();
    72. 

  72. 		foreach ( $index_data as $_index ) {
    73. 

  73. 			if ( $_index['Sub_part'] ) {
    74. 

  74. 				$_columns[] = '`' . $_index['Column_name'] . '`(' . $_index['Sub_part'] . ')';
    75. 

  75. 			} else {
    76. 

  76. 				$_columns[] = '`' . $_index['Column_name'] . '`';
    77. 

  77. 			}
    78. 

  78. 		}
    79. 

  79. 		$_columns = join( ', ', $_columns );
    80. 

  80. 

  81. 		$index_definition = $_type . ' ' . $_name . ' (' . $_columns . ')';
    82. 

  82. 		return preg_replace( '@\s+@', ' ', $index_definition );
    83. 

  83. 	}
    84. 

  84. 

  85. 	/**
    86. 

  86. 	 * Returns a table structure from a raw sql query of the form "CREATE TABLE foo" etc.
    87. 

  87. 	 * The resulting array contains the original query, the columns as would be returned by DESCRIBE `foo`
    88. 

  88. 	 * and the indices as would be returned by SHOW INDEX FROM `foo` on a real table
    89. 

  89. 	 */
    90. 

  90. 	function describe_table( $query )
    91. 

  91. 	{
    92. 

  92. 		// Retrieve the table structure from the query
    93. 

  93. 		if ( !preg_match( '@^CREATE\s+TABLE(\s+IF\s+NOT\s+EXISTS)?\s+`?([^\s|`]+)`?\s+\((.*)\)\s*([^\)|;]*)\s*;?@ims', $query, $_matches ) ) {
    94. 

  94. 			return $query;
    95. 

  95. 		}
    96. 

  96. 

  97. 		$_if_not_exists = $_matches[1];
    98. 

  98. 

  99. 		// Tidy up the table name
    100. 

  100. 		$_table_name = trim( $_matches[2] );
    101. 

  101. 

  102. 		// Tidy up the table columns/indices
    103. 

  103. 		$_columns_indices = trim( $_matches[3], " \t\n\r\0\x0B," );
    104. 

  104. 		// Split by commas not followed by a closing parenthesis ")", using fancy lookaheads
    105. 

  105. 		$_columns_indices = preg_split( '@,(?!(?:[^\(]+\)))@ms', $_columns_indices );
    106. 

  106. 		$_columns_indices = array_map( 'trim', $_columns_indices );
    107. 

  107. 

  108. 		// Tidy the table attributes
    109. 

  109. 		$_attributes = preg_replace( '@\s+@', ' ', trim( $_matches[4] ) );
    110. 

  110. 		unset( $_matches );
    111. 

  111. 

  112. 		// Initialise some temporary arrays
    113. 

  113. 		$_columns = array();
    114. 

  114. 		$_indices = array();
    115. 

  115. 

  116. 		// Loop over the columns/indices
    117. 

  117. 		foreach ( $_columns_indices as $_column_index ) {
    118. 

  118. 			if ( preg_match( '@^(PRIMARY\s+KEY|UNIQUE\s+(?:KEY|INDEX)|FULLTEXT\s+(?:KEY|INDEX)|KEY|INDEX)\s+(?:`?(\w+)`?\s+)*\((.+?)\)$@im', $_column_index, $_matches ) ) {
    119. 

  119. 				// It's an index
    120. 

  120. 

  121. 				// Tidy the type
    122. 

  122. 				$_index_type = strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[1] ) ) );
    123. 

  123. 				$_index_type = str_replace( 'INDEX', 'KEY', $_index_type );
    124. 

  124. 				// Set the index name
    125. 

  125. 				$_index_name = ( 'PRIMARY KEY' == $_matches[1] ) ? 'PRIMARY' : $_matches[2];
    126. 

  126. 				// Split into columns
    127. 

  127. 				$_index_columns = array_map( 'trim', explode( ',', $_matches[3] ) );
    128. 

  128. 

  129. 				foreach ( $_index_columns as $_index_columns_index => $_index_column ) {
    130. 

  130. 					preg_match( '@`?(\w+)`?(?:\s*\(\s*(\d+)\s*\))?@i', $_index_column, $_matches_column );
    131. 

  131. 

  132. 					$_indices[$_index_name][] = array(
    133. 

  133. 						'Table'        => $_table_name,
    134. 

  134. 						'Non_unique'   => ( 'UNIQUE KEY' == $_index_type || 'PRIMARY' == $_index_name ) ? '0' : '1',
    135. 

  135. 						'Key_name'     => $_index_name,
    136. 

  136. 						'Seq_in_index' => (string) ( $_index_columns_index + 1 ),
    137. 

  137. 						'Column_name'  => $_matches_column[1],
    138. 

  138. 						'Sub_part'     => ( isset( $_matches_column[2] ) && $_matches_column[2] ) ? $_matches_column[2] : null,
    139. 

  139. 						'Index_type'   => ( 'FULLTEXT KEY' == $_index_type ) ? 'FULLTEXT' : 'BTREE'
    140. 

  140. 					);
    141. 

  141. 				}
    142. 

  142. 				unset( $_index_type, $_index_name, $_index_columns, $_index_columns_index, $_index_column, $_matches_column );
    143. 

  143. 

  144. 			} 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 ) ) {
    145. 

  145. 				// It's a column
    146. 

  146. 

  147. 				// Tidy the NOT NULL
    148. 

  148. 				$_matches[5] = isset( $_matches[5] ) ? strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[5] ) ) ) : '';
    149. 

  149. 

  150. 				$_columns[$_matches[1]] = array(
    151. 

  151. 					'Field'   => $_matches[1],
    152. 

  152. 					'Type'    => ( is_numeric( $_matches[3] ) ) ? $_matches[2] . '(' . $_matches[3] . ')' . ( ( isset( $_matches[4] ) && strtolower( $_matches[4] ) == 'unsigned' ) ? ' unsigned' : '' ) : $_matches[2],
    153. 

  153. 					'Null'    => ( 'NOT NULL' == strtoupper( $_matches[5] ) ) ? 'NO' : 'YES',
    154. 

  154. 					'Default' => ( isset( $_matches[7] ) && 'default' == strtolower( $_matches[7] ) && 'NULL' !== strtoupper( $_matches[8] ) ) ? trim( $_matches[8], "'" ) : null,
    155. 

  155. 					'Extra'   => ( isset( $_matches[6] ) && 'auto_increment' == strtolower( $_matches[6] ) ) ? 'auto_increment' : ''
    156. 

  156. 				);
    157. 

  157. 			}
    158. 

  158. 		}
    159. 

  159. 		unset( $_matches, $_columns_indices, $_column_index );
    160. 

  160. 

  161. 		// Tidy up the original query
    162. 

  162. 		$_tidy_query = 'CREATE TABLE';
    163. 

  163. 		if ( $_if_not_exists ) {
    164. 

  164. 			$_tidy_query .= ' IF NOT EXISTS';
    165. 

  165. 		}
    166. 

  166. 		$_tidy_query .= ' `' . $_table_name . '` (' . "\n";
    167. 

  167. 		foreach ( $_columns as $_column ) {
    168. 

  168. 			$_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_column_definition( $_column ) . ",\n";
    169. 

  169. 		}
    170. 

  170. 		unset( $_column );
    171. 

  171. 		foreach ( $_indices as $_index ) {
    172. 

  172. 			$_tidy_query .= "\t" . BP_SQL_Schema_Parser::get_index_definition( $_index ) . ",\n";
    173. 

  173. 		}
    174. 

  174. 		$_tidy_query = substr( $_tidy_query, 0, -2 ) . "\n" . ') ' . $_attributes . ';';
    175. 

  175. 

  176. 		// Add to the query array using the table name as the index
    177. 

  177. 		$description = array(
    178. 

  178. 			'query_original' => $query,
    179. 

  179. 			'query_tidy' => $_tidy_query,
    180. 

  180. 			'columns' => $_columns,
    181. 

  181. 			'indices' => $_indices
    182. 

  182. 		);
    183. 

  183. 		unset( $_table_name, $_columns, $_indices, $_tidy_query );
    184. 

  184. 

  185. 		return $description;
    186. 

  186. 	}
    187. 

  187. 

  188. 	/**
    189. 

  189. 	 * Helper function to flatten arrays
    190. 

  190. 	 */
    191. 

  191. 	function _flatten_array( $array, $cut_branch = 0, $keep_child_array_keys = true )
    192. 

  192. 	{
    193. 

  193. 		if ( !is_array( $array ) ) {
    194. 

  194. 			return $array;
    195. 

  195. 		}
    196. 

  196. 

  197. 		if ( empty( $array ) ) {
    198. 

  198. 			return null;
    199. 

  199. 		}
    200. 

  200. 

  201. 		$temp = array();
    202. 

  202. 		foreach ( $array as $k => $v ) {
    203. 

  203. 			if ( $cut_branch && $k == $cut_branch )
    204. 

  204. 				continue;
    205. 

  205. 			if ( is_array( $v ) ) {
    206. 

  206. 				if ( $keep_child_array_keys ) {
    207. 

  207. 					$temp[$k] = true;
    208. 

  208. 				}
    209. 

  209. 				$temp += BP_SQL_Schema_Parser::_flatten_array( $v, $cut_branch, $keep_child_array_keys );
    210. 

  210. 			} else {
    211. 

  211. 				$temp[$k] = $v;
    212. 

  212. 			}
    213. 

  213. 		}
    214. 

  214. 		return $temp;
    215. 

  215. 	}
    216. 

  216. 

  217. 	/**
    218. 

  218. 	 * Splits grouped SQL statements into queries within a highly structured array
    219. 

  219. 	 * Only supports CREATE TABLE, INSERT and UPDATE
    220. 

  220. 	 */
    221. 

  221. 	function parse( $sql )
    222. 

  222. 	{
    223. 

  223. 		// Only accept strings or arrays
    224. 

  224. 		if ( is_string( $sql ) ) {
    225. 

  225. 			// Just pop strings into an array to start with
    226. 

  226. 			$queries = array( $sql );
    227. 

  227. 		} elseif ( is_array( $sql ) ) {
    228. 

  228. 			// Flatten the array
    229. 

  229. 			$queries = BP_SQL_Schema_Parser::_flatten_array( $sql, 0, false );
    230. 

  230. 			// Remove empty nodes
    231. 

  231. 			$queries = array_filter( $queries );
    232. 

  232. 		} else {
    233. 

  233. 			return false;
    234. 

  234. 		}
    235. 

  235. 

  236. 		// Clean up the queries
    237. 

  237. 		$_clean_queries = array();
    238. 

  238. 		foreach ( $queries as $_query ) {
    239. 

  239. 			// Trim space and semi-colons
    240. 

  240. 			$_query = trim( $_query, "; \t\n\r\0\x0B" );
    241. 

  241. 			// If it exists and isn't a number
    242. 

  242. 			if ( $_query && !is_numeric( $_query ) ) {
    243. 

  243. 				// Is it more than one query?
    244. 

  244. 				if ( strpos( ';', $_query ) !== false ) {
    245. 

  245. 					// Explode by semi-colon
    246. 

  246. 					foreach ( explode( ';', $_query ) as $_part ) {
    247. 

  247. 						$_part = trim( $_part );
    248. 

  248. 						if ( $_part && !is_numeric( $_part ) ) {
    249. 

  249. 							// Pull out any commented code
    250. 

  250. 							// Can't properly deal with /*!4321 FOO `bar` */ version specific inclusion, just includes it regardless of version
    251. 

  251. 							$_part = preg_replace( '@/\*![0-9]*([^\*]*)\*/@', '$1', $_part );
    252. 

  252. 							$_part = preg_replace( '@/\*[^\*]*\*/@', '', $_part );
    253. 

  253. 							$_part = preg_replace( '@[\-\-|#].*$@m', '', $_part );
    254. 

  254. 							$_clean_queries[] = trim( $_part ) . ';';
    255. 

  255. 						}
    256. 

  256. 					}
    257. 

  257. 					unset( $_part );
    258. 

  258. 				} else {
    259. 

  259. 					$_clean_queries[] = $_query . ';';
    260. 

  260. 				}
    261. 

  261. 			}
    262. 

  262. 		}
    263. 

  263. 		unset( $_query );
    264. 

  264. 		if ( !count( $_clean_queries ) ) {
    265. 

  265. 			return false;
    266. 

  266. 		}
    267. 

  267. 		$queries = $_clean_queries;
    268. 

  268. 		unset( $_clean_queries );
    269. 

  269. 

  270. 		$_queries = array();
    271. 

  271. 		foreach ( $queries as $_query ) {
    272. 

  272. 			// Only process table creation, inserts and updates, capture the table/database name while we are at it
    273. 

  273. 			if ( !preg_match( '@^(CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?|INSERT\s+INTO|UPDATE)\s+`?([^\s|`]+)`?@im', $_query, $_matches ) ) {
    274. 

  274. 				continue;
    275. 

  275. 			}
    276. 

  276. 

  277. 			// Tidy up the type so we can switch it
    278. 

  278. 			$_type = strtoupper( preg_replace( '@\s+@', ' ', trim( $_matches[1] ) ) );
    279. 

  279. 			$_table_name = trim( $_matches[2] );
    280. 

  280. 			unset( $_matches );
    281. 

  281. 

  282. 			switch ( $_type ) {
    283. 

  283. 				case 'CREATE TABLE':
    284. 

  284. 				case 'CREATE TABLE IF NOT EXISTS':
    285. 

  285. 					$_description = BP_SQL_Schema_Parser::describe_table( $_query );
    286. 

  286. 					if ( is_array( $_description ) ) {
    287. 

  287. 						$_queries['tables'][$_table_name] = $_description;
    288. 

  288. 					}
    289. 

  289. 					break;
    290. 

  290. 

  291. 				case 'INSERT INTO':
    292. 

  292. 					// Just add the query as is for now
    293. 

  293. 					$_queries['insert'][$_table_name][] = $_query;
    294. 

  294. 					break;
    295. 

  295. 

  296. 				case 'UPDATE':
    297. 

  297. 					// Just add the query as is for now
    298. 

  298. 					$_queries['update'][$_table_name][] = $_query;
    299. 

  299. 					break;
    300. 

  300. 			}
    301. 

  301. 			unset( $_type, $_table_name );
    302. 

  302. 		}
    303. 

  303. 		unset( $_query );
    304. 

  304. 

  305. 		if ( !count( $_queries ) ) {
    306. 

  306. 			return false;
    307. 

  307. 		}
    308. 

  308. 		return $_queries;
    309. 

  309. 	}
    310. 

  310. 

  311. 	/**
    312. 

  312. 	 * Evaluates the difference between a given set of SQL queries and real database structure
    313. 

  313. 	 */
    314. 

  314. 	function delta( $db_object, $queries, $ignore = false, $execute = true )
    315. 

  315. 	{
    316. 

  316. 		if ( !$db_object || !is_object( $db_object ) || !( is_a( $db_object, 'BPDB' ) || is_a( $db_object, 'BPDB_Multi' ) || is_a( $db_object, 'BPDB_Hyper' ) ) ) {
    317. 

  317. 			return __( 'Passed variable is not a BackPress database object.' );
    318. 

  318. 		}
    319. 

  319. 

  320. 		if ( !$_queries = BP_SQL_Schema_Parser::parse( $queries ) ) {
    321. 

  321. 			return __( 'No schema available.' );
    322. 

  322. 		}
    323. 

  323. 

  324. 		// Set up default elements to ignore
    325. 

  325. 		$ignore_defaults = array(
    326. 

  326. 			'tables'  => array(), // Just a list of tablenames, including prefix. Does not affect INSERT and UPDATE queries.
    327. 

  327. 			'columns' => array(), // Arrays of column names, keyed with the table names, including prefix.
    328. 

  328. 			'indices' => array()  // Arrays of index names, keyed with the table names, including prefix.
    329. 

  329. 		);
    330. 

  330. 

  331. 		// Add the elements to ignore that were passed to the function
    332. 

  332. 		if ( !$ignore || !is_array( $ignore ) ) {
    333. 

  333. 			$ignore = $ignore_defaults;
    334. 

  334. 		} else {
    335. 

  335. 			if ( isset( $ignore['tables'] ) && is_array( $ignore['tables'] ) ) {
    336. 

  336. 				$ignore['tables'] = array_merge( $ignore_defaults['tables'], $ignore['tables'] );
    337. 

  337. 			}
    338. 

  338. 			if ( isset( $ignore['columns'] ) && is_array( $ignore['columns'] ) ) {
    339. 

  339. 				$ignore['columns'] = array_merge( $ignore_defaults['columns'], $ignore['columns'] );
    340. 

  340. 			}
    341. 

  341. 			if ( isset( $ignore['indices'] ) && is_array( $ignore['indices'] ) ) {
    342. 

  342. 				$ignore['indices'] = array_merge( $ignore_defaults['indices'], $ignore['indices'] );
    343. 

  343. 			}
    344. 

  344. 		}
    345. 

  345. 

  346. 		// Build an array of $db_object registered tables and their database identifiers
    347. 

  347. 		$_tables = $db_object->tables;
    348. 

  348. 		$db_object_tables = array();
    349. 

  349. 		foreach ( $_tables as $_table_id => $_table_name ) {
    350. 

  350. 			if ( is_array( $_table_name ) && isset( $db_object->db_servers['dbh_' . $_table_name[0]] ) ) {
    351. 

  351. 				$db_object_tables[$db_object->$_table_id] = 'dbh_' . $_table_name[0];
    352. 

  352. 			} else {
    353. 

  353. 				$db_object_tables[$db_object->$_table_id] = 'dbh_global';
    354. 

  354. 			}
    355. 

  355. 		}
    356. 

  356. 		unset( $_tables, $_table_id, $_table_name );
    357. 

  357. 

  358. 		$alterations = array();
    359. 

  359. 

  360. 		// Loop through table queries
    361. 

  361. 		if ( isset( $_queries['tables'] ) ) {
    362. 

  362. 			foreach ( $_queries['tables'] as $_new_table_name => $_new_table_data ) {
    363. 

  363. 				if ( in_array( $_new_table_name, $ignore['tables'] ) ) {
    364. 

  364. 					continue;
    365. 

  365. 				}
    366. 

  366. 

  367. 				// See if the table is custom and registered in $db_object under a custom database
    368. 

  368. 				if (
    369. 

  369. 					isset( $db_object_tables[$_new_table_name] ) &&
    370. 

  370. 					$db_object_tables[$_new_table_name] != 'dbh_global' &&
    371. 

  371. 					isset( $db_object->db_servers[$db_object_tables[$_new_table_name]]['ds'] )
    372. 

  372. 				) {
    373. 

  373. 					// Force the database connection
    374. 

  374. 					$_dbhname = $db_object->db_servers[$db_object_tables[$_new_table_name]]['ds'];
    375. 

  375. 					$db_object->_force_dbhname = $_dbhname;
    376. 

  376. 				} else {
    377. 

  377. 					$_dbhname = 'dbh_global';
    378. 

  378. 				}
    379. 

  379. 

  380. 				// Fetch the existing table column structure from the database
    381. 

  381. 				$db_object->suppress_errors();
    382. 

  382. 				if ( !$_existing_table_columns = $db_object->get_results( 'DESCRIBE `' . $_new_table_name . '`;', ARRAY_A ) ) {
    383. 

  383. 					$db_object->suppress_errors( false );
    384. 

  384. 					// The table doesn't exist, add it and then continue to the next table
    385. 

  385. 					$alterations[$_dbhname][$_new_table_name][] = array(
    386. 

  386. 						'action' => 'create_table',
    387. 

  387. 						'message' => __( 'Creating table' ),
    388. 

  388. 						'query' => $_new_table_data['query_tidy']
    389. 

  389. 					);
    390. 

  390. 					continue;
    391. 

  391. 				}
    392. 

  392. 				$db_object->suppress_errors( false );
    393. 

  393. 

  394. 				// Add an index to the existing columns array
    395. 

  395. 				$__existing_table_columns = array();
    396. 

  396. 				foreach ( $_existing_table_columns as $_existing_table_column ) {
    397. 

  397. 					// Remove 'Key' from returned column structure
    398. 

  398. 					unset( $_existing_table_column['Key'] );
    399. 

  399. 					$__existing_table_columns[$_existing_table_column['Field']] = $_existing_table_column;
    400. 

  400. 				}
    401. 

  401. 				$_existing_table_columns = $__existing_table_columns;
    402. 

  402. 				unset( $__existing_table_columns );
    403. 

  403. 

  404. 				// Loop over the columns in this table and look for differences
    405. 

  405. 				foreach ( $_new_table_data['columns'] as $_new_column_name => $_new_column_data ) {
    406. 

  406. 					if ( isset( $ignore['columns'][$_new_table_name] ) && in_array( $_new_column_name, $ignore['columns'][$_new_table_name] ) ) {
    407. 

  407. 						continue;
    408. 

  408. 					}
    409. 

  409. 

  410. 					if ( !in_array( $_new_column_data, $_existing_table_columns ) ) {
    411. 

  411. 						// There is a difference
    412. 

  412. 						if ( !isset( $_existing_table_columns[$_new_column_name] ) ) {
    413. 

  413. 							// The column doesn't exist, so add it
    414. 

  414. 							$alterations[$_dbhname][$_new_table_name][] = array(
    415. 

  415. 								'action' => 'add_column',
    416. 

  416. 								'message' => sprintf( __( 'Adding column: %s' ), $_new_column_name ),
    417. 

  417. 								'column' => $_new_column_name,
    418. 

  418. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD COLUMN ' . BP_SQL_Schema_Parser::get_column_definition( $_new_column_data ) . ';'
    419. 

  419. 							);
    420. 

  420. 							continue;
    421. 

  421. 						}
    422. 

  422. 

  423. 						// Adjust defaults on columns that allow defaults
    424. 

  424. 						if (
    425. 

  425. 							$_new_column_data['Default'] !== $_existing_table_columns[$_new_column_name]['Default'] &&
    426. 

  426. 							!in_array(
    427. 

  427. 								strtolower( $_new_column_data['Type'] ),
    428. 

  428. 								array( 'tinytext', 'text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob' )
    429. 

  429. 							)
    430. 

  430. 						) {
    431. 

  431. 							// Change the default value for the column
    432. 

  432. 							$alterations[$_dbhname][$_new_table_name][] = array(
    433. 

  433. 								'action' => 'set_default',
    434. 

  434. 								'message' => sprintf( __( 'Setting default on column: %s' ), $_new_column_name ),
    435. 

  435. 								'column' => $_new_column_name,
    436. 

  436. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` ALTER COLUMN `' . $_new_column_name . '` SET DEFAULT \'' . $_new_column_data['Default'] . '\';'
    437. 

  437. 							);
    438. 

  438. 							// Don't continue, overwrite this if the next conditional is met
    439. 

  439. 						}
    440. 

  440. 

  441. 						if (
    442. 

  442. 							$_new_column_data['Type'] !== $_existing_table_columns[$_new_column_name]['Type'] ||
    443. 

  443. 							( 'YES' === $_new_column_data['Null'] xor 'YES' === $_existing_table_columns[$_new_column_name]['Null'] ) ||
    444. 

  444. 							$_new_column_data['Extra'] !== $_existing_table_columns[$_new_column_name]['Extra']
    445. 

  445. 						) {
    446. 

  446. 							// Change the structure for the column
    447. 

  447. 							$alterations[$_dbhname][$_new_table_name][] = array(
    448. 

  448. 								'action' => 'change_column',
    449. 

  449. 								'message' => sprintf( __( 'Changing column: %s' ), $_new_column_name ),
    450. 

  450. 								'column' => $_new_column_name,
    451. 

  451. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` CHANGE COLUMN `' . $_new_column_name . '` ' . BP_SQL_Schema_Parser::get_column_definition( $_new_column_data ) . ';'
    452. 

  452. 							);
    453. 

  453. 						}
    454. 

  454. 					}
    455. 

  455. 				}
    456. 

  456. 				unset( $_existing_table_columns, $_new_column_name, $_new_column_data );
    457. 

  457. 

  458. 				// Fetch the table index structure from the database
    459. 

  459. 				if ( !$_existing_table_indices = $db_object->get_results( 'SHOW INDEX FROM `' . $_new_table_name . '`;', ARRAY_A ) ) {
    460. 

  460. 					continue;
    461. 

  461. 				}
    462. 

  462. 

  463. 				// Add an index to the existing columns array and organise by index name
    464. 

  464. 				$__existing_table_indices = array();
    465. 

  465. 				foreach ( $_existing_table_indices as $_existing_table_index ) {
    466. 

  466. 					// Remove unused parts from returned index structure
    467. 

  467. 					unset(
    468. 

  468. 						$_existing_table_index['Collation'],
    469. 

  469. 						$_existing_table_index['Cardinality'],
    470. 

  470. 						$_existing_table_index['Packed'],
    471. 

  471. 						$_existing_table_index['Null'],
    472. 

  472. 						$_existing_table_index['Comment']
    473. 

  473. 					);
    474. 

  474. 					$__existing_table_indices[$_existing_table_index['Key_name']][] = $_existing_table_index;
    475. 

  475. 				}
    476. 

  476. 				$_existing_table_indices = $__existing_table_indices;
    477. 

  477. 				unset( $__existing_table_indices );
    478. 

  478. 

  479. 				// Loop over the indices in this table and look for differences
    480. 

  480. 				foreach ( $_new_table_data['indices'] as $_new_index_name => $_new_index_data ) {
    481. 

  481. 					if ( isset( $ignore['indices'][$_new_table_name] ) && in_array( $_new_index_name, $ignore['indices'][$_new_table_name] ) ) {
    482. 

  482. 						continue;
    483. 

  483. 					}
    484. 

  484. 

  485. 					if ( !in_array( $_new_index_data, $_existing_table_indices ) ) {
    486. 

  486. 						// There is a difference
    487. 

  487. 						if ( !isset( $_existing_table_indices[$_new_index_name] ) ) {
    488. 

  488. 							// The index doesn't exist, so add it
    489. 

  489. 							$alterations[$_dbhname][$_new_table_name][] = array(
    490. 

  490. 								'action' => 'add_index',
    491. 

  491. 								'message' => sprintf( __( 'Adding index: %s' ), $_new_index_name ),
    492. 

  492. 								'index' => $_new_index_name,
    493. 

  493. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition( $_new_index_data ) . ';'
    494. 

  494. 							);
    495. 

  495. 							continue;
    496. 

  496. 						}
    497. 

  497. 

  498. 						if ( $_new_index_data !== $_existing_table_indices[$_new_index_name] ) {
    499. 

  499. 							// The index is incorrect, so drop it and add the new one
    500. 

  500. 							if ( $_new_index_name == 'PRIMARY' ) {
    501. 

  501. 								$_drop_index_name = 'PRIMARY KEY';
    502. 

  502. 							} else {
    503. 

  503. 								$_drop_index_name = 'INDEX `' . $_new_index_name . '`';
    504. 

  504. 							}
    505. 

  505. 							$alterations[$_dbhname][$_new_table_name][] = array(
    506. 

  506. 								'action' => 'drop_index',
    507. 

  507. 								'message' => sprintf( __( 'Dropping index: %s' ), $_new_index_name ),
    508. 

  508. 								'index' => $_new_index_name,
    509. 

  509. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` DROP ' . $_drop_index_name . ';'
    510. 

  510. 							);
    511. 

  511. 							unset( $_drop_index_name );
    512. 

  512. 							$alterations[$_dbhname][$_new_table_name][] = array(
    513. 

  513. 								'action' => 'add_index',
    514. 

  514. 								'message' => sprintf( __( 'Adding index: %s' ), $_new_index_name ),
    515. 

  515. 								'index' => $_new_index_name,
    516. 

  516. 								'query' => 'ALTER TABLE `' . $_new_table_name . '` ADD ' . BP_SQL_Schema_Parser::get_index_definition( $_new_index_data ) . ';'
    517. 

  517. 							);
    518. 

  518. 						}
    519. 

  519. 					}
    520. 

  520. 				}
    521. 

  521. 				unset( $_new_index_name, $_new_index_data );
    522. 

  522. 

  523. 				// Go back to the default database connection
    524. 

  524. 				$db_object->_force_dbhname = false;
    525. 

  525. 			}
    526. 

  526. 			unset( $_new_table_name, $_new_table_data, $_dbhname );
    527. 

  527. 		}
    528. 

  528. 

  529. 		// Now deal with the sundry INSERT and UPDATE statements (if any)
    530. 

  530. 		if ( isset( $_queries['insert'] ) && is_array( $_queries['insert'] ) && count( $_queries['insert'] ) ) {
    531. 

  531. 			foreach ( $_queries['insert'] as $_table_name => $_inserts ) {
    532. 

  532. 				foreach ( $_inserts as $_insert ) {
    533. 

  533. 					$alterations['dbh_global'][$_table_name][] = array(
    534. 

  534. 						'action' => 'insert',
    535. 

  535. 						'message' => __( 'Inserting data' ),
    536. 

  536. 						'query' => $_insert
    537. 

  537. 					);
    538. 

  538. 				}
    539. 

  539. 				unset( $_insert );
    540. 

  540. 			}
    541. 

  541. 			unset( $_table_name, $_inserts );
    542. 

  542. 		}
    543. 

  543. 		if ( isset( $_queries['update'] ) && is_array( $_queries['update'] ) && count( $_queries['update'] ) ) {
    544. 

  544. 			foreach ( $_queries['update'] as $_table_name => $_updates ) {
    545. 

  545. 				foreach ( $_updates as $_update ) {
    546. 

  546. 					$alterations['dbh_global'][$_table_name][] = array(
    547. 

  547. 						'action' => 'update',
    548. 

  548. 						'message' => __( 'Updating data' ),
    549. 

  549. 						'query' => $_update
    550. 

  550. 					);
    551. 

  551. 				}
    552. 

  552. 				unset( $_update );
    553. 

  553. 			}
    554. 

  554. 			unset( $_table_name, $_updates );
    555. 

  555. 		}
    556. 

  556. 

  557. 		// Initialise an array to hold the output messages
    558. 

  558. 		$messages = array();
    559. 

  559. 		$errors = array();
    560. 

  560. 

  561. 		foreach ( $alterations as $_dbhname => $_tables ) {
    562. 

  562. 			// Force the database connection (this was already checked to be valid in the previous loop)
    563. 

  563. 			$db_object->_force_dbhname = $_dbhname;
    564. 

  564. 

  565. 			// Note the database in the return messages
    566. 

  566. 			$messages[] = '>>> ' . sprintf( __( 'Modifying database: %s (%s)' ), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host'] );
    567. 

  567. 

  568. 			foreach ( $_tables as $_table_name => $_alterations ) {
    569. 

  569. 				// Note the table in the return messages
    570. 

  570. 				$messages[] = '>>>>>> ' . sprintf( __( 'Table: %s' ), $_table_name );
    571. 

  571. 

  572. 				foreach ( $_alterations as $_alteration ) {
    573. 

  573. 					// If there is no query, then skip
    574. 

  574. 					if ( !$_alteration['query'] ) {
    575. 

  575. 						continue;
    576. 

  576. 					}
    577. 

  577. 

  578. 					// Note the action in the return messages
    579. 

  579. 					$messages[] = '>>>>>>>>> ' . $_alteration['message'];
    580. 

  580. 

  581. 					if ( !$execute ) {
    582. 

  582. 						$messages[] = '>>>>>>>>>>>> ' . __( 'Skipped' );
    583. 

  583. 						continue;
    584. 

  584. 					}
    585. 

  585. 

  586. 					// Run the query
    587. 

  587. 					$_result = $db_object->query( $_alteration['query'] );
    588. 

  588. 					$_result_error = $db_object->get_error();
    589. 

  589. 

  590. 					if ( $_result_error ) {
    591. 

  591. 						// There was an error
    592. 

  592. 						$_result =& $_result_error;
    593. 

  593. 						unset( $_result_error );
    594. 

  594. 						$messages[] = '>>>>>>>>>>>> ' . __( 'SQL ERROR! See the error log for more detail' );
    595. 

  595. 						$errors[] = __( 'SQL ERROR!' );
    596. 

  596. 						$errors[] = '>>> ' . sprintf( __( 'Database: %s (%s)' ), $db_object->db_servers[$_dbhname]['name'], $db_object->db_servers[$_dbhname]['host'] );
    597. 

  597. 						$errors[] = '>>>>>> ' . $_result->error_data['db_query']['query'];
    598. 

  598. 						$errors[] = '>>>>>> ' . $_result->error_data['db_query']['error'];
    599. 

  599. 					} else {
    600. 

  600. 						$messages[] = '>>>>>>>>>>>> ' . __( 'Done' );
    601. 

  601. 					}
    602. 

  602. 					unset( $_result );
    603. 

  603. 				}
    604. 

  604. 				unset( $_alteration );
    605. 

  605. 			}
    606. 

  606. 			unset( $_table_name, $_alterations );
    607. 

  607. 		}
    608. 

  608. 		unset( $_dbhname, $_tables );
    609. 

  609. 

  610. 		// Reset the database connection
    611. 

  611. 		$db_object->_force_dbhname = false;
    612. 

  612. 

  613. 		return array( 'messages' => $messages, 'errors' => $errors );
    614. 

  614. 	}
    615. 

  615. } // END class BP_SQL_Schema_Parser
    616.