public static function get_data_sql($old_schema, $old_table, $new_schema, $new_table, $delete_mode = FALSE)
{
$sql = '';
$new_table_rows = dbx::get_table_rows($new_table);
if ($old_table == NULL) {
if (!$delete_mode) {
// old table doesnt exist, just pump inserts
if ($new_table_rows) {
$new_table_row_columns = preg_split("/[\\,\\s]+/", $new_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
foreach ($new_table_rows->row as $data_row) {
// is the row marked for delete?
if (isset($data_row['delete']) && strcasecmp($data_row['delete'], 'true') == 0) {
// don't insert it, we are inserting data that should be there
} else {
$sql .= mssql10_diff_tables::get_data_row_insert($new_schema, $new_table, $new_table_row_columns, $data_row);
}
}
}
}
} else {
// data row match scenarios are based on primary key matching
$old_table_rows = dbx::get_table_rows($old_table);
if ($old_table_rows) {
$old_table_row_columns = preg_split("/[\\,\\s]+/", $old_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
}
// is caller asking for deletes or data updates?
if ($delete_mode) {
// what old rows have no matches in the new rows? delete them
if ($old_table_rows) {
mssql10_diff_tables::table_data_rows_compare($old_table, $new_table, FALSE, $old_rows, $new_rows, $changes);
$count_old_rows = count($old_rows);
for ($i = 0; $i < $count_old_rows; $i++) {
mssql10_diff_tables::get_data_row_delete($old_schema, $old_table, $old_table_row_columns, $old_rows[$i], $sql_append);
//@REVISIT
$sql .= $sql_append;
}
}
} else {
if ($new_table_rows) {
$new_table_row_columns = preg_split("/[\\,\\s]+/", $new_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
}
// what columns in matching rows between old and new are different?
if ($old_table_rows && $new_table_rows) {
$new_table_primary_keys = preg_split("/[\\,\\s]+/", $new_table['primaryKey'], -1, PREG_SPLIT_NO_EMPTY);
mssql10_diff_tables::table_data_rows_compare($old_table, $new_table, TRUE, $old_rows, $new_rows, $changes);
$count_old_rows = count($old_rows);
for ($i = 0; $i < $count_old_rows; $i++) {
$new_data_row = NULL;
$changed_columns = NULL;
if (count($changes[$i]) > 0) {
// changes were found between primary key matched old_table_row and new_table_row
// get the sql to make that happen
$sql .= mssql10_diff_tables::get_data_row_update($new_schema, $new_table, $old_table_row_columns, $old_rows[$i], $new_table_row_columns, $new_rows[$i], $changes[$i]);
}
}
}
// what new rows are missing from the old? insert them
if ($new_table_rows) {
mssql10_diff_tables::table_data_rows_compare($new_table, $old_table, FALSE, $new_rows, $old_rows, $changes);
$count_new_rows = count($new_rows);
for ($i = 0; $i < $count_new_rows; $i++) {
$sql .= mssql10_diff_tables::get_data_row_insert($new_schema, $new_table, $new_table_row_columns, $new_rows[$i]);
}
}
}
}
// when the new table has data rows
// AND (
// new_table has an IDENTITY column
// OR
// old_table had an IDENTITY column
// )
// SET IDENTITY_INSERT for data row insert statements
// this is because the table IDENTITY column will not be stripped until stage 3 changes
// see 'identity()d table' console statements for where this is done
// reference FS#25730 - dbsteward not properly upgrading serial to int
if (strlen($sql) > 0 && $new_table_rows && (mssql10_table::has_identity($new_table) || is_object($old_table) && mssql10_table::has_identity($old_table))) {
// this is needed for mssql to allow IDENTITY columns to be explicitly specified
$sql = "SET IDENTITY_INSERT " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']) . " ON;\n" . $sql . "SET IDENTITY_INSERT " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']) . " OFF;\n";
}
return $sql;
}
