/**
* 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.
*/
$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);
// Auto-incrementing columns.
if ($column->is_auto_increment()) {
// Don't set $sql_values item.
} elseif ($column->is_boolean()) {
$zeroValues = array(0, '0', false, 'false', 'FALSE', 'off', 'OFF', 'no', 'NO');
if (($value === null || $value === '') && $column->nullable()) {
$data[$field] = null;
$sql_values[$field] = 'NULL';
} elseif (in_array($value, $zeroValues, true)) {
$data[$field] = false;
$sql_values[$field] = '0';
} else {
$data[$field] = true;
$sql_values[$field] = '1';
}
} elseif (!$column->allows_empty_string() && '' === $value && $column->nullable()) {
$data[$field] = null;
$sql_values[$field] = 'NULL';
} elseif (is_null($value) && $column->nullable()) {
$data[$field] = null;
$sql_values[$field] = 'NULL';
} elseif ($column->get_type() == 'point') {
$sql_values[$field] = "GeomFromText('" . esc_sql($value) . "')";
} elseif ($column->is_numeric()) {
$sql_values[$field] = $value;
} 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 ($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);
}
$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;
}
/**
* Rename this table and all of its change-tracker entries.
* @param string $new_name
*/
public function rename($new_name)
{
if ($this->get_database()->get_table($new_name)) {
throw new Exception("Table '{$new_name}' already exists");
}
$wpdb = $this->get_database()->get_wpdb();
$old_name = $this->get_name();
$wpdb->query("RENAME TABLE `{$old_name}` TO `{$new_name}`;");
$this->get_database()->reset();
$new = $this->get_database()->get_table($new_name, false);
if (!$new) {
throw new Exception("Table '{$old_name}' was not renamed to '{$new_name}'");
}
$this->name = $new->get_name();
$wpdb->query("UPDATE `" . ChangeTracker::changes_name() . "`" . " SET `table_name` = '{$new_name}' " . " WHERE `table_name` = '{$old_name}';");
}
/**
* Get most recent changes.
* @return array|string
*/
public function get_changes()
{
$wpdb = $this->table->get_database()->get_wpdb();
$sql = "SELECT cs.id AS changeset_id, c.id AS change_id, date_and_time, " . "user_nicename, table_name, record_ident, column_name, old_value, " . "new_value, comment " . "FROM " . ChangeTracker::changes_name() . " c " . " JOIN " . ChangeTracker::changesets_name() . " cs ON (c.changeset_id=cs.id) " . " JOIN {$wpdb->prefix}users u ON (u.ID=cs.user_id) " . "WHERE table_name = %s AND record_ident = %s" . "ORDER BY date_and_time DESC, cs.id DESC " . "LIMIT 15 ";
$params = array($this->table->get_name(), $this->get_primary_key());
return $wpdb->get_results($wpdb->prepare($sql, $params));
}