summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDries Buytaert2010-06-26 01:40:05 (GMT)
committerDries Buytaert2010-06-26 01:40:05 (GMT)
commit5ca8621ba40016315be9a081814b31586920ff30 (patch)
treec595fb557b61fa920b308aa6e0bc8b8e4dbd3bfb
parent60883871aab067f13bd3411bfd2874b50cfe7978 (diff)
- Patch #715108 by Damien Tournoud, Berdir, Josh Waihi, Crell, chx, justinrandell: Make merge queries more consistent and robust.
-rw-r--r--includes/database/mysql/query.inc120
-rw-r--r--includes/database/query.inc63
2 files changed, 48 insertions, 135 deletions
diff --git a/includes/database/mysql/query.inc b/includes/database/mysql/query.inc
index 63c0fe8..584a4a4 100644
--- a/includes/database/mysql/query.inc
+++ b/includes/database/mysql/query.inc
@@ -87,121 +87,19 @@ class InsertQuery_mysql extends InsertQuery {
}
}
-class MergeQuery_mysql extends MergeQuery {
-
- public function execute() {
-
- // A merge query without any key field is invalid.
- if (count($this->keyFields) == 0) {
- throw new InvalidMergeQueryException("You need to specify key fields before executing a merge query");
- }
-
- // Set defaults.
- if ($this->updateFields) {
- $update_fields = $this->updateFields;
- }
- else {
- // When update fields are derived from insert fields, we don't need
- // placeholders since we can tell MySQL to reuse insert supplied
- // values using the VALUES(col_name) function.
- $update_fields = array();
- }
-
- $insert_fields = $this->insertFields + $this->keyFields;
-
- $max_placeholder = 0;
- $values = array();
- // We assume that the order here is the same as in __toString(). If that's
- // not the case, then we have serious problems.
- foreach ($insert_fields as $value) {
- $values[':db_insert_placeholder_' . $max_placeholder++] = $value;
- }
-
- // Expressions take priority over literal fields, so we process those first
- // and remove any literal fields that conflict.
- foreach ($this->expressionFields as $field => $data) {
- if (!empty($data['arguments'])) {
- $values += $data['arguments'];
- }
- unset($update_fields[$field]);
- }
-
- // Because we filter $fields the same way here and in __toString(), the
- // placeholders will all match up properly.
- $max_placeholder = 0;
- foreach ($update_fields as $field => $value) {
- $values[':db_update_placeholder_' . ($max_placeholder++)] = $value;
- }
-
-
- // MySQL's INSERT ... ON DUPLICATE KEY UPDATE queries return 1
- // (MergeQuery::STATUS_INSERT) for an INSERT operation or 2
- // (MergeQuery::STATUS_UPDATE) for an UPDATE operation.
- //
- // @link http ://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html
- $this->queryOptions['return'] = Database::RETURN_AFFECTED;
- return $this->connection->query((string) $this, $values, $this->queryOptions);
- }
-
-
+class TruncateQuery_mysql extends TruncateQuery {
public function __toString() {
- // Create a comments string to prepend to the query.
- $comments = (!empty($this->comments)) ? '/* ' . implode('; ', $this->comments) . ' */ ' : '';
-
- // Set defaults.
- if ($this->updateFields) {
- $update_fields = $this->updateFields;
+ // TRUNCATE is actually a DDL statement on MySQL, and DDL statements are
+ // not transactional, and result in an implicit COMMIT. When we are in a
+ // transaction, fallback to the slower, but transactional, DELETE.
+ if ($this->connection->inTransaction()) {
+ // Create a comments string to prepend to the query.
+ $comments = (!empty($this->comments)) ? '/* ' . implode('; ', $this->comments) . ' */ ' : '';
+ return $comments . 'DELETE FROM {' . $this->connection->escapeTable($this->table) . '}';
}
else {
- $update_fields = $this->insertFields;
- // If there are no exclude fields, this is a no-op.
- foreach ($this->excludeFields as $exclude_field) {
- unset($update_fields[$exclude_field]);
- }
- }
-
- // If the merge query has no fields to update, add the first key as an
- // update field so the query will not fail if a duplicate key is found.
- if (!$update_fields && !$this->expressionFields) {
- $update_fields = array_slice($this->keyFields, 0, 1, TRUE);
- }
-
- $insert_fields = $this->insertFields + $this->keyFields;
-
- $query = $comments . 'INSERT INTO {' . $this->table . '} (' . implode(', ', array_keys($insert_fields)) . ') VALUES ';
-
- $max_placeholder = 0;
- $values = array();
- // We don't need the $field, but this is a convenient way to count.
- foreach ($insert_fields as $field) {
- $values[] = ':db_insert_placeholder_' . $max_placeholder++;
+ return parent::__toString();
}
-
- $query .= '(' . implode(', ', $values) . ') ON DUPLICATE KEY UPDATE ';
-
- // Expressions take priority over literal fields, so we process those first
- // and remove any literal fields that conflict.
- $max_placeholder = 0;
- $update = array();
- foreach ($this->expressionFields as $field => $data) {
- $update[] = $field . '=' . $data['expression'];
- unset($update_fields[$field]);
- }
-
- // Build update fields clauses based on caller supplied list, or derived
- // from insert supplied values using the VALUES(col_name) function.
- foreach ($update_fields as $field => $value) {
- if ($this->updateFields) {
- $update[] = ($field . '=:db_update_placeholder_' . $max_placeholder++);
- }
- else {
- $update[] = ($field . '=VALUES(' . $field . ')');
- }
- }
-
- $query .= implode(', ', $update);
-
- return $query;
}
}
diff --git a/includes/database/query.inc b/includes/database/query.inc
index a319e6e..9e03330 100644
--- a/includes/database/query.inc
+++ b/includes/database/query.inc
@@ -801,29 +801,45 @@ class MergeQuery extends Query {
// In the degenerate case of this query type, we have to run multiple
// queries as there is no universal single-query mechanism that will work.
- // Our degenerate case is not designed for performance efficiency but
- // for comprehensibility. Any practical database driver will override
- // this method with database-specific logic, so this function serves only
- // as a fallback to aid developers of new drivers.
// Wrap multiple queries in a transaction, if the database supports it.
$transaction = $this->connection->startTransaction();
- // Manually check if the record already exists.
- $select = $this->connection->select($this->table);
- foreach ($this->keyFields as $field => $value) {
- $select->condition($field, $value);
- }
-
- $select = $select->countQuery();
- $sql = (string) $select;
- $arguments = $select->getArguments();
- $num_existing = $this->connection->query($sql, $arguments)->fetchField();
-
+ try {
+ // Manually check if the record already exists.
+ // We build a 'SELECT 1 FROM table WHERE conditions FOR UPDATE' query,
+ // that will lock the rows that matches our set of conditions as well as
+ // return the information that there are such rows.
+ $select = $this->connection->select($this->table);
+ $select->addExpression('1');
+ foreach ($this->keyFields as $field => $value) {
+ $select->condition($field, $value);
+ }
- if ($num_existing) {
- // If there is already an existing record, run an update query.
+ // Using SELECT FOR UPDATE syntax will lock the rows we want to attempt to update.
+ $sql = ((string) $select) . ' FOR UPDATE';
+ $arguments = $select->getArguments();
+
+ // If there are no existing records, run an insert query.
+ if (!$this->connection->query($sql, $arguments)->fetchField()) {
+ // If there is no existing record, run an insert query.
+ $insert_fields = $this->insertFields + $this->keyFields;
+ try {
+ $this->connection->insert($this->table, $this->queryOptions)->fields($insert_fields)->execute();
+ return MergeQuery::STATUS_INSERT;
+ }
+ catch (Exception $e) {
+ // The insert query failed, maybe it's because a racing insert query
+ // beat us in inserting the same row. Retry the select query, if it
+ // returns a row, ignore the error and continue with the update
+ // query below.
+ if (!$this->connection->query($sql, $arguments)->fetchField()) {
+ throw $e;
+ }
+ }
+ }
+ // Proceed with an update query if a row was found.
if ($this->updateFields) {
$update_fields = $this->updateFields;
}
@@ -835,7 +851,7 @@ class MergeQuery extends Query {
}
}
if ($update_fields || $this->expressionFields) {
- // Only run the update if there are no fields or expressions to update.
+ // Only run the update if there are fields or expressions to update.
$update = $this->connection->update($this->table, $this->queryOptions)->fields($update_fields);
foreach ($this->keyFields as $field => $value) {
$update->condition($field, $value);
@@ -847,13 +863,12 @@ class MergeQuery extends Query {
return MergeQuery::STATUS_UPDATE;
}
}
- else {
- // If there is no existing record, run an insert query.
- $insert_fields = $this->insertFields + $this->keyFields;
- $this->connection->insert($this->table, $this->queryOptions)->fields($insert_fields)->execute();
- return MergeQuery::STATUS_INSERT;
+ catch (Exception $e) {
+ // Something really wrong happened here, bubble up the exception to the
+ // caller.
+ $transaction->rollback();
+ throw $e;
}
-
// Transaction commits here where $transaction looses scope.
}