/**
* Executes the statement in unbuffered mode (if possible)
*
* @internal
*
* @param fUnbufferedResult $result The object to place the result into
* @param array $params The parameters for the statement
* @param mixed &$extra A variable to place extra information needed by some database extensions
* @param boolean $different If this statement is different than the last statement run on the fDatabase instance
* @return void
*/
public function executeUnbufferedQuery($result, $params, &$extra, $different)
{
if ($different && $this->used) {
$this->regenerateStatement();
}
$this->used = TRUE;
$extension = $this->database->getExtension();
$connection = $this->database->getConnection();
$statement = $this->statement;
$params = $this->prepareParams($params);
// For the extensions that require the statement be passed to the result
// object, we store it in a stdClass object so the result object knows
// not to free it when done
$statement_holder = new stdClass();
$statement_holder->statement = NULL;
switch ($extension) {
case 'ibm_db2':
$extra = $statement;
if (db2_execute($statement, $params)) {
$statement_holder->statement = $statement;
} else {
$result->setResult(FALSE);
}
break;
case 'mssql':
$result->setResult(mssql_query($result->getSQL(), $this->connection, 20));
break;
case 'mysql':
$result->setResult(mysql_unbuffered_query($result->getSQL(), $this->connection));
break;
case 'mysqli':
$extra = $this->statement;
if ($statement->execute()) {
$statement_holder->statement = $statement;
} else {
$result->setResult(FALSE);
}
break;
case 'oci8':
$result->setResult(oci_execute($statement, $this->database->isInsideTransaction() ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS));
break;
case 'odbc':
$extra = odbc_execute($statement, $params);
if ($extra) {
odbc_longreadlen($statement, 1048576);
odbc_binmode($statement, ODBC_BINMODE_CONVERT);
$statement_holder->statement = $statement;
} else {
$result->setResult($extra);
}
break;
case 'pgsql':
$result->setResult(pg_execute($connection, $this->identifier, $params));
break;
case 'sqlite':
$result->setResult(sqlite_unbuffered_query($connection, $this->database->escape($statement, $params), SQLITE_ASSOC, $extra));
break;
case 'sqlsrv':
$extra = sqlsrv_execute($statement);
if ($extra) {
$statement_holder->statement = $statement;
} else {
$result->setResult($extra);
}
break;
case 'pdo':
$extra = $statement->execute();
if ($extra) {
$result->setResult($statement);
} else {
$result->setResult($extra);
}
break;
}
if ($statement_holder->statement) {
$result->setResult($statement_holder);
}
return $result;
}
/**
* Translates the structure of `CREATE TABLE` statements to the database specific syntax
*
* @param string $sql The SQL to translate
* @param array &$extra_statements Any extra SQL statements that need to be added
* @param array &$rollback_statements SQL statements to rollback `$sql` and `$extra_statements` if something goes wrong
* @return string The translated SQL
*/
private function translateAlterTableStatements($sql, &$extra_statements, &$rollback_statements = NULL)
{
if (!preg_match('#^\\s*ALTER\\s+TABLE\\s+(\\w+|"[^"]+")\\s+(.*)$#siD', $sql, $table_matches) && !preg_match('#^\\s*COMMENT\\s+ON\\s+COLUMN\\s+"?((?:\\w+"?\\."?)?\\w+)"?\\.("?\\w+"?\\s+IS\\s+(?:\'.*\'|%\\d+\\$s))\\s*$#Dis', $sql, $table_matches)) {
return $sql;
}
$statement = $table_matches[2];
$data = array('table' => $table_matches[1]);
if (preg_match('#"?(\\w+)"?\\s+IS\\s+(\'.*\'|:string\\w+|%\\d+\\$s)\\s*$#Dis', $statement, $statement_matches)) {
$data['type'] = 'column_comment';
$data['column_name'] = trim($statement_matches[1], '"');
$data['comment'] = $statement_matches[2];
} elseif (preg_match('#RENAME\\s+TO\\s+(\\w+|"[^"]+")\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'rename_table';
$data['new_table_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#RENAME\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+TO\\s+(\\w+|"[^"]+")\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'rename_column';
$data['column_name'] = trim($statement_matches[1], '"');
$data['new_column_name'] = trim($statement_matches[2], '"');
} elseif (preg_match('#ADD\\s+COLUMN\\s+("?(\\w+)"?.*)$#isD', $statement, $statement_matches)) {
$data['type'] = 'add_column';
$data['column_definition'] = $statement_matches[1];
$data['column_name'] = $statement_matches[2];
} elseif (preg_match('#DROP\\s+COLUMN\\s+(\\w+|"[^"]+")\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_column';
$data['column_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+TYPE\\s+(.*?)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'alter_type';
$data['column_name'] = trim($statement_matches[1], '"');
$data['data_type'] = $statement_matches[2];
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+DROP\\s+DEFAULT\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_default';
$data['column_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+SET\\s+DEFAULT\\s+(.*?)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'set_default';
$data['column_name'] = trim($statement_matches[1], '"');
$data['default_value'] = trim($statement_matches[2], '"');
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+DROP\\s+NOT\\s+NULL\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_not_null';
$data['column_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+SET\\s+NOT\\s+NULL(\\s+DEFAULT\\s+(.*))?\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'set_not_null';
$data['column_name'] = trim($statement_matches[1], '"');
if (isset($statement_matches[2])) {
$data['default'] = $statement_matches[3];
}
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+DROP\\s+CHECK\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_check_constraint';
$data['column_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#ALTER\\s+COLUMN\\s+(\\w+|"[^"]+")\\s+SET\\s+CHECK\\s+IN\\s+(\\(.*?\\))\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'set_check_constraint';
$data['column_name'] = trim($statement_matches[1], '"');
$data['constraint'] = ' CHECK(' . $statement_matches[1] . ' IN ' . $statement_matches[2] . ')';
} elseif (preg_match('#DROP\\s+PRIMARY\\s+KEY\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_primary_key';
} elseif (preg_match('#ADD\\s+PRIMARY\\s+KEY\\s*\\(\\s*([^\\)]+?)\\s*\\)(\\s+AUTOINCREMENT)?\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'add_primary_key';
$data['column_names'] = preg_split('#"?\\s*,\\s*"?#', trim($statement_matches[1], '"'), -1, PREG_SPLIT_NO_EMPTY);
$data['autoincrement'] = count($data['column_names']) == 1 && !empty($statement_matches[2]);
if (count($data['column_names']) == 1) {
$data['column_name'] = reset($data['column_names']);
}
} elseif (preg_match('#DROP\\s+FOREIGN\\s+KEY\\s*\\(\\s*(\\w+|"[^"]+")\\s*\\)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_foreign_key';
$data['column_name'] = trim($statement_matches[1], '"');
} elseif (preg_match('#ADD\\s+FOREIGN\\s+KEY\\s*\\((\\w+|"[^"]+")\\)\\s+REFERENCES\\s+("?(\\w+)"?\\s*\\(\\s*"?(\\w+)"?\\s*\\)\\s*.*)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'add_foreign_key';
$data['column_name'] = trim($statement_matches[1], '"');
$data['references'] = $statement_matches[2];
$data['foreign_table'] = self::unescapeIdentifier($statement_matches[3]);
$data['foreign_column'] = self::unescapeIdentifier($statement_matches[4]);
} elseif (preg_match('#DROP\\s+UNIQUE\\s*\\(\\s*([^\\)]+?)\\s*\\)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'drop_unique';
$data['column_names'] = preg_split('#"?\\s*,\\s*"?#', trim($statement_matches[1], '"'), -1, PREG_SPLIT_NO_EMPTY);
if (count($data['column_names']) == 1) {
$data['column_name'] = reset($data['column_names']);
}
} elseif (preg_match('#ADD\\s+UNIQUE\\s*\\(\\s*([^\\)]+?)\\s*\\)\\s*$#isD', $statement, $statement_matches)) {
$data['type'] = 'add_unique';
$data['column_names'] = preg_split('#"?\\s*,\\s*"?#', trim($statement_matches[1], '"'), -1, PREG_SPLIT_NO_EMPTY);
if (count($data['column_names']) == 1) {
$data['column_name'] = reset($data['column_names']);
}
} else {
return $sql;
}
$data['table'] = self::unescapeIdentifier($data['table']);
if (isset($data['new_table_name'])) {
$data['new_table_name'] = self::unescapeIdentifier($data['new_table_name']);
}
if (isset($data['column_name'])) {
$data['column_name'] = self::unescapeIdentifier($data['column_name']);
}
if (isset($data['column_names'])) {
$data['column_names'] = array_map(array('fSQLSchemaTranslation', 'unescapeIdentifier'), $data['column_names']);
}
if (isset($data['new_column_name'])) {
$data['new_column_name'] = self::unescapeIdentifier($data['new_column_name']);
}
if ($this->database->getType() == 'db2') {
$sql = $this->translateDB2AlterTableStatements($sql, $extra_statements, $data);
}
if ($this->database->getType() == 'mssql') {
$sql = $this->translateMSSQLAlterTableStatements($sql, $extra_statements, $data);
}
if ($this->database->getType() == 'mysql') {
$sql = $this->translateMySQLAlterTableStatements($sql, $extra_statements, $rollback_statements, $data);
}
if ($this->database->getType() == 'oracle') {
$sql = $this->translateOracleAlterTableStatements($sql, $extra_statements, $data);
}
if ($this->database->getType() == 'postgresql') {
$sql = $this->translatePostgreSQLAlterTableStatements($sql, $extra_statements, $data);
}
if ($this->database->getType() == 'sqlite') {
if ($data['type'] == 'rename_table') {
$sql = $this->translateSQLiteRenameTableStatements($sql, $extra_statements, $data);
} else {
$sql = $this->translateSQLiteAlterTableStatements($sql, $extra_statements, $data);
}
}
// All databases except for MySQL and Oracle support transactions around data definition queries
// All of the Oracle statements will fail on the first query, if at all, so we don't need to
// worry too much. MySQL is a huge pain though.
if (!in_array($this->database->getType(), array('mysql', 'oracle'))) {
array_unshift($extra_statements, $sql);
if (!$this->database->isInsideTransaction()) {
$sql = "BEGIN";
$extra_statements[] = "COMMIT";
$rollback_statements[] = "ROLLBACK";
} else {
$sql = array_shift($extra_statements);
}
}
return $sql;
}
/**
* Executes the statement in unbuffered mode (if possible)
*
* @internal
*
* @param fUnbufferedResult $result The object to place the result into
* @param array $params The parameters for the statement
* @param mixed &$extra A variable to place extra information needed by some database extensions
* @param boolean $different If this statement is different than the last statement run on the fDatabase instance
* @return void
*/
public function executeUnbufferedQuery($result, $params, &$extra, $different)
{
if (is_array($params) && count($params) == 1 && is_array($params[0]) && count($this->placeholders) > 1) {
$params = $params[0];
}
if ($different && $this->used) {
$this->regenerateStatement();
}
$this->used = TRUE;
$extension = $this->database->getExtension();
if ($extension == 'pdo' && $this->database->getType() == 'mssql') {
$extension = 'pdo_dblib';
}
$connection = $this->database->getConnection();
$statement = $this->statement;
$params = $this->prepareParams($params);
// For the extensions that require the statement be passed to the result
// object, we store it in a stdClass object so the result object knows
// not to free it when done
$statement_holder = new stdClass();
$statement_holder->statement = NULL;
switch ($extension) {
case 'ibm_db2':
$extra = $statement;
if (db2_execute($statement, $params)) {
$statement_holder->statement = $statement;
} else {
$result->setResult(FALSE);
}
break;
case 'mssql':
$result->setResult(mssql_query($result->getSQL(), $this->connection, 20));
break;
case 'mysql':
$result->setResult(mysql_unbuffered_query($result->getSQL(), $this->connection));
break;
case 'mysqli':
$extra = $this->statement;
if ($statement->execute()) {
$statement_holder->statement = $statement;
} else {
$result->setResult(FALSE);
}
break;
case 'oci8':
$result->setResult(oci_execute($statement, $this->database->isInsideTransaction() ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS));
break;
case 'pgsql':
$result->setResult(pg_execute($connection, $this->identifier, $params));
break;
case 'sqlite':
$result->setResult(sqlite_unbuffered_query($connection, $this->database->escape($statement, $params), SQLITE_ASSOC, $extra));
break;
case 'sqlsrv':
$extra = sqlsrv_execute($statement);
if ($extra) {
$statement_holder->statement = $statement;
} else {
$result->setResult($extra);
}
break;
case 'pdo':
$extra = $statement->execute();
if ($extra) {
$result->setResult($statement);
} else {
$result->setResult($extra);
}
break;
case 'pdo_dblib':
$sql = $this->database->escape($statement, $params);
$result->setResult($res = $connection->query($sql));
break;
}
if ($statement_holder->statement) {
$result->setResult($statement_holder);
}
return $result;
}
