public static function foreign_key($db_doc, $node_schema, $node_table, $column, &$foreign)
{
$fschema = strlen($column['foreignSchema']) == 0 ? (string) $node_schema['name'] : (string) $column['foreignSchema'];
$foreign['schema'] = dbx::get_schema($db_doc, $fschema);
if (!$foreign['schema']) {
throw new exception("Failed to find foreign schema '" . dbsteward::string_cast($column['foreignSchema']) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
$foreign['table'] = dbx::get_table($foreign['schema'], $column['foreignTable']);
if (!$foreign['table']) {
throw new exception("Failed to find foreign table '" . $column['foreignTable'] . "' for " . $node_schema['name'] . "." . $node_table['name'] . "." . $column['name']);
}
// if foreignColumn is not set
// the column is assumed to be the same name as the referring column
if (isset($column['foreignColumn']) && strlen($column['foreignColumn'])) {
$foreignColumn = $column['foreignColumn'];
} else {
$foreignColumn = $column['name'];
}
$foreign['column'] = dbx::get_table_column($foreign['table'], $foreignColumn);
if (!$foreign['column']) {
var_dump($foreign['column']);
throw new exception("Failed to find foreign column '" . dbsteward::string_cast($foreignColumn) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
// column type is missing, and resolved foreign is also a foreign key?
// recurse and find the cascading foreign key
if (strlen($foreign['column']['type']) == 0 && isset($foreign['column']['foreignColumn'])) {
dbsteward::trace("Seeking nested foreign key for " . dbsteward::string_cast($foreign['schema']['name']) . "." . dbsteward::string_cast($foreign['table']['name']) . "." . $foreign['column']['name']);
$nested_fkey = array();
self::foreign_key($db_doc, $foreign['schema'], $foreign['table'], $foreign['column'], $nested_fkey);
//var_dump($nested_fkey['column']);
// make a separate clone of the column element because we are specifying the type only for foreign key type referencing
$foreign['column'] = new SimpleXMLElement($foreign['column']->asXML());
$foreign['column']['type'] = $nested_fkey['column']['type'];
}
$foreign['name'] = pgsql8_index::index_name($node_table['name'], $column['name'], 'fkey');
$table_name = format::get_fully_qualified_table_name($foreign['schema']['name'], $foreign['table']['name']);
$column_name = format::get_quoted_column_name($foreign['column']['name']);
$foreign['references'] = "{$table_name}({$column_name})";
}
protected static function get_data_row_update($node_schema, $node_table, $old_data_row_columns, $old_data_row, $new_data_row_columns, $new_data_row, $changed_columns)
{
if (count($changed_columns) == 0) {
throw new exception("empty changed_columns passed");
}
// what columns from new_data_row are different in old_data_row?
// those are the ones to push through the update statement to make the database current
$old_columns = array();
$update_columns = array();
foreach ($changed_columns as $changed_column) {
if (!isset($changed_column['old_col'])) {
$old_columns[] = 'NOTDEFINED';
} else {
$old_col_value = format::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['old_col']);
$old_columns[] = $changed_column['name'] . ' = ' . $old_col_value;
}
$update_col_name = format::get_quoted_column_name($changed_column['name']);
$update_col_value = format::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['new_col']);
$update_columns[] = $update_col_name . ' = ' . $update_col_value;
}
// if the computed update_columns expression is < 5 chars, complain
// if ( strlen($update_columns) < 5 ) {
// var_dump($update_columns);
// throw new exception(sprintf("%s.%s update_columns is < 5 chars, unexpected", $node_schema['name'], $node_table['name']));
// }
$old_columns = implode(', ', $old_columns);
$update_columns = implode(', ', $update_columns);
// use multiline comments here, so when data has newlines they can be preserved, but upgrade scripts don't catch on fire
$sql = sprintf("UPDATE %s SET %s WHERE (%s); /* old values: %s */\n", format::get_fully_qualified_table_name($node_schema['name'], $node_table['name']), $update_columns, dbx::primary_key_expression(dbsteward::$new_database, $node_schema, $node_table, $new_data_row_columns, $new_data_row), $old_columns);
return $sql;
}
public static function get_foreign_key_reference_sql($foreign)
{
return format::get_fully_qualified_table_name($foreign['schema']['name'], $foreign['table']['name']) . ' (' . format::get_quoted_column_name($foreign['column']['name']) . ')';
}
