se/SE/se-lib/DB_Util.php

<?php


class DB_Util {


	/**
	 * Compare db structures
	 */
	function compare_db($db1, $db2) {
		
	}

	function action_drop_table_to_sql($table_name) {
		$sql = "DROP `{$table_name}` ";
		return $sql;
	}

	/**
	 * @param $field_params array('type'=>'' [, 'default'=>'', 'not null'=>bool, 'extra'=>''])
	 */
	function action_modify_column_to_sql($table_name, $field_name, $field_params) {
		$sql = "ALTER TABLE `{$table_name}` MODIFY `{$field_name}` ";
		$sql .= $field_params['type'];
		if (!empty($field_params['not null']) && $field_params['not null'] == true) {
			$sql .= " NOT ";
		}
		$sql .= " NULL ";
		if (!empty($field_params['default'])) {
			$sql .= " " . $field_params['default'];
		}
		if (!empty($field_params['extra'])) {
			$sql .= " " . $field_params['extra'];
		}
		return $sql;
	}

	/**
	 * @param $field_params array('type'=>'' [, 'default'=>'', 'not null'=>bool, 'extra'=>''])
	 */
	function action_add_column_to_sql($table_name, $field_name, $field_params) {
		$sql = "ALTER TABLE `{$table_name}` ADD `{$field_name}` ";
		$sql .= $field_params['type'];
		if (!empty($field_params['not null']) && $field_params['not null'] == true) {
			$sql .= " NOT ";
		}
		$sql .= " NULL ";
		if (!empty($field_params['default'])) {
			$sql .= " " . $field_params['default'];
		}
		if (!empty($field_params['extra'])) {
			$sql .= " " . $field_params['extra'];
		}
		return $sql;
	}

	function action_drop_column_to_sql($table_name, $field_name) {
		$sql = "ALTER TABLE `{$table_name}` DROP `{$field_name}` ";
		return $sql;
	}

	/**
	 * @param $columns array of column params
	 * @param $keys array of key params
	 */
	function action_create_table_to_sql($table_name, $columns, $keys) {
		$sql = "DROP `{$table_name}` ";
		return $sql;
	}

	function action_add_key_to_sql($table_name, $key_name, $key_params) {
		$sql = "ALTER TABLE `{$table_name}` ADD {$key_params['type']}";
		if ($key_params['type'] != 'PRIMARY KEY')  $sql .= " {$key_name}";
		$sql .= " (" . implode(", ", $key_params['columns']) . ")";
		return $sql;
	}

	function action_drop_key_to_sql($table_name, $key_name) {
		$sql = "ALTER TABLE `{$table_name}` DROP INDEX {$key_name} ";
		return $sql;
	}

	/**
	 * @returns array(0=>[sql], 1=>[sql]) - array of sql for db1 and db2
	 */
	function compare_db_table_to_sql($table_name, &$cmp) {
		$sqls = array();
		if (!empty($cmp['columns'])) {
			foreach ($cmp['columns'] as $k_field => $v_cmp) {
				if (count($v_cmp) == 2) {
					$sqls[0][$k_field] = self::action_modify_column_to_sql($table_name, $k_field, $v_cmp[1]);
					$sqls[1][$k_field] = self::action_modify_column_to_sql($table_name, $k_field, $v_cmp[0]);
				} else if (count($v_cmp) == 1) {
					if (array_key_exists(0, $v_cmp)) {
						$sqls[0][$k_field] = self::action_drop_column_to_sql($table_name, $k_field);
						$sqls[1][$k_field] = self::action_add_column_to_sql($table_name, $k_field, $v_cmp[0]);
					} else {
						$sqls[0][$k_field] = self::action_add_column_to_sql($table_name, $k_field, $v_cmp[1]);
						$sqls[1][$k_field] = self::action_drop_column_to_sql($table_name, $k_field);
					}
				}
			}
		}
		if (!empty($cmp['keys'])) {
			foreach ($cmp['keys'] as $k_key => $v_cmp) {
				if (count($v_cmp) == 2) {
					$sqls[0][$k_key] = self::action_modify_key_to_sql($table_name, $k_key, $v_cmp[0]);
					$sqls[1][$k_key] = self::action_modify_key_to_sql($table_name, $k_key, $v_cmp[0]);
				} else if (count($v_cmp) == 1) {
					if (array_key_exists(0, $v_cmp)) {
						$sqls[0][$k_key] = self::action_drop_key_to_sql($table_name, $k_key);
						$sqls[1][$k_key] = self::action_add_key_to_sql($table_name, $k_key, $v_cmp[0]);
					} else {
						$sqls[0][$k_key] = self::action_add_key_to_sql($table_name, $k_key, $v_cmp[1]);
						$sqls[1][$k_key] = self::action_drop_key_to_sql($table_name, $k_key);
					}
				}
			}
		}
		return $sqls;
	}

