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']) . ')'; }