/**
* Compare two schemas
*
* @param array $obj_master
* @param array $obj_slave
* @return array
*/
public function compare_schemas($obj_master, $obj_slave, $options = [])
{
$result = ['success' => false, 'error' => [], 'data' => [], 'count' => 0];
// delete first
$result['data']['delete_triggers'] = [];
$result['data']['delete_views'] = [];
$result['data']['delete_constraints'] = [];
$result['data']['delete_indexes'] = [];
$result['data']['delete_functions'] = [];
$result['data']['delete_columns'] = [];
$result['data']['delete_tables'] = [];
$result['data']['delete_domains'] = [];
// after tables and columns
$result['data']['delete_sequences'] = [];
// after domains
$result['data']['delete_schemas'] = [];
$result['data']['delete_extensions'] = [];
// last
// new second
$result['data']['new_extensions'] = [];
// first
$result['data']['new_schemas'] = [];
$result['data']['new_schema_owners'] = [];
$result['data']['new_domains'] = [];
// after schema
$result['data']['new_domain_owners'] = [];
$result['data']['new_sequences'] = [];
$result['data']['new_sequence_owners'] = [];
$result['data']['new_tables'] = [];
$result['data']['new_table_owners'] = [];
$result['data']['new_columns'] = [];
$result['data']['change_columns'] = [];
$result['data']['new_constraints'] = [];
$result['data']['new_indexes'] = [];
$result['data']['new_views'] = [];
// views goes after we add columns
$result['data']['change_views'] = [];
$result['data']['new_view_owners'] = [];
$result['data']['new_functions'] = [];
$result['data']['new_function_owner'] = [];
$result['data']['new_triggers'] = [];
// after functions
$result['data']['change_triggers'] = [];
// add extension
if (!empty($obj_master['extension'])) {
foreach ($obj_master['extension'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_slave['extension'][$k][$k2])) {
$result['data']['new_extensions'][$k . '.' . $k2] = array('type' => 'extension', 'data' => $v2);
$result['count']++;
}
}
}
}
// delete extensions
if (!empty($obj_slave['extension'])) {
foreach ($obj_slave['extension'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_master['extension'][$k][$k2])) {
$result['data']['delete_extensions'][$k . '.' . $k2] = array('type' => 'extension_delete', 'data' => $v2);
$result['count']++;
}
}
}
}
// new schemas
if (!empty($obj_master['schema'])) {
foreach ($obj_master['schema'] as $k => $v) {
if (empty($obj_slave['schema'][$k])) {
$result['data']['new_schemas'][$k] = array('type' => 'schema', 'data' => $v);
$result['count']++;
} else {
if ($v['owner'] != $obj_slave['schema'][$k]['owner']) {
$result['data']['new_schema_owners'][$k] = array('type' => 'schema_owner', 'data' => $v);
$result['count']++;
}
}
}
}
// delete schema
if (!empty($obj_slave['schema'])) {
foreach ($obj_slave['schema'] as $k => $v) {
if (empty($obj_master['schema'][$k])) {
$result['data']['delete_schemas'][$k] = array('type' => 'schema_delete', 'data' => $v);
$result['count']++;
}
}
}
// new tables
if (!empty($obj_master['table'])) {
foreach ($obj_master['table'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_slave['table'][$k][$k2])) {
$result['data']['new_tables'][$v2['full_table_name']] = array('type' => 'table_new', 'data' => $v2);
$result['count']++;
} else {
if ($v2['owner'] != $obj_slave['table'][$k][$k2]['owner']) {
$result['data']['new_table_owners'][$v2['full_table_name']] = array('type' => 'table_owner', 'data' => $v2);
$result['count']++;
}
}
}
}
}
// delete table
if (isset($obj_slave['table'])) {
foreach ($obj_slave['table'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_master['table'][$k][$k2])) {
$result['data']['delete_tables'][$v2['full_table_name']] = array('type' => 'table_delete', 'data' => $v2);
$result['count']++;
}
}
}
}
// new columns
if (!empty($obj_master['table'])) {
foreach ($obj_master['table'] as $k => $v) {
foreach ($v as $k2 => $v2) {
// if we have new table we do not need to check for new columns
if (!empty($result['data']['new_tables'][$v2['full_table_name']])) {
continue;
}
// finding new column
foreach ($v2['columns'] as $k3 => $v3) {
if (empty($obj_slave['table'][$k][$k2]['columns'][$k3])) {
$result['data']['new_columns'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'column_new', 'name' => $k3, 'table' => $v2['full_table_name'], 'data' => $v3);
$result['count']++;
} else {
// comparing data types
$temp_error = false;
if ($v3['type'] != $obj_slave['table'][$k][$k2]['columns'][$k3]['type']) {
if (strpos($v3['type'], 'serial') !== false || strpos($obj_slave['table'][$k][$k2]['columns'][$k3]['type'], 'serial') !== false) {
$result['error'][] = 'Serial data type changes must be handled manually, column ' . $k . '.' . $k2 . '.' . $k3;
return $result;
}
$temp_error = true;
}
if (!isset($v3['null'])) {
$v3['null'] = false;
}
if (!isset($obj_slave['table'][$k][$k2]['columns'][$k3]['null'])) {
$obj_slave['table'][$k][$k2]['columns'][$k3]['null'] = false;
}
if ($v3['null'] != $obj_slave['table'][$k][$k2]['columns'][$k3]['null']) {
$temp_error = true;
}
if (!isset($v3['default'])) {
$v3['default'] = null;
}
if (!isset($obj_slave['table'][$k][$k2]['columns'][$k3]['default'])) {
$obj_slave['table'][$k][$k2]['columns'][$k3]['default'] = null;
}
if ($v3['default'] != $obj_slave['table'][$k][$k2]['columns'][$k3]['default']) {
$temp_error = true;
}
// auto_increment for mysqli
if ($options['backend'] == 'mysqli') {
if (!isset($v3['auto_increment'])) {
$v3['auto_increment'] = 0;
}
if ($v3['auto_increment'] != $obj_slave['table'][$k][$k2]['columns'][$k3]['auto_increment']) {
$temp_error = true;
}
}
if ($temp_error) {
$result['data']['change_columns'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'column_change', 'name' => $k3, 'table' => $v2['full_table_name'], 'data' => $v3, 'data_slave' => $obj_slave['table'][$k][$k2]['columns'][$k3]);
$result['count']++;
}
}
}
// finding columns to be deleted
foreach ($obj_slave['table'][$k][$k2]['columns'] as $k3 => $v3) {
if (empty($obj_master['table'][$k][$k2]['columns'][$k3])) {
$result['data']['delete_columns'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'column_delete', 'name' => $k3, 'table' => $v2['full_table_name']);
$result['count']++;
}
}
}
}
}
// exceptions for mysqli submodule
if ($options['backend'] == 'mysqli' && !empty($obj_slave['constraint'])) {
foreach ($obj_slave['constraint'] as $k => $v) {
foreach ($v as $k2 => $v2) {
foreach ($v2 as $k3 => $v3) {
if ($v3['type'] == 'pk') {
$found = null;
if (!empty($obj_master['constraint'][$k][$k2])) {
foreach ($obj_master['constraint'][$k][$k2] as $k4 => $v4) {
if ($v4['type'] == 'pk') {
$found = $k4;
break;
}
}
}
if (!empty($found)) {
$temp = $obj_slave['constraint'][$k][$k2][$k3];
unset($obj_slave['constraint'][$k][$k2][$k3]);
$obj_slave['constraint'][$k][$k2][$found] = $temp;
}
}
}
}
}
}
// new constraints
if (!empty($obj_master['constraint'])) {
foreach ($obj_master['constraint'] as $k => $v) {
foreach ($v as $k2 => $v2) {
foreach ($v2 as $k3 => $v3) {
if (empty($obj_slave['constraint'][$k][$k2][$k3])) {
$result['data']['new_constraints'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'constraint_new', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count']++;
} else {
// comparing structure
$temp_error = false;
if ($v3['type'] != $obj_slave['constraint'][$k][$k2][$k3]['type']) {
$temp_error = true;
}
if ($v3['full_table_name'] != $obj_slave['constraint'][$k][$k2][$k3]['full_table_name']) {
$temp_error = true;
}
if (!array_compare_level1($v3['columns'], $obj_slave['constraint'][$k][$k2][$k3]['columns'])) {
$temp_error = true;
}
// additiona verifications for fk constraints
if ($v3['type'] == 'fk') {
if ($v3['foreign_table'] != $obj_slave['constraint'][$k][$k2][$k3]['foreign_table']) {
$temp_error = true;
}
if (!array_compare_level1($v3['foreign_columns'], $obj_slave['constraint'][$k][$k2][$k3]['foreign_columns'])) {
$temp_error = true;
}
}
// if we have an error we rebuild
if ($temp_error) {
$result['data']['delete_constraints'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'constraint_delete', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['data']['new_constraints'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'constraint_new', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count'] += 1;
}
}
}
}
}
}
// delete constraints
if (isset($obj_slave['constraint'])) {
foreach ($obj_slave['constraint'] as $k => $v) {
foreach ($v as $k2 => $v2) {
foreach ($v2 as $k3 => $v3) {
if (empty($obj_master['constraint'][$k][$k2][$k3])) {
$result['data']['delete_constraints'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'constraint_delete', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count']++;
}
}
}
}
}
// new indexes
if (!empty($obj_master['index'])) {
foreach ($obj_master['index'] as $k => $v) {
foreach ($v as $k2 => $v2) {
foreach ($v2 as $k3 => $v3) {
if (empty($obj_slave['index'][$k][$k2][$k3])) {
$result['data']['new_indexes'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'index_new', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count']++;
} else {
// comparing structure
$temp_error = false;
if ($v3['type'] != $obj_slave['index'][$k][$k2][$k3]['type']) {
$temp_error = true;
}
if (!array_compare_level1($v3['columns'], $obj_slave['index'][$k][$k2][$k3]['columns'])) {
$temp_error = true;
}
// todo: comparison for foreign key & check
if ($temp_error) {
$result['data']['delete_indexes'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'index_delete', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $obj_slave['index'][$k][$k2][$k3]);
$result['data']['new_indexes'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'index_new', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count'] += 1;
}
}
}
}
}
}
// delete indexes
if (isset($obj_slave['index'])) {
foreach ($obj_slave['index'] as $k => $v) {
foreach ($v as $k2 => $v2) {
foreach ($v2 as $k3 => $v3) {
if (empty($obj_master['index'][$k][$k2][$k3])) {
$result['data']['delete_indexes'][$k . '.' . $k2 . '.' . $k3] = array('type' => 'index_delete', 'name' => $k3, 'table' => $v3['full_table_name'], 'data' => $v3);
$result['count']++;
}
}
}
}
}
// for mysqli/pgsql we need to put new sequences after new tables
if ($options['backend'] == 'mysqli' || $options['backend'] == 'pgsql') {
unset($result['data']['new_sequences']);
$result['data']['new_sequences'] = [];
}
// new sequences
if (!empty($obj_master['sequence'])) {
foreach ($obj_master['sequence'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_slave['sequence'][$k][$k2])) {
$result['data']['new_sequences'][$k . '.' . $k2] = array('type' => 'sequences_new', 'name' => $v2['full_sequence_name'], 'owner' => $v2['owner'], 'data' => $v2);
$result['count']++;
} else {
if ($options['backend'] == 'pgsql') {
// todo: fix here for pgsql!!!
// checking owner information
if ($v2['owner'] != $obj_slave['sequence'][$k][$k2]['owner']) {
$result['data']['new_sequence_owners'][$k . '.' . $k2] = array('type' => 'sequence_owner', 'name' => $v2['full_sequence_name'], 'owner' => $v2['owner'], 'data' => $v2);
}
}
}
}
}
}
// delete sequences
if (!empty($obj_slave['sequence'])) {
foreach ($obj_slave['sequence'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_master['sequence'][$k][$k2])) {
$result['data']['delete_sequences'][$k . '.' . $k2] = array('type' => 'sequence_delete', 'name' => $v2['full_sequence_name'], 'data' => $v2);
$result['count']++;
}
}
}
}
// functions
if (!empty($obj_master['function'])) {
foreach ($obj_master['function'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_slave['function'][$k][$k2])) {
$result['data']['new_functions'][$k . '.' . $k2] = array('type' => 'function_new', 'name' => $v2['full_function_name'], 'owner' => $v2['owner'], 'data' => $v2);
$result['count']++;
} else {
// if function has changed
if ($options['backend'] == 'mysqli') {
if ($v2['sql_parts']['body'] != $obj_slave['function'][$k][$k2]['sql_parts']['body']) {
$result['data']['delete_functions'][$k . '.' . $k2] = array('type' => 'function_delete', 'name' => $v2['full_function_name'], 'data' => $v2);
$result['data']['new_functions'][$k . '.' . $k2] = array('type' => 'function_new', 'name' => $v2['full_function_name'], 'owner' => $v2['owner'], 'data' => $v2);
$result['count']++;
}
} else {
if ($options['backend'] == 'pgsql') {
if (numbers_backend_db_class_ddl::sanitize_function($v2['sql_full']) != numbers_backend_db_class_ddl::sanitize_function($obj_slave['function'][$k][$k2]['sql_full'])) {
$result['data']['delete_functions'][$k . '.' . $k2] = array('type' => 'function_delete', 'name' => $v2['full_function_name'], 'data' => $v2);
$result['data']['new_functions'][$k . '.' . $k2] = array('type' => 'function_new', 'name' => $v2['full_function_name'], 'owner' => $v2['owner'], 'data' => $v2);
$result['count']++;
} else {
if ($v2['owner'] != $obj_slave['function'][$k][$k2]['owner']) {
// checking owner
$result['data']['new_function_owners'][$k . '.' . $k2] = array('type' => 'function_owner', 'name' => $v2['full_function_name'], 'owner' => $v2['owner'], 'data' => $v2);
$result['count']++;
}
}
}
}
}
}
}
}
// delete function
if (!empty($obj_slave['function'])) {
foreach ($obj_slave['function'] as $k => $v) {
foreach ($v as $k2 => $v2) {
if (empty($obj_master['function'][$k][$k2])) {
$result['data']['delete_functions'][$k . '.' . $k2] = array('type' => 'function_delete', 'name' => $v2['full_function_name'], 'data' => $v2);
$result['count']++;
}
}
}
}
// if we delete tables there's no need to delete constrants and/or indexes
foreach ($result['data']['delete_tables'] as $k => $v) {
// unsetting constraints
foreach ($result['data']['delete_constraints'] as $k2 => $v2) {
if ($v2['data']['full_table_name'] == $k) {
unset($result['data']['delete_constraints'][$k2]);
}
}
// unsetting indexes
foreach ($result['data']['delete_indexes'] as $k2 => $v2) {
if ($v2['data']['full_table_name'] == $k) {
unset($result['data']['delete_indexes'][$k2]);
}
}
}
// final step clean up empty keys
foreach ($result['data'] as $k => $v) {
// clean up empty nodes
if (empty($v)) {
unset($result['data'][$k]);
}
}
$result['success'] = true;
return $result;
}