	/**
	 * Compare db structures.
	 * @returns mixed
	 *   null - table not exists in both db
	 *   empty array - table are the same
	 *   array - columns, keys that differs
	 * example:
	 *   array(
	 *     'columns' => array(
	 *       'ID' => array(
	 *           0 => 1-st db column ID info or null if not exists
	 *           1 => 2-nd db column ID info or null if not exists
	 *         )
	 *     )
	 *   )
	 *   array(
	 *     'keys' => array(
	 *       'KEY X' => array(
	 *           0 => 1-st db KEY X info or null if not exists
	 *           1 => 2-nd db KEY X info or null if not exists
	 *         )
	 *     )
	 *   )
	 */
	function compare_db_table($db1, $db2, $table_name) {
		if (empty($table_name)) {
			return false;
		}

		$dbs = array($db1, $db2);

		$tbl_structs = array();
		// check if table exists in both db
		foreach ($dbs as $k_db_ind => $v_db) {
			$tbl_structs[$k_db_ind] = self::get_table_struct($v_db, $table_name);
		}
		$tbl_keys = array();
		foreach ($dbs as $k_db_ind => $v_db) {
			if (!empty($tbl_structs[$k_db_ind])) {
				$tbl_keys[$k_db_ind] = self::get_table_keys($v_db, $table_name);
			} else {
				$tbl_keys[$k_db_ind] = null;
			}
		}
		$cmp = array();
		if (empty($tbl_structs[0]) && empty($tbl_structs[1])) {
			return null;
		}

		$cmp['columns'] = array();
		$cmp['keys'] = array();

		foreach ($dbs as $k_ind => $v_db) {
			$k_2nd_ind = 1 - $k_ind;
			if (empty($tbl_structs[$k_ind])) {
				continue;
			}
			foreach ($tbl_structs[$k_ind] as $k_field => $v_field) {
				if (!array_key_exists($k_field, $tbl_structs[$k_2nd_ind])) {
					$cmp['columns'][$k_field][$k_ind] = $v_field;
				} else {
					$v_2nd_field = $tbl_structs[$k_2nd_ind][$k_field];
					$column_differ = false;
					if ($v_field['type'] != $v_2nd_field['type']) {
						$column_differ = true;
					}
					$field_add_keys = array('default', 'not null', 'extra');
					foreach ($field_add_keys as $field_key) {
						if (isset($v_field[$field_key]) && isset($v_2nd_field[$field_key])) {
							if ($v_field[$field_key] != $v_2nd_field[$field_key]) {
								$column_differ = true;
							}
						} else if (isset($v_field[$field_key]) != isset($v_2nd_field[$field_key])) {
							$column_differ = true;
						}
					}
					if ($column_differ) {
						$cmp['columns'][$k_field][$k_ind] = $v_field;
						$cmp['columns'][$k_field][$k_2nd_ind] = $v_2nd_field;
					}
				}
			}
		}
		// keys
		foreach ($dbs as $k_ind => $v_db) {
			$k_2nd_ind = 1 - $k_ind;
			if (empty($tbl_structs[$k_ind])) {
				continue;
			}
			if (empty($tbl_keys[$k_ind])) {
				continue;
			}
			foreach ($tbl_keys[$k_ind] as $k_key_name => $v_key) {
				if (!array_key_exists($k_key_name, $tbl_keys[$k_2nd_ind])) {
					$cmp['keys'][$k_key_name][$k_ind] = $v_key;
				} else {
					$v_2nd_key = $tbl_keys[$k_2nd_ind][$k_key_name];
					$column_differ = false;
					if ($v_key['type'] != $v_2nd_key['type']) {
						$column_differ = true;
					}
					$a_diff_1 = array_diff($v_key['columns'], $v_2nd_key['columns']);
					$a_diff_2 = array_diff($v_2nd_key['columns'], $v_key['columns']);
					if (!empty($a_diff_1) || !empty($a_diff_2)) {
						$column_differ = true;
					}
					if ($column_differ) {
						$cmp['keys'][$k_key_name][$k_ind] = $v_key;
						$cmp['keys'][$k_key_name][$k_2nd_ind] = $v_2nd_key;
					}
				}
			}
		}
		if (empty($cmp['columns'])) unset($cmp['columns']);
		if (empty($cmp['keys'])) unset($cmp['keys']);
		return $cmp;
	}

