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; }