/**
* if in_both = false, what rows in A are not in B ?
*
* if in_both = true, what rows are in A and B ?
* - when a is empty, all of b's are returned
* - a's row members are the ones returned when in_both rows are found
* this is important when comparing tables whose rows are the same but have added columns
*
* @return void
*/
public static function table_data_rows_compare($table_a, $table_b, $in_both, &$a_rows, &$b_rows, &$changes)
{
$a_rows = array();
$b_rows = array();
$changes = array();
$table_a_data_rows = dbx::get_table_rows($table_a);
$table_b_data_rows = dbx::get_table_rows($table_b);
// data_row_overlay_key_search() needs these to do the matching
$table_a_data_rows_columns = preg_split("/[\\,\\s]+/", $table_a_data_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$table_b_data_rows_columns = preg_split("/[\\,\\s]+/", $table_b_data_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
if ($table_a_data_rows == null || $table_a_data_rows_columns == null) {
// table_a has no data rows
if ($in_both) {
// what rows are in A and B? none in A to compare
} else {
// what rows in A are not in B? none to list
}
} else {
if ($table_b_data_rows == null || $table_b_data_rows_columns == null) {
// table_b has no data rows
if ($in_both) {
// what rows are in A and B? none in B to compare
} else {
// what rows in A are not in B? omg, all of them!
$a_rows = $table_a_data_rows->row;
}
} else {
$table_b_primary_keys = mysql5_table::primary_key_columns($table_b);
// @NOTICE: in the case of table row data comparising for DIFFING, the primary keys to use are table B
// but the table B and A columns are reversed compared to data_rows_overlay() as the comparison is B vs A not base vs overlay (AvB)
// so the columns to match as base and overlay are reversed, comared to other calls to data_row_overlay_primary_key_index()
$primary_key_index = xml_parser::data_row_overlay_primary_key_index($table_b_primary_keys, $table_b_data_rows_columns, $table_a_data_rows_columns);
$base_pklookup = array();
$i = 0;
if (count($primary_key_index['base'])) {
foreach ($table_b_data_rows->row as $base_row) {
$s = '';
foreach ($primary_key_index['base'] as $index) {
$s .= ':' . $base_row->col[$index];
}
$base_pklookup[$s] = $i++;
}
}
$table_b_index = 0;
foreach ($table_a_data_rows->row as $table_a_data_row) {
$s = '';
foreach ($primary_key_index['overlay'] as $index) {
$s .= ':' . $table_a_data_row->col[$index];
}
if (array_key_exists($s, $base_pklookup)) {
$match = TRUE;
$table_b_index = $base_pklookup[$s];
} else {
$match = FALSE;
}
if ($match) {
// we found a match
//echo "rows match\n"; var_dump($table_a_data_row); var_dump($table_b_data_row);
$table_b_data_row =& $table_b_data_rows->row[$table_b_index];
if ($in_both) {
// looking for rows in both
// is the row marked for delete in A?
if (self::table_data_row_deleted($table_a_data_row)) {
// don't return changes, we are looking for rows in_both
} else {
if (self::table_data_row_deleted($table_b_data_row)) {
// don't return changes, we are looking for rows in_both
} else {
// do table data row diff, add rows to return by reference rows for both A and B
$changed_columns = array();
static::table_data_row_diff($table_a_data_rows_columns, $table_a_data_row, $table_b_data_rows_columns, $table_b_data_row, $changed_columns);
$a_rows[] = $table_a_data_row;
$b_rows[] = $table_b_data_row;
$changes[] = $changed_columns;
}
}
} else {
// is the A row marked for delete?
if (self::table_data_row_deleted($table_a_data_row)) {
// there was a match, but we are looking for A not in B, A row is marked deleted, don't return it
} else {
if (self::table_data_row_deleted($table_b_data_row)) {
// there was a match
// A is not deleted
// we are looking for A not in B
// B is deleted
$a_rows[] = $table_a_data_row;
}
}
}
} else {
// echo "rows don't match\n"; var_dump($table_a_data_row); var_dump($table_b_data_row);
// no match
if (!$in_both) {
// looking for A not in B
if (self::table_data_row_deleted($table_a_data_row)) {
// but the A row is marked deleted, don't return it
} else {
$a_rows[] = $table_a_data_row;
}
}
}
}
}
}
}
/**
* Overlay table rows from an overlay file onto a base table element
*
* @param type $base_table base table element to put overlay into
* @param type $overlay_table_rows overlay table rows element
* @throws exception
*/
public static function data_rows_overlay(&$base_table, &$overlay_table_rows)
{
$base_table_rows =& dbx::get_table_rows($base_table, TRUE, $overlay_table_rows['columns']);
$base_table_rows_count = count($base_table_rows->row);
// if the rows element columns attribute doesnt have a column that the overlay does
if (strlen($base_table_rows['columns']) == 0) {
throw new exception("base rows element missing columns attribute - unexpected");
}
if (strlen($overlay_table_rows['columns']) == 0) {
throw new exception("overlay rows element missing columns attribute - unexpected");
}
$base_cols = preg_split("/[\\,\\s]+/", $base_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$overlay_cols = preg_split("/[\\,\\s]+/", $overlay_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$cols_diff = array_diff($overlay_cols, $base_cols);
// contains any values $overlay_cols does that $base_cols didnt, so add them
foreach ($cols_diff as $cols_diff_col) {
// add the missing column, padding the base's row->col entries with empty col's to match the new size
$base_cols[] = $cols_diff_col;
for ($i = 0; $i < $base_table_rows_count; $i++) {
// need to do it for each row entry, check for default for the column
$node_col = $base_table_rows->row[$i]->addChild('col', self::column_default_value($base_table, $cols_diff_col, $node_col));
}
}
// put the new columns list back in the node
$base_table_rows['columns'] = implode(', ', $base_cols);
// determine the "natural" ordering of primary key columns, so that we can deterministically create a primary key key
$base_primary_keys = preg_split("/[\\,\\s]+/", $base_table['primaryKey'], -1, PREG_SPLIT_NO_EMPTY);
$primary_key_index = self::data_row_overlay_primary_key_index($base_primary_keys, $base_cols, $overlay_cols);
// primary key key => row index
$base_pklookup = array();
$i = 0;
foreach ($base_table_rows->row as $base_row) {
$s = '';
foreach ($primary_key_index['base'] as $index) {
$s .= ':' . $base_row->col[$index];
}
$base_pklookup[$s] = $i++;
}
// merge all row entries for the rows element
$base_row_index = 0;
foreach ($overlay_table_rows->row as $overlay_row) {
// sanity check the overlay's rows columns list against the col count of the row
$overlay_row_count = count($overlay_row->col);
if (count($overlay_cols) != $overlay_row_count) {
dbsteward::error(count($overlay_cols) . " overlay columns != " . $overlay_row_count . " overlay elements");
var_dump($overlay_cols);
foreach ($overlay_row->col as $olcol) {
var_dump($olcol);
}
throw new exception("overlay_cols list count does not match overlay_row->col count");
}
// simple optimization:
// if the node had no ->row's to start
// don't try to match any of the children we are considering in this loop
if ($base_table_rows_count == 0) {
dbsteward::debug("DEBUG: skipping " . $base_table['name'] . " overlay -- no base table rows");
$row_match = FALSE;
} else {
$s = '';
foreach ($primary_key_index['overlay'] as $index) {
$s .= ':' . $overlay_row->col[$index];
}
if (array_key_exists($s, $base_pklookup)) {
$row_match = TRUE;
$base_row_index = $base_pklookup[$s];
} else {
$row_match = FALSE;
}
// $row_match = self::data_row_overlay_key_search($base_table_rows, $overlay_row, $primary_key_index, $base_row_index);
}
if ($row_match) {
// $base_row_index is set to $i in _match() when a match is found, so use it to overlay the matched row
$node_row = $base_table_rows->row[$base_row_index];
} else {
// not found, add the row and empty col entries
$node_row = $base_table_rows->addChild('row');
foreach ($base_cols as $base_col) {
$node_col = $node_row->addChild('col');
$node_col = self::column_default_value($base_table, $base_col, $node_col);
}
// then overlay the data in the overlay row
}
self::data_row_overlay_row($base_table, $node_row, $overlay_row, $base_cols, $overlay_cols);
}
}
public static function build_data($db_doc, $ofs, $tables)
{
// use the dependency order to then write out the actual data inserts into the data sql file
$limit_to_tables_count = count(dbsteward::$limit_to_tables);
foreach ($tables as $dep_table) {
$schema = $dep_table['schema'];
$table = $dep_table['table'];
if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
if ($limit_to_tables_count > 0) {
if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) {
if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) {
// table is to be included
} else {
continue;
}
} else {
continue;
}
}
$ofs->write(mysql5_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE));
$table_primary_keys = mysql5_table::primary_key_columns($table);
$table_column_names = dbx::to_array($table->column, 'name');
$node_rows =& dbx::get_table_rows($table);
// the <rows> element
$data_column_names = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
// set serial primary keys to the max value after inserts have been performed
// only if the PRIMARY KEY is not a multi column
if (count($table_primary_keys) == 1 && in_array($table_primary_keys[0], $data_column_names)) {
$pk_column_name = $table_primary_keys[0];
$node_pk_column = dbx::get_table_column($table, $pk_column_name);
if ($node_pk_column == NULL) {
throw new exception("Failed to find primary key column '" . $pk_column_name . "' for " . $schema['name'] . "." . $table['name']);
}
// only set the pkey to MAX() if the primary key column is also a serial/bigserial and if serialStart is not defined
if (mysql5_column::is_serial($node_pk_column['type']) && !isset($node_pk_column['serialStart'])) {
$fqtn = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']);
$qcol = mysql5::get_quoted_column_name($pk_column_name);
$setval = mysql5_sequence::get_setval_call(mysql5_column::get_serial_sequence_name($schema, $table, $node_pk_column), "MAX({$qcol})", "TRUE");
$sql = "SELECT {$setval} FROM {$fqtn};\n";
$ofs->write($sql);
}
}
// unlike the pg class, we cannot just set identity column start values here with setval without inserting a row
// check if primary key is a column of this table - FS#17481
if (count(array_diff($table_primary_keys, $table_column_names)) != 0) {
throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']);
}
}
// include all of the unstaged sql elements
dbx::build_staged_sql($db_doc, $ofs, NULL);
$ofs->write("\n");
}
/**
* @TODO: this needs rewritten to delete the XML document row instead
*/
public static function delete_data_row(&$node_table, $where)
{
// break up the clause by its parenthetical () and logical AND OR segmentation
$clause = sql_parser::clause_explode($where);
if (!is_array($clause)) {
$clause = array($clause);
}
$new_table_rows = dbx::get_table_rows($new_table);
$data_rows = $new_table_rows->row;
dbsteward::trace("CLAUSE " . $where);
dbsteward::trace("BEFORE this->data has " . count($data_rows) . " rows");
for ($i = 0; $i < count($data_rows); $i++) {
if (self::clause_match($data_rows[$i], $clause)) {
unset($data_rows[$i]);
$data_rows = array_merge($data_rows);
// redo array keys
$i--;
}
}
dbsteward::trace("AFTER this->data has " . count($data_rows) . " rows");
}
public static function build_data($db_doc, $ofs, $tables)
{
// use the dependency order to then write out the actual data inserts into the data sql file
$tables_count = count($tables);
$limit_to_tables_count = count(dbsteward::$limit_to_tables);
for ($i = 0; $i < $tables_count; $i++) {
$schema = $tables[$i]['schema'];
$table = $tables[$i]['table'];
if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
if ($limit_to_tables_count > 0) {
if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) {
if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) {
// table is to be included
} else {
continue;
}
} else {
continue;
}
}
$ofs->write(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE));
// set serial primary keys to the max value after inserts have been performed
// only if the PRIMARY KEY is not a multi column
$node_rows =& dbx::get_table_rows($table);
$columns = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
if (isset($table['primaryKey']) && strlen($table['primaryKey']) > 0 && in_array(dbsteward::string_cast($table['primaryKey']), $columns)) {
$pk_column = dbsteward::string_cast($table['primaryKey']);
// only do it if the primary key column is also a serial/bigserial
$nodes = xml_parser::inheritance_get_column($table, $pk_column);
if (count($nodes) != 1) {
var_dump($nodes);
throw new exception("Failed to find primary key column '" . $pk_column . "' for " . $schema['name'] . "." . $table['name']);
}
$pk = $nodes[0];
$pk_column_type = strtolower(dbsteward::string_cast($pk['type']));
if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $pk_column_type) > 0) {
// only set the pkey to MAX() if serialStart is not defined
if (!isset($pk['serialStart'])) {
$sql = "SELECT setval(pg_get_serial_sequence('" . $schema['name'] . "." . $table['name'] . "', '" . $pk_column . "'), MAX({$pk_column}), TRUE) FROM " . $schema['name'] . "." . $table['name'] . ";\n";
$ofs->write($sql);
}
}
}
// check if primary key is a column of this table - FS#17481
$primary_keys_exist = self::primary_key_split($table['primaryKey']);
foreach ($table->column as $column) {
// while looping through columns, check to see if primary key is one of them
// if it is remove it from the primary keys array, at the end of loop array should be empty
$key = array_search($column['name'], $primary_keys_exist);
if (is_numeric($key)) {
unset($primary_keys_exist[$key]);
}
}
// throw an error if the table is using a primaryKey column that does not actually exist
if (!empty($primary_keys_exist)) {
if (empty($table['inheritsTable'])) {
throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']);
} else {
dbsteward::info('Primary key ' . $table['primaryKey'] . ' does not exist as a column in child table ' . $table['name'] . ', but may exist in parent table');
}
}
}
// include all of the unstaged sql elements
dbx::build_staged_sql($db_doc, $ofs, NULL);
$ofs->write("\n");
}