	/**
	 * @returns array('field name' => array('type'=>'' [, 'default'=>'', 'not null'=>bool, 'extra'=>''] ))
	 */
	function get_table_struct(&$db, $table_name) {
		$tbl_struct = array();
		$sql = sprintf("SHOW FIELDS FROM `%s`", $table_name);
		$res = $db->query($sql);
		if ($db->num_rows($res) == 0) {
			return null;
		}
		else {
			while ($record = $db->fetch_assoc($res)) {
				$tbl_struct[$record['Field']] = array();
				$tbl_struct[$record['Field']]['type'] = $record['Type'];
				if (!empty($record['Default'])) {
					if (substr($record['Default'], 0, strlen('CURRENT_TIMESTAMP')) == 'CURRENT_TIMESTAMP') {
						$tbl_struct[$record['Field']]['default'] = $record['Default'];
					} else {
						$tbl_struct[$record['Field']]['default'] = "'" . $record['Default'] . "'";
					}
				}
				if (@strcmp($record['Null'], 'YES') != 0) {
					$tbl_struct[$record['Field']]['not null'] = true;
				}
				if (!empty($record['Extra'])) {
					$tbl_struct[$record['Field']]['extra'] = $record['Extra'];
				}
			}
		}
		return $tbl_struct;
	}

	/**
	 * @returns array('key name' => array('type'=>'', 'columns'=>array of column names))
	 */
	function get_table_keys(&$db, $table_name) {
		$tbl_keys = array();
		$sql = sprintf("SHOW KEYS FROM `%s`", $table_name);
		$res = $db->query($sql);
		if ($db->num_rows($res) == 0) {
			return null;
		}
		while ($row = $db->fetch($res)) {
			$key_name = $row->Key_name;
			$key_type = '';
			if ($row->Key_name == 'PRIMARY') {
				if ($row->Index_type == 'BTREE') {
					$key_type = "PRIMARY KEY";
				}
			}
			else {
				if ($row->Non_unique == '0' && $row->Index_type == 'BTREE') {
					$key_type = "UNIQUE KEY";
					$key_name = "`{$key_name}`";
				}
				if ($row->Non_unique == '1' && $row->Index_type == 'BTREE') {
					$key_type = "KEY";
					$key_name = "`{$key_name}`";
				}
				if ($row->Non_unique == '1' && $row->Index_type == 'FULLTEXT') {
					$key_type = "FULLTEXT";
					$key_name = "`{$key_name}`";
				}
			}

			if (!$key_type) {
				// TODO: error - unknown type
			} else {
				if (empty($tbl_keys[$key_name])) {
					$tbl_keys[$key_name] = array('type'=>$key_type, 'columns'=>array());
				}
				$tbl_keys[$key_name]['columns'][] = $row->Column_name;
			}
		}
		return $tbl_keys;
	}

	function get_table_list(&$db, $table_prefix = '', $table_type = null) {
		$tbl_list = array();
		if($table_type) $sql="SHOW FULL TABLES WHERE TABLE_TYPE LIKE  '{$table_type}'";
		else $sql="SHOW TABLES";
		$res = $db->query($sql);
		if ($db->num_rows($res) == 0) {
			return $tbl_list;
		}
		while ($record = $db->fetch_row($res)) {
			if ($table_prefix == '' || $table_prefix == '%' || $table_prefix == substr($record[0], 0, strlen($table_prefix))) {
				$tbl_list[] = $record[0];
			}
		}
		return $tbl_list;
	}

	function get_db_structure_by_table_prefix(&$db, $table_prefix) {
		$structure = '';
		$res = $db->query('SHOW TABLES');
		if ($db->num_rows($res) == 0) {
			return false;
		}
		while ($record = $db->fetch_row($res)) {
			if ($table_prefix == '' || $table_prefix == '%' || $table_prefix == substr($record[0], 0, strlen($table_prefix))) {
				$table_struct = self::get_table_structure($db, $record[0]);
				if ($table_struct === false) {
					// TODO: get_table_structure fail
				} else {
					$structure .= $table_struct;
				}
			}
		}
		return $structure;
	}

