#356074 by chx and Damien Tournoud: Provide a sequences API. Gets rid of stupid tables that only contain an incrementing ID, and fixes database import problems due to user ID 0.

3 files changed, 93 insertions, 1 deletions

diff --git a/includes/actions.inc b/includes/actions.inc
index ce196cfe5..6921e2f44 100644
--- a/includes/actions.inc
+++ b/includes/actions.inc
@@ -324,7 +324,7 @@ function actions_save($function, $type, $params, $label, $aid = NULL) {
   // aid is the callback for singleton actions so we need to keep a separate
   // table for numeric aids.
   if (!$aid) {
-    $aid = db_insert('actions_aid')->useDefaults(array('aid'))->execute();
+    $aid = db_next_id();
   }
 
   db_merge('actions')
diff --git a/includes/database/database.inc b/includes/database/database.inc
index 82ac78bc7..81f04fb2e 100644
--- a/includes/database/database.inc
+++ b/includes/database/database.inc
@@ -1047,6 +1047,44 @@ abstract class DatabaseConnection extends PDO {
   public function commit() {
     throw new ExplicitTransactionsNotSupportedException();
   }
+
+  /**
+   * Retrieves an unique id from a given sequence.
+   *
+   * Use this function if for some reason you can't use a serial field. For
+   * example, MySQL has no ways of reading of the current value of a sequence
+   * and PostgreSQL can not advance the sequence to be larger than a given
+   * value. Or sometimes you just need a unique integer.
+   *
+   * @param $existing_id
+   *   After a database import, it might be that the sequences table is behind,
+   *   so by passing in the maximum existing id, it can be assured that we
+   *   never issue the same id.
+   * @return
+   *   An integer number larger than any number returned by earlier calls and
+   *   also larger than the $existing_id if one was passed in.
+   */
+  public function nextId($existing_id = 0) {
+    $transaction = $this->startTransaction();
+    // We can safely use literal queries here instead of the slower query
+    // builder because if a given database breaks here then it can simply
+    // override nextId. However, this is unlikely as we deal with short
+    // strings and integers and no known databases require special handling
+    // for those simple cases.
+    // If another transaction wants to write the same row, it will wait until
+    // this transaction commits.
+    $stmt = $this->query('UPDATE {sequences} SET value = GREATEST(value, :existing_id) + 1', array(
+      ':existing_id' => $existing_id,
+    ));
+    if (!$stmt->rowCount()) {
+      $this->query('INSERT INTO {sequences} (value) VALUES (:existing_id + 1)', array(
+        ':existing_id' => $existing_id,
+      ));
+    }
+    // The transaction gets committed when the transaction object gets
+    // destructed because it gets out of scope.
+    return $new_value;
+  }
 }
 
 /**
@@ -2079,6 +2117,24 @@ function db_close(array $options = array()) {
 }
 
 /**
+ * Retrieves a unique id.
+ *
+ * Use this function if for some reason you can't use a serial field,
+ * normally a serial field with db_last_insert_id is preferred.
+ *
+ * @param $existing_id
+ *   After a database import, it might be that the sequences table is behind,
+ *   so by passing in a minimum id, it can be assured that we never issue the
+ *   same id.
+ * @return
+ *   An integer number larger than any number returned before for this
+ *   sequence.
+ */
+function db_next_id($existing_id = 0) {
+  return Database::getConnection()->nextId($existing_id);
+}
+
+/**
  * @} End of "defgroup database".
  */
 
diff --git a/includes/database/mysql/database.inc b/includes/database/mysql/database.inc
index 3f143cfe2..bbf927655 100644
--- a/includes/database/mysql/database.inc
+++ b/includes/database/mysql/database.inc
@@ -68,6 +68,42 @@ class DatabaseConnection_mysql extends DatabaseConnection {
     // We don't want to override any of the defaults.
     return NULL;
   }
+
+  public function nextId($existing_id = 0) {
+    static $shutdown_registered = FALSE;
+    $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', array(), array('return' => Database::RETURN_INSERT_ID));
+    // This should only happen after an import or similar event.
+    if ($existing_id >= $new_id) {
+      // If we INSERT a value manually into the sequences table, on the next
+      // INSERT, MySQL will generate a larger value. However, there is no way
+      // of knowing whether this value already exists in the table. MySQL
+      // provides an INSERT IGNORE which would work, but that can mask problems
+      // other than duplicate keys. Instead, we use INSERT ... ON DUPLICATE KEY
+      // UPDATE in such a way that the UPDATE does not do anything. This way,
+      // duplicate keys do not generate errors but everything else does.
+      $this->query('INSERT INTO {sequences} (value) VALUES (:value) ON DUPLICATE KEY UPDATE value = value', array(':value' => $existing_id));
+      $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', array(), array('return' => Database::RETURN_INSERT_ID));
+    }
+    if (!$shutdown_registered) {
+      register_shutdown_function(array(get_class($this), 'nextIdDelete'));
+      $shutdown_registered = TRUE;
+    }
+    return $new_id;
+  }
+
+  public static function nextIdDelete() {
+    // While we want to clean up the table to keep it up from occupying too
+    // much storage and memory, we must keep the highest value in the table
+    // because InnoDB  uses an in-memory auto-increment counter as long as the
+    // server runs. When the server is stopped and restarted, InnoDB
+    // reinitializes the counter for each table for the first INSERT to the
+    // table based solely on values from the table so deleting all values would
+    // be a problem in this case. Also, TRUNCATE resets the auto increment
+    // counter.
+    $max_id = db_select('SELECT MAX(value) FROM {sequences}')->fetchField();
+    // We know we are using MySQL here, so need for the slower db_delete().
+    db_query('DELETE FROM {sequences} WHERE value < :value', array(':value' => $max_id));
+  }
 }