/** * The "sane" behaviour requires explicit commits. * * @throws DatabaseTransactionExplicitCommitNotAllowedException */ public function commit() { if (!$this->settings->Get_Sane()) { throw new DatabaseTransactionExplicitCommitNotAllowedException(); } // Cannot commit a rolledback transaction... if ($this->rolledBack) { throw new Exception('Cannot Commit after rollback.'); //DatabaseTransactionCannotCommitAfterRollbackException(); } // Mark as commited, and commit! $this->commited = TRUE; $this->connection->popTransaction($this->name); }
/** * Summary of pushTransaction * @param string $name * @param DatabaseTransactionSettings $settings * @throws DatabaseTransactionNameNonUniqueException * @return void */ public function pushTransaction($name, $settings = NULL) { if ($settings == NULL) { $settings = DatabaseTransactionSettings::GetBetterDefaults(); } if (!$this->supportsTransactions()) { return; } if (isset($this->transactionLayers[$name])) { throw new DatabaseTransactionNameNonUniqueException($name . " is already in use."); } $started = FALSE; // If we're already in a transaction. // TODO: Transaction scope Options is not working properly // for first level transactions. It assumes that - always - a first level // transaction must be started. if ($this->inTransaction()) { switch ($settings->Get_ScopeOption()) { case DatabaseTransactionScopeOption::RequiresNew(): $this->query_direct('SAVE TRANSACTION ' . $name); $started = TRUE; break; case DatabaseTransactionScopeOption::Required(): // We are already in a transaction, do nothing. break; case DatabaseTransactionScopeOption::Supress(): // The only way to supress the ambient transaction is to use a new connection // during the scope of this transaction, a bit messy to implement. throw new Exception('DatabaseTransactionScopeOption::Supress not implemented.'); } } else { if ($settings->Get_IsolationLevel() != DatabaseTransactionIsolationLevel::Ignore()) { $current_isolation_level = strtoupper($this->schema()->UserOptions()['isolation level']); // Se what isolation level was requested. $level = $settings->Get_IsolationLevel()->__toString(); if (strcasecmp($current_isolation_level, $level) !== 0) { $this->query_direct("SET TRANSACTION ISOLATION LEVEL {$level}"); } } // In order to start a transaction current statement cursors // must be closed. foreach($this->statement_cache as $statement) { $statement->closeCursor(); } $this->connection->beginTransaction(); } // Store the name and settings in the stack. $this->transactionLayers[$name] = array('settings' => $settings, 'active' => TRUE, 'name' => $name, 'started' => $started); }
/** * {@inheritdoc} */ public function execute() { if (!$this->preExecute()) { return NULL; } // Default options for upsert queries. $this->queryOptions += array( 'throw_exception' => TRUE, ); // Default fields are always placed first for consistency. $insert_fields = array_merge($this->defaultFields, $this->insertFields); $insert_fields_escaped = array_map(function($f) { return $this->connection->escapeField($f); }, $insert_fields); $table = $this->connection->escapeTable($this->table); $unique_key = $this->connection->escapeField($this->key); // We have to execute multiple queries, therefore we wrap everything in a // transaction so that it is atomic where possible. $transaction = $this->connection->startTransaction(NULL, DatabaseTransactionSettings::GetDDLCompatibleDefaults()); // First, create a temporary table with the same schema as the table we // are trying to upsert in. $query = 'SELECT TOP(0) * FROM {' . $table . '}'; $temp_table = $this->connection->queryTemporary($query, [], array_merge($this->queryOptions, array('real_table' => TRUE))); // Second, insert the data in the temporary table. $insert = $this->connection->insert($temp_table, $this->queryOptions) ->fields($insert_fields); foreach ($this->insertValues as $insert_values) { $insert->values($insert_values); } $insert->execute(); // Third, lock the table we're upserting into. $this->connection->query("SELECT 1 FROM {{$table}} WITH (HOLDLOCK)", [], $this->queryOptions); // Fourth, update any rows that can be updated. This results in the // following query: // // UPDATE table_name // SET column1 = temp_table.column1 [, column2 = temp_table.column2, ...] // FROM temp_table // WHERE table_name.id = temp_table.id; $update = []; foreach ($insert_fields_escaped as $field) { if ($field !== $unique_key) { $update[] = "$field = {" . $temp_table . "}.$field"; } } $update_query = 'UPDATE {' . $table . '} SET ' . implode(', ', $update); $update_query .= ' FROM {' . $temp_table . '}'; $update_query .= ' WHERE {' . $temp_table . '}.' . $unique_key . ' = {' . $table . '}.' . $unique_key; $this->connection->query($update_query, [], $this->queryOptions); // Fifth, insert the remaining rows. This results in the following query: // // INSERT INTO table_name // SELECT temp_table.primary_key, temp_table.column1 [, temp_table.column2 ...] // FROM temp_table // LEFT OUTER JOIN table_name ON (table_name.id = temp_table.id) // WHERE table_name.id IS NULL; $select = $this->connection->select($temp_table, 'temp_table', $this->queryOptions) ->fields('temp_table', $insert_fields); $select->leftJoin($this->table, 'actual_table', 'actual_table.' . $this->key . ' = temp_table.' . $this->key); $select->isNull('actual_table.' . $this->key); $this->connection->insert($this->table, $this->queryOptions) ->from($select) ->execute(); // Drop the "temporary" table. $this->connection->query_direct("DROP TABLE {$temp_table}"); $transaction->commit(); // Re-initialize the values array so that we can re-use this query. $this->insertValues = array(); return TRUE; }
public function execute() { if (!$this->preExecute()) { return NULL; } // Fetch the list of blobs and sequences used on that table. $columnInformation = $this->connection->schema()->queryColumnInformation($this->table); // Find out if there is an identity field set in this insert. $this->setIdentity = !empty($columnInformation['identity']) && in_array($columnInformation['identity'], $this->insertFields); $identity = !empty($columnInformation['identity']) ? $columnInformation['identity'] : NULL; #region Select Based Insert if (!empty($this->fromQuery)) { // Re-initialize the values array so that we can re-use this query. $this->insertValues = array(); $stmt = $this->connection->prepareQuery((string) $this); // Handle the case of SELECT-based INSERT queries first. $arguments = $this->fromQuery->getArguments(); DatabaseUtils::BindArguments($stmt, $arguments); $stmt->execute(); // We can only have 1 identity column per table (or none, where fetchColumn will fail) try { return $stmt->fetchColumn(0); } catch(\PDOException $e) { return NULL; } } #endregion #region Inserts with no values (full defaults) // Handle the case of full-default queries. if (empty($this->fromQuery) && (empty($this->insertFields) || empty($this->insertValues))) { // Re-initialize the values array so that we can re-use this query. $this->insertValues = array(); $stmt = $this->connection->prepareQuery((string) $this); $stmt->execute(); // We can only have 1 identity column per table (or none, where fetchColumn will fail) try { return $stmt->fetchColumn(0); } catch(\PDOException $e) { return NULL; } } #endregion #region Regular Inserts // Each insert happens in its own query. However, we wrap it in a transaction // so that it is atomic where possible. $transaction = NULL; $batch_size = 200; // At most we can process in batches of 250 elements. $batch = array_splice($this->insertValues, 0, $batch_size); // If we are going to need more than one batch for this... start a transaction. if (empty($this->queryOptions['sqlsrv_skip_transactions']) && !empty($this->insertValues)) { $transaction = $this->connection->startTransaction('', DatabaseTransactionSettings::GetBetterDefaults()); } while (!empty($batch)) { // Give me a query with the amount of batch inserts. $query = (string) $this->__toString2(count($batch)); // Prepare the query. $stmt = $this->connection->prepareQuery($query); // We use this array to store references to the blob handles. // This is necessary because the PDO will otherwise messes up with references. $blobs = array(); $max_placeholder = 0; foreach ($batch as $insert_index => $insert_values) { $values = array_combine($this->insertFields, $insert_values); DatabaseUtils::BindValues($stmt, $values, $blobs, ':db_insert', $columnInformation, $max_placeholder, $insert_index); } $stmt->execute(array(), array('fetch' => PDO::FETCH_ASSOC)); // We can only have 1 identity column per table (or none, where fetchColumn will fail) // When the column does not have an identity column, no results are thrown back. foreach($stmt as $insert) { try { $this->inserted_keys[] = $insert[$identity]; } catch(\Exception $e) { $this->inserted_keys[] = NULL; } } // Fetch the next batch. $batch = array_splice($this->insertValues, 0, $batch_size); } // If we started a transaction, commit it. if ($transaction) { $transaction->commit(); } // Re-initialize the values array so that we can re-use this query. $this->insertValues = array(); // Return the last inserted key. return empty($this->inserted_keys) ? NULL : end($this->inserted_keys); #endregion }
/** * Override DatabaseSchema::changeField(). * * @status complete */ public function changeField($table, $field, $field_new, $spec, $new_keys = array()) { if (!$this->fieldExists($table, $field)) { throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot change the definition of field %table.%name: field doesn't exist.", array('%table' => $table, '%name' => $field))); } if (($field != $field_new) && $this->fieldExists($table, $field_new)) { throw new DatabaseSchemaObjectExistsException(t("Cannot rename field %table.%name to %name_new: target field already exists.", array('%table' => $table, '%name' => $field, '%name_new' => $field_new))); } // SQL Server supports transactional DDL, so we can just start a transaction // here and pray for the best. // @var DatabaseTransaction_sqlsrv $transaction = $this->connection->startTransaction(NULL, DatabaseTransactionSettings::GetDDLCompatibleDefaults()); // IMPORTANT NOTE: To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field. // That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). // @see https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_change_field/7 // // What we are going to do in the SQL Server Driver is a best-effort try to preserve original keys if they do not conflict // with the new_keys parameter, and if the callee has done it's job (droping constraints/keys) then they will of course not be recreated. // Introspect the schema and save the current primary key if the column // we are modifying is part of it. Make sure the schema is FRESH. $this->queryColumnInformationInvalidate($table); $primary_key_fields = $this->introspectPrimaryKeyFields($table); if (in_array($field, $primary_key_fields)) { // Let's drop the PK $this->cleanUpPrimaryKey($table); } // If there is a generated unique key for this field, we will need to // add it back in when we are done $unique_key = $this->uniqueKeyExists($table, $field); // Drop the related objects. $this->dropFieldRelatedObjects($table, $field); // Verify if the old field specification was nullable. $info = $this->queryColumnInformation($table); $old_field_nullable = $info['columns'][$field]['is_nullable'] == TRUE; // Start by renaming the current column. $this->connection->query_direct('EXEC sp_rename :old, :new, :type', array( ':old' => $this->connection->prefixTables('{' . $table . '}.' . $field), ':new' => $field . '_old', ':type' => 'COLUMN', )); // If the new SPEC does not allow NULLS but the old SPEC did so, we // need to bridge the data and manually populate default values. $fixnull = FALSE; if (!empty($spec['not null']) && $old_field_nullable) { $fixnull = TRUE; $spec['not null'] = FALSE; } // Create a new field. $this->addField($table, $field_new, $spec); // Migrate the data over. // Explicitly cast the old value to the new value to avoid conversion errors. $field_spec = $this->processField($spec); $this->connection->query_direct("UPDATE [{{$table}}] SET [{$field_new}] = CAST([{$field}_old] AS {$field_spec['sqlsrv_type']})"); // Switch to NOT NULL now. if ($fixnull === TRUE) { // There is no warranty that the old data did not have NULL values, we need to populate // nulls with the default value because this won't be done by MSSQL by default. $default_expression = $this->defaultValueExpression($field_spec['sqlsrv_type'], $field_spec['default']); $this->connection->query_direct("UPDATE [{{$table}}] SET [{$field_new}] = {$default_expression} WHERE [{$field_new}] IS NULL"); // Now it's time to make this non-nullable. $spec['not null'] = TRUE; $this->connection->query_direct('ALTER TABLE {' . $table . '} ALTER COLUMN ' . $this->createFieldSql($table, $field_new, $this->processField($spec), TRUE)); } // Initialize new keys. if (!isset($new_keys)) { $new_keys = array( 'unique keys' => array(), 'primary keys' => array() ); } // Recreate the primary key if no new primary key // has been sent along with the change field. if (in_array($field, $primary_key_fields) && (!isset($new_keys['primary keys']) || empty($new_keys['primary keys']))) { // The new primary key needs to have // the new column name. unset($primary_key_fields[$field]); $primary_key_fields[$field_new] = $field_new; $new_keys['primary key'] = $primary_key_fields; } // Recreate the unique constraint if it existed. if ($unique_key && !isset($new_keys['unique keys']) && !in_array($field_new, $new_keys['unique keys'])) { $new_keys['unique keys'][] = $field_new; } // Drop the old field. $this->dropField($table, $field . '_old'); // Add the new keys. $this->recreateTableKeys($table, $new_keys); // Refresh introspection for this table. $this->queryColumnInformationInvalidate($table); // Commit. $transaction->commit(); }