	function get_db_structure(&$db, $tables = array()) {
		$structure = '';
		$res = $db->query('SHOW TABLES');
		if ($db->num_rows($res) == 0) {
			return false;
		}
		while ($record = $db->fetch_row($res)) {
			if (empty($tables) || in_array($record[0], $tables)) {
				$table_struct = self::get_table_structure($db, $record[0]);
				if ($table_struct === false) {
					// TODO: get_table_structure fail
				} else {
					$structure .= $table_struct;
				}
			}
		}
		return $structure;
  }

  function get_table_structure(&$db, $table) {
		// Structure Header
		$structure = "-- \n";
		$structure .= "-- Table structure for table `{$table}` \n";
		$structure .= "-- \n\n";
		// Dump Structure
		//$structure .= 'DROP TABLE IF EXISTS `'.$table.'`;'."\n";
		// Create sql
		$structure .= "CREATE TABLE `{$table}` (\n";
		$sql_table_fields = array();
		$records = $db->query('SHOW FIELDS FROM `'.$table.'`');
		if ($db->num_rows($records) == 0) {
			return false;
		}
		while ($record = $db->fetch_assoc($records)) {
			$curr_sql_field = "";
			$curr_sql_field .= "`" . $record['Field'] . "` " . $record['Type'];
			if (!empty($record['Default'])) {
				if (substr($record['Default'], 0, strlen('CURRENT_TIMESTAMP')) == 'CURRENT_TIMESTAMP') {
					$curr_sql_field .= " DEFAULT " . $record['Default'];
				} else {
					$curr_sql_field .= " DEFAULT '{$record['Default']}'";
				}
			}
			if (@strcmp($record['Null'], 'YES') != 0) {
				$curr_sql_field .= " NOT NULL";
			}
			if (!empty($record['Extra'])) {
				$curr_sql_field .= " " . $record['Extra'];
			}
			$sql_table_fields[] = $curr_sql_field;
		}
		$structure .= (!empty($sql_table_fields))? implode(",\n", $sql_table_fields) : "";
		//$structure = @ereg_replace(",\n$", null, $structure);

		// Save all Column Indexes
		$table_keys = self::get_sql_keys_table($db, $table);
		if ($table_keys === false) {
			// TODO: get_sql_keys_table fail
		} else {
			$structure .= ",\n" . $table_keys;
		}
		$structure .= "\n)";

		//Save table engine
		$records = $db->query("SHOW TABLE STATUS LIKE '".$table."'");
		if ($record = $db->fetch_assoc($records)) {
			if (!empty($record['Engine'])) {
				$structure .= ' ENGINE='.$record['Engine'];
			}
			if (!empty($record['Auto_increment'])) {
				$structure .= ' AUTO_INCREMENT='.$record['Auto_increment'];
			}
		}

		$structure .= ";\n\n-- --------------------------------------------------------\n\n";
		return $structure;
	}

	function get_sql_keys_table(&$db, $table) {
		$ret_keys = array();
		$primary = "";
		$unique = array();
		$index = array();
		$fulltext = array();
		$res = $db->query("SHOW KEYS FROM `" . $table . "`");
		if ($db->num_rows($res) == 0) {
			return false;
		}
		while ($row = $db->fetch($res)) {
			if ($row->Key_name == 'PRIMARY') {
				if ($row->Index_type == 'BTREE') {
					$ret_keys["PRIMARY KEY"][] = "`" . $row->Column_name . "`";
				}
			}
			else {
				if ($row->Non_unique == '0' && $row->Index_type == 'BTREE') {
					$ret_keys["UNIQUE KEY `" . $row->Key_name . "`"][] = "`" . $row->Column_name . "`";
				}
				if ($row->Non_unique == '1' && $row->Index_type == 'BTREE') {
					$ret_keys["KEY `" . $row->Key_name . "`"][] = "`" . $row->Column_name . "`";
				}
				if ($row->Non_unique == '1' && $row->Index_type == 'FULLTEXT') {
					$ret_keys["FULLTEXT `" . $row->Key_name . "`"][] = "`" . $row->Column_name . "`";
				}
			}
		}
		$out_keys = array();
		if (!empty($ret_keys)) {
			foreach ($ret_keys as $key => $val) {
				$out_keys[] = $key . " (" . implode(", ", $val) . ")";
			}
		}
		$out_keys = (!empty($out_keys))? implode(",\n", $out_keys) : '';
		return $out_keys;
	}

}