/** * Save data to this table. If a primary key value is given, that row will be * updated; otherwise, a new row will be inserted. * * @param array $data The data to insert. * @param string $pk_value The value of the record's PK. Null if the record doesn't exist. * @return \WordPress\Tabulate\DB\Record The updated or inserted record. * @throws Exception If the user doesn't have permission, or something else has gone wrong. */ public function save_record($data, $pk_value = null) { // Changeset only created here if not already in progress. $changeset_comment = isset($data['changeset_comment']) ? $data['changeset_comment'] : null; $change_tracker = new ChangeTracker($this->get_database()->get_wpdb(), $changeset_comment); $columns = $this->get_columns(); /* * Go through all data and clean it up before saving. */ $sql_values = array(); foreach ($data as $field => $value) { // Make sure this column exists in the DB. if (!isset($columns[$field])) { unset($data[$field]); continue; } $column = $this->get_column($field); if ($column->is_auto_increment()) { // Auto-incrementing columns. // Do nothing (don't set $sql_values item). } elseif ($column->is_boolean()) { // Boolean values. if ($column->nullable() && (is_null($value) || '' === $value)) { // Null. $data[$field] = null; $sql_values[$field] = 'NULL'; } elseif ($value === false || in_array(strtoupper($value), array('0', 'FALSE', 'OFF', 'NO'))) { // False. $data[$field] = false; $sql_values[$field] = '0'; } else { // True. $data[$field] = true; $sql_values[$field] = '1'; } } elseif (!$column->allows_empty_string() && '' === $value && $column->nullable()) { // Empty strings. $data[$field] = null; $sql_values[$field] = 'NULL'; } elseif (is_null($value) && $column->nullable()) { // Nulls. $data[$field] = null; $sql_values[$field] = 'NULL'; } elseif ($column->get_type() === 'point') { // POINT columns. $sql_values[$field] = "GeomFromText('" . esc_sql($value) . "')"; } elseif ($column->is_numeric()) { // Numeric values. $sql_values[$field] = $value; } else { // Everything else. $sql_values[$field] = "'" . esc_sql($value) . "'"; } } // Find the PK, and hide errors (for now). $pk_name = $this->get_pk_column()->get_name(); $this->database->get_wpdb()->hide_errors(); // Compile SQL for insert and update statements. // This is a workaround for NULL support in $wpdb->update() and $wpdb->insert(). // Can probably be removed when https://core.trac.wordpress.org/ticket/15158 is resolved. $set_items = array(); foreach ($sql_values as $field => $escd_datum) { $set_items[] = "`{$field}` = {$escd_datum}"; } $set_clause = 'SET ' . join(', ', $set_items); // Prevent PK from being set to empty. if (isset($data[$pk_name]) && empty($data[$pk_name])) { unset($data[$pk_name]); } $change_tracker->before_save($this, $data, $pk_value); if (!empty($pk_value)) { // Update? // Check permission. if (!Grants::current_user_can(Grants::UPDATE, $this->get_name())) { throw new Exception('You do not have permission to update data in this table.'); } $where_clause = $this->database->get_wpdb()->prepare("WHERE `{$pk_name}` = %s", $pk_value); $this->database->get_wpdb()->query('UPDATE ' . $this->get_name() . " {$set_clause} {$where_clause};"); $new_pk_value = isset($data[$pk_name]) ? $data[$pk_name] : $pk_value; } else { // Or insert? // Check permission. if (!Grants::current_user_can(Grants::CREATE, $this->get_name())) { throw new Exception('You do not have permission to insert records into this table.'); } $sql = 'INSERT INTO ' . $this->get_name() . ' ' . $set_clause . ';'; $this->database->get_wpdb()->query($sql); if (!empty($this->database->get_wpdb()->last_error)) { Exception::wp_die('The record was not created.', 'Unable to create record', $this->database->get_wpdb()->last_error, $sql); // WPCS: XSS OK. } if ($this->get_pk_column()->is_auto_increment()) { // Use the last insert ID. $new_pk_value = $this->database->get_wpdb()->insert_id; } elseif (isset($data[$pk_name])) { // Or the PK value provided in the data. $new_pk_value = $data[$pk_name]; } else { // If neither of those work, how can we find out the new PK value? throw new Exception("Unable to determine the value of the new record's prmary key."); } } $new_record = $this->get_record($new_pk_value); if (!$new_record instanceof Record) { throw new Exception("Unable to fetch record with PK of: <code>" . var_export($new_pk_value, true) . '</code>'); } // Save the changes. $change_tracker->after_save($this, $new_record); // Show errors again, reset the record count, // and return the new or updated record. $this->database->get_wpdb()->show_errors(); $this->record_counter->clear(); return $new_record; }
/** * Save data to this table. If the 'id' key of the data array is numeric, * the row with that ID will be updated; otherwise, a new row will be * inserted. * * @param array $data The data to insert. * @param string $pk_value The value of the record's PK. * @return Record The updated or inserted record. */ public function save_record($data, $pk_value = null) { // Changeset only created here if not already in progress. $changeset_comment = isset($data['changeset_comment']) ? $data['changeset_comment'] : null; $change_tracker = new ChangeTracker($this->get_database()->get_wpdb(), $changeset_comment); $columns = $this->get_columns(); /* * Go through all data and clean it up before saving. */ foreach ($data as $field => $value) { // Make sure this column exists in the DB. if (!isset($columns[$field])) { unset($data[$field]); continue; } $column = $this->get_column($field); // Boolean values. if ($column->is_boolean()) { $zeroValues = array(0, '0', false, 'false', 'FALSE', 'off', 'OFF', 'no', 'NO'); if (($value === null || $value === '') && $column->nullable()) { $data[$field] = null; } elseif (in_array($value, $zeroValues, true)) { $data[$field] = false; } else { $data[$field] = true; } } // Empty strings. if (!$column->allows_empty_string() && '' === $value && $column->nullable()) { $data[$field] = null; } } // Find the PK, and hide errors (for now). $pk_name = $this->get_pk_column()->get_name(); $this->database->get_wpdb()->hide_errors(); // Compile SQL for insert and update statements. // This is a workaround for NULL support in $wpdb->update() and $wpdb->insert(). // Can probably be removed when https://core.trac.wordpress.org/ticket/15158 is resolved. $set_items = array(); foreach ($data as $field => $datum) { if (is_null($datum)) { $escd_datum = 'NULL'; } elseif (is_numeric($datum)) { $escd_datum = $datum; } else { $escd_datum = "'" . esc_sql($datum) . "'"; } $set_items[] = "`{$field}` = {$escd_datum}"; } $set_clause = 'SET ' . join(', ', $set_items); // Prevent PK from being set to empty. if (isset($data[$pk_name]) && empty($data[$pk_name])) { unset($data[$pk_name]); } $change_tracker->before_save($this, $data, $pk_value); if ($pk_value) { // Update? // Check permission. if (!Grants::current_user_can(Grants::UPDATE, $this->get_name())) { throw new \Exception('You do not have permission to update data in this table.'); } $where_clause = $this->database->get_wpdb()->prepare("WHERE `{$pk_name}` = %s", $pk_value); $this->database->get_wpdb()->query('UPDATE ' . $this->get_name() . " {$set_clause} {$where_clause};"); $new_pk_value = isset($data[$pk_name]) ? $data[$pk_name] : $pk_value; } else { // Or insert? // Check permission. if (!Grants::current_user_can(Grants::CREATE, $this->get_name())) { throw new \Exception('You do not have permission to insert records into this table.'); } $this->database->get_wpdb()->query('INSERT INTO ' . $this->get_name() . " {$set_clause};"); if (!empty($this->database->get_wpdb()->last_error)) { throw new Exception($this->database->get_wpdb()->last_error); } $new_pk_value = $this->database->get_wpdb()->insert_id; } $new_record = $this->get_record($new_pk_value); // Save the changes. $change_tracker->after_save($this, $new_record); // Show errors again, reset the record count, // and return the new or updated record. $this->database->get_wpdb()->show_errors(); $this->record_count = false; return $new_record; }