/**
* 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();
